Unit 2: Relational Query Language — DDL & DML

Why This Chapter Pays Your Salary

SQL is the single most in-demand technical skill in Indian IT. TCS NQT, Infosys InfyTQ, Wipro NLTH, Cognizant GenC — every campus placement test has a SQL section. Every backend developer, data analyst, QA engineer, and DBA writes SQL daily. This chapter teaches you the two pillars: DDL (building the structure) and DML (working with the data).

📦 Schema Setup — Hospital Management System

All examples in this chapter use a Hospital Management System domain. Here's the complete schema we'll build step by step:

Domain: Hospital Management
Tables we will create in this chapter:
  departments   — Hospital departments (Cardiology, Orthopedics, etc.)
  doctors       — Doctor profiles with specialization
  patients      — Patient records with Aadhaar linkage
  appointments  — OPD appointments (patient + doctor + date)
  prescriptions — Medicines prescribed per appointment
  medicines     — Medicine catalog with stock
  bills         — Patient billing records

Learning Outcomes — Bloom's Taxonomy

Concept Explanations

Part A — Data Definition Language (DDL)

3.1 DDL Overview — Building the Blueprint

3.2 Data Types — Choosing the Right Container

3.3 CREATE TABLE — Building Tables

SQL — Oracle & PostgreSQL
-- ═══════════════════════════════════════
-- Example 1: Hospital Departments Table
-- Business Context: Master table for hospital departments
-- Industry: Every hospital HIS (AIIMS, Apollo, Fortis)
-- ═══════════════════════════════════════

CREATE TABLE departments (
    dept_id     NUMBER(5)      PRIMARY KEY,
    dept_name   VARCHAR2(50)   NOT NULL UNIQUE,
    building    VARCHAR2(30)   DEFAULT 'Main Block',
    floor_no    NUMBER(2)      CHECK (floor_no BETWEEN 0 AND 15),
    is_active   NUMBER(1)      DEFAULT 1 CHECK (is_active IN (0, 1))
);

-- PostgreSQL version differences:
-- NUMBER(5) → INTEGER
-- VARCHAR2   → VARCHAR
-- NUMBER(1)  → BOOLEAN DEFAULT TRUE

SQL
-- ═══════════════════════════════════════
-- Example 2: Doctors Table (with FK)
-- Business Context: Doctor profiles linked to departments
-- ═══════════════════════════════════════

CREATE TABLE doctors (
    doctor_id       NUMBER(10)    PRIMARY KEY,
    doctor_name     VARCHAR2(100) NOT NULL,
    specialization  VARCHAR2(50)  NOT NULL,
    phone           VARCHAR2(10)  UNIQUE,
    email           VARCHAR2(100) UNIQUE,
    dept_id         NUMBER(5)     NOT NULL,
    salary          NUMBER(10,2)  CHECK (salary > 0),
    joining_date    DATE           DEFAULT SYSDATE,
    CONSTRAINT fk_doc_dept FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
        ON DELETE SET NULL
);

SQL
-- ═══════════════════════════════════════
-- Example 3: Patients Table
-- Business Context: Patient master with Aadhaar linkage
-- ═══════════════════════════════════════

CREATE TABLE patients (
    patient_id    NUMBER(10)    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_name  VARCHAR2(100) NOT NULL,
    aadhaar_no    CHAR(12)      UNIQUE,
    phone         VARCHAR2(10)  NOT NULL,
    dob           DATE,
    gender        CHAR(1)       CHECK (gender IN ('M', 'F', 'O')),
    blood_group   VARCHAR2(5),
    address       VARCHAR2(300),
    reg_date      DATE           DEFAULT SYSDATE
);

-- INSERT sample data
INSERT INTO departments VALUES (1, 'Cardiology', 'Main Block', 3, 1);
INSERT INTO departments VALUES (2, 'Orthopedics', 'Main Block', 2, 1);
INSERT INTO departments VALUES (3, 'Neurology', 'East Wing', 4, 1);
INSERT INTO departments VALUES (4, 'Pediatrics', 'West Wing', 1, 1);
INSERT INTO departments VALUES (5, 'General Medicine', 'Main Block', 1, 1);

