数据库SQL实验报告书

南京理工大学紫金学院实验报告书课程名称：《数据库系统》实验题目：实验1用SQL语句创建数据库和表班级：计算机科学与技术2班学号：09061257姓名：朱书宏指导教师：叶庆生成绩：成绩：批阅教师：年月日《数据库系统》实验报告090601257朱书宏-PAGE18-实验目的使用SQL2005练习使用SQL语句创建数据库与表2．练习SQL查询、修改、删除等语句语句3.掌握数据库中创建表时主键、约束条件的纂写二.实验内容1．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。Product(marker,model,type)PC(model,speed,ram,hd,rd,price)Laptop(model,speed,ram,hd,screen,price)Printer(model,color,type,price)2．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。Classes(class,type,country,numCuns,bore,displacement)Ships（name,class,launched)Battles（name,date)Outcomes(ship,battle,result)3．将相关的数据用SQL语句插入到数据中。4．基于习题5.2.4给出的数据库模式和数据写出后面的查询语句以及查询结果。Classes(class,type,country,numCuns,bore,displacement)Ships（name,class,launched)Battles（name,date)Outcomes(ship,battle,result)a）找出战舰类型的数量。b）找出不同类型战舰拥有的平均火炮数量。!c）找出战舰的平均火炮数量。注意c）和b）的不同在于：在计算均值的时候，是使用战舰的数目还是战舰的类型数目。!d）找出每一类型（class）的第一艘船下水的年份。!e）找出每一类型中被击沉船的数目。!!f)找出至少有3艘船的类型中被击沉的船的数目。!!g）军舰火炮使用的炮弹的重量（以磅为单位）大约是火炮的口径（以英寸为单位）的一半。找出各个国家的军舰炮弹重量的平均值。5．根据习题5.2.1给出的数据库模式，写出下面的数据库修改。描述对该习题数据库修改够的结果。Product(marker,model,type)PC(model,speed,ram,hd,rd,price)Laptop(model,speed,ram,hd,screen,price)Printer(model,color,type,price)a)通过两条INSERT语句在数据库中添加如下信息：制造商C生产的型号为1100的PC，速度为1800，RAM为256，硬盘大小80，具有一个20x的DVD，售价为$2499。！b)加入如下信息：对于数据库中每台PC，都对应一台与其速度、RAM、硬盘相同，具有15英寸的屏幕，型号大于1100、价格高于$500的相同厂商制造的手提电脑。c)删除所有硬盘不超过20G的PC。d)删除所有不制造打印机的厂商生产的手提电脑。e)厂商A收购了厂商B，将所有B生产的产品改为由A生产。f)对于每台PC，把它的内存加倍并且增加20G的硬盘容量。（记住UPDATE语句中可以同时更改多个属性的值）！g)把厂商B生产的手提电脑的屏幕尺寸增加一英寸并且价格下调$100。三、实验步骤1．创建数据库表2.编写SQL语句3,在SQL2005上调试语句并运行结果四、实验结果1．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。Product(marker,model,type)PC(model,speed,ram,hd,rd,price)Laptop(model,speed,ram,hd,screen,price)Printer(model,color,type,price) SQL语句：createdatabase[zuoye1]createtableproduct(markervarchar(16)primarykey,modelvarchar(16)notnull,typevarchar(16)notnull,)createtablePC(modelvarchar(16)primarykey,speedvarchar(16)notnull,ramvarchar(16)notnull,hdvarchar(16)notnull,rdvarchar(16)notnull,priceintnotnull)createtablelaptop(modelvarchar(16)primarykey,speedvarchar(16)notnull,ramvarchar(16)notnull,hdvarchar(16)notnull,screemvarchar(16)notnull,priceintnotnull,)createtableprinter(modelvarchar(16)primarykey,colorvarchar(16)notnull,typevarchar(16)notnull,priceintnotnull,)2．根据下列数据库模式，用SQL语句创建一个适当地数据库，并创建相关的表，注意表中的主键和外键及某些属性域的约束。Classes(class,type,country,numCuns,bore,displacement)Ships（name,class,launched)Battles（name,date)Outcomes(ship,battle,result)SQL语句：createdatabase[zuoye2]createtableClasses(classvarchar(16)primarykeynotnull,typevarchar(16)notnull,countryvarchar(16)notnull,numGunsint,boreint,displacementint,)createtableShips(namevarchar(16)primarykey,classvarchar(16)notnull,launchedvarchar(16)notnull,)createtableBattles(namevarchar(16)primarykey,datevarchar(16)notnull,)createtableOutcomes(shipvarchar(16)primarykey,battlevarchar(16)notnull,resultvarchar(16)notnull,)3．将相关的数据用SQL语句插入到数据中。SQL语句：数据库1：zuoye1insertintoProduct(marker,model,type)values('A',1001,'pc');insertintoProduct(marker,model,type)values('A',1002,'pc');insertintoProduct(marker,model,type)values('A',1003,'pc');insertintoProduct(marker,model,type)values('A',2004,'laptop');insertintoProduct(marker,model,type)values('A',2005,'laptop');insertintoProduct(marker,model,type)values('A',2006,'laptop');insertintoProduct(marker,model,type)values('B',1004,'pc');insertintoProduct(marker,model,type)values('B',1005,'pc');insertintoProduct(marker,model,type)values('B',1006,'pc');insertintoProduct(marker,model,type)values('B',2007,'laptop');insertintoProduct(marker,model,type)values('C',1007,'pc');insertintoProduct(marker,model,type)values('D',1008,'pc');insertintoProduct(marker,model,type)values('D',1009,'pc');insertintoProduct(marker,model,type)values('D',1010,'pc');insertintoProduct(marker,model,type)values('D',3004,'printer');insertintoProduct(marker,model,type)values('D',3005,'printer');insertintoProduct(marker,model,type)values('E',1011,'pc');insertintoProduct(marker,model,type)values('E',1012,'pc');insertintoProduct(marker,model,type)values('E',1013,'pc');insertintoProduct(marker,model,type)values('E',2001,'laptop');insertintoProduct(marker,model,type)values('E',2002,'laptop');insertintoProduct(marker,model,type)values('E',2003,'laptop');insertintoProduct(marker,model,type)values('E',3001,'printer');insertintoProduct(marker,model,type)values('E',3002,'printer');insertintoProduct(marker,model,type)values('E',3003,'printer');insertintoProduct(marker,model,type)values('F',2008,'laptop');insertintoProduct(marker,model,type)values('F',2009,'laptop');insertintoProduct(marker,model,type)values('G',2010,'laptop');insertintoProduct(marker,model,type)values('H',3006,'printer');insertintoProduct(marker,model,type)values('H',3007,'printer');insertintoPC(model,speed,ram,hd,price)values(1001,2.66,1024,250,2114);insertintoPC(model,speed,ram,hd,price)values(1002,2.10,512,250,995);insertintoPC(model,speed,ram,hd,price)values(1003,1.42,512,80,478);insertintoPC(model,speed,ram,hd,price)values(1004,2.80,1024,250,649);insertintoPC(model,speed,ram,hd,price)values(1005,3.20,512,250,630);insertintoPC(model,speed,ram,hd,price)values(1006,3.20,1024,320,1049);insertintoPC(model,speed,ram,hd,price)values(1007,2.20,1024,200,510);insertintoPC(model,speed,ram,hd,price)values(1008,2.20,2048,250,770);insertintoPC(model,speed,ram,hd,price)values(1009,2.00,1024,250,650);insertintoPC(model,speed,ram,hd,price)values(1010,2.80,2048,300,770);insertintoPC(model,speed,ram,hd,price)values(1011,1.86,2048,160,959);insertintoPC(model,speed,ram,hd,price)values(1012,2.80,1024,160,649);insertintoPC(model,speed,ram,hd,price)values(1013,3.06,512,80,529);insertintoPrinter(model,color,type,price)values(3001,'true','ink-jet',99);insertintoPrinter(model,color,type,price)values(3002,'false','laser',239);insertintoPrinter(model,color,type,price)values(3003,'true','laser',899);insertintoPrinter(model,color,type,price)values(3004,'true','ink-jet',120);insertintoPrinter(model,color,type,price)values(3005,'false','laser',120);insertintoPrinter(model,color,type,price)values(3006,'true','ink-jet',100);insertintoPrinter(model,color,type,price)values(3007,'true','laser',200);insertintoLaptop(model,speed,ram,hd,screen,price)values(2001,2.00,2048,240,20.1,3673);insertintoLaptop(model,speed,ram,hd,screen,price)values(2002,1.73,1024,80,17.0,3673);insertintoLaptop(model,speed,ram,hd,screen,price)values(2003,1.802,512,60,16.4,549);insertintoLaptop(model,speed,ram,hd,screen,price)values(2004,2.00,512,60,13.3,1150);insertintoLaptop(model,speed,ram,hd,screen,price)values(2005,2.16,1024,120,17.0,2600);insertintoLaptop(model,speed,ram,hd,screen,price)values(2006,2.00,2048,80,15.4,1700);insertintoLaptop(model,speed,ram,hd,screen,price)values(2007,1.83,1024,120,13.3,1429);insertintoLaptop(model,speed,ram,hd,screen,price)values(2008,1.60,1024,100,15.4,900);insertintoLaptop(model,speed,ram,hd,screen,price)values(2009,1.60,512,80,14.1,680);insertintoLaptop(model,speed,ram,hd,screen,price)values(2010,2.00,2048,160,15.4,2300);数据库2：zuoye2intoClasses(class,type,country,numGuns,bore,displacement)values('Bismark','bb','Germany',8,15,42000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('Iowa','bb','USA',9,15,46000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('Kongo','bc','Janpan',8,14,32000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('NorthCarolina','bb','USA',9,16,37000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('Renown','bc','Gt.Brintain',6,15,32000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('Revenge','bb','Gt.Brintain',8,15,29000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('Tennessee','bb','USA',12,14,32000);insertintoClasses(class,type,country,numGuns,bore,displacement)values('Yamato','bb','Janpan',9,18,65000);insertintoBattles(name,date)values('DenmarkStrait','5/24-27-41');insertintoBattles(name,date)values('Guadalcanal','11/15/42');insertintoBattles(name,date)values('NorthCape','12/26/43');insertintoBattles(name,date)values('SurigaoStrait','10/25/44');insertintoShips(name,class,launched)values('California','Tennessee',1921);insertintoShips(name,class,launched)values('Haruna','Kongo',1915);insertintoShips(name,class,launched)values('Hiei','Kongo',1914);insertintoShips(name,class,launched)values('Iowa','Iowa',1943);insertintoShips(name,class,launched)values('Kirishima','Kongo',1915);insertintoShips(name,class,launched)values('Kongo','Kongo',1913);insertintoShips(name,class,launched)values('Missouri','Iowa',1944);insertintoShips(name,class,launched)values('Mnsashi','Yamato',1942);insertintoShips(name,class,launched)values('NewJersey','Iowa',1943);insertintoShips(name,class,launched)values('NorthCalifornia','NorthCalifornia',1941);insertintoShips(name,class,launched)values('Ramilliss','Revenge',1917);insertintoShips(name,class,launched)values('Renown','Renown',1915);insert intoShips(name,class,launched)values('Repulse','Renown',1915);insertintoShips(name,class,launched)values('Resolution','Revenge',1916);insertintoShips(name,class,launched)values('Revenge','Revenge',1916);insertintoShips(name,class,launched)values('RoyalDak','Revenge',1916);insertintoShips(name,class,launched)values('RoyalSovereign','Revenge',1916);insertintoShips(name,class,launched)values('Tennessee','Tennessee',1920);insertintoShips(name,class,launched)values('Washington','NorthCarolina',1941);insertintoShips(name,class,launched)values('Wisconsin','Iowa',1944);insertintoShips(name,class,launched)values('Yamato','Yamato',1941);insertintoOutcomes(ship,battle,result)values('Arizona','PearlHarbor','sunk');insertintoOutcomes(ship,battle,result)values('Bismarck','DenmarkStrait','sunk');insertintoOutcomes(ship,battle,result)values('California','DenmarkStrait','ok');insertintoOutcomes(ship,battle,result)values('DunkofYork','NorthCape','ok');insertintoOutcomes(ship,battle,result)values('Fuso','SurgaoStrait','sunk');insertintoOutcomes(ship,battle,result)values('Hook','DenmarkStrait','sunk');insertintoOutcomes(ship,battle,result)values('KingGeorgeV','DenmarkStrait','ok');insertintoOutcomes(ship,battle,result)values('Kirishina','Guadalcanal','sunk');insertintoOutcomes(ship,battle,result)values('PrinceofWales','DenmarkStrait','damaged');insertintoOutcomes(ship,battle,result)values('Rodney','DenmarkStrait','ok');insertintoOutcomes(ship,battle,result)values('Scharnhorst','NorthCape','sunk');insertintoOutcomes(ship,battle,result)values('SouthDakota','Guadalcanal','damaged');insertintoOutcomes(ship,battle,result)values('Tennessee','SurigaoStrait','ok');insertintoOutcomes(ship,battle,result)values('Washington','Guadalcanal','ok');insertintoOutcomes(ship,battle,result)values('WestVirginia','SurigaoStrait','ok');insertintoOutcomes(ship,battle,result)values('Yamashiro','SurigaoStrait','sunk');4．基于习题5.2.4给出的数据库模式和数据写出后面的查询语句以及查询结果。Classes(class,type,country,numGuns,bore,displacement)Ships（name,class,launched)Battles（name,date)Outcomes(ship,battle,result)a）找出战舰类型的数量。b）找出不同类型战舰拥有的平均火炮数量。!c）找出战舰的平均火炮数量。注意c）和b）的不同在于：在计算均值的时候，是使用战舰的数目还是战舰的类型数目。!d）找出每一类型（class）的第一艘船下水的年份。!e）找出每一类型中被击沉船的数目。!!f)找出至少有3艘船的类型中被击沉的船的数目。!!g）军舰火炮使用的炮弹的重量（以磅为单位）大约是火炮的口径（以英寸为单位）的一半。找出各个国家的军舰炮弹重量的平均值。SQL语句：a)selectcount(distinctclass)fromshipsb)selecttype,avg(numGuns)fromclassesgroupbytypehavingtypein(selectdistincttypefromclasses)c) selecttype,sum(numGuns)fromclassesgroupbytyped)selectclass,min(launched)fromshipsgroupbyclasse)selectclass,count(result)fromoutcomes,shipswhereresult='sunk'and=outcomes.shipgroupbyclassf)selectcountry,avg(bore*1/2)fromclassesgroupbycountry5．根据习题5.2.1给出的数据库模式，写出下面的数据库修改。描述对该习题数据库修改够的结果。Product(marker,model,type)PC(model,speed,ram,hd,rd,price)Laptop(model,speed,ram,hd,screen,price)Printer(model,color,type,price)a)通过两条INSERT语句在数据库中添加如下信息：制造商C生产的型号为1100的PC，速度为1800，RAM为256，硬盘大小80，具有一个20x的DVD，售价为$2499。！b)加入如下信息：对于数据库中每台PC，都对应一台与其速度、RAM、硬盘相同，具有15英寸的屏幕，型号大于1100、价格高于$500的相同厂商制造的手提电脑。c)删除所有硬盘不超过20G的PC。d)删除所有不制造打印机的厂商生产的手提电脑。e)厂商A收购了厂商B，将所有B生产的产品改为由A生产。f)对于每台PC，把它的内存加倍并且增加20G的硬盘容量。（记住UPDATE语句中可以同时更改多个属性的值）！g)把厂商B生产的手提电脑的屏幕尺寸增加一英寸并且价格下调$100。SQL语句：a)insertintoPC(model,speed,ram,hd,price)values(1100,1800,256,80,2499);insertintoProduct(marker,model,type)values('C',1100,'pc');b)