Unit 1: SQL from schema design to advanced queries
Database Management Systems-I Laboratory notes · PTU syllabus (UGCC2513)
On this page
Unit summary
This lab moves from designing a database on paper (ER diagrams) to building and querying it in SQL — DDL, DML, constraints, transactions, aggregates, grouping, analytical and recursive queries, pattern matching and joins.
After this unit you can
- Design an ER diagram and reduce it to tables
- Create, alter and drop tables with constraints
- Insert, update, delete and query data, and control transactions
- Write aggregate, grouped, analytical, recursive and join queries
PTU syllabus topics
- ER diagram design and table reduction
- DDL commands (create/alter/drop/rename/truncate)
- DML commands (insert, select, filter, delete)
- queries with primary/foreign/unique/check/default constraints
- TCL (savepoints, rollback, commit)
- aggregate functions (min/max/avg/sum/count)
- GROUP BY/HAVING/ORDER BY queries
- analytical/hierarchical/recursive queries
- comparison and pattern-matching operators
- inner/natural/left/right/full outer joins
Topic 1
From ER diagram to tables
Draw the ER diagram (entities, attributes, keys, relationships with cardinality), then convert it: each entity becomes a table, 1:N relationships become foreign keys, and M:N relationships become a separate table.
Example
Mapping rules: the entity STUDENT(roll, name) becomes a table with roll as primary key. A 1:N relationship (DEPT has many STUDENTs) adds dept_id as a foreign key in STUDENT. An M:N relationship (STUDENT enrols in COURSE) becomes a new table ENROL(roll, course_id).
Topic 2
DDL with constraints
sqlCREATE TABLE dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(30) UNIQUE NOT NULL
);
CREATE TABLE emp (
emp_id INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
salary DECIMAL(10,2) CHECK (salary > 0),
city VARCHAR(20) DEFAULT 'Ludhiana',
dept_id INT REFERENCES dept(dept_id)
);
ALTER TABLE emp ADD email VARCHAR(50);
RENAME TABLE emp TO employee; -- MySQL syntax
TRUNCATE TABLE employee; -- removes all rows, keeps structureExample
Testing a constraint: with salary DECIMAL(10,2) CHECK (salary > 0), the statement INSERT INTO emp VALUES (1, 'Ana', -500, 10); is rejected. With a foreign key on dept_id, inserting dept_id 99 when no such department exists is also rejected.
Exam tip
DDL: CREATE, ALTER, DROP, TRUNCATE. These commit automatically.
Topic 3
DML and TCL
sqlINSERT INTO dept VALUES (10, 'Accounts'), (20, 'IT');
UPDATE employee SET salary = salary * 1.10 WHERE dept_id = 20;
DELETE FROM employee WHERE salary < 15000;
START TRANSACTION;
UPDATE employee SET salary = salary + 1000 WHERE emp_id = 1;
SAVEPOINT s1;
DELETE FROM employee WHERE emp_id = 2;
ROLLBACK TO s1; -- undo the delete only
COMMIT;Example
A transaction with rollback: START TRANSACTION; UPDATE emp SET salary = salary * 1.1; SAVEPOINT s1; DELETE FROM emp WHERE dept_id = 20; ROLLBACK TO s1; COMMIT; The salary increase is saved; the delete is undone.
Exam tip
DELETE can be rolled back and can have a WHERE clause. TRUNCATE removes all rows and cannot be rolled back.
Topic 4
Aggregates, grouping and pattern matching
sqlSELECT dept_id, COUNT(*), MIN(salary), MAX(salary), AVG(salary), SUM(salary)
FROM employee GROUP BY dept_id HAVING COUNT(*) > 2 ORDER BY AVG(salary) DESC;
SELECT name FROM employee WHERE name LIKE 'A%'; -- starts with A
SELECT name FROM employee WHERE salary BETWEEN 20000 AND 40000;
SELECT name FROM employee WHERE city IN ('Ludhiana', 'Jalandhar');Example
WHERE and HAVING: SELECT dept_id, AVG(salary) FROM emp WHERE salary > 10000 GROUP BY dept_id HAVING AVG(salary) > 30000; WHERE filters rows before grouping; HAVING filters groups after. WHERE name LIKE 'A%' finds names starting with A; '_a%' finds names whose second letter is a.
Topic 5
Analytical, recursive queries and joins
sqlSELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employee;
WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 5)
SELECT n FROM nums;
SELECT e.name, d.dept_name FROM employee e
LEFT JOIN dept d ON e.dept_id = d.dept_id;DELETE
Selected rows (with WHERE)
Yes, before COMMIT
TRUNCATE
All rows, keeps the table
No (in most DBMS)
DROP
The whole table and structure
No
Example
Second-highest salary: SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp); or, with a window function, select the row where DENSE_RANK() OVER (ORDER BY salary DESC) equals 2.
Key terms
- Savepoint
- A marker inside a transaction to roll back to
- CHECK constraint
- A rule each row's value must satisfy
- Window function
- A function like RANK() computed over a set of rows
- LEFT JOIN
- Returns all left-table rows with matching right rows or NULLs
Quick revision
- Define constraints when creating tables.
- ROLLBACK TO savepoint undoes part of a transaction.
- LIKE 'A%' starts with A; '%a' ends with a; '_' is one character.
- RANK() OVER for rankings; WITH RECURSIVE for recursion.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.Differentiate between DELETE, TRUNCATE and DROP.
- Q2.What is a savepoint?
- Q3.Write a query to find names starting with 'S'.
- Q4.What does a LEFT JOIN return?
- Q5.How do you add a column to an existing table?
Long-answer questions
- Q1.Create EMPLOYEE and DEPARTMENT tables with all constraints and insert sample data.
- Q2.Write queries using aggregate functions with GROUP BY, HAVING and ORDER BY.
- Q3.Write queries demonstrating inner, left, right and full outer joins.
- Q4.Demonstrate COMMIT, ROLLBACK and SAVEPOINT with an example transaction.
Stuck on this unit?
Message SBS on WhatsApp for help with Database Management Systems-I Laboratory, or to ask about studying BCA at Synetic.