INSERT INTO doctors VALUES (101, 'Dr. Anil Sharma', 'Cardiologist', '9876543210', 'anil@hospital.com', 1, 250000, DATE '2018-06-15');
INSERT INTO doctors VALUES (102, 'Dr. Priya Patel', 'Orthopedic Surgeon', '9876543211', 'priya@hospital.com', 2, 220000, DATE '2019-03-01');
INSERT INTO doctors VALUES (103, 'Dr. Rajesh Kumar', 'Neurologist', '9876543212', 'rajesh@hospital.com', 3, 280000, DATE '2017-08-20');
INSERT INTO doctors VALUES (104, 'Dr. Sneha Reddy', 'Pediatrician', '9876543213', 'sneha@hospital.com', 4, 180000, DATE '2021-01-10');
INSERT INTO doctors VALUES (105, 'Dr. Amit Gupta', 'General Physician', '9876543214', 'amit@hospital.com', 5, 160000, DATE '2022-07-01');

COMMIT;

3.4 Integrity Constraints — The Guard Rails

Foreign Key — ON DELETE Actions

SQL
-- ON DELETE CASCADE: Delete parent → auto-delete all children
CONSTRAINT fk_appt_patient FOREIGN KEY (patient_id)
    REFERENCES patients(patient_id)
    ON DELETE CASCADE;  -- Delete patient → all their appointments deleted

-- ON DELETE SET NULL: Delete parent → set FK to NULL in children
CONSTRAINT fk_doc_dept FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON DELETE SET NULL;  -- Delete dept → doctor.dept_id becomes NULL

-- ON DELETE RESTRICT (default): Refuse to delete parent if children exist
-- ORA-02292: integrity constraint violated - child record found

3.5 ALTER TABLE, DROP, TRUNCATE

SQL — ALTER TABLE
-- ═══════════════════════════════════════
-- Example 4: Modifying table structure
-- Business Context: Hospital adds new fields as requirements evolve
-- ═══════════════════════════════════════

-- ADD column
ALTER TABLE patients ADD (
    emergency_contact  VARCHAR2(10),
    insurance_id       VARCHAR2(20)
);

-- MODIFY column (change data type or size)
ALTER TABLE patients MODIFY (phone VARCHAR2(15));  -- Oracle
-- ALTER TABLE patients ALTER COLUMN phone TYPE VARCHAR(15);  -- PostgreSQL

-- DROP column
ALTER TABLE patients DROP COLUMN insurance_id;

-- ADD constraint to existing table
ALTER TABLE doctors ADD CONSTRAINT chk_salary_min
    CHECK (salary >= 50000);

-- DROP constraint
ALTER TABLE doctors DROP CONSTRAINT chk_salary_min;

-- RENAME column
ALTER TABLE patients RENAME COLUMN phone TO mobile_no;

-- RENAME table
ALTER TABLE patients RENAME TO patient_records;  -- Oracle
-- ALTER TABLE patients RENAME TO patient_records;  -- Same in PG

DROP vs TRUNCATE vs DELETE

Part B — Data Manipulation Language (DML)

3.6 INSERT, UPDATE, DELETE

SQL — INSERT
-- ═══════════════════════════════════════
-- Example 5: Inserting patient records
-- Business Context: New patient registration at OPD counter
-- ═══════════════════════════════════════

-- Single row INSERT (explicit columns — BEST PRACTICE)
INSERT INTO patients (patient_name, aadhaar_no, phone, dob, gender, blood_group, address)
VALUES ('Rahul Verma', '234567890123', '9811234567', 
        DATE '1990-05-15', 'M', 'B+', '45 MG Road, Pune');

-- Multiple rows (PostgreSQL syntax — also works in Oracle 23c+)
INSERT INTO patients (patient_name, phone, gender, blood_group) VALUES
    ('Priya Singh',    '9822345678', 'F', 'A+'),
    ('Amit Joshi',     '9833456789', 'M', 'O+'),
    ('Sneha Kulkarni', '9844567890', 'F', 'AB-'),
    ('Rajesh Gupta',   '9855678901', 'M', 'O-');

-- INSERT from SELECT (copy data from another table)
CREATE TABLE senior_doctors AS
    SELECT * FROM doctors WHERE salary > 200000;

-- Oracle: INSERT ALL (multi-table insert)
INSERT ALL
    INTO cardio_patients (patient_id, name) VALUES (id, name)
    INTO patient_audit (patient_id, action) VALUES (id, 'REGISTERED')
SELECT patient_id AS id, patient_name AS name FROM patients
WHERE reg_date = TRUNC(SYSDATE);

SQL — UPDATE
-- ═══════════════════════════════════════
-- Example 6: Updating records
-- Business Context: Salary revision, patient info updates
-- ═══════════════════════════════════════

-- Single column update
UPDATE doctors SET salary = 270000 WHERE doctor_id = 102;

