数据库作业,关于舰船、it产品数据库的基础数据输入

数据库作业

关于舰船、it产品数据库的基础数据输入
代码用sql sever 2005编写，其他软件仅供参考。

it产品

create table Product(  --创建表
	maker varchar(3),	
	model int,
	[type] varchar(20),
	primary key (model)
)
 
--drop table Product

insert into Product(maker,model,type) values('A',1001,'pc')
insert into Product(maker,model,type) values('A',1002,'pc')
insert into Product(maker,model,type) values('A',1003,'pc')
insert into Product(maker,model,type) values('A',2004,'laptop')
insert into Product(maker,model,type) values('A',2005,'laptop')
insert into Product(maker,model,type) values('A',2006,'laptop')
insert into Product(maker,model,type) values('B',1004,'pc')
insert into Product(maker,model,type) values('B',1005,'pc')
insert into Product(maker,model,type) values('B',1006,'pc')
insert into Product(maker,model,type) values('B',2007,'laptop')
insert into Product(maker,model,type) values('C',1007,'pc')
insert into Product(maker,model,type) values('D',1008,'pc')
insert into Product(maker,model,type) values('D',1009,'pc')
insert into Product(maker,model,type) values('D',1009,'pc')
insert into Product(maker,model,type) values('D',1010,'printer')
insert into Product(maker,model,type) values('D',3005,'printer')
insert into Product(maker,model,type) values('E',1011,'pc')
insert into Product(maker,model,type) values('E',1012,'pc')
insert into Product(maker,model,type) values('E',1013,'pc')
insert into Product(maker,model,type) values('E',2001,'laptop')
insert into Product(maker,model,type) values('E',2002,'laptop')
insert into Product(maker,model,type) values('E',2003,'laptop')
insert into Product(maker,model,type) values('E',3001,'printer')
insert into Product(maker,model,type) values('E',3002,'printer')
insert into Product(maker,model,type) values('E',3003,'printer')
insert into Product(maker,model,type) values('F',2008,'laptop')
insert into Product(maker,model,type) values('F',2009,'laptop')
insert into Product(maker,model,type) values('G',2010,'laptop')
insert into Product(maker,model,type) values('H',3006,'printer')
insert into Product(maker,model,type) values('H',3007,'printer')


create table PC(
	model int,
	speed float,
	ram int,
	hd int,
	price int,
	primary key (model)
)

--drop table PC

insert into PC(model, speed, ram, hd, price) values(1001,2.66,1024,250,2114)
insert into PC(model, speed, ram, hd, price) values(1002,2.1,512,250,995)
insert into PC(model, speed, ram, hd, price) values(1003,1.42,512,80,478)
insert into PC(model, speed, ram, hd, price) values(1004,2.8,1024,250,649)
insert into PC(model, speed, ram, hd, price) values(1005,3.2,512,250,630)
insert into PC(model, speed, ram, hd, price) values(1006,3.2,1024,320,1049)
insert into PC(model, speed, ram, hd, price) values(1007,2.2,1024,200,510)
insert into PC(model, speed, ram, hd, price) values(1008,2.2,2048,250,770)
insert into PC(model, speed, ram, hd, price) values(1009,2,1024,250,650)
insert into PC(model, speed, ram, hd, price) values(1010,2.8,2048,300,770)
insert into PC(model, speed, ram, hd, price) values(1011,1.86,2048,160,959)
insert into PC(model, speed, ram, hd, price) values(1012,2.8,1024,160,649)
insert into PC(model, speed, ram, hd, price) values(1013,3.06,512,80,529)


create table Laptop(
	model int,
	speed float,
	ram int,
	hd int,
	screen float,
	price int,
	primary key(model)
	)



insert into laptop(model,speed,ram,hd,screen,price) values(2001,2,2048,240,20.1,3673)
insert into laptop(model,speed,ram,hd,screen,price) values(2002,1.73,1024,80,17,949)
insert into laptop(model,speed,ram,hd,screen,price) values(2003,1.8,512,60,15.4,549)
insert into laptop(model,speed,ram,hd,screen,price) values(2004,2,512,60,13.3,1150)
insert into laptop(model,speed,ram,hd,screen,price) values(2005,2.16,1024,120,17,2500)
insert into laptop(model,speed,ram,hd,screen,price) values(2006,2,2048,80,15.4,1700)
insert into laptop(model,speed,ram,hd,screen,price) values(2007,1.83,1024,120,13.3,1429)
insert into laptop(model,speed,ram,hd,screen,price) values(2008,1.6,1024,100,15.4,900)
insert into laptop(model,speed,ram,hd,screen,price) values(2009,1.6,512,80,14.1,680)
insert into laptop(model,speed,ram,hd,screen,price) values(2010,2,2048,160,15.4,2300)


create table Printer(
	model int,
	color varchar(10),
	[type] varchar(15),
	price int,
	primary key(model)
)

