题目如下: 1.查询sC表中的全部数据。 代码如下: -- 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;
|
|