-- Multiple column update
UPDATE patients 
SET phone = '9999888877', address = '12 Baner Road, Pune'
WHERE patient_id = 1;

-- UPDATE with subquery (give 10% raise to all Cardiology doctors)
UPDATE doctors 
SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Cardiology');

-- Correlated UPDATE (set bonus based on department average)
UPDATE doctors d
SET salary = salary + (SELECT AVG(salary) * 0.05 FROM doctors 
                        WHERE dept_id = d.dept_id);

SQL — DELETE
-- ═══════════════════════════════════════
-- Example 7: Deleting records
-- Business Context: Removing cancelled appointments, old records
-- ═══════════════════════════════════════

-- DELETE specific rows
DELETE FROM appointments WHERE status = 'CANCELLED';

-- DELETE with subquery
DELETE FROM appointments 
WHERE doctor_id IN (
    SELECT doctor_id FROM doctors WHERE dept_id = 5
);

-- DELETE all rows (table structure remains)
DELETE FROM appointments;  -- Can be rolled back!

3.7 Transaction Control — COMMIT, ROLLBACK, SAVEPOINT

SQL — Transaction Control
-- ═══════════════════════════════════════
-- Example 8: Hospital billing transaction
-- Business Context: Generate bill + update patient status atomically
-- ═══════════════════════════════════════

-- Start transaction (implicit in Oracle; explicit BEGIN in PG)
INSERT INTO bills (bill_id, patient_id, amount, bill_date, status)
VALUES (5001, 1, 15000, SYSDATE, 'GENERATED');

SAVEPOINT after_bill;  -- Bookmark this point

UPDATE patients SET address = 'Updated Address' WHERE patient_id = 1;

-- Oops, wrong update! Rollback to savepoint (NOT everything)
ROLLBACK TO after_bill;  -- Bill INSERT preserved, address UPDATE undone

UPDATE appointments SET status = 'COMPLETED' WHERE appointment_id = 301;

COMMIT;  -- Both the bill INSERT and appointment UPDATE are now permanent

3.8 Data Control Language — GRANT & REVOKE

SQL — DCL
-- ═══════════════════════════════════════
-- Example 9: Hospital access control
-- Business Context: Different staff have different data access
-- Industry: RBI mandates role-based DB access for banks
-- ═══════════════════════════════════════

-- Create roles
CREATE ROLE doctor_role;
CREATE ROLE billing_role;
CREATE ROLE admin_role;

-- Grant object privileges to roles
GRANT SELECT ON patients TO doctor_role;
GRANT SELECT, UPDATE ON appointments TO doctor_role;
GRANT SELECT, INSERT ON prescriptions TO doctor_role;

GRANT SELECT, INSERT, UPDATE ON bills TO billing_role;
GRANT SELECT ON patients (patient_id, patient_name) TO billing_role;
-- Billing staff sees name/ID but NOT Aadhaar, blood group, etc.

GRANT ALL PRIVILEGES ON patients TO admin_role;

-- Assign roles to users
GRANT doctor_role TO dr_anil;
GRANT billing_role TO cashier_ravi;

-- WITH GRANT OPTION (user can further grant to others)
GRANT SELECT ON patients TO dr_anil WITH GRANT OPTION;

-- Revoke privileges
REVOKE INSERT ON prescriptions FROM doctor_role;

Part C — SQL Basic Operations (SELECT Mastery)

3.9 SELECT Statement — The Most Important SQL Command

Execution Order
WRITTEN ORDER:          EXECUTION ORDER:
1. SELECT               1. FROM       ← Which tables?
2. FROM                 2. WHERE      ← Filter rows
3. WHERE                3. GROUP BY   ← Group remaining rows
4. GROUP BY             4. HAVING     ← Filter groups
5. HAVING               5. SELECT     ← Choose columns
6. ORDER BY             6. ORDER BY   ← Sort output

-- WHY THIS MATTERS: You can't use a column alias in WHERE
-- because WHERE executes BEFORE SELECT!
-- ❌ WRONG: SELECT salary * 12 AS annual FROM doctors WHERE annual > 200000
-- ✅ RIGHT: SELECT salary * 12 AS annual FROM doctors WHERE salary * 12 > 200000

SQL — SELECT Operations
-- ═══════════════════════════════════════
-- Example 10: WHERE clause operators
-- ═══════════════════════════════════════

