读书时sql语句的整理（2）

   将下载的exec.sql在查询分析器中执行一次，创建一些查询语句中需要使用到的表，然后将.mdb中的数据使用sql server 中的企业管理器导入。接下来就是按要求完成sql查询的语句啦。

 

use pubs

select name from dbo.sysobjects where xtype='u' and (not name LIKE 'dtproperties')
/*列出当前数据库中的所有表，xtype char(2) 对象类型。可以是下列对象类型中的一种： 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程*/

select * into new_jobs from jobs where 1<>1--复制表结构

insert into new_jobs(job_desc,min_lvl, max_lvl) select job_desc,min_lvl,max_lvl from jobs--拷贝表数据

select * from new_jobs--查询表

delete * from new_jobs--删除表

select job_desc,min_lvl, max_lvl from new_jobs where job_id=(select job_id from jobs where job_id=1 ) --子查询

select job_desc,min_lvl, max_lvl from new_jobs where job_id in (select job_id from jobs) --子查询

select stu_name from stu where stu_id in(select stu_id from sk where stu_id=1)--在sk表中查询学生编号是1的学生姓名

--查询有选修课程名称为’语文’的所有学员学号和姓名
SELECT * FROM stu WHERE stu_id IN(SELECT stu_id FROM sk,kc WHERE kc.kc_id=sk.kc_id AND kc.kc_name='语文')

--查询有选修2门课程以上的学生信息 注意：group by 一般和聚合函数一起使用,in中不能包含两个以上表达式
select * from stu where  stu_id in(select stu_id from sk group by stu_id having count(kc_id)>2)

GO
--计算stor_id为7131客户的总订货数量
select sum(qty) as '总订货数量' from sales where stor_id=7131

--统计每个客户分别下了几张订单,以及他们分别订了多少数量的货物
select stor_id,count(ord_num) as '订单数',sum(qty) as '货物数量' from sales group by stor_id

--根据订单量统计平均订货量
select sum(qty)/count(distinct ord_num)as '平均订货量' from sales

--找出订货量最大的一笔订单
select ord_num,max(qty) as '最大订单' from sales  group by ord_num

select top 1 ord_num,max(qty) as '最大订单' from sales  group by ord_num order by 最大订单 desc

--找出书名为“The Gourmet Microwave”的书是否有销售
select stor_id,ord_num,qty,ord_date from sales inner join titles on sales.title_id=titles.title_id where titles.title='The Gourmet Microwave'

--请查找书名中含有引号的书籍
select * from titles where title like '%''%'

--请计算每种类型的书籍的销量（类型字段名为type）
select * from titles
select type,sum(qty) as '销量' from titles right join sales on titles.title_id=sales.title_id group by type

--请找出销量最好的书籍是哪种类型（类型字段名为type）
select top 1 type, sum(qty) as '销量' from titles right join sales on titles.title_id=sales.title_id group by type order by 销量 desc

--计算出每张定单距今天有多少天
select  *, datediff (day,ord_date,getdate())as 距现在多少天 from sales

--找出在1994年9月销售的书籍名称
select datepart(year,ord_date) as 年份,datepart(month,ord_date)as 月份 from sales  order by 年份 desc

--每种类型（类型字段名type）的书籍各有几本
select type,count(title_id) as '该类型书本数量' from titles group by type 

--查询不及格的学员的姓名和科目、分数
select students.stuName,course.courseName,score.chengji from students inner join score on students.stu_id=score.stu_id inner join course on course.course_id=score.course_id
where score.chengji<60

--统计学员的所有考试科目的平均成绩，返回平均最高的学员的姓名和平均分
select top 1 avg(chengji) as '平均成绩',stuName as '姓名' from score inner join students on score.stu_id=students.stu_id group by stuName order by 平均成绩 desc

--查找每个科目的最高分，返回该学员的姓名和成绩
select * from students where students.stu_id in( select score.stu_id from score  where chengji in(select max(chengji) as chengji from score group by course_id)group by score.stu_id)

select students.stu_id,students.stuName,score.chengji from students,score where students.stu_id=score.stu_id and students.stu_id in( select score.stu_id from score inner join students on score.stu_id=students.stu_id where chengji in(select max(chengji) as chengji from score group by course_id)group by score.stu_id) group by students.stuName

--统计每个科目的考生数量
select count(stu_id) as '每科考生数量' from score group by course_id

--内联接

--查看6380客户所定书的书名以及该书由哪个出版社出版的
select stor_id,title,pub_name from sales inner join titles 
on sales.title_id=titles.title_id
inner join publishers 
on titles.pub_id=publishers.pub_id 
where stor_id=6380

/*左右外联接 返回符合搜索条件的所有记录，表与表中的行不匹配时所返回的结果集，将为没有相应记录的表提供 NULL 值。
（即以一个表为主，列出该表的所有记录，另一个表则只列出与前一个表条件相等的值，不相等的部分用null补充）*/
--查询没有销售的书籍
select * from titles left join sales 
on sales.title_id=titles.title_id 
where stor_id is null

select titles.* from sales right join titles 
on sales.title_id=titles.title_id 
where stor_id is null