SQL 经典题目1

转自  lenotang 老师的专栏   在此表示感谢


应学员们的需求,我这里列出一些外面公司笔试用的的SQL题目及答案,供大家学习参考:

.有下列三张表:

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.为该表增加1列NAME(系名),可变长,最大20个字符。

 

答案:

 

1.写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。

CREATE TABLE BORROW

(CNO CHAR(20),

BNO CHAR(20),

RDATE DATETIME);

ALTER TABLE BORROW ADD CONSTRAINT BORROW_CNO_BNO_PK PRIMARY KEY

(CNO,BNO);

ALTER TABLE BORROW ADD CONSTRAINT BORROW_CNO_FK FOREIGN KEY(CNO) 

REFERENCES CARD(CNO);

ALTER TABLE BORROW ADD CONSTRAINT BORROW_BNO_FK FOREIGN KEY(BNO) 

REFERENCES BOOKS(BNO);

 

2.找出借书超过5本的读者,输出借书卡号及所借图书册数。

SELECT C.CNO,COUNT(C.CNO) FROM BORROW C GROUP BY(C.CNO);

 

3.查询借阅了"水浒"一书的读者,输出姓名及班级。

SELECT A.NAME,A.CLASS

FROM CARD A,BOOKS B ,BORROW C

WHERE A.CNO=C.CNO

AND B.BNO=C.BNO

AND B.BNAME='水浒';

 

4.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。

  ORACLE:SELECT * FROM BORROW C WHERE RDATE< SYSDATE;

SQL SERVER:SELECT * FROM BORROW C WHERE RDATE<GETDATE() ;

 

5.查询书名包括"网络"关键词的图书,输出书号、书名、作者。 

SELECT B.BNO,B.BNAME,B.AUTHOR FROM BOOKS B WHERE B.BNAME LIKE '%网络%';

 

6.查询现有图书中价格最高的图书,输出书名及作者。

SELECT * FROM BOOKS B WHERE B.PRICE IN (SELECT MAX(PRICE) FROM  BOOKS);

 

7.查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。

SELECT C.CNO

FROM BOOKS B, BORROW C

WHERE B.BNO=C.CNO

AND B.BNAME LIKE '计算方法' 

AND B.BNAME NOT LIKE '计算方法习题集'

ORDER BY C.CNO DESC;

 

8."C01"班同学所借图书的还期都延长一周。 

UPDATE BORROW SET RDATE = DATEADD(WEEK,1,RDATE)

WHERE CNO IN(SELECT CNO FROM CARD WHERE CLASS = 'C01')

或者

UPDATE BORROW SET RDATE=

(SELECT C.RDATE+7

FROM CARD A ,BORROW C 

WHERE C.BNO=A.BNO AND A.CLASS='C01');

 

9.BOOKS表中删除当前无人借阅的图书记录。 

DELETE  FROM  Books

 WHERE Books.BNO NOT IN 

 (SELECT DISTINCT C.BNO from BORROW C )

 

10.如果经常按书名查询图书信息,请建立合适的索引。 

CREATE INDEX BOOKS_INDEX on BOOKS(BNAME);

 

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

CREATE OR REPLACE TRIGGER BORROW_Insert_Trigs

BEFORE INSERT ON BORROW

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

DECLARE

V_BAME BOOKS.BNAME%TYPE;

BEGIN

SELECT BNAME INTO V_BNAME FROM BOOKS WHERE BNO=:NEW.BNO; 

IF V_NAME='数据库技术及应用' THEN

INSERT INTO ORROW_SAVE VALUES(:NEW.BNO,:NEW.BNAME,:NEW.AUTHOR,:NEW.PRICE,:NEW.QUANTITY)

END;

 

12.建立一个视图,显示"C01"班学生的借书信息(只要求显示姓名和书名)。

CREATE OR REPLACE VIEW ABC_VIEW

AS SELECT A.NAME,B.BNAME ]

FROM CARD A,BOOKS B,BORROW C

WHERE A.CNO=C.CNO

AND B.BNO=C.BNO

AND A.CLASS='C01')

 

13.查询当前同时借有"计算方法""组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。 

SELECT A.CNO  FROM CARD A