-- Comparison operators
SELECT doctor_name, salary FROM doctors WHERE salary > 200000;
SELECT * FROM doctors WHERE salary BETWEEN 150000 AND 250000;
SELECT * FROM doctors WHERE dept_id IN (1, 3, 5);

-- Pattern matching
SELECT * FROM doctors WHERE doctor_name LIKE 'Dr. A%';  -- starts with 'Dr. A'
SELECT * FROM patients WHERE phone LIKE '98%';        -- phone starts with 98
SELECT * FROM patients WHERE patient_name LIKE '_a%'; -- 2nd char is 'a'

-- NULL handling
SELECT * FROM patients WHERE aadhaar_no IS NULL;
SELECT * FROM patients WHERE aadhaar_no IS NOT NULL;
-- ⚠️ NEVER use = NULL or != NULL — always use IS NULL / IS NOT NULL

-- Sorting
SELECT doctor_name, salary FROM doctors 
ORDER BY salary DESC, doctor_name ASC;

-- NULLS FIRST / LAST (Oracle and PostgreSQL)
SELECT * FROM patients ORDER BY aadhaar_no NULLS LAST;

-- DISTINCT
SELECT DISTINCT dept_id FROM doctors;
SELECT DISTINCT specialization, dept_id FROM doctors;

-- FETCH FIRST (Oracle 12c+ / SQL Standard)
SELECT * FROM doctors ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

-- PostgreSQL: LIMIT
-- SELECT * FROM doctors ORDER BY salary DESC LIMIT 3;

3.10 SQL Functions — String, Numeric, Date, Conversion

String Functions

SQL — String Functions
-- ═══════════════════════════════════════
-- Example 11: Text processing in queries
-- ═══════════════════════════════════════

SELECT 
    UPPER(doctor_name)                            AS name_upper,
    LOWER(email)                                  AS email_lower,
    INITCAP('RAHUL SHARMA')                       AS proper_case,   -- Rahul Sharma
    LENGTH(doctor_name)                            AS name_len,
    SUBSTR(phone, 1, 3)                            AS area_code,     -- First 3 digits
    INSTR(email, '@')                               AS at_position,   -- Position of @
    CONCAT('Dr. ', doctor_name)                     AS formal_name,
    REPLACE(phone, '987', '***')                   AS masked_phone,
    TRIM('  Rahul  ')                               AS trimmed,       -- 'Rahul'
    LPAD(dept_id, 5, '0')                          AS padded_id      -- '00001'
FROM doctors;

Numeric Functions

SQL — Numeric Functions
SELECT
    ROUND(15678.456, 2)    AS rounded,     -- 15678.46
    TRUNC(15678.456, 2)    AS truncated,   -- 15678.45
    MOD(17, 5)              AS remainder,   -- 2
    ABS(-250)               AS absolute,    -- 250
    CEIL(4.2)               AS ceiling,     -- 5
    FLOOR(4.8)              AS floor_val,   -- 4
    POWER(2, 10)            AS power_val,   -- 1024
    SQRT(144)               AS sqrt_val     -- 12
FROM dual;  -- Oracle uses DUAL; PostgreSQL: just SELECT without FROM

Date Functions

SQL — Date Functions
-- ═══════════════════════════════════════
-- Example 12: Date calculations in hospital context
-- ═══════════════════════════════════════

SELECT
    SYSDATE                                       AS today,          -- Oracle
    -- CURRENT_DATE                                  AS today,          -- PostgreSQL
    ADD_MONTHS(SYSDATE, 6)                        AS follow_up,      -- 6 months later
    MONTHS_BETWEEN(SYSDATE, DATE '2024-01-15')  AS months_since,
    NEXT_DAY(SYSDATE, 'MONDAY')                   AS next_monday,
    LAST_DAY(SYSDATE)                              AS month_end
FROM dual;

-- Calculate patient age
SELECT patient_name,
    TRUNC(MONTHS_BETWEEN(SYSDATE, dob) / 12) AS age
FROM patients
WHERE dob IS NOT NULL;

-- Format dates
SELECT 
    TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI') AS formatted_date,
    TO_DATE('15-Jun-2025', 'DD-Mon-YYYY')   AS parsed_date,
    EXTRACT(YEAR FROM SYSDATE)                AS current_year,
    EXTRACT(MONTH FROM SYSDATE)               AS current_month
FROM dual;

Conversion & NULL Functions

SQL — Conversion & NULL Handling
-- ═══════════════════════════════════════
-- Example 13: Handling NULLs and type conversion
-- ═══════════════════════════════════════

