Oracle SQL Plus Q2. Consider the following relations. Primary key attributes are
ID: 3714146 • Letter: O
Question
Oracle SQL Plus
Q2. Consider the following relations. Primary key attributes are underlined. Product (productCode, productName, category.description) Manufacturer (manuCode, manufacturerName, city, Phone) Supply(manuCode, productCode, storeID, wholesaleUnitPrice, quantity) Store(storeID, storeName, phoneNumber, city) Formulate the following queries in SQL. (a) Find names of all stores that are in the city of "Kuala Lumpur".(3 marks) (b) Find the name of the manufacturer that supplies the largest quantity of any (4 marks) product. (Note : Must use Subquery) (c) Find the names and cities of all manufactures that supply any product of more (4 marks) than 100 units whose wholesale unit price is greater than 50 (d) Find the names of store-manufacturer pairs where the store and the manufacturer in each pair is located in the same city and there is a supply record of the manufacturer whose total cost (i.e. unit price multiplied by quantity) is greater than 10,000 (4 marks) (e Find the store name, city and product name of all the products whose wholesale unit price is less than 100 and the city is not "Johor Bahru" (5 marks) (t) Write SQL code to convert query from question 2(e) into a View name as "WholesaleJB" and identify TWO (2) advantages of using Views as a way of implementing database queries (5 marks)Explanation / Answer
Note: I created sample tables as per the given question and implemented below queries on those tables.
Below queries are working fine and giving desired output. Please let me know, if you have any doubts.
Don't forget to rate me.
Queries:
a) SELECT storeName FROM dbo.Store WHERE city LIKE 'Kuala Lumpur'
b) SELECT a.manufacturerName
FROM dbo.Manufacturer a INNER JOIN dbo.Supply b ON b.manuCode = a.manuCode
WHERE b.quantity
= (
SELECT MAX(quantity)
FROM dbo.Supply
)
c) SELECT m.manufacturerName, m.city FROM dbo.Manufacturer m
INNER JOIN dbo.Supply s ON s.manuCode = m.manuCode
WHERE s.quantity > 100 AND s.wholesaleUnitPrice > 50.00
d) SELECT m.manufacturerName +' - '+ s.storeName AS 'store-manufacturer pair'
FROM dbo.Manufacturer m
INNER JOIN dbo.Store s ON s.city = m.city
INNER JOIN dbo.Supply p ON p.manuCode = m.manuCode
WHERE p.wholesaleUnitPrice * p.quantity > 10000
e) SELECT s.storeName, s.city, p.productName FROM dbo.Store s
INNER JOIN dbo.Supply c ON c.storeID = s.storeID
INNER JOIN dbo.Product p ON p.productCode = c.productCode
WHERE c.wholesaleUnitPrice < 1000 AND s.city <> 'Johor Bahru'
f) CREATE VIEW WholesaleJB AS
SELECT s.storeName, s.city, p.productName FROM dbo.Store s
INNER JOIN dbo.Supply c ON c.storeID = s.storeID
INNER JOIN dbo.Product p ON p.productCode = c.productCode
WHERE c.wholesaleUnitPrice < 1000 AND s.city <> 'Johor Bahru'
Advantages:
1) Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, so it will restrict the user's access to stored data.
2) Views can give a user a "personalized" view of the database structure, presenting the database as a set of virtual tables that make sense for that user.
3) If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.