Practice Problem 1
CSE 3104 – Assignment 01
Question: Write suitable SQL commands for the following tasks.
1. Create a table (Passenger) based on the following specifications.
Attribute Type
Passenger_ID Integer
Passenger_FName Varchar
Passenger_LName Varchar
Passenger_Age Integer
Destination Varchar
2. Add the following five records to the table.
Passenger_ID Passenger_FName Passenger_LName Passenger_Age Destination
1 Ken Kaneki 21 Tokyo
2 Hideyoshi Nagachika 26 Tokyo
3 Frank Robertson 46 Madrid
4 Indrajit Roy 37 Delhi
5 Abul Kashem 57 Lahore
3. Display the unique Destination values.
4. Update the Destination of Frank to Delhi.
5. Delete the records from the table if the Destination is Tokyo.
6. Display the Passenger_FName values if the Passenger_Age value is greater than 30.
7. Add a new column Source of Varchar type to the table.
8. For all the existing entries of the table, set the value of Source as Paris.
9. Display all the entries completely.
10. Introduce Primary Key in combination of Passenger_ID and Passenger_LName.
11. Introduce a Check so that the Passenger_Age must be greater than 18.
12. Set the Default value of Destination as Kyoto.
13. Display the records in descending order in regards to the Passenger_Age values.
14. Display the records whose Passenger_Age values are within the range of 25 to 40.
15. Display Passenger_FName values of records whose Passenger_LName values have o in
them.
16. Drop the Check on Passenger_Age attribute.
17. Drop the Default constraint from the Destination attribute.
18. Delete the Passenger_Age column from the table.
19. Delete all the records from the Passenger table.
20. Delete the Passenger table.
(This assignment will be evaluated out of 20 marks. There are 20 tasks & each will hold 1 mark.)
Practice Problem 2
CSE 3104 – Assignment 02
1. For the tables salesman (sId, sName, sCity, sCommission) and customer (cId,
cName, cCity, sId), write a query to find the names of the Customers and
Salespersons who reside in the same city.
2. For the tables salesman (sId, sName, sCity, sCommission) and customer (cId,
cName, cCity, sId), write a query to find the names of the Customers a Salesman
represents.
3. For the tables salesman (sId, sName, sCity, sCommission) and customer (cId,
cName, cCity, sId), write a query to find the names of the Salespersons who do not
live in the same city as the Customers they represent.
4. For the tables salesman (sId, sName, sCity, sCommission) and customer (cId,
cName, cCity, sId), write a query to display the names of all customers along with
their representing salespersons.
5. For the tables salesman (sId, sName, sCity, sCommission) and customer (cId,
cName, cCity, sId), write a query to display the names of all salespersons who
represents one or more customers or still have not started representing any.
6. For the tables company (cId, cName) and products (pId, pName, price, cId),
write a query to display the products’ names, prices and producing company names.
7. For the tables company (cId, cName) and products (pId, pName, price, cId),
write a query to display names of companies along with their products’ average price.
8. For the tables department (dId, dName, dBudget) and employee (eId, fName,
lName, dId), write a query to display names of the employees along with their
department name.
9. For the tables department (dId, dName, dBudget) and employee (eId, fName,
lName, dId), write a query to display names of the employees whose departments’
budgets are greater than fifty thousand.
10. For the tables department (dId, dName, dBudget) and employee (eId, fName,
lName, dId), write a query to display names of the departments that has more than
two employees.
N.B.:
• Primary keys are underlined and each foreign key has a dashed underline.
• Queries of more than one question will not be the same.
• This assignment will be evaluated out of 10 marks. There are 10 questions & each will
hold 1 mark.
Practice Problem 3
CSE 3104 – Assignment 03
Assume that the following tables exist containing the displayed data.
sample table: Salesman
salesman_id name city commission
5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
5003 Lauson Hen San Jose 0.12
sample table: Customer
customer_id customer_name city grade salesman_id
3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
3005 Graham Zusi California 200 5002
3008 Julian Green London 300 5002
3004 Fabian Johnson Paris 300 5006
3009 Geoff Cameron Berlin 100 5003
3003 Jozy Altidor Moscow 200 5007
3001 Brad Guzan London 5005
sample table: Order
order_id purchase_amount customer_id salesman_id
7001 150.5 3005 5002
7009 270.5 3001 5005
7002 65.5 3002 5001
7004 110 3009 5003
7007 948.5 3005 5002
7005 2400 3007 5001
7008 5760 3002 5001
7010 1983 3004 5006
7003 2480 3009 5003
7012 250.5 3008 5002
7011 76 3003 5007
7013 3045 3002 5001
1. Write a SQL query to find all the orders along with their details issued by the salesman
Paul Adam.
2. Write a SQL query to find all orders along with their details generated by Paris-based
salespeople.
Page 1 of 2
3. Write a SQL query to count the number of customers with grades above the average in
New York City.
4. Write a SQL query to find salespeople who had more than one customer.
5. Write a SQL query to find salespeople who deal with a single customer.
6. Write a SQL query to find the salespeople who deal the customers with more than one
order.
7. Write a SQL query to find those orders where every order amount is less than the
maximum order amount of a customer who lives in London City.
8. Write a SQL query to find those customers whose grades are not the same as those who
live in London City.
9. Write a SQL query to find those customers whose grades are different from those living
in Paris.
10. Write a SQL query to find the number of customers who have different grades than any
customer who living in California.
N.B.:
➢ Write the queries using Sub Queries/ Nested Sub Queries.
➢ This assignment will be evaluated out of 10.
Page 2 of 2