Suppose a database consists of the following relations: SUPPLIER (SCODE, SNAME, CITY) PART (PCODE, PNAME, PDESC, CITY) PROJECTS (PRCODE, PRNAME, PRCITY) SPPR (SCODE, PCODE, PRCODE, QTY) Write SQL programs corresponding to the following queries: i. Print PCODE values for parts supplied to any project in DELHI by a supplier in DELHI. ii. Print all triples <CITY, PCODE, CITY> such that a supplier in the first city supplies the specified part to a project in the second city, but do not print the triples in which the two CITY values are the same. Which of the following SQL queries correctly answers query (i)? A. SELECT PCODE FROM SPPR, SUPPLIER, PROJECTS WHERE SPPR.SCODE = SUPPLIER.SCODE AND SPPR.PRCODE = PROJECTS.PRCODE AND SUPPLIER.CITY = 'DELHI' AND PROJECTS.PRCITY = 'DELHI' B. SELECT PCODE FROM SPPR WHERE SCODE IN (SELECT SCODE FROM SUPPLIER WHERE CITY = 'DELHI') C. SELECT PCODE FROM PART WHERE CITY = 'DELHI' D. SELECT PCODE FROM SPPR, PROJECTS WHERE SPPR.PRCODE = PROJECTS.PRCODE AND PRCITY = 'DELHI'

GATE 1991 · Databases · SQL · medium

Answer: A. SELECT PCODE FROM SPPR, SUPPLIER, PROJECTS WHERE SPPR.SCODE = SUPPLIER.SCODE AND SPPR.PRCODE = PROJECTS.PRCODE AND SUPPLIER.CITY = 'DELHI' AND PROJECTS.PRCITY = 'DELHI'

1. Analyze Option A: This performs a three-way join of SPPR, SUPPLIER, and PROJECTS with proper join conditions and both city filters. It correctly retrieves PCODE for parts supplied by a Delhi supplier to a Delhi project.
2. Analyze Options B, C, D: Option B: Only checks supplier city = DELHI, ignores project city condition — incomplete. Option C: Checks PART.CITY = 'DELHI' which is the city where the part is stocked, not supplier/project city — wrong interpretation. Option D: Only joins SPPR with PROJECTS, checks project city but ignores supplier city condition — incomplete.
3. SQL for Part (ii) — triples with different cities: Join SPPR with SUPPLIER and PROJECTS; filter where SUPPLIER.CITY != PROJECTS.PRCITY; select the triple <SUPPLIER.CITY, PCODE, PROJECTS.PRCITY>.