Exam samples

1

Good day everyone. I am a newbie in this forum and in SQL. Just started learning. So i have some exam sample questions here? Is it ok if i post the questions and give my answers and u guys help to verify if it is right? Here we go.

SUPPLIER(Supp_ID , Supp_Name, CITY)
PARTS(Part_Num, Part_Name, Colour)
PROJECT(Project_Num, Project_Name, City)
QUANTITY(Supp_ID, Part_Num, Project_Num, Qty)

Explain the following SQL query statement.

SELECT Supp_Name FROM SUPPLIER WHERE CITY= 'Alor Star'
My answer= the statement states that the supplier name will be obtained from the supplier field where the the supplier is from Alor Star.

Select Part_Num, Project_Num, AVG(Qty)
FROM QUANTITY
WHERE Project_Num ='PJT007'
GROUP BY Part_Num
My answer=the statement states that the part number, project number, average of the quantity will be selected from the quantity field. It will then specify by choosing it from project Num of PJT007. Lastly, it will group it according to the part number.

Write an SQL statement to find name of suppliers who supply parts to projects in the city which they are based.
My answer=
SELECT Supp_Name, Part_Name, Project_Name, City
FROM Supplier, Parts , Project
Where City=Supplier

Is it correct or there are ammendments. If so, pls help. I have no where to ask for help. Tq.

2

better: return Supp_Name from the SUPPLIER Table where the CITY column is equal to 'Alor Star'

Not quite. Select happens after filtering and grouping. Try again

Better in ANSI format

SELECT s.Supp_Name, pa.Part_Name, pr.Project_Name, s.City
FROM Supplier s
JOIN Parts pa on -- add a join predicate
JOIN Project pr  -- add a join predicate
Where -- don't know what to put here.  can't see the schema of the other tables
1 Like
3

What is a join predicate? Im really new in SQL so yeah...

4

For the 2 question, this is my 2 try. Pls help.
The statement will first sort the project number that matches PJT007 followed by the part name. Once this is done, it will select the data that matches the criteria from the quantity table together with the quantity which already has been averaged.

5

Well, it won't do a sort. What happens is this:

  1. Filter the rows by only using those that have a Project Number = 'PJT007'
  2. Group those rows by part number
  3. Take the average of the quantity parts within those groups
  4. project the Part Number, Project Number and the computed average
1 Like
6

Fancy math term for condition.

SELECT ...
FROM table1
JOIN table2 on table1.somecolumn = table2.columnwithmatchingdata
7

So for ur answer in the ANSI for the last question, is it complete or incomplete coz of the last one?

8

What you posted won't work (A column (City) can't equal a table (Supplier)) . What I posted is not complete, just a guideline