DBMS SQL Lab File | 12 Tested Copyable Practicals

DBMS and SQL lab file: 12 distinct, tested SQLite practicals covering tables, joins, grouping, constraints, views, indexes and transactions. Each block is a separate script: start with a fresh empty database, copy one block and run it. SQLite syntax differs from MySQL/PostgreSQL; follow your college's required dialect. These are general exercises, not an official lab manual. Read the DBMS Exam Notes and index and Semester 3 lab guide.

Lab Files index | Online Compiler (C/C++/Java/Python/JavaScript only). For these SQL scripts use a local SQLite shell or SQLite-capable editor; the TechVani compiler does not run SQL.

1. Create table and insert

Aim: Create a student table with a primary key.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, score INTEGER CHECK(score BETWEEN 0 AND 100));
INSERT INTO students VALUES (1, 'Ana', 82), (2, 'Ben', 74);
SELECT id, name, score FROM students ORDER BY id;

Sample input: the INSERT rows in this standalone script. Output: 1 | Ana | 82 2 | Ben | 74.

Viva: Why use a primary key? It uniquely identifies a row.

2. Selection and projection

Aim: Filter students and return only needed columns.

CREATE TABLE students(id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO students VALUES (1,'Ana',82),(2,'Ben',39),(3,'Cara',91);
SELECT name, score FROM students WHERE score >= 40 ORDER BY score DESC;

Sample input: the INSERT rows in this standalone script. Output: Cara | 91 Ana | 82.

Viva: What is projection? Selecting a subset of columns.

3. Update and delete

Aim: Edit one row and delete another.

CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT, stock INTEGER);
INSERT INTO items VALUES(1,'Pen',10),(2,'Book',5);
UPDATE items SET stock = stock - 2 WHERE id = 1;
DELETE FROM items WHERE id = 2;
SELECT name, stock FROM items ORDER BY id;

Sample input: the INSERT rows in this standalone script. Output: Pen | 8.

Viva: Why include WHERE in UPDATE? Without it, every row changes.

4. Foreign key and join

Aim: Join students with their department names.

PRAGMA foreign_keys = ON;
CREATE TABLE departments(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE students(id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER REFERENCES departments(id));
INSERT INTO departments VALUES(1,'CSE'),(2,'ECE');
INSERT INTO students VALUES(10,'Ana',1),(11,'Ben',2);
SELECT s.name, d.name FROM students AS s JOIN departments AS d ON s.dept_id = d.id ORDER BY s.id;

Sample input: the INSERT rows in this standalone script. Output: Ana | CSE Ben | ECE.

Viva: What does the foreign key protect? It blocks references to missing departments.

5. Grouping and having

Aim: Count students by department and filter groups.

CREATE TABLE students(name TEXT, dept TEXT);
INSERT INTO students VALUES('Ana','CSE'),('Ben','CSE'),('Cara','ECE');
SELECT dept, COUNT(*) AS total FROM students GROUP BY dept HAVING COUNT(*) >= 2 ORDER BY dept;

Sample input: the INSERT rows in this standalone script. Output: CSE | 2.

Viva: How does HAVING differ from WHERE? HAVING filters grouped results.

6. Aggregate and null

Aim: Compute a mean while ignoring missing marks.

CREATE TABLE scores(name TEXT, mark INTEGER);
INSERT INTO scores VALUES('Ana',80),('Ben',NULL),('Cara',90);
SELECT COUNT(*) AS rows, COUNT(mark) AS graded, ROUND(AVG(mark),1) AS average FROM scores;

Sample input: the INSERT rows in this standalone script. Output: 3 | 2 | 85.0.

Viva: Does AVG include NULL? No, it ignores NULL values.

7. Subquery

Aim: Find scores above the overall average.

CREATE TABLE scores(name TEXT, mark INTEGER);
INSERT INTO scores VALUES('Ana',80),('Ben',60),('Cara',100);
SELECT name, mark FROM scores WHERE mark > (SELECT AVG(mark) FROM scores) ORDER BY name;

Sample input: the INSERT rows in this standalone script. Output: Cara | 100.

Viva: What does the inner query return? One average value.

8. Unique constraint

Aim: Store distinct email addresses.

CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
INSERT INTO users VALUES(1,'ana@example.test'),(2,'ben@example.test');
SELECT COUNT(*) AS users, COUNT(DISTINCT email) AS emails FROM users;

Sample input: the INSERT rows in this standalone script. Output: 2 | 2.

Viva: What does UNIQUE prevent? Duplicate values in the constrained column.

9. View

Aim: Define a reusable query without copying rows.

CREATE TABLE students(name TEXT, score INTEGER);
INSERT INTO students VALUES('Ana',82),('Ben',31);
CREATE VIEW passing AS SELECT name FROM students WHERE score >= 40;
SELECT name FROM passing ORDER BY name;

Sample input: the INSERT rows in this standalone script. Output: Ana.

Viva: Does a standard view store a copy of its rows? No, it runs the underlying query.

10. Index and query plan

Aim: Create an index and inspect whether SQLite uses it.

CREATE TABLE items(id INTEGER PRIMARY KEY, code TEXT, price INTEGER);
INSERT INTO items VALUES(1,'A100',20),(2,'B200',35),(3,'C300',50);
CREATE INDEX idx_items_code ON items(code);
EXPLAIN QUERY PLAN SELECT price FROM items WHERE code='B200';

Sample input: the INSERT rows in this standalone script. Output: SEARCH items USING INDEX idx_items_code (code=?). The exact numeric prefix in EXPLAIN QUERY PLAN may vary by SQLite version; check for the named index.

Viva: Does an index guarantee faster every query? No, writes and some scans can cost more.

11. Transaction rollback

Aim: Undo an uncommitted inventory change.

CREATE TABLE stock(id INTEGER PRIMARY KEY, qty INTEGER);
INSERT INTO stock VALUES(1,10);
BEGIN TRANSACTION;
UPDATE stock SET qty = qty - 3 WHERE id = 1;
ROLLBACK;
SELECT qty FROM stock WHERE id=1;

Sample input: the INSERT rows in this standalone script. Output: 10.

Viva: What does ROLLBACK do? Undoes changes in the active transaction.

12. Common table expression

Aim: Use a CTE to show totals by department.

CREATE TABLE sales(dept TEXT, amount INTEGER);
INSERT INTO sales VALUES('CSE',20),('CSE',30),('ECE',15);
WITH totals AS (SELECT dept, SUM(amount) AS total FROM sales GROUP BY dept)
SELECT dept, total FROM totals ORDER BY dept;

Sample input: the INSERT rows in this standalone script. Output: CSE | 50 ECE | 15.

Viva: What is a CTE? A named temporary result for one statement.

Verification: All 12 scripts ran against SQLite 3.37.2 in a fresh in-memory database and matched the shown results; the index query plan was checked for the named index. SQL dialects differ, so adapt to your course database before submission. Semester hub.