SQL Lab Questions & Answer

Partical for SQL Lab

Field name Data type Constraint
Cost_id Number Primary key
Cost_name Varchar Not null
Address Varchar Only ktm is allowed
Salary Number Should be positive
Phone_no Number Should be unique

  1. Write SQL syntax to create above table customer. 
    CREATE TABLE customer (
    Cost_id INT PRIMARY KEY,
    Cost_name VARCHAR(50) NOT NULL,
    Address VARCHAR(50) CHECK (Address = 'ktm'),
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    Phone_no BIGINT UNIQUE
);
    OR 
    CREATE TABLE customer (
    Cost_id NUMBER PRIMARY KEY,
    Cost_name VARCHAR(50) NOT NULL,
    Address VARCHAR(50) CHECK (Address = 'ktm'),
    Salary NUMBER CHECK (Salary > 0),
    Phone_no NUMBER UNIQUE
);
  2. Write SQL syntax to insert new records in the customer table. 
    INSERT INTO customer
(Cost_id, Cost_name, Address, Salary, Phone_no)
VALUES
(101, 'Ram', 'ktm', 25000, 9800000001);
  3. Write SQL syntax to add a new field (e-mail with data type varchar) to the customer table. 
    ALTER TABLE customer
ADD email VARCHAR(100);
  4. Remove phone_no column. 
    ALTER TABLE customer
DROP COLUMN Phone_no;
Empno Ename Salary Deptno
101 Ram 10000 1
102 Shyam 12000 1
103 Jeevan 3800 2
104 Shanta 3700 2
105 Saroj 8000 1 
CREATE TABLE Emp (
    Empno  INT,
    Ename  VARCHAR(50),
    Salary DECIMAL(10,2),
    Deptno INT
);
            
INSERT INTO emp (Empno, Ename, Salary, Deptno)
VALUES
(101, 'Ram', 10000, 1),
(102, 'Shyam', 12000, 1),
(103, 'Jeevan', 3800, 2),
(104, 'Shanta', 3700, 2),
(105, 'Saroj', 8000, 1);
Or, if your SQL system requires one row at a time: 
INSERT INTO emp VALUES (101, 'Ram', 10000, 1);
INSERT INTO emp VALUES (102, 'Shyam', 12000, 1);
INSERT INTO emp VALUES (103, 'Jeevan', 3800, 2);
INSERT INTO emp VALUES (104, 'Shanta', 3700, 2);
INSERT INTO emp VALUES (105, 'Saroj', 8000, 1);

SELECT Ename, Salary
FROM Emp
WHERE Salary BETWEEN 5000 AND 10000;
            
SELECT Empno, Ename
FROM Emp
WHERE Ename LIKE 'S%';
            
SELECT SUM(Salary) AS Total_Salary
FROM Emp
WHERE Deptno = 1;

SELECT *
FROM Emp
ORDER BY Salary DESC;

SELECT `Ename`,`Salary` FROM `empy` WHERE `Ename` LIKE ('%m');
Empno Ename Address Salary Post Age Comm. Deptno
101 Ashika Maidhar 20000 Mgr 20 90 10
102 Ashmita Itahatta 10000 Asst 32 60 10
103 Pratikshya Kakarvitta 9000 Acct 27 50 10
104 Dolma Brt 8000 CEO 35 20 20
105 Jyoti Bhadrapur 15000 Typist 48 10 20
106 Nisha Btm 7000 Receptionist 22 40 30
107 Bivuti Surunga 11000 Peon 40 30 30
108 Nigi Damak 13000 Clerk 49 25 30
109 Dipes h Itahari 17000 Technician 37 45 30 
CREATE TABLE Employee (
Empno int,
Ename varchar(30),
Address varchar(30),
Salary int,
Post varchar(30),
Age int,
Comm int,
Deptno int
);
            
INSERT INTO Employee
(Empno, Ename, Address, Salary, Post, Age, Comm, Deptno)
VALUES
(101, 'Ashika', 'Maidhar', 20000, 'Mgr', 20, 90, 10),
(102, 'Ashmita', 'Itavatta', 10000, 'Asst', 32, 60, 10),
(103, 'Pratikshya', 'Kakarvitta', 9000, 'Acct', 27, 50, 10),
(104, 'Dolma', 'Brt', 8000, 'CEO', 35, 20, 20),
(105, 'Jyoti', 'Bhadrapur', 15000, 'Typist', 48, 10, 20),
(106, 'Nisha', 'Btm', 7000, 'Receptionist', 22, 40, 30),
(107, 'Bivuti', 'Surunga', 11000, 'Peon', 40, 30, 30),
(108, 'Nigi', 'Damak', 13000, 'Clerk', 49, 25, 30),
(109, 'Dipesh', 'Itahari', 17000, 'Technician', 37, 45, 30);

