Suppose table User has attributes email, name, address, and householdSize, where email being the primary key.
(a) Express the above query in SQL.
(b) Write in SQL the query that nds the users whose household size is at least 50% more
than the average household size and returns their name and household size, sorted by
household size. (Hint: decompose the problem into subproblems, and you may use a
view to capture a intermediate result.)
(c) Write in SQL the query that nds all users each having a household size di erent from
the total number of users having the same address as him or her. (Hint: again, decompose
the problem into subproblems, and you may use a view to capture a intermediate result.)
(a)
SELECT U.name, U2.name, U.address
FROM User U, User U2
WHERE U.name <> U2.name AND U.householdSize=2 AND U2.householdSize=2
AND U.address=U2.address
(b)
CREATE VIEW AvgHsize (avghsize) AS
SELECT AVG(U.householdSize)
FROM User U
SELECT U.name, U.householdSize
FROM User U, AvgHsize A
WHERE U.householdSize >= 1.5 * A.avghsize
ORDERED BY householdSize
(c)
CREATE VIEW AddrAndCount (address,count) AS
SELECT U.address, COUNT(*)
FROM User U
GROUP BY U.address
SELECT U.*
FROM User U, AddrAndCount A
WHERE U.address=A.address AND U.householdSize <> A.count
You might also like to view...
The ________ function can be used to find the smallest value in a range of numbers
Fill in the blank(s) with the appropriate word(s).
The ____ contains the variables for each merged document.
A. data source B. filter C. main document D. preview pane
A(n) ______ device is a small, mobile computing device designed to be worn. Examples include activity trackers and smartwatches.
Fill in the blank(s) with the appropriate word(s).
?Briefly explain plug-ins with the help of a browser serving as an example.
What will be an ideal response?