数据库实验截图.doc

北京科技大学实验报告 课程：数据库技术与应用 实验名称：数据库查询 系别：通信工程 实验日期：2015年12月9日 专业班级：通信1304 提交日期： 学号 姓名：鲁守城 教师审批签字： 一、实验环境： Microsoft SQL Server 2014 Management Studio 二、实验内容与完成情况： 建立StudentMIS数据库，创建三个表格，分别命名为Student、Course、SC，并编辑一定量的数据 输入Transact-SQL查询语句，完成查询功能： (1) SELECT StuNo,StuName FROM Student WHERE StuNo=ANY(SELECT StuNo FROM SC WHERE CNo=(SELECT CNo FROM Course WHERE CName=数据库) ) (2) SELECT StuNo,StuName FROM Student WHERE StuNo=ANY(SELECT StuNo FROM SC WHERE CNo 000001AND Score ANY(SELECT Score FROM SC WHERE CNo=000001) ) （3） SELECT StuName FROM Student WHERE MajorNo=0002AND Birthday(SELECT Birthday FROM Student WHERE StuName=田二妞) (4) SELECT StuName FROM Student WHERE StuNo!=ALL(SELECT StuNo FROM SC WHERE CNo=000001) (5) SELECT CNo FROM Course WHERE CNo=ANY(SELECT CNo FROM SC) (6) SELECT COUNT(StuNo) FROM SC WHERE CNo=000001 (7) SELECT StuName,Birthday,Address FROM Student WHERE MajorNo=0001AND Sex=女 (8) SELECT * FROM Student WHERE StuName LIKE李% （9） SELECT CNo,AVG(Score)AS 平均成绩,MAX(Score)AS 最高分,MIN(Score)AS 最低分 FROM SC GROUP BY CNo HAVING CNo=(SELECT CNo FROM Course WHERE CName=数据库) (10) SELECT StuName FROM Student WHERE StuNo= ANY(SELECT StuNo FROM SC WHERE Score IS NULL) (11) SELECT * FROM Student WHERE StuNo=ANY(SELECT StuNo FROM SC WHERE CNo=(SELECT CNo FROM SC WHERE StuNo=(SELECT StuNo FROM Student WHERE StuName=奥巴马))) (12) SELECT * FROM Student WHERE Birthday BETWEEN1993-01-01AND1997-01-01 (13) SELECT StuNo,Score FROM SC WHERE CNo=000001ORDER BY Score DESC (14) SELECT * FROM Student ORDER BY MajorNo,Birthday (15) SELECT * FROM Student WHERE StuNo=ANY(SELECT StuNo FROM SC GROUP BY StuNo HAVING COUNT(*)=2) (16) SELECT StuName,COUNT(CNo) FROM SC,Student WHERE SC.StuNo=Student.StuNo GROUP BY Student.StuName (17) SELECT StuName,AGE FROM Student WHERE AGE (SELECT AVG (AGE) FROM Student WHERE Sex=男) AND Sex=女 在学生管理信息