学生表查询

表数据

1001,李菲,1,语文,86
1001,李菲,2,数学,56
1001,李菲,3,物理,48
1001,李菲,4,化学,90
1001,李菲,5,英语,57
1002,王琪,1,语文,90
1002,王琪,2,数学,68
1002,王琪,3,物理,87
1002,王琪,4,化学,68
1002,王琪,5,英语,45
1003,杨阳,1,语文,86
1003,杨阳,2,数学,56
1003,杨阳,3,物理,48
1003,杨阳,4,化学,90
1003,杨阳,5,英语,52
1004,和树,1,语文,82
1004,和树,2,数学,56
1004,和树,3,物理,67
1004,和树,4,化学,30
1004,和树,5,英语,68

CREATE TABLE db_grade(
Sno INT,
Sname NVARCHAR(30),
Cno INT ,
Cname NVARCHAR(30),
score INT
)
DROP TABLE db_grade
INSERT INTO db_grade VALUES (1001,'李菲',1,'语文',86)
INSERT INTO db_grade VALUES (1001,'李菲',2,'数学',56)
INSERT INTO db_grade VALUES (1001,'李菲',3,'物理',48)
INSERT INTO db_grade VALUES (1001,'李菲',4,'化学',90)
INSERT INTO db_grade VALUES (1001,'李菲',5,'英语',57)
INSERT INTO db_grade VALUES (1002,'王琪',1,'语文',90)
INSERT INTO db_grade VALUES (1002,'王琪',2,'数学',68)
INSERT INTO db_grade VALUES (1002,'王琪',3,'物理',87)
INSERT INTO db_grade VALUES (1002,'王琪',4,'化学',68)
INSERT INTO db_grade VALUES (1002,'王琪',5,'英语',45)
INSERT INTO db_grade VALUES (1003,'杨阳',1,'语文',86)
INSERT INTO db_grade VALUES (1003,'杨阳',2,'数学',56)
INSERT INTO db_grade VALUES (1003,'杨阳',3,'物理',48)
INSERT INTO db_grade VALUES (1003,'杨阳',4,'化学',90)
INSERT INTO db_grade VALUES (1003,'杨阳',5,'英语',52)
INSERT INTO db_grade VALUES (1004,'和树',1,'语文',82)
INSERT INTO db_grade VALUES (1004,'和树',2,'数学',56)
INSERT INTO db_grade VALUES (1004,'和树',3,'物理',67)
INSERT INTO db_grade VALUES (1004,'和树',4,'化学',30)
INSERT INTO db_grade VALUES (1004,'和树',5,'英语',68)

# 1、查询不及格科目数大于等于2的学生学号和不及格科目数量：
SELECT Sno,COUNT(score) 不及格科目 FROM db_grade WHERE score<60 GROUP  BY Sno HAVING COUNT(score)>=2

#2、查询不及格科目数大于等于2的学生学号和学生姓名：
SELECT Sno 学号, Sname 姓名 FROM db_grade  WHERE score<60 GROUP BY Sno HAVING COUNT(score)>=2

#3、查询不及格科目数大于等于2的学生学号、学生姓名、科目号、科目名称和分数，并按学号降序、科目号升序排序：
SELECT * FROM db_grade ORDER BY Cno DESC
SELECT * FROM db_grade WHERE score<60 GROUP BY Sno HAVING COUNT(score)>=2 ORDER BY Sno DESC,Cno ASC

多表查询

CREATE TABLE class(
classID INT PRIMARY KEY,
className NVARCHAR(30)
)

CREATE TABLE student(
stuID INT PRIMARY KEY,
classID INT,
stuName NVARCHAR(30)
)

DROP TABLE score

CREATE TABLE score(
courseID INT ,
stuID INT,
course NVARCHAR(30),
score INT
)

INSERT INTO class VALUES (1,'一班')
INSERT INTO class VALUES (2,'二班')
INSERT INTO class VALUES (3,'三班')

INSERT INTO student VALUES (1001,1,'张三')
INSERT INTO student VALUES (1002,1,'李丽')
INSERT INTO student VALUES (1003,1,'钱封')
INSERT INTO student VALUES (1004,2,'杨国')
INSERT INTO student VALUES (1005,2,'小样')
INSERT INTO student VALUES (1006,2,'区天')
INSERT INTO student VALUES (1007,3,'李三宅')
INSERT INTO student VALUES (1008,3,'黄武')
INSERT INTO student VALUES (1009,3,'赵六')

INSERT INTO score VALUES (2,1001,'数学',73)

INSERT INTO score VALUES (3,1001,'英语',79)
INSERT INTO score VALUES (1,1001,'语文',81)
INSERT INTO score VALUES (3,1002,'英语',87)
INSERT INTO score VALUES (2,1002,'数学',83)
INSERT INTO score VALUES (1,1002,'语文',79)
INSERT INTO score VALUES (1,1003,'语文',65)
INSERT INTO score VALUES (3,1003,'英语',65)
INSERT INTO score VALUES (2,1003,'数学',97)
INSERT INTO score VALUES (1,1004,'语文',78)
INSERT INTO score VALUES (3,1004,'英语',78)
INSERT INTO score VALUES (2,1004,'数学',86)
INSERT INTO score VALUES (1,1005,'语文',67)
INSERT INTO score VALUES (3,1005,'英语',88)
INSERT INTO score VALUES (2,1005,'数学',89)
INSERT INTO score VALUES (2,1006,'数学',90)
INSERT INTO score VALUES (3,1006,'英语',92)
INSERT INTO score VALUES (1,1006,'语文',98)
INSERT INTO score VALUES (1,1007,'语文',85)
INSERT INTO score VALUES (2,1007,'数学',78)
INSERT INTO score VALUES (3,1007,'英语',72)
INSERT INTO score VALUES (1,1008,'语文',78)
INSERT INTO score VALUES (3,1008,'英语',77)
INSERT INTO score VALUES (2,1008,'数学',85)
INSERT INTO score VALUES (3,1009,'英语',94)
INSERT INTO score VALUES (2,1009,'数学',91)
INSERT INTO score VALUES (1,1009,'语文',68)

#1、查询各班各科分数最高的学生学号，姓名，班级名称，科目名称，分数：

SELECT st.stuID,st.stuName, ca.className,sc.course,sc.score
FROM class ca,student st,score sc
WHERE ca.classID=st.classID
AND st.stuID=sc.stuID
ORDER BY ca.className,sc.score DESC

#select score,max(sum) from score group by score

#SELECT MAX(score) AS LargestOrderPrice FROM score s,class c where s.courseID=c.classID

#SELECT * from score sc, student st,class c where sc.stuID=st.stuID and st.classID=c.classID