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.
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
Riya NULL
🔸 % (Modulus – Remainder)
SELECT Salary % 2 AS Remainder FROM Employee;
Output
Remainder
0
Remainder
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
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.