WHERE  A.CNO  IN

(SELECT DISTINCT C.CNO 

FROM BORROW C,BOOKS B 

WHERE C.BNO=B.BNO

AND B.BNAME IN('计算方法','组合数学') )

ORDER BY A.CNO;

 

14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句

ALTER TABLE BOOKS ADD CONSTRAINT BOOKS_BNO_PK PRIMARY KEY(BNO);

 

15.CARD表做如下修改: 

  a.将NAME最大列宽增加到10个字符(假定原为6个字符)。  b.为该表增加1列NAME(系名),可变长,最大20个字符。

  ALTER TABLE CARD MODIFY (NAME CHAR(10));

ALTER TABLE CARD ADD  NAME_P VARCHAR(20);

 

.有下列三张表:

 

S (SNO,SNAME          学生关系。SNO 为学号,SNAME 为姓名

C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE)     选课关系。SCGRADE 为成绩

1. 找出没有选修过许亮老师讲授课程的所有学生姓名

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

3. 列出既学过“1”号课程,又学过“2”号课程的所有学生学号及姓名

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号及姓名

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

答案:

1. 找出没有选修过许亮老师讲授课程的所有学生姓名

SELECT SNAME

FROM S

WHERE SNO NOT IN

(SELECT SNO FROM SC WHERE CNO IN(SELECT CNO FROM C WHERE CTEACHER = '许亮'))

 

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

SELECT S.SNO,S.SNAME,平均成绩=AVG(SC.SCGRADE)

FROM S,SC,(SELECT SNO FROM SC WHERE SCGRADE < 60 GROUP BY SNO HAVING

COUNT(DISTINCT CNO)>=2) as 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.CNO

 IN('1','2')GROUP BY SC.SNO

    HAVING COUNT(DISTINCT SC.CNO)=2) SC

  WHERE S.SNO=SC.SNO

 

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号及姓名

SELECT S.SNO,S.SNAME

FROM S,SC AS SC1,SC AS SC2

    WHERE SC1.CNO='1'

        AND SC2.SNO='2'

        AND SC1.CNO=SC2.CNO

        AND SC1.SCGRADE > SC2.SCGRADE AND SC1.SNO=S.SNO

 

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE

FROM SC SC1,SC SC2

WHERE SC1.CNO='1'

        AND SC2.CNO='2'

        AND SC1.SNO= SC2.SNO

        AND SC1.SCGRADE > SC2.SCGRADE

 

三:按照给定信息写ORACLE数据库的SQL语句

 

1:有下列信息表:

   T_CUST

CUST_NO

CUST_TYPE

1

1

2

A

3

M

4

C

5

A

6

M

7

3

  其中, CUST_NO客户编号,CUST_TYPE客户类型,1,A表示全球通,3,C表示神州行,M表示动感地带,要求写出一条SQL语句得到以下结果:

客户类型

人数统计

全球通

3

神州行

2

动感地带

2

 

2:打开Oracle中的EMP表:

编号

工资

部门

001

5000.00

10

002

3300.00

20

003

3300.00

20

 

要求写出一条SQL语句,得到每个部门工资排名前三位的员工信息

 

3:有下列两张表:

T_ITEM(商品表)

商品编号

商品数量

001

200

002

300

003

600

 T_SALE(出货表);

出货单号

商品编号

出货数量

出货日期

1

001

10

2002-02-02

2

002

20

2003-02-02

3

002

30

2002-05-02

 要求写出一条SQL语句,得出商品的剩余库存量(如下表

商品编号

库存数量

001

190

002

250

003

600

 

1

select a.用户类型, count(*) as 人数统计 from( select decode(cust_type,'A','全球通','1','全球通','3','神州行','C','神州行','动感地带') 用户类型  from t_cust ) a group by 用户类型

2

select * from(

select empno,sal,deptno, dense_rank() over(partition by deptno order by

sal desc) 名次 from emp)  where 名次<4

3

select a.商品编号,NVL((a.商品数量-b.出货数量),a.商品数量) as 库存数量

from  T_ITEM a left join (select 商品编号,sum(出货数量) 出货数量 from T_SALE group by 商品编号) b on a.商品编号=b.商品编号

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值