Practice Questions — Introduction to SQL and Databases

-- DDL
CREATE TABLE t (id INT);
-- DML
SELECT * FROM t;
-- DCL
GRANT SELECT ON t TO 'user'@'localhost';
-- TCL
COMMIT;
-- DDL
DROP TABLE t;
-- DML
UPDATE t SET id = 1;
-- DCL
REVOKE SELECT ON t FROM 'user'@'localhost';
-- TCL
ROLLBACK;
-- DDL
ALTER TABLE t ADD COLUMN name VARCHAR(50);
-- DML
DELETE FROM t;

-- The employees table has 5 columns:
-- emp_id, name, department, salary, joining_date.
--
-- Suggested primary key: emp_id
-- Reason: emp_id is unique for each employee, never NULL,
-- and never changes. Names can repeat (two Aaravs),
-- joining_date can repeat (many people joined on Jan 1),
-- so they are not safe primary keys.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    salary DECIMAL(10,2),
    joining_date DATE
);

-- Step 1: START TRANSACTION (table is empty)
-- Step 2: INSERT 3 rows -> 3 rows in transaction buffer
-- Step 3: SAVEPOINT sp1 -> marker at 3 rows
-- Step 4: INSERT 2 rows -> 5 rows in buffer
-- Step 5: ROLLBACK TO sp1 -> back to 3 rows (the 2 are undone)
-- Step 6: COMMIT -> the 3 rows are saved permanently.
--
-- Final row count: 3

START TRANSACTION;
INSERT INTO employees VALUES (1, 'Aarav', 'IT', 60000, '2024-01-15');
INSERT INTO employees VALUES (2, 'Priya', 'HR', 55000, '2024-02-10');
INSERT INTO employees VALUES (3, 'Rohan', 'Sales', 48000, '2024-03-01');
SAVEPOINT sp1;
INSERT INTO employees VALUES (4, 'Ananya', 'IT', 72000, '2024-04-12');
INSERT INTO employees VALUES (5, 'Vikram', 'Marketing', 51000, '2024-05-20');
ROLLBACK TO sp1;
COMMIT;

SELECT COUNT(*) FROM employees;
-- Output: 3

-- (a) Android Notes app -> SQLite
--     Embedded, single file per device, zero configuration.
--
-- (b) Bank ledger -> PostgreSQL (or Oracle / MySQL InnoDB)
--     Strong ACID guarantees needed for money transfers.
--
-- (c) Real-time chat session cache -> Redis
--     In-memory key-value, microsecond reads, perfect for ephemeral sessions.
--
-- (d) JSON-heavy product catalog -> MongoDB (or PostgreSQL with JSONB)
--     Flexible schema for nested product attributes that vary per category.
--
-- (e) WordPress blog -> MySQL
--     Officially supported, free, every shared host runs it.

-- Three tables for a college library system:
--
-- 1) books
--    book_id (PK)  | title          | author        | total_copies
--    101           | Let Us C       | Yashwant K.   | 5
--    102           | DBMS Concepts  | Korth         | 3
--
-- 2) students
--    student_id (PK) | name          | course | year
--    2401            | Aarav Sharma  | BTech  | 2
--    2402            | Priya Iyer    | BTech  | 3
--
-- 3) borrowings
--    borrow_id (PK) | student_id (FK) | book_id (FK) | borrow_date | return_date
--    1              | 2401            | 101          | 2026-04-01  | NULL
--    2              | 2402            | 102          | 2026-04-05  | 2026-04-15
--
-- Relationship: borrowings is a junction table linking students and books.
-- A student can borrow many books over time, and a book can be borrowed
-- by many students -> many-to-many, resolved through borrowings.

-- (1) FALSE. SQL keywords are NOT case-sensitive.
--     SELECT, select, and SeLeCt all work the same.
--
-- (2) FALSE. MongoDB is a NoSQL document database.
--     It uses its own query language (BSON-based), not SQL.
--
-- (3) TRUE. TRUNCATE is DDL because it drops and recreates the table
--     internally — a structural change.
--
-- (4) TRUE. Oracle (1979) was the first commercial SQL database,
--     beating IBM's own System R to market.
--
-- (5) FALSE. Primary key values can NEVER be NULL.
--     They must be unique and non-null for every row.
--
-- Final score: statements 3 and 4 are TRUE, the rest are FALSE.