数据库作业---例题

例1 SELECT Sno Sname from Student 关系代数 Sname SnoStudent 例2 SELECT Sname Sno Sdept from Student 关系代数 name SSno Sdept Student 例3 SELECT FROM Student 关系代数 1 2 3 4 5 6 Student 例4 select Sname 2004 sage from Student 关系代数 Sname 2004sage Student 例5 SELECT Sname Year of Birth 2004 Sage LOWER Sdept FROM Student 关系代数 Sname Year of Birth 2004 Sage LOWER Sdept Student 例6 SELECT Sno from SC 关系代数 Sno SC 例7 SELECT Sname from Student WHERE Sdept CS 关系代数 CS SnameSdept Student 例8 SELECT Sname Sage FROM Student WHERE Sage 20 关系代数 Sname Sage20 Sage Student 例9 SELECT DISTINCT Sno from SC WHERE Grade 60 关系代数 60 SnoGrade SC 例10 SELECT Sname Sdept Sage From Student WHERE Sage NOT BETWEEN 20 AND 23 关系代数 2023 Sname Sdept SageSageSgae Student 例11 SELECT Sname Sdept Sage From Student WHERE Sage BETWEEN 20 AND 23 关系代数 Sname Sdept Sage2320 SageSage Student 例12 SELECT Sname Ssex from Student WHERE Sdept IN CS MA IS 关系代数 Sname SdeptSdeptCSMAIS Student 例13 SELECT Sname Ssex FROM Student WHERE Sdept NOT IN CS MA IS 关系代数 Sname SsexSdeptCSMAIS Student 例14 SELECT FROM Student WHERE Sno LIKE 200215121 200215121 李勇 男 20 CS 8 关系代数 1 2 3 4 5 6 200215121 Sno Student 例15 select Sname Sno Ssex from Student WHERE Sname LIKE 刘 刘晨 200215122 女 关系代数 Sname Sno SsexSnameStudent 刘 例16 select Sname from Student WHERE Sname LIKE 李 李勇 关系代数 SnameSname Student 欧阳 例17 SELECT Sname Sno from Student WHERE Sname LIKE 敏 王敏 200215123 关系代数 Sname SnoSname Student 阳 例18 SELECT Sname Sno Ssex from Student WHERE Sname NOT LIKE 刘 李勇 200215121 男 王敏 200215123 女 张立 200215125 男 关系代数 Sname Sno Ssex Sname Sno Ssex Sname StudentStudent 刘 例19 select Cno Ccredit from Course WHERE Cname LIKE DB Design ESCAPE 验证通过 关系代数 Cno Ccredit CnameDBDesign Course 例20 SELECT FROM Course WHERE Cname LIKE DB I escape 验证通过 关系代数 1 2 3 4 CnameDBi Course 例21 SELECT Sno Cno from SC WHERE Grade IS NULL 验证通过 关系代数 Sno Cno GradeNULL SC 例22 SELECT Sno Cno from SC WHERE Grade is not null 200215121 1 200215121 2 200215121 3 200215122 2 200215122 3 关系代数 Sno CnoSno Cno GradeNULL SCSC 例23 select Sname from Student where Sdept CS AND Sage2 200215121 例33 SELECT Student SC FROM Student SC WHERE Student Sno sc Sno 200215121 李勇 男 20CS 80200215121 1 92 200215121 李勇 男 20CS 80200215121 2 85 200215121 李勇 男 20CS 80200215121 3 88 200215122 刘晨 女 19CS 83200215122 2 90 200215122 刘晨 女 19CS 83200215122 3 80 关系代数 Sno Sname Ssex Sage Sdept Cno Grade Student SC 例34 SELECT Student Sno Sname Ssex Sdept Cno Grade from Student SC WHERE Student Sno SC Sno 200215121 李勇 男 CS 1 92 200215121 李勇 男 CS 2 85 200215121 李勇 男 CS 3 88 200215122 刘晨 女 CS 2 90 200215122 刘晨 女 CS 3 80 关系代数 Sno Sname Ssex Sage Sdept Cno Grade Student SC 例35 SELECT FIRST Cno SECOND Cpno from Course FIRST Course SECOND WHERE FIRST Cpno SECOND Cno 1 7 3 5 4 NULL 5 6 7 NULL 关系代数 1 7 3 5 7 NULLcourse course 例36 select Student Sno Sname Ssex Sage Sdept Cno Grade from Student LEFT JOIN SC ON Student Sno SC Sno 200215121 李勇 男 20CS 1 92 200215121 李勇 男 20CS 2 85 200215121 李勇 男 20CS 3 88 200215122 刘晨 女 19CS 2 90 200215122 刘晨 女 19CS 3 80 200215123 王敏 女 18MA NULLNULL 200215125 张立 男 19IS NULLNULL 例37 SELECT Student Sno Sname from Student SC WHERE Student Sno SC Sno and SC Cno 2 and SC Grade 80 200215121 李勇 200215122 刘晨 关系代数 2 90 Sname Sno CnoGradeSCStudent 例38 select Student Sno Sname Cname Grade from Student SC Course where Student Sno SC Sno and SC Cno Course Cno 200215121 李勇 数据库 92 200215121 李勇 数学 85 200215122 刘晨 数学 90 200215121 李勇 信息系统 88 200215122 刘晨 信息系统 80 关系代数 Sno Sname Cname GradeStudent SC Course 例39 select Sno Sname Sdept FROM Student where Sdept in SELECT Sdept from Student where Sname 刘晨 200215121 李勇 CS 200215122 刘晨 CS 关系代数 Sdept SnameStudent 刘晨 例40 select Sno Sname from Student where Sno in select Sno from SC where Cno in select Cno from Course where Cname 信息系统 200215121 李勇 200215122 刘晨 关系代数 Sno SnameCname Cno Student SCCourse 信息系统 例41 SELECT Sno Cno from SC x where Grade select AVG Grade from SC y where y Sno x Sno 消息8117 级别16 状态1 第1 行 操作数数据类型nchar 对于avg 运算符无效 例42 SELECT Sname Sage from Student WHERE Sage ANY SELECT Sage from Student where Sdept CS and Sdept CS 王敏 18 张立 19 例43 SELECT Sname Sage from Student where Sage all SELECT Sage from Student where Sdept CS AND Sdept CS 王敏 18 例44 SELECT Sname from Student where exists select from SC WHERE Sno Student Sno and Cno 1 李勇 关系代数 1 Sname Sno Cno S SC 例45 SELECT Sname from Student where not exists select from SC where Sno Student Sno AND Cno 1 刘晨 王敏 张立 关系代数 1 Sname Sname Sno Cno Student S SC 例46 select Sname from Student where not exists select from Course WHERE NOT EXISTS SELECT FROM SC WHERE Sno Student Sno and Cno Course Cno 验证通过 关系代数 Sname Cno SnoCno S SCCourse 例47 select DISTINCT Sno from SC SCX WHERE NOT EXISTS SELECT FROM SC SCY WHERE SCY Sno 200215122 AND NOT EXISTS SELECT FROM SC SCZ WHERE SCZ Sno SCX Sno and SCZ Cno SCY Cno 200215121 200215122 关系代数 200215122 Cno Sno Cno Sno SC SC 例48 SELECT FROM Student where Sdept CS UNION SELECT FROM Student where Sage 19 200215121 李勇 男 20CS 80 200215122 刘晨 女 19CS 83 200215123 王敏 女 18MA 87 200215125 张立 男 19IS 90 关系代数 19 Sno Sname Ssex Sage Sdept SageSdeptCSStudent 例49 SELECT Sno from SC WHERE Cno 1 union SELECT Sno from SC WHERE Cno 2 200215121 200215122 关系代数 1 2 Sno CnoCno CnoCno SCCourse 例50 SELECT FROM Student where Sdept CS INTERSECT SELECT FROM Student where Sage 19 200215122 刘晨 女 19CS 83 关系代数 19 Sno Sname Ssex Sage Sdept SageSdeptCSStudent 例51 SELECT Sno from SC W