带答案--实验6：SQL高级查询

一、实验目的1. 掌握SQL语言中高级查询语句的使用方法。

2. 熟悉使用子查询、连接查询、分组查询、排序查询等高级查询功能。

3. 提高数据库查询能力，解决实际查询问题。

二、实验环境1. 操作系统：Windows 102. 数据库：MySQL 5.73. 数据库工具：MySQL Workbench三、实验内容1. 创建数据库和表（1）创建数据库```sqlCREATE DATABASE experiment;```（2）创建表```sqlUSE experiment;CREATE TABLE department (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL);CREATE TABLE employee (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,department_id INT,salary DECIMAL(10, 2),FOREIGN KEY (department_id) REFERENCES department(id) );```2. 高级查询（1）子查询```sql-- 查询部门名称为“技术部”的员工信息SELECT FROM employee WHERE department_id IN (SELECT id FROM department WHERE name = '技术部');-- 查询年龄大于30岁的员工信息SELECT FROM employee WHERE age > (SELECT AVG(age) FROM employee);```（2）连接查询```sql-- 查询部门名称为“技术部”的员工及其部门信息SELECT e., AS department_nameFROM employee eJOIN department d ON e.department_id = d.idWHERE = '技术部';-- 查询员工姓名为“张三”的部门及其部门领导信息SELECT AS employee_name, AS department_name, AS manager_nameFROM employee eJOIN department d ON e.department_id = d.idJOIN employee m ON d.id = m.department_idWHERE = '张三' AND = '部门领导';```（3）分组查询```sql-- 查询每个部门员工的人数SELECT AS department_name, COUNT(e.id) AS employee_countFROM department dJOIN employee e ON d.id = e.department_idGROUP BY ;-- 查询平均工资大于5000的部门信息SELECT AS department_name, AVG(e.salary) AS average_salaryFROM department dJOIN employee e ON d.id = e.department_idGROUP BY HAVING AVG(e.salary) > 5000;```（4）排序查询```sql-- 查询所有员工信息，按年龄升序排序SELECT FROM employee ORDER BY age ASC;-- 查询所有员工信息，按工资降序排序SELECT FROM employee ORDER BY salary DESC;```四、实验结果与分析通过本次实验，我们学习了SQL语言中高级查询语句的使用方法，包括子查询、连接查询、分组查询、排序查询等。

实验六SQL 查询一、实验目的：1．熟练掌握SELECT语句的语法格式2．掌握联接的几种方法3．掌握子查询的表示和执行4．能够对SELECT查询结果进行分组、排序及统计5．能够运用T-SQL语句对表进行数据的插入、修改、删除6．能够通过导入/导出向导进行数据的导入导出二、实验内容：利用实验四中的数据库做如下操作：1．在“学生表”中，找出性别为“男”的学生记录，字段包括“姓名”、“出生日期”和“专业”。

2．在“课程表”中，找出“课程名”中包含“计算机”三个字的课程。

3．在“成绩表”中，找出“课程编号”为“001”的课程成绩前三名学生。

4．在“成绩表”、“学生表”和“课程表”中，找出“课程编号”为“001”的课程成绩在[80，90]之间的学生的姓名、课程名和成绩。

5．在“学生表”中，找出“专业”为“计算机软件”、“电子商务”专业的学生信息。

6．统计“计算机应用基础”课程的平均分。

7．查找各门课程的修课人数。

8．在“成绩表”中，找出课程编号为“001”的这门课程的所有学生的分数以及最高分、最低分和平均分。

9．找出所有女生的“计算机应用基础”这门课的成绩，包括字段：姓名、课程名、成绩。

10．查找“成绩表”中，课程编号为“001”的成绩高于平均分的所有学生的学号、姓名、课程名和成绩。

11．查找“成绩表”中，高于各门课程平均分的学生信息。

12．查找“课程表”中，没有被学生修课的课程信息。

13．将“课程表”中的课程编号为“001”的学分增加1学分。

14．删除学号为“”学生的相关信息。

三、实验过程：启动“查询分析器”，在其文本窗口中输入相应的Transcat-SQL语句，分析并执行，观察输出结果。

1．Use 学生select 姓名,出生日期,专业 from 学生表 where 性别='男'2．use 学生select * from 课程表 where 课程名 like '%计算机%'3．use 学生select top 3 * from 成绩表 where 课程编号='001' order by 成绩 desc4．Use 学生select a.姓名,c.课程名,b.成绩 from 学生表 as a join 成绩表 as b on b.课程编号=001 and a.学号=b.学号 and b.成绩 between 80 and 90 join 课程表 as c on c.课程编号=b.课程编号5．use 学生select * from 学生表 where 专业 in ('计算机软件','电子商务')6．use 学生select avg(成绩)as 平均成绩 from 成绩表 as a join 课程表 as bon b.课程名='计算机应用基础' and b.课程编号=a.课程编号7．use 学生select 课程编号,count(学号)as 修课人数 from 成绩表 group by 课程编号8．use 学生select * from 成绩表 where 课程编号=001compute max(成绩),min(成绩),avg(成绩)9．use 学生select a.姓名,b.课程名,c.成绩 from 学生表 as a join 成绩表 as c on a.学号=c.学号 and a.性别='女'join 课程表 as bon b.课程编号=c.课程编号 and b.课程名='计算机应用基础'10．use 学生select a.学号,a.姓名,b.课程名,c.成绩 from 课程表 as b join 成绩表 as c on b.课程编号=c.课程编号 and c.课程编号=001 and c.成绩>(select avg(成绩) from 成绩表 where c.课程编号=001)join 学生表 as a on a.学号=c.学号11．use 学生select * from 成绩表 as a where 成绩>(select avg(成绩) from 成绩表 as b where a.课程编号=b.课程编号)12．use 学生select * from 课程表 where not exists(select * from 成绩表 where 成绩表.课程编号=课程表.课程编号)13．use 学生update 课程表 set 学分=学分+1 where 课程编号=00114．use 学生delete 成绩表 where 学号='20030101'。

C顾客cidcnamecity discntc001 李广天津10.00c002 王开基北京12.00c003 安利德北京8.00c004 曹士雄天津8.00c006 曹士雄广州0.00P商品pidpname city quantity pricep01 梳子天津111400 0.50p02 刷子成都203000 0.50p03 刀片西安150600 1.00p04 钢笔西安125300 1.00p05 铅笔天津221400 1.00p06 文件夹天津123100 2.00p07 盒子成都100500 1.00A代理aidanamecity percenta01 史可然北京 6a02 韩利利上海 6a03 杜不朗成都7a04 甘瑞北京 6a05 敖斯群武汉 5a06 史可然天津 5O订单ordnomonthcidaid pidqtydollars1011 01 c001 a01 p01 1000 450.00 1012 01 c001 a01 p01 1000 450.00 1019 02 c001 a02 p02 400 180.00 1017 02 c001 a06 p03 600 540.00 1018 02 c001 a03 p04 600 540.00 1023 03 c001 a04 p05 500 450.00 1022 03 c001 a05 p06 400 720.00 1025 04 c001 a05 p07 800 720.001013 01 c002 a03 p03 1000 880.001026 05 c002 a05 p03 800 704.001 查询所有定购了至少一个价值为0.50的商品的顾客的名字。