SELECT * FROM `Employee` WHERE `Deptno` IN (10,30);
            
SELECT *
FROM Employee
WHERE Age > 30 AND Age < 50;
Or using Between: 
SELECT *
FROM Employee
WHERE Age BETWEEN 31 AND 49;
ASC sorts the salary in ascending order (lowest to highest). 
SELECT Empno, Ename, Salary, Age
FROM emp
ORDER BY Salary ASC;
Empno Ename Job Salary Commission Deptno
100 Balak Mgr 10000 10 10
102 Smith Sman 4000 NULL 30
103 Jones Mgr 10000 10 20
104 Jackson Sman 4000 NULL 10
105 Robin Peon 3000 NULL 20
106 Allen Clerk 5000 5 30


  1. Write SQL query to Create table 'Emp' ? 
    CREATE TABLE Emp (
    Empno INT,
    Ename VARCHAR(30),
    Job VARCHAR(20),
    Salary INT,
    Commission INT,
    Deptno INT
);
  2. Write SQL query to insert data of above table 'Emp' ? 
    INSERT INTO Emp (Empno, Ename, Job, Salary, Commission, Deptno)
VALUES
(100, 'Balak', 'Mgr', 10000, 10, 10),
(102, 'Smith', 'Sman', 4000, NULL, 30),
(103, 'Jones', 'Mgr', 10000, 10, 20),
(104, 'Jackson', 'Sman', 4000, NULL, 10),
(105, 'Robin', 'Peon', 3000, NULL, 20),
(106, 'Allen', 'Clerk', 5000, 5, 30);
  3. Write SQL query to retrieve empno, ename, job, salary of all employees in descending order of their salary. 
    SELECT Empno, Ename, Job, Salary
FROM Emp
ORDER BY Salary DESC;
  4. Write a SQL query to retrieve all information of employees belonging to department 10 or 20. 
    SELECT *
FROM Emp
WHERE Deptno IN (10, 20);
  5. Write a query to display all employees who don't have any commission. 
    SELECT *
FROM Emp
WHERE Commission IS NULL;
  6. Write a query to display deptno and total salary of each department. 
    SELECT Deptno, SUM(Salary) AS Total_Salary
FROM Emp
GROUP BY Deptno;
  7. Write a query to display deptno and name of employees whose name starts with 'J'. 
    SELECT Deptno, Ename
FROM Emp
WHERE Ename LIKE 'J%';

Table : Customers

ID Name
1 Raj Mehta
2 Sanjay Mishra
3 Aditi Gupta

Table_Name : Shopping_Details

ID Item_Name
2 Chips
3 Chocolate

Table : Result         3 × 2 = 6 rows

Customers
CustID 		Name Shopping_d
etails.CustID 		Item_Name
1 Raj Mehta 2 Chips
1 Raj Mehta 3 Chocolate
2 Sanjay Mishra 2 Chips
2 Sanjay Mishra 3 Chocolate
3 Aditi Gupta 2 Chips
3 Aditi Gupta 3 Chocolate

1. Create Customers Table

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

2. Insert Data into Customers Table

INSERT INTO Customers (ID, Name)
VALUES
(1, 'Raj Mehta'),
(2, 'Sanjay Mishra'),
(3, 'Aditi Gupta');

3. Create Shopping_Details Table

CREATE TABLE Shopping_Details (
    ID INT,
    Item_Name VARCHAR(50)
);

4. Insert Data into Shopping_Details Table

INSERT INTO Shopping_Details (ID, Item_Name)
VALUES
(2, 'Chips'),
(3, 'Chocolate');

5. Display Result Using CROSS JOIN

SELECT *
FROM Customers CROSS JOIN Shopping_Details;
OR 
SELECT 
    Customers.ID AS CustomerID,
    Customers.Name,
    Shopping_Details.ID AS Shopping_CustomerID,
    Shopping_Details.Item_Name
FROM Customers
CROSS JOIN Shopping_Details;

SELF JOIN: A Table Joins Itself

A SELF JOIN is a table joined to itself using different aliases. It is not a separate keyword. You use INNER JOIN or LEFT JOIN on the same table.

This is essential for hierarchical data: employee/manager relationships, category/subcategory trees, or referral chains.

