《[工学]数据库原理及应用孟彩霞版实验38.doc》由会员分享，可在线阅读，更多相关《[工学]数据库原理及应用孟彩霞版实验38.doc（32页珍藏版）》请在三一办公上搜索。
1、西安邮电大学 网络工程 孟彩霞版数据库原理及应用 课后实验三八试验3：插入：INSERT INTO 学生表StudentVALUES (201010,李男,男,21,135725556,IS)修改：UPDATE 学生表StudentSET Age=Age+1 删除： DELETE FROM 学生选课表SCWHERE Cno=5 试验四：1.1SELECT Sno,Sname,SdeptFROM 学生表StudentSELECT *FROM 学生表StudentSELECT Sname,2008-AgeFROM 学生表Student；SELECT Sname,Year of Birth,2008
2、-AgeFROM 学生表Student;SELECT Sname,2008-Age BirthdayFROM 学生表Student;SELECT Sno,Sname,LOWER(Sdept)FROM 学生表Student;SELECT DISTINCT SnoFROM 学生选课表SC;SELECT Sno,SnameFROM 学生表StudentWHERE Sdept=CS;SELECT SnoFROM 学生选课表SCWHERE Grade=20 AND Age=23;SELECT Sname,AgeFROM 学生表StudentWHERE Sdept IN (CS,MA,IS);SELECT
3、 Sname,AgeFROM 学生表StudentWHERE Sdept NOT IN (CS,MA);SELECT *FROM 学生表StudentWHERE Sdept LIKE IS;SELECT Sname,Sno,SexFROM 学生表StudentWHERE Sname LIKE *_*%;SELECT Cno,creditFROM 课程表CourseWHERE Cname LIKE 数据库 ESCAPE ; SELECT Cno,SnoFROM 学生选课表SCWHERE Grade IS NULL; SELECT Sno,Sname,SexFROM 学生表StudentWHERE
4、 Sdept=CS AND Age90; 2.2Select cname,goodsname,price,quantity,ordersumfrom customers,goods,orderswhere customers.customerid=orders.customeridand goods.goodsid=orders.goodsidand orderdate between2012-7-1and2012-11-11and ordersum30000;2.3select sno from SPJ,Pwhere SPJ.pno=P.pnoand SPJ.jno=J1and P.colo
5、r=红;试验5：（1）40.select Snamefrom 学生表Student ,学生选课表SCwhere 学生表Student.Sno = 学生选课表SC.Sno and Cno=1;41.select Sno,Snamefrom 学生表Student where Sdept = (SELECT Sdept from 学生表Student WHERE Sname = .);42.select Sname,Agefrom 学生表Studentwhere Sdept CS and Age =(select avg(Grade) from 学生选课表SC ywhere y.Cno=x.Cno)
6、;44.select Snamefrom 学生表Studentwhere exists (select * from 学生选课表SC where Sno=学生表Student.Sno and Cno=3);45.select Snamefrom 学生表Studentwhere not exists (select * from 课程表Course where not exists (select * from 学生选课表SC where Sno=学生表Student.Sno and Cno=课程表Course.Cno);46.select distinct Snofrom 学生选课表SC SC
7、Xwhere not exists (select * from 学生选课表SC SCY where SCY.Sno=201004 and not exists (select * from 学生选课表SC SCZ where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno);47.select Sno from 学生选课表SCwhere Cno=1unionselect Snofrom 学生选课表SCwhere Cno=2; （2）6. select CustomerID,GoodsID,OrderIDfrom Orderswhere Quantity200order
8、 by OrderDate; 7.select *from Orderswhere CustomerID in (select CustomerID from Customers where City=北京);8.select *from Orderswhere CustomerID not in (select CustomerID from Customers where City=天津);9.select Customers.CustomerID,CName,City,OrderID, GoodsID,Quantity,OrderSum,OrderDatefrom Customers l
9、eft outer join Orders on Customers.CustomerID=Orders.CustomerIDwhere City=西安;(3)5.3-4select jno from J where jno not in(select jnofrom SPJ,S,Pwhere SPJ.sno=S.snoand SPJ.pno=P.pnoand city=天津and color=红);5.3-5SELECT DIST PNO FROM SPJ WHERE SNO=S1SELECT JNO FROM SPJ WHERE PNO=P1AND JNO IN (SELECT JNO F
10、ROM SPJ WHERE PNO=P2)(4)试验612 （1）54Create view c_学生表Studentasselect Sno,Sname,Sex,Agefrom 学生表Studentwhere Sdept=CSwith check option55create view 学生表student_course_2asselect 学生表Student.Sno,Sname,Sex,Age,Cno,Gradefrom 学生表Student,学生选课表SCwhere 学生表Student.Sno=学生选课表SC.Sno andGrade=80 and Cno=256create vie
11、w 学生表student_birth(Sno,Sname,Sbirth)asselect Sno,Sname,2008-Agefrom 学生表Student57select Sno,Snamefrom c_学生表studentwhere Age between 18 and 2258select Sno,Snamefrom 学生表student_course_2where Sex=女 and Grade=9059update c_学生表studentset Sname = 张良where Sno=2010106061DELETEFROM c_学生表studentWHERE Sno= 20100
12、7;（2）1）CREATE VIEW Bj_orders(orderid,goodsname,quantity,customerid,cname)ASselect orderid,goodsname,quantity,orders.customerid,cname from orders,customers,goodswhere customers.customerid=orders.customeridand goods.goodsid=orders.goodsidand city=beijing2）Select*from Bj_orderswhere Cname=张三 试验71） JWGL
13、第四章，例1，371. create procedure 学生表student_courseasselect 学生表student.Sno,Sname,Cname,Gradefrom 学生表student,课程表Course,学生选课表SCwhere 学生表student.Sno=学生选课表SC.Sno and 课程表Course.Cno=学生选课表SC.Cno and Sname=刘伟;3. create procedure 学生表student_course1StudentName varchar(10)asselect 学生表student.Sno,Sname,Cname,Gradefr
14、om 学生表Student,课程表Course,学生选课表SCwhere 学生表student.Sno=学生选课表SC.Sno and 课程表Course.Cno=学生选课表SC.Cno and Sname=StudentName;4. create procedure 学生表student_insertsno char(8),sname varchar(10),sex char(2),age int,sdept varchar(10),phonenumber char(20)asinsert into 学生表studentvalues(sno,sname,sex,age,sdept,phon
15、enumber);5.create procedure 学生表student_course3StudentName varchar(10)=nullasif StudentName is nullbeginselect 学生表Student.Sno,Sname,Cname,Gradefrom 学生表Student,课程表Course,学生选课表SCwhere 学生表student.Sno=学生选课表SC.Sno and 课程表Course.Cno=学生选课表SC.Cnoendelsebeginselect 学生表Student.Sno,Sname,Cname,Gradefrom 学生表Stud
16、ent,课程表Course,学生选课表SCwhere 学生表student.Sno=学生选课表SC.Sno and 课程表Course.Cno=学生选课表SC.Cno and Sname=StudentNameend6. create procedure 学生表student_countcourseName varchar(20),sex char(2),StudentSum int outputas select StudentSum=count(*) from 课程表Course,学生选课表SC where 课程表Course.Cno=学生选课表SC.Cno and Cname=Cours
17、eName;7.2） Market第四章，习题5，141. create procedure sp_shanghaiasselect *from cusromerswhere city=shanghai;2.3.4. create procedure insertgoodsgoodsid char(10),Goodsname varchar(20),price char(19),description varchar(200),storage int,provider varchar(50),status int as insert into goodsvalues(goodsid ,Goodsname ,price,storage,status,description,provider);3）实验8（1） JWGL第四章，例8138.9.10.11.12.13（2） Market第四章，习题5，595.6.7.8.9.
