彭文志 db 2013 porject 3 demo 題目

db proj 3 ans

# 1
彭文志老師在101學年度總共開了幾門課?

SELECT COUNT(C.id) FROM Course C, Professor P WHERE P.name = "彭文志" AND P.id = C.pro_id

# 2
資訊工程學系在上學期有幾門不同的課? (課名相同而老師不同視為相同)

SELECT COUNT(DISTINCT C.name) FROM Course C, Department D WHERE D.name = "資訊工程學系" AND D.id = C.department AND C.year = '1'


# 5
課程人數上限總和最多的


SELECT P.name, SUM( C.student_upper_bound ) AS num
FROM Course C, Professor P
WHERE P.id = C.pro_id
GROUP BY C.pro_id
ORDER BY num DESC 
LIMIT 0 , 1



# 6
上學期開課數最多的

create view gg as SELECT P.name, COUNT( C.id ) AS num
FROM Course C, Professor P
WHERE P.id = C.pro_id
AND C.year =  '1'
GROUP BY C.pro_id
ORDER BY num DESC

SELECT * 
FROM gg
WHERE num = ( 
SELECT MAX( num ) 
FROM  `gg` 
WHERE 1 )