Assi-1 — Create Tables with Constraints
Code
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE Student (
student_id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK (age >= 18),
marks NUMERIC(5,2) CHECK (marks BETWEEN 0 AND 100),
dept_id INT,
CONSTRAINT fk_student_dept
FOREIGN KEY (dept_id)
REFERENCES Department(dept_id)
);
INSERT INTO Department VALUES
(1, 'Computer Science'),
(2, 'Commerce'),
(3, 'Mathematics');
INSERT INTO Student VALUES
(101, 'Amit', 'amit@gmail.com', 20, 85.50, 1),
(102, 'Sneha', 'sneha@gmail.com', 21, 90.00, 2),
(103, 'Rahul', 'rahul@gmail.com', 19, 76.25, 1);
SELECT * FROM Department;
SELECT * FROM Student;
Output
Department Table
dept_id dept_name
1 Computer Science
2 Commerce
3 Mathematics
Student Table
student_id student_name email age marks dept_id
101 Amit amit@gmail.com 20 85.50 1
102 Sneha sneha@gmail.com 21 90.00 2
103 Rahul rahul@gmail.com 19 76.25 1
Constraint Demonstration
Primary Key violation:
ERROR: duplicate key value violates unique constraint
Foreign Key violation:
ERROR: department ID does not exist
CHECK violation:
ERROR: marks must be between 0 and 100
UNIQUE violation:
ERROR: email already exists
NOT NULL violation:
ERROR: student_name cannot be NULL
Assi-2 — Insert, Update, Delete and Alter Table
Code
INSERT INTO Student
VALUES (104, 'Pooja', 'pooja@gmail.com', 20, 88.00, 1);
SELECT * FROM Student
WHERE student_id = 104;
UPDATE Student
SET marks = 92.00
WHERE student_id = 104;
SELECT * FROM Student
WHERE student_id = 104;
DELETE FROM Student
WHERE student_id = 104;
SELECT * FROM Student;
ALTER TABLE Student
ADD COLUMN phone VARCHAR(15);
ALTER TABLE Student
RENAME COLUMN phone TO mobile_no;
ALTER TABLE Student
ALTER COLUMN mobile_no TYPE VARCHAR(20);
ALTER TABLE Student
DROP COLUMN mobile_no;
DROP TABLE Student;
Output
After INSERT:
student_id student_name email age marks dept_id
104 Pooja pooja@gmail.com 20 88.00 1
After UPDATE:
student_id student_name email age marks dept_id
104 Pooja pooja@gmail.com 20 92.00 1
After DELETE:
Record of Pooja is no longer present.
Assi-3 — Query Tables Using SELECT, WHERE, ORDER BY and GROUP BY
Code
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
department VARCHAR(30),
salary NUMERIC(10,2),
age INT
);
INSERT INTO Employee VALUES
(1, 'Amit', 'IT', 45000, 25),
(2, 'Sneha', 'HR', 38000, 30),
(3, 'Rahul', 'Finance', 42000, 28),
(4, 'Priya', 'Sales', 50000, 26),
(5, 'Kiran', 'IT', 60000, 32);
SELECT * FROM Employee;
SELECT emp_name, salary
FROM Employee;
SELECT emp_name, salary
FROM Employee
WHERE salary > 40000;
SELECT emp_name, age
FROM Employee
WHERE age > 25;
SELECT *
FROM Employee
WHERE salary BETWEEN 40000 AND 50000;
SELECT *
FROM Employee
WHERE department = 'IT';
SELECT *
FROM Employee
WHERE emp_name LIKE 'P%';
SELECT *
FROM Employee
ORDER BY salary ASC;
SELECT *
FROM Employee
ORDER BY salary DESC;
SELECT department, COUNT(*)
FROM Employee
GROUP BY department;
SELECT department, AVG(salary)
FROM Employee
GROUP BY department;
SELECT department, MAX(salary)
FROM Employee
GROUP BY department;
SELECT department, MIN(salary)
FROM Employee
GROUP BY department;
SELECT department, SUM(salary)
FROM Employee
GROUP BY department;
SELECT department, COUNT(*)
FROM Employee
GROUP BY department
HAVING COUNT(*) > 1;
SELECT department, AVG(salary)
FROM Employee
GROUP BY department
HAVING AVG(salary) > 40000;
Output
Employees with salary greater than 40000:
Amit 45000
Rahul 42000
Priya 50000
Kiran 60000
Employees from IT department:
Amit IT
Kiran IT
Employees whose name starts with P:
Priya Sales
Department-wise employee count:
Finance 1
HR 1
IT 2
Sales 1
Department-wise average salary:
Finance 42000
HR 38000
IT 52500
Sales 50000
Assi-4 — Query Tables Using UNION and INTERSECT
Code
CREATE TABLE Student_A (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
course VARCHAR(30)
);
CREATE TABLE Student_B (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
course VARCHAR(30)
);
INSERT INTO Student_A VALUES
(1, 'Amit', 'BSc CS'),
(2, 'Sneha', 'BSc CS'),
(3, 'Rahul', 'BCA'),
(4, 'Priya', 'BSc CS');
INSERT INTO Student_B VALUES
(3, 'Rahul', 'BCA'),
(4, 'Priya', 'BCA'),
(5, 'Kiran', 'BSc IT'),
(6, 'Neha', 'BSc IT');
SELECT * FROM Student_A;
SELECT * FROM Student_B;
SELECT student_id, student_name, course
FROM Student_A
UNION
SELECT student_id, student_name, course
FROM Student_B;
SELECT student_id, student_name, course
FROM Student_A
INTERSECT
SELECT student_id, student_name, course
FROM Student_B;
SELECT student_id, student_name, course
FROM Student_A
WHERE course = 'BCA'
UNION
SELECT student_id, student_name, course
FROM Student_B
WHERE course = 'BCA';
SELECT student_id, student_name, course
FROM Student_A
WHERE course = 'BCA'
INTERSECT
SELECT student_id, student_name, course
FROM Student_B
WHERE course = 'BCA';
Output
UNION Result:
student_id student_name course
1 Amit BSc CS
2 Sneha BSc CS
3 Rahul BCA
4 Priya BSc CS
4 Priya BCA
5 Kiran BSc IT
6 Neha BSc IT
INTERSECT Result:
student_id student_name course
3 Rahul BCA
Assi-5 — Nested Queries Using EXCEPT, EXISTS, NOT EXISTS, ALL and ANY
Code
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
department VARCHAR(20),
salary NUMERIC(10,2)
);
INSERT INTO Department VALUES
(1, 'IT'),
(2, 'HR'),
(3, 'Sales'),
(4, 'Finance');
INSERT INTO Employee VALUES
(101, 'Amit', 'IT', 45000),
(102, 'Sneha', 'HR', 38000),
(103, 'Rahul', 'Sales', 42000),
(104, 'Priya', 'Sales', 42000),
(105, 'Neha', 'HR', 40000);
SELECT department
FROM Department
EXCEPT
SELECT department
FROM Employee;
SELECT department_name
FROM Department D
WHERE EXISTS (
SELECT 1
FROM Employee E
WHERE E.department = D.dept_name
);
SELECT department_name
FROM Department D
WHERE NOT EXISTS (
SELECT 1
FROM Employee E
WHERE E.department = D.dept_name
);
SELECT emp_name, salary
FROM Employee
WHERE salary > ALL (
SELECT salary
FROM Employee
WHERE department = 'HR'
);
SELECT emp_name, salary
FROM Employee
WHERE salary > ANY (
SELECT salary
FROM Employee
WHERE department = 'Sales'
);
SELECT emp_name, salary
FROM Employee
WHERE salary > (
SELECT AVG(salary)
FROM Employee
);
Output
Departments without employees:
Finance
Employees earning more than all HR employees:
Amit 45000
Rahul 42000
Employees earning more than at least one Sales employee:
Amit 45000
Neha 40000
Employees earning more than average salary:
Amit 45000
Priya 42000
Assi-6 — Create and Use Views
Code
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Marks INT,
City VARCHAR(30)
);
INSERT INTO Student VALUES
(1, 'Rahul', 75, 'Shirpur'),
(2, 'Amit', 82, 'Dhule'),
(3, 'Priya', 88, 'Nashik'),
(4, 'Sneha', 70, 'Jalgaon'),
(5, 'Rohit', 90, 'Shirpur');
CREATE VIEW Student_View AS
SELECT RollNo, Name, City
FROM Student;
SELECT * FROM Student_View;
CREATE VIEW High_Marks AS
SELECT RollNo, Name, Marks
FROM Student
WHERE Marks > 75;
SELECT * FROM High_Marks;
CREATE VIEW Course_Average AS
SELECT AVG(Marks) AS Average_Marks
FROM Student;
SELECT * FROM Course_Average;
UPDATE Student_View
SET City = 'Pune'
WHERE RollNo = 1;
SELECT * FROM Student_View;
DROP VIEW Student_View;
SELECT * FROM Student;
Output
Student_View:
RollNo Name City
1 Rahul Shirpur
2 Amit Dhule
3 Priya Nashik
4 Sneha Jalgaon
5 Rohit Shirpur
High_Marks:
RollNo Name Marks
2 Amit 82
3 Priya 88
5 Rohit 90
Course Average:
Average_Marks
81.00
Assi-7 — Stored Procedures with IN, OUT and INOUT Parameters
Code
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Course VARCHAR(20),
Marks INT
);
INSERT INTO Student VALUES
(1, 'Rahul', 'BSc CS', 75),
(2, 'Amit', 'BSc CS', 82),
(3, 'Priya', 'BCA', 88);
CREATE OR REPLACE PROCEDURE display_students()
LANGUAGE plpgsql
AS $$
BEGIN
SELECT * FROM Student;
END;
$$;
CALL display_students();
CREATE OR REPLACE PROCEDURE display_student(IN rno INT)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT * FROM Student
WHERE RollNo = rno;
END;
$$;
CALL display_student(2);
CREATE OR REPLACE PROCEDURE student_count(OUT total INT)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO total
FROM Student;
END;
$$;
CALL student_count(NULL);
CREATE OR REPLACE PROCEDURE add_marks(INOUT marks INT)
LANGUAGE plpgsql
AS $$
BEGIN
marks := marks + 10;
END;
$$;
CALL add_marks(70);
CREATE OR REPLACE PROCEDURE double_number(INOUT num INT)
LANGUAGE plpgsql
AS $$
BEGIN
num := num * 2;
END;
$$;
CALL double_number(25);
Output
CALL display_student(2);
RollNo Name Course Marks
2 Amit BSc CS 82
CALL student_count(NULL);
total
3
CALL add_marks(70);
marks
80
CALL double_number(25);
num
50
Assi-8 — Stored Functions and Recursive Functions
Code
CREATE OR REPLACE FUNCTION welcome()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Welcome to PostgreSQL Stored Function';
END;
$$;
SELECT welcome();
CREATE OR REPLACE FUNCTION square_num(n INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
RETURN n * n;
END;
$$;
SELECT square_num(5);
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Marks INT
);
INSERT INTO Student VALUES
(1, 'Rahul', 75),
(2, 'Amit', 82),
(3, 'Priya', 88);
CREATE OR REPLACE FUNCTION get_marks(rno INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
m INT;
BEGIN
SELECT Marks INTO m
FROM Student
WHERE RollNo = rno;
RETURN m;
END;
$$;
SELECT get_marks(2);
CREATE OR REPLACE FUNCTION factorial(n INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
IF n = 0 OR n = 1 THEN
RETURN 1;
ELSE
RETURN n * factorial(n - 1);
END IF;
END;
$$;
SELECT factorial(5);
CREATE OR REPLACE FUNCTION sum_numbers(n INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
IF n = 0 THEN
RETURN 0;
ELSE
RETURN n + sum_numbers(n - 1);
END IF;
END;
$$;
SELECT sum_numbers(5);
Output
SELECT welcome();
NOTICE:
Welcome to PostgreSQL Stored Function
SELECT square_num(5);
square_num
25
SELECT get_marks(2);
get_marks
82
SELECT factorial(5);
factorial
120
SELECT sum_numbers(5);
sum_numbers
15
Comments
Post a Comment