SQL

04-21

sgd

1. Find the names of all instructors in Comp Sci and, if they taught a course in Autumn

2009, give the course id and sec id.

SELECT A2, A4, A5FROM ( SELECT ID AS A1, NAME AS A2 FROM instructor WHERE dept_name = 『Comp.Sci.』) AS T1LEFT OUTER JOIN( SELECT ID AS A3, course_id AS A4, sec_id AS A5 FROM teaches WHERE semester = 『Autumn』 AND YEAR = 2009) AS T2ON (A1 = A3)

1. Find the maximum enrolment, across all sections, in Spring

2009.

SELECT MAX(enrolmt) AS maxEnrolmtFROM ( SELECT course_id, sec_id, COUNT(*) AS enrolmt FROM takes WHERE semester = Spring AND YEAR = 2009 GROUP BY course_id, sec_id) AS T1;

asdgfds

5. Find all instructors earning the highest salary (there may be more than one with the same salary).

SELECT ID, NAMEFROM instructorWHERE salary IN( SELECT MAX(salary) FROM instructor);

Find the enrollment of each section that was offered in Spring 2009

SELECT course_id, sec_id, COUNT(*) AS enrollFROM takesWHERE semester = Spring AND YEAR = 2009GROUP BY course_id, sec_id;

lists the number of customers in each country:

SELECT COUNT(CustomerID), CountryFROM CustomersGROUP BY Country;

1. SELECT max(Salary) as maxSalary

FROM instructor;

SELECT MAX(Salary) AS maxSalaryFROM instructor;