Database Management Systems

Unit 2: Relational Query Language — DDL & DML

From CREATE TABLE to SELECT mastery — the SQL commands that build, fill, protect, and query every database on Earth.

🏢 Oracle & PostgreSQL | 📝 15 MCQs (Bloom's) | 🔬 5 Lab Exercises | 💼 Interview Prep

Section 1

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).

🏢 Industry Snapshot

IRCTC — Their reservation system runs ~200 DDL migration scripts per release cycle. Every table (trains, coaches, seats, passengers, bookings, payments) was designed with the constraints you'll learn in this chapter. A missing NOT NULL on pnr_number once caused a production bug affecting 50,000 bookings.

Razorpay — Processes ₹7 lakh crore annually. Every payment is an INSERT + UPDATE wrapped in a transaction. Their DBA team reviews every ALTER TABLE because adding a column to a 500-million-row table can lock it for hours if done wrong.

TCS / Infosys — 80% of freshers are assigned to database-related projects in their first year. Your SQL skills from this chapter directly determine your project allocation and early career trajectory.

🇮🇳 IRCTC🇮🇳 Razorpay🇮🇳 TCS🇮🇳 PhonePe🇮🇳 Infosys🇮🇳 Flipkart

SQL was invented in 1974 by Donald Chamberlin and Raymond Boyce at IBM. 50+ years later, it remains the most widely used database language. Every major DBMS — Oracle, PostgreSQL, MySQL, SQL Server, SQLite — speaks SQL. Learning SQL is the best ROI of any technical skill.

📦 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

Section 2

Learning Outcomes — Bloom's Taxonomy

Bloom's Level	Outcome Statement
L1 — Remember	List all DDL commands (CREATE, ALTER, DROP, TRUNCATE, RENAME) and DML commands (INSERT, UPDATE, DELETE); recall Oracle vs PostgreSQL data type differences
L2 — Understand	Explain the difference between DELETE, TRUNCATE, and DROP; describe how COMMIT, ROLLBACK, and SAVEPOINT control transactions; explain SELECT execution order
L3 — Apply	Write CREATE TABLE with all constraint types (PK, FK, UNIQUE, CHECK, NOT NULL, DEFAULT); write INSERT/UPDATE/DELETE with subqueries; write SELECT with functions
L4 — Analyze	Analyze query execution order to debug incorrect results; compare Oracle vs PostgreSQL syntax differences and choose the appropriate one
L5 — Evaluate	Evaluate constraint design choices for a production database; justify when to use TRUNCATE vs DELETE; critique a GRANT/REVOKE security configuration
L6 — Create	Design a complete schema with DDL, populate with realistic DML, configure role-based access with DCL, and write complex queries using functions and CASE expressions