INDEX
Sr No. Practical Date Grade Sign
1 To study SQL and Features of SQL.
2 To study SQL Components.
Study of DDL and DML command.
3 Create table and insert sample data in
tables.
Perform queries to retrieve data from
4 tables with different predicates (LIKE,
BETWEEN, IN) etc.
5 To study aggregate functions with query.
6 To study Single-row functions.
Study of different data manipulation
7
commands.
Displaying data from Multiple Tables
8
(join).
To apply the concept of Aggregating
9
Data using Group functions.
To study and apply the concept of
10
subquery.
To study and apply the concept of
11
security and privileges.
12 Study and apply PL/SQL.
To study stored procedure and stored
13
function
14 Study and execute a Cursor.
15 Study and execute a Trigger.
Practical-1
AIM: To study SQL and Features of SQL.
➢ What is SQL?
SQL (Structured Query Language) is a standard language used to interact with relational databases.
It allows you to store, manipulate, and retrieve data from databases like MySQL, Oracle, PostgreSQL,
SQL Server, etc.
➢ Features of SQL
Here are the main features you should know:
1. Data Definition Language (DDL)
Used to define database structures.
• CREATE – create new tables, databases, views
• ALTER – modify an existing table
• DROP – delete tables/databases
• TRUNCATE – remove all rows from a table (but keep structure)
2. Data Manipulation Language (DML)
Used to manage data inside tables.
• INSERT – add new data
• UPDATE – modify existing data
• DELETE – remove data
• SELECT – retrieve data (most used)
3. Data Control Language (DCL)
Used for permissions & security.
• GRANT – give access rights
• REVOKE – remove access rights
Enrolment Number - 1
4. Transaction Control Language (TCL)
Used to manage transactions.
• COMMIT – save changes permanently
• ROLLBACK – undo changes
• SAVEPOINT – set a temporary point in a transaction
5. Other Features
• Data Querying: fetch records with conditions (WHERE, LIKE, BETWEEN, etc.)
• Joins: combine data from multiple tables (INNER JOIN, LEFT JOIN, etc.)
• Constraints: ensure data integrity (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL,
CHECK)
• Views: virtual tables created from queries
• Indexes: speed up searching
• Functions & Operators: (COUNT(), AVG(), SUM(), string & date functions)
Enrolment Number - 2
Practical-2
AIM: To study SQL Components.
There are five types of SQL commands:
DDL, DML, DCL, TCL, and DQL.
1 Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
All the command of DDL is auto-committed that means it permanently save all the changes in the database.
Here are some commands that come under DDL:
● CREATE
● DROP
● ALTER
● TRUNCATE
CREATE: It is used to create a new table in the database.
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE,);
DROP: It is used to delete both the structure and record
stored in the table.
Syntax:
DROP TABLE TABLE_NAME;
ALTER: It is used to alter the structure of the database. This
change could be either to modify the characteristics of an existing attribute or probably to add a new
attribute.
To add a new column in the table
Syntax:
ALTER TABLE TABLE_NAME ADD (COLUMN_NAME COLUMN_DEFINITION);
To modify existing column in the table
Syntax:
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME COLUMN_DEFINITION);
TRUNCATE: It is used to delete all the rows from the table and
free the space containing the table.
Syntax:
TRUNCATE TABLE TABLE_NAME;
Enrolment Number - 3
2 Data Manipulation Language (DML)
DML commands are used to modify the database. It is responsible for all form of changes in the database.
The command of DML is not auto-committed that means it can't permanently save all the changes in the
database. They can be rollback.
Here are some commands that come under DML:
● INSERT
● UPDATE
● DELETE
INSERT: The INSERT statement is a SQL query. It is used to insert
data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME VALUES
(VALUE1, VALUE2, VALUE3, VALUE4, ....);
UPDATE: This command is used to update or modify the value of a
column in the table.
Syntax:
UPDATE TABLE_NAME
SET COLUMN_NAME = COLUMN_VALUE
WHERE COLUMN_NAME = COLUMN_VALUE;
DELETE: It is used to remove one or more row from a table.
Syntax:
DELETE FROM TABLE_NAME
WHERE COLUMN_NAME = COLUMN_VALUE;
3 Data Control Language (DCL)
DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
● GRANT
● REVOKE
GRANT: It is used to give user access privileges to a database.
Syntax:
GRANT SELECT, UPDATE ON TABLE_NAME TO SOME_USER, ANOTHER_USER;
REVOKE: It is used to take back permissions from the user.
Enrolment Number - 4
Syntax:
REVOKE SELECT, UPDATE ON TABLE_NAME FROM USER1, USER2;
4 Transaction Control Language (TCL)
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used while creating
tables or dropping them.
Here are some commands that come under TCL:
● COMMIT
● ROLLBACK
● SAVEPOINT
COMMIT: Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;
ROLLBACK: Rollback command is used to undo transactions that have
not already been saved to the database.
Syntax:
ROLLBACK;
SAVEPOINT: It is used to roll the transaction back to a certain
point without rolling back the entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
5 Data Query Language (DQL)
DQL is used to fetch the data from the database.
It uses only one command:
● SELECT
SELECT: It is used to select the attribute based on the
condition described by WHERE clause.
To select whole table:
Syntax:
SELECT * FROM TABLE_NAME;
To select certain columns:
Enrolment Number - 5
Syntax:
SELECT COLUMN_NAMES
FROM TABLE_NAME
WHERE COLUMN_NAME = COLUMN_VALUE;
Enrolment Number - 6
Practical-5
AIM: To Study Agreegate Function
1. Find total payroll of all employee
select sum(salary) from employee_details;
2. Find total salary for sell department
select sum(salary) from employee_details where Department='sales';
3. Find total salary for employee in Mumbai
select sum(salary) from employee_details where location='mumbai';
4. Find total salary of employee who join in or after 2020
select sum(salary) from employee_details where join_date>='2022-01-01';
Enrolment Number - 7
5. Find total salary for each department
select sum(salary) from employee_details group by department;
6. Find total salary of it employee in Noida
select sum(salary) from employee_details where location='noida';
7. Write query for salary of employee in hydrabad and ranchi
select sum(salary) from employee_details where location='ranchi' & 'hydrabad';
8. Find total salary of employee with salary >and 60000 group by department
SELECT department, SUM(salary) AS total_salary
FROM employee_details
WHERE salary > 60000
GROUP BY department;
Enrolment Number - 8
9. Find total salary of specific employee mena iyer
SELECT salary
FROM employee_details
WHERE name = 'mena iyer';
10. Find total salary for each location
SELECT location, SUM(salary) AS total_salary
FROM employee_details
GROUP BY location;
• AVG
1. Find avg salary of all employee
select AVG(salary) from employee_details;
Enrolment Number - 9
2. Find avg salary in hr department
select AVG(salary) from employee_details where Department='HR';
3. Find avg salary of each department
select AVG(salary) from employee_details group by department;
4. Find avg salary in Kolkata
select AVG(salary) from employee_details where location='KOLKATA';
5. Find avg salary each employee who join in 2020 or 2021
SELECT name, AVG(salary) AS avg_salary
FROM employee_details
WHERE YEAR(join_date) IN (2020, 2021)
GROUP BY name;
Enrolment Number - 10
6. Find every salary for each location
SELECT location, name, salary
FROM employee_details
ORDER BY location;
7. Find avg salary of finance employee in Bhopal
SELECT AVG(salary) AS avg_salary
FROM employee_details
WHERE department = 'Finance' AND location = 'bhopal';
8. Find department with avg salary >60000
SELECT department, AVG(salary) AS avg_salary
FROM employee_details
GROUP BY department
HAVING AVG(salary) > 60000;
Enrolment Number - 11
9. Find avg salary for employee whos name start with A
SELECT AVG(salary) AS avg_salary
FROM employee_details
WHERE name LIKE 'A%';
10. Find avg salary of employee who earn more than the company avg
SELECT AVG(salary) AS avg_salary_above_company_avg
FROM employee_details
WHERE salary > (SELECT AVG(salary) FROM employee_details);
• MIN
1.
Enrolment Number - 12
