Practical No.

1
Write SQL queries for Data Definition Language (DDL).

DDL Commands Covered

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

1. CREATE

Definition:
CREATE command is used to create database objects such as tables, views, and indexes.

Syntax:

CREATE TABLE table_name ( column_name datatype [constraint], ... );

2. ALTER

Definition:
ALTER command is used to modify the structure of an existing database object.

Syntax (Add Column):

ALTER TABLE table_name ADD column_name datatype;

Syntax (Modify Column):

ALTER TABLE table_name MODIFY column_name datatype;

Syntax (Drop Column):

ALTER TABLE table_name DROP column_name;

3. DROP

Definition:
DROP command is used to permanently delete database objects.

Syntax:

DROP TABLE table_name;

4. TRUNCATE

Definition:
TRUNCATE command is used to remove all records from a table permanently.

Syntax:

TRUNCATE TABLE table_name;

5. RENAME

Definition:
RENAME command is used to change the name of a database object.

Syntax:

RENAME old_table_name TO new_table_name;

5 Questions on DDL (Practical No. 1)

Q1

Create a table EMPLOYEE with EmpID, Name, Salary.

Q2

Add column Department to EMPLOYEE table.

Q3

Change Salary datatype to NUMBER(8,2).

Q4

Rename EMPLOYEE table to EMP_DETAILS.

Q5

Remove EMP_DETAILS table completely.

 Practical No. 2 – DML Commands
Aim

Write SQL queries for Data Manipulation Language (DML).

DML Commands Covered

1. INSERT
2. UPDATE
3. DELETE
4. SELECT
DML Commands
1. INSERT

Definition:
INSERT command is used to add new records into a table.

Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);

2. UPDATE

Definition:
UPDATE command is used to modify existing records in a table.

Syntax:

UPDATE table_name SET column_name = value WHERE condition;

3. DELETE

Definition:
DELETE command is used to remove records from a table.

Syntax:

DELETE FROM table_name WHERE condition;

4. SELECT

Definition:
SELECT command is used to retrieve data from a table.

Syntax:

SELECT column_list FROM table_name WHERE condition;

5 Practice Questions on DML (Practical No. 2)

Q1

Insert 3 records into Employee table. EmpID, Name,Department,Salary

Q2

Display all employee details.

Q3

Update department of EmpID = 1.

Q4

Delete employee whose salary is less than 25000.

Q5

Display employees from IT department.

Queries on DDL + DML combined

Query 1
Create an EMPLOYEE table with columns EmpID, Name, Salary, Department.
Insert 5 employee records.
Add a new column City using ALTER command.
Update the City of employees whose Department is IT.
Display all records.

Query 2

Create an EMPLOYEE table with columns EmpID, Name, Salary.

Insert 5 records.
Modify the datatype of the Name column to increase its size.
Update Salary by adding 3000 for employees whose Salary is less than 25000.
Display updated records.

Query 3

Create an EMPLOYEE table with columns EmpID, Name, Salary, Dept.

Insert 5 records.
Delete employees whose Department is HR.
Display remaining employee details.

Query 4

Create an EMPLOYEE table with columns EmpID, Name, Salary, Dept.

Insert 5 records.
Add a new column Experience.
Update Experience to 5 years for employees whose Salary is greater than 40000.
Display Name and Experience.

Query 5

Create an EMPLOYEE table with columns EmpID, Name, Salary.

Insert 5 records.
Update Salary using SET clause for all employees.
Display employees whose Salary is greater than 30000.

Query 6

Create an EMPLOYEE table with columns EmpID, Name, Salary, Dept.

Insert 5 records.
Delete employees whose Salary is less than 20000.
Display all records after deletion.

Query 7

Create an EMPLOYEE table with columns EmpID, Name, Salary, Dept.

Insert 5 records.
Modify the Dept column size using ALTER command.
Update Department name from “Admin” to “Administration”.
Display updated table.

Query 8

Create an EMPLOYEE table with columns EmpID, Name, Salary, City.

Insert 5 records.
Update Salary for employees living in “Delhi”.
Display Name and Salary of those employees.

Query 9

