SQL应用实例

来源:互联网 发布:海知智能 CTO 编辑:程序博客网 时间:2024/03/29 22:23

一、为管理岗位业务培训信息,建立3个表:

S (Sno,SN,SD,SA) Sno, SN, SD, SA 分别代表学号、学员姓名、所属单位、学员年龄

C (Cno,CN ) Cno,CN 分别代表课程编号、课程名称

SC ( Sno,Cno,G ) Sno,Cno,G 分别代表学号、所选修的课程编号、学习成绩

1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名

--实现代码:

Select SN,SD FROM S Where Sno IN( Select Sno FROM C,SC Where C.Cno=SC.Cno AND CN=N'税收基础')

2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

--实现代码:

Select S.SN,S.SD FROM S,SC Where S.Sno=SC.Sno AND SC.Cno='C2'

3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

--实现代码:

Select SN, SD FROM S Where Sno NOT IN( Select Sno FROM SC Where Cno='C5')

4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

--实现代码:

Select SN,SD FROM S Where [Sno] IN( Select [Sno] FROM SC RIGHT JOIN C ON SC.[Cno]=C.[Cno] GROUP BY [Sno] HAVING COUNT(*)=COUNT([Sno]))

5. 查询选修了课程的学员人数

--实现代码:

Select 学员人数=COUNT(DISTINCT Sno) FROM SC

6. 查询选修课程超过5门的学员学号和所属单位

--实现代码:

Select SN,SD FROM S Where Sno IN( Select Sno FROM SC GROUP BY Sno HAVING COUNT(DISTINCT Cno)>5)

二、问题描述:学生选课系统

S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER)
课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE)
选课关系。SCGRADE 为成绩


1.
找出没有选修过李明老师讲授课程的所有学生姓名
--
实现代码:
Select SNAME FROM S

Where NOT EXISTS( Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)


2.
列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
--
实现代码:
Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)

FROM S,SC,( Select SNO FROM SC Where SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2 )A

Where S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME


3.
列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
--
实现代码:
Select S.SNO,S.SNAME

FROM S, (Select SC.SNO FROM SC,C Where SC.CNO=C.CNO AND C.CNAME IN('1','2') GROUP BY SNO HAVING COUNT(DISTINCT CNO)=2 )SC

Where S.SNO=SC.SNO


4.
列出“1”号课成绩比“2”号课成绩高的所有学生的学号
--
实现代码:
Select S.SNO,S.SNAME

FROM S, ( Select SC1.SNO FROM SC SC1,C C1,SC SC2,C C2 Where SC1.CNO=C1.CNO AND C1.NAME='1' AND SC2.CNO=C2.CNO AND C2.NAME='2' AND SC1.SCGRADE > SC2.SCGRADE ) SC

Where S.SNO=SC.SNO


5.
列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--
实现代码:
Select S.SNO,S.SNAME,SC.[1
号课成绩],SC.[2号课成绩]

FROM S, (Select SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE FROM SC SC1,C C1,SC SC2,C C2 Where SC1.CNO=C1.CNO AND C1.NAME='1' AND SC2.CNO=C2.CNO AND C2.NAME='2' AND SC1.SCGRADE>SC2.SCGRADE )SC

Where S.SNO=SC.SNO

 

6.列出每科都大于90分的人得学号和姓名(腾讯笔试题)

select s.sno, s.sname

from s, sc,

(select sno from sc where grade >=90 group by sno having count(distinct cno)=(select count(cno) from c)) a

where s.sno=a.sno  and  sc.sno=a.sno group by s.sno,s.sname

 

三、问题描述:图书管理系统

   本题用到下面三个关系表:
CARD    
借书卡。   CNO 卡号,NAME  姓名,CLASS 班级
BOOKS   
图书。     BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
BORROW  
借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期

备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理:
 
1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。
 
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
 
3. 查询借阅了"水浒"一书的读者,输出姓名及班级。
 
4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
 
5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。
 
6. 查询现有图书中价格最高的图书,输出书名及作者。
 
7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。
 
8. 将"C01"班同学所借图书的还期都延长一周。
 
9. 从BOOKS表中删除当前无人借阅的图书记录。
 
10.如果经常按书名查询图书信息,请建立合适的索引。
 
11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。
 
12.建立一个视图,显示"01"班学生的借书信息(只要求显示姓名和书名)。
 
13.查询当前同时借有"计算方法""组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。
 
14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
 
15.对CARD表做如下修改:
    a.
NAME最大列宽增加到10个字符(假定原为6个字符)。
    b.
为该表增加1NAME(系名),可变长,最大20个字符。


1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束
--实现代码:
CREATE TABLE BORROW(
    CNO
int FOREIGN KEY REFERENCES
CARD(CNO),
    BNO
int FOREIGN KEY REFERENCES
BOOKS(BNO),
    RDATE
datetime
,
   
PRIMARY KEY
(CNO,BNO))

2.
找出借书超过5本的读者,输出借书卡号及所借图书册数
--实现代码:
SELECT CNO,借图书册数=COUNT(*)
FROM
BORROW
GROUP BY
CNO
HAVING COUNT(*)>5


3. 查询借阅了"水浒"一书的读者,输出姓名及班级
--实现代码:
SELECT * FROM CARD c
WHERE EXISTS
(
   
SELECT * FROM
BORROW a,BOOKS b
   
WHERE a.BNO=
b.BNO
       
AND b.BNAME=N'水浒'

       
AND a.CNO=c.CNO)

