記錄一下無聊的資料庫作業

題目如下:

1.查詢sC表中的全部數據。
2. 查詢電腦系學生的姓名和年齡
3.查詢成績在70~80分的學生的學號、課程號和成績
4.查詢電腦系年齡在18~20歲的男生姓名和年齡
s.查詢C001課程的最高分
6.查詢電腦系學生的最大年齡和最小年齡
7.統計每個系的學生人數
8.統計每]課程的選課人數和最高成績。
9.統計每個學生的選課門數和考試總成績，並按選課]數升序顯示結果。
10.列出總成績超過200的學生的學號和總成績
11.查詢選了C002課程的學生姓名和所在系
12.查詢考試成績80分以上的學生姓名、課程號和成績，並按成績降序排列結果
13.查詢與VB在同一學期開設的課程的課程名和開課學期
14.查詢與李勇年齡相同的學生的姓名、所在系和年齡
15.查詢哪些課程沒有學生選修，列出課程號和課程名
16.查詢每個學生的選課情況,包括未選課的學生，列出學生的學號、姓名、選的課程號
17.查詢電腦系哪些學生沒有選課，列出學生姓名
18.查詢電腦系年齡最大的三個學生的姓名和年齡
19.列出“VB"課程考試成績前三名的學生的學號、姓名、所在系和VB成績
20.查詢選課門]數最多的前2位學生，列出學號和選課門數

代碼如下:

-- 1
SELECT *
FROM SC;
-- 2
SELECT s.Sname, s.Sage
FROM Student s
WHERE s.Sdept = N'電腦系';
-- 3
SELECT sc.Sno, sc.Cno, sc.Grade
FROM SC sc
WHERE sc.Grade BETWEEN 70 and 80;
-- 4
SELECT s.Sname, s.Sage
FROM Student s
WHERE s.Sdept = N'電腦系'
  AND s.Sage in (18, 20)
  AND s.Ssex = N'男';
-- 5
SELECT MAX(sc.Grade) AS max_grade
FROM SC sc
GROUP BY sc.Cno
HAVING sc.Cno = 'C001';
-- 6
SELECT MAX(s.Sage) AS max_age, MIN(s.Sage) AS min_age
FROM Student s
GROUP BY s.Sdept
having s.Sdept = '電腦系';
-- 7
SELECT CONCAT(s.Sdept, ' : ', COUNT(s.Sno)) AS stu_nums
FROM Student s
GROUP BY s.Sdept;
-- 8
SELECT sc.Cno AS Cno, COUNT(sc.Sno) as c_nums, MAX(sc.Grade) as max_grade
FROM SC sc
GROUP BY sc.Cno;
-- 9
SELECT COUNT(sc.Cno) as c_nums, SUM(sc.Grade) as sum_grades
FROM SC sc
GROUP BY sc.Sno
ORDER BY c_nums;
-- 10
SELECT sc.Sno, SUM(sc.Grade) AS sum_grades
FROM SC sc
GROUP BY sc.Sno
Having SUM(sc.Grade) > 200;
-- 11
SELECT s.sname, s.Sdept
FROM SC sc
         inner join Student s
                    on sc.Cno = 'C002';
-- 12
SELECT s.Sname, sc.Cno, sc.Grade
FROM SC sc
         INNER JOIN Student s on sc.Sno = s.Sno
GROUP BY s.Sname, sc.Cno, sc.Grade
HAVING sc.Grade > 80
ORDER BY sc.Grade DESC;
-- 13
SELECT c.Cno, c.Semester
FROM Course c
WHERE c.Semester = (SELECT Semester FROM Course WHERE Cname = 'VB')
  AND c.Cname <> 'VB';
-- 14
SELECT s.Sname, s.Sdept, s.Sage
FROM Student s
WHERE s.Sage = (SELECT Sage FROM Student WHERE Sname = N'李勇')
  AND s.Sname <> N'李勇';
-- 15
SELECT c.Cno, c.Cname
FROM Course c
WHERE c.Cno not in (SELECT sc.Cno FROM SC sc);
--16
SELECT s.Sno,
       s.Sname,
       cno=STUFF((
                     SELECT ',' + TRIM(c.Cno)
                     FROM Course c,
                          SC sc1
                     WHERE s.Sno = sc1.Sno
                       AND sc1.Cno = c.Cno
                     FOR XML PATH ('')), 1, 1, '')
FROM SC sc
         RIGHT JOIN Student S on sc.Sno = S.Sno
GROUP BY s.Sno, s.Sname;
-- 17
SELECT s.Sname
FROM Student s
WHERE s.Sno not in (SELECT sc.Sno FROM SC sc);
-- 18
SELECT
TOP 3
s.sname
,
s.Sage
FROM Student s
WHERE s.Sdept = N'電腦系'
ORDER BY s.Sage;
-- 19
SELECT
TOP 3
s.sno
,
s.sname
,
s.Sdept
,
sc.Grade
FROM Course c
         INNER JOIN SC sc ON c.Cno = sc.Cno
         INNER JOIN Student s on sc.Sno = s.Sno
WHERE c.Cname = 'VB';
--20
SELECT
TOP 2
sc.Sno
,
COUNT(sc.Cno) AS course_nums
FROM SC sc
GROUP BY sc.Sno;