CSCE 2501                Design OF SQL Queries            Dr. KHALIL
Consider the given relational DATABASE schema FOR a university application. The PRIMARY KEY OF each relation IS underlined.
STUDENT (StudentId, Fname, Lname, Gender, Bdate, Class)
DEPARTMENT (DeptCode, DeptName)
STUDENT_MAJOR (StudentId, MajorDeptCode)
STUDENT_MINOR (StudentId, MinorDeptCode)
INSTRUCTOR (InstId, Fname, Lname, STATUS, DeptCode, Salary)
COURSE (DeptCode, CourseNo, CourseName, CreditHours)
PREREQUISITE (DeptCode, CourseNo, PreDeptCode, PreCourseNo)
SECTION (DeptCode, CourseNo, SectionINo, Semester, YEAR, MW/UTR, TIME, InstId)
TRANSCRIPT(StudentId, DeptCode, CourseNo, SectionNo, Semester, YEAR, Grade)

1.  WRITE one UPDATE statement TO increase salaries OF CSCE instructors BY 10%, ECNG instructors BY 15% AND MENG instructors BY 5% (CSCE, EENG AND MENG are departments’ codes)
UPDATE instructor
SET Salary = CASE
    WHEN deptCode = 'CSCE' THEN Salary * 1.10
    WHEN deptCode = 'ECNG' THEN Salary * 1.15
    WHEN deptCode = 'MENG' THEN Salary * 1.05
    ELSE NULL
END
WHERE deptCode IN ('CSCE', 'ECNG', 'MENG');


2.  Display the Salary OF each Instructor after a raise OF 10%.
SELECT salary*1.1
FROM instructor;


3.  Generate a list OF ALL CSCE AND CSCI students. The list should contain the student Id, FULL name, AND major. The should be ordered BY major (ASC) AND gender (DESC).
CREATE VIEW CompMajors (S_ID, SM_DCode, S_Fname, S_Lname, S_Gender)
AS (
    SELECT S.studentId, SM.majorDeptCode, S.Fname, S.Lname, S.Gender
    FROM student S INNER JOIN Student_Major SM
    ON S.studentId = SM.studentId
);

-----

SELECT *
FROM CompMajors
WHERE SM_DCode = 'CSCE' OR SM_DCode = 'CSCI'
ORDER BY SM_DCode ASC, S_Gender DESC;


4.  GET ALL senior students (Class=4) ordered ascendengly according TO student LAST name.
SELECT *
FROM student S
WHERE S.Class = 4
ORDER BY S.Lname;


5.  Display the average instructor salary FOR each department.
SELECT I.deptCode, AVG(I.Salary)
FROM instructor I INNER JOIN department D
ON I.deptCode = D.deptCode
GROUP BY I.deptCode;

6.  GET list OF ALL junior, senior AND graduating senior CSCE students (classes  3, 4, AND 5)
SELECT *
FROM student S INNER JOIN Student_Major SM
ON S.studentId = SM.studentId
WHERE (S.Class = 3 OR S.Class = 4 OR S.Class = 5) AND SM.majorDeptCode = 'CSCE';

7.  GET Id AND name OF ALL students whose birthdate IS missing.
SELECT studentId, Fname, Lname
FROM student
WHERE Bdate IS NULL;

8.  GET Id AND name OF ALL students who were born IN 1999.
SELECT studentId, Fname, Lname
FROM student
WHERE Bdate BETWEEN '1999-01-01' AND '1999-12-31';

9.  GET a list OF ALL senior students whose FIRST name contains ‘sh’.
SELECT *
FROM student
WHERE Class = 4 AND (Fname LIKE '%sh%' OR  Fname LIKE '%sh' OR  Fname LIKE 'sh%');

10. GET a list OF ALL senior students whose FIRST names START WITH ‘Moh’ AND LAST names END BY ‘sh’.
SELECT *
FROM student
WHERE Class = 4 AND Fname LIKE 'Moh%' AND Lname LIKE '%sh';

