RDBMS LAB EXERCISES WITH ANSWER

EXERCISE:1

Create a table called Customer table

Name Type

Cust_name Varchar2(20)

Cust_street Varchar2(20)

Cust_city Varchar2(20)

(i) Insert records into the table.

(ii) Add salary column to the table.

(iii) Alter the table column domain.

(iv) Drop salary column of the customer table.

(v) Delete the rows of customer table whose cust_city is

“New York”.

💠 Step-by-step SQL Queries:

✅(1) Create the Customer table

SQL>CREATE TABLE Customer (

Cust_name VARCHAR2(20),

Cust_street VARCHAR2(20),

Cust_city VARCHAR2(20)

);

✅ (2) Insert records into the table

SQL>INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Alice', '1st Avenue', 'New York');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Bob', '2nd Street', 'Los Angeles');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Charlie', '3rd Blvd', 'New York');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('David', '4th Lane', 'Chicago');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Eve', '5th Road', 'Houston');

✅ (3) Add salary column to the table

SQL>ALTER TABLE Customer

ADD salary NUMBER(10, 2);

✅ (4) Alter the table column domain

(Example: Change Cust_name from 20 to 30 characters)

SQL>ALTER TABLE Customer

MODIFY Cust_name VARCHAR2(30);

✅ (5) Drop the salary column from the table

SQL>ALTER TABLE Customer

DROP COLUMN salary;

✅ (6) Delete rows where cust_city is 'New York'

SQL>DELETE FROM Customer

WHERE Cust_city = 'New York';

Let me know if you'd like this in a downloadable .sql file or as a PDF.

EXERCISE:2

Create a table called Stock table

Name Type

Sid Number

Sname Varchar2(20)

Rating Varchar2(20)

(i) Add column age to the Stock table.

(ii) Insert values into the Stock table.

(iii) Delete the row with Rating >8.

(iv) Update the column details of Stock.

(v) Insert null values into the table.

✅ (1) Create the Stock table

SQL>CREATE TABLE Stock (

Sid NUMBER,

Sname VARCHAR2(20),

Rating VARCHAR2(20)

);

✅ (2) Add column age to the Stock table

SQL>ALTER TABLE Stock

ADD age NUMBER;

✅ (3) Insert values into the Stock table

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (101, 'Reliance', '9', 30);

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (102, 'TCS', '7', 25);

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (103, 'Infosys', '8', 28);

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (104, 'Wipro', '6', 26);

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (105, 'HCL', '10', 35);

✅ (4) Delete rows with Rating > 8

SQL>DELETE FROM Stock

WHERE TO_NUMBER(Rating) > 8;

✅ (5) Update column details of Stock

SQL>UPDATE Stock

SET Rating = '8', age = 27

WHERE Sid = 102;

You can update other columns similarly.

✅ (6) Insert NULL values into the table

SQL>INSERT INTO Stock (Sid, Sname, Rating, age)

VALUES (106, NULL, NULL, NULL);

EXERCISE:3

Consider the following tables namely “DEPARTMENTS”

and “EMPLOYEES” Their schemas are as follows.

Departments (dept _no, dept_ name, dept_location);

Employees (emp_id, emp_name, emp_salary, dept_no);

(i) Develop a query to use all DDL commands and

constraints.

(ii) Develop a query to use all DML commands and

constraints.

(iii) Generate a report of employees who joins after

1-jan-2020.

(i) DDL Queries with Constraints

SQL>CREATE TABLE Departments (

dept_no INT PRIMARY KEY,

dept_name VARCHAR(50) NOT NULL,

dept_location VARCHAR(50)

);

SQL>CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(100) NOT NULL,

emp_salary DECIMAL(10, 2) CHECK (emp_salary > 0),

dept_no INT,

emp_join_date DATE NOT NULL,

FOREIGN KEY (dept_no) REFERENCES Departments(dept_no)

);

🟨 (ii) DML Queries with Constraints

✅ Insert Records:

-- Insert into Departments

SQL>INSERT INTO Departments VALUES

(10, 'HR', 'Chennai'),

(20, 'Finance', 'Mumbai'),

(30, 'IT', 'Bangalore');

SQL>SELECT * FROM Departments;

dept_no	dept_name	dept_location
10	HR	Chennai
20	Finance	Mumbai
30	IT	Bangalore

