Unit 2 - Notes

INT306

Unit 2: Relational Query Language

1. SQL Command Classifications

SQL (Structured Query Language) is the standard language for interacting with Relational Database Management Systems (RDBMS). It is not a single block of commands but is categorized based on the type of operation performed.

A. Data Definition Language (DDL)

DDL is used to define, alter, and manage the database schema (structure). These commands deal with the descriptions of the database schema and are used to create and modify the structure of database objects in the database.

Characteristics:
- DDL commands affect the metadata (data about data).
- They are auto-committed (changes are saved permanently immediately; they cannot be rolled back).
Key Commands:
1. CREATE: Used to create the database or its objects (like tables, indexes, functions, views, store procedures, and triggers).
  SQL
```
        CREATE TABLE Students (
            StudentID int,
            Name varchar(255),
            Age int
        );
        
```
2. DROP: Used to delete objects from the database. It removes the table structure and all data.
  SQL
```
        DROP TABLE Students;
        
```
3. ALTER: Used to modify the structure of the database. This includes adding, modifying, or deleting columns in an existing table.
  SQL
```
        ALTER TABLE Students ADD Email varchar(255);
        
```
4. TRUNCATE: Used to remove all records from a table, including all spaces allocated for the records are removed. The structure remains.
  SQL
```
        TRUNCATE TABLE Students;
        
```
5. RENAME: Used to rename an object existing in the database.

B. Data Manipulation Language (DML)

DML commands are used for managing data within schema objects. They deal with the manipulation of the data present in the database.

Characteristics:
- DML commands affect the actual data, not the structure.
- They are not auto-committed (changes can be rolled back unless committed).
Key Commands:
1. INSERT: Used to insert data into a table.
  SQL
```
        INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'John Doe', 20);
        
```
2. UPDATE: Used to update existing data within a table.
  SQL
```
        UPDATE Students SET Age = 21 WHERE StudentID = 1;
        
```
3. DELETE: Used to delete records from a database table.
  SQL
```
        DELETE FROM Students WHERE StudentID = 1;
        
```
4. SELECT: (Often categorized under DQL - Data Query Language, but functionally manipulates the view of data). Used to retrieve data from the database.

C. Data Control Language (DCL)

DCL commands are used to grant and take back authority from any database user. They deal with rights, permissions, and other controls of the database system.

Key Commands:

GRANT: Gives user's access privileges to the database.

SQL

        GRANT SELECT, UPDATE ON Students TO user1;

REVOKE: Withdraws user's access privileges given by using the GRANT command.
SQL
```
        REVOKE SELECT ON Students FROM user1;
        
```

D. Transaction Control Language (TCL)

TCL commands deal with the transaction within the database. They allow you to control the outcome of DML statements.

Key Commands:
1. COMMIT: Commits a transaction. It permanently saves all the changes made in the transaction to the database.
2. ROLLBACK: Restores the database to the last committed state (undoes changes).
3. SAVEPOINT: Sets a savepoint within a transaction to which you can later roll back.

2. Database Keys

Keys are fundamental elements in relational databases used to establish relationships between tables and ensure unique identification of records.

A. Primary Key (PK)

Definition: A column (or set of columns) that uniquely identifies each row in a table.
Rules:
- Values must be unique.
- Cannot contain NULL values.
- A table can have only one Primary Key.
Example: StudentID in a Student table.

B. Candidate Key

Definition: A set of attributes that can uniquely identify a tuple. A super key with no repeated attributes is a candidate key.
Relationship: The Primary Key is selected from the pool of Candidate Keys.
Example: In a table with StudentID and Email (where both are unique), both are Candidate Keys.

C. Super Key

Definition: A set of attributes that can uniquely identify a tuple. It is a superset of a Candidate Key.
Example: If StudentID is a unique key, then (StudentID, Name) is a Super Key, even though Name is not required for uniqueness.

D. Foreign Key (FK)

Definition: A column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables.
Function: Enforces Referential Integrity. The value in a Foreign Key column must match a value in the Primary Key of the parent table, or be NULL.
Example: DepartmentID in the Students table linking to DepartmentID in the Departments table.

E. Alternate Key (Secondary Key)

Definition: A key that was a Candidate Key but was not selected to be the Primary Key.
Example: If StudentID is chosen as PK, then Email becomes the Alternate Key.

F. Composite Key

