计算机科学与信息学院软件工程系上机实验报告
(2)求该校的所有系名。
SELECT DISTINCT Sdept FROM Student;
(3)求计科系男生的学号和姓名。
SELECT Sno,Sname FROM Student WHERE Ssex=’男’ and Sdept=’CS’;
(4)求年龄在19-21岁(包含19岁和21岁)之间的学生姓名和所在系。
SLELCT Sno,Sdept FROM Student WHERE Sage BETWEEN 19 AND 21;
(5)求存在先行课的课程的课程名和学分。
SELECT Cname,Ccredit FROM Course WHERE Cpno IS not NULL;
(6)求数学系或者信息系的学生姓名。
SELECT Sname FROM Student where Sdept in('MA','IS');
(7)求以“数据”开头的所有课程的课程名和学分。SELECT Cname,Ccredit FROM Course WHERE Cname LIKE'数据%';
(8)求课程DB_Design的学分。
SELECT Ccredit FROM Course WHERE Cname LIKE'DB_Design';
(9)求缺考学生的学号和课程号。
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
(10)求选修了2号课程学生的学号和成绩。SELECT Sno,Grade FROM SC WHERE Cno='2';
(11)求计科系老师的姓名和工资。
SELECT Tname,Twage FROM Teacher WHERE Tdept='CS';
(12)求姓王的老师的姓名和所在系。
SELECT Tname,Taddress FROM Teacher WHERE Tname LIKE'王%';
(13)求工资在2500-3500之间的老师姓名。SELECT Tname
FROM Teacher
where Twage BETWEEN 2500 AND 3500;
(14)求外语系所有副教授的姓名和年龄。SELECT Tname,Tage
FROM Teacher
where Tdept='FL'AND Ttitles LIKE'副教授';
(15)求所有女老师的姓名、年龄和所在系。SELECT Tname,Tage,Tdept
FROM Teacher
WHERE Tsex='女';
(16)求学生的总人数。
SELECT COUNT(*)as'学生总人数'
FROM Student;
(17)求选修了课程的学生人数。
SELECT Cno,COUNT(Sno)as'选修该课程的学生人数' FROM SC
GROUP BY Cno ;
(18)求课程号和选修了该课程的学生人数。SELECT COUNT(DISTINCT Sno)as'选修课程的学生人数' FROM SC;
(19)求教师人数超过4人的系名。
SELECT Tdept as'教师人数超过4人的系'
FROM Teacher
GROUP BY Tdept
HAVING COUNT(*)>4;
(20)求学生的学号及其选修课程的总成绩。SELECT Sno,sum(Grade)as'总成绩'
FROM SC
GROUP BY Sno;