Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
AIM: Implementation of at least five different SQL nested sub queries in
from clause on each “Banking, University and database Project assigned to
each student”, by running on WAMP/ LAMP /XAMPP /SQL server.
Procedure
1. Search for XAMPP Control Panel in your device. Open XAMPP Control Panel
on your device. The XAMPP control window will open.
2. Click on the “Start” button for Apache server and MySQL server. The models will
now be initialised. After the initialisation of models has taken place, the “Stop” button
will appear.
3. Click on the “Admin” button next to the “Stop” button for MySQL server.
4. After clicking on the “Admin” button, you will be redirected to
http://localhost/phpmyadmin/.
Page No.
CO23338
Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
5. We will now import the required databases to perform nested sub queries in SELECT
clause.
6. Create a database named ‘banking’ and import the banking database into it.
7. Similarly, create another database named ‘university’ and import the university
database into it.
8. After the tables have been imported we can view their structures and tables by
clicking on the Browse button on the topmost toolbar.
9. Running 5 different SQL nested sub queries in select clause on each “Banking,
University and database Project assigned to each student”:
i) In University Database:
1) Calculate the average classroom capacity for buildings where the capacity
is greater than 50:
SELECT AVG(capacity) AS avg_capacity
FROM (
SELECT capacity
FROM classroom
WHERE capacity > 50) AS large_classrooms;
Page No.
CO23338
Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
2) List all course titles from the Physics department with more than 2 credits:
SELECT title
FROM (
SELECT title, dept_name, credits
FROM course
WHERE dept_name = 'Physics')
AS physics_courses
WHERE credits > 2;
3) List of Departments Offering Courses With 4 or More Credits:
SELECT dept_name
FROM (
SELECT dept_name, credits
FROM course
WHERE credits >= 4) AS subquery
GROUP BY dept_name;
4) Highest Credit Course in Each Department:
SELECT dept_name, MAX(credits) AS highest_credits
FROM (
SELECT dept_name, credits
FROM course
) AS subquery
GROUP BY dept_name;
Page No.
CO23338
Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
ii) In Banking Database:
1) Total Balance by Branch:
SELECT branch_name, SUM(total_balance) AS total_balance
FROM (
SELECT branch_name, balance AS total_balance
FROM account
) AS subquery
GROUP BY branch_name;
2) Accounts with Balances Greater Than 500:
SELECT account_number,branch_name
FROM (SELECT account_nymber,branch_name,balance
FROM account
WHERE balance>500
)AS subquery;
Page No.
CO23338
Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
3) Average Balance of Accounts by Branch:
SELECT branch_name,AVG(balance) AS avg_balance
FROM(
SELECT branch_name,balance
FROM account
)AS subquery
GROUP BY branch_name;
4) Branches with More Than 2 Accounts:
SELECT branch_name
FROM(SELECT branch_name,COUNT(account_number) AS account_count
FROM account
GROUP BY branch_name)
AS subquery
WHERE account_count>2;
Page No.
CO23338
Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
5) Highest Balance for Each Branch:
SELECT branch_name,MAX(balance) AS highest_balance
FROM(
SELECT branch_name,balance
FROM account
)AS subquery
GROUP BY branch_name;
Page No.
CO23338
Date of practical: 16 September, 2024 Practical 9 Date of submission: 23 September, 2024
Page No.
CO23338