amefrom clientwherecid in (select cid from order1 where pid in(select pid from product where price='0.5'))2 找出全部没有在代理商a03处订购商品的顾客cid值。

实验六T-SQL高级应用一、实验目的：(1) 掌握T-SQL程序设计的控制结构及程序设计逻辑。

(2) 掌握自定义函数、存储过程、游标的使用。

二、实验内容(1) 从学生数据库school中查询所有同学选课成绩情况：姓名、课程名、成绩。

要求：小于60分的输出“不及格”；60—70分的输出“及格”；70—80分的输出“中等”；80—90分的输出“良好”；90—100分的输出“优秀”。

select sname as '姓名',cname as '课程名',casewhen degree<60 then '不及格'when degree<70 then '及格'when degree<80 then '中等'when degree<90 then '良好'else '优秀'end as '成绩'from student s,course c,scwhere s.sno=sc.sno and o=o(2) 创建一个自定义函数，根据学生姓名查询该生所有的选课信息，包括课程名、成绩。

--创建函数f1create function f1(@xm char(10))returns tableasreturn (select cname,degreefrom student s,course c,scwhere s.sno=sc.sno and o=o and sname=@xm) --调用函数f1select * from f1('刘晨')(3) 创建一个自定义函数，实现如下功能：判断一个数是否是素数。

--创建函数，返回1代表n是素数，返回0代表n不是素数create function sh(@n int)returns tinyintbegindeclare @i int,@f tinyintset @i=2set @f=1 --假定@n是素数while @i<=sqrt(@n)beginif @n%@i=0beginset @f=0breakendset @i=@i+1endreturn @fendgo--调用函数sh，判断5是否是素数if dbo.sh(5)=1print '是素数'elseprint '不是素数'(4)创建包含两个参数的存储过程stucred，一个输入参数（@sno）用于指定学生学号，一个输出参数（@s_cred）用于返回该生所修学分总和。

查询练习一、简单查询（无条件查询）：1、查询“学生档案”表中所有的记录SELECT * FORM 学生档案2、查询“学生档案”表中全体学生的姓名、学号、家庭地址SELECT 姓名, 学号, 家庭地址 FROM 学生档案二、有条件查询1、查询“成绩管理”表中语文成绩在80分以下的学生的学号。

SELECT 学号 FROM 成绩管理 WHERE 语文<802、查询“成绩管理”表中语文成绩在80分到90分之间的学生的学号，语文，数学，英语成绩。

SELECT 学号，语文，数学，英语FROM成绩管理WHERE 语文 >= 80 AND 语文<=90==（语文 BETWEEN 80 AND 90）3、查询“成绩管理”表中数学成绩不在75分到85分之间的学生的学号，语文，数学，英语成绩。

SELECT 学号，语文，数学，英语FROM 成绩管理WHERE 数学 NOT BETWEEN 75 AND 854、查询“学生档案”表中李成刚，刘艺梅，郑莉三名学生的信息。

SELECT *FROM 学生档案WHERE 姓名 IN (“李成刚”,“刘艺梅”,“郑莉”)==（姓名 =“李成刚” OR 姓名=“刘艺梅” OR 姓名=“郑莉”）5、查询“学生档案”表中所有姓张的学生的姓名、学号和性别SELECT 姓名，学号，性别 FROM学生档案WHERE 姓名 LIKE “张*”6、查询“学生档案”表中所有姓张且全名为三个汉字的学生的姓名SELECT 姓名FROM 学生档案WHERE姓名 LIKE “张？？”7、查询“学生档案”表中第二个字符为“建”字的学生的学号和姓名SELECT 学号，姓名FROM 学生档案WHERE姓名 LIKE “?建*”8、查询“学生档案”表中家庭住址为“人民路”和“育才路”的学生学号，姓名，性别和家庭住址。

SELECT 学号，姓名，性别，家庭住址FROM 学生档案WHERE家庭住址 LIKE “人民路*” OR家庭住址 LIKE “育才路*”9、查询“学生档案”表中所有团员的学生班级和姓名。

SQL高级查询实战1.创建数据库(5分)1)数据库名：studydb2.创建表(15分)1)学生表2)课程表3)成绩表4)教师表3.插入测试数据(5分)略。

4.实现高级查询(70分，自选14题，每小题5分)1)查询java课程比C#分数高的学生2)查询平均分成绩大于70分的同学的姓名和平均成绩3)查询所有同学的学号、姓名、选课数、总成绩4)查询姓“王”的老师的个数5)查询没有学过java课的学生的学号、姓名6)查询学过“C#”课程并且也学过“sql”课程的学生的学号、姓名7)查询所有课程的平均分、及格率8)查询所有课程成绩小于60分的同学的学号、姓名、性别9)查询没有学全所有课的同学的学号、姓名、性别10)查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名11)查询至少学过学号为“002”同学所有一门课的其他同学学号和姓名12)把成绩表中“李庆”老师教的课的成绩都更改为此课程的平均成绩13)查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名14)删除学习“李庆”老师课的成绩表记录15)按平均成绩从高到低显示所有学生的“sql”、“java”、“c#”三门的课程成绩，按如下形式显示：学生ID,sql,java,c#,有效课程数,有效平均分16)查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分17)查询不同老师所教不同课程平均分从高到低显示18)查询各科成绩前三名的记录:(不考虑成绩并列情况)19)查询每门课程被选修的学生数20)查询出只选修了一门课程的全部学生的学号和姓名21)查询男生、女生人数22)查询姓“张”的学生名单23)查询同名同性学生名单，并统计同名人数24)查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列25)查询平均成绩大于的所有学生的学号、姓名和平均成绩26)检索至少选修两门课程的学生学号27)查询两门以上不及格课程的同学的学号及其平均成绩28)检索“java”课程分数小于60，按分数降序排列的同学姓名29)删除“002”同学的“001”课程的成绩30)查询不及格的课程，并按课程号从大到小排列。

