Unit 2: Relational Query Language

1. Introduction to SQL (Structured Query Language)

SQL is the standard language for interacting with Relational Database Management Systems (RDBMS). It allows users to create, maintain, and retrieve data from a relational database. SQL commands are categorized based on the type of operation they perform.

1.1 Data Definition Language (DDL)

DDL commands are used to define or modify the structure (schema) of the database. These commands do not deal with the data itself, but rather the containers (tables, indexes, views) that hold the data. DDL commands are auto-committed.

• CREATE: Used to create objects in the database (tables, views, databases).
  
  SQL

      CREATE TABLE Student (
          StudentID INT,
          Name VARCHAR(50),
          Age INT
      );
      

• ALTER: Used to alter the structure of the database (e.g., add a column, change data type).
  
  SQL

      ALTER TABLE Student ADD Email VARCHAR(100);
      

• DROP: Used to delete objects from the database. It removes the table structure and all data.
  
  SQL

      DROP TABLE Student;
      

• TRUNCATE: Used to remove all records from a table while keeping the structure intact. It is faster than DELETE.
  
  SQL

      TRUNCATE TABLE Student;
      

1.2 Data Manipulation Language (DML)

DML commands are used for managing data within schema objects. They allow users to insert, update, retrieve, and delete data. DML commands are not auto-committed.

• INSERT: Used to insert data into a table.
  
  SQL

      INSERT INTO Student (StudentID, Name, Age) VALUES (1, 'John Doe', 20);
      

• UPDATE: Used to modify existing data within a table.
  
  SQL

      UPDATE Student SET Age = 21 WHERE StudentID = 1;
      

• DELETE: Used to delete records from a table.
  
  SQL

      DELETE FROM Student WHERE StudentID = 1;
      

• SELECT: Used to retrieve data from the database. (Sometimes classified separately as DQL - Data Query Language).
  
  SQL

      SELECT * FROM Student;
      

1.3 Data Control Language (DCL)

DCL commands deal with the rights, permissions, and other controls of the database system.

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

      GRANT SELECT, UPDATE ON Student TO User1;
      

• REVOKE: Withdraws user's access privileges given by using the GRANT command.
  
  SQL

      REVOKE SELECT ON Student FROM User1;
      

1.4 Transaction Control Language (TCL)

TCL commands deal with the transaction within the database. They help manage the changes made by DML statements.

• COMMIT: Saves all the transactions to the database permanently.
  
  SQL

      COMMIT;
      

• ROLLBACK: Undoes transactions that have not yet been saved to the database.
  
  SQL

      ROLLBACK;
      

• SAVEPOINT: Used to roll the transaction back to a certain point without rolling back the entire transaction.
  
  SQL

      SAVEPOINT Savepoint1;
      

2. Database Keys

Keys are fundamental to the relational model. They represent relationships between tables and ensure that each record in a table can be uniquely identified.

2.1 Primary Key (PK)

A column or a set of columns that uniquely identifies each row in a table.

• Must contain unique values.
• Cannot contain NULL values.
• Only one Primary Key is allowed per table.

2.2 Candidate Key

A set of attributes that can uniquely identify a tuple.

• A table can have multiple candidate keys.
• The Primary Key is selected from the set of Candidate Keys.
• Example: In a purely academic table, both Student_ID and Social_Security_Number are candidate keys.

2.3 Super Key

A set of attributes that can uniquely identify a tuple. A super key is a superset of a candidate key.

• Example: If Student_ID is a candidate key, then (Student_ID, Name) is a Super Key.

2.4 Foreign Key (FK)

A column or a combination of columns that is used to establish and enforce a link between the data in two tables.

• The Foreign Key in one table points to the Primary Key in another table.
• It maintains Referential Integrity.
• It can accept NULL values and duplicate values.

2.5 Alternate Key

All candidate keys that were not selected to be the Primary Key are called Alternate Keys (or Secondary Keys).

2.6 Composite Key

A Primary Key that consists of two or more attributes (columns) to uniquely identify a record.

3. Integrity Constraints

Integrity constraints are a set of rules used to maintain the quality and consistency of information in a database. They ensure that the data insertion, updating, and other processes do not compromise data accuracy.

3.1 Domain Integrity Constraints

These define the valid set of values for an attribute.

• NOT NULL: Ensures that a column cannot have a NULL value.
• CHECK: Ensures that all values in a column satisfy a specific condition.
  • Example: CHECK (Age >= 18) ensures no student under 18 is entered.
• DEFAULT: Provides a default value for a column when none is specified.

3.2 Entity Integrity Constraints

These ensure that each row in a table is a uniquely identifiable entity.

• PRIMARY KEY Constraint: Ensures uniqueness and non-null status.
• UNIQUE Constraint: Ensures that all values in a column are different. Unlike Primary Keys, a Unique constraint usually allows one NULL value (depending on the DBMS).

3.3 Referential Integrity Constraints

These preserve the defined relationships between tables when records are entered or deleted.

• FOREIGN KEY Constraint: Prevents actions that would destroy links between tables. It ensures that a value in the child table (Foreign Key) corresponds to a valid value in the parent table (Primary Key) or is NULL.

4. SQL Basic Operations

4.1 Basic Structure of SQL Queries

The fundamental structure of an SQL query consists of three clauses: SELECT, FROM, and WHERE.

SELECT column1, column2
FROM table_name
WHERE condition;

• SELECT: Specifies the attributes to be retrieved.
• FROM: Specifies the table(s) to be accessed.
• WHERE: Specifies the condition (predicate) to filter rows.

4.2 SQL Operators

Arithmetic Operators

Used to perform mathematical operations on numeric data.

• + (Addition), - (Subtraction), * (Multiplication), / (Division), % (Modulus).

Comparison Operators

Used in the WHERE clause to compare one expression with another.

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

Logical Operators

Used to combine multiple conditions.

• AND: Returns true if both conditions are true.
• OR: Returns true if at least one condition is true.
• NOT: Reverses the result of the condition.

Special Operators

• BETWEEN: Selects values within a given range.
• LIKE: Search for a pattern (uses wildcards % and _).
• IN: To specify multiple possible values for a column.
• IS NULL: To test for empty values.

4.3 Set Operations

SQL supports set operations which allow the results of multiple queries to be combined into a single result set.

• UNION: Combines the result-set of two or more SELECT statements. It removes duplicate rows by default.
  
  SQL

      SELECT Name FROM TableA
      UNION
      SELECT Name FROM TableB;
      

• UNION ALL: Combines result sets including duplicates.
• INTERSECT: Returns only the rows that are common to both result sets.
  
  SQL

      SELECT Name FROM TableA
      INTERSECT
      SELECT Name FROM TableB;
      

• MINUS (or EXCEPT): Returns rows from the first query that are not present in the second query.
  
  SQL

      SELECT Name FROM TableA
      MINUS
      SELECT Name FROM TableB;
      

4.4 Aggregate Functions

SQL provides built-in functions to perform calculations on data.

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum of a numeric column.
• AVG(): Returns the average value.
• MIN(): Returns the smallest value.
• MAX(): Returns the largest value.