MANAGMENT INFORMATION SYSTEMS IS SUBJECT WAS UNSURE WHERE TO PUT THIS 1. If a DB
ID: 3714122 • Letter: M
Question
MANAGMENT INFORMATION SYSTEMS IS SUBJECT WAS UNSURE WHERE TO PUT THIS
1. If a DBMS enforces a DELETE CASECADE OPTION on the referential integrity constraint between SALES_REP and TERRITORY show the records in tables SALES_REP and TERRITORY after a user tries to delete the first record (1, Joe,E) from SALES_REP.
2. Display the ProductID, ProductName, ProductPrice, VendorName for all procuts. Sort the results by Product ID. (WRITE SQL queries that accompish this task.)
It's request that we write the query twice once by JOIN and once by SUBQUERY.
This is what i wrote i'm not sure if it classifies for subquery or join but here it is.
SELECT ProductID, ProductName, ProductPrice,
FROM Product p, Category c,
WHERE p.CategoryID= c.CategoryID
AND c.CategoryName= ‘Camping’
ORDER BY ProductID;
Help on these two problems would be greatly appreciated :)
SALES REP TERRITORY SRIDSRName TerID TerID TerName oe Sue Meg Bob Joe Pat Lee Joe East West South North Central 3 5 8Explanation / Answer
1. Only first record (1, Joe,E) will get deleted from SALES_REP rest all remains same as SALES_REP is the child table. Incase the first record was deleted from the parent table i.e. TERRITORY then first two records from SALES_REP would get deleted as they have TerID='E' which has been deleted from parent table TERRITORY.
2. --BY JOIN
SELECT ProductID, ProductName, ProductPrice,
FROM Product p, Category c
WHERE p.CategoryID= c.CategoryID
AND c.CategoryName= ‘Camping’
ORDER BY ProductID;
--BY SUBQUERY
SELECT ProductID, ProductName, ProductPrice
FROM Product WHERE CategoryID IN (SELECT CategoryID FROM Category WHERE CategoryName= ‘Camping’)
ORDER BY ProductID;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.