11. GET Id AND name OF ALL students who were born IN January.
SELECT studentId, Fname, Lname, Bdate
FROM student
WHERE Bdate LIKE '_____01___';


12. GET Id AND name OF ALL students who were born ON the 15th OF March.
SELECT studentId, Fname, Lname, Bdate
FROM student
WHERE Bdate LIKE '_____03-15';

13. Define a VIEW TO GET age OF each VIEW. USE the defined VIEW TO GET a list OF ALL male CSCE students whose age exceeded 21 years.
14. Display ALL CSCE sections OF Fall 2019 (CSCE IS a department code)
SELECT *
FROM SECTION s
WHERE s.deptCode = 'CSCE' AND s.Semester = 'Fall' AND s.YEAR = 2019;

15. GET a list OF ALL sections OF Spring 2020 which are NOT assigned yet an Instructor.
SELECT *
FROM SECTION s
WHERE s.InstId IS NULL AND s.Semester = 'Spring' AND s.YEAR = 2020;

16. GET list OF ALL junior, senior AND graduating senior Computer Engineering students (classes  3, 4, AND 5)
SELECT s.studentId, Fname, Lname, Gender, Bdate, Class, deptName
FROM student S INNER JOIN Student_Major SM
ON S.studentId = SM.studentId
INNER JOIN department d
ON d.deptCode = SM.majorDeptCode
WHERE (S.Class = 3 OR S.Class = 4 OR S.Class = 5) AND d.deptName = 'Computer Engineering';

17. GET a list OF ALL Computer Engineering students HAVING more than one minor.
18. GET the total NUMBER OF students IN each major.
SELECT COUNT(*), majorDeptCode
FROM Student_Major
GROUP BY majorDeptCode;

19. GET a list OF ALL majors HAVING no students.
SELECT majorDeptCode
FROM Student_Major
GROUP BY majorDeptCode
HAVING COUNT(*) = 0;

20. GET a list OF ALL Electronics Engineering students HAVING DOUBLE major.
SELECT S.studentId, COUNT(*) AS DOUBLE_MAJORS
FROM Student_Major SM INNER JOIN student S
ON S.studentId = SM.studentId
GROUP BY S.studentId
HAVING COUNT(s.studentId) = 2;

21. Define a VIEW TO include the schedule OF each instructor IN each semester. USE the defined VIEW TO dispay the teaching schedule OF Dr. Khalil FOR the Fall 2019 semester. Also, USE the same defined VIEW TO GET a list OF ALL sections that are taught BY “Khalil” IN 2018, AND 2019.
22. GET a list OF ALL students HAVING DOUBLE major IN Computer Engineering AND Electronics Engineering.
23. GET a list OF ALL courses HAVING no prerequisite courses.
24. Retrieve names OF ALL Computer Science Faculty who are NOT teaching any course IN Fall 2019. WRITE the query USING: a) OUTER JOIN b) Subquery WITH “IN” OR “NOT IN” operator c) Subquery query USING “EXISTS” OR “NOT EXISTS” d) SET operator “Minus”
25. Retrieve names OF ALL majors HAVING a NUMBER OF students less than 5.
26. Define a VIEW TO include the total NUMBER OF students IN each grade FOR each SECTION IN years 2018 AND 2019. USE the VIEW TO GET the total NUMBER od students scoring an ‘A’ FOR ALL sections OF 2019.
27. FOR each course GET the course name AND the total NUMBER OF its prerequisite courses.
28. Define a VIEW TO include the total NUMBER OF taken courses AND the total NUMBER OF achieved credit hours OF each student IN each semester. USE the defined VIEW TO GET a list OF ALL graduating senior CSCE students WITH the total NUMBER OF achieved credit hours.
29. FOR each SECTION IN 2017, 2018 AND 2019, display the SECTION identifying attributes, SECTION’s instructor name AND the total name OF students IN each grade.
30. GET each department that doesn’t have any faculty. WRITE the query USING: a) OUTER JOIN b) Subquery WITH “IN” OR “NOT IN” operator c) Subquery query USING “EXISTS” OR “NOT EXISTS” d) SET operator “Minus”.