Create an EMPLOYEE table with columns EmpID, Name, Salary.

Insert 5 records.
Truncate the table.
Display all records from the table.

Query 10

Create an EMPLOYEE table with columns EmpID, Name, Salary, Dept.

Insert 5 records.
Add a new column Bonus.
Update Bonus for employees whose Salary is greater than 35000.
Delete employees whose Bonus is less than 2000.
Display final employee details.
Practical no 3
Write SQL queries using logical, relational, and arithmetic operators.

Part 1 : Create following table and apply logical, relational, and arithmetic operators

EMPLOYEE

EmpID Name Salary Dept

1 Amit 20000 IT

2 Neha 30000 HR

3 Ravi 25000 IT

4 Riya NULL HR

1️ ARITHMETIC OPERATORS
+ , - , * , / , %

🔹 Meaning

Used to perform mathematical calculations on numeric values.

🔸 + (Addition)
SELECT Name, Salary + 5000 AS New_Salary FROM Employee;

Output

Name New_Salary

Amit 25000

Neha 35000

Ravi 30000

Riya NULL
🔸 - (Subtraction)
SELECT Name, Salary - 2000 AS Updated_Salary FROM Employee;

Output

Name Updated_Salary

Amit 18000

Neha 28000

Ravi 23000

Riya NULL

🔸 * (Multiplication)
SELECT Name, Salary * 2 AS Double_Salary FROM Employee;

Output

Name Double_Salary

Amit 40000

Neha 60000

Ravi 50000

Riya NULL

🔸 / (Division)
SELECT Name, Salary / 2 AS Half_Salary FROM Employee;

Output

Name Half_Salary

Amit 10000

Neha 15000

Ravi 12500
Name Half_Salary

Riya NULL

2️ COMPARISON OPERATORS
(relational)
= , != , <> , > , < , >= , <=

Operator Meaning

= Equal to

!= Not equal

<> Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

🔸 = Equal
SELECT * FROM Employee WHERE Salary = 25000;
EmpID Name Salary Dept

3 Ravi 25000 IT

!= / <> Not Equal

SELECT * FROM Employee WHERE Dept != 'IT';

EmpID Name Salary Dept

2 Neha 30000 HR
 EmpID Name Salary Dept

4 Riya NULL HR

🔸 > Greater Than

SELECT * FROM Employee WHERE Salary > 25000;
EmpID Name Salary Dept

2 Neha 30000 HR

🔸 < Less Than

SELECT * FROM Employee WHERE Salary < 25000;
EmpID Name Salary Dept

1 Amit 20000 IT

🔸 >= Greater Than or Equal

SELECT * FROM Employee WHERE Salary >= 25000;
EmpID Name Salary Dept

2 Neha 30000 HR

3 Ravi 25000 IT

🔸 <= Less Than or Equal

SELECT * FROM Employee WHERE Salary <= 20000;
EmpID Name Salary Dept

1 Amit 20000 IT

3️ LOGICAL OPERATORS
AND , OR , NOT

🔹 Meaning

Used to combine conditions.

🔸 AND
SELECT * FROM Employee
WHERE Dept = 'IT' AND Salary >= 25000;
EmpID Name Salary Dept

3 Ravi 25000 IT

🔸 OR
SELECT * FROM Employee
WHERE Dept = 'HR' OR Salary = 20000;
EmpID Name Salary Dept

1 Amit 20000 IT

2 Neha 30000 HR

4 Riya NULL HR

🔸 NOT
SELECT * FROM Employee WHERE NOT Dept = 'IT';
EmpID Name Salary Dept

2 Neha 30000 HR

4 Riya NULL HR
Part 2: Create following table and perform given query

Table: STUDENT

RollNo Name Marks Age Class

1 Amit 85 20 TY
2 Neha 72 19 SY
3 Rahul 90 21 TY
4 Pooja 65 20 SY
5 Ankit 40 18 FY

