SQL SERVER实验-- 数据检索

实验3  数据检索

一、实验目的

（1）理解T-SQL语言的使用。

（2）熟练掌握数据查询语句。

SELECT [ALL|DISTINCT] {*|<表达式>,…,<表达式>}

FROM <表名>[, <表名>…]

[WHERE <条件>]

[GROUP BY <列名>[, <列名>…][HAVING <谓词>]]

[ORDER BY <列名> [ASC|DESC] [, <列名> ASC|DESC]…]

[COMPUTE …]

 

二、实验内容

1、 [3.2]b  USE CAP
select aid from AGENTS where [percent]=(select max([percent] )from AGENTS
[3.5]  Select cid,aid from customers c, agents a  Where cid not in (select cid from orders x
Where x.cid = c.cid and x.aid = a.aid)  And
aid not in (select aid from orders v  Where v.aid = a.aid and v.cid = c.cid)
[3.8]a   select cid,max(dollars) as MAXSPEND from orders group by cid
[3.8]b   select avg(MAXSPEND) average,cid from  (select cid,max(dollars) as MAXSPEND from orders group by cid)as S group by cid
[3.11]b  Select avg(temp.maxspend)avgspend,temp.cid
from (select cid,max(dollars) as maxspend from orders  
Group by cid) as temp group by temp.cid
[3.11]f  select pid from products p
where not exists(select cid from customers c where city='Duluth'
and not exists (select * from orders o where o.cid=c.cid and o.pid=p.pid))
[3.11]j  update products
set price=price*1.1 where city='Duluth' or city='Dallas'
[3.11]l  select aid,[percent] from agents a where not exists(select * from customers c
where city='Duluth' and not exists
(select * from orders o where o.cid=c.cid  and o.aid=a.aid)) order by [percent]DESC

<选做>[3.15]、[3.16]、[3.17]

 

2、在“学生管理数据库”中建立四张表，分别如下：

学生表：（主键：学号）

学号	姓名	性别	年龄	所在院系	班级	入学日期
20009001	葛文卿	女	22	国际贸易	国贸2班	2000－8－29
20014019	郑秀丽	女	21	会计学	会计1班	2001－9－2
20023001	刘成铠	男	18	计算机	软件2班	2002－8－27
20026002	李涛	女	19	电子学	电子1班	2002－8－27
20023002	沈香娜	女