资源描述:
《数据库概论实验三、四答案》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、--实验三--1createtablekcb(Cnochar(4)constraintzjprimarykey,Cnamechar(40),Cpnochar(4)constraintwjforeignkeyreferenceskcb(cno),Ccreditsmallint);insertintokcb(Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);--2altertableStudentaddphonechar(7)default'1111111';--3altertablekcbdropconstraintwj,zj--4
2、altertableCourseaddcheck(class_hour>=Ccredit*16);--5alterdatabase学生管理数据库modifyfile(name='students_data',filegrowth=3mb);--6sp_renamedb学生管理数据库,学生管理--7backupdatabase学生管理todisk='c297'--9createuniqueindexCoucnoonCourse(Cno);--实验四--1select*fromstudentselect*fromSC--2selectSname,Sagefromstudent
3、whereSdept='计算机系';--3selectSno,GradefromSCwhereCno='c01';--4select*fromSCwhereGrade>70andGrade<80--5selectSname,SagefromstudentwhereSdept='计算机系'andSsex='男'andSage>18andSage<20--6select*fromSCwhereSno='0412101'--7selectmax(Grade)maxfromSCwhereCno='c01'--8selectdistinctCnofromSC--9selectOrde
4、rID,CustomerID,OrderDatefromOrdersORDERBYOrderDateDESC--10selectOrderID,CustomerID,ShipCountryfromOrderswhereShipCountrylike'B_a%'orShipCountrylike'C_a%'orShipCountrylike'D_a%'orShipCountrylike'F_a%'--11selectOrderID,CustomerID,ShipCountryfromOrderswhereShipCountrynotlike'A%a'andShipCountr
5、ynotlike'B%a'andShipCountrynotlike'C%a'andShipCountrynotlike'D%a'andShipCountrynotlike'E%a'andShipCountrynotlike'F%a'--12selectmax(Sage)max,min(Sage)minfromstudent--13selectavg(Grade)avg,max(Grade)max,min(Grade)minfromSCwhereCno='c02'--14selectSdept,count(*)renshufromstudentGroupbySdept--1
6、5selectCno,count(*)renshu,max(Grade)maxGradefromSCgroupbyCno--16selectSno,count(Sno)SnumfromSCgroupbySnoorderbycount(Sno)asc--17selectCnofromSCgroupbyCnohavingcount(Grade)=0--18selecttop2withtiesSname,SagefromstudentwhereSdept='计算机系'orderbySagedesc