TUR07 CISC3000 Tutorial 07

Question 1

The suppliers-and-parts database consists of following relations:

S (SID, SNAME, STATUS, CITY)
- PRIMARY KEY (SID)
P (PID, PNAME, COLOR, WEIGHT, CITY)
- PRIMARY KEY (PID)
SP (SID, PID, QTY)
- PRIMARY KEY (SID, PID), FOREIGN KEY (SID) REFERENCES S,
- FOREIGN KEY (PID) REFERENCES P

The tables S and P represent suppliers and parts respectively.
The relation SP (shipments) indicates which supplier has shipped which part in which quantity.

A) Create the above tables and insert the values according to the below values.
I. Create the tables

create schema if not exists `suppliers-and-parts` collate utf8mb4_0900_ai_ci;
create table if not exists P
(
    PID varchar(5) not null
        primary key,
    PNAME varchar(15) null,
    COLOR varchar(10) null,
    WEIGHT decimal(3) null,
    CITY varchar(15) null
);
create table if not exists S
(
    SID varchar(5) not null
        primary key,
    SNAME varchar(15) null,
    STATUS decimal(4) null,
    CITY varchar(15) null
);
create table if not exists SP
(
    SID varchar(4) not null,
    PID varchar(4) not null
        primary key,
    QTY decimal(5) null,
    constraint S_P_1
        foreign key (SID) references S (SID),
    constraint S_P_2
        foreign key (PID) references P (PID)
);

II. Insert the values
P table

INSERT INTO `suppliers-and-parts`.P (PID, PNAME, COLOR, WEIGHT, CITY) VALUES ('P1', 'Nut', 'Red', 12, 'London');
INSERT INTO `suppliers-and-parts`.P (PID, PNAME, COLOR, WEIGHT, CITY) VALUES ('P2', 'Bolt', 'Green', 17, 'Paris');
INSERT INTO `suppliers-and-parts`.P (PID, PNAME, COLOR, WEIGHT, CITY) VALUES ('P3', 'Screw', 'Blue', 17, 'Rome');
INSERT INTO `suppliers-and-parts`.P (PID, PNAME, COLOR, WEIGHT, CITY) VALUES ('P4', 'Screw', 'Red', 14, 'London');
INSERT INTO `suppliers-and-parts`.P (PID, PNAME, COLOR, WEIGHT, CITY) VALUES ('P5', 'Cam', 'Blue', 12, 'Paris');
INSERT INTO `suppliers-and-parts`.P (PID, PNAME, COLOR, WEIGHT, CITY) VALUES ('P6', 'Cog', 'Red', 19, 'London');

S Table

INSERT INTO `suppliers-and-parts`.S (SID, SNAME, STATUS, CITY) VALUES ('S1', 'Smith', 20, 'London');
INSERT INTO `suppliers-and-parts`.S (SID, SNAME, STATUS, CITY) VALUES ('S2', 'Jones', 10, 'Paris');
INSERT INTO `suppliers-and-parts`.S (SID, SNAME, STATUS, CITY) VALUES ('S3', 'Black', 30, 'Paris');
INSERT INTO `suppliers-and-parts`.S (SID, SNAME, STATUS, CITY) VALUES ('S4', 'Clark', 20, 'London');
INSERT INTO `suppliers-and-parts`.S (SID, SNAME, STATUS, CITY) VALUES ('S5', 'Adams', 30, 'Athens');

SP Table

INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S1', 'P1', 300);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S1', 'P2', 200);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S1', 'P3', 400);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S1', 'P4', 200);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S1', 'P5', 100);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S1', 'P6', 100);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S2', 'P1', 300);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S2', 'P2', 400);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S3', 'P2', 200);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S4', 'P2', 200);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S4', 'P4', 300);
INSERT INTO `suppliers-and-parts`.SP (SID, PID, QTY) VALUES ('S4', 'P5', 400);

B) Execute the queries from your midterm test at any database.

Give SQL formulations for the following queries:

1. Find full details of parts heavier than 3 kgs.

   select *
   from p
   where WEIGHT > 3;

2. Find supplier id, supplier name and supplier city for suppliers who supply part P3.

   select S.SID, SNAME, CITY
   from S,SP
   where S.SID = SP.SID and PID = 'P3';

3. Find the maximum and the minimum quantity for part P2.

   select max(QTY), min(QTY)
   from SP
   where PID = 'p2';

4. Find the part number and the total shipment quantity for each part supplied less than 700.

   select PID, sum(QTY)
   from SP
   group by PID
   having sum(QTY) < 700;

5. Find all shipments where quantity is in the range 400 and 800 inclusive.

   select *
   from SP
   where qty >= 400 and QTY <= 800;

6. Find colors of parts supplied by supplier S3.

   select distinct COLOR
   from p,SP
   where P.PID = SP.PID and SID = 's3';

   Copy your SQL formulation and paste at the space below.