SQL IT & Computer Science Coursework Research Paper (Coursework Sample)

Databases - SQL
Student’s Name
Institution Affiliation
Question 1
* [Nested query without JOIN] List the unique names of instructors whose name’s the second character is ‘o’ and who have a higher salary than some instructor in the ‘EE’ department.
SELECT instructor.name FROM instructor WHERE instructor.name LIKE '_O%' AND instructor.salary > ( SELECT instructor.salary FROM instructor WHERE instructor.dept_name='EE');
* [single SELECT with JOIN] List the department name (in descending order), and total number of the department’s unique instructors who teach at least one course in the Spring 2018 semester.
SELECT instructor.dept_name,instructor.name,COUNT(instructor.ID) AS TOTAL FROM instructor INNER JOIN teaches ON instructor.ID=teaches.ID WHERE teaches.semester='Spring' AND teaches.year='2018' GROUP BY instructor.dept_name ORDER BY instructor.dept_name DESC;
* [single SELECT with JOIN] For course sections with more than 1 student in some semester in 2018, list the course title (in descending order), section id, semester, and number of students
SELECT course.title,section.sec_id,section.semester,course.credits as Number_of_students FROM course INNER JOIN section ON course.course_id=section.course_id WHERE section.year='2018' ORDER BY course.title DESC