Saturday, August 30, 2014

SQL Exercise 3

Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Queries involving selection, projection, joins, and order by clauses

Solve and write necessary Queries for the case given below;


Consider the relation schemas of a selling database given below. Primary keys of all the tables are underlined.

PART(Part_Number, Part_Description, Quantity_On_Hand, Class, Warehouse, Price)
ORDERLINE(Order_Number, Part_Number_Ordered, Quantity_Ordered, Quoted_Price)
ORDERS(Order_Number, Order_Date, Customer_Number)
CUSTOMER(Customer_Number, Last_Name, First_Name, Street, City, State, Pincode, Balance, Credit_Limit, Sales_Representative_Number)
SALESREP(Sales_Representative_Number, Last_Name, First_Name, Street, City, State, Pincode, Commission, Rate)

Write SQL Queries to achieve the following;
1. Display the Part_Number, Part_Description, Quantity_On_Hand, and Price of all the parts in ascending order of Part_Description.
2.  List down the Pincode, Last_name, Street, City, and State of every customer in the ascending order of the Pincode.
3.  Produce a list showing Part_Number, Part_Description, Quantity_On_Hand, and Price sorted by both Warehouse and Class.
4.   List all the records of ORDERLINE by selecting only rows where the Quantity_Ordered is greater than or equal to 2.
5.  List all customers’ Last_Name and First_Name whose Credit_Limit is less than or equal to 10000.
6.   List the Last_Name and First_Name of customers whose Credit_Limit is greater than or equal to 10000 and Pincode is 649219.
7.    Display all the parts that have a Part_Number that begins with ‘B’
8.   Find the part number, part description, number of parts ordered and the quoted price for every part that are ordered.
9.   List the Part_Number, Part_Description, Quantity_Ordered and Quoted_Price of all the parts whose Part_Number begins with ‘C’
10. Find the order number, date of order, of every order made by customer along with the part numbers, part description, number of parts ordered and the quoted price of the part.
11. List the Order_Number, Order_Date, Part_Number, Part_Description, Quantity_Ordered and Quoted_Price of all the orders where the minimum quantity ordered is at least 10.
12.Find the Customer’s Last_Name and First_Name followed by the Order_Number, Part_Description and Quantity_Ordered for all of his orders.


Answers:
In all the queries, the words mentioned in ALL CAPS (except in tables) are keywords used in SQL.
 

1. Display the Part_Number, Part_Description, Quantity_On_Hand, and Price of all the parts in ascending order of Part_Description.
What we need to display? (SELECT clause)
Part_Number, Part_Description, Quantity_On_Hand, and Price
Where do we get it?     (Table/Tables) (FROM Clause)
Part table
Conditions (if any)
(WHERE clause)
No Conditions
Special Information
(Any other clauses)
Order the tuples on Part_Description in ascending order

Query:
SELECT  Part_Number, Part_Description, Quantity_On_Hand, Price
FROM  Part
ORDER BY Part_Description;

2. List down the Pincode, Last_name, Street, City, and State of every customer in the ascending order of the Pincode.
What we need to display? (SELECT clause)
Pincode, Last_name, Street, City, and State of every customer
Where do we get it?     (Table/Tables) (FROM Clause)
Customer table
Conditions (if any)
(WHERE clause)
No conditions
Special Information
(Any other clauses)
Order the records on Pincode attribute in ascending order

Query:
SELECT  Pincode, Last_Name, Street, City, State
FROM Customer
ORDER BY Pincode;
3. Produce a list showing Part_Number, Part_Description, Quantity_On_Hand, and Price sorted by both Warehouse and Class.
What we need to display? (SELECT clause)
Part_Number, Part_Description, Quantity_On_Hand, and Price of parts
Where do we get it?     (Table/Tables) (FROM Clause)
Part table
Conditions (if any)
(WHERE clause)
No Conditions
Special Information
(Any other clauses)
Order by both Warehouse and Class attributes in ascending order

Query:
SELECT  Part_Number, Part_Description, Quantity_On_Hand, Price
FROM  Part
ORDER BY Warehouse, Class;
(Note: This query will order all the records on Warehouse attribute first and if there are more records with same Warehouse values will be orders based on the Class value in ascending order)
4. List all the records of ORDERLINE by selecting only rows where the Quantity_Ordered is greater than or equal to 2.
What we need to display? (SELECT clause)
Whole table, i.e., all the columns. Hence, we would use * to mention all the attributes.
Where do we get it?     (Table/Tables) (FROM Clause)
Orderline table
Conditions (if any)
(WHERE clause)
The ordered part’s Part Number should be greater than or equal to zero, i.e, Part_Number_Ordered >=2
Special Information
(Any other clauses)
Nil

