East West University

Department of Computer Science and Engineering

CSE 302: LAB 05 (Handout)

Course Instructor: Dr. Mohammad Rezwanul Huq

Introducing Nested Subqueries and Outer Joins in SQL

Lab Objective

Familiarize students with nested subqueries in SQL.

Lab Outcome

After completing this lab successfully, students will be able to:

1. Construct SQL statements to perform queries involving nested subqueries.

Psychomotor Learning Levels

This lab involves activities that encompass the following learning levels in psychomotor domain.
Level Category Meaning Keywords
P1 Imitation Copy action of Relate, Repeat, Choose, Copy,
another; observe and Follow, Show, Identify, Isolate.
replicate.
P2 Manipulation Reproduce activity Copy, response, trace, Show,
from instruction or Start, Perform, Execute,
memory Recreate.

Instructions

• Go to the Google Drive Couse Folder.

• Go to the folder: Database Schema
• Download the file University.sql containing university database with records.
• Execute the sql script in your database account.

Lab Activities

Discussion on the following operators/keywords:

➔ Subqueries in the WHERE clause

A. IN / NOT IN
▪ Find courses offered in Fall 2009 and in Spring 2010
▪ Find courses offered in Fall 2009 but not in Spring 2010
▪ Find the total number of (distinct) students who have taken course sections taught by the
instructor with ID 10101

B. SOME / ALL
▪ Find names of instructors with salary greater than that of some (at least one) instructor in the
Biology department.
▪ Find the names of all instructors whose salary is greater than the salary of all instructors in
the Biology department.
 C. EXISTS/NOT EXISTS
▪ Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester
▪ Find all courses taught in Fall 2009 semester but not in the Spring 2010 semester
▪ Find all students who have taken all courses offered in the Biology department.

➔ Subqueries in the FROM clause

▪ Find the average instructors’ salaries of those departments where the average salary is greater
than $42,000

➔ Complex Queries using WITH clause

▪ Find all departments with the maximum budget
▪ Find all departments where the total salary is greater than the average of the total salary at all
departments

➔ Subqueries in the SELECT clause (Scalar Subquery)

▪ Find number of instructors for each department

➔ Performing Outer Joins

▪ Left Outer Join
▪ Right Outer Join
▪ Full Outer Join
▪ Find number of instructors for each department. You must include departments with no
instructor.
 East West University
Department of Computer Science and Engineering

CSE 302: LAB 05 (Exercise - Offline)

Course Instructor: Dr. Mohammad Rezwanul Huq

LAB 05 (Practice Queries)

The following Banking database schema is given.

1) Branch (branch_name, branch_city, assets)
2) Customer (customer_name, customer_street, customer_city)
3) Account (account_number, branch_name, balance)
4) Loan (loan_number, branch_name, amount)
5) Depositor (customer_name, account_number)
6) Borrower (customer_name, loan_number)

➢ Write SQL statements for the following queries following the given guidelines.

1. Find all customer related information who have an account in a branch, located in the same
city as they live. (write this query without using subqueries and then using a subquery)
2. Find all customer related information who have a loan in a branch, located in the same city as
they live. (write this query without using subqueries and then using a subquery)
3. For each branch city, find the average balance of all the accounts opened in a branch located
in that branch city. Do not include any branch city in the result where the total balance of all
accounts opened in a branch located in that city is less than 1000. (Write this query with and
without using ‘having’ clause)
4. For each branch city, find the average amount of all the loans opened in a branch located in
that branch city. Do not include any branch city in the result where the average amount of all
loans opened in a branch located in that city is less than 1500. (write this query with and
without using ‘having’ clause)
5. Find the customer name, customer street, customer city of the account which has the highest
balance among all the accounts. (Write this query with and without using all keyword)
6. Find the customer name, customer street, customer city of the loan which has the lowest
amount among all the loans. (write this query with and without using all keyword)
7. Find the distinct branches (name and city) that have opened both accounts and loans. (Write
this query using in and exists keyword)
8. Find the distinct customers (name and city) who do not have loans but have accounts. (write
this query using not in and not exists keyword)
9. Find those branch names which have total account balance greater than the average of total
balance among all the branches. (write this query with and without using with clause)
10. Find those branch names which have total loan amount less than the average of total loan
amount among all the branches. (write this query with and without using with clause)