--drop table Printer

--insert into Printer(model,color,type,price) values()
insert into Printer(model,color,type,price) values(3001,'ture','ink-jet',99)
insert into Printer(model,color,type,price) values(3002,'false ','laser',239)
insert into Printer(model,color,type,price) values(3003,'ture','laser',899)
insert into Printer(model,color,type,price) values(3004,'ture','ink-jet',120)
insert into Printer(model,color,type,price) values(3005,'false ','laser',120)
insert into Printer(model,color,type,price) values(3006,'ture','ink-jet',100)
insert into Printer(model,color,type,price) values(3007,'ture','laser',200)

舰船

create table Classes(
 class varchar(50),
 [type] varchar(5),
 country varchar(50),
 numGuns int,
 bore int,
 displacement int,
 primary key (class)
)

insert into Classes(class,type,country,numGuns,bore,displacement) values('Bismarck','bb','Germany',8,15,42000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('Iowa','bb','USA',9,16,46000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('Kongo','be','Japan',8,14,32000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('North Carolina','bb','USA',9,16,37000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('Renown','be','Gt. Britain',6,15,32000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('Revenge','bb','Gt. Britain',8,15,29000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('Tennessee','bb','USA',12,14,32000)
insert into Classes(class,type,country,numGuns,bore,displacement) values('Yamato','bb','Japan',9,18,65000)


create table Outcomes(
 ship  varchar(50),
 battle varchar(70),
 rueslt varchar(20),
 primary key (ship)
 )

insert into Outcomes(ship,battle,rueslt) values('Arizona','Pearl Harbor','sunk')
insert into Outcomes(ship,battle,rueslt) values('Bismarck','Denmark Strait ','sunk')
insert into Outcomes(ship,battle,rueslt) values('California','Surigao Strait ','ok')
insert into Outcomes(ship,battle,rueslt) values('Duke of York','North Cape','ok')
insert into Outcomes(ship,battle,rueslt) values('Fuso','Surigao Strait ','sunk')
insert into Outcomes(ship,battle,rueslt) values('Hood','Denmark Strait ','sunk')
insert into Outcomes(ship,battle,rueslt) values('King George V','Denmark Strait ','ok')
insert into Outcomes(ship,battle,rueslt) values('Kirishima','Guadalcanal','sunk')
insert into Outcomes(ship,battle,rueslt) values('Prince of Wales','Denmark Strait ','damaged')
insert into Outcomes(ship,battle,rueslt) values('Rodney','Denmark Strait ','ok')
insert into Outcomes(ship,battle,rueslt) values('Scharnhorst','North Cape','sunk')
insert into Outcomes(ship,battle,rueslt) values('South Dakota','Guadalcanal','damaged')
insert into Outcomes(ship,battle,rueslt) values('Tennessee','Surigao Strait ','ok')
insert into Outcomes(ship,battle,rueslt) values('Washington','Guadalcanal','ok')
insert into Outcomes(ship,battle,rueslt) values('West Virginia','Surigao Strait ','ok')
insert into Outcomes(ship,battle,rueslt) values('Yamashiro','Surigao Strait','sunk')




insert into Outcomes(ship,battle,rueslt) values(,'','',)


create table Ships(
 [name] varchar(40),
 class varchar(40),
 launched int,
 primary key (name)
)

insert into Ships(name,class,launched) values('California','Tennessee',1921)
insert into Ships(name,class,launched) values('Haruna','Kongo',1915)
insert into Ships(name,class,launched) values('Hiei','Kongo',1914)
insert into Ships(name,class,launched) values('Iowa','Iowa',1943)
insert into Ships(name,class,launched) values('Kirishima','Kongo',1915)
insert into Ships(name,class,launched) values('Kongo','Kongo',1913)
insert into Ships(name,class,launched) values('Missouri','Iowa',1944)
insert into Ships(name,class,launched) values('Musashi','Yamato',1942)
insert into Ships(name,class,launched) values('New Jersey','Iowa',1943)
insert into Ships(name,class,launched) values('North Carolina ','North Carolina ',1941)
insert into Ships(name,class,launched) values('Ramillies','Revenge',1917)
insert into Ships(name,class,launched) values('Renown','Renown',1916)
insert into Ships(name,class,launched) values('Repulse','Renown',1916)
insert into Ships(name,class,launched) values('Resolution','Revenge',1916)
insert into Ships(name,class,launched) values('Revenge','Revenge',1916)
insert into Ships(name,class,launched) values('Royal Oak','Revenge',1916)
insert into Ships(name,class,launched) values('Royal Sovereign ','Revenge',1916)
insert into Ships(name,class,launched) values('Tennessee','Tennessee',1920)
insert into Ships(name,class,launched) values('Washington','North Carolina ',1941)
insert into Ships(name,class,launched) values('Wisconsin','Iowa',1944)
insert into Ships(name,class,launched) values('Yamato','Yamato',1941)