-- Insert into Employees

SQL>INSERT INTO Employees VALUES

(101, 'Alice', 55000, 10, '2019-12-15'),

(102, 'Bob', 60000, 30, '2021-02-20'),

(103, 'Carol', 65000, 30, '2023-05-10'),

(104, 'David', 50000, 20, '2018-08-25');

SQL>SELECT * FROM Employees;

✅ Example Output:

emp_id	emp_name	emp_salary	dept_no	emp_join_date
101	Alice	55000.00	10	2019-12-15
102	Bob	65000.00	30	2021-02-20
103	Carol	70000.00	30	2023-05-10

✅ Update Record:

SQL>UPDATE Employees

SET emp_salary = emp_salary + 5000

WHERE dept_no = 30;

✅ Delete Record:

SQL>DELETE FROM Employees

WHERE emp_id = 104;

🟦 (iii) Report – SELECT Query

✅ Display employees who joined after 1-Jan-2020:

SQL>SELECT emp_id, emp_name, emp_salary, emp_join_date

FROM Employees

WHERE emp_join_date > '2020-01-01';

📄 Example Output:

emp_id	emp_name	emp_salary	emp_join_date
102	Bob	65000.00	2021-02-20
103	Carol	70000.00	2023-05-10

✅ Optional – Display All Employee Records:

SQL>SELECT * FROM Employees;

✅ Optional – Display Employees with Department Info (Join):

SQL>SELECT E.emp_id, E.emp_name, E.emp_salary, E.emp_join_date, D.dept_name, D.dept_location

FROM Employees E

JOIN Departments D ON E.dept_no = D.dept_no;

EXERCISE4:

Create a table called Employee with the following

structure.

Empno (Number), Ename (Varchar2(20)), Job

(Varchar2(20) ), Sal (Number)

(i) Add a column DoJ (DATE) with the Employee table.

(ii) Insert any five records into the table.

(iii) Update the column details of job

(iv) Rename the column Sal as Emp_Salary of Employ

table using alter command.

(v) Delete the employee whose empno is 101.

🟩 Step-by-Step Tasks Covered:

Task No.	Description
(i)	Create Employee table and add DoJ column
(ii)	Insert 5 records
(iii)	Update job titles
(iv)	Rename column Sal to Emp_Salary
(v)	Delete employee with empno = 101

✅ (1) Create the Employee Table

SQL>CREATE TABLE Employee (

Empno NUMBER PRIMARY KEY,

Ename VARCHAR2(20),

Job VARCHAR2(20),

Sal NUMBER

);

✅ (i) Add a Column DoJ (Date of Joining)

SQL>ALTER TABLE Employee

ADD DoJ DATE;

✅ (ii) Insert 5 Records into the Table

SQL>INSERT INTO Employee VALUES (101, 'Alice', 'Manager', 60000, TO_DATE('2020-01-15', 'YYYY-MM-DD'));

SQL>INSERT INTO Employee VALUES (102, 'Bob', 'Clerk', 25000, TO_DATE('2019-11-10', 'YYYY-MM-DD'));

SQL>INSERT INTO Employee VALUES (103, 'Charlie', 'Analyst', 40000, TO_DATE('2021-06-01', 'YYYY-MM-DD'));

SQL>INSERT INTO Employee VALUES (104, 'David', 'Salesman', 35000, TO_DATE('2022-09-12', 'YYYY-MM-DD'));

SQL>INSERT INTO Employee VALUES (105, 'Eva', 'HR', 30000, TO_DATE('2023-03-21', 'YYYY-MM-DD'));

✅ (iii) Update the Column Details of Job

SQL>UPDATE Employee

SET Job = 'Accountant'

WHERE Empno = 102;

You can also update multiple if needed.

✅ (iv) Rename the Column Sal to Emp_Salary

SQL>ALTER TABLE Employee

RENAME COLUMN Sal TO Emp_Salary;

✅ (v) Delete the Employee Whose Empno is 101

SQL>DELETE FROM Employee

WHERE Empno = 101;

✅ Final Table Structure (after ALTER)

Column Name	Data Type
Empno	NUMBER
Ename	VARCHAR2(20)
Job	VARCHAR2(20)
Emp_Salary	NUMBER
DoJ	DATE

✅ SELECT Query to View Records

