SQL: Data Definition, Manipulation, Constraints, and Complex Queries

Overview of SQL

Structured Query Language (SQL) is the declarative language used to interact with Relational Database Management Systems (RDBMS). It is based on relational algebra and tuple relational calculus.

Data Definition Language (DDL)

DDL is used to define the database schema.

• CREATE: Defines a new table.
• ALTER: Modifies an existing table structure.
• DROP: Removes a table and its data permanently.

Example: Creating Tables with Constraints

CREATE TABLE Courses (
    CID INT PRIMARY KEY,
    CourseName VARCHAR(50),
    Dept VARCHAR(30)
);

CREATE TABLE HoD (
    Dept VARCHAR(30) PRIMARY KEY,
    Head VARCHAR(50)
);

-- Adding Foreign Key constraint
ALTER TABLE Courses 
ADD CONSTRAINT fk_dept FOREIGN KEY (Dept) REFERENCES HoD(Dept);

Data Manipulation Language (DML)

DML allows users to interact with the data stored in the tables.

• SELECT: Retrieves data.
• INSERT: Adds new rows.
• UPDATE: Modifies existing data.
• DELETE: Removes rows.

Querying Multiple Tables (Joins)

Joins are used to combine rows from two or more tables based on a related column.

Example: Retrieving data from multiple tables Given: SCHOOL(SID, SName, SAddress, SPhone), TEACHER(TID, TName, TAddress, TQualification), SCHOOL_TEACHER(SID, TID, No_of_Period)

To find the TName, SName, and SPhone for "ABC" school:

SELECT T.TName, S.SName, S.SPhone
FROM TEACHER T
JOIN SCHOOL_TEACHER ST ON T.TID = ST.TID
JOIN SCHOOL S ON ST.SID = S.SID
WHERE S.SName = 'ABC';

Integrity Constraints

Constraints enforce rules on data in the table to maintain accuracy and reliability.

1. PRIMARY KEY: Uniquely identifies each record.
2. FOREIGN KEY: Ensures referential integrity between two tables.
3. NOT NULL: Ensures a column cannot have a NULL value.
4. UNIQUE: Ensures all values in a column are different.
5. CHECK: Ensures values in a column satisfy a specific condition.

Comparison: DDL vs DML

Aggregate Functions and Grouping

SQL provides built-in functions to perform calculations on sets of values:

• COUNT(): Returns the number of rows.
• SUM(): Returns the sum of a numeric column.
• AVG(): Returns the average value.
• GROUP BY: Groups rows that have the same values into summary rows.
• HAVING: Used instead of WHERE with aggregate functions.

Example: Finding products with price 1000

SELECT Pname FROM Product WHERE price = 1000;

Subqueries

A subquery is a query nested inside another query. It is useful when a query depends on the result of another query.

Example: Find students enrolled in a specific course

SELECT SName FROM Student 
WHERE SID IN (SELECT SID FROM Enroll WHERE CID = 'CS101');

Exam Tip

In TU exams, you are often asked to write SQL queries based on a provided schema.

1. Always identify the Primary and Foreign keys first to understand how tables are linked.
2. Use Aliases (e.g., FROM Student S) to make your queries readable and avoid ambiguity when joining tables.
3. Check for "All" or "Every": If a question asks for "all students who took all courses," this usually requires a GROUP BY with a HAVING COUNT(*) clause or a double-negation subquery.
4. Constraints: When asked to create tables, always include PRIMARY KEY and FOREIGN KEY clauses to ensure full marks for schema design.