数据库上机实验报告Word格式.docx
- 文档编号:20957695
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:25
- 大小:325.61KB
数据库上机实验报告Word格式.docx
《数据库上机实验报告Word格式.docx》由会员分享,可在线阅读,更多相关《数据库上机实验报告Word格式.docx(25页珍藏版)》请在冰豆网上搜索。
snochar(20)primarykey,
snamechar(10)unique,
ssexchar
(2),
sagesmallint,
sdeptchar(12)default'
cs'
)
createtablecourse(
cnochar(10)primarykey,
cnamechar(20),
cpnochar(10),
ccreditchar(6)
createtablesc(
snochar(20),
cnochar(10),
gradesmallint,
primarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),
foreignkey(cno)referencescourse(cno)
实验二、
在实验一的结果上进行各种操作练习及其代码
一修改表结构,对学生表增加,电话号码一列,删除年龄一列。
altertablestudent
addpnumberchar(22)
dropcolumnsage
二简单查询
例1.查询全体学生详细记录
select*
fromstudent
例2.查询信息系所有男生的学号、姓名、出生年份
selectsno,sname,sage
wheressex='
男'
例3.查询选修过课的学生的学号
selectsno
fromsc
例4.查询年龄在25-30之间的学生姓名及性别。
selectsname,ssex
wheresagebetween25and30
例5.查询姓“欧阳”的学生。
selectsname
wheresnamelike'
欧阳%'
例6.查询信息系IS,数学系MA和计算机系CS的学生。
wheresdept='
is'
orsdept='
ma'
例7查询所有学生的成绩及姓名
selectgrade,sname
fromstudent,sc
wheresc.sno=student.sno
例8查询‘95001‘学生的所选的课程,成绩和专业
selectcname,grade,sdept
fromstudent,sc,course
wherestudent.sno=sc.snoando=oandstudent.sno='
95001'
实验三、四
关系模式如下:
数据内容如下:
创建数据库并使用数据库
createdatabaseEnterprise;
useEnterprise;
创建表EMPLOYEE并插入数据
createtableEMPLOYEE(
FNAMEchar(20),
MINITchar
(1),
LNAMEchar(20),
SSNchar(9)primarykey,
BDATEdatetime,
ADDRESSchar(50),
SEXchar
(1)check(SEX='
M'
orSEX='
F'
),
SALARYint,
SUPERSSNchar(9),
DNOsmallint,
foreignkey(SUPERSSN)referencesEMPLOYEE(SSN)
);
insertintoEMPLOYEEvalues('
James'
'
E'
Bong'
888665555'
1937-11-10,'
450Stone,Houston,TX'
55000,null,1);
Franklin'
T'
Wong'
333445555'
1955-12-08,'
638Voss,Houston,Tx'
40000,'
5);
Jennifer'
S'
Wallace'
987654321'
1941-06-20,'
291Berry,Bellaire,TX'
43000,'
4);
Ramesh'
K'
Narayan'
666884444'
1962-09-15,'
975FireOak,Humble,TX'
38000,'
Joyce'
A'
English'
453453453'
1972-07-31,'
5631Rice,Houston,TX'
25000,'
John'
B'
Smith'
123456789'
1965-01-09,'
731Fondren,Houston,TX'
30000,'
Alicia'
J'
Zelaya'
999887777'
1968-07-19,'
3321Castle,Spring,TX'
Ahmad'
V'
Jabbar'
987987987'
1969-03-29,'
980Dallas,Houston,TX'
创建表DEPARTMENT并插入数据
createtableDEPARTMENT(
DNAMEchar(20),
DNUMBERsmallintprimarykey,
MGRSSNchar(9),
MGRSTARTDATEdatetime
insertintoDEPARTMENTvalues('
Research'
5,'
1988-05-22);
Administration'
4,'
1995-01-01);
Headquarters'
1,'
1981-06-19);
创建表PROJECT并插入数据
createtablePROJECT(
PNAMEchar(20),
PNUMBERsmallintprimarykey,
PLOCATIONchar(20),
DNUMsmallint,
foreignkey(DNUM)referencesDEPARTMENT(DNUMBER)
insertintoPROJECTvalues('
ProductX'
Bellaire'
ProductY'
2,'
Sugarland'
ProductZ'
3,'
Houston'
Computerization'
10,'
Stafford'
Reorganization'
20,'
1);
Newbenefits'
30,'
创建表WORKS_ON并插入数据
createtableWORKS_ON(
ESSNchar(9),
PNOsmallint,
HOURSfloat
(1),
primarykey(ESSN,PNO),
foreignkey(ESSN)referencesEMPLOYEE(SSN),
foreignkey(PNO)referencesPROJECT(PNUMBER)
insertintoWORKS_ONvalues('
1,32.5);
2,7.5);
3,40.0);
1,20.0);
2,20.0);
2,10.0);
3,10.0);
10,10.0);
20,10.0);
30,30.0);
10,35.0);
30,5.0);
30,20.0);
20,15.0);
20,null);
创建表DEPT_LOACTION并插入数据
createtableDEPT_LOCATION(
DNUMBERsmallint,
DLOCATIONchar(20),
primarykey(DNUMBER,DLOCATION),
foreignkey(DNUMBER)referencesDEPARTMENT(DNUMBER)
insertintoDEPT_LOCATIONvalues(1,'
insertintoDEPT_LOCATIONvalues(4,'
insertintoDEPT_LOCATIONvalues(5,'
创建表DEPENDENT并插入数据
createtableDEPENDENT(
DEPENDENT_NAMEchar(20),
SEXchar
(1),
RELATIONSHIPchar(10),
primarykey(ESSN,DEPENDENT_NAME),
foreignkey(ESSN)referencesEMPLOYEE(SSN)
insertintoDEPENDENTvalues('
Alice'
1986-04-05,'
DAUGHTER'
Theodore'
1983-10-25,'
SON'
Joy'
1958-05-03,'
SPOUSE'
Abner'
1942-02-28,'
Michael'
1988-01-04,'
1988-12-30,'
Elizabeth'
1967-05-05,'
查询操作
Retrievethebirthdateandaddressoftheemployeewhosenameis'
JohnB.Smith'
.
selectBDATE,ADDRESSfromEMPLOYEE
whereFNAME='
andMINIT='
andLNAME='
;
Retrievethenameandaddressofallemployeeswhoworkforthe'
department.
selectFNAME,ADDRESSfromEMPLOYEE,DEPARTMENT
whereDEPARTMENT.MGRSSN=EMPLOYEE.SSN
andDEPARTMENT.DNAME='
Foreveryprojectlocatedin'
listtheprojectnumber,thecontrollingdepartmentnumber,andthedepartmentmanager'
slastname,address,andbirthdate.
selectPNUMBER,DNAME,LNAME,BDATE,ADDRESS
fromPROJECT,DEPARTMENT,DEPT_LOCATION,EMPLOYEE
whereDEPT_LOCATION.DLOCATION='
andDEPT_LOCATION.DNUMBER=PROJECT.DNUM
andDEPT_LOCATION.DNUMBER=DEPARTMENT.DNUMBER
andDEPARTMENT.MGRSSN=EMPLOYEE.SSN;
Foreachemployee,retrievetheemployee'
sname,andthenameofhisorherimmediatesupervisor.
selectEP.FNAME,EP.MINIT,EP.LNAME,ES.FNAME,ES.MINIT,ES.LNAME
fromEMPLOYEEEP,EMPLOYEEES
whereEP.SUPERSSN=ES.SSN;
RetrievetheSSNvaluesforallemployees.
selectSSNfromEMPLOYEE;
Retrievethenamesofemployeeswhohavenodependents.
selectSSNfromEMPLOYEE
wherenotexists(
selectESSNfromDEPENDENT
whereEMPLOYEE.SSN=ESSN);
Retrievethenameofeachemployeewhoworksonalltheprojectscontrolledbydepartmentnumber5.
selectdistinctFNAME,LNAME
fromEMPLOYEE,PROJECT,DEPARTMENT,WORKS_ON
whereDEPARTMENT.DNUMBER=5
andPROJECT.DNUM=DEPARTMENT.DNUMBER
andPROJECT.PNUMBER=WORKS_ON.PNO
andEMPLOYEE.SSN=WORKS_ON.ESSN;
Makealistofallprojectnumbersforprojectsthatinvolveanemployeewhoselastnameis'
asaworkerorasamanagerofthedepartmentthatcontrolstheproject.
selectdistinctWORKS_ON.PNO
fromEMPLOYEE,WORKS_ON,PROJECT,DEPARTMENT
whereEMPLOYEE.LNAME='
and(EMPLOYEE.SSN=WORKS_ON.ESSN
orPROJECT.DNUM=DEPARTMENT.DNUMBER
andDEPARTMENT.MGRSSN=EMPLOYEE.SSN);
Retrievethesocialsecuritynumbersofallemployeeswhoworkonprojectnumber1,2,or3.
selectdistinctESSNfromWORKS_ON,PROJECT
whereWORKS_ON.PNO=PROJECT.PNUMBER
and(PROJECT.PNUMBER=1orPROJECT.PNUMBER=2orPROJECT.PNUMBER=3)
Retrievethenamesofallemployeeswhodonothavesupervisors
selectFNAMEfromEMPLOYEE
whereSUPERSSNisnull
Findthemaximumsalary,theminimumsalary,andtheaveragesalaryamongallemployees.
selectmax(SALARY)'
maximumsalary'
min(SALARY)'
minimumsalary'
avg(SALARY)'
averagesalary'
fromEMPLOYEE
Findthemaximumsalary,theminimumsalary,andtheaveragesalaryamongemployeeswhoworkforthe'
fromEMPLOYEE,DEPARTMENT
whereEMPLOYEE.DNO=DEPARTMENT.DNUMBER
Retrievethetotalnumberofemployeesinthecompany
selectcount(FNAME)'
totalnumber'
fromEMPLOYEE;
Retrievethenumberofemployeesinthe'
department
fromEMPLOYEE,DEPARTMENT
Foreachdepartment,retrievethedepartmentnumber,thenumberofemployeesinthedepartment,andtheiraveragesalary.
selectDEPARTMENT.DNUMBER,count(EMPLOYEE.DNO)'
count'
avg(EMPLOYEE.SALARY)'
fromDEPARTMENT,EMPLOYEE
whereDEPARTMENT.DNUMBER=EMPLOYEE.DNO
groupbyDEPARTMENT.DNUMBER
Foreachproject,retrievetheprojectnumber,projectname,andthenumberofemployeeswhoworkonthatproject.
selectPROJECT.PNAME,count(PROJECT.PNUMBER)'
fromPROJECT,WORKS_ON
wherePROJECT.PNUMBER=WORKS_ON.PNO
groupbyPROJECT.PNAME
Fore
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 上机 实验 报告