SELECT
    -- NVL: replace NULL with default value (Oracle)
    NVL(aadhaar_no, 'NOT PROVIDED')          AS aadhaar_display,
    -- COALESCE: ANSI standard (works in both Oracle & PG)
    COALESCE(aadhaar_no, phone, 'NO ID')   AS primary_id,
    -- NVL2: if not null → value1; if null → value2 (Oracle)
    NVL2(aadhaar_no, 'Verified', 'Pending') AS kyc_status,
    -- NULLIF: returns NULL if two values are equal
    NULLIF(blood_group, 'Unknown')           AS cleaned_bg
FROM patients;

-- CASE expression (works everywhere — the SQL "if-else")
SELECT doctor_name, salary,
    CASE
        WHEN salary > 250000 THEN 'Senior'
        WHEN salary > 180000 THEN 'Mid-Level'
        ELSE 'Junior'
    END AS seniority_level
FROM doctors;

-- Oracle DECODE (legacy — use CASE instead)
SELECT DECODE(gender, 'M', 'Male', 'F', 'Female', 'O', 'Other') AS gender_text
FROM patients;

3.11 Window Functions — The Interview Darling

SQL — Window Functions
-- ═══════════════════════════════════════
-- Example 14: Ranking and analytics without GROUP BY
-- Business Context: Doctor salary ranking per department
-- ═══════════════════════════════════════

SELECT 
    doctor_name, dept_id, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC)                  AS overall_rank,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
    RANK()       OVER (ORDER BY salary DESC)                  AS rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY salary DESC)                  AS dense_rank_val
FROM doctors;

-- Output (sample):
-- doctor_name     | dept | salary  | overall | dept_rank | rank | dense
-- Dr. Rajesh Kumar| 3    | 280000  | 1       | 1         | 1    | 1
-- Dr. Anil Sharma | 1    | 250000  | 2       | 1         | 2    | 2
-- Dr. Priya Patel | 2    | 220000  | 3       | 1         | 3    | 3
-- Dr. Sneha Reddy | 4    | 180000  | 4       | 1         | 4    | 4
-- Dr. Amit Gupta  | 5    | 160000  | 5       | 1         | 5    | 5

SQL — LAG and LEAD
-- ═══════════════════════════════════════
-- Example 15: Compare with previous/next row
-- Business Context: Salary comparison with previous hire
-- ═══════════════════════════════════════

SELECT 
    doctor_name, joining_date, salary,
    LAG(salary) OVER (ORDER BY joining_date)  AS prev_salary,
    LEAD(salary) OVER (ORDER BY joining_date) AS next_salary,
    salary - LAG(salary) OVER (ORDER BY joining_date) AS salary_diff
FROM doctors;

-- LAG(col, n, default) — look N rows back (default 1)
-- LEAD(col, n, default) — look N rows forward (default 1)

Industry Problems

SQL
-- Complete schema (abbreviated — referencing tables from Section 3)
CREATE TABLE appointments (
    appt_id      NUMBER(10)    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id   NUMBER(10)    NOT NULL REFERENCES patients(patient_id),
    doctor_id    NUMBER(10)    NOT NULL REFERENCES doctors(doctor_id),
    appt_date    DATE           NOT NULL,
    appt_time    VARCHAR2(5),  -- HH:MM format
    status       VARCHAR2(15)  DEFAULT 'SCHEDULED'
                 CHECK (status IN ('SCHEDULED','COMPLETED','CANCELLED','NO_SHOW')),
    fee          NUMBER(8,2)   CHECK (fee >= 0),
    CONSTRAINT uq_appt_slot UNIQUE (doctor_id, appt_date, appt_time)
);

-- Constraint violation tests:
-- 1. PK violation:
--    INSERT INTO departments VALUES (1, 'Test', 'X', 1, 1);
--    → ORA-00001: unique constraint violated (dept_id=1 exists)