Detective Case:
Suppose each suspect has an associate_id pointing to another suspect in the same table. We want to find who knows who.

Updated suspects table

ID Name Role Associate_ID
1 Marcus Webb Manager 2
2 Diana Cross Accountant NULL
3 Victor Stone Security 1
4 Elena Morris Intern 3

Example

SELECT
    s1.name AS suspect,
    s2.name AS known_associate
FROM suspects s1
LEFT JOIN suspects s2
    ON s1.associate_id = s2.id;

Result

suspect known_associate
Marcus Webb Diana Cross
Diana Cross NULL
Victor Stone Marcus Webb
Elena Morris Victor Stone
Key Point:
The aliases s1 and s2 let the database treat the same table as two separate tables. Marcus knows Diana. Victor knows Marcus. Diana has no known associate (NULL).

💡 Common SELF JOIN Patterns

  • Employee/Manager: Find each employee and their manager name from the same employees table.
  • Referral chains: Which user referred which other user.
  • Hierarchy traversal: Categories and subcategories in one table.
suspects interviews No interview No suspect Matched rows

INNER JOIN returns only the overlapping center

INNER JOIN Venn diagram

suspects

id name role
1 Marcus Webb Manager
2 Diana Cross Accountant
3 Victor Stone Security
4 Elena Morris Intern

interviews

id suspect_id statement date
1 1 "I was home" 2026-01-15
2 2 "At the bar" 2026-01-16
3 5 "No comment" 2026-01-17
Primary table Foreign key Matching row No match

INNER JOIN Result

SELECT s.name, i.statement, i.date FROM suspects s INNER JOIN interviews i ON s.id = i.suspect_id;


name statement date
Marcus Webb "I was home" 2026-01-15
Diana Cross "At the bar" 2026-01-16
LEFT JOIN returns all rows from the left table, plus matching rows from the right.

Our Example Data

suspects

Left table

id name role
1 Marcus Webb Manager
2 Diana Cross Accountant
3 Victor Stone Security
4 Elena Morris Intern
🔵 3 & 4: No matching interview
interviews

Right table

id suspect_id statement date
1 1 "I was home" 2026-01-15
2 2 "At the bar" 2026-01-16
3 5 "No comment" 2026-01-17
🔴 5: No matching suspect
🟢 Matching records 🔴 No match 🔵 Left table 🟢 Right table

Notice: Suspects 3 and 4 have no interviews. Interview 3 references suspect_id = 5 , who does not exist in the suspects table.

LEFT JOIN

Returns every row from the left table plus matching rows from the right table

suspects interviews NULL filled Excluded rows Matched

LEFT JOIN = entire left circle + overlapping center

Example

Keep every suspect, even when no interview exists.

SQL Query 
SELECT
    s.name,
    s.role,
    i.statement
FROM suspects s
LEFT JOIN interviews i
    ON s.id = i.suspect_id;
Result
name role statement
Marcus Webb Manager "I was home"
Diana Cross Accountant "At the bar"
Victor Stone Security NULL
Elena Morris Intern NULL
4 suspects

Every suspect remains in the result.

2 matches

Marcus and Diana have interviews.

2 NULLs

Victor and Elena have no interview.

Key takeaway

All 4 suspects appear in the result. Victor and Elena receive NULL for the interview columns because they were never interviewed.

LEFT JOIN = LEFT OUTER JOIN

The OUTER keyword is optional.

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table, plus matching rows from the left. No match? NULLs fill the left side.
suspects
| id | name          | role       |
|----|---------------|------------|
| 1  | Marcus Webb   | Manager    |
| 2  | Diana Cross   | Accountant |
| 3  | Victor Stone  | Security   |
| 4  | Elena Morris  | Intern     |
interviews
| id | suspect_id | statement      | date       |
|----|------------|----------------|------------|
| 1  | 1          | "I was home"   | 2026-01-15 |
| 2  | 2          | "At the bar"   | 2026-01-16 |
| 3  | 5          | "No comment"   | 2026-01-17 |

Example: RIGHT JOIN

Query 
SELECT s.name, i.statement, i.date
FROM suspects s
RIGHT JOIN interviews i ON s.id = i.suspect_id;
Result
name statement date
Marcus Webb "I was home" 2026-01-15
Diana Cross "At the bar" 2026-01-16
NULL "No comment" 2026-01-17

All 3 interviews appear. The third interview has NULL for name because suspect_id = 5 does not exist in the suspects table.

💡 Convert to LEFT JOIN

These two queries produce identical results.

