SQL习题.docx

1、SQL习题SQL习题商品销售数据库Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int) Customer(顾客号char (4)，顾客名 char (8)，性别 char(2)，年龄 int)OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, 日期 date) 1用SQL建立三个表，须指出该表的实体完整性和参照完整性，对性别和年龄指出用户自定义的约束条件。（性别分成男女，年龄从10到100）。顾客表的数据用插入语句输入数据，其它两表可用任意方式输入数据。create table OrderItem (顾

2、客号 char(4),商品号 char(4),日期 datetime,数量 smallint, primary key (顾客号,商品号,日期), foreign key (商品号) references Article (商品号), foreign key (顾客号) references Custommer(顾客号) );2检索定购商品号为0001的顾客号和顾客名。 select distinct 顾客号 from OrderItemwhere 商品号=0001 or 商品号=0002; 3检索定购商品号为0001或0002的顾客号。 select distinct 顾客号 from Or

3、derItemwhere 商品号=0001 or 商品号=0002; 4检索至少定购商品号为0001和0002的顾客号。(用交的方法) select顾客号 from OrderItem where 商品号=0001 and 顾客号 in( select 顾客号 from OrderItem where 商品号=0002); 5检索至少定购商品号为0001和0002的顾客号。(用自表连接方法) select X. 顾客号 from OrderItem X,OrderItem Y where X.顾客号=Y.顾客号 and X.商品号=0001 and Y. 商品号=0002; 6检索没定购商品的

4、顾客号和顾客名。 select 顾客号,顾客名 from Custommer where 顾客号 not in (select 顾客号 from OrderItem); 7检索一次定购商品号0001商品数量最多的顾客号和顾客名。 select 顾客号,顾客名 from Custommer where 顾客号 in(select 顾客号 from OrderItem where 商品号=0001and 数量=(select MAX(数量)from OrderItem where 商品号=0001);8检索男顾客的人数和平均年龄。 select count(*) 人数,avg(年龄) 平均年龄 f

5、rom Custommer where 性别=男; 9检索至少订购了一种商品的顾客数。 select count(distinct 顾客号) from OrderItem; 10检索订购了商品的人次数。select count( 顾客号) from OrderItem;select count(distinct 顾客号) from OrderItem; 11检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。 selectsum( 数量),MAX(数量)-MIN(数量) from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客

6、号 and 顾客名=张三;12检索至少订购了3单商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并按商品总数量降序排序。 select Custommer.顾客号,顾客名,count( *),Sum(数量) from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客号group by Custommer.顾客号,顾客名having count( *)3 order by 4 desc;13检索年龄在30至40岁的顾客所购买的商品名及商品单价。 select 商品名,单价 from Custommer,Article,OrderIte

7、mwhere Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商品号and 年龄 between 30 and 40;14创建一个视图GM，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量*单价）。指定用内连接方式做。 create view GM asselect Custommer.顾客号,顾客名,商品名,日期,单价*数量 as 金额from Custommer,Article,OrderItemwhere Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商

8、品号 create view GM1 asselect Custommer.顾客号,顾客名,商品名,日期,单价*数量 as 金额from (Custommer inner join OrderItem on Custommer.顾客号=OrderItem.顾客号)inner join Article onArticle.商品号=OrderItem.商品号15检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。 select Custommer.顾客号,顾客名from Custommer,OrderItem,Article whereCustommer.顾客号=OrderItem

9、.顾客号 andArticle.商品号=OrderItem.商品号and 单价100016检索购买的购买价都高于或等于1000元的顾客号和顾客名。 select Custommer.顾客号,顾客名 from Custommer where 顾客号 in (select 顾客号 from OrderItem where 顾客号 not in(select 顾客号 from OrderItem,Article where OrderItem.商品号=Article.商品号 and 单价10)23.建立断言：顾客的年龄必须大于18岁。 Create ASSERTION A1 check ( not

10、exists (select * from Custommer where 年龄53. 查询借阅了水浒一书的读者，输出姓名及班级-实现代码：SELECT * FROM CARD cWHERE 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 RDATE0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BO

11、OKS bWHERE i.BNO=b.BNO AND b.BNAME=N数据库技术及应用12. 建立一个视图，显示力01班学生的借书信息（只要求显示姓名和书名）-实现代码：CREATE VIEW V_VIEWASSELECT a.NAME,b.BNAMEFROM BORROW ab,CARD a,BOOKS bWHERE ab.CNO=a.CNO AND ab.BNO=b.BNO AND a.CLASS=N力0113. 查询当前同时借有计算方法和组合数学两本书的读者，输出其借书卡号，并按卡号升序排序输出-实现代码：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a

12、.BNO=b.BNO AND b.BNAME IN(N计算方法,N组合数学)GROUP BY a.CNOHAVING COUNT(*)=2ORDER BY a.CNO DESC14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句-实现代码：ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）-实现代码：ALTER TABLE CARD ALTER COLUMN NAME varchar(10)15.2 为该表增加1列NAME（系名），可变长，最大20个字符-实现代码：ALTER TABLE CARD ADD 系名 varchar(20)