.分别查询学⽣表和学⽣修课表中的全部数据。
标题 .分别查询学⽣表和学⽣修课表中的全部数据。
–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=‘计算机系’
distinct查询或
select sdept, sname,ssex,cname,grade from student,sc,course
where student.sno=sc.sno and sco=courseo 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="">
–27.将所有选修了’c01’课程的学⽣的成绩加10分。
UPDATE SC
SET Grade=Grade+10
WHERE Cno=‘C01’

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。