-- 2. FK violation:
--    INSERT INTO doctors VALUES (110,'Dr.X','GP','9999','x@h.com',99,100000,SYSDATE);
--    → ORA-02291: integrity constraint violated (dept_id=99 doesn't exist)

-- 3. CHECK violation:
--    UPDATE doctors SET salary = -5000 WHERE doctor_id = 101;
--    → ORA-02290: check constraint violated (salary > 0)

-- 4. NOT NULL violation:
--    INSERT INTO patients (patient_name, phone) VALUES (NULL, '9999');
--    → ORA-01400: cannot insert NULL into (patient_name NOT NULL)

-- 5. UNIQUE violation:
--    INSERT INTO doctors VALUES (110,'Dr.Y','GP','9876543210','y@h.com',1,100000,SYSDATE);
--    → ORA-00001: unique constraint violated (phone already used by Dr. Anil)

SQL — Transaction with SAVEPOINT
-- Step 1: Insert bill
INSERT INTO bills (bill_id, patient_id, amount, bill_date, status)
VALUES (6001, 1, 8500.00, SYSDATE, 'GENERATED');
SAVEPOINT sp_bill;

-- Step 2: Mark appointment completed
UPDATE appointments SET status = 'COMPLETED' WHERE appt_id = 301;
SAVEPOINT sp_appointment;

-- Step 3: Decrease medicine stock
UPDATE medicines SET stock_qty = stock_qty - 5 WHERE medicine_id = 201;

-- If stock went negative (business rule violation):
-- ROLLBACK TO sp_appointment;  ← Undo only the stock update
-- Handle manually: alert pharmacist, then retry

-- If everything is OK:
COMMIT;  -- All 3 changes permanently saved

-- If catastrophic failure:
-- ROLLBACK;  ← Undo EVERYTHING since last commit

SQL — Role-Based Access
-- Create roles
CREATE ROLE role_doctor;
CREATE ROLE role_billing;
CREATE ROLE role_lab_tech;
CREATE ROLE role_admin;

-- Doctor: read patients, manage appointments & prescriptions
GRANT SELECT ON patients TO role_doctor;
GRANT SELECT, INSERT, UPDATE ON appointments TO role_doctor;
GRANT SELECT, INSERT ON prescriptions TO role_doctor;
-- NO access to bills table

-- Billing: access bills and limited patient info
GRANT SELECT, INSERT, UPDATE ON bills TO role_billing;
-- Create view for limited patient access
CREATE VIEW patient_billing_view AS
    SELECT patient_id, patient_name, phone FROM patients;
GRANT SELECT ON patient_billing_view TO role_billing;

-- Lab technician: INSERT results only
GRANT INSERT ON lab_results TO role_lab_tech;
GRANT SELECT ON patients TO role_lab_tech; -- Need to look up patient

-- Admin: full access
GRANT ALL PRIVILEGES ON patients TO role_admin;
GRANT ALL PRIVILEGES ON doctors TO role_admin;
GRANT ALL PRIVILEGES ON bills TO role_admin;

-- Assign to users
GRANT role_doctor TO dr_anil, dr_priya;
GRANT role_billing TO cashier_ravi;

Lab Exercises

MCQ Assessment Bank — 15 Questions

Hover to reveal answer and explanation.

Chapter Summary

DDL:  CREATE TABLE t (col TYPE CONSTRAINT);
      ALTER TABLE t ADD (col TYPE);
      ALTER TABLE t MODIFY (col NEW_TYPE);
      ALTER TABLE t DROP COLUMN col;
      DROP TABLE t;    TRUNCATE TABLE t;

DML:  INSERT INTO t (cols) VALUES (vals);
      UPDATE t SET col = val WHERE condition;
      DELETE FROM t WHERE condition;

TCL:  COMMIT;  ROLLBACK;  SAVEPOINT sp;  ROLLBACK TO sp;

DCL:  GRANT SELECT ON t TO user;
      REVOKE INSERT ON t FROM user;
      CREATE ROLE r;  GRANT r TO user;

KEY FUNCTIONS:
  String:  UPPER() LOWER() SUBSTR(s,start,len) LENGTH() TRIM() LPAD()
  Number:  ROUND(n,d) TRUNC(n,d) MOD(a,b) ABS() CEIL() FLOOR()
  Date:    SYSDATE  ADD_MONTHS(d,n)  MONTHS_BETWEEN(d1,d2)
           TO_CHAR(d,'DD-Mon-YYYY')  TO_DATE('str','fmt')
  NULL:    NVL(expr,default)  COALESCE(a,b,c)  NULLIF(a,b)
  Window:  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)
           RANK()  DENSE_RANK()  LAG(col,n)  LEAD(col,n)

Interview & Career Preparation

🎓 Certification Path

• Oracle Database SQL Certified Associate (1Z0-071) — Covers everything in this chapter. The single most valuable SQL certification in Indian IT.
• HackerRank SQL Certificate — Free. Proves basic-to-intermediate SQL. Add to LinkedIn immediately.
• LeetCode SQL 50 — 50 curated problems covering SELECT, JOINs, subqueries, window functions. Industry interview preparation gold standard.