RIGHT JOIN Original version 
SELECT s.name, i.statement
FROM suspects s
RIGHT JOIN interviews i
    ON s.id = i.suspect_id;
↓ Equivalent
LEFT JOIN Preferred version 
SELECT s.name, i.statement
FROM interviews i
LEFT JOIN suspects s
    ON s.id = i.suspect_id;
📌

Practical takeaway

Most teams standardize on LEFT JOIN for consistency. Know that RIGHT JOIN exists, but prefer LEFT JOIN in practice.

FULL OUTER JOIN returns all rows from both tables. Where there is a match, you get combined data. Where there is not, NULLs fill the missing side.
suspects

Left table

id name role
1 Marcus Webb Manager
2 Diana Cross Accountant
3 Victor Stone Security
4 Elena Morris Intern
interviews

Right table

id suspect_id statement date
1 1 "I was home" 2026-01-15
2 2 "At the bar" 2026-01-16
3 5 "No comment" 2026-01-17

FULL OUTER JOIN

Returns every row from both tables — matched or unmatched.

Example 
SELECT s.name, s.role, i.statement
FROM suspects s
FULL OUTER JOIN interviews i
    ON s.id = i.suspect_id;
Result
name role statement
Marcus Webb Manager "I was home"
Diana Cross Accountant "At the bar"
Victor Stone Security NULL
Elena Morris Intern NULL
NULL NULL "No comment"
5 rows. All suspects (including those without interviews) AND all interviews (including the orphaned one referencing suspect_id 5).
2 matches + 2 unmatched suspects + 1 orphaned interview = 5 rows
3-Table JOIN

Chaining 3 tables

Join suspects → interviews → locations one step at a time.

SQL Query 
SELECT
  s.name,
  i.statement,
  l.location_name
FROM suspects s
INNER JOIN interviews i ON s.id = i.suspect_id
LEFT JOIN locations l ON i.location_id = l.id;
Result
name statement location_name
Marcus Webb "I was home" Blue Note Lounge
Diana Cross "At the bar" NULL
1
INNER JOIN

Filters the data to suspects who have a matching interview.

2
LEFT JOIN

Adds location data when available. Diana's interview has no location_id , so location_name = NULL.

💡 JOIN pipeline
suspects → INNER JOIN interviews → LEFT JOIN locations → final result

SQL CREATE VIEW

Create a reusable virtual table from a SQL query.

1. Original Table — employees

id name department salary
1 Marcus Webb Manager $75,000
2 Diana Cross Accounting $68,000
3 Victor Stone Security $55,000
4 Elena Morris Intern $32,000

2. Create a View 

CREATE VIEW manager_view AS
SELECT
    id,
    name,
    department,
    salary
FROM employees
WHERE department = 'Manager';

The query is saved as a reusable virtual table called manager_view.

3. Query the View 

SELECT *
FROM manager_view;
id name department salary
1 Marcus Webb Manager $75,000

💡 Note

A VIEW is like a saved SQL query that you can use like a table. It usually does not store a separate copy of the data; instead, the database runs the underlying query when you access the view.

employees → CREATE VIEW → manager_view

Subqueries in SQL

A subquery is a query nested inside another SQL query. Different types of subqueries return different kinds of results.

1️⃣

Single Row

Returns one row or value.

2️⃣

Multiple Row

Returns multiple rows.

3️⃣

Multiple Column

Returns multiple columns.

4️⃣

Correlated

Depends on the outer query.

🏛️ Galleries

Art gallery locations

id city
1 Jaipur
2 Kolkata
3 Madhubani

🎨 Paintings

Available artworks

id name gallery_id price
1 Patterns 3 5000
2 Ringer 1 4500
3 Gift 1 3200
4 Violin Lessons 2 6700
5 Curiosity 2 9800

💼 Sales Agents

Gallery sales representatives

id last_name first_name gallery_id agency_fee
1 Brown Denis 2 2250
2 White Kate 3 3120
3 Black Sarah 2 1640
4 Smith Helen 1 4500
5 Stewart Tom 3 2130

👔 Managers

Gallery management assignments

id gallery_id
1 2
2 3
4 1
1

Single Row Subquery

Returns a single value or row to the parent query.

SQL 
SELECT
    name AS painting,
    price,
    (
        SELECT AVG(price)
        FROM paintings
    ) AS avg_price
FROM paintings;
Result
painting price avg_price
Patterns 5000 5840
Ringer 4500 5840
Gift 3200 5840
Violin Lessons 6700 5840
Curiosity 9800 5840
How it works: The inner query calculates the average price, 5840. Because the subquery is independent of the outer query, it can run on its own.