Definition: A key that consists of two or more attributes (columns) that together uniquely identify an entity occurrence.
Example: In a CourseGrades table, neither StudentID nor CourseID is unique alone, but the combination (StudentID, CourseID) is unique.

3. Integrity Constraints

Integrity constraints are a set of rules used to maintain the quality of information. They ensure that the data insertion, updating, and other processes do not ruin the data integrity.

A. Types of Integrity

Entity Integrity: Ensures that each row in a table is a unique instance. (Enforced by Primary Keys).
Referential Integrity: Ensures the consistency of data between cross-referenced tables. (Enforced by Foreign Keys).
Domain Integrity: Ensures that all data in a column falls within defined valid limits (e.g., data types, check constraints).

B. SQL Constraints

When creating or altering tables, specific keywords define these rules:

NOT NULL: Ensures that a column cannot have a NULL value.
SQL
```
    Name varchar(255) NOT NULL
    
```
UNIQUE: Ensures that all values in a column are different.
SQL
```
    Email varchar(255) UNIQUE
    
```
PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
FOREIGN KEY: Uniquely identifies a row/record in another table.
CHECK: Ensures that all values in a column satisfy a specific condition.
SQL
```
    Age int CHECK (Age >= 18)
    
```
DEFAULT: Sets a default value for a column when no value is specified.
SQL
```
    City varchar(255) DEFAULT 'New York'
    
```

4. SQL Basic Operations

The core of SQL usage lies in the basic operations used to query and filter data.

A. The SELECT Statement

The most commonly used statement. It allows you to select data from a database.

Syntax:

SQL

    SELECT column1, column2, ...
    FROM table_name;

Select All: Using the asterisk (*) selects all columns.
SQL
```
    SELECT * FROM Students;
    
```

DISTINCT: Returns only distinct (different) values.

SQL

    SELECT DISTINCT City FROM Students;

B. The WHERE Clause

Used to filter records. It extracts only those records that fulfill a specified condition.

Syntax:

SQL

    SELECT column1, column2
    FROM table_name
    WHERE condition;

C. SQL Operators

Operators are reserved words or characters used primarily in a WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

1. Arithmetic Operators

+ (Add), - (Subtract), * (Multiply), / (Divide), % (Modulus).

2. Comparison Operators

= : Equal to
<> or != : Not equal to
> : Greater than
< : Less than
>= : Greater than or equal to
<= : Less than or equal to

3. Logical Operators

AND: Displays a record if all the conditions separated by AND are TRUE.
OR: Displays a record if any of the conditions separated by OR is TRUE.
NOT: Displays a record if the condition(s) is NOT TRUE.

4. Special Operators

BETWEEN: Selects values within a given range.
SQL
```
    WHERE Age BETWEEN 18 AND 25;
    
```
LIKE: Used to search for a specified pattern in a column.
- % represents zero, one, or multiple characters.
- _ represents a single character.
  SQL
```
    WHERE Name LIKE 'A%'; -- Starts with A
    
```
IN: Allows you to specify multiple values in a WHERE clause (shorthand for multiple OR conditions).
SQL
```
    WHERE City IN ('Paris', 'London', 'Berlin');
    
```
IS NULL / IS NOT NULL: Used to test for empty values (NULL). You cannot use = with NULL.
SQL
```
    WHERE Email IS NULL;
    
```

D. Sorting Results (ORDER BY)

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Default: Ascending order.

Syntax:

SQL

    SELECT * FROM Students
    ORDER BY Age DESC; -- Sorts by Age high to low

E. Aliases (AS)

SQL aliases are used to give a table, or a column in a table, a temporary name to make it more readable.

SQL

SELECT Name AS StudentName, Age AS StudentAge
FROM Students;

Unit 2 - Notes

Table of Contents

Unit 2: Relational Query Language

1. SQL Command Classifications

A. Data Definition Language (DDL)

B. Data Manipulation Language (DML)

C. Data Control Language (DCL)

D. Transaction Control Language (TCL)

2. Database Keys

A. Primary Key (PK)

B. Candidate Key

C. Super Key

D. Foreign Key (FK)

E. Alternate Key (Secondary Key)

F. Composite Key

3. Integrity Constraints

A. Types of Integrity

B. SQL Constraints

4. SQL Basic Operations

A. The SELECT Statement

B. The WHERE Clause

C. SQL Operators

1. Arithmetic Operators

2. Comparison Operators

3. Logical Operators

4. Special Operators

D. Sorting Results (ORDER BY)

E. Aliases (AS)