Your organization has requested that you create a new database to support organizational needs. Based on the information you provided in this weeks Discussion, create a database as defined in the database project.

The project must include at least five tables, and should have no more than 10 tables.

1. Define your database.
   • Describe the database, its purpose, why it was created, who it was created for, who the users are, and what type of processes and functions it will support.
   • List the entities of your database.
   • Present the business rules that determine connectivity, using the format reviewed in Week 2.
   • Draw the ERD; copy the drawing into your Word project document.

Can anyone answer this above question based on below answer . I mean you the below university DB details to give the above 1st point answer

//Query to create Database

CREATE DATABASE University;

//Query to use University Database

USE University;

//Query to create Student table

CREATE TABLE Students( student_id INT NOT NULL, name VARCHAR(50) NOT NULL, branch VARCHAR(10), PRIMARY KEY(student_id) );

//Query to create Courses table

CREATE TABLE Courses( course_id INT NOT NULL, course_name VARCHAR(50), PRIMARY KEY(course_id) );

//Query to create Teachers table

CREATE TABLE Teachers( teacher_id INT NOT NULL, name VARCHAR(50), salary DECIMAL(10,2), course_id INT, PRIMARY KEY(teacher_id), FOREIGN KEY(course_id) REFERENCES Courses(course_id) );

//Query to create Tests table

CREATE TABLE Tests( test_id INT NOT NULL, test_name VARCHAR(50), total_marks INT, PRIMARY KEY(test_id) );

//Query to create Score table

CREATE TABLE Scores( score_id INT NOT NULL, student_id INT, course_id INT, marks INT, test_id INT, PRIMARY KEY(score_id), FOREIGN KEY(student_id) REFERENCES Students(student_id), FOREIGN KEY(course_id) REFERENCES Courses(course_id), FOREIGN KEY(test_id) REFERENCES Tests(test_id) );

//Query to insert data into Students table

INSERT INTO Students VALUES(1, "Student1", "CSE"), (2, "Student2", "Mech"), (3, "Student3", "CSE"), (4, "Student4", "Civil");

//Query to insert data into Courses table

INSERT INTO Courses VALUES(101, "Course101"), (102, "Course102"), (103, "Course103");

//Query to insert data into Teachers table

INSERT INTO Teachers VALUES(201, "Course101 Teacher", "8000", 101), (202, "Course102 Teacher", "7000", 102), (203, "Course103 Teacher", "10000", 103);

//Query to insert data into Tests table

INSERT INTO Tests VALUES(301, "Workshop", 200), (302, "Mains", 70), (303, "Practicals", 30);

//Query to insert data into Scores table

INSERT INTO Scores VALUES(401, 1, 101, 70, 301), (402, 2, 102, 75, 302), (403, 3, 103, 80, 303);

//Query to display data of Students, Courses, Teachers, Tests, and Scores

SELECT * FROM Students;

SELECT * FROM Courses;

SELECT * FROM Teachers;

SELECT * FROM Tests;

SELECT * FROM Scores;

2. Perform given SQL on above tables

1. Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk(*). State the purpose of the query; show the query and the output.

//Below Query will list out the all the Students with student_id, name, and branch where branch is CSE.

SELECT student_id, name, branch FROM Students WHERE branch="CSE";

2. Get information from at least 3 tables in one statement, and provide the output using the Join operator. Use ANSI Join syntax. State the purpose of the query; show the query and the output. Add a screen shot of SS Management Studio showing the query and results.

//Below Query will display the Marks of Student2 in Course102

SELECT Students.name, Students.branch, Courses.course_name, Scores.marks FROM Students JOIN Scores ON Students.student_id = Scores,student_id JOIN Courses ON Scores.course_id = Courses.course_id WHERE Students.student_id = 2 and Courses.course_id = 102;

3. Get information from 2 tables in one statement, and provide the output using the Left Outer Join operator. State the purpose of the query; show the query and the output. The outer join should be designed to retrieve information from the left table that has no matches in the right table. If that is not possible for your database, explain why.

//Below Query will display the marks of Students, if marks for a student doesn'e exist, marks will display as NULL

SELECT Students.name, Scores.marks FROM Students LEFT OUTER JOIN Scores ON Students.student_id = Scores.student_id;

4. Create a query using the IN keyword with a subquery. State the purpose of the query; show the query and the output.

//Below Query will list out all teachers and their salaries where their teaching course is there in the course id that we select in sub-query from Courses table.

SELECT name, salary FROM Teachers WHERE course_id IN (SELECT course_id FROM Courses);

5. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command. State the purpose of the query; show the query and the output.

//Below Query displays the COUNT of students in particular branch

SELECT COUNT(student_id), branch FROM Students GROUP BY branch;

6. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command using the HAVING clause to filter the aggregate results. State the purpose of the query; show the query and the output.

//Below Query displays the COUNT of students in particular branch only when number of students persuing the course is grater than 1

SELECT COUNT(student_id), branch FROM Students GROUP BY branch HAVING COUNT(student_id) > 1;

7. Update one row. State the purpose of the query; show the result set for the row(s) before the update; show the query; show the row(s) after the update.

//Below Query will update the Salary of teacher to 15000 where course_id = 102

//Before update SELECT * FROM Teachers;

//Update UPDATE Teachers SET salary = "15000" WHERE course_id = 102;

//After Update SELECT * FROM Teachers;

8. Delete one row. State the purpose of the query; show the result set before the delete; show the query; show the result set after the delete.

//Below Query delets the Test row where test_id is 303

DELETE FROM Tests WHERE test_id = 303;