3.11 Write the following queriesin SQL, using the university schema. a. Find the

Question

3.11 Write the following queriesin SQL, using the university schema.

a. Find the names of all students who have taken at least one Comp.Sci. course; make sure there are no duplicate names in the result.

b. Find the IDs and names of all students who have not taken any course offering before Spring 2009.

c. For each department, nd the maximum salary of instructors in that department.You may assume that every department has at least one instructor.

d. Find the lowest,across all departments,of the per-department maximum salary computed by the preceding query.

Explanation / Answer

If you have any doubts, please give me comment...

a)

SELECT name

FROM student NATURAL JOIN takes NATURAL JOIN course

WHERE course.dept = 'Comp. Sci.';

b)

SELECT id, name

FROM student

EXCEPT(

SELECT id, name

FROM student NATURAL JOIN takes

WHERE year<2009);

c)

SELECT dept_name, MAX(salary)

FROM instructor

GROUP BY dept_name;

d)

SELECT MIN(maximum_salary)

FROM (

SELECT dept_name, MAX(salary) maximum_salary

FROM instructor

GROUP BY dept_name

);

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

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