SQL>SELECT * FROM Employee;

📌 Sample Output:

Empno	Ename	Job	Emp_Salary	DoJ
102	Bob	Accountant	25000	2019-11-10
103	Charlie	Analyst	40000	2021-06-01
104	David	Salesman	35000	2022-09-12
105	Eva	HR	30000	2023-03-21

EXERCISE 5:

Write a program for student database and do the following

operations.

(i) Modification

(ii) Deletion

(iii) Aggregate functions count(), sum(), avg(), max(),min()

ANSWER:

Here's a complete SQL program to create and manage a Student Database, including:

✅ Table creation
✅ Insertion of data
✅ (i) Modification (UPDATE)
✅ (ii) Deletion (DELETE)
✅ (iii) Use of Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN()

🟩 Step 1: Create the STUDENT Table

CREATE TABLE Student (

RollNo INT PRIMARY KEY,

Name VARCHAR(50),

Dept VARCHAR(30),

Marks INT

);

🟨 Step 2: Insert Sample Records

INSERT INTO Student VALUES (101, 'Alice', 'CSE', 85);

INSERT INTO Student VALUES (102, 'Bob', 'CSE', 78);

INSERT INTO Student VALUES (103, 'Charlie', 'ECE', 92);

INSERT INTO Student VALUES (104, 'David', 'IT', 69);

INSERT INTO Student VALUES (105, 'Eva', 'ECE', 88);

🟦 (i) MODIFICATION (UPDATE)

✅ Example: Update marks of student with RollNo 104

UPDATE Student

SET Marks = 75

WHERE RollNo = 104;

📌 David's marks are updated from 69 to 75.

🟥 (ii) DELETION

✅ Example: Delete student with RollNo 102

DELETE FROM Student WHERE RollNo = 102;

📌 Bob’s record is deleted from the table.

🟨 (iii) AGGREGATE FUNCTIONS

These are used to summarize data in a column.

✅ 1. COUNT() – Total number of students:

SELECT COUNT(*) AS Total_Students FROM Student;

✅ 2. SUM() – Total marks of all students:

SELECT SUM(Marks) AS Total_Marks FROM Student;

✅ 3. AVG() – Average marks of students:

SELECT AVG(Marks) AS Average_Marks FROM Student;

✅ 4. MAX() – Highest mark:

SELECT MAX(Marks) AS Highest_Mark FROM Student;

✅ 5. MIN() – Lowest mark:

SELECT MIN(Marks) AS Lowest_Mark FROM Student;

✅ Final Table After Deletion and Update (Example Output)

RollNo	Name	Dept	Marks
101	Alice	CSE	85
103	Charlie	ECE	92
104	David	IT	75
105	Eva	ECE	88

EXERCISE 6:

Create a table called Branch table.

Name Type

Branch name Varchar2(20)

Branch_city Varchar2(20)

Asserts Number

(i) Increase the size of data type for Asserts to the

branch

(ii) Add and drop a column to the Branch table

(iii) Insert values to the table.

(iv) Update the Branch_name column

(v) Delete any two columns from the table

✅ (a) Create the Branch table

SQL>CREATE TABLE Branch (

Branch_name VARCHAR2(20),

Branch_city VARCHAR2(20),

Asserts NUMBER

);

✅ (i) Increase the size of data type for Asserts column

SQL>ALTER TABLE Branch

MODIFY Asserts NUMBER(12, 2);

✅ (ii) Add and Drop a column to/from the Branch table

Add a column – e.g., Branch_code:

SQL>ALTER TABLE Branch

ADD Branch_code VARCHAR2(10);

Drop a column – e.g., Branch_code:

SQL>ALTER TABLE Branch

DROP COLUMN Branch_code;

✅ (iii) Insert values into the table

SQL>INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('Main', 'Chennai', 500000);

SQL>INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('West', 'Mumbai', 300000);

SQL>INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('East', 'Delhi', 400000);

✅ (iv) Update the Branch_name column

Example: Rename West branch to West Side:

SQL>UPDATE Branch

SET Branch_name = 'West Side'

WHERE Branch_name = 'West';

✅ (v) Delete any two columns from the table

To delete columns like Branch_city and Asserts:

SQL>ALTER TABLE Branch

DROP COLUMN Branch_city;

SQL>ALTER TABLE Branch

