Φροντιστήριο sql (από το βιβλίο του date )
DESCRIPTION
Φροντιστήριο SQL (από το βιβλίο του Date ). Παράδειγμα σχήματος Suppliers and Parts Εντολές DDL Εντολές DML. Φροντιστήριο SQL. Suppliers and Parts database. Φροντιστήριο SQL. CREATE TABLE SP (S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL, PRIMARY KEY (S#,P#) - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/1.jpg)
Φροντιστήριο SQL
(από το βιβλίο του Date)
Παράδειγμα σχήματος Suppliers and Parts
Εντολές DDL
Εντολές DML
![Page 2: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/2.jpg)
Φροντιστήριο SQL
P# PNAME COLOR WEIGHT CITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
S# SNAME STATUS CITY
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
S# P# QTY
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400
Suppliers and Partsdatabase
![Page 3: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/3.jpg)
Φροντιστήριο SQL
CREATE TABLE SP(S# S# NOT NULL,P# P# NOT NULL,QTY QTY NOT NULL,PRIMARY KEY (S#,P#)FOREIGN KEY(S#) REFERENCES S
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(P#) REFERENCES PON DELETE CASCADE ON UPDATE CASCADE,
CHECK (QTY>0 AND QTY<5001) );
![Page 4: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/4.jpg)
Φροντιστήριο SQL
CREATE DOMAIN S# CHAR(5);CREATE DOMAIN P# CHAR(6);CREATE DOMAIN QTY NUMERIC(9);
![Page 5: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/5.jpg)
Φροντιστήριο SQL
Get color and city for “nonParis” parts with weight greater than ten
SELECT P.COLOR, P.CITYFROM PWHERE P.CITY< >`PARIS` AND P.WEIGHT>10;
![Page 6: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/6.jpg)
Φροντιστήριο SQL
For all parts, get the part number and the weight of that part in grams
SELECT P.P#, P.WEIGHT * 454 AS GWTFROM P;
![Page 7: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/7.jpg)
Φροντιστήριο SQL
Get all combinations of supplier and part information such that the supplier and part in question are collocated
SELECT S.S#, S.SNAME, S.STATUS, S.CITY, P.P#, P.PNAME, P.COLOR, P.WEIGHT
FROM S, PWHERE S.CITY=P.CITY;ήSELECT S.S#, S.SNAME, S.STATUS, S.CITY,
P.P#, P.PNAME, P.COLOR, P.WEIGHTFROM S JOIN P ON S.CITY=P.CITY;
![Page 8: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/8.jpg)
Φροντιστήριο SQL
Get all pairs of supplier numbers such that the two suppliers concerned are collocated
SELECT FIRST.S# AS SA, SECOND.S# AS SB FROM S AS FIRST, S AS SECONDWHERE FIRST.CITY=SECOND.CITY AND
FIRST.S# < SECOND.S#;
![Page 9: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/9.jpg)
Φροντιστήριο SQL
Get the maximum and minimum quantity for part P2
SELECT MAX (SP.QTY) AS MAXQ, MIN (SP.QTY) AS MINQ
FROM SPWHERE SP.P# = `P2`;
![Page 10: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/10.jpg)
Φροντιστήριο SQL
For each part supplied, get the part number and the total shipment quantity
SELECT SP.P#, SUM (SP.QTY) AS TOTQTY FROM SPGROUP BY SP.P# ;ήSELECT P.P#, (SELECT SUM (SP.QTY)
FROM SPWHERE SP.P#=P.P#) AS TOTQTY
FROM P;
![Page 11: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/11.jpg)
Φροντιστήριο SQL
Get part numbers for all parts supplied by more than one supplier
SELECT SP.P#FROM SPGROUP BY SP.P# HAVING COUNT(SP.S#) >1;
![Page 12: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/12.jpg)
Φροντιστήριο SQL
Get supplier names for suppliers who supply part P2
SELECT DISTINCT S.SNAMEFROM SWHERE S.S# IN (SELECT SP.S#
FROM SPWHERE SP.P# = `P2`);
ήSELECT DISTINCT S.SNAMEFROM S, SPWHERE S.S# =SP.S# AND SP.P# = `P2`;ή
![Page 13: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/13.jpg)
Φροντιστήριο SQL
Get supplier names for suppliers who supply part P2
SELECT DISTINCT S.SNAMEFROM SWHERE EXISTS
(SELECT *FROM SPWHERE SP.S#=S.S# AND SP.P# = `P2`);
![Page 14: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/14.jpg)
Φροντιστήριο SQL
Get supplier names for suppliers who do not supply part P2
SELECT DISTINCT S.SNAMEFROM SWHERE NOT EXISTS
(SELECT *FROM SP WHERE SP.S#=S.S# AND SP.P# = `P2`);
ήSELECT DISTINCT S.SNAMEFROM SWHERE S.S# NOT IN
(SELECT SP.S#*FROM SPWHERE SP.P# = `P2`);
![Page 15: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/15.jpg)
Φροντιστήριο SQL
Get supplier names for suppliers who supply at least one red part
SELECT DISTINCT S.SNAMEFROM SWHERE S.S# IN (SELECT SP.S#
FROM SPWHERE SP.P# IN
(SELECT P.P# FROM PWHERE P.COLOR= `Red`));
![Page 16: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/16.jpg)
Φροντιστήριο SQL
Get supplier names for suppliers who supply all parts
SELECT DISTINCT S.SNAMEFROM SWHERE NOT EXISTS
(SELECT *FROM PWHERE NOT EXISTS
(SELECT * FROM SPWHERE SP.S#=S.S# AND SP.P#= P.P#));
![Page 17: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/17.jpg)
Φροντιστήριο SQL
Get supplier names for suppliers who supply all parts
SELECT DISTINCT S.SNAMEFROM SWHERE (SELECT COUNT(SP.P#)
FROM SPWHERE SP.S#=S.S#) =
(SELECT COUNT (P.P#)FROM P);
![Page 18: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/18.jpg)
Φροντιστήριο SQL
Get supplier numbers for suppliers with status less that the current maximum status in the S table
SELECT S.S#FROM SWHERE S.STATUS <
(SELECT MAX(S.STATUS)FROM S);
![Page 19: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/19.jpg)
Φροντιστήριο SQL
Get part numbers for parts that either weight more than 16 pounds or are supplied by supplier S2, or both
SELECT P.P#FROM PWHERE P.WEIGHT > 16UNIONSELECT SP.P#FROM SPWHERE SP.S#=`S2`);
![Page 20: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/20.jpg)
Φροντιστήριο SQL
INSERTINTO P(P#,PNAME,COLOR,WEIGHT,CITY)VALUES (`P8`, `Sprocket`, `Pink`, 14, `Nice`);
INSERTINTO TEMP(S#,CITY)
SELECT S.S#, S.CITYFROM SWHERE S.STATUS>15;
![Page 21: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/21.jpg)
Φροντιστήριο SQL
UPDATE PSET COLOR=`Yellow`, WEIGHT=P.WEIGHT+5 WHERE P.CITY=`Paris`;
UPDATE PSET CITY = (SELECT S.CITY
FROM SWHERE S.S#=`S5`)
WHERE P.COLOR=`Red`;
![Page 22: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/22.jpg)
Φροντιστήριο SQL
DELETEFROM SPWHERE `London` =
(SELECT S.CITYFROM SWHERE S.S#=SP.S#);
![Page 23: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/23.jpg)
Φροντιστήριο SQL
For all red and blue parts such that the total quantity supplied is greater than 350 (excluding from the total all shipment for which the quantity is less than or equal to 200), get the part number, the weight in grams, the color, and the maximum quantity supplied of that part
![Page 24: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/24.jpg)
Φροντιστήριο SQL
SELECT P.P#, `Weight in grams =` AS TEXT1, P.WEIGHT * 454 AS GMWT, P COLOR,
`Max Quantity=` AS TEXT2, MAX(SP.QTY) AS MQY
FROM P, SPWHERE P.P#=SP.P# AND SP.QTY>200 AND
(P.COLOR=`Red` OR P.COLOR=`Blue`)GROUP BY P.P#, P.WEIGHT, P.COLORHAVING SUM(SP.QTY) > 350;
![Page 25: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/25.jpg)
Φροντιστήριο SQL
P# TEXT1 GMWT COLOR TEXT2 MQY
P1 Weight in
grams = 5448 Red
Max quantity = 300
P5 Weight in
grams = 5448 Blue
Max quantity = 400
P3 Weight in
grams = 7718 Blue
Max quantity = 400
![Page 26: Φροντιστήριο SQL (από το βιβλίο του Date )](https://reader035.vdocuments.us/reader035/viewer/2022081420/56814a50550346895db771e8/html5/thumbnails/26.jpg)
Φροντιστήριο SQL
Get part names for parts whose weight is greater that that of every blue part
SELECT DISTINCT PX.PNAMEFROM P AS PXWHERE PX.WEIGHT > ALL
(SELECT PY.WEIGHTFROM P AS PYWHERE PY.COLOR=`Blue`);