6.3.3 自连接
• 自连接是指同一张表之间的连接查询，它主 要用于在自参照表上显示上下级关系或层次 关系。 例如:emp表中包含empno列和mgr列，二者之 间就有参照关系。（mgr参照empno）。 由于自连接是在同一张表之间的连接， 所以必须定义表别名。
例1： SQL> select manager.ename from emp manager,emp worker 2* where manager.empno=worker.mgr and worker.ename='BLAKE‘ ENAME ---------KING
Oracle 10g 数据库
第6章 SQL高级查询
目标
• • • • 了解关系操作和关系完整性； 理解和掌握连接查询； 掌握子查询； 会使用集合操作符。
6.1 关系操作
• 关系运算包括传统的集合运算和专门的关系 运算。 • 传统的集合运算将关系看成元组的集合，其 运算是从关系的“水平”方向即行的角度进 行；而专门的关系运算不仅涉及行而且涉及 列。 • 传统的集合元素包括并、交、差、笛卡尔积 四种运算，专门的关系运算包括选择、投影、 连接和除法运算。
A a1 a2 NULL
B b1 b3 b2
C 5 8 NULL
E 3 10 7
右外连接
6.2 关系的完整性约束
关系模型中有三种完整性约束：实体完整性、 参照完整性、用户自定义完整性。其中前两个是 关系模型必须满足的完整性约束条件，有关系系 统自动支持。而用户自定义完整性是应用领域需 要遵循的约束条件，体现了具体应用领域中的语 义约束。 （1）实体完整性 若属性（指一个或一组属性）A是关系R的主 属性（即主键中的属性），则A不能去空值。 所谓空值就是“不知道”或“不存在”的值。