Query:
SELECT  *
FROM Orederline
WHERE Part_Number_Ordered >= 2;
5. List all customers’ Last_Name and First_Name whose Credit_Limit is less than or equal to 10000.
What we need to display? (SELECT clause)
Last name and first name of the customers
Where do we get it?     (Table/Tables) (FROM Clause)
Customer table
Conditions (if any)
(WHERE clause)
Value of credit_limit is <=10000
Special Information
(Any other clauses)
Nil

Query:

SELECT Last_Name, First_Name
FROM Customer
WHERE Credit_Limit <= 10000;
6. List the Last_Name and First_Name of customers whose Credit_Limit is greater than or equal to 10000 and Pincode is 649219.
What we need to display? (SELECT clause)
Last name and first name of the customers
Where do we get it?     (Table/Tables) (FROM Clause)
Customer table
Conditions (if any)
(WHERE clause)
Value of credit_limit is >=10000
Pincode should be 649219
Special Information
(Any other clauses)
Nil

Query:
SELECT Last_Name, First_Name
FROM Customer
WHERE Credit_Limit >= 10000 AND Pincode = 649219;



7. Display all the parts that have a Part_Number that begins with ‘B’
What we need to display? (SELECT clause)
* / All the columns
Where do we get it?     (Table/Tables) (FROM Clause)
Parts table
Conditions (if any)
(WHERE clause)
Part numbers that start with B. For example, ‘B101’

Special Information
(Any other clauses)
Nil

Query:
SELECT *
FROM Parts
WHERE Part_Number LIKE ‘B%’;
(Note: in the question, the given condition specifies a portion of the value. That means, we need to match a substring of the actual values. Hence, we have to use the keyword LIKE. LIKE matches the substring given to the right of it with the values stored in the column which is to the left of LIKE)


8. Find the part number, part description, number of parts ordered(Quantity_Ordered) and the quoted price for every part that are ordered.
What we need to display? (SELECT clause)
Part number, part description, quantity ordered and quoted price
Where do we get it?     (Table/Tables) (FROM Clause)
1. Part_Number and Part_Description are available in Table PART.
2. Quantity_Ordered and Quoted_Price are part of ORDERLINE table.
Part_Number_Ordered attribute (foreign key) of ORDERLINE table references the value of Part_Number attribute of PART table.
Two tables are involved – PART and ORDERLINE
Conditions (if any)
(WHERE clause)
Only join condition (in case of CARTESIAN Product). That is, the values of common attributes of both tables should be matched.
Special Information
(Any other clauses)
Nil

Query:
SELECT Part_Number, Part_Description, Quantity_Ordered, Quoted_Price
FROM Part, Orderline
WHERE Part.Part_Number = Orderline.Part_Number_Ordered;
(Note: The dot notation ‘Part.Part_Number’ is used because in some cases the common attributes might be named with same attribute name. For the above query, we can even mention the WHERE condition as Part_Number = Part_Number_Ordered as these two are pointing same domain with different names.)
9. List the Part_Number, Part_Description, Quantity_Ordered and Quoted_Price of all the parts whose Part_Number begins with ‘C’
What we need to display? (SELECT clause)
Part number, part description, quantity ordered and quoted price
Where do we get it?     (Table/Tables) (FROM Clause)
1. Part_Number and Part_Description are available in Table PART.
2. Quantity_Ordered and Quoted_Price are part of ORDERLINE table.
Part_Number_Ordered attribute (foreign key) of ORDERLINE table references the value of Part_Number attribute of PART table.
Two tables are involved – PART, and ORDERLINE
Conditions (if any)
(WHERE clause)
First condition is join condition (CARTESIAN Product) which matches the values of common attributes of both tables.
Second condition mentions only the part numbers that begins with the alphabet ‘C’.
Special Information
(Any other clauses)
Nil

