首先创建三个表,student,course和sc表,
student表包括Sname,Sno,Ssex,Sdept,Sage;
course表包括Cno,Cname,Credit,Cpno;
sc表包括Sno,Cno,Grade;
查询全体学生学号和姓名:
SELECT Sno,Sname FROM student;
查询学生全部信息:
SELECT * FROM student;
查询选修的学生学号:
SELECT Sno FROM sc;
查询不及格学生学号:
SELECT Sno
FROM sc
WHERE Grade<=60;
查询不是is和cs的学生性别,年龄,系别:
SELECT Ssex,Sage,Sdept
FROM student
WHERE Sdept NOT IN('is','cs');
查询选修4号课程学号,成绩,降序:
SELECT Sno,Grade
FROM sc
WHERE Cno='004' ORDER BY Grade DESC;
查询选修课对应的选修人数:
SELECT Cno,COUNT( Cno)
FROM sc
GROUP BY Cno;
查询计算机系学生信息:
SELECT Sname,Sage,Sdept
FROM student
WHERE Sdept='cs';
查询18-20学生的信息:
SELECT Sno,Sname,Sdept,Sage
FROM student
WHERE Sage>=18 AND Sage<=20;
查询姓刘学生的信息:
SELECT *
FROM student
WHERE Sname LIKE '刘%';
同时选修1和2的学生学号:
SELECT Sno
FROM sc
WHERE Cno='001' AND Sno IN (SELECT Sno FROM sc WHERE Cno=’002’);
查询学生姓名和出生年份:
SELECT Sname,(2003 - Sage)AS BIRTHDATE
FROM student
WHERE (2003 - Sage);
查询没有成绩的学生学号课程号:
SELECT Sno,Cno
FROM sc
WHERE Grade IS NULL;
总成绩大于200的学生:
SELECT Sno,SUM(Grade)
FROM sc
GROUP BY Sno HAVING SUM(Grade)>200;
求每门课程不及格人数:
SELECT Cno,COUNT( Cno )
FROM sc
WHERE Grade<60
GROUP BY Cno;
查询不及格门数超过3门的学生学号:
SELECT Sno,COUNT(*)
FROM sc
WHERE Grade<60
GROUP BY Sno HAVING COUNT(*)>3;
查询年龄在10-19的学生信息:
SELECT *
FROM student
WHERE Sage>=10 AND Sage<=19;
查询全体学生情况,按所在系升序排列,同一个系学生按年龄降序排列:
SELECT *
FROM student
ORDER BY Sdept ASC,Sage DESC;
查询选1号课程的学生平均成绩:
SELECT AVG(Grade)
FROM sc
WHERE Cno='001';
查询选3号课程学生的最高分:
SELECT MAX(Grade)
FROM sc
WHERE Cno='003';
查询每个同学的总成绩:
SELECT Sno,SUM(Grade)
FROM sc
GROUP BY Sno;
SELECT Sno,Sname FROM student;
查询学生全部信息:
SELECT * FROM student;
查询选修的学生学号:
SELECT Sno FROM sc;
查询不及格学生学号:
SELECT Sno
FROM sc
WHERE Grade<=60;
查询不是is和cs的学生性别,年龄,系别:
SELECT Ssex,Sage,Sdept
FROM student
WHERE Sdept NOT IN('is','cs');
查询选修4号课程学号,成绩,降序:
SELECT Sno,Grade
FROM sc
WHERE Cno='004' ORDER BY Grade DESC;
查询选修课对应的选修人数:
SELECT Cno,COUNT( Cno)
FROM sc
GROUP BY Cno;
查询计算机系学生信息:
SELECT Sname,Sage,Sdept
FROM student
WHERE Sdept='cs';
查询18-20学生的信息:
SELECT Sno,Sname,Sdept,Sage
FROM student
WHERE Sage>=18 AND Sage<=20;
查询姓刘学生的信息:
SELECT *
FROM student
WHERE Sname LIKE '刘%';
同时选修1和2的学生学号:
SELECT Sno
FROM sc
WHERE Cno='001' AND Sno IN (SELECT Sno FROM sc WHERE Cno=’002’);
查询学生姓名和出生年份:
SELECT Sname,(2003 - Sage)AS BIRTHDATE
FROM student
WHERE (2003 - Sage);
查询没有成绩的学生学号课程号:
SELECT Sno,Cno
FROM sc
WHERE Grade IS NULL;
总成绩大于200的学生:
SELECT Sno,SUM(Grade)
FROM sc
GROUP BY Sno HAVING SUM(Grade)>200;
求每门课程不及格人数:
SELECT Cno,COUNT( Cno )
FROM sc
WHERE Grade<60
GROUP BY Cno;
查询不及格门数超过3门的学生学号:
SELECT Sno,COUNT(*)
FROM sc
WHERE Grade<60
GROUP BY Sno HAVING COUNT(*)>3;
查询年龄在10-19的学生信息:
SELECT *
FROM student
WHERE Sage>=10 AND Sage<=19;
查询全体学生情况,按所在系升序排列,同一个系学生按年龄降序排列:
SELECT *
FROM student
ORDER BY Sdept ASC,Sage DESC;
查询选1号课程的学生平均成绩:
SELECT AVG(Grade)
FROM sc
WHERE Cno='001';
查询选3号课程学生的最高分:
SELECT MAX(Grade)
FROM sc
WHERE Cno='003';
查询每个同学的总成绩:
SELECT Sno,SUM(Grade)
FROM sc
GROUP BY Sno;