Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

2. Part 2 Given a set of tables with their content (tuples/rows), show what would be retrieved if the given SQL queries were issued Consider

image text in transcribedimage text in transcribed

2. Part 2 Given a set of tables with their content (tuples/rows), show what would be retrieved if the given SQL queries were issued Consider tables, of a Supplier-Parts DB, shown in Figure 2. Names of tables, columns and identification of primary foreign keys are shown. Questions in this part ask you to show what would be displayed by the following SQL queries should they be executed against the tables having content (tuples/rows) shown in Figure 2. In each answer to a question, do not forget to display column headings in addition to the tuples/rows that would be shown. + and Note for those who are familiar with the SAFE mode assume that the SAFE mode is turned off (although it should not matter). If you feel that there is an error in the statement, e.g. instead of "FROM", it is spelled "FORM", please make and note a reasonable assumption (e.g., I assume that "FORM" should have been "FROM") and proceed. Also, consider both single or double quotes to be correct for string literals +' 1. SELECT ppo, color- FROM WHERE color Red p,pop, color- p JOIN USING (pno) color Red 2. SELECT FROM WHERE 3. SELECT popgo, color, suo, qty FROM WHERE color 'Red': 4. SELECT FROM WHERE s NATURAL JOIN sp. JOIN p color 'Red': 5. SELECT ssne, sp.sno, sp.png, qty, p.pno FROM WHERE city = 'Paris, AND color = "Green, AND &sno= spoSng AND spopAR = ppuN. 6. SELECT FROM WHERE S, SD. P city Paris' AND color- Green'; * 7. SELECT FROM WHERE S, Sp. p city = 'Paris, AND color = 'Green, AND ssna= sposo; Relation S... contains information about suppliers SNAME STATUS CITY Supplier number (PK) primary ke Supplier name Supplier status le.g., reliability code between 1 and 5,supplier's location y....varchar with i being best, 5 worst... int Relation P. contains information about parts PNAME Name of the part+ varchar Weight supplier location Part number (PK) primary key Part color " Relation SP. contains information about which parts are supplied by which suppliers in which quantities+ QTY Part color Supplier number (PK) (FK)+ Part number (PK) (FK) art of primary kev, foreign ke yvarchar- art of primar key. varchar- MORE COMPACT FORM PH SNO (PK) SNAMESTATUS CITY PNO (PK).PNAME COLOR WEIGHT SP+ SNO PK) (FK) PNO (PK) (FK) QTY Figure 1. Supplier-Parts DB Tables* PH SNO(PK) | SNAME | STATUS Smith Jones Blake PNO PKPNAME COLOR WEIGHT 20 10 30 20 30 P1 P2 P3 P4 P5 Nut S2 S3 S4 S5 Blue Paris 14 Athens Cam Blue SNO 300 200 P1 P2 P3 P4 P5 P1 P2 P3 Pl P2 P3 P2 P4 200 100 300 S2 S2 S3 S3 S3 S4 S4 S5 200 100 200 200 200 Figure 2. Content of the Supplier-Parts DB Tables

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Database Management Systems

Authors: Patricia Ward, George A Dafoulas

1st Edition

1844804526, 978-1844804528

More Books

Students also viewed these Databases questions

Question

2. What efforts are countries making to reverse the brain drain?

Answered: 1 week ago