Visakha Institute of Engineering & Technology

88th Division, Narava,GVMC,

Visakhapatnam,Andhra Pradesh 530027,
___________________________________________________________________________
Department of Computer Science and Engineering
Workshop on – 'Hands-on Training in Database Design and SQL: A Practical Approach
Date: 5th–7th June 2025
Trainer: Dr.V Ranga Rao, Professor of Practice and Dean, Innovations & Student Projects

Create four tables

-- 1-Student Table
CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);

-- 2-Teacher Table
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(100) NOT NULL
);

-- 3-Subject Table
CREATE TABLE subject (
subject_id INT PRIMARY KEY,
subject_name VARCHAR(100) NOT NULL
);

-- 4-Marks Table
CREATE TABLE marks (
student_id INT,
teacher_id INT,
subject_id INT,
marks FLOAT CHECK (marks >= 0 AND marks <= 100),
PRIMARY KEY (student_id, teacher_id, subject_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
FOREIGN KEY (subject_id) REFERENCES subject(subject_id)
);

2- Data Manipulation Language (DML)

Insert values in tables

INSERT INTO student VALUES
(1, 'Aarav'), (2, 'Priya'), (3, 'Rahul'), (4, 'Isha'), (5, 'Karan'),
(6, 'Ananya'), (7, 'Dev'), (8, 'Meera'), (9, 'Yash'), (10, 'Sneha');

-- 2-Insert Teachers
INSERT INTO teacher VALUES
(1, 'Mr. Sharma'), (2, 'Ms. Rao'), (3, 'Mr. Singh'), (4, 'Ms. Patel'), (5, 'Mr. Khan'),

Page 1 of 10
(6, 'Ms. Das'), (7, 'Mr. Reddy'), (8, 'Ms. Nair'), (9, 'Mr. Gupta'), (10, 'Ms. Joshi');

-- 3-Insert Subjects
INSERT INTO subject VALUES
(1, 'Maths'), (2, 'Science'), (3, 'English'), (4, 'History'), (5, 'Geography'),
(6, 'Hindi'), (7, 'Computer'), (8, 'Biology'), (9, 'Physics'), (10, 'Chemistry');

-- 4-Insert Marks
INSERT INTO marks VALUES
(1, 1, 1, 85.0), (2, 2, 2, 78.5), (3, 3, 3, 92.0), (4, 4, 4, 67.0), (5, 5, 5, 74.5),
(6, 6, 6, 88.0), (7, 7, 7, 79.5), (8, 8, 8, 91.0), (9, 9, 9, 83.5), (10, 10, 10, 69.0);

PostgreSQL Command (DML) Reference

Section 1: PostgreSQL DML Commands

Analogy (Indian
Command Purpose Example using your tables
Context)
Retrieve data SELECT student_id,
Find names of all
SELECT from one or student_name FROM
students in the register
more tables student;
Add new INSERT INTO student
Add a new student to
INSERT records to a (student_id, student_name)
the class
table VALUES (11, 'Neha');
UPDATE marks SET
Modify existing
Update marks after re- marks = 89.0 WHERE
UPDATE records in a
evaluation student_id = 2 AND
table
subject_id = 2;
Remove records Remove a student who DELETE FROM student
DELETE
from a table transferred WHERE student_id = 11;
INSERT INTO student
(student_id, student_name)
VALUES (11, 'Neha') ON
Insert or update
MERGE / If Neha exists, update CONFLICT (student_id)
in one
UPSERT marks; if not, insert DO UPDATE SET
command
student_name =
EXCLUDED.student_nam
e;
Get rows UPDATE marks SET
Know what was
affected by marks = 90 WHERE
RETURNING inserted or updated
INSERT/UPDA student_id = 3
immediately
TE/DELETE RETURNING *;
Remove all
rows from a Clear the entire class TRUNCATE TABLE
TRUNCATE
table (faster register for a new year student;
than DELETE)
Section 2: Filtering & Conditions
Analogy (Indian
Clause Purpose Example
Context)

Page 2 of 10
 Filter rows Show marks only for
SELECT * FROM marks
WHERE based on a students who scored
WHERE marks > 80;
condition above 80
SELECT subject_id,
Filter groups AVG(marks) FROM marks
Show subjects where
HAVING after GROUP BY subject_id
average marks > 75
aggregation HAVING AVG(marks) >
75;
SELECT student_id,
Conditional marks, CASE WHEN
Label each student’s
CASE logic in marks >= 35 THEN 'Pass'
score as 'Pass' or 'Fail'
SELECT ELSE 'Fail' END AS result
FROM marks;
SELECT * FROM marks
Check if a student’s
Compare value WHERE marks > ANY
ALL / ANY / marks are above any
against (SELECT AVG(marks)
SOME teacher’s student
subquery results FROM marks GROUP BY
average
teacher_id);
SELECT
Return first
Display student name, COALESCE(student_name
COALESCE non-null value
or 'Unknown' if null , 'Unknown') AS name
from a list
FROM student;
Section 3: Sorting & Grouping
Analogy (Indian
Clause Purpose Example
Context)
Sorts rows Show all student
SELECT * FROM marks
ORDER BY based on marks in descending
ORDER BY marks DESC;
column(s) order
SELECT subject_id,
COUNT(*) AS
Groups rows Find total students
GROUP BY total_students FROM
for aggregation scored per subject
marks GROUP BY
subject_id;
SELECT teacher_id,
subject_id, SUM(marks)
Group data in Show total marks by
GROUPING FROM marks GROUP BY
multiple ways teacher and subject,
SETS GROUPING SETS
in one query and by teacher only
((teacher_id, subject_id),
(teacher_id));
SELECT teacher_id,
Hierarchical Show total marks by subject_id, SUM(marks)
ROLLUP grouping for teacher, subject, and FROM marks GROUP BY
subtotals grand total ROLLUP (teacher_id,
subject_id);
Section 4: Set-Based Filtering
Analogy (Indian
Clause Purpose Example
Context)

Page 3 of 10
 Checks if a Show marks for SELECT * FROM marks
IN value exists in a students with IDs 1, 2, WHERE student_id IN (1,
list or 3 2, 3);
Show marks for SELECT * FROM marks
Ensures a value
NOT IN students excluding IDs WHERE student_id NOT
is not in a list
4, 5 IN (4, 5);
Selects values SELECT * FROM marks
Show all marks
BETWEEN in a range WHERE marks
between 60 and 90
(inclusive) BETWEEN 60 AND 90;
SELECT * FROM student
Checks for Find students with no WHERE student_id NOT
IS NULL
NULL values recorded marks IN (SELECT student_id
FROM marks);
Section 5: Pattern Matching
Analogy (Indian
Clause Purpose Example
Context)
Pattern match SELECT * FROM student
Find students whose
LIKE using % (any WHERE student_name
names start with ‘A’
chars), _ (one) LIKE 'A%';
Excludes Find students whose SELECT * FROM student
NOT LIKE matches to a names do not start WHERE student_name
pattern with ‘R’ NOT LIKE 'R%';
Case- SELECT * FROM teacher
Match names like
ILIKE insensitive WHERE teacher_name
'raju', 'RAJU', etc.
version of LIKE ILIKE 'raju%';
Uses SQL- `SELECT * FROM student
Match name patterns
SIMILAR TO standard pattern WHERE student_name
like 'Arun' or 'Arvind'
syntax SIMILAR TO 'Ar(un
SELECT * FROM teacher
POSIX Regex Match names ending
~ WHERE teacher_name ~
(case-sensitive) with ‘esh’
'esh$';
POSIX Regex SELECT * FROM student
Match 'manoj',
~* (case- WHERE student_name ~*
'Manoj', etc.
insensitive) 'manoj';
Does not match Exclude teachers SELECT * FROM teacher
!~ regex pattern whose names end with WHERE teacher_name !~
(case-sensitive) 'lal' 'lal$';
Case- Exclude students SELECT * FROM student
!~* insensitive whose names contain WHERE student_name !~*
version of !~ 'ram' (any case) 'ram';
SELECT
Replace strings REGEXP_REPLACE(teac
REGEXP_REPL Replace 'Mr.' with
matching a her_name, '^Mr\.', 'Prof.')
ACE 'Prof.' in teacher names
regex pattern AS new_name FROM
teacher;

Page 4 of 10
Section 6:
Limiting Data
Analogy (Indian
Clause Purpose Example
Context)
Restrict number
SELECT * FROM student
LIMIT of rows Show top 5 students
LIMIT 5;
returned
Skip a number
Skip first 3 students SELECT * FROM student
OFFSET of rows before
and show next 5 OFFSET 3 LIMIT 5;
returning results
SELECT * FROM subject
ANSI-style
FETCH FIRST / Top 3 subjects ORDER BY subject_name
alternative to
ROWS ONLY alphabetically FETCH FIRST 3 ROWS
LIMIT
ONLY;
SELECT student_id, marks
Include rows
FROM marks ORDER BY
tied with the Top 3 scoring
WITH TIES marks DESC FETCH
last row based students, including ties
FIRST 3 ROWS WITH
on ORDER BY
TIES;
Alternative to SELECT TOP 5
LIMIT (less Show top 5 students student_id, marks FROM
TOP
common in by marks marks ORDER BY marks
PostgreSQL) DESC;
Section 7: Joins
Analogy (Indian
Join Type Purpose Example
Context)
SELECT s.student_name,
sub.subject_name FROM
Returns student s INNER JOIN
List of students with
matching rows marks m ON s.student_id =
INNER JOIN subjects where they
from both m.student_id INNER JOIN
have marks
tables subject sub ON
m.subject_id =
sub.subject_id;
SELECT s.student_name,
t.teacher_name FROM
All rows from
student s LEFT JOIN
left table + All students and their
LEFT JOIN marks m ON s.student_id =
matching right teachers (if any)
m.student_id LEFT JOIN
table rows
teacher t ON m.teacher_id
= t.teacher_id;
SELECT
sub.subject_name,
All rows from
All subjects and s.student_name FROM
right table +
RIGHT JOIN students who took subject sub RIGHT JOIN
matching left
them marks m ON
table rows
sub.subject_id =
m.subject_id RIGHT JOIN

Page 5 of 10
 student s ON m.student_id
= s.student_id;

SELECT s.student_name,
t.teacher_name FROM
All rows from student s FULL OUTER
All students and all
FULL OUTER both tables, JOIN marks m ON
teachers, even if not
JOIN with NULLs for s.student_id = m.student_id
linked
non-matches FULL OUTER JOIN
teacher t ON m.teacher_id
= t.teacher_id;
Cartesian SELECT s.student_name,
product: every All possible student- t.teacher_name FROM
CROSS JOIN
row of A with teacher pairs student s CROSS JOIN
every row of B teacher t;
SELECT t1.teacher_name
AS mentor,
t2.teacher_name AS
Join table with Teachers mentoring
SELF JOIN mentee FROM teacher t1
itself other teachers
Bloom JOIN teacher t2 ON
t1.teacher_id !=
t2.teacher_id;
Join tables Match students and
SELECT student_name,
NATURAL based on marks based on
marks FROM student
JOIN columns with common column
NATURAL JOIN marks;
the same name names
Section 8: Subqueries & Advanced Clauses
Analogy (Indian
Clause Purpose Example
Context)
SELECT student_name
FROM student WHERE
Nested query student_id IN (SELECT
Find students scoring
SUBQUERIES inside another student_id FROM marks
above average marks
query WHERE marks >
(SELECT AVG(marks)
FROM marks));
SELECT teacher_name
Checks if FROM teacher t WHERE
Find teachers who
subquery EXISTS (SELECT 1
EXISTS have awarded any
returns any FROM marks m WHERE
marks
rows m.teacher_id =
t.teacher_id);

Page 6 of 10
 SELECT subject_name
FROM subject sub
Ensures
Find subjects with no WHERE NOT EXISTS
NOT EXISTS subquery
marks awarded yet (SELECT 1 FROM marks
returns no rows
m WHERE m.subject_id =
sub.subject_id);
SELECT DISTINCT
Removes
DISTINCT List unique subjects subject_name FROM
duplicate rows
subject;
SELECT DISTINCT ON
(m.subject_id)
m.subject_id,
PostgreSQL-
List first student per s.student_name, m.marks
specific; first
DISTINCT ON subject by marks FROM marks m JOIN
row for each
descending student s ON m.student_id
unique value
= s.student_id ORDER BY
m.subject_id, m.marks
DESC;
WITH avg_marks AS
(SELECT subject_id,
AVG(marks) AS
avg_score FROM marks
Temporary GROUP BY subject_id)
Find subjects and
named result SELECT
average marks, then
WITH (CTEs) sets for sub.subject_name,
list subjects with avg >
complex avg_score FROM
50
queries avg_marks am JOIN
subject sub ON
am.subject_id =
sub.subject_id WHERE
avg_score > 50;
SELECT s.student_name,
m.top_marks FROM
Allows student s CROSS JOIN
subqueries to LATERAL (SELECT
Find top 3 marks per
LATERAL reference marks AS top_marks
student
columns from FROM marks m WHERE
preceding tables m.student_id = s.student_id
ORDER BY marks DESC
LIMIT 3) m;
Section 9: Aggregate Functions
Analogy (Indian
Function Purpose Example
Context)
Counts rows or SELECT COUNT(*)
COUNT() Count total students
non-null values FROM student;
Calculates sum
Total marks awarded SELECT SUM(marks)
SUM() of numeric
by all teachers FROM marks;
values
Finds average Average marks scored SELECT AVG(marks)
AVG()
value by students FROM marks;

Page 7 of 10
 SELECT MAX(marks)
Finds maximum Highest marks scored
MAX() FROM marks WHERE
value in a subject
subject_id = 101;
SELECT MIN(marks)
Finds minimum Lowest marks scored
MIN() FROM marks WHERE
value in a subject
subject_id = 101;
SELECT subject_id,
STRING_AGG(student_na
Concatenates List all student names
me, ', ') FROM marks m
STRING_AGG() strings with per subject separated
JOIN student s ON
delimiter by commas
m.student_id = s.student_id
GROUP BY subject_id;
SELECT student_id,
Aggregates
Get array of marks per ARRAY_AGG(marks)
ARRAY_AGG() values into an
student FROM marks GROUP BY
array
student_id;
SELECT
percentile_cont(0.5)
Median value
WITHIN GROUP
MEDIAN() using Median marks in a
(ORDER BY marks) AS
(percentile_cont) percentile_cont( subject
median_marks FROM
)
marks WHERE subject_id
= 101;
Calculates SELECT STDDEV(marks)
standard Measure variation in AS marks_variation
STDDEV()
deviation of marks for a subject FROM marks WHERE
values subject_id = 101;
Section 10: Window Functions
Function / Analogy (Indian
Purpose Example
Clause Context)
SELECT student_id,
marks, RANK() OVER
Defines
Ranking students (ORDER BY marks
OVER window frame
within their class DESC) AS rank FROM
for functions
marks WHERE subject_id
= 101;
SELECT teacher_id,
student_id, marks,
Divides rows
SUM(marks) OVER
into groups for Calculate total marks
PARTITION BY (PARTITION BY
window per teacher
teacher_id) AS
calculations
total_teacher_marks
FROM marks;
SELECT student_id,
subject_id,
Assigns unique Number students ROW_NUMBER() OVER
ROW_NUMBE
row numbers sequentially within (PARTITION BY
R()
per partition each subject subject_id ORDER BY
marks DESC) AS rn
FROM marks;

Page 8 of 10
 SELECT student_id,
marks, RANK() OVER
Assigns rank Rank students with
(ORDER BY marks
RANK() with gaps for same marks sharing
DESC) AS rank FROM
ties same rank
marks WHERE subject_id
= 101;
SELECT student_id,
marks, DENSE_RANK()
Rank students in class
DENSE_RANK( Assigns rank OVER (ORDER BY marks
without skipping ranks
) without gaps DESC) AS dense_rank
for ties
FROM marks WHERE
subject_id = 101;
SELECT student_id,
marks, NTILE(4) OVER
Divide students into
Divides rows (ORDER BY marks
NTILE(n) quartiles based on
into n buckets DESC) AS quartile FROM
marks
marks WHERE subject_id
= 101;
SELECT student_id,
Compare student’s marks, LAG(marks) OVER
Access value
current marks with (ORDER BY student_id)
LAG() from previous
previous student’s AS prev_student_marks
row
marks FROM marks WHERE
subject_id = 101;
SELECT student_id,
marks, LEAD(marks)
Compare student’s OVER (ORDER BY
Access value
LEAD() marks with next student_id) AS
from next row
student’s marks next_student_marks
FROM marks WHERE
subject_id = 101;
SELECT student_id,
marks,
Returns first FIRST_VALUE(marks)
FIRST_VALUE( Get top scorer’s marks
value in OVER (PARTITION BY
) in each subject
window subject_id ORDER BY
marks DESC) AS
top_marks FROM marks;
SELECT student_id,
marks,
LAST_VALUE(marks)
OVER (PARTITION BY
subject_id ORDER BY
Returns last
marks ASC ROWS
LAST_VALUE( value in Get lowest scorer’s
BETWEEN
) window (use marks in each subject
UNBOUNDED
frame specs)
PRECEDING AND
UNBOUNDED
FOLLOWING) AS
lowest_marks FROM
marks;

Page 9 of 10
 SELECT student_id,
Calculates marks, CUME_DIST()
Find percentage of
cumulative OVER (PARTITION BY
CUME_DIST() students with marks
distribution of subject_id ORDER BY
below each student
values marks) AS cumulative_dist
FROM marks;
Section 11: Set Operations
Analogy (Indian
Operation Purpose Example
Context)
Combine results
SELECT student_id AS id
from two Get list of all unique
FROM student UNION
UNION queries, IDs from students and
SELECT teacher_id AS id
removing teachers
FROM teacher;
duplicates
Combine all
Get full list of IDs SELECT student_id AS id
results from
(including duplicates if FROM student UNION
UNION ALL two queries,
same person is both ALL SELECT teacher_id
including
student & teacher) AS id FROM teacher;
duplicates
Get only Find IDs common in SELECT student_id
common rows both students and FROM student
INTERSECT
between two marks (i.e., those who INTERSECT SELECT
queries have marks) student_id FROM marks;
Get rows from SELECT student_id
Find students who
first query that FROM student EXCEPT
EXCEPT don’t have any marks
are not in SELECT student_id
recorded
second FROM marks;

Page 10 of 10