DROP COLUMN Asserts;

EXERCISE 7:

Create department table with the following structure.

Deptno (Number), Deptname (Varchar2(20)), location

(Varchar2(20))

(i) Add column designation to the department table.

(ii) Insert values into the table.

(iii) List the records of emp table grouped by deptno.

(iv) Update the record where deptno is 5.

(v) Delete any column data from the table

✅ (1) Create the Department table

SQL>CREATE TABLE Department (

Deptno NUMBER,

Deptname VARCHAR2(20),

location VARCHAR2(20)

);

✅ (i) Add column designation to the Department table

SQL>ALTER TABLE Department

ADD designation VARCHAR2(20);

✅ (ii) Insert values into the table

SQL>INSERT INTO Department (Deptno, Deptname, location, designation)

VALUES (1, 'HR', 'Chennai', 'Manager');

INSERT INTO Department (Deptno, Deptname, location, designation)

VALUES (2, 'Finance', 'Mumbai', 'Analyst');

INSERT INTO Department (Deptno, Deptname, location, designation)

VALUES (3, 'IT', 'Bangalore', 'Developer');

INSERT INTO Department (Deptno, Deptname, location, designation)

VALUES (4, 'Sales', 'Delhi', 'Executive');

INSERT INTO Department (Deptno, Deptname, location, designation)

VALUES (5, 'Admin', 'Hyderabad', 'Clerk');

✅ (iii) List the records of emp table grouped by deptno

SQL>SELECT deptno, COUNT(*) AS employee_count

FROM emp

GROUP BY deptno;

✅ (iv) Update the record where deptno is 5

SQL>UPDATE Department

SET Deptname = 'Support', location = 'Pune', designation = 'Support Staff'

WHERE Deptno = 5;

✅ (v) Delete any column data from the table

SQL>UPDATE Department

SET designation = NULL;

If instead you want to remove the entire column:

SQL>ALTER TABLE Department

DROP COLUMN designation;

EXERCISE 8:

Develop “Employee” details table.

(i) Count the number of employees in department 20

(ii) Find the minimum salary earned by clerk.

(iii) Find minimum, maximum, average salary of all employees.

(iv) List the minimum and maximum salaries for each job type.

(v) List the employee names in descending order.

(vi) List the employee id, names in ascending order by

empid.

✅ (1) Create the Employee table

SQL>CREATE TABLE Employee (

EmpID NUMBER,

EmpName VARCHAR2(20),

Job VARCHAR2(20),

Salary NUMBER,

DeptNo NUMBER

);

✅ (2) Insert sample data

SQL>INSERT INTO Employee (EmpID, EmpName, Job, Salary, DeptNo) VALUES (101, 'Alice', 'Clerk', 25000, 10);

INSERT INTO Employee (EmpID, EmpName, Job, Salary, DeptNo) VALUES (102, 'Bob', 'Manager', 55000, 20);

INSERT INTO Employee (EmpID, EmpName, Job, Salary, DeptNo) VALUES (103, 'Charlie', 'Clerk', 22000, 20);

INSERT INTO Employee (EmpID, EmpName, Job, Salary, DeptNo) VALUES (104, 'David', 'Analyst', 60000, 30);

INSERT INTO Employee (EmpID, EmpName, Job, Salary, DeptNo) VALUES (105, 'Eve', 'Manager', 58000, 20);

INSERT INTO Employee (EmpID, EmpName, Job, Salary, DeptNo) VALUES (106, 'Frank', 'Clerk', 21000, 10);

💠 Required Queries

✅ (i) Count the number of employees in department 20

SQL>SELECT COUNT(*) AS Emp_Count

FROM Employee

WHERE DeptNo = 20;

✅ (ii) Find the minimum salary earned by a 'Clerk'

SQL>SELECT MIN(Salary) AS Min_Clerk_Salary

FROM Employee

WHERE Job = 'Clerk';

✅ (iii) Find minimum, maximum, and average salary of all employees

SQL>SELECT

MIN(Salary) AS Min_Salary,

MAX(Salary) AS Max_Salary,

AVG(Salary) AS Avg_Salary

FROM Employee;

✅ (iv) List the minimum and maximum salaries for each job type

SQL>SELECT Job,

MIN(Salary) AS Min_Salary,

MAX(Salary) AS Max_Salary

