Chapter 10: Structured Query Language (SQL)

SQL (Structured Query Language) is the standard language for interacting with Relational Database Management Systems (RDBMS). It is used to create, modify, retrieve, and delete data within databases.

SQL commands are broadly categorized into two main groups:

1. DDL (Data Definition Language): Used to define or alter the database structure (schema). These commands affect the tables themselves, not just the data inside them.
   • Examples: CREATE, ALTER, DROP
2. DML (Data Manipulation Language): Used to manage and manipulate the actual data stored within the tables.
   • Examples: INSERT, UPDATE, DELETE, SELECT

10.1 DDL Commands: Creating & Modifying Tables

1. Data Types and Constraints

When creating a table, you must define the type of data each column will hold.

• CHAR(n): Fixed-length character string. (e.g., CHAR(10) always uses 10 bytes, padding with spaces if needed).
• VARCHAR(n): Variable-length character string. (Uses only the storage needed, up to a maximum of n characters. Better for storage efficiency).
• INT / INTEGER: Whole numbers.
• FLOAT: Decimal numbers.
• DATE: Stores dates (format usually ‘YYYY-MM-DD’).

Constraints are rules applied to columns to enforce data integrity:

• NOT NULL: Ensures that a column cannot have a NULL (empty) value.
• UNIQUE: Ensures all values in a column are entirely distinct.
• PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.

2. Basic Database Commands

• CREATE DATABASE School; (Creates a new database)
• USE School; (Selects the active database to work on)
• SHOW DATABASES; (Lists all databases on the server)
• DROP DATABASE School; (Deletes the entire database)

3. Creating a Table

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    DOB DATE,
    Fee FLOAT
);

4. Viewing Table Structure

• SHOW TABLES; (Lists all tables in the current database)
• DESCRIBE Student; or DESC Student; (Shows the column definitions of the table)

5. Altering and Dropping Tables

-- Adding a completely new column
ALTER TABLE Student ADD City VARCHAR(20);

-- Modifying the data type of an existing column
ALTER TABLE Student MODIFY Name VARCHAR(50);

-- Removing a primary key (Note: there can be only one, so you don't name it)
ALTER TABLE Student DROP PRIMARY KEY;

-- Deleting the entire table
DROP TABLE Student;

10.2 DML Commands: Manipulating Data

1. Inserting Data

-- Inserting a single row (must match the column order exactly)
INSERT INTO Student VALUES (1, 'Ravi', '2005-04-12', 4500.50, 'Delhi');

-- Inserting explicitly into specific columns
INSERT INTO Student (RollNo, Name, Fee) VALUES (2, 'Priya', 5000.00);

2. The Powerful SELECT Command (Retrieval)

The SELECT command is the most frequently used SQL command. It allows you to query the database and retrieve specific data.

-- 1. Display all columns for every row
SELECT * FROM Student;

-- 2. Display specific columns
SELECT Name, City FROM Student;

-- 3. ALIASING (Renaming columns in the output for readability)
SELECT Name AS 'Student Name', Fee AS 'Monthly Fee' FROM Student;

-- 4. DISTINCT (Removing duplicate values from the result)
SELECT DISTINCT City FROM Student;

3. Filtering and Conditions (WHERE clause)

SELECT * FROM Student WHERE City = 'Delhi';
SELECT * FROM Student WHERE Fee > 4000;

-- Relational Operators: =, >, <, >=, <=, != or <>
-- Logical Operators: AND, OR, NOT

SELECT * FROM Student WHERE City = 'Delhi' AND Fee > 4500;

4. Special Operators (IN, BETWEEN, LIKE, IS NULL)

-- IN: Matches any value in a defined list
SELECT * FROM Student WHERE City IN ('Delhi', 'Mumbai', 'Pune');

-- BETWEEN: Specifies a range (inclusive)
SELECT * FROM Student WHERE Fee BETWEEN 3000 AND 5000;

-- LIKE: Pattern matching. 
-- '%' matches zero or more characters. '_' matches exactly ONE character.
SELECT * FROM Student WHERE Name LIKE 'A%';   -- Starts with 'A'
SELECT * FROM Student WHERE Name LIKE '%A';   -- Ends with 'A'
SELECT * FROM Student WHERE Name LIKE '_a%';  -- Second letter is 'a'

-- IS NULL: Checks for empty values. (Do NOT use '= NULL')
SELECT * FROM Student WHERE City IS NULL;
SELECT * FROM Student WHERE City IS NOT NULL;

5. Sorting Data (ORDER BY)

Sorts the result set. Default is Ascending (ASC). Use DESC for Descending.

SELECT * FROM Student ORDER BY Name ASC;
SELECT * FROM Student ORDER BY Fee DESC, Name ASC;

6. Updating Data (UPDATE)

Modifies existing records. Always use a WHERE clause! If you forget it, every single row in the table will be updated.

UPDATE Student SET Fee = Fee + 500 WHERE City = 'Delhi';

7. Deleting Data (DELETE)

Removes existing records. Again, always use a WHERE clause!

DELETE FROM Student WHERE RollNo = 2;
-- (If you wrote `DELETE FROM Student;`, the table would be emptied)

10.3 Advanced SQL Concepts

1. Aggregate Functions

These functions perform a calculation on a set of values and return a single value. They ignore NULL values (except COUNT(*)).

• MAX(): Returns the largest value.
• MIN(): Returns the smallest value.
• AVG(): Returns the average value.
• SUM(): Returns the total sum.
• COUNT(): Returns the number of rows.
  • COUNT(*) counts all rows including NULLs.
  • COUNT(column_name) counts non-NULL values in that column.

SELECT MAX(Fee), MIN(Fee), AVG(Fee) FROM Student;
SELECT COUNT(*) FROM Student WHERE City = 'Delhi';

2. Grouping Data (GROUP BY and HAVING)

GROUP BY groups rows that have the same values into summary rows. It is almost always used in conjunction with aggregate functions.

-- Count how many students are in each city
SELECT City, COUNT(*) FROM Student GROUP BY City;

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. HAVING applies conditions after the grouping has occurred.

-- Find cities that have MORE than 5 students
SELECT City, COUNT(*) FROM Student GROUP BY City HAVING COUNT(*) > 5;

3. Joins (Combining Tables)

A JOIN is used to combine rows from two or more tables based on a related column (usually Primary Key and Foreign Key) between them.

Suppose we have an Employee table and a Department table.

• Cartesian Product (Cross Join): Combines every row in the first table with every row in the second table. Rarely useful on its own, produces massive output. (Used when no condition is specified).
• Equi-Join: Joins tables based on an equality condition (=) between two columns.

  SELECT Employee.Name, Department.DeptName 
  FROM Employee, Department 
  WHERE Employee.DeptId = Department.DeptId;
  

• Natural Join: A simpler way to write an Equi-Join. If the two tables share a column with the exact same name (like DeptId), a Natural Join automatically connects them based on that column, without needing a WHERE clause.

  SELECT * FROM Employee NATURAL JOIN Department;
  

Mastering SQL is the key to interacting effectively with databases of any size. Practices these queries extensively!