复习SQL基础

下面是初始化三张表结构和数据，写出对应条件的SQL：



-- ----------------------------

-- Table structure for course

-- ----------------------------

DROP TABLE IF EXISTS course;

CREATE TABLE course (

  cno varchar(10) NOT NULL DEFAULT '',

  cname varchar(20) DEFAULT NULL,

  hours int(4) DEFAULT NULL,

  PRIMARY KEY (cno)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO course VALUES ('c001', '计算机文化学', '70');

INSERT INTO course VALUES ('c002', 'VB', '90');

INSERT INTO course VALUES ('c003', '计算机网络', '80');

INSERT INTO course VALUES ('c004', '数据库基础', '108');

INSERT INTO course VALUES ('c005', '高等数学', '108');

INSERT INTO course VALUES ('c006', '数据结构', '72');



-- ----------------------------

-- Table structure for sc

-- ----------------------------

DROP TABLE IF EXISTS sc;

CREATE TABLE sc (

  sno varchar(10) NOT NULL DEFAULT '',

  cno varchar(10) NOT NULL DEFAULT '',

  grade int(4) DEFAULT NULL,

  PRIMARY KEY (sno,cno)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of sc

-- ----------------------------

INSERT INTO sc VALUES ('s001', 'c001', '78');

INSERT INTO sc VALUES ('s001', 'c002', '82');

INSERT INTO sc VALUES ('s001', 'c003', '59');

INSERT INTO sc VALUES ('s002', 'c001', '80');

INSERT INTO sc VALUES ('s002', 'c002', '72');

INSERT INTO sc VALUES ('s003', 'c001', '81');

INSERT INTO sc VALUES ('s003', 'c002', '81');

INSERT INTO sc VALUES ('s004', 'c001', '69');



-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS student;

CREATE TABLE student (

  sno varchar(10) NOT NULL,

  sname varchar(20) DEFAULT NULL,

  sage int(2) DEFAULT NULL,

  ssex varchar(5) DEFAULT NULL,

  sdept varchar(20) DEFAULT NULL,

  PRIMARY KEY (sno)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO student VALUES ('s001', '张三', '23', '男', '计算机系');

INSERT INTO student VALUES ('s002', '李四', '23', '男', '计算机系');

INSERT INTO student VALUES ('s003', '吴鹏', '25', '男', '计算机系');

INSERT INTO student VALUES ('s004', '琴沁', '20', '女', '计算机系');

INSERT INTO student VALUES ('s005', '王丽', '20', '女', '信息系');

INSERT INTO student VALUES ('s006', '李波', '21', '男', '信息系');

INSERT INTO student VALUES ('s007', '刘玉', '21', '男', '信息系');

INSERT INTO student VALUES ('s008', '萧蓉', '21', '女', '信息系');

INSERT INTO student VALUES ('s009', '陈萧晓', '23', '女', '数学系');

INSERT INTO student VALUES ('s010', '陈美', '22', '女', '数学系');



--1.分别查询学生表和学生修课表中的全部数据。

SELECT FROM student--查询学生表

SELECT FROM course--查询课程表



--2.查询成绩在70到80分之间的学生的学号、课程号和成绩。

SELECT Sno 学号,Cno 课程号,Grade 成绩

FROM SC

WHERE Grade BETWEEN 70 AND 80



--3．查询C01号课程成绩最高的分数

SELECT TOP 1 Grade 

FROM SC 

WHERE Cno='C01' 

ORDER BY Grade DESC--降序DESC，升序ASC



--4.查询学生都选修了哪些课程,要求列出课程号。

SELECT Cname AS 学生选修的课程,Cno AS 课程号

FROM course

WHERE Cno IN(SELECT DISTINCT Cno FROM SC)--DISTINCT用来去除重复



--5.查询Northwind数据库中orders表的OrderID、CustomerID和OrderDate,

--并将最新的定购日期(OrderDate)列在前边。

USE Northwind

GO

SELECT OrderID,CustomerID,OrderDate 

FROM orders 

ORDER BY OrderDate DESC--降序DESC，升序ASC



--6.查询Northwind数据库中orders表的ShipCountry列以B,C,D,F --//ShipCountry BCDF

--开始且第三个字符为"a"的OrderID、CustomerID和ShipCountry的信息。//OrderID、CustomerID第三个字符为"a"

USE Northwind

GO

SELECT OrderID,CustomerID,ShipCountry FROM orders 

WHERE ShipCountry LIKE '[BCDF]_a%'--_下划线表示任意一个字符，%表示一个或多个字符



--7.查询Northwind数据库中orders表的ShipCountry列不以A,B,C,D,E,F开始且最后一个字

--母是"a"的OrderID、CustomerID和ShipCountry的信息。

USE Northwind

GO

SELECT OrderID,CustomerID,ShipCountry 

FROM orders 

WHERE ShipCountry LIKE '[^ABCDEF]%a'



--8.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。

SELECT AVG(Grade) 平均成绩,MAX(Grade) 最高成绩,MIN(Grade) 最低成绩

FROM SC 

WHERE Cno='C02' 



--9.统计每个系的学生人数。

SELECT Sdept 系别,COUNT() 人数

FROM student 

WHERE Sdept IN(SELECT DISTINCT Sdept FROM student) 

GROUP BY Sdept



--10.统计每门课程的修课人数和考试最高分。

SELECT Cname 课程名,COUNT() 修课人数,MAX(Grade) 考试最高分

FROM SC,course

WHERE SC.Cno IN(SELECT DISTINCT Cno FROM SC ) AND course.Cno=SC.Cno

GROUP BY course.Cname



--11.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。

SELECT student.Sname 学生姓名,student.Sno 学生学号,COUNT(SC.Sno) 选课门数

FROM student 

inner join SC ON SC.Sno=student.Sno

GROUP BY student.Sname,student.Sno

ORDER BY COUNT(SC.Sno) ASC



--12.统计选修课的学生总数和考试的平均成绩。

SELECT COUNT(DISTINCT(Sno)) AS 学生总数,AVG(Grade) AS 平均成绩

FROM SC  --//用DISTINCT消除重复的行



--13.查询选课门数超过2门的学生的平均成绩和选课门数。

SELECT Student.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数

FROM SC 

--内联接join或inner join,内联系是比较运算符，只返回符合条件的行

    JOIN Student ON (SC.Sno = Student.Sno)

    JOIN Course ON (SC.Cno = Course.Cno)

GROUP BY Student.Sname

HAVING COUNT(distinct Course.Cno) >2--分组条件



--14.列出总成绩超过200分的学生,要求列出学号、总成绩。

SELECT Sno 学号,SUM(Grade) 总成绩

FROM SC

GROUP BY Sno

HAVING SUM(Grade)>200



--15.查询pubs数据库的titles表中每类图书的平均价格超过12.0元的书的类型(Type)、

--平均价格和最高价格。

USE pubs

GO

SELECT Type AS 书的类型,AVG(price) AS 平均价格,MAX(price) AS 最高价格

FROM titles

GROUP BY Type

HAVING AVG(price)>12.0



--16.查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。

USE pubs

GO

SELECT 图书类型=Type,图书的数目=count(Type),图书的总价格=SUM(price)

FROM titles

GROUP BY Type

HAVING count(Type)>3



--17.查询选修了c02号课程的学生的姓名和所在系。

SELECT Sname 学生姓名,Sdept 所在系,SC.Cno AS 选修课程

FROM student

inner join SC ON student.Sno=SC.Sno

WHERE SC.Cno='C02'



--18.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩

FROM student

inner join SC ON student.Sno=SC.Sno--内连接表SC查询

WHERE SC.Grade>80

ORDER BY SC.Grade DESC



--19.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。

SELECT Sname 姓名,Ssex 性别,SC.Grade 成绩

FROM student

inner join SC ON Cno IN(SELECT Cno FROM course WHERE Cname='数据库基础') --显示成绩的条件

AND student.Sno=SC.Sno --显示成绩的学生的学号

WHERE Sdept='计算机系' AND Ssex='男' 



--20.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

--这题使用到了表的自连接，所以需要给表取两个别名，如A和B

SELECT A.Sname 年龄相同的学生的姓名,A.Sage 年龄

FROM student A 

inner join student B ON A.Sage IN(SELECT Sage FROM student WHERE A.Sage=B.Sage AND A.Sname!=B.Sname)

GROUP BY A.Sname,A.Sage

ORDER BY A.Sage



--21.查询哪些课程没有人选,要求列出课程号和课程名。

SELECT Cno AS 课程号,Cname AS 课程名

FROM course

WHERE Cno NOT IN(SELECT DISTINCT SC.Cno FROM SC)



--22.查询有考试成绩的所有学生的姓名、修课名称及考试成绩

--要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。

SELECT student.Sname AS 有考试成绩的学生的姓名,course.Cname AS 修课名称,SC.Grade AS 考试成绩

INTO [new_sc] --将查询结果放入新表new_sc中

FROM student,course,SC

WHERE SC.Grade IS NOT NULL AND student.Sno=SC.Sno AND course.Cno=SC.Cno



--23.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,

--并要求将这两个查询结果合并成一个结果集,

--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

--//此题用到了并union查询

SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩

FROM student 

inner join SC     ON student.Sno=SC.Sno

inner join course ON course.Cno=SC.Cno

WHERE Sdept='信息系'

UNION 

SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩

FROM student 

inner join SC     ON student.Sno=SC.Sno

inner join course ON course.Cno=SC.Cno

WHERE Sdept='计算机系'

或

select sdept, sname,ssex,cname,grade from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno and (sdept='信息系' or sdept='计算机系')



--24.用子查询实现如下查询:

--(1) 查询选修了C01号课程的学生的姓名和所在系。

SELECT Sname AS 选修了C01号课程的学生的姓名,Sdept AS 所在系

FROM student

WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='C01')



--(2)查询数学系成绩80分以上的学生的学号、姓名。

SELECT Sno AS 数学系成绩80分以上的学生的学号,Sname AS 姓名

FROM student

WHERE Sno IN(SELECT Sno FROM SC WHERE Grade>80) 

AND   Sno IN(SELECT Sno FROM student WHERE Sdept='数学系')



--(3)    查询计算机系学生所选的课程名.

SELECT Cname AS 计算机系学生所选的课程名

FROM course

WHERE 

course.Cno IN(SELECT DISTINCT Cno FROM SC WHERE SC.Sno IN(SELECT Sno FROM student WHERE Sdept='计算机系'))



--25.将计算机系成绩高于80分的学生的修课情况插入到另一张表中,分两种情况实现：//////////////?????/

--(1)    在插入数据过程中建表。

--使用SELECT INTO插入数据的方法，是在插入数据的过程中建立新表

SELECT student.Sname AS 学生名字,course.Cname AS 选修课程,SC.Cno AS 课程编号

INTO [SC_Info1]--将计算机系成绩高于80分的学生的修课情况插入到表SC_Info1中

FROM student,course,SC

WHERE student.Sdept='计算机系' AND SC.Grade>80 AND course.Cno=SC.Cno AND student.Sno=SC.Sno



--(2)先建一个新表,然后再插入数据。

--创建表SC_Info2

CREATE TABLE SC_Info2 (

    Sname char(7),

    Cname char(20),

    Cno   char(10)

)



--往表SC_info2插入查询得到的结果

INSERT SC_Info2

SELECT student.Sname AS 学生名字,course.Cname AS 选修课程,SC.Cno AS 课程编号

FROM student,course,SC

WHERE student.Sdept='计算机系' AND SC.Grade>80 AND course.Cno=SC.Cno AND student.Sno=SC.Sno



--26.删除修课成绩小于50分的学生的修课记录

DELETE SC WHERE Grade<50 OR Grade IS NULL



--27.将所有选修了"c01"课程的学生的成绩加10分。

UPDATE SC 

SET Grade=Grade+10

WHERE Cno='C01'