-
Notifications
You must be signed in to change notification settings - Fork 3
/
PCDB.sql
96 lines (85 loc) · 3.6 KB
/
PCDB.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- Using MySQL Workbench
-- Create DB
CREATE DATABASE IF NOT EXISTS PC;
USE PC;
-- Create tables
CREATE TABLE IF NOT EXISTS LAPTOP (
CODE INTEGER NOT NULL,
MODEL VARCHAR(4) NOT NULL,
SPEED DECIMAL(4, 0) NOT NULL,
RAM DECIMAL(4, 0) NOT NULL,
HD DECIMAL(3, 0) NOT NULL,
PRICE FLOAT NOT NULL,
SCREEN INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS PC (
CODE INTEGER NOT NULL ,
MODEL VARCHAR(4) NOT NULL ,
SPEED DECIMAL(4, 0) NOT NULL ,
RAM DECIMAL(4, 0) NOT NULL ,
HD DECIMAL(3, 0) NOT NULL ,
CD VARCHAR(3) NOT NULL ,
PRICE FLOAT NOT NULL
);
CREATE TABLE IF NOT EXISTS PRODUCT (
MAKER char(1) NOT NULL ,
MODEL varchar(4) NOT NULL ,
type varchar(7) NOT NULL
);
CREATE TABLE IF NOT EXISTS PRINTER (
code int NOT NULL ,
model varchar(4) NOT NULL ,
color char(1) NOT NULL ,
TYPE varchar(6) NOT NULL ,
PRICE float NOT NULL
);
ALTER TABLE LAPTOP ADD CONSTRAINT PK_LAPTOP PRIMARY KEY (CODE);
ALTER TABLE PC ADD CONSTRAINT PK_PC PRIMARY KEY (CODE);
ALTER TABLE PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (MODEL);
ALTER TABLE PRINTER ADD CONSTRAINT PK_PRINTER PRIMARY KEY (CODE);
ALTER TABLE LAPTOP ADD CONSTRAINT FK_LAPTOP_PRODUCT FOREIGN KEY (MODEL) REFERENCES PRODUCT (MODEL);
ALTER TABLE PC ADD CONSTRAINT FK_PC_PRODUCT FOREIGN KEY (MODEL) REFERENCES PRODUCT (MODEL);
ALTER TABLE PRINTER ADD CONSTRAINT FK_PRINTER_PRODUCT FOREIGN KEY(MODEL) REFERENCES PRODUCT (MODEL);
-- Product
INSERT INTO PRODUCT VALUES ('B', '1121', 'PC');
INSERT INTO PRODUCT VALUES ('A', '1232', 'PC');
INSERT INTO PRODUCT VALUES ('A', '1233', 'PC');
INSERT INTO PRODUCT VALUES ('E', '1260', 'PC');
INSERT INTO PRODUCT VALUES ('A', '1276', 'Printer');
INSERT INTO PRODUCT VALUES ('D', '1288', 'Printer');
INSERT INTO PRODUCT VALUES ('A', '1298', 'Laptop');
INSERT INTO PRODUCT VALUES ('C', '1321', 'Laptop');
INSERT INTO PRODUCT VALUES ('A', '1401', 'Printer');
INSERT INTO PRODUCT VALUES ('A', '1408', 'Printer');
INSERT INTO PRODUCT VALUES ('D', '1433', 'Printer');
INSERT INTO PRODUCT VALUES ('E', '1434', 'Printer');
INSERT INTO PRODUCT VALUES ('B', '1750', 'Laptop');
INSERT INTO PRODUCT VALUES ('A', '1752', 'Laptop');
INSERT INTO PRODUCT VALUES ('E', '2111', 'PC');
INSERT INTO PRODUCT VALUES ('E', '2112', 'PC');
-- PC
INSERT INTO PC VALUES (1, '1232', 500, 64, 5, '12x', 600);
INSERT INTO PC VALUES (2, '1121', 750, 128, 14, '40x', 850);
INSERT INTO PC VALUES (3, '1233', 500, 64, 5, '12x', 600);
INSERT INTO PC VALUES (4, '1121', 600, 128, 14, '40x', 850);
INSERT INTO PC VALUES (5, '1121', 600, 128, 8, '40x', 850);
INSERT INTO PC VALUES (6, '1233', 750, 128, 20, '50x', 950);
INSERT INTO PC VALUES (7, '1232', 500, 32, 10, '12x', 400);
INSERT INTO PC VALUES (8, '1232', 450, 64, 8, '24x', 350);
INSERT INTO PC VALUES (9, '1232', 450, 32, 10, '24x', 350);
INSERT INTO PC VALUES (10, '1260', 500, 32, 10, '12x', 350);
INSERT INTO PC VALUES (11, '1233', 900, 128, 40, '40x', 980);
-- Laptop
INSERT INTO LAPTOP VALUES (1, '1298', 350, 32, 4, 700, 11);
INSERT INTO LAPTOP VALUES (2, '1321', 500, 64, 8, 970, 12);
INSERT INTO LAPTOP VALUES (3, '1750', 750, 128, 12, 1200, 14);
INSERT INTO LAPTOP VALUES (4, '1298', 600, 64, 10, 1050, 15);
INSERT INTO LAPTOP VALUES (5, '1752', 750, 128, 10, 1150, 14);
INSERT INTO LAPTOP VALUES (6, '1298', 450, 64, 10, 950, 12);
-- Printer
insert into printer values(1, '1276', 'n', 'Laser', 400);
insert into printer values(2, '1433', 'y', 'Jet', 270);
insert into printer values(3, '1434', 'y', 'Jet', 290);
insert into printer values(4, '1401', 'n', 'Matrix', 150);
insert into printer values(5, '1408', 'n', 'Matrix', 270);
insert into printer values(6, '1288', 'n', 'Laser', 400);