1. (10 pts each) Formulate a SQL query for each of the following questions. The

Question

1. (10 pts each) Formulate a SQL query for each of the following questions. The prefix […], if any, in front of a question specifies limitations which the SQL query must be constrained by. For each of the questions, your answer must include the SQL query (input, text) and the corresponding screenshot of the result set (query output).

a. [Nested query without JOIN] List the unique names of instructors whose name’s second character is ‘o’ and who have a higher salary than some instructor in the ‘CMPE’ department. 2

b. [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 2010 semester. [NOTE: department.dept_name is unique.]

c. [single SELECT with JOIN] For course sections with more than 1 student in some semester in 2010, list the course title (in descending order), section id, semester, and number of students. [NOTE: course.title is not unique.]

The enclosed.sql file defines the following university database where primary key for each table is underlined department.dept_name is primary key (i.e., unique) course.title is not unique instructor.name is not globally unique but is unique within the department student.name is not globally unique but is unique within the department student takes ID ID name dept name tot cred section course department advisor title dept name credits s id i id semester building budget time slot ilding room no time slot id end time instructor classroom ID ame dept name teaches ID sec id ear capacity salary

Explanation / Answer

a) SELECT ID, `name` FROM instructor WHERE `name` LIKE '_o%' AND salary > ANY(SELECT salary FROM instructor WHERE dept_name ='CMPE');

b) SELECT d.dept_name, COUNT(*) FROM (department d NATURAL JOIN instructor i) NATURAL JOIN teaches t WHERE t.semester = 'Spring' AND t.year =2010 GROUP BY d.dept_name, i.name ORDER BY dept_name DESC;

c) SELECT c.title, s.section_id, s.semester, COUNT(*) AS no_of_stud FROM (course c NATURAL JOIN section s) NATURAL JOIN takes t WHERE s.year = 2010 GROUP BY c.title, s.section_id, s.semester ORDER BY c.title DESC;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.