EXERCISE 1 (SQL Queries) Consider the following schema: SUPPLIERS (SID : integer, SNAME : string, CITY : string) PARTS (PID : integer, PNAME : string, COLOR : string) CATALOG (SID : integer, PID : integer, COST : real) The key fields are underlined, and the domain of each field is listed after the field name. Thus, SID is the key for SUPPLIERS, PID is the key for PARTS, and SID and PID together form the key for CATALOG. The CATALOG relation lists the prices charged for parts by suppliers. CATALOG.SID is a foreign key referring to SUPPLIERS.SID and CATALOG.PID is a foreign key referring to PARTS.PID. Write the following queries in SQL.
7. For every part supplied by a supplier who is at the city of Newark, print the PID and the SID and the name of the suppliers who sell it at the highest price.
8. For every part which has at least two suppliers, find its PID, its PNAME and the total number of suppliers who sell it.
9. Find the PIDs of parts supplied by every supplier who is at the city of Newark or by every supplier who is at the city of Trenton.
10. Find the PIDs of parts supplied by every supplier who is at the city of Newark and by every supplier who is at the city of Trenton. 11. Find the SIDs of suppliers who supply a red part but do not supply a blue part.
7.
The SQL query for the given question would be as follows :
Select CATALOG.PID,CATALOG.SID,SUPPLIERS.SNAME from CATALOG
left join SUPPLIERS on
CATALOG.SID=SUPPLIERS.SID
group by CATALOG.PID
having CATALOG.COST = max(CATALOG.COST)
where SUPPLIERS.CITY = 'Newark';
8.
The SQL query for the given question would be as follows :
select CATALOG.PID,PARTS.PNAME,count(CATALOG.SID) from
CATALOG
left join PARTS on
PARTS.PID=CATALOG.PID
group by CATALOG.PID
having count(CATALOG.SID)>=2;
9.
The SQL query for the given question would be as follows :
select CATALOG.PID from CATALOG
left join SUPPLIERS on
CATALOG.SID=SUPPLIERS.SID
where SUPPLIERS.CITY in ('Newark','Trenton');
10.
The SQL query for the given question would be as follows :
select CATALOG.PID from CATALOG
left join SUPPLIERS on
CATALOG.SID=SUPPLIERS.SID
where SUPPLIERS.CITY = 'Newark' and SUPPLIERS.CITY='Trenton';
Note :
I am able to answer only the first 4 questions as per the Chegg guidelines.
Please comment and let me know if you have any doubts.
Thank you.
Have a good day.
EXERCISE 1 (SQL Queries) Consider the following schema: SUPPLIERS (SID : integer, SNAME : string, CITY...
Consider the following schema: SUPPLIERS (SID: integer, SNAME: string, STREET: string, CITY: string, ZIP: string) PARTS (PID: integer, PNAME: string, COLOR: string) CATALOG (SID: integer, PID: integer, COST: real) The primary key attributes are underlined, and the domain of each attribute is listed after the attribute name. Thus, SID is the primary key for SUPPLIERS, PID is the primary key for PARTS, and SID and PID together form the primary key for CATALOG. Attribute SID in CATALOG is a foreign...
Please finish all parts, thanks! 2) Consider the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid:integer, pname: string, color: string ) Catalog( sid: integer, pid: integer, cost: real) Write the following queries in relational algebra. a) Find the names of suppliers who supply some red part b) Find the sids of suppliers who supply some red or green part c) Find the sids of suppliers who supply some red and some green part. d) Find the sids of suppliers...
Suppliers(sid: integer, sname: string, address:string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)The Catalog relation lists the prices charged for parts bySuppliers. Write the following queries in SQL:1) Find the pnames of parts supplied by Acme Widget Suppliers andno one else.2) Find the sids of suppliers who charge more for some partthan the average cost of that part (averaged over all the supplierswho supply that part).3) For each part, find the sname of the supplier who chargesthe most...
In .sql Given the following relational schemas, answer the following questions: Suppliers(sid: int, sname: VARCHAR(30), address: VARCHAR(50)) Parts(pid: int, pname: VARCHAR(30), color: VARCHAR(10)) Catalog(sid: int, pid: int, cost: double) c. (8 points) List sid, sname, and address of all suppliers who supply at least one part. In other words, the answer must not show sid and sname of any supplier who does not have its sid in the Catalog table d. (4 points) Find all distinct black parts in the...
C. Answer the following five (5) questions, based on the schema provided.Consider the following schema:Supplier (sid: integer, sname: string, address: string)Part(pid: integer, pname: string, , color: string)Catalog(sid: integer, pid: integer, cost: real)The relation Supplier stores suppliers and the primary key of that relation is sid. The relation Part stores parts, and pid is the primary key of that relation. Finally, Catalog stores which supplier supplies which part and at which cost (price). The primary key is the combination of the...
Any help appreciated! Question 1 (5 points). Answer each of the following questions briefly. The questions are based on the following relational schema: Emp( eid: integer, ename: string, age: integer, sala1l1: real) Works( eid: integer, did: integer, pet_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) 1. Give an example of a foreign key constraint that involves the Dept relation. 2. Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign...
consider the following relational database that records details of parts and suppliers. Primary keys are underlined and the Foreign Keys are identified with (FK). Suppliers (sid, sname, address, credit) Parts (pid, pname, color) Catalog (sid (FK), pid (FK), cost) The credit attribute denotes the size of the supplier’s credit account. The Catalog relation lists the prices charged for parts by Suppliers. Which of the following queries returns the snames of suppliers who supply every part? Select one or more: a....
Consider a database schema with three relations: - Parts (pid:integer, pname:string, year:integer, price:integer) -Suppliers (sid:integer, sname: string, state:string, zipcode:string) -Orders (pid:integer, sid:integer, quantity:integer) The description is as follows: a factory keeps a database with parts that it uses, the suppliers of those parts, and purchase orders. Each part is uniquely identified by pid. Each part has a string description pname, year of fabrication and price per unit. Parts are provided by suppliers, and each supplier is uniquely identified by sid....
Consider the following database Relation Schemas: Relation Schemas: Suppliers(SID CHAR(5), Parts(pID VARCHAR(5), type VARCHAR(15), pName VARCHAR(35), sName VARCHAR(15), address VARCHAR(30, city VARCHAR(20), state CHAR(2), PRIMARY KEY(sID); PRIMARY KEY(pID) Catalog(sID CHAR(5), pID VARCHAR(5), ty SMALLINT, cost FLOAT (10,2), PRIMARY KEY(sid, pid), FOREIGN KEY(sid) REFERENCES Suppliers(SID), FOREIGN KEY (pid) REFERENCES Parts pID) The meaning of these relations is straightforward; for example, the Catalog relation lists the prices charged for parts by Suppliers. Instances of the relations Suppliers sName address SID cit state...
Please HELP, THANK YOU SO MUCH Consider the below schema of the university database (keys are in bold and underline) Part(pid: string, pname: string, description: string, color: string) Department(did: string, dnamestring, address: string) Supplier(sid string,.sname:string, address:string) Order(did:string. sid string pidstring, time:string, quantityreal, pricereal) The key fields are underlined, and the domain of each field is listed after the field name. Thus pid is the key for Part, did is the key for Department, sid is the key for Supplier, and...