Assume two tables User and Goods. The tables are related by User\'s Uname field
ID: 3718766 • Letter: A
Question
Assume two tables User and Goods. The tables are related by User's Uname field linked to Goods' Gname field. User will always have a unique Uname, but goods will contain multiple records where some users have purchased goods multiple times (like Mel). Some sample rows are as follows:
User (Uname is unique and will not be repeated)
Uname | addr
Mel | 123 North
Will | 567 South
Snarf | 890 East
Sandy | 4567 West
Goods (Mel has made 2 purchases, Sandy made one)
GName | totalPurch
Mel | 92
Sandy | 91
Mel | 60
a) Write a select query that will select the person's name, the address of the person, and purchases that are more than 60.
The columns displayed must be only the follow (dont worry about casing):
Uname | addr | totalPurch
Rows must show only purchases more than 60 and must show higher totalPurch values first (descending).
b) Show all Uname from the User table that do NOT have any records in Goods. So, given the above sample data, we should see 2 rows:
Uname
Snarf
Will
Snarf and Will did not make any purchases accoring to the sample data.
Properly label your two queries
Explanation / Answer
a. SQL query is :
select Uname, addr, totalPurch from User u, Goods g where u.Uname = g.GName and totalPurch > 60 order by totalPurch desc;
The above query will select Uname and addr from User table and totalPurch from Goods table by joining the tables on th fields Uname(User) and GName(Goods) and whose totalPurch > 60. order by clause is used to order the result based on totalPurch in descending order i.e higherPurch will be shown first and then lowerPurch.
b. SQL query is :
select distinct(Uname) from User where Uname not in (select distinct(GName) from Goods);
The subquery : select distinct(GName) from Goods will select the distinct GName in Goods table.
The main query will select only those Uname which are not present in the above subquery i.e all the Uname that are present in User table but not present in Goods table.