SQL复习

晚上复习了一下CC（Cracking the Coding Interview）的SQL一章，明天早上准备看看OOD。

假设数据库如下，*表示key，primary key和foreign key
Courses: CourseID*, CourseName, TeacherID
Teachers: TeacherID*, TeacherName
Students: StudentID*, StudentName
StudentCourses: CourseID*, StudentID*

下面是例子的两个联系
Query 1: Student Enrollment
get a list of all students and how many courses each student is enrolled in.

SELECT Students.StudentID, count(StudentCourses.CourseID) AS [Cnt]
FROM Students LEFT JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID

#1：这个查询语句能够保证，哪怕某些学生没有选课，那些学生也能够存在LEFT JOIN之后的表格里面。
#2：而且COUNT(StudentCourses.CourseID)才是正确的count，如果写count(*)或者count(StudentID)都会把那个没有注册的学生当作1.

使用嵌套之后完整的query是：

SELECT StudentName, Students.StudentID, Cnt
FROM (
	SELECT Students.StudentID, count(StudentCourses.CourseID) AS [Cnt]
	FROM Students LEFT JOIN StudentCourses
	ON Students.StudentID = StudentCourses.StudentID
	GROUP BY Students.StudentID
) T INNER JOIN Students ON T.StudentsID = Students.StudentID

Query 2: Teacher class size
a list of all teachers and how many students they each teach

SELECT TeacherID, count(StudentCourses.CourseID) AS [Number]
FROM Courses INNER JOIN StudentCourses
ON Courses.CourseID = StudentCourses.CourseID
GROUP BY Courses.TeacherID

#1 这个语句选出teacherId以及对应教的学生数目（这里我觉得可以count(*)）

SELECT TeacherName, isNull(StudentSize.Number, 0)
FROM Teachers LEFT JOIN (
	SELECT TeacherID, count(StudentCourses.CourseID) AS [Number]
	FROM Courses INNER JOIN StudentCourses
	ON Courses.CourseID = StudentCourses.CourseID
	GROUP BY Courses.TeacherID
) StudentSize
ON Teachers.TeacherID = StudentSize.TeacherID
ORDER BY StudentSize.Number DESC

CC里面的习题：

数据库如下：
Apartments: AptID, UnitNumber, BuildingID
Buildings: BuildingID, ComplexID, BuildingName, Address
Tenants: TenantID, TenantName
Complexes: ComplexID, ComplexName
AptTenants: TenantID, AptID
Requests: RequestID, Status, AptID, Description


#1 get a list of tenants who are renting more than one apt

SELECT TenantName
FROM Tenants INNER JOIN (
	SELECT TenantID
	FROM AptTenants
	GROUP BY TenantID
	HAVING count(AptID) > 1
) T
ON Tenants.TenantID = T.TenantID

使用having语句的时候记得用group by


#2 get a list of all buildings and # of open requests

SELECT BuildingName, isNull(T.Cnt, 0) As [Cnt]
FROM Buildings LEFT JOIN (
	SELECT BuildingID, count(*) AS [Cnt]
	FROM Apartments INNER JOIN Requests
	ON Apartments.AptID = Requests.AptID
	GROUP BY BuildingID
	WHERE Status = 'Open'
) T 
ON Buildings.BuildingID = T.BuildingID

注意这里是group by buildingID，而并非aptID


#3 close all requests from apartments in building #11

UPDATE Requests 
SET Status = 'close'
WHERE AptID IN (
	SELECT AptID
	FROM Apartments
	WHERE BuildingID = 11
)