FROM Employee

GROUP BY Job;

✅ (v) List the employee names in descending order

SQL>SELECT EmpName

FROM Employee

ORDER BY EmpName DESC;

✅ (vi) List the employee id and names in ascending order by EmpID

SQL>SELECT EmpID, EmpName

FROM Employee

ORDER BY EmpID ASC;

EXERCISE 9:

Create an employee data base and divided into certain the

database into certain departments and each department

consists of employees. The following two tables describes

the schemes.

Dept_table (deptno, dname, loc)

Emp_table (empno, ename, job, mgr, hiredate, sal, comm,

deptno)

(i) Update the employee salary by 15%, whose

experience is greater than 10 years.

(ii) Delete the employees, who completed 30 years of

service.

(iii) Display the manager who is having maximum

number of employees working under him?

(iv) Create a view, which contain employee names and

their manager.

Generate a report of employees who gets more than

20000 salary

Here's a complete SQL solution for your Employee Database system using two tables: Dept_table and Emp_table. Each step includes creation, updates, deletions, queries, and a report — with clear and practical SQL examples.

💠 Table Structures

✅ (1) Create Dept_table

SQL>CREATE TABLE Dept_table (

deptno NUMBER PRIMARY KEY,

dname VARCHAR2(20),

loc VARCHAR2(20)

);

✅ (2) Create Emp_table

SQL>CREATE TABLE Emp_table (

empno NUMBER PRIMARY KEY,

ename VARCHAR2(20),

job VARCHAR2(20),

mgr NUMBER,

hiredate DATE,

sal NUMBER,

comm NUMBER,

deptno NUMBER,

FOREIGN KEY (deptno) REFERENCES Dept_table(deptno)

);

💠 Insert Sample Data

✅ Dept_table sample data

INSERT INTO Dept_table VALUES (10, 'HR', 'Chennai');

INSERT INTO Dept_table VALUES (20, 'IT', 'Bangalore');

INSERT INTO Dept_table VALUES (30, 'Sales', 'Mumbai');

✅ Emp_table sample data

INSERT INTO Emp_table VALUES (101, 'Alice', 'Clerk', 201, TO_DATE('01-JAN-2005', 'DD-MON-YYYY'), 18000, 1000, 10);

INSERT INTO Emp_table VALUES (102, 'Bob', 'Manager', NULL, TO_DATE('15-MAR-2000', 'DD-MON-YYYY'), 35000, NULL, 20);

INSERT INTO Emp_table VALUES (103, 'Charlie', 'Developer', 102, TO_DATE('10-JUN-2012', 'DD-MON-YYYY'), 22000, 500, 20);

INSERT INTO Emp_table VALUES (104, 'David', 'Salesman', 102, TO_DATE('20-JUL-1990', 'DD-MON-YYYY'), 17000, 2000, 30);

INSERT INTO Emp_table VALUES (105, 'Eve', 'Analyst', 102, TO_DATE('25-DEC-2013', 'DD-MON-YYYY'), 25000, NULL, 20);

💠 Required Operations

✅ (i) Update employee salary by 15% for experience > 10 years

SQL>UPDATE Emp_table

SET sal = sal * 1.15

WHERE MONTHS_BETWEEN(SYSDATE, hiredate) / 12 > 10;

✅ (ii) Delete employees who completed 30 years of service

SQL>DELETE FROM Emp_table

WHERE MONTHS_BETWEEN(SYSDATE, hiredate) / 12 >= 30;

✅ (iii) Display the manager with maximum number of employees under them

SQL>SELECT mgr, COUNT(*) AS num_employees

FROM Emp_table

WHERE mgr IS NOT NULL

GROUP BY mgr

ORDER BY num_employees DESC

FETCH FIRST 1 ROWS ONLY;

✅ (iv) Create a view of employee names and their managers

SQL>CREATE VIEW emp_manager_view AS

SELECT e.ename AS employee_name, m.ename AS manager_name

FROM Emp_table e

LEFT JOIN Emp_table m ON e.mgr = m.empno;

✅ (v) Generate a report of employees with salary > 20000

SQL>SELECT empno, ename, job, sal, deptno

FROM Emp_table

WHERE sal > 20000;

Exercise 10

