DBMS Lab - Subqueries & Constraints

Online Compiler if required

https://www.programiz.com/sql/online-compiler

                
                    drop table Customers; 

                    drop table Orders;

                    drop table Shippings;

to drop existing table

1. Database Schema Setup

Execute the following commands to create the customer and orders tables. Note the use of Primary Key and Foreign Key.

                
                        CREATE TABLE customer(

                          cid INT PRIMARY KEY,

                          cname VARCHAR(15),

                          city VARCHAR(15),

                          grade INT

                        );

                        CREATE TABLE orders(

                          oid INT PRIMARY KEY,

                          amount DECIMAL(10,2),

                          cid INT,

                          FOREIGN KEY (cid) REFERENCES customer(cid)

                        );

2. Sample Data Insertion

Insert these records to perform the analysis tasks.

                
                        INSERT INTO customer VALUES

                        (10, 'Ravi','Bangalore', 100),

                        (12, 'shwetha','Mangalore', 300),

                        (13, 'shallini','Channai', 500),

                        (14, 'sushmitha','Bangalore', 700),

                        (16, 'ramya','Bangalore', 500);

3. Orders Data Insertion

Insert these records into the orders table to link transactions to customers.

                
                    INSERT INTO orders VALUES

                    (5001, 150.00, 10),

                    (5002, 350.50, 12),

                    (5003, 1000.00, 14),

                    (5004, 250.00, 10),

                    (5005, 550.00, 16);

Note: Notice that Customer 13 (Shallini) has no orders in this set.

Task 1: Renaming with Aliases

Write a query to display all customers, but rename the column cname to Customer_Name and grade to Performance_Score.

here we are not alering the table, just displaying


            SELECT cname AS Customer_Name, grade AS Performance_Score

            FROM customer;

Task 2: Advanced Subquery Analysis

Find the count of all customers whose grade is strictly above the average grade of customers specifically from the city of 'Bangalore'.


            SELECT COUNT(*) AS count 

            FROM customer 

            WHERE grade > (SELECT AVG(grade) FROM customer WHERE city = 'Bangalore');

Calculation Insight:
Bangalore Average = (100 + 700 + 500) / 3 = 433.33.
Resulting Count: 3 (Shwetha, Shallini, Sushmitha, Ramya - excluding Ravi).

⚠️ Key Takeaways

Subquery Logic: The database executes the inner query (parentheses) first.
Referential Integrity: You cannot insert an order for a cid that doesn't exist in the customer table.
Aggregate Error: Remember that AVG() cannot be used directly in a WHERE clause; it requires a subquery.

Section: Aggregation and Grouping

Question 1: City-Wise Performance

Find the Average Grade for each city. Rename the result column as Avg_Grade.

Goal: Practice GROUP BY and AVG.

City	Avg_Grade
Bangalore	433.3333333333333
Channai	500
Mangalore	300

Question 2: Filtering Groups

List all cities where the Maximum Grade is greater than 400. Rename the output column as High_Grade_City.

Goal: Learn to use HAVING instead of WHERE for aggregated data.

High_Grade_City
Bangalore
Channai

Question 3: Revenue per Client

From the orders table, find the total amount spent by each cid. Sort the results so the highest spender appears first.

Goal: Practice SUM and ORDER BY DESC.

cid	total_spent
14	1000
16	550
10	400
12	350.5

Section: Subqueries and Logic

Question 4: The "Elite" Filter

Display the names of customers who have a grade higher than the overall average grade of all customers in the database.

Goal: Use a scalar subquery in the WHERE clause.

cname
shallini
sushmitha
ramya

Question 5: Inter-City Comparison

Find all customers living in 'Bangalore' who have a grade higher than the average grade of customers in 'Mangalore'.

Goal: Compare specific subsets of data using a subquery.

cid	cname	city	grade
14	sushmitha	Bangalore	700
16	ramya	Bangalore	500

Question 6: High-Value Orders

Select all oid (Order IDs) where the purchase amount is greater than the average purchase amount of all orders ever placed.

Goal: Apply subqueries to numeric calculations in a different table.

oid
5003
5005

Section: Relational Thinking (Table Connections)

Question 7: Zero-Order Identification

Find the names of customers who have not placed any orders. (Use the NOT IN operator with a subquery).

Goal: Understand logical exclusion between related tables.

cname
shallini

Question 8: The Top Spender

Identify the cname of the customer who placed the single largest order (maximum amount).

Goal: Linking customer name to orders data via cid.

cname
sushmitha

Question 9: Frequent Shoppers

Find the cid of customers who have placed more than one order. Rename the count as Order_Count.

Goal: Using COUNT with GROUP BY and HAVING > 1.

cid	Order_Count
10	2

Section: The "Professor's Challenge"

Question 10: Performance Gap Analysis

List the cities where the difference between the Highest Grade and the Lowest Grade is more than 200 points.

Goal: Perform mathematical operations within the HAVING clause.

city
Bangalore

Section: String & Pattern Matching

Question 11: Name Pattern Search

Find all customers whose cname starts with the letter 's'. Display their full name and city.

Goal: Practice the LIKE operator with a wildcard.

cname	city
shwetha	Mangalore
shallini	Channai
sushmitha	Bangalore

Question 12: Character Position Filter

Find all customers whose name has exactly 5 characters.

Goal: Use LIKE with the fixed-length wildcard _ (underscore).

cname
ramya

Section: Set Operations & IN Clause

Question 13: Multi-City Filter

Display all customers who live in either 'Bangalore' or 'Mangalore'. Use the IN operator.

Goal: Use IN as a cleaner alternative to multiple OR conditions.

cid	cname	city	grade
10	Ravi	Bangalore	100
12	shwetha	Mangalore	300
14	sushmitha	Bangalore	700
16	ramya	Bangalore	500

Question 14: Grade Range Filter

List all customers with a grade between 300 and 600 (inclusive). Use the BETWEEN operator.

Goal: Practice BETWEEN as a shorthand for range conditions.

cname	grade
shwetha	300
shallini	500
ramya	500

Section: UPDATE & DELETE Operations

Question 15: Grade Upgrade

Update the grade of all customers in 'Mangalore' by adding 50 to their current grade. Then display the updated table.

Goal: Practice UPDATE ... SET with arithmetic and a WHERE filter.

cid	cname	city	grade
10	Ravi	Bangalore	100
12	shwetha	Mangalore	350
13	shallini	Channai	500
14	sushmitha	Bangalore	700
16	ramya	Bangalore	500

Question 16: Remove Low-Value Orders

Delete all orders where the amount is less than 200. Then verify by selecting all orders.

Goal: Practice DELETE FROM with a WHERE condition.

oid	amount	cid
5002	350.5	12
5003	1000	14
5004	250	10
5005	550	16

Section: JOIN Operations

Question 17: Customer Order Report

Display each customer's name alongside their order ID and amount using an INNER JOIN.

Goal: Learn how INNER JOIN connects two tables on a common key.

cname	oid	amount
shwetha	5002	350.5
sushmitha	5003	1000
Ravi	5004	250
ramya	5005	550