SQL⾼级查询——50句查询（含答案）--⼀个题⽬涉及到的50个Sql语句--（下⾯表的结构以给出,⾃⼰在数据库中建⽴表.并且添加相应的数据,数据要全⾯些. 其中Student表中,SId为学⽣的ID）------------------------------------表结构----------------------------------------学⽣表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）--成绩表tblScore（学⽣编号StuId、课程编号CourseId、成绩Score）--教师表tblTeacher（教师编号TeaId、姓名TeaName）-----------------------------------------------------------------------------------问题：--1、查询“001”课程⽐“002”课程成绩⾼的所有学⽣的学号；Select StuId From tblStudent s1Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')--2、查询平均成绩⼤于60分的同学的学号和平均成绩；Select StuId,Avg(Score) as AvgScore From tblScoreGroup By StuIdHaving Avg(Score)>60--3、查询所有同学的学号、姓名、选课数、总成绩；Select StuId,StuName,SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId),SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId)From tblStudent s1--4、查询姓“李”的⽼师的个数；Select Count(*) From tblTeacher Where TeaName like '李%'--5、查询没学过“叶平”⽼师课的同学的学号、姓名；Select StuId,StuName From tblStudentWhere StuId Not In(Select StuID From tblScore scInner Join tblCourse cu ON sc.CourseId=cu.CourseIdInner Join tblTeacher tc ON cu.TeaId=tc.TeaIdWhere tc.TeaName='叶平')--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；Select StuId,StuName From tblStudent stWhere (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId='001')>0 And(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId='002')>0--7、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；Select StuId,StuName From tblStudent st Where not exists(Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaIDWhere tc.TeaName='叶平' And CourseID not in(Select CourseID From tblScore Where StuID=st.StuID))--8、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；Select StuId,StuName From tblStudent s1Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')> (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')--9、查询所有课程成绩⼩于60分的同学的学号、姓名；Select StuId,StuName From tblStudent stWhere StuId Not IN(Select StuId From tblScore sc Where st.StuId=sc.StuId And Score>60)--10、查询没有学全所有课的同学的学号、姓名；Select StuId,StuName From tblStudent stWhere (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)<(Select Count(*) From tblCourse)--11、查询⾄少有⼀门课与学号为“1001”的同学所学相同的同学的学号和姓名；------运⽤连接查询Select DistInct st.StuId,StuName From tblStudent stInner Join tblScore sc ON st.StuId=sc.StuIdWhere sc.CourseId IN (Select CourseId From tblScore Where StuId='1001')------嵌套⼦查询Select StuId,StuName From tblStudentWhere StuId In(Select Distinct StuId From tblScore Where CourseId In (Select CourseId From tblScore Where StuId='1001'))--12、查询⾄少学过学号为“1001”同学所有课程的其他同学学号和姓名；Select StuId,StuName From tblStudentWhere StuId In(Select Distinct StuId From tblScore Where CourseId Not In (Select CourseId From tblScore Where StuId='1001')--13、把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩； (从⼦查询中获取⽗查询中的表名，这样也⾏)--创建测试表Select * Into Sc From tblScoregoUpdate Sc Set Score=(Select Avg(Score) From tblScore s1 Where s1.CourseId=sc.CourseId)Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaID=tc.TeaID WHERE TeaName ='叶平')--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；Select StuID,StuName From tblStudent stWhere StuId <> '1002'AndNot Exists(Select * From tblScore sc Where sc.StuId=st.StuId And CourseId Not In (Select CourseId From tblScore Where StuId='1002')) AndNot Exists(Select * From tblScore Where StuId='1002' And CourseId Not In (Select CourseId From tblScore sc Where sc.StuId=st.StuId))--15、删除学习“叶平”⽼师课的SC表记录；Delete From tblScore Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaId=tc.TeaId Where tc.TeaName='叶平')--16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、'002'号课的平均成绩；Insert Into tblScore (StuId,CourseId,Score)Select StuId,'002',(Select Avg(Score) From tblScore Where CourseId='002') From tblScore WhereStuId Not In (Select StuId From tblScore Where CourseId='003')--17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID,,数据库,企业管理,英语,有效课程数,有效平均分Select StuId,数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='数据库' And sc.StuID=st.StuId),企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='企业管理' Andsc.StuID=st.StuId),英语=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='英语' Andsc.StuID=st.StuId),有效课程数=(Select Count(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='英语') And sc.StuID=st.StuId),有效平均分=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='英语') And sc.StuID=st.StuId)From tblStudent stOrder by 有效平均分 Desc--18、查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分Select CourseId as 课程ID, 最⾼分=(Select Max(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),最低分=(Select Min(Score) From tblScore sc Where sc.CourseId=cs.CourseId )From tblCourse cs--19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序 (百分数后如何格式化为两位⼩数??)Select 课程ID,平均分,及格率 From(Select CourseId as 课程ID, 平均分=(Select Avg(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),及格率=Convert(varchar(10),((Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId And sc.Score>=60)*10000/(Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId))/100)+'%'From tblScore cs) as tmpGroup by 课程ID,平均分,及格率Order by 平均分, Convert(float,substring(及格率,1,len(及格率)-1)) Desc--20、查询如下课程平均成绩和及格率的百分数(⽤"1⾏"显⽰): 企业管理（001），马克思（002），OO&UML （003），数据库（004）Select 课程ID=sc.CourseId,课程名称=cs.CourseName,平均成绩=Avg(Score),及格率 =Convert(varchar(10),((Select Count(Score) From tblScore Where CourseId=sc.CourseId AndScore>=60)*10000/Count(Score))/100.0)+'%'From tblScore scInner Join tblCourse cs ON sc.CourseId=cs.CourseIdWhere sc.CourseId like '00[1234]'Group By sc.CourseId,cs.CourseName--21、查询不同⽼师所教不同课程平均分从⾼到低显⽰Select 课程ID=CourseId,课程名称=CourseName,授课教师=TeaName,平均成绩=(Select Avg(Score) From tblScore WhereCourseId=cs.CourseId)From tblCourse csInner Join tblTeacher tc ON cs.TeaId=tc.TeaIdOrder by 平均成绩 Desc--22、查询如下课程成绩第 3 名到第 6 名的学⽣成绩单：企业管理（001），马克思（002），UML （003），数据库（004）格式：[学⽣ID],[学⽣姓名],企业管理,马克思,UML,数据库,平均成绩Select * From(Select Top 6 学⽣ID=StuId,学⽣姓名=StuName,企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='企业管理' And sc.StuID=st.StuId),马克思=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='马克思' Andsc.StuID=st.StuId),UML=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='UML' Andsc.StuID=st.StuId),数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='数据库' Andsc.StuID=st.StuId),平均成绩=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='UML'or CourseName='马克思') And sc.StuID=st.StuId),排名=Row_Number() Over(Order by(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='UML'or CourseName='马克思') And sc.StuID=st.StuId) DESC) From tblStudent stOrder by 排名) as tmpWhere 排名 between 3 And 6--23、统计列印各科成绩,各分数段⼈数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]Select 课程ID=CourseId, 课程名称=CourseName,[100-85]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 85 And 100),[85-70]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 70 And 84),[70-60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 60 And 69),[<60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score <60)From tblCourse cs--24、查询学⽣平均成绩及其名次Select 学号=st.StuId, 姓名=StuName,平均成绩=sc.AvgScore,名次=(Dense_Rank() Over(Order by sc.AvgScore Desc)) From tblStudent st Inner Join (Select StuId,Avg(Score) as AvgScore From tblScore Group by StuId) as sc On sc.StuId=st.StuIdOrder by 学号--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)Select 学号=StuId,课程号=CourseId,分数=ScoreFrom(Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as tmp --得到⼀个临时的排名表,其中i表⽰编号Where i In(Select Top 3 i From (Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as t1 Wheret1.CourseId=tmp.CourseId)--26、查询每门课程被选修的学⽣数Select 课程ID=CourseId,选修⼈数=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp) From tblCourse cs--27、查询出只选修了⼀门课程的全部学⽣的学号和姓名Select 学号=StuId,姓名=StuNameFrom tblStudent stWhere (Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)=1--28、查询男⽣、⼥⽣⼈数Select 男⽣⼈数=(select Count(*) From tblStudent Where StuSex='男'),⼥⽣⼈数=(select Count(*) From tblStudent Where StuSex='⼥')--29、查询姓“张”的学⽣名单Select * From tblStudent Where StuName like '张%'--30、查询同名同性学⽣名单，并统计同名⼈数Select Distinct 学⽣姓名=StuName,同名⼈数=(Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName) From tblStudent st Where (Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName)>=2--31、1981年出⽣的学⽣名单(注：Student表中Sage列的类型是datetime)Select * From tblStudent Where Year(Sage)=1981--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select 课程ID=CourseId,课程名称=CourseName,平均成绩=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)From tblCourse csOrder by 平均成绩,CourseId Desc--33、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩Select 学号=StuId,姓名=StuName,平均成绩=(Select Avg(Score) From tblScore Where StuId=st.StuId) From tblStudent stWhere (Select Avg(Score) From tblScore Where StuId=st.StuId)>85--34、查询课程名称为“数据库”，且分数低于60的学⽣姓名和分数Select 姓名=StuName,分数=Score From tblScore scInner Join tblStudent st On sc.StuId=st.StuIdInner Join tblCourse cs On sc.CourseId=cs.CourseIdWhere CourseName='数据库' And Score<60--35、查询所有学⽣的选课情况；Select 学号=StuId,选课数=(Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)From tblStudent stSelect distinct 姓名=StuName,选修课程=CourseName From tblScore scInner Join tblStudent st On sc.StuId=st.StuIdInner Join tblCourse cs On sc.CourseId=cs.CourseId--36、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数；Select 姓名=StuName,课程名称=CourseName,分数=Score From tblScore scInner Join tblStudent st On sc.StuId=st.StuIdInner Join tblCourse cs On sc.CourseId=cs.CourseIdWhere Score>=70--37、查询不及格的课程，并按课程号从⼤到⼩排列Select * From tblScore Where Score<60 order by CourseId Desc--38、查询课程编号为003且课程成绩在80分以上的学⽣的学号和姓名；Select StuId,StuName From tblStudentWhere StuId in(Select StuId From tblScore Where CourseId='003' And Score>=80)--39、求选了课程的学⽣⼈数Select 选了课程的学⽣⼈数=Count(*) From tblStudent st Where StuId IN (Select StuID From tblScore)--40、查询选修“叶平”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩Select CourseId,CourseName,该科最⾼学⽣=(Select StuName From tblStudent Where StuId in (Select Top 1 StuID From tblScore Where CourseId=cs.CourseId Order by Score Desc)),成绩=(Select Top 1 Score From tblScore Where CourseId=cs.CourseId Order by Score Desc)From tblCourse cs Inner Join tblTeacher tc ON cs.TeaId=tc.TeaIdWhere TeaName='叶平'--41、查询各个课程及相应的选修⼈数Select 课程ID=CourseId,选修⼈数=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp) From tblCourse cs--42、查询不同课程成绩相同的学⽣的学号、课程号、学⽣成绩Select 学号=StuId, 课程号=CourseId, 成绩=Score From tblScore scWhere Exists (Select * From tblScore Where Score=sc.Score And StuId=sc.StuId And CourseId <>sc.CourseId)Order by 学号,成绩--43、查询每门功成绩最好的前两名Select 课程号=CourseId,第1名=(Select Top 1 StuId From tblScore Where CourseId=cs.CourseId Order by Score DESC),第2名=(Select Top 1 StuID From (Select Top 2 StuId,Score From tblScore Where CourseId=cs.CourseId Order by Score DESC) as tmp Order by Score)From tblCourse cs--44、统计每门课程的学⽣选修⼈数（超过10⼈的课程才统计）。

数据库实验和参考答案上机实验七1．声明一个字符串型的局部变量，并对其赋值：‘我的变量’，然后显示出此值。

declare @a char(10)set @a='我的变量'--select @a as 变量的值print @a2．编程实现如下功能：1）声明两个整形的局部变量：@i1和@i2,对@i1赋初值：10，@i2的值为：@i1乘以5，再显示@i2的结果值。

declare @i1 int,@i2 intset @i1=10set @i2=@i1*5print @i22）用While语句实现5000减1，减2，……一直减到50的计算，并显示最终的结果。

declare @sum int,@i intset @sum=5000set @i=1while(@i<=50)beginset @i=@i+1endprint @sum3）输出100以内的素数。

declare @i smallint,@jsmallint,@k smallintset @i=2while(@i<=100)beginset @k=0set @j=2while(@j<@i)beginif(@i%@j=0)beginset @j=@Iendset @j=@j+1endif @k=0print @Iset @i=@i+1end4）将字符数在20以内的字符串变量C的值逆序输出。

要求输出界面为：declare @i varchar(20),@j int,@k varchar(20)set @j=1set @k=''while @j<=len(@i)beginset @k=substring(@i,@j,1) set @j=@j+1endprint '字符串C的值:'+@iprint 'C的逆序字符串:'+@k5）从SC表中查询所有学生的选课成绩情况，分别统计各分数段人数，并输出统计结果。

oracle实验6 sql高级查询一、实验目的1.掌握SELECT语句的多表连接查询。

2.掌握SELECT语句的子查询。

二、实验内容完成第六章实验和习题内容三、实验环境Windows xp , Oracle 10g四、实验步骤（一）根据Oracle数据库scott方案下的emp表和dept表，完成下列操作：1.查询所有工种为CLERK的员工的姓名及其部门名称。

select ename,dnamefrom scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptnowhere job='CLERK';2.查询所有部门及其员工信息，包括那些没有员工的部门。

select * from scott.emp t1 right join scott.dept t2 on t1.deptno=t2.deptno3.查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

select * from scott.emp t1 left join scott.dept t2 on t1.deptno=t2.deptno4.查询在SALES部门工作的员工的姓名信息。

用子查询实现:select * from scott.empwhere deptno=(select deptno from scott.dept where dname='SALES')用连接查询实现:select * from scott.emp t1 inner join scott.dept t2 on t1.deptno=t2.deptnowhere t2.dname='SALES';注意两种实现方式，在行和列上的变化。

5.查询所有员工的姓名及其直接上级的姓名。

select t1.ename as 员工姓名,t2.ename 经理姓名from scott.emp t1,scott.emp t2where t1.mgr=t2.empno;6.查询入职日期早于其上级领导的所有员工的信息。

练习6 复杂的结构化查询语句6．1 上机目的1、掌握SELECT语句的统计函数的作用和使用方法2、通过练习SELECT语句的GROUP BY和ORDER BY字句的用法，理解其作用，掌握语句的写法。

3、通过练习涉及多张表的连接查询，掌握它的作用和写法6．2 上机练习预备知识点说明：①函数中DISTINCT的作用是统计计算的过程中去掉重复值。

②函数中ALL的作用是统计计算全部的值包括重复值。

可省略。

6．2．2 GROUP BY 子句作用：将记录根据GROUP BY后所跟字段的值分成多个组，进行分组计算。

一般情况GROUP BY 子句与汇总函数连用。

格式：GROUP BY（字段，…n）例14 按照班级把学生信息表的数据分组,并且统计每个班级的人数分析：Students表中班级人数这一列，因此需要计算才能得到。

因此需要先按class分组，class列中有几个不同的值就要分成几组，再按各组进行统计计算。

SELECT class,人数= count(*)FROM StudentsGROUP BY class注意：①分组也可以根据多个字段；②不能对数据类型为ntext,text,image或bit的字段使用GROUP BY1、HAVING 子句作用：HAVING子句将对GROUP BY 子句选择出来的结果进行再次筛选，最后输出符合HAVING 子句条件的结果。

HAVING子句必须与GROUP BY子句连用。

例15 查询平均入学总分在350分以上的班级分析：Students表中没有平均分这一列，因此需要计算才能得到。

因此需要先按class分组，class列中有几个不同的值就要分成几组，再进行统计计算，最后用HAVING子句筛选出AVG(mgrade) >=350的记录。

SELECT class,AVG(mgrade)FROM StudentsGROUP BY classHAVING AVG(mgrade)>=3502、ALL关键字作用：暂时忽略WHERE子句中的查询条件。

SQL查询语言高级应用测试（答案见尾页）一、选择题1. SQL中，用于修改查询结果的命令是（）。

A. SELECTB. UPDATEC. DELETED. ALTER2. 在SQL中，若要修改表结构，应该使用（）。

A. CREATEB. ALTERC. DROPD. UPDATE3. SQL查询语句中，用于分组查询结果的是（）。

A. GROUP BYB. ORDER BYC. HAVINGD. DISTINCT4. 在SQL中，若要删除一个表，应该使用（）。

A. DROP TABLEB. DELETE TABLEC. DELETED. TRUNCATE TABLE5. SQL查询中，用于筛选满足特定条件的记录的关键字是（）。

A. WHEREB. FORC. WHILED. EACH6. 在SQL中，若要修改列的数据类型，应该使用（）。

A. ALTER TABLEB. CREATE TABLEC. DROP COLUMND. MODIFY COLUMN7. SQL查询中，用于排序查询结果的是（）。

A. ORDER BYB. GROUP BYC. DISTINCTD. HAVING8. 在SQL中，若要查看表的结构，应该使用（）。

A. DESCRIBEB. SHOW TABLESC. LOOKUP TABLED. INFO TABLE9. SQL查询中，用于分组的命令是（）。

A. ORDER BYB. GROUP BYC. DISTINCTD. HAVE10. 在SQL中，若要插入新的记录，应该使用（）。

A. INSERT INTOB. REPLACE INTOC. CREATED. PUT11. SQL查询语言中，用于排序查询结果的是哪个关键字？A. ORDER BYB. GROUP BYC. DISTINCTD. HAVING12. 在SQL中，为了修改表结构，应使用哪种关键字？A. ALTER TABLEB. CREATE TABLEC. DELETE TABLED. RENAME TABLE13. SQL查询语句中，用于筛选满足某个条件的记录的关键字是哪个？A. WHEREB. WHILEC. FORD. EACH14. 在SQL的聚合函数中，用于计算所有记录的平均值的是哪个函数？A. SUMB. AVGC. COUNTD. MAX15. SQL查询中的子查询是指什么？A. 一个包含SQL查询的查询B. 一个嵌套在主查询中的查询C. 一个SELECT查询语句D. 一个包含WHERE子句的查询16. 在SQL中，用于分组查询结果的是哪个关键字？A. GROUP BYB. ORDER BYC. DISTINCTD. HAVING17. SQL查询中，用于指定查询结果返回的列数的关键字是哪个？A. SELECTB. FROMC. WHERED. ALL18. 在SQL的聚合函数中，用于计算查询结果中某个字段的总和的是哪个函数？A. SUMB. AVGC. COUNTD. MAX19. SQL查询中，用于限制查询结果数量的关键字是哪个？A. LIMITB. OFFSETC.哥哥D.妹妹20. 在SQL查询中，用于将查询结果按照指定的顺序排列的关键字是哪个？A. ORDER BYB. GROUP BYC. DISTINCTD. HAVING21. SQL中，用于修改查询结果的命令是什么？A. DELETEB. UPDATEC. INSERT INTOD. ALTER TABLE22. 在SQL中，如何使用子查询来更新主表的数据？A. 将子查询的结果直接赋值给主表的某个字段B. 使用子查询来更新主表的条件C. 将子查询的结果作为另一个查询的筛选条件D. 将子查询的结果与主表的数据进行交集运算23. 什么是SQL注入攻击？它如何发生？A. SQL注入攻击是通过在SQL查询中插入恶意的SQL代码，导致执行非预期的SQL 语句B. SQL注入攻击发生在应用程序没有正确过滤用户的输入C. SQL注入攻击可以通过电子邮件发送恶意链接来传播D. SQL注入攻击是由于应用程序使用的是旧的SQL版本24. 在SQL中，如何使用联合查询来合并两个或多个数据表的信息？A. 使用INNER JOINB. 使用LEFT JOINC. 使用RIGHT JOIND. 使用FULL OUTER JOIN25. 什么是SQL视图？它有哪些优点和限制？A. SQL视图是一个虚拟表，它包含了查询结果B. 视图可以用来简化复杂的查询逻辑C. 视图可以用来实现数据的加密D. 视图具有数据完整性的约束，不能修改26. 在SQL中，如何使用事务来保证数据的完整性和一致性？A. 将多个SQL语句包装在一个事务中B. 使用COMMIT和ROLLBACK命令来管理事务C. 使用SET TRANSACTION ISOLATION LEVEL命令来设置事务隔离级别D. 使用SELECT语句来查看事务的状态27. 什么是SQL索引？它如何提高查询性能？A. SQL索引是一个存储在磁盘上的表格，用于加速数据的查找B. 索引可以按照指定的列进行排序C. 索引可以加快查询速度，但会降低写入性能D. 索引可以用来唯一标识表中的每一行数据28. 在SQL中，如何使用分组函数（如SUM）来统计查询结果？A. 将查询结果按照指定的列进行分组B. 使用GROUP BY子句来对查询结果进行分组C. 使用HAVING子句来过滤分组后的结果D. 使用ORDER BY子句来对分组结果进行排序29. 什么是SQL触发器？它在数据库中的作用是什么？A. SQL触发器是一种数据库对象，用于自动执行响应特定事件的操作B. 触发器可以在数据库中实现复杂的业务逻辑C. 触发器可以用来强制数据完整性D. 触发器只能在SQL Server中存在30. 在SQL中，如何使用外键来维护表之间的关系？A. 在表中添加一个字段，该字段引用另一个表的主键B. 在表中添加一个字段，该字段引用另一个表的外键C. 在表中添加一个字段，该字段引用另一个表的唯一键D. 在表中添加一个字段，该字段引用另一个表的所有键31. SQL中用于数据查询的命令是？A. SELECTB. INSERTC. UPDATED. DELETE32. 在SQL中，若要修改表结构，应该使用哪种命令？A. ALTER TABLEB. CREATE TABLEC. DROP TABLED. MODIFY TABLE33. SQL语言中的子查询是指？A. 一个包含SELECT语句的查询B. 一个包含FROM子句的查询C. 一个包含WHERE子句的查询D. 一个不包含任何子句的查询34. 在SQL中，用于分组查询结果的命令是？A. GROUP BYB. ORDER BYC. HAVINGD. DISTINCT35. SQL中的聚合函数不包括以下哪个？A. COUNTB. SUMC. AVGD. MAX36. 在SQL中，用于连接两个表的命令是？A. JOINB. UNIONC. CROSS JOIND. INNER JOIN37. SQL中，用于筛选满足特定条件的查询结果的是？A. WHERE子句B. HAVING子句C. BETWEEN关键字D. LIKE关键字38. 在SQL中，用于插入数据的命令是？A. INSERT INTOB. CREATE TABLEC. UPDATED. DELETE39. SQL中的视图（View）是一种虚拟表，其功能包括？A. 查看表中的数据B. 修改表中的数据C. 创建表D. 删除表40. 在SQL中，用于删除表中数据的命令是？A. DROP TABLEB. DELETEC. TRUNCATE TABLED. CASCADE二、问答题1. 什么是SQL查询中的SELECT语句？它的主要功能是什么？2. 如何在SQL查询中使用WHERE子句来过滤结果？3. 什么是SQL的聚合函数？它们有哪些用途？4. 在SQL查询中，如何使用JOIN子句来连接不同的表？5. 什么是SQL的子查询？它有什么特点？6. 如何在SQL查询中使用CASE语句来进行条件判断？7. 什么是SQL的透视表？它的作用是什么？8. 如何在SQL查询中使用ORDER BY子句对结果进行排序？参考答案选择题：1. B2. B3. A4. A5. A6. D7. A8. A9. B 10. A11. A 12. A 13. A 14. B 15. B 16. A 17. D 18. A 19. A 20. A21. B 22. C 23. A 24. A 25. AB 26. B 27. ACD 28. ABC 29. ABC 30. A31. A 32. A 33. A 34. A 35. D 36. D 37. A 38. A 39. AB 40. B问答题：1. 什么是SQL查询中的SELECT语句？它的主要功能是什么？SELECT语句是SQL查询的核心，用于从数据库表中检索数据。

sql查询举例(含答案)编辑整理：尊敬的读者朋友们：这里是精品文档编辑中心，本文档内容是由我和我的同事精心编辑整理后发布的，发布之前我们对文中内容进行仔细校对，但是难免会有疏漏的地方，但是任然希望（sql查询举例(含答案)）的内容能够给您的工作和学习带来便利。

同时也真诚的希望收到您的建议和反馈，这将是我们进步的源泉，前进的动力。

本文可编辑可修改，如果觉得对您有帮助请收藏以便随时查阅，最后祝您生活愉快业绩进步，以下为sql查询举例(含答案)的全部内容。

查询练习一、简单查询（无条件查询）：1、查询“学生档案”表中所有的记录SELECT * FORM 学生档案2、查询“学生档案”表中全体学生的姓名、学号、家庭地址SELECT 姓名，学号，家庭地址 FROM 学生档案二、有条件查询1、查询“成绩管理”表中语文成绩在80分以下的学生的学号。

SELECT 学号 FROM 成绩管理 WHERE 语文<802、查询“成绩管理”表中语文成绩在80分到90分之间的学生的学号,语文，数学，英语成绩。

SELECT 学号，语文，数学，英语FROM成绩管理WHERE 语文 >= 80 AND 语文<=90==(语文 BETWEEN 80 AND 90）3、查询“成绩管理”表中数学成绩不在75分到85分之间的学生的学号,语文，数学，英语成绩.SELECT 学号，语文，数学，英语FROM 成绩管理WHERE 数学 NOT BETWEEN 75 AND 854、查询“学生档案”表中李成刚，刘艺梅，郑莉三名学生的信息。

SELECT *FROM 学生档案WHERE 姓名 IN (“李成刚”,“刘艺梅",“郑莉”）==（姓名 =“李成刚” OR 姓名=“刘艺梅” OR 姓名=“郑莉”）5、查询“学生档案"表中所有姓张的学生的姓名、学号和性别SELECT 姓名，学号，性别 FROM学生档案WHERE 姓名 LIKE “张＊"6、查询“学生档案”表中所有姓张且全名为三个汉字的学生的姓名SELECT 姓名FROM 学生档案WHERE姓名 LIKE “张？?"7、查询“学生档案"表中第二个字符为“建"字的学生的学号和姓名SELECT 学号，姓名FROM 学生档案WHERE姓名 LIKE “?建＊”8、查询“学生档案”表中家庭住址为“人民路"和“育才路"的学生学号，姓名,性别和家庭住址.SELECT 学号，姓名,性别，家庭住址FROM 学生档案WHERE家庭住址 LIKE “人民路*" OR家庭住址 LIKE “育才路＊"9、查询“学生档案”表中所有团员的学生班级和姓名.SELECT 班级，姓名FROM 学生档案WHERE是否团员=yes10、查询“学生档案”表中1995年4月1日以前出生,女同学或团员的学生记录。

数据库中有如下三个表：学生表（学号id，姓名name，性别sex，系部depart，年龄age）8个学生记录选课表（学号id，课程号cid，成绩grade） 12门课程课程表（课程号cid，课程名cname，学分Ccredit） 6门课程1.从学生表中查询所有同学的所有信息select*from学生表2.从学生表中查询所有学生的信息，并分别赋予一个别名select学号as xuehao,姓名as xingming,性别as xingbie,系部as xibu,年龄as nianling from学生表3.从学生表中查询姓名是Allen的学生的信息select*from学生表where姓名='Allen'4.从学生表中查询学号在1101到1199之间的所有学生的信息select*from学生表where学号between 1101 and 11995.从学生表中查询年龄小于18和大于20的所有学生的学号和姓名select学号,姓名from学生表where年龄<18 or年龄>206.从学生表中查询计算机系年龄小于20的所有学生的信息select*from学生表where系部='computer'and年龄<207.从学生表中查询姓名以A开头的学生的信息select*from学生表where姓名LIKE'A%'8.从学生表中查询姓名的第三个字符是A的学生的学号和姓名select学号,姓名from学生表where姓名LIKE'__A%'9.从学生表中查询姓名中包含“llen”的学生的学号和姓名select学号,姓名from学生表where姓名LIKE'%llen%'10.从学生表中查询姓名中包含“llen”且姓名只有5个字符的学生的学号和姓名select学号,姓名from学生表where姓名LIKE'%llen%'and len(姓名)=511.从学生表中查询有年龄信息的学生的学号和姓名select学号,姓名from学生表where年龄is not null12.从学生表中查询最大年龄和最小年龄select max(年龄)最大年龄,min(年龄)最小年龄from学生表13.从学生表中查询所有学生的平均年龄select avg(年龄)平均年龄from学生表14.从学生表中查询学校所有系的名字select distinct系部from学生表15.从学生表中查询学校共有多少个系select count(distinct系部)系部总和from学生表16.从选课表中查询所有学生的选课情况select distinct课程号from选课表17.从选课表中查询选修课程号为C01课程的学生的学号select学号from选课表where课程号='C01'18.从选课表中查询所有没有选C02课程的学生的学号select distinct学号from选课表where课程号!='C02'19.从选课表中查询有选修C01或C02课程的学生的学号select distinct学号from选课表where课程号='C01' or 课程号='C02'20.从选课表中查询学号为1101的学生的选课情况select课程号from选课表where学号='1101'21.从选课表中查询所有选课信息，即学号、课程号、成绩，并给成绩加8分select学号,课程号,成绩=成绩+8 from选课表22.从选课表中查询学号为1101的学生的所有选修课程成绩的总和select sum(成绩)成绩总和from选课表where学号='1101'23.从选课表中查询选修课程好为C02所有学生的成绩平均值并赋予“平均成绩”列名select avg(成绩)平均成绩from选课表where课程号='C02'24.从选课表中查询选修课程号C02且该门课程考试及格的学生的学号select学号from选课表where课程号='C02'and成绩>=6025.从选课表中查询所有无考试成绩的学生的学号和课程的课程号select学号,课程号from选课表where成绩is null26.从选课表中查询选修了课程号以C开头的学生的学号和所选课程的课程号select学号,课程号from选课表where课程号LIKE'C%'27.从选课表中查询选修了课程号以C、D或E开头学生的学号和所选课程的课程号select学号,课程号from选课表where课程号LIKE'[CDE]%'28.从选课表中查询选修了课程号中包含DB的学生的学号和课程号select学号,课程号from选课表where课程号LIKE'%DB%'29.从选课表中查询选修了课程的学生的学号select distinct学号from选课表where课程号is not null30.从选课表中查询选修了课程的学生的人数select count(distinct学号)总人数from选课表31.找出姓名以D开头的学生姓名和所有成绩select学生表.姓名,选课表.成绩from学生表join选课表on学生表.学号=选课表.学号where学生表.姓名LIKE'D%'32.查找的所有学生姓名与学号，结果按学号降序排序select 学号,姓名from学生表order BY学号DESC33.查找成绩介于80和90之间的学生姓名，结果按成绩和姓名升序排序select学生表.姓名from选课表join学生表on学生表.学号=选课表.学号where选课表.成绩between 80 and 90order BY选课表.成绩,学生表.姓名34.查找english系的所有学生姓名，结果按成绩和姓名升序排序select学生表.姓名,学生表.学号,选课表.成绩from选课表join学生表on学生表.学号=选课表.学号where学生表.系部='english'35.查找同时选修了C01及C02两门课程的学生姓名及学号select学生表.姓名,A.学号from选课表as A join选课表as B on A.学号=B.学号join学生表on学生表.学号=A.学号where A.课程号='C01'and B.课程号='C02'36.查找所有选修了课程的学生姓名及所在系别select distinct学生表.姓名,学生表.系部from学生表join选课表on学生表.学号=选课表.学号where选课表.课程号is not null37.查找成绩高于90分的学生姓名、学号及系别select学生表.姓名,学生表.学号,学生表.系部from学生表join选课表on学生表.学号=选课表.学号where选课表.成绩>=9038.找出选修了C01课程的学生姓名select学生表.姓名from学生表join选课表on学生表.学号=选课表.学号where选课表.课程号='C01'39.查询English系学生人数select count(*) English 系总人数from学生表where系部='English'40.分别查询各系的学生人数select系部,count(*)人数from学生表group by系部41.创建一个角色uus.CREATE ROLE uus;42.给uus授权SELECT,UPDATE,INSERT .GRANT SELECT,UPDATE,INSERTON StuTO uus43.增加一个登录,登录名为tp,密码为123,默认的数据库为stuEXEC sp_addlogin 'tp', '123', 'stu'44.将登录tp增加为test库的一个用户,并连接到test库。

实验六SQL 查询一、实验目的：1．熟练掌握SELECT语句的语法格式2．掌握联接的几种方法3．掌握子查询的表示和执行4．能够对SELECT查询结果进行分组、排序及统计5．能够运用T-SQL语句对表进行数据的插入、修改、删除6．能够通过导入/导出向导进行数据的导入导出二、实验内容：利用实验四中的数据库做如下操作：1．在“学生表”中，找出性别为“男”的学生记录，字段包括“姓名”、“出生日期”和“专业”。

2．在“课程表”中，找出“课程名”中包含“计算机”三个字的课程。

3．在“成绩表”中，找出“课程编号”为“001”的课程成绩前三名学生。

4．在“成绩表”、“学生表”和“课程表”中，找出“课程编号”为“001”的课程成绩在[80，90]之间的学生的姓名、课程名和成绩。

5．在“学生表”中，找出“专业”为“计算机软件”、“电子商务”专业的学生信息。

6．统计“计算机应用基础”课程的平均分。

7．查找各门课程的修课人数。

8．在“成绩表”中，找出课程编号为“001”的这门课程的所有学生的分数以及最高分、最低分和平均分。

9．找出所有女生的“计算机应用基础”这门课的成绩，包括字段：姓名、课程名、成绩。

10．查找“成绩表”中，课程编号为“001”的成绩高于平均分的所有学生的学号、姓名、课程名和成绩。

11．查找“成绩表”中，高于各门课程平均分的学生信息。

12．查找“课程表”中，没有被学生修课的课程信息。

13．将“课程表”中的课程编号为“001”的学分增加1学分。

14．删除学号为“20030101”学生的相关信息。

三、实验过程：启动“查询分析器”，在其文本窗口中输入相应的Transcat-SQL语句，分析并执行，观察输出结果。

1．Use 学生select 姓名,出生日期,专业 from 学生表 where 性别='男'2．use 学生select * from 课程表 where 课程名 like '%计算机%'3．use 学生select top 3 * from 成绩表 where 课程编号='001' order by 成绩 desc4．Use 学生select a.姓名,c.课程名,b.成绩 from 学生表 as a join 成绩表 as b on b.课程编号=001 and a.学号=b.学号 and b.成绩 between 80 and 90join 课程表 as c on c.课程编号=b.课程编号select * from 学生表 where 专业 in ('计算机软件','电子商务')6．use 学生select avg(成绩)as 平均成绩 from 成绩表 as a join 课程表 as bon b.课程名='计算机应用基础' and b.课程编号=a.课程编号select 课程编号,count(学号)as 修课人数 from 成绩表 group by 课程编号8．use 学生select * from 成绩表 where 课程编号=001compute max(成绩),min(成绩),avg(成绩)9．use 学生select a.姓名,b.课程名,c.成绩 from 学生表 as a join 成绩表 as con a.学号=c.学号 and a.性别='女'join 课程表 as bon b.课程编号=c.课程编号 and b.课程名='计算机应用基础'10．use 学生select a.学号,a.姓名,b.课程名,c.成绩 from 课程表 as b join 成绩表 as c on b.课程编号=c.课程编号 and c.课程编号=001 and c.成绩>(select avg(成绩) from 成绩表 where c.课程编号=001)join 学生表 as a on a.学号=c.学号select * from 成绩表 as a where 成绩>(select avg(成绩) from 成绩表 as b where a.课程编号=b.课程编号)12．use 学生select * from 课程表 where not exists(select * from 成绩表 where 成绩表.课程编号=课程表.课程编号)13．use 学生update 课程表 set 学分=学分+1 where 课程编号=001delete 成绩表 where 学号='20030101'。

sql实验习题答案SQL实验习题答案在学习SQL（Structured Query Language）时，习题是一种非常有效的学习方式。

通过实践操作，我们可以更好地理解SQL语言的各种概念和用法。

下面是一些常见的SQL实验习题及其答案，希望对大家的学习有所帮助。

1. 查询某个表的所有数据答案：SELECT * FROM 表名;2. 查询某个表的前n行数据答案：SELECT * FROM 表名 LIMIT n;3. 查询某个表中满足某个条件的数据答案：SELECT * FROM 表名 WHERE 条件;4. 查询某个表中某个字段的最大值答案：SELECT MAX(字段名) FROM 表名;5. 查询某个表中某个字段的最小值答案：SELECT MIN(字段名) FROM 表名;6. 查询某个表中某个字段的总和答案：SELECT SUM(字段名) FROM 表名;7. 查询某个表中某个字段的平均值答案：SELECT AVG(字段名) FROM 表名;8. 查询某个表中某个字段的记录数答案：SELECT COUNT(字段名) FROM 表名;9. 查询某个表中某个字段的记录数，并按照字段值进行分组答案：SELECT 字段名, COUNT(字段名) FROM 表名 GROUP BY 字段名;10. 查询某个表中满足多个条件的数据答案：SELECT * FROM 表名 WHERE 条件1 AND 条件2;11. 查询某个表中满足多个条件中的任意一个条件的数据答案：SELECT * FROM 表名 WHERE 条件1 OR 条件2;12. 查询某个表中满足某个条件，并按照某个字段进行排序的数据答案：SELECT * FROM 表名 WHERE 条件 ORDER BY 字段名;13. 查询某个表中满足某个条件，并限制结果的行数答案：SELECT * FROM 表名 WHERE 条件 LIMIT n;14. 查询某个表中满足某个条件，并跳过前n行的数据答案：SELECT * FROM 表名 WHERE 条件 OFFSET n;15. 查询某个表中满足某个条件，并按照某个字段进行分页显示答案：SELECT * FROM 表名 WHERE 条件 ORDER BY 字段名 LIMIT n OFFSET m;这些习题涵盖了SQL语言的基本操作和常用函数。

实验六高级查询【实验目的与要求】1、熟练掌握IN子查询2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替）4、熟练掌握复杂查询的select语句【实验准备】1．准备好测试数据2．熟悉多表查询与嵌套查询的用法。

【实验内容】5.1.嵌套子查询以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：（也可以不按指导书给出的思路写查询语句，只要是正确的即可，有疑问时可以和同学及老师商量你的查询语句是否正确）查询在2004年3月18日没有销售的产品名称（不允许重复）。

用IN子查询：写出对应SQL语句并给出查询结果：select distinct 产品名称from CPwhere 产品编号 not in(select 产品编号from CPXSBwhere 销售日期='2004-3-18');用EXISTS子查询：写出对应SQL语句并给出查询结果：select 产品名称from CPwhere not exists(select 产品编号from CPXSBwhere 销售日期='2004-3-12'and CP.产品编号=CPXSB.产品编号)select distinct 产品名称from CPwhere 产品名称!=all(select 产品名称from CPwhere exists(select 产品编号from CPXSBwhere 销售日期='2004-03-18' andCP.产品编号=CPXSB.产品编号))查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。

用IN子查询：写出对应SQL语句并给出查询结果：select 产品名称,数量from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号)where 客户编号 in(select 客户编号from XSSwhere 客户名称='家电市场')and 销售日期='2004-03-18'用EXISTS子查询：写出对应SQL语句并给出查询结果：select 产品名称,数量from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号)where CPXSB.客户编号 =(select 客户编号from XSSwhere 客户名称='家电市场')and exists(select distinct 产品名称from CPwhere 销售日期='2004-03-18' andCP.产品编号=CPXSB.产品编号)查询销售量大于所有2004年3月18日销售的各产品销售数量的产品编号。