Create Employee Database with Employee_Personal (ID,

NAME, DOJ, AGE, ADDRESS, DEPARTMENT,

DESIGNATION) and Employee Salary (ID, NAME,

SALARY, BP, DA, HRA, CCA, GROSS_PAY, NET_PAY).

Use sql sub queries to updates the SALARY by 10 times in

the EMPLOYEE_Salary table for all employee whose AGE

is greater than or equal to 29.

CREATE TABLE:

Create the tables


CREATE TABLE Employee_Personal (
    ID INT PRIMARY KEY,
    NAME VARCHAR(100),
    DOJ DATE,
    AGE INT,
    ADDRESS VARCHAR(255),
    DEPARTMENT VARCHAR(50),
    DESIGNATION VARCHAR(50)
);

CREATE TABLE Employee_Salary (
    ID INT PRIMARY KEY,
    NAME VARCHAR(100),
    SALARY DECIMAL(10,2),
    BP DECIMAL(10,2),
    DA DECIMAL(10,2),
    HRA DECIMAL(10,2),
    CCA DECIMAL(10,2),
    GROSS_PAY DECIMAL(10,2),
    NET_PAY DECIMAL(10,2)
);

insert:

SQL>INSERT INTO Employee_Personal (ID, NAME, DOJ, AGE, ADDRESS, DEPARTMENT, DESIGNATION)

VALUES

(1, 'Alice', '2020-01-15', 30, '123 Main St', 'IT', 'Developer'),

(2, 'Bob', '2019-03-10', 28, '456 Elm St', 'HR', 'Manager');

SQL>INSERT INTO Employee_Salary (ID, NAME, SALARY, BP, DA, HRA, CCA, GROSS_PAY, NET_PAY)

VALUES

(1, 'Alice', 50000, 50000, 0, 0, 0, 0, 0),

(2, 'Bob', 40000, 40000, 0, 0, 0, 0, 0);

-- Update the salary components for all employees

SQL>UPDATE Employee_Salary

SET

DA = BP * 0.20,

HRA = BP * 0.10,

CCA = BP * 0.05,

GROSS_PAY = BP + (BP * 0.20) + (BP * 0.10) + (BP * 0.05),

NET_PAY = (BP + (BP * 0.20) + (BP * 0.10) + (BP * 0.05)) * 0.90;

Update SALARY Using Subquerysql

SQL>UPDATE Employee_Salary

SET SALARY = SALARY * 10

WHERE ID IN (

SELECT ID

FROM Employee_Personal

WHERE AGE >= 29

);

EXERCISE 11:

Create a Student Database with the following tables.

STUDENT_PERSONAL_INFO with fields (NAME,

ROLL_NO, ADDRESS, PHONE_NO)

STUDENT_CLASS_INFO with NAME, ROLL_NO,

SECTION.

Use subqueries to display NAME, PHONE_NO of the

Student Database whose section is “A”.

answer:

Step 1: Create Tables

SQL>
CREATE TABLE STUDENT_PERSONAL_INFO (
    NAME VARCHAR(100),
    ROLL_NO INT PRIMARY KEY,
    ADDRESS VARCHAR(255),
    PHONE_NO VARCHAR(15)
);

-- Table: STUDENT_CLASS_INFO
CREATE TABLE STUDENT_CLASS_INFO (
    NAME VARCHAR(100),
    ROLL_NO INT PRIMARY KEY,
    SECTION CHAR(1)
);

✅ Step 2: Sample Data (Optional for Testing)


SQL>
INSERT INTO STUDENT_PERSONAL_INFO (NAME, ROLL_NO, ADDRESS, PHONE_NO)
VALUES
('Alice', 101, '123 Maple St', '555-1234'),
('Bob', 102, '456 Oak St', '555-5678'),
('Charlie', 103, '789 Pine St', '555-9012');

SQL>
INSERT INTO STUDENT_CLASS_INFO (NAME, ROLL_NO, SECTION)
VALUES
('Alice', 101, 'A'),
('Bob', 102, 'B'),
('Charlie', 103, 'A');

✅ Step 3: Query using Subquery

SELECT NAME, PHONE_NO
FROM STUDENT_PERSONAL_INFO
WHERE ROLL_NO IN (
    SELECT ROLL_NO
    FROM STUDENT_CLASS_INFO
    WHERE SECTION = 'A'
);

Search This Blog

KANIMOZHIK