Using the following Schema provide SQL queries and relational expressions for th

Question

Using the following Schema provide SQL queries and relational expressions for these statements.

For the one who asked what ‘NY’ and ‘SF’ are, they are cities.

CREATE TABLE Supplier ( SID bigserial SName varchar(10) NOT NULL, supplier name Status varchar(10 NOT NULL, -supplier status (a government designation) City varchar(10) NOT NULL supplier city (where it is located) primary key, supplier id CREATE TABLE Part PID bigserial PName varchar(10) NOT NULL, part name Color char 10 NOT NULL, -part color Weight real price real primary key, part id NOT NULL, part weight n bs) NOT NULL-part price (in $) CREATE TABLE Shipment SID bigint FOREIGN KEY REFERENCES Supplier(SID),-, supplier id PID bigint FOREIGN KEY REFERENCES Part(PID),-part id Qty int NOT NULL, quantity shipped PRIMARY KEY (SID, PID) CREATE TABLE Customer CID bigseria primary key, -customer id CName varchar(10) NOT NULL, customer name Phone varchar(10) NOT NULL, customer phone number City varchar(10) NOT NULL customer city (where it is located) CREATE TABLE PartPurchase ( SID bigint FOREIGN KEY REFERENCES Supplier(SID),-supplier id PID bigint FOREIGN KEY REFERENCES Part(PID),-part id CID bigint FOREIGN KEY REFERENCES Customer (CID), -customer id Qty int NOT NULL, - quantity of parts purchased by customer from supplier PRIMARY KEY (SID, PID, CID

Explanation / Answer

1. select SID as ID,SName as Name from Supplier where City='NY' and Status='WomenOwn'

2. select PID as ID, PName as Name, price from Part where color='red' and price>=10 and Weight > 0.5

3. select c.CID as ID, c.CName as Name from Customer c join PartPurchase pp on c.CID=pp.CID where pp.PID=1234

4. select p.PID as ID, p.PName as Name, p.price as Price from Part p join PartPurchase pp on p.PID.=pp.PID join Customer c on c.CID=pp.CID where c.City='SF'

5. select s.SID as ID, s.SName as Name, s.Status as Status ,s.City as City from Supplier s join PartPurchase pp on s.SID=pp.SID where s.Status= 'WomenOwn' join Part p on p.PID=pp.PID where p.color='blue' join Customer c on c.CID=pp.CID where c.Phone='787-830-0000'

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.