Single-row subquery with WHERE 

SELECT *
FROM sales_agents
WHERE agency_fee >
(
    SELECT AVG(agency_fee)
    FROM sales_agents
);
id last_name first_name agency_fee
2 White Kate 3120
4 Smith Helen 4500

The subquery calculates the average agency fee as 2728. The outer query keeps agents whose fee is greater than that average.

2

Multiple Row Subquery

Returns multiple rows to the parent query.

SQL 
SELECT AVG(agency_fee)
FROM sales_agents
WHERE id NOT IN
(
    SELECT id
    FROM managers
);

Inner query

Returns the IDs of all managers.

1  2  4

Outer query

Excludes those IDs and calculates the average fee of the remaining agents.

1885
3

Multiple Column Subquery

Returns multiple columns to the parent query.

SQL 
SELECT id, name, price
FROM paintings
WHERE (name, price) IN
(
    SELECT name, MIN(price)
    FROM paintings
);
Result
id name gallery_id price
3 Gift 1 3200
How it works: The inner query returns multiple columns. The outer query compares (name, price) pairs and returns the matching painting.
4

Correlated Subquery

The inner query depends on the current row of the outer query.

SQL 
SELECT
    city,
    (
        SELECT COUNT(*)
        FROM paintings p
        WHERE g.id = p.gallery_id
    ) AS total_paintings
FROM galleries g;
Result
city total_paintings
Jaipur 2
Kolkata 2
Madhubani 1
Key idea: The inner query references g.id from the outer query. Therefore, the subquery's result changes for each gallery.

🔄 Equivalent JOIN 

SELECT
    g.city,
    COUNT(p.name) AS total_paintings
FROM galleries g
JOIN paintings p
    ON g.id = p.gallery_id
GROUP BY g.city;

The same result can be achieved using a JOIN. In general, JOINs often perform better, while subqueries can sometimes be more intuitive depending on the problem.

Correlated Subquery with WHERE 

SELECT
    last_name,
    first_name,
    agency_fee
FROM sales_agents sa1
WHERE sa1.agency_fee >=
(
    SELECT AVG(agency_fee)
    FROM sales_agents sa2
    WHERE sa2.gallery_id = sa1.gallery_id
);
last_name first_name agency_fee
Brown Denis 2250
White Kate 3120
Smith Helen 4500

Subquery Cheat Sheet

Type Returns Common usage
Single Row One value / row WHERE, SELECT, HAVING
Multiple Row Multiple rows IN, NOT IN, ANY, ALL
Multiple Column Multiple columns Row/column comparisons
Correlated Depends on outer row WHERE, SELECT, FROM

SQL Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single summary value.

📊

Example: employees

A simple table we can use with aggregate functions

id name department salary
1 Alice IT $50,000
2 Bob HR $40,000
3 Charlie IT $60,000
4 Diana Sales $45,000
5 Ethan IT $55,000

The 5 Common Aggregate Functions

Each function answers a different question about the data.

COUNT()

Count rows

SELECT COUNT(*) AS total
FROM employees;

Result

5

There are 5 employees.

SUM()

Add values

SELECT SUM(salary) AS total_salary
FROM employees;

Result

$250,000

Total salary of all employees.

AVG()

Calculate average

SELECT AVG(salary) AS avg_salary
FROM employees;

Result

$50,000

Average employee salary.

MAX()

Find the largest value

SELECT MAX(salary) AS highest
FROM employees;

Result

$60,000

Charlie has the highest salary.

MIN()

Find the smallest value

SELECT MIN(salary) AS lowest
FROM employees;

Result

$40,000

Bob has the lowest salary.

📦

Aggregate Functions with GROUP BY

Calculate summaries separately for each department.

SQL 
SELECT
    department,
    COUNT(*) AS employees,
    AVG(salary) AS average_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Result

department employees average_salary total_salary
IT 3 $55,000 $165,000
HR 1 $40,000 $40,000
Sales 1 $45,000 $45,000

🎯 Aggregate Functions Cheat Sheet

Function Purpose Example
COUNT() Counts rows COUNT(*)
SUM() Adds numeric values SUM(salary)
AVG() Calculates average AVG(salary)
MAX() Finds largest value MAX(salary)
MIN() Finds smallest value MIN(salary)
Remember: Aggregate functions summarize multiple rows into a value. Use GROUP BY when you want a separate summary for each group.
Report 1
Report 2
Report ER Diagram 3
Report ER Diagram 4