4.
查询过期未还图书,输出借阅者(卡号)、书号及还书日期
--实现代码:
SELECT * FROM BORROW
WHERE RDATE<GETDATE
()

5.
查询书名包括"网络"关键词的图书,输出书号、书名、作者
--实现代码:
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N'%网络%'


6.
查询现有图书中价格最高的图书,输出书名及作者
--实现代码:
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=
(
   
SELECT MAX(PRICE) FROM
BOOKS)

7.
查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出
--实现代码:
SELECT a.CNO
FROM
BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'

   
AND NOT EXISTS(
       
SELECT * FROM
BORROW aa,BOOKS bb
       
WHERE aa.BNO=
bb.BNO
           
AND bb.BNAME=N'计算方法习题集'

           
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC


8.
"C01"班同学所借图书的还期都延长一周
--实现代码:
UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM
CARD a,BORROW b
WHERE a.CNO=
b.CNO
   
AND a.CLASS=N'C01'


9.
BOOKS表中删除当前无人借阅的图书记录
--实现代码:
DELETE A FROM BOOKS a
WHERE NOT EXISTS
(
   
SELECT * FROM
BORROW
   
WHERE BNO=
a.BNO)

10.
如果经常按书名查询图书信息,请建立合适的索引
--实现代码:
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

11.
BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)
--实现代码:
CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE

AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=
b.BNO
   
AND b.BNAME=N'数据库技术及应用'


12.
建立一个视图,显示"01"班学生的借书信息(只要求显示姓名和书名)
--实现代码:
CREATE VIEW V_VIEW
AS

SELECT a.NAME,b.BNAME
FROM
BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=
a.CNO
   
AND ab.BNO=
b.BNO
   
AND a.CLASS=N'01'


13. 查询当前同时借有"计算方法""组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出
--实现代码:
SELECT a.CNO
FROM
BORROW a,BOOKS b
WHERE a.BNO=
b.BNO
   
AND b.BNAME IN(N'计算方法',N'组合数学'
)
GROUP BY
a.CNO
HAVING COUNT(*)=2

ORDER BY a.CNO DESC

14.
假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句
--实现代码:
ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)

15.1
NAME最大列宽增加到10个字符(假定原为6个字符)
--实现代码:
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

15.2
为该表增加1NAME(系名),可变长,最大20个字符
--实现代码:
ALTER TABLE CARD ADD 系名 varchar(20)

打电话通知面试的时候就已说明,要求上机做两道Sql Server面试题。

四、查询出销售表中,销售额大于本地区平均水平的记录,用一条sql语句

Sales

OrderID

Region

Total

1

A

100.00

2

C

80.00

3

A

130.00

4

B

90.00

5

B

100.00

6

C

120.00

7

A

90.00

8

C

90.00

9

B

80.00

Sql语句:

select * from sales as s , (select avg(total) as avge,region from sales group by region) avgtable on s.region = avgtable.region where total > avgtable.avge 

五、他们公司网站上的广告位是轮播的,每天某一广告位最多可轮播的广告数量是有限制的,比如A广告位,每天只能轮播三个广告,但销售人员在销售广告位时并不考虑此限制,要求查询出合同表中,超过广告位轮播数量的合同。

合同表 Orders

OrderID

Positioncode

Startdate

Enddate

1

A

2006-11-01

2006-11-03

2

C

2006-11-02

2006-11-03

3

B

2006-11-01

2006-11-04

4

A

2006-11-03

2006-11-04

5

C

2006-11-01

2006-11-02

6

B

2006-11-02

2006-11-05

7

A

2006-11-02

2006-11-03

8

A

2006-11-04

2006-11-05

9

C

2006-11-03

2006-11-04

10

C

2006-11-02

2006-11-04

广告位表 Product

Positioncode

Showcount

A

2

B

1

C

3

说明:对于广告位A来讲,轮播情况如下表

OrderID

2006-11-01

2006-11-02

2006-11-03

2006-11-04

2006-11-05

1

 

 

 

 

 

4

 

 

 

 

 

7

 

 

 

 

 

8

 

 

 

 

 

广告位A每天最多可轮播2个广告,但合同表中在2006-11-03这天有三个广告(147),对于广告位A147则是最终需要得到的结果。如需要可使用临时表、存储过程等。

可能当时也有点紧张吧,这道题面试的时候弄了两个多小时,还是没有解决,最终只好放弃了。不过还是不死心,回家后又仔细研究了一下,终于给解决了,使用了存储过程,但不知道还有没有更好的方式,过程过下。
    
create proc overcontract
    as
    declare @mindate smalldatetime
    declare @days int
    declare @temptable table ( orderid int)

set @mindate = (select min(startdate) from orders)
     set @days = (select datediff(d,min(startdate),max(enddate)) from orders)

while (@days>-1)
       begin
              declare @curdate smalldatetime
              set @curdate = dateadd(d,@days,@mindate) 

              insert into @temptable select o.orderid from product as p inner join
 (select count(positioncode) as total,positioncode from orders where @curdate between startdate and enddate group by positioncode ) dt on dt.positioncode = p.positioncode left join orders o on o.positioncode = p.positioncode 
where total>p.showcount and @curdate between startdate and enddate

       set @days = @days-1    
        end
       select distinct(orderid) from @temptable 
     go