· 8 years ago · Aug 12, 2018, 02:44 PM
1USE master
2GO
3if exists (select * from sysdatabases where name='pc')
4 DROP DATABASE pc
5GO
6
7CREATE DATABASE pc
8GO
9USE pc
10GO
11
12CREATE TABLE laptop(
13 code int NOT NULL,
14 model varchar(4) NOT NULL,
15 speed decimal(4, 0) NOT NULL,
16 ram decimal(4, 0) NOT NULL,
17 hd decimal(3, 0) NOT NULL,
18 price float NOT NULL,
19 screen int NOT NULL);
20
21 CREATE TABLE pc(
22 code int NOT NULL ,
23 model varchar(4) NOT NULL ,
24 speed decimal(4, 0) NOT NULL ,
25 ram decimal(4, 0) NOT NULL ,
26 hd decimal(3, 0) NOT NULL ,
27 cd varchar(3) NOT NULL ,
28 price float NOT NULL
29);
30
31CREATE TABLE product(
32 maker char(1) NOT NULL ,
33 model varchar(4) NOT NULL ,
34 type varchar(7) NOT NULL
35);
36
37CREATE TABLE printer(
38 code int NOT NULL ,
39 model varchar(4) NOT NULL ,
40 color char(1) NOT NULL ,
41 type varchar(6) NOT NULL ,
42 price float NOT NULL
43);
44
45ALTER TABLE laptop ADD CONSTRAINT PK_laptop PRIMARY KEY(code);
46
47ALTER TABLE pc ADD CONSTRAINT PK_pc PRIMARY KEY(code);
48
49ALTER TABLE product ADD CONSTRAINT PK_product PRIMARY KEY (model);
50
51ALTER TABLE printer ADD CONSTRAINT PK_printer PRIMARY KEY(code);
52
53ALTER TABLE laptop ADD CONSTRAINT FK_laptop_product FOREIGN KEY(model) REFERENCES product(model);
54
55ALTER TABLE pc ADD CONSTRAINT FK_pc_product FOREIGN KEY(model) REFERENCES product(model);
56
57ALTER TABLE printer ADD CONSTRAINT FK_printer_product FOREIGN KEY(model) REFERENCES product(model);
58
59----Product------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
60insert into product values('B','1121','PC');
61insert into product values('A','1232','PC');
62insert into product values('A','1233','PC');
63insert into product values('E','1260','PC');
64insert into product values('A','1276','Printer');
65insert into product values('D','1288','Printer');
66insert into product values('A','1298','Laptop');
67insert into product values('C','1321','Laptop');
68insert into product values('A','1401','Printer');
69insert into product values('A','1408','Printer');
70insert into product values('D','1433','Printer');
71insert into product values('E','1434','Printer');
72insert into product values('B','1750','Laptop');
73insert into product values('A','1752','Laptop');
74insert into product values('E','2111','PC');
75insert into product values('E','2112','PC');
76----PC------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
77insert into pc values(1,'1232',500,64,5,'12x',600);
78insert into pc values(2,'1121',750,128,14,'40x',850);
79insert into pc values(3,'1233',500,64,5,'12x',600);
80insert into pc values(4,'1121',600,128,14,'40x',850);
81insert into pc values(5,'1121',600,128,8,'40x',850);
82insert into pc values(6,'1233',750,128,20,'50x',950);
83insert into pc values(7,'1232',500,32,10,'12x',400);
84insert into pc values(8,'1232',450,64,8,'24x',350);
85insert into pc values(9,'1232',450,32,10,'24x',350);
86insert into pc values(10,'1260',500,32,10,'12x',350);
87insert into pc values(11,'1233',900,128,40,'40x',980);
88----Laptop------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
89insert into laptop values(1,'1298',350,32,4,700,11);
90insert into laptop values(2,'1321',500,64,8,970,12);
91insert into laptop values(3,'1750',750,128,12,1200,14);
92insert into laptop values(4,'1298',600,64,10,1050,15);
93insert into laptop values(5,'1752',750,128,10,1150,14);
94insert into laptop values(6,'1298',450,64,10,950,12);
95----Printer------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
96insert into printer values(1,'1276','n','Laser',400);
97insert into printer values(2,'1433','y','Jet',270);
98insert into printer values(3,'1434','y','Jet',290);
99insert into printer values(4,'1401','n','Matrix',150);
100insert into printer values(5,'1408','n','Matrix',270);
101insert into printer values(6,'1288','n','Laser',400);