COMSATS University Islamabad, Wah Campus

Department of Computer Science

CSC371 Database Systems 1

Name: Muhammad Hassan Nazar

Reg # FA20-BCS-019

Section: A1

Lab Assignment 01
Guidelines:
• This is individual assignment.
• Late submissions will not be entertained.
• Submit through Google Classroom.

SQL Reference:
[Link]

Lab Assignment Objectives:

In this lab assignment, you will learn how to apply the SQL SELECT command to perform the
following tasks:
1. Single and/or Multi Column Data Retrieval from a single table.
2. Single Level and Multi Level Data Sorting.
3. Performing arithmetic calculations.
4. Combining data of multiple columns.
5. Handling of Null values.
6. Record Selection using:
a. Numeric Search
b. Range Search
c. Text / String Search
d. Date Search
e. Null Search
f. Set Search
7. Creating Data Groups and performing data aggregation using group functions

Evaluation Criteria:
1. Problem solving approach 3 Marks
2. Query Syntax 1 Marks
3. Query Results 1 Mark

Total Questions: 07
Each Question Marks: 05

Page 1 of 5
Total Marks: 35
Tasks To Do:
--Use data in Customer table to perform given tasks.
--1 Find number of customers in each country. Use proper column headings. Sort the
results in alphabetical order (A → Z) of country name.
Total
Country
Customers

AMERICA 184

CHINA 4

GERMANY 3

INDIA 53

ITALY 43

JAPAN 1

SWITZERLAND 29

THAILAND 2

Answer
SELECT nls_territory as Country, Count(customer_id) as Total_Customers
FROM OEHR_CUSTOMERS
group by nls_territory
order by nls_territory asc

--2 Create a report that shows country wise Customers’ Strength as shown in the figure
below. Sort the result in such a way that a country with the highest number of
customers appear at the top.
Country wise Customers
Strength

184 customers from AMERICA

53 customers from INDIA

43 customers from ITALY

29 customers from
SWITZERLAND
4 customers from CHINA

3 customers from GERMANY

2 customers from THAILAND

1 customers from JAPAN

Answer

Page 2 of 5
 select count(customer_id) || ' customer from ' || nls_territory as
Country_Wise_Customers_Strength
from OEHR_CUSTOMERS
group by nls_territory
order by count(customer_id) desc
--3. Find Highest Credit Limit of customers in each state of each country.

Highest Credit
Country State
Limit

AMERICA IA 3500

AMERICA IN 3700

AMERICA MD 5000

AMERICA MI 3700

AMERICA MN 3600

AMERICA NY 5000

AMERICA ONT 1200

AMERICA PA 5000

AMERICA WI 5000

CHINA - 3600

GERMANY - 2400

INDIA Har 2500

Answer
Select nls_territory as Country, State_province as state, Max(credit_limit) as highest_credit_limit
From OEHR_CUSTOMERS
group by nls_territory, State_province
order by nls_territory asc

--4. Find ID, name and phone number of customers who live in Bangalore and their
name starts with character 'B'.
ID First Name Last Name Phone

+91 80 012
905 Billy Hershey
3687

+91 80 012
906 Billy Dench
3689

+91 80 012
909 Blake Mastroianni
3695

+91 80 012
911 Bo Dickinson
3699

+91 80 012
919 Brooke Michalkow
3715
Page 3 of 5
+91 80 012
920 Bruce Hulce 3717
Answer
select customer_id as ID, cust_first_name ||' '|| cust_last_name as First_name, phone_number as
phone
from OEHR_CUSTOMERS
where state_province = 'Banglore'
and cust_first_name like 'B%'

--5. Find customers who live in America and are using email address from Flicker
domain.

CUST_FIRST_NAM CUST_LAST_NAM
CUSTOMER_ID COUNTRY_ID CUST_EMAIL
E E
[Link]@[Link]
132 Doris Spacek US
M
[Link]@FLICKER.C
158 Frederico Lyon US
OM
270 Meg Derek US [Link]@[Link]

Answer
select customer_id, cust_first_name, cust_last_name, country_id, cust_email
from OEHR_CUSTOMERS
where cust_email like
'%[Link]'

--6. Find id, name and email address of those customers who work in [Link]
company and their credit limit ranges between 1000 - 2000.

CUST_FIRST_NAM CUST_LAST_NAM
CUSTOMER_ID COUNTRY_ID CUST_EMAIL
E E
[Link]@[Link]
132 Doris Spacek US
M
[Link]@FLICKER.C
158 Frederico Lyon US
OM
270 Meg Derek US [Link]@[Link]

Answer
select customer_id, cust_first_name
||' '|| cust_last_name as Name,
country_id, cust_email as
Email_address
from OEHR_CUSTOMERS
where cust_email like
'%[Link]' and
credit_limit between 1000
and 2000

--Use data in Orders table to perform given task

==================================================

Page 4 of 5
--7. There are two modes of placing an order: direct mode or online mode. Find number
of orders placed through each ordering mode. Also find the total amount received
through each ordering mode as shown below.
Total Orders Total Amount
Ordering Mode
Placed Received

DIRECT Order 73 1903629

ONLINE Order 32 1764426

Answer
select order_mode,
count(order_mode) as
Total_Order_Placed,
round(sum(order_total)) as
Total_Amount_Recieved
from OEHR_ORDERS
group by order_mode
order by order_mode asc

Page 5 of 5