SELECT *
--FROM STUDENTS
FROM CHOICES
--(1) 查询年级为2001的所有学生的名称并按编号升序排列。
SELECT sname
FROM STUDENTS
WHERE grade ='2001'
ORDER BY sid ASC
--(2) 查询学生的选课成绩合格的课程成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。
SELECT SCORE,(score-50)/10 +((score-50)%10)*0.1 as gpi
FROM CHOICES
WHERE score>60
--(3) 查询课时是48或64的课程的名称。
SELECT CNAME
FROM COURSES
WHERE hour IN (48,64)
--(4) 查询所有课程名称中含有data的课程编号。
SELECT CID
FROM COURSES
WHERE cname LIKE '%data%'
--(5) 查询所有选课记录的课程号（不重复显示）。
SELECT DISTINCT CID
FROM CHOICES
--(6) 统计所有教师的平均工资。
SELECT AVG(SALARY)
FROM TEACHERS
--***(7) 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。
SELECT TID,AVG(score) AS AVG_SOCRE
FROM CHOICES
GROUP BY tid
ORDER BY AVG(score) DESC
--***(8)统计各个课程的选课人数和平均成绩。
SELECT CID ,COUNT(*)AS NUM,AVG(score) AS AVG_SCORE
FROM CHOICES
GROUP BY CID
--(9) 查询至少选修了三门课程的学生编号。
SELECT SID --,COUNT(*) AS SC_NUM
FROM CHOICES
GROUP BY sid
HAVING COUNT(*)>3
--(10) 查询编号800009026的学生所选的全部课程的课程名和成绩。
SELECT CID,SCORE
FROM CHOICES
WHERE SID = '800009026'
--(11) 查询所有选修了database的学生的编号。
SELECT SID
FROM CHOICES
WHERE CID = (
SELECT CID
FROM COURSES
WHERE cname = 'DATABASE'
)
--(12) 求出选择了同一个课程的学生数。
SELECT CID, COUNT(*) AS S_NUM
FROM CHOICES
GROUP BY cid
--(13) 求出至少被两名学生选修的课程编号。
SELECT CID
FROM CHOICES
GROUP BY CID
HAVING COUNT(*)>2
--***(14) 查询选修了编号80009026的学生所选的某个课程的学生编号。
SELECT CID
FROM CHOICES
WHERE sid = (
SELECT sid
FROM CHOICES
WHERE CID = '80009026'
)
--(15) 查询学生的基本信息及选修课程编号和成绩。
SELECT STUDENTS.SID,SNAME,EMAIL,GRADE ,CID,score
FROM STUDENTS, CHOICES
--(16) 查询学号850955252的学生的姓名和选修的课程名及成绩。
SELECT STUDENTS.SNAME,CHOICES.cid,CHOICES.score
FROM STUDENTS,CHOICES
WHERE (STUDENTS.sid = CHOICES.sid)and(STUDENTS.sid = '850955252')
--(17) 查询与学号850955252的学生同年纪的所有学生资料。
SELECT SID,SNAME,EMAIL,GRADE
FROM STUDENTS
WHERE grade = (
SELECT grade
FROM STUDENTS
WHERE sid = '850955252'
)
--(18) 查询所有有选课的学生的详细信息。
SELECT STUDENTS.SID,STUDENTS.SNAME,STUDENTS.EMAIL,STUDENTS.GRADE
FROM STUDENTS
WHERE SID =ANY (
SELECT sid
FROM CHOICES
GROUP BY sid
HAVING COUNT(*)>0
)
--(19)***查询没有学生选的课程的编号。
SELECT CID ,count(*) as s_num
FROM CHOICES
group by cid
--(20) 查询选修了课程名为C++的课时一样课程名称。
select cname
from COURSES
where hour = (
select hour
from COURSES
where cname = 'C++'
)
--(21) 找出选修课程成绩最好的选课记录。
SELECT *
FROM CHOICES
WHERE SCORE = (
SELECT MAX(SCORE)
FROM CHOICES
)
--(22) 找出和课程UML或课程C++的课时一样课程名称。
SELECT CNAME
FROM COURSES
WHERE HOUR IN (
SELECT hour
FROM COURSES
WHERE cname = 'UML'OR cname= 'C++'
)
--(23) 查询所有选修编号10001的课程的学生的姓名。
SELECT sname
FROM STUDENTS
WHERE sid=ANY(
SELECT SID
FROM CHOICES
WHERE cid = '10001'
)
--(24)*** 查询选修了所有课程的学生姓名。
SELECT SNAME
FROM STUDENTS
WHERE NOT EXISTS(
SELECT *
FROM COURSES
WHERE NOT EXISTS(
SELECT *
FROM CHOICES
WHERE (STUDENTS.sid = CHOICES.sid )AND( COURSES.cid = CHOICES.cid)
)
)
--(25) 利用集合运算，查询选修课程C++或选修课程Java的学生的编号。
SELECT SID
FROM CHOICES
WHERE cid =(
SELECT cid
FROM COURSES
WHERE cname = 'C++'
)
UNION ALL
SELECT SID
FROM CHOICES
WHERE cid =(
SELECT cid
FROM COURSES
WHERE cname = 'java'
)
--(26) 实现集合交运算，查询既选修课程C++又选修课程Java的学生的编号。
SELECT SID
FROM CHOICES
WHERE cid =(
SELECT cid
FROM COURSES
WHERE cname = 'C++'
)
UNION
SELECT SID
FROM CHOICES
WHERE cid =(
SELECT cid
FROM COURSES
WHERE cname = 'java'
)
--(27) 实现集合减运算，查询选修课程C++而没有选修课程Java的学生的编号。
SELECT SID
FROM CHOICES
WHERE cid =(
SELECT cid
FROM COURSES
WHERE cname = 'C++'
)
EXCEPT
SELECT SID
FROM CHOICES
WHERE cid =(
SELECT cid
FROM COURSES
WHERE cname = 'java'
)
SELECT *
FROM SC
SELECT SNAME ,BIRTH = YEAR(GETDATE())-AGE
FROM S
--比较大小
SELECT S#,GRADE
FROM SC
WHERE GRADE>85
SELECT S# ,GRADE
FROM SC
WHERE C# = 'C1'
--多重条件查询
select S#,C#,GRADE
FROM SC
--WHERE (C#='C1'OR C#='C2')AND GRADE>=90
where C# IN ('C1','C2')
--确定范围
select *
from t
select TNO,TN,PROF
FROM T
where SAL BETWEEN 1000 AND 6000
-- 确定集合 IN 关键字
-- 部分匹配查询 LIKE
SELECT TNO,TN
FROM T
WHERE TN LIKE '张%'
-- 空值查询 IS NULL
SELECT S#
FROM SC
WHERE GRADE IS NULL