1. Write a query to display the names of students whose marks increase by five still
remain less than seventy.
2. Display roll number and name of students whose marks become double and exceed
one hundred and sixty.
3. Find students whose marks reduce by ten and still remain greater than sixty and
whose age is more than nineteen.
4. Display students who are either in second year or third year and whose marks after
adding five cross seventy.
5. Write a query to find students whose half of marks is greater than the marks of student
having roll number five.
6. Display student details where the sum of marks and age is greater than one hundred.
7. Find students whose marks are not in the range of fifty to eighty and who do not
belong to first year.
8. Display students whose age plus one becomes equal to twenty.
9. Write a query to display names of students where marks multiplied by two are greater
than the sum of marks and age.
10. Find students whose marks decrease by twenty and become less than fifty or whose
age is less than nineteen.
Practical no 4
Aim : Write SQL queries using SET operators using following.
Create following tables

EMP1 Table
EmpID Name

1 Amit

2 Neha

3 Ravi

EMP2 Table
EmpID Name

3 Ravi

4 Pooja

5 Kiran

1. Perform set operators

2. UNION
3. UNION ALL
4. INTERSECT
5. MINUS

Write syntax query and output for all set operators

Given Tables (same as above)

Student_A

StudentID Name

101 Amit

102 Neha

103 Rahul

104 Pooja
Student_B

StudentID Name

103 Rahul

104 Pooja

105 Karan

106 Sneha

Q1

Display the list of students who are present in both Student_A and Student_B tables.

Q2

Display the students who are present in Student_A table but completely absent in
Student_B table.

Q3

Display the students who are present in Student_B table but not available in Student_A
table.

Q4

Display the students who are present in either Student_A or Student_B table but not in
both tables.

Q5

Display the complete list of students from both tables, but ensure that no duplicate
student appears in the result.

Q6

Display the list of students whose StudentID is greater than 102 and who appear in both
tables.
Practical no 5
Aim: Write SQL queries using GROUP BY, ORDER BY, and aggregate functions.
Create following table and perform given queries

Employee

EmpID EmpName Dept Salary

1 Amit IT 50000

2 Neha HR 40000

3 Rahul IT 60000

4 Priya Finance 45000

5 Karan HR 42000

6 Sneha IT 55000

Group by

1. Find number of employees in each department

2. Find average salary of each department

Order by

1. Display employees salary in ascending order

2. Display employees ordered by department (A–Z) and salary descending

AGGREGATE FUNCTIONS

1. Find total salary paid by company (SUM)

2. Find maximum and minimum salary (MAX, MIN)
3. Find average salary and total employees (AVG, COUNT)
4. Count total number of employees
5. Count employees department-wise
6. Count employees with salary greater than 45000
7. Count distinct departments
Practical no 6
Aim: Write SQL queries using DISTINCT, UNIQUE, and HAVING keywords.

Base Table: Employee

EmpID EmpName Department Salary City Email

1 Amit IT 50000 Pune amit@[Link]

2 Neha HR 40000 Mumbai neha@[Link]

3 Ravi IT 60000 Pune ravi@[Link]

4 Priya Finance 45000 Delhi priya@[Link]

5 Karan IT 50000 Mumbai karan@[Link]

6 Sneha HR 40000 Pune sneha@[Link]

7 Raj Finance 70000 Delhi raj@[Link]

8 Anita IT 60000 Mumbai anita@[Link]

📝 QUESTIONS
Q1. Display distinct departments from Employee table.

Q2. Show distinct cities where employees are working.

Q3. Display distinct salary values.

Q4. Show distinct Department and City combinations.

Q5. Display departments having more than 2 employees.

Q6. Show cities having more than 2 employees.

Q7. Display departments where average salary is greater than 50000.

Q8. Show salary groups having more than 1 employee.

Q9. Display departments where maximum salary is greater than 65000.

Q10. Email column has UNIQUE constraint. What will happen if we insert
duplicate email 'amit@[Link]'?

Mixed Queries :

🔹 Q1
Display distinct departments where more than 2 employees are working.

🔹 Q2
Show distinct cities where average salary is greater than 45000.

🔹 Q3
Display distinct salary values where more than 1 employee is getting same salary.

🔹 Q4
Show distinct Department and City combinations where total salary of employees is greater
than 100000.

🔹 Q5
Display distinct departments where maximum salary is greater than 60000 and total
employees are at least 2.