Query:
SELECT Part_Number, Part_Description, Quantity_Ordered, Quoted_Price
FROM Part, Orderline
WHERE Part.Part_Number = Orderline.Part_Number_Ordered AND Part_Number LIKE ‘C%’;
[Refer the note of Query 7]
10. Find the order number, date of order of every order made by customer along with the part numbers, part description, number of parts ordered and the quoted price of the part.
What we need to display? (SELECT clause)
Order number, Order date, Part number, Part description, Quantity ordered and quoted price
Where do we get it?     (Table/Tables) (FROM Clause)
1. Part_Number and Part_Description are available in Table PART.
2. Quantity_Ordered and Quoted_Price are part of ORDERLINE table.
Part_Number_Ordered attribute (foreign key) of ORDERLINE table references the value of Part_Number attribute of PART table.
3. Order_Number and Order_Date are part of ORDER table.
Order_Number attribute (foreign key) of ORDERLINE table refers Order_Number attribute (Primary Key) of ORDER table.
Three tables are involved – PART, ORDERLINE and ORDER
Conditions (if any)
(WHERE clause)
Only the join condition (CARTESIAN Product) which matches the values of common attributes of all the three tables.
One condition matches the common attributes of PART and ORDERLINE and the second join condition matches the common attributes of ORDERLINE and ORDER tables.
Special Information
(Any other clauses)
Nil
Query:
SELECT Order.Order_Number, Order_Date, Part_Number, Part_Description, Quantity_Ordered, Quoted_Price
FROM Order, Part, Orderline
WHERE Order.Order_Number = Orderline.Order_Number AND Part.Part_Number = Orderline.Part_Number_Ordered;
(Note: Observe from the above query for dot notation Order.Order_Number. In this case, we must use dot notation and specify which table we refer. Otherwise, it will cause ambiguity. That is, if we use Order_Number without prefix the name of the table, the query processor confused with the name as Order_Number available in both tables ORDER and ORDERLINE)
11. List the Order_Number, Order_Date, Part_Number, Part_Description, Quantity_Ordered and Quoted_Price of all the orders where the minimum quantity ordered is at least 10.
What we need to display? (SELECT clause)
Order number, Order date, Part number, Part description, Quantity ordered and quoted price
Where do we get it?     (Table/Tables) (FROM Clause)
1. Part_Number and Part_Description are available in Table PART.
2. Quantity_Ordered and Quoted_Price are part of ORDERLINE table.
Part_Number_Ordered attribute (foreign key) of ORDERLINE table references the value of Part_Number attribute of PART table.
3. Order_Number and Order_Date are part of ORDER table.
Order_Number attribute (foreign key) of ORDERLINE table refers Order_Number attribute (Primary Key) of ORDER table.
Three tables are involved – PART, ORDERLINE and ORDER
Conditions (if any)
(WHERE clause)
One join condition matches the common attributes of PART and ORDERLINE and the second join condition matches the common attributes of ORDERLINE and ORDER tables.
The other condition verifies for the quantity greater than or equal to 10.
THREE conditions are used.
Special Information
(Any other clauses)
Nil

Query:
SELECT Order.Order_Number, Order_Date, Part_Number, Part_Description, Quantity_Ordered, Quoted_Price
FROM Order, Part, Orderline
WHERE Order.Order_Number = Orderline.Order_Number AND Part.Part_Number = Orderline.Part_Number_Ordered AND Quantity_Ordered >= 10;
12. Find the Customer’s Last_Name and First_Name followed by the Order_Number, Part_Description and Quantity_Ordered for all of his orders.
What we need to display? (SELECT clause)
Last name and First name of the customers, Order number, Part description, and Quantity ordered
Where do we get it?     (Table/Tables) (FROM Clause)
1. Part_Description is available in Table PART.
2. Quantity_Ordered is part of ORDERLINE table.
Part_Number_Ordered attribute (foreign key) of ORDERLINE table references the value of Part_Number attribute of PART table.
3. Customer First name and Last name are part of CUSTOMER table.
Customer_Number attribute (foreign key) of ORDER table refers Customer_Number attribute (Primary Key) of CUSTOMER table.
CUSTOMER and is linked to ORDERLINE through ORDER table only.
Hence, four tables are involved – PART, ORDERLINE ORDER, and CUSTOMER
Conditions (if any)
(WHERE clause)
One join condition matches the common attributes of PART and ORDERLINE, the second join condition matches the common attributes of ORDERLINE and ORDER tables, third join condition matches CUSTOMER with ORDER.
THREE conditions are used.
Special Information
(Any other clauses)
Nil

Query:
SELECT Last_Name, First_Name, Order.Order_Number, Part_Description, Quantity_Ordered
FROM Customer, Order, Orderline, Part
WHERE Customer.Customer_Number = Order.Customer_Number AND Order.Order_Number = Orderline.Order_Number AND Part_Number = Part_Number_Ordered;
(Note: Here, the condition Part_Number = Part_Number_Ordered is written without prefixing the table names which is valid)











Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery