QuestionQuestion

Transcribed TextTranscribed Text

1. 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) The key fields are underlined, and the domain of each field is listed after the field name. Therefore 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. Write the following queries in SQL and test on a DBMS: 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 part or are at 221 Packer Street. d. Find the sids of suppliers who supply some red part and some green part. c. Find the sids of suppliers who supply every part. f. Find the sids of suppliers who supply every red part. g. Find the sids of suppliers who supply every red or green part. h. Find the sids of suppliers who supply every red part or supply every green part. i. Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid. j. Find the pids of parts supplied by at least two different suppliers. 2. This assignment is to be completed on a DBMS of your choice. Indicate the name and version of your DBMS. 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) The Catalog relation lists the prices charged for parts by Suppliers. sid sname address pid pname colour sid pid cost sl smith london pl nut red sl pl 200 s2 jones paris p2 bolt green sl p3 300 s3 blake paris p3 screw red s2 pl 100 s4 clark london p4 cam blue s2 p2 400 s5 adams athens p5 cog green s2 p3 200 s6 (you) regina s2 p4 300 s2 p5 100 s3 p2 200 s3 p5 400 s4 p4 400 s5 pl 100 s5 p2 200 Write the following queries in SQL: 1. Find the pnames of parts for which there is some supplier. 2. Find the snames of suppliers who supply every part. 3. Find the snames of suppliers who supply every red part. 4. Find the pnames of parts supplied by S1 and no one else. 5. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part). 6. For each part, find the sname of the supplier who charges the most for that part. 7. Find the sids of suppliers who supply only red parts. 8. Find the sids of suppliers who supply a red part and a green part. 9. Find the sids of suppliers who supply a red part or a green part. 10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. 11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies. Hand in your SQLquery statement and the test results.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

1. d) Find the sids of suppliers who supply some red part and some green part.

SELECT C.sid
FROM Parts P, Catalog C
WHERE P.color='red' and P.pid=C.pid
AND EXISTS (
SELECT P2.pid
FROM Parts P2, Catalog C2
WHERE P2.color = 'green' AND C2.sid =C.sid AND P2.pid = C2.pid)...
$25.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats