Computer Science: Class 12 — Chapter 9: Structured Query Language (SQL)

• Declarative Language: Users specify what data to retrieve rather than how to retrieve it.
• Case Insensitivity: SQL statements are case-insensitive (e.g., SELECT is equivalent to select ). However, database and table names can be case-sensitive in Linux environments.
• Statement Terminator: Every SQL statement must end with a semicolon ( ; ).
• Multiline Input: Statements can span multiple lines. The prompt changes from mysql> to -> to indicate continuation.

• NOT NULL : Ensures that a column cannot store a NULL (missing/unknown) value.
• UNIQUE : Guarantees that all values in a column are distinct.
• DEFAULT : Provides a fallback value when no explicit value is specified during insertion.
• PRIMARY KEY : Uniquely identifies each record in a table. It implicitly combines NOT NULL and UNIQUE .
• FOREIGN KEY : Establishes a link between tables by referencing the primary key of another table, enforcing referential integrity.

  
    -- Create a new database
    CREATE DATABASE StudentAttendance;
    -- View existing databases
    SHOW DATABASES;
    -- Select a database for active use
    USE StudentAttendance;
    -- View tables within the active database
    SHOW TABLES;
  

The CREATE TABLE statement defines the relation name, attributes, data types, and constraints.

  
    CREATE TABLE STUDENT(
    RollNumber INT,
    SName VARCHAR(20) NOT NULL,
    SDateofBirth DATE,
    GUID CHAR(12),
    PRIMARY KEY (RollNumber)
    );
  

To inspect the schema of an existing table, use the DESCRIBE or DESC command:

  
  DESCRIBE STUDENT;

The ALTER TABLE statement allows dynamic modifications to an existing table structure without destroying data.

1. Add a Primary Key:

       
       ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
     
   

2. Add a Composite Primary Key:

     
     ALTER TABLE ATTENDANCE ADD PRIMARY KEY (AttendanceDate, RollNumber);
   
   

3. Add a Foreign Key:

     
     ALTER TABLE STUDENT ADD FOREIGN KEY (GUID) REFERENCES GUARDIAN(GUID);
   
   

4. Add a UNIQUE Constraint:

     
     ALTER TABLE GUARDIAN ADD UNIQUE (GPhone);
   
   

5. Add a New Attribute:

     
     ALTER TABLE GUARDIAN ADD income INT;
   
   

6. Modify Attribute Datatype:

     
     ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);
   
   

7. Modify Constraint (Add NOT NULL):

     
     ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;
   
   

8. Add a Default Value:

     
     ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT '2000-05-15';
   
   

9. Remove an Attribute:

     
     ALTER TABLE GUARDIAN DROP COLUMN income;
   
   

10. Remove a Primary Key:

      
      ALTER TABLE GUARDIAN DROP PRIMARY KEY;
    
    

The DROP statement permanently deletes tables or databases from the system. This operation is non-reversible.

  
    -- Delete a table and all its data
    DROP TABLE STUDENT;
    -- Delete an entire database and all its tables
    DROP DATABASE StudentAttendance;
  

Records can be inserted by specifying values for all columns in schema order, or by explicitly declaring target columns.

  
    -- Insertion by schema order
    INSERT INTO GUARDIAN
    VALUES (444444444444, 'Amit Ahuja', 5711492685, 'G-35, Ashok Vihar, Delhi');
    -- Insertion with explicit column mapping (useful for omitting nullable/default columns)
    INSERT INTO GUARDIAN (GUID, GName, GAddress)
    VALUES (333333333333, 'Danny Dsouza', 'S-13, Ashok Village, Daman');
  

The UPDATE statement modifies existing values. A WHERE clause is critical to restrict the scope of modification; omitting it updates all rows in the table.

  
    UPDATE STUDENT
    SET GUID = '101010101010'
    WHERE RollNumber = 3;
  

The DELETE statement removes rows. Like UPDATE , omitting the WHERE clause deletes all records while preserving the table structure.

  
    DELETE FROM STUDENT
    WHERE RollNumber = 2;
  

  
    -- Retrieve all columns and rows
    SELECT * FROM EMPLOYEE;
    -- Retrieve specific columns
    SELECT EmpNo, Ename FROM EMPLOYEE;
    -- Column Aliasing (Renaming output headers)
    SELECT EName AS Name, Salary * 12 AS 'Annual Income'
    FROM EMPLOYEE;
  

The WHERE clause filters records based on logical and comparison expressions.

• Relational Operators: = , < , > , <= , >= , != (or <> ).
• Logical Operators: AND , OR , NOT .
• Range Filtering (BETWEEN): Inclusive boundary check.

    
      SELECT Ename, DeptId FROM EMPLOYEE
      WHERE Salary BETWEEN 20000 AND 50000;
    
  

• Set Membership (IN): Matches any value in a specified list.

    
      SELECT * FROM EMPLOYEE
      WHERE DeptId IN ('D01', 'D02', 'D04');
    
  

Sorts the output in ascending ( ASC , default) or descending ( DESC ) order.

  
    SELECT * FROM EMPLOYEE
    ORDER BY Salary DESC, Bonus ASC;
  

Because NULL represents an unknown state, standard comparison operators (like = NULL ) fail. Special operators must be used:

  
    -- Find records with missing values
    SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
    -- Find records with defined values
    SELECT * FROM EMPLOYEE WHERE Bonus IS NOT NULL;
  

Enables substring search using wildcards:

• % : Matches zero, one, or multiple characters.
• _ : Matches exactly one character.

  
    -- Names starting with 'K'
    SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';
    -- Names ending with 'a'
    SELECT * FROM EMPLOYEE WHERE Ename LIKE '%a';
    -- Names with 'a' as the second character
    SELECT * FROM EMPLOYEE WHERE Ename LIKE '_a%';
    -- Exactly 5-character names ending with 'ANYA'
    SELECT * FROM EMPLOYEE WHERE Ename LIKE '_ANYA';
  

These functions operate on a single value per row and return one result per row.

These functions process a set of values across multiple rows to return a single summary value.

• MAX(column) : Returns the largest value in the column.
• MIN(column) : Returns the smallest value in the column.
• AVG(column) : Computes the average value (ignores NULL ).
• SUM(column) : Calculates the total sum of values.
• COUNT(*) : Counts the total number of rows in a table, including those with NULL values.
• COUNT(column) : Counts the number of non-NULL values in a specific column.

To filter groups based on aggregate conditions, the HAVING clause must be used instead of WHERE (since WHERE filters individual rows before aggregation occurs).

  
    -- Count cars purchased by each customer
    SELECT CustID, COUNT(*) AS 'Number of Cars'
    FROM SALE
    GROUP BY CustID;
    -- Find customers who purchased more than 1 car
    SELECT CustID, COUNT(*)
    FROM SALE
    GROUP BY CustID
    HAVING COUNT(*) > 1;
  

• UNION ($\\cup$): Combines rows from both tables, eliminating duplicate rows.

      
        SELECT Name FROM DANCE
        UNION
        SELECT Name FROM MUSIC;
      
    

• INTERSECT ($\\cap$): Returns only the common rows present in both tables.

    
      -- Conceptual representation (MySQL uses INNER JOIN or IN for intersection)
      SELECT D.Name FROM DANCE D
      WHERE D.Name IN (SELECT M.Name FROM MUSIC M);
    
  

• MINUS / EXCEPT (-): Returns rows present in the first table but absent in the second.

    
      -- Conceptual representation
      SELECT D.Name FROM DANCE D
      WHERE D.Name NOT IN (SELECT M.Name FROM MUSIC M);
    
  

Combines every row of the first relation with every row of the second relation.

• Degree: Sum of the degrees of both relations ($Degree(R \\times S) = Degree(R) + Degree(S)$).
• Cardinality: Product of the cardinalities of both relations ($Cardinality(R \\times S) = Cardinality(R) \\times Cardinality(S)$).

  
  SELECT * FROM DANCE, MUSIC;

1. Equi-Join using WHERE clause:

       
         SELECT * FROM UNIFORM U, COST C
         WHERE U.UCode = C.UCode;
       
     

2. Explicit JOIN ON clause:

     
       SELECT * FROM UNIFORM U
       JOIN COST C ON U.UCode = C.UCode;
     
   

3. Natural Join:

     
       SELECT * FROM UNIFORM
       NATURAL JOIN COST;