1.
Create a database table, add constraints (primary key, unique, check, not
null), insert rows, update and delete rows using SQL DDL and DML
commands.
-- Create Table with Constraints
CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
age INT CHECK (age >= 18)
);
-- Insert Records
INSERT INTO Student VALUES (1, 'Arun', 'arun@[Link]', 20);
INSERT INTO Student VALUES (2, 'Divya', 'divya@[Link]', 22);
INSERT INTO Student VALUES (3, 'Kiran', 'kiran@[Link]', 19);
-- Display Records
SELECT * FROM Student;
-- Update Record
UPDATE Student
SET age = 23
WHERE student_id = 2;
-- Display After Update
SELECT * FROM Student;
-- Delete Record
DELETE FROM Student
WHERE student_id = 3;
-- Final Display
SELECT * FROM Student;
Output:
student_id name email age
1 Arun arun@[Link] 20
2 Divya divya@[Link] 22
3 Kiran kiran@[Link] 19
student_id name email age
1 Arun arun@[Link] 20
2 Divya divya@[Link] 23
3 Kiran kiran@[Link] 19
student_id name email age
1 Arun arun@[Link] 20
2 Divya divya@[Link] 23
2A. Create a set of tables, add foreign key constraints and incorporate
referential integrity.
-- Create Parent Table
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
-- Create Child Table with Foreign Key
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
dept_id INT,
FOREIGN KEY (dept_id)
REFERENCES Department(dept_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
-- Insert into Parent Table
INSERT INTO Department VALUES (1, 'HR');
INSERT INTO Department VALUES (2, 'IT');
-- Insert into Child Table
INSERT INTO Employee VALUES (101, 'Arun', 1);
INSERT INTO Employee VALUES (102, 'Divya', 2);
INSERT INTO Employee VALUES (103, 'Kiran', 1);
-- Display Tables
SELECT * FROM Department;
SELECT * FROM Employee;
-- Update Parent Key (Referential Integrity - CASCADE)
UPDATE Department
SET dept_id = 3
WHERE dept_id = 2;
-- Delete Parent Row (CASCADE)
DELETE FROM Department
WHERE dept_id = 1;
-- Final Output
SELECT * FROM Department;
SELECT * FROM Employee;
Output
dept_id dept_name
1 HR
2 IT
emp_id emp_name dept_id
101 Arun 1
102 Divya 2
103 Kiran 1
dept_id dept_name
3 IT
emp_id emp_name dept_id
102 Divya 3
2B. Query the database table using different WHERE clause conditions and also
implement aggregate functions
-- Create Table
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
department VARCHAR(50),
salary INT
);
-- Insert Records
INSERT INTO Employee VALUES (1, 'Arun', 'HR', 20000);
INSERT INTO Employee VALUES (2, 'Divya', 'IT', 30000);
INSERT INTO Employee VALUES (3, 'Kiran', 'HR', 25000);
INSERT INTO Employee VALUES (4, 'Meena', 'IT', 40000);
INSERT INTO Employee VALUES (5, 'Ravi', 'Sales', 15000);
-- WHERE Clause Conditions
SELECT * FROM Employee WHERE department = 'HR';
SELECT * FROM Employee WHERE salary > 25000;
SELECT * FROM Employee WHERE department = 'IT' AND salary > 30000;
SELECT * FROM Employee WHERE salary BETWEEN 20000 AND 35000;
-- Aggregate Functions
SELECT COUNT(*) AS Total_Employees FROM Employee;
SELECT SUM(salary) AS Total_Salary FROM Employee;
SELECT AVG(salary) AS Average_Salary FROM Employee;
SELECT MAX(salary) AS Highest_Salary FROM Employee;
SELECT MIN(salary) AS Lowest_Salary FROM Employee;
-- Aggregate with WHERE
SELECT AVG(salary) AS HR_Avg_Salary
FROM Employee
WHERE department = 'HR';
Output
emp_id emp_name department salary
1 Arun HR 20000
3 Kiran HR 25000
emp_id emp_name department salary
2 Divya IT 30000
4 Meena IT 40000
emp_id emp_name department salary
4 Meena IT 40000
emp_id emp_name department salary
1 Arun HR 20000
2 Divya IT 30000
3 Kiran HR 25000
Total_Employees
5
Total_Salary
130000
Average_Salary
26000
Highest_Salary
40000
Lowest_Salary
15000
HR_Avg_Salary
22500
[Link] the database tables and explore subqueries and simple join operations
-- Remove old tables (to avoid error)
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;
-- Create Department Table
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
-- Create Employee Table
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
-- Insert into Department
INSERT INTO Department VALUES (1, 'HR');
INSERT INTO Department VALUES (2, 'IT');
INSERT INTO Department VALUES (3, 'Sales');
-- Insert into Employee
INSERT INTO Employee VALUES (101, 'Arun', 20000, 1);
INSERT INTO Employee VALUES (102, 'Divya', 30000, 2);
INSERT INTO Employee VALUES (103, 'Kiran', 25000, 1);
INSERT INTO Employee VALUES (104, 'Meena', 40000, 2);
INSERT INTO Employee VALUES (105, 'Ravi', 15000, 3);
-- 🔹 JOIN Query
SELECT e.emp_id, e.emp_name, d.dept_name, [Link]
FROM Employee e
JOIN Department d
ON e.dept_id = d.dept_id;
-- 🔹 Subquery (Salary > Average)
SELECT emp_name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);
-- 🔹 Subquery with IN (HR Employees)
SELECT emp_name
FROM Employee
WHERE dept_id IN (
SELECT dept_id FROM Department WHERE dept_name = 'HR'
);
Output:
emp_id emp_name dept_name salary
101 Arun HR 20000
102 Divya IT 30000
103 Kiran HR 25000
104 Meena IT 40000
105 Ravi Sales 15000
emp_name salary
Divya 30000
Meena 40000
emp_name
Arun
emp_name
Kiran
4. Write user defined functions and stored procedures in SQL
-- Drop table
DROP TABLE IF EXISTS Employee;
-- Create Table
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT
);
-- Insert Data
INSERT INTO Employee VALUES (1, 'Arun', 20000);
INSERT INTO Employee VALUES (2, 'Divya', 30000);
INSERT INTO Employee VALUES (3, 'Kiran', 25000);
--------------------------------------------------
-- Function Logic (Bonus Calculation)
SELECT emp_name, salary, salary * 0.10 AS bonus
FROM Employee;
--------------------------------------------------
-- Procedure Logic (High Salary Employees)
SELECT * FROM Employee
WHERE salary > 22000;
Output
emp_name salary bonus
Arun 20000 2000
Divya 30000 3000
Kiran 25000 2500
emp_id emp_name salary
2 Divya 30000
3 Kiran 25000
5. Execute complex transactions and realize DCL and TCL commands.
-- Create Table
CREATE TABLE Account (
id INT PRIMARY KEY,
name VARCHAR(20),
balance INT
);
-- Insert Data
INSERT INTO Account VALUES (1, 'Arun', 5000);
INSERT INTO Account VALUES (2, 'Divya', 7000);
-- Transaction
BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 1000 WHERE id = 1;
UPDATE Account SET balance = balance + 1000 WHERE id = 2;
COMMIT;
-- Display
SELECT * FROM Account;
Output
id name balance
1 Arun 4000
2 Divya 8000
6. Write SQL triggers for insert, delete, and update operations in database tables
-- Drop tables
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Log;
-- Main Table
CREATE TABLE Employee (
id INT PRIMARY KEY,
name VARCHAR(20),
salary INT
);
-- Log Table (to store trigger actions)
CREATE TABLE Log (
msg TEXT
);
--------------------------------------------------
-- 🔹 INSERT TRIGGER
CREATE TRIGGER trg_insert
AFTER INSERT ON Employee
BEGIN
INSERT INTO Log VALUES ('Inserted');
END;
--------------------------------------------------
-- 🔹 UPDATE TRIGGER
CREATE TRIGGER trg_update
AFTER UPDATE ON Employee
BEGIN
INSERT INTO Log VALUES ('Updated');
END;
--------------------------------------------------
-- 🔹 DELETE TRIGGER
CREATE TRIGGER trg_delete
AFTER DELETE ON Employee
BEGIN
INSERT INTO Log VALUES ('Deleted');
END;
--------------------------------------------------
-- Perform Operations
INSERT INTO Employee VALUES (1, 'Arun', 20000);
UPDATE Employee SET salary = 25000 WHERE id = 1;
DELETE FROM Employee WHERE id = 1;
--------------------------------------------------
-- Display Log
SELECT * FROM Log;
Output
msg
Inserted
Updated
Deleted
7. Create views and indexes for database tables with larger data.
-- Drop table
DROP TABLE IF EXISTS Employee;
-- Create Table
CREATE TABLE Employee (
id INT PRIMARY KEY,
name VARCHAR(20),
dept VARCHAR(20),
salary INT
);
-- Insert Data
INSERT INTO Employee VALUES (1, 'Arun', 'HR', 20000);
INSERT INTO Employee VALUES (2, 'Divya', 'IT', 30000);
INSERT INTO Employee VALUES (3, 'Kiran', 'HR', 25000);
INSERT INTO Employee VALUES (4, 'Meena', 'IT', 40000);
--------------------------------------------------
-- 🔹 CREATE VIEW (only IT employees)
CREATE VIEW IT_Employees AS
SELECT name, salary
FROM Employee
WHERE dept = 'IT';
-- Display View
SELECT * FROM IT_Employees;
--------------------------------------------------
-- 🔹 CREATE INDEX (on salary column)
CREATE INDEX idx_salary
ON Employee(salary);
Output
name salary
Divya 30000
Meena 40000
8. Create an XML database and validate it using XML schema