SQL语句总结

建表语句

CREATE TABLE students
(sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5))

CREATE TABLE courses
(cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL)

CREATE TABLE scores
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL)

CREATE TABLE teachers
(tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL, tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL, prof VARCHAR(6),
depart VARCHAR(10) NOT NULL)

3.查询Score表中的最高分的学生学号和课程号。

SELECT Sno,Cno
FROM Scores
ORDER BY Degree DESC
LIMIT 1;

4.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT Cno,AVG(Degree)
FROM Scores
WHERE Cno LIKE '3%'
GROUP BY Cno
HAVING COUNT(Sno) >= 5;

7.查询“95033”班所选课程的平均分。

SELECT Cname,AVG(Degree)
FROM Students INNER JOIN Scores
ON(Students.Sno=Scores.Sno) INNER JOIN Courses
ON(Scores.Cno=Courses.Cno)
WHERE Class='95033'
GROUP BY Courses.Cno
ORDER BY Cname;

假设使用如下命令建立了一个grade表：

CREATE TABLE grade(low TINYINT,upp TINYINT,rank CHAR(1));
INSERT INTO grade VALUES(90,100,’A’);
INSERT INTO grade VALUES(80,89,’B’);
INSERT INTO grade VALUES(70,79,’C’);
INSERT INTO grade VALUES(60,69,’D’);
INSERT INTO grade VALUES(0,59,’E’);

8.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT s1.Sno,s1.Degree
FROM Scores AS s1 INNER JOIN Scores AS s2
ON(s1.Cno=s2.Cno AND