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)