数据库上机报告.doc

数据库SQL上机实验报告书姓名:学号:班级:实验一:设计数据库、数据表并编程实现建立student表createtableStudent(snochar(9)Primarykey,snamechar(10)constraintc1notnull,sbirthdayDateTime,ssexchar(2)constraintc2check(ssexin('男','女')),sclasschar(20),sremarkchar(100),adresschar(40),zipcodechar(6),phonechar(15),emailchar(40));建立course表:ochar(6)amechar(20),cpnochar(6),ctimeNumeric(2),creditNumeric(2),Foreignkey(cpno)o));建立score表:createtableScore(snochar(9),cnochar(6),scoreNumeric(3),Primarykey(o),Foreignkey(sno)ReferencesStudent(sno),o)o));建立Teacher表:createtableTeacher(tnochar(7)primarykey,tnamechar(10),tsexchar(2),tbirthdayDateTime,positionchar(12),departmentchar(16),tamountNumeric(7,2),experiencechar(200));建立Teachering表createtableTeaching(tnochar(7),cnochar(6),tdateDatetime,classroomchar(10),sclasschar(20),Primarykey(o),Foreignkey(tno)ReferencesTeacher(tno),o)o),)实验二:设计数据插入、修改、删除、查询和视图等操作并编程实现(1)根据以下给定的部分数据表信息,分别对student,course,score,teacher,teching表进行数据插入。Student表插入语句:InsertintoStudent(sno,sname,sbirthday,ssex,sclass,sremark,adress,zipcode,phone,email)Values(011110101,章海潮,,信管系0101)Course表插入语句:ame,cpno,ctime,credit)Values(C001,数据库原理,C005,4,64,)Score表插入语句:InsertintoScore(o,score,)Values(0111101,C001,90)Teacher表插入语句:InsertintoTeacher(tno,tname,tsex,tbirthday,position,department,tamount,experience)Values(T001,江承基,男,信息管理系,)Teachering表插入语句:InsertintoTeachering(o,tdate,classroom,sclass)Values(T001,C005,2012-01-09,西二405,信管系0101)(2)查询全体学生的详细记录;select*fromStudentleftouterjoinScoreon(=);(3)查询全体学生的学号与姓名;selectsno,snamefromStudent(4)查询全体学生的学号、姓名、所属班级,相同班级列在一起;selectsno,sname,sclassfromStudentorderbysclassDESC;(5)查询全体学生的姓名、出生年份;selectsname,sbirthdayfromStudent;(6)查询全体学生的姓名及其年龄;selectsname,year(getdate())-year(sbirthday)fromStudent;(7)查询“信管系0101”班全体学生名单;selectsnamefromStudentwheresclass='信管系0101';(8)查询查询所有年龄在27岁以下的学生姓名及其年龄;selectsname,year(getDate())-year(sbirthday)fromStudentwhereyear(getdate())-year(sbirthday)<30(9)查询考试成绩有不及格的学生的学号;,=Scor