Untitled
unknown
pgsql
a year ago
6.9 kB
1
Indexable
Never
--select * from laptop;
DROP TABLE if exists Laptop;
DROP TABLE if exists PC;
DROP TABLE if exists Printer;
DROP TABLE if exists Product;
CREATE TABLE Laptop (
code int NOT NULL ,
model varchar (50) NOT NULL ,
speed smallint NOT NULL ,
ram smallint NOT NULL ,
hd real NOT NULL ,
price decimal(12,2) NULL ,
screen smallint NOT NULL
)
;
CREATE TABLE PC (
code int NOT NULL ,
model varchar (50) NOT NULL ,
speed smallint NOT NULL ,
ram smallint NOT NULL ,
hd real NOT NULL ,
cd varchar (10) NOT NULL ,
price decimal(12,2) NULL
)
;
CREATE TABLE Product (
maker varchar (10) NOT NULL ,
model varchar (50) NOT NULL ,
type varchar (50) NOT NULL
)
;
CREATE TABLE Printer (
code int NOT NULL ,
model varchar (50) NOT NULL ,
color char (1) NOT NULL ,
type varchar (10) NOT NULL ,
price decimal(12,2) 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','2113','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');
insert into PC values(12,'1233',800,128,20,'50x','970');
----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');
select distinct product.maker from product join pc on product.model = pc.model where pc.speed >= 750 and maker in (select maker from product join laptop on product.model = laptop.model where laptop.speed >= 750)
with minPcRam as (select * from pc where ram = (SELECT MIN(ram) FROM PC)), MaxSpeedPc as (select * from minPcRam where speed = (SELECT max(speed) FROM minPcRam)), PrinterMakers as (select maker from product join printer on printer.model = product.model) select distinct maker from MaxSpeedPc join product on product.model = MaxSpeedPc.model where maker in (select maker from PrinterMakers);
with minPcRam as (select * from pc where ram = (SELECT MIN(ram) FROM PC)), MaxSpeedPc as (select * from minPcRam where speed = (SELECT max(speed) FROM minPcRam)) select product.maker from product join printer on product.model = printer.model where maker in (select maker from MaxSpeedPc)
select price from product join pc on pc.model = product.model where product.maker = 'A' union all (select price from product join laptop on laptop.model = product.model where product.maker = 'A')
select maker, avg(pc.hd) from pc, product where product.model = pc.model and product.maker in (select distinct product.maker from product where product.type='printer') group by maker
WITH MakerModelCount AS (
SELECT maker, COUNT(model) AS model_count
FROM Product
GROUP BY maker
)
SELECT count(*)
FROM MakerModelCount
WHERE model_count = 1;