Please give the answer for 5,6,7 4. Exercises on university database: Submit SQl
ID: 3748795 • Letter: P
Question
Please give the answer for 5,6,7
4. Exercises on university database: Submit SQl queries for following statements. 40 points 1. Find the names of courses in Computer science department which have 3 credits 2. For the student with ID 12345 (or any other value), show all course id and title of all 3. 4. Display the IDs of all instructors who have never taught a couse. courses registered for by the student. Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names) 5. You need to create a movie database. For that, create three tables, one for actors(AID, name), one for movies(MID, title) and one for actor_role(MID, AID, rolename). Use appropriate data types for each of the attributes and add appropriate primary/foreign key constraints. Insert at least two records in instructor table from History department. Update budget of Music department by 10%. 6. 7.Explanation / Answer
1. SELECT COURSE_ID,COURSE_NAME FROM COURSES WHERE DEPARTMENT_NAME="CSE" AND CREDITS=3;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
2.SELECT COURSE_ID,COURSE_TITLE FROM STUDENT,TAKES STUDENT.ID=TAKES.ID AND STUDENT.ID=12345;
-------------------------------------------------------------------------------------------------------------------------------------------------------------
3.SELECT STUDENT_NAME,STUDENT_ID FROM STUDENT,TAKES,COURSE WHERE TAKES.COURSEID=COURSE.COURSEID AND STUDENT.ID=TAKES.ID AND DEPARTMENT_NAME="CSE";
-------------------------------------------------------------------------------------------------------------------------------------------------------------
4.SELECT INSTRUCTOR.ID,INSTRUCTOR.NAME FROM INSTRUCTOR WHERE INSTRUCTOR.ID NOT IN (SELECT DISTINCT ID FROM TEACHES);
--------------------------------------------------------------------------------------------------------------------------------------------------------------
5.CREATE TABLE ACTOR(AID INT PRIMARY KEY,ACTOR_NAME CHAR(100));
CREATE TABLE MOVIES(MID INT PRIMARY KEY ,TITLE CHAR(100));
CREATE TABLE ACTOR_ROLE(MID INT,AID INT,ROLENAME CHAR(20));
ALTER TABLE ACTOR_ROLE ADD FOREIGN KEY(MID) REFERNCES MOVIES(MID);
ALTER TABLE ACTOR_ROLE ADD FOREIGN KEY(AID) REFERENCES ACTOR(AID);
-------------------------------------------------------------------------------------------------------------------------------------------------------------
6. INSERT INTO INSTRUCTOR (INSTRUCTOR_NAME,INSTRUCTOR_ID,SALARY,DEPARTMENT) SELECT NAME,ID,SAL,DEPNAME FROM DEPARTMENT WHERE DEPARTMENT.DEPNAME="HISTORY"
--------------------------------------------------------------------------------------------------------------------------------------------------------
7.UPDATE DEPARTMENT SET BUDJET=BUDJET+(BUDJET*0.1) WHERE DEPARTMENT.NAME="MUSIC"
IN THE GIVEN QUESTION THEY DIDN'T MENTIONED ANY REALTION TABLES OR ANY ER DIAGRAM SO THAT I ASSUMED SOME TABLES BASED ON UNIVERSITY DATABASE AND I EXECUTED THE QUERIES.HOPE IT WILL HELP YOU IF YOU HAVE ANY DOUBTS PLEASE PUT A COMMENT.