Introduction
SQL keys are crucial for database management and integrity. They ensure that each
record within a table can be uniquely identified and establish relationships between
tables.
Types of Keys in SQL
1. **Primary Key**
2. **Foreign Key**
3. **Unique Key**
4. **Composite Key**
5. **Candidate Key**
6. **Super Key**
1. Primary Key
A primary key uniquely identifies each record in a table. It must contain unique values
and cannot contain NULL values.
Example
Consider a table `Students`:
| StudentID | FirstName | LastName | Age |
|-----------|-----------|----------|-----|
|1 | John | Doe | 22 |
|2 | Jane | Smith | 23 |
|3 | Emily | Davis | 21 |
Here, `StudentID` is the primary key.
```sql
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
```
2. Foreign Key
A foreign key is a field (or collection of fields) in one table that refers to the primary
key in another table. It establishes a link between the two tables.
Example
Consider another table `Courses` and a table `Enrollments` that links students to
courses:
```sql
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100)
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
```
3. Unique Key
A unique key ensures that all values in a column are unique. Unlike a primary key, a
table can have multiple unique keys, and unique keys can contain NULL values (but
only one NULL per column).
Example
Consider adding an `Email` field to the `Students` table that must be unique:
```sql
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Email VARCHAR(100) UNIQUE
);
```
4. Composite Key
A composite key is a primary key composed of multiple columns. It uniquely identifies
a record by combining the values in these columns.
Example
Consider a `BookLoans` table with a composite key made of `BookID` and
`LibraryID`:
```sql
CREATE TABLE BookLoans (
BookID INT,
LibraryID INT,
LoanDate DATE,
PRIMARY KEY (BookID, LibraryID)
);
```
5. Candidate Key
A candidate key is a column, or a set of columns, that can uniquely identify a record
in a table. Each table can have multiple candidate keys, but only one primary key.
Example
In the `Students` table, both `StudentID` and `Email` can be candidate keys:
```sql
-- Assume the Students table from previous examples
ALTER TABLE Students ADD CONSTRAINT Unique_Email UNIQUE (Email);
```
6. Super Key
A super key is a set of one or more columns that, when combined, can uniquely
identify a record in a table. A super key includes all candidate keys and primary keys.
Example
In the `Students` table, combinations like `StudentID` and `Email` together can be a
super key:
```sql
-- No explicit SQL syntax for super key as it is a conceptual understanding
-- Example: (StudentID, Email) is a super key
```
Summary
- **Primary Key:** Uniquely identifies records; no NULLs allowed.
- **Foreign Key:** Links two tables together.
- **Unique Key:** Ensures all values in a column are unique.
- **Composite Key:** A primary key consisting of multiple columns.
- **Candidate Key:** A column or set of columns that can uniquely identify a
record.
- **Super Key:** A set of one or more columns that can uniquely identify a
record.
These keys are fundamental to ensuring data integrity and establishing relationships
between tables in a relational database. Understanding and implementing them
correctly is crucial for effective database design.
Module 4: SQL Functions and Operators
Day 7-8: SQL Functions and Operators
1. String Functions
SQL provides a variety of functions to manipulate and handle string data.
CONCAT
Combines two or more strings into one.
```sql
SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
-- Output: Hello World
```
SUBSTRING
Extracts a portion of a string.
```sql
SELECT SUBSTRING('Hello World', 1, 5) AS SubStr;
-- Output: Hello
```
LENGTH
Returns the length of a string.
```sql
SELECT LENGTH('Hello World') AS StrLength;
-- Output: 11
```
2. Numeric Functions
Functions to perform operations on numeric data.
ROUND
Rounds a number to a specified number of decimal places.
```sql
SELECT ROUND(123.456, 2) AS RoundedNumber;
-- Output: 123.46
```
ABS
Returns the absolute value of a number.
```sql
SELECT ABS(-123.45) AS AbsoluteValue;
-- Output: 123.45
```
MOD
Returns the remainder of a division operation.
```sql
SELECT MOD(10, 3) AS Modulus;
-- Output: 1
```
3. Date and Time Functions
Functions to handle date and time values.
DATE
Extracts the date part of a date or datetime expression.
```sql
SELECT DATE('2024-07-10 10:20:30') AS OnlyDate;
-- Output: 2024-07-10
```
TIME
Extracts the time part of a date or datetime expression.
```sql
SELECT TIME('2024-07-10 10:20:30') AS OnlyTime;
-- Output: 10:20:30
```
TIMESTAMP
Combines date and time into a single value.
```sql
SELECT TIMESTAMP('2024-07-10', '10:20:30') AS DateTime;
-- Output: 2024-07-10 10:20:30
```
4. Aggregate Functions
Functions to perform calculations on a set of values and return a single value.
COUNT
Returns the number of rows that match a specified condition.
```sql
SELECT COUNT(*) AS TotalRows FROM Students;
```
SUM
Returns the sum of a numeric column.
```sql
SELECT SUM(Age) AS TotalAge FROM Students;
```
AVG
Returns the average value of a numeric column.
```sql
SELECT AVG(Age) AS AverageAge FROM Students;
```
MAX
Returns the maximum value in a set of values.
```sql
SELECT MAX(Age) AS MaxAge FROM Students;
```
MIN
Returns the minimum value in a set of values.
```sql
SELECT MIN(Age) AS MinAge FROM Students;
```
5. Conditional Functions
Functions to perform actions based on conditions.
CASE
Returns values based on conditions.
```sql
SELECT
StudentID,
CASE
WHEN Age < 20 THEN 'Teenager'
WHEN Age >= 20 AND Age < 30 THEN 'Young Adult'
ELSE 'Adult'
END AS AgeGroup
FROM Students;
```
COALESCE
Returns the first non-null value in a list of expressions.
```sql
SELECT COALESCE(NULL, NULL, 'Default Value') AS Result;
-- Output: Default Value
```
---
Module 5: Working with Multiple Tables
Day 9-12: Table Joins and Set Operations
1. Understanding Table Joins
SQL joins are used to combine rows from two or more tables based on a related
column between them.
INNER JOIN
Returns records that have matching values in both tables.
```sql
SELECT [Link], [Link], [Link]
FROM Students
INNER JOIN Enrollments ON [Link] = [Link]
INNER JOIN Courses ON [Link] = [Link];
```
LEFT JOIN (or LEFT OUTER JOIN)
Returns all records from the left table, and the matched records from the right table.
The result is NULL from the right side, if there is no match.
```sql
SELECT [Link], [Link], [Link]
FROM Students
LEFT JOIN Enrollments ON [Link] = [Link]
LEFT JOIN Courses ON [Link] = [Link];
```
RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all records from the right table, and the matched records from the left table.
The result is NULL from the left side, when there is no match.
```sql
SELECT [Link], [Link], [Link]
FROM Students
RIGHT JOIN Enrollments ON [Link] = [Link]
RIGHT JOIN Courses ON [Link] = [Link];
```
FULL OUTER JOIN
Returns all records when there is a match in either left or right table.
```sql
SELECT [Link], [Link], [Link]
FROM Students
FULL OUTER JOIN Enrollments ON [Link] = [Link]
FULL OUTER JOIN Courses ON [Link] = [Link];
```
2. Combining Results using UNION, INTERSECT, and EXCEPT Operators
UNION
Combines the result sets of two or more SELECT statements (removes duplicates).
```sql
SELECT FirstName FROM Students
UNION
SELECT FirstName FROM Teachers;
```
UNION ALL
Combines the result sets of two or more SELECT statements (includes duplicates).
```sql
SELECT FirstName FROM Students
UNION ALL
SELECT FirstName FROM Teachers;
```
INTERSECT
Returns the common records from two SELECT statements.
```sql
SELECT FirstName FROM Students
INTERSECT
SELECT FirstName FROM Teachers;
```
EXCEPT
Returns records from the first SELECT statement that are not in the second SELECT
statement.
```sql
SELECT FirstName FROM Students
EXCEPT
SELECT FirstName FROM Teachers;
```
3. Nested Queries and Subqueries
A subquery is a query nested inside another query.
Example
Retrieve students enrolled in the 'Math' course.
```sql
SELECT FirstName, LastName
FROM Students
WHERE StudentID IN (
SELECT StudentID
FROM Enrollments
WHERE CourseID = (SELECT CourseID FROM Courses WHERE CourseName =
'Math')
);
```
4. Set Operations
SQL set operations include UNION, UNION ALL, INTERSECT, and EXCEPT, which
we have covered above.
Module 7: Advanced SQL Topics
Day 16-18: Stored Procedures, Functions, and Triggers
1. Working with Stored Procedures and Functions
Stored procedures and functions are precompiled collections of SQL statements.
Stored Procedure
```sql
CREATE PROCEDURE AddStudent(IN FirstName VARCHAR(50), IN LastName
VARCHAR(50))
BEGIN
INSERT INTO Students (FirstName, LastName) VALUES (FirstName, LastName);
END;
```
Calling a Stored Procedure
```sql
CALL AddStudent('
Extracts a portion of a string.
```sql
SELECT SUBSTRING('Hello World', 1, 5) AS SubStr;
-- Output: Hello
```
LENGTH
Returns the length of a string.
```sql
SELECT LENGTH('Hello World') AS StrLength;
-- Output: 11
```
2. Numeric Functions
Functions to perform operations on numeric data.
ROUND
Rounds a number to a specified number of decimal places.
```sql
SELECT ROUND(123.456, 2) AS RoundedNumber;
-- Output: 123.46
```
ABS
Returns the absolute value of a number.
```sql
SELECT ABS(-123.45) AS AbsoluteValue;
-- Output: 123.45
```
MOD
Returns the remainder of a division operation.
```sql
SELECT MOD(10, 3) AS Modulus;
-- Output: 1
```
3. Date and Time Functions
Functions to handle date and time values.
DATE
Extracts the date part of a date or datetime expression.
```sql
SELECT DATE('2024-07-10 10:20:30') AS OnlyDate;
-- Output: 2024-07-10
```
TIME
Extracts the time part of a date or datetime expression.
```sql
SELECT TIME('2024-07-10 10:20:30') AS OnlyTime;
-- Output: 10:20:30
```
TIMESTAMP
Combines date and time into a single value.
```sql
SELECT TIMESTAMP('2024-07-10', '10:20:30') AS DateTime;
-- Output: 2024-07-10 10:20:30
```
4. Aggregate Functions
Functions to perform calculations on a set of values and return a single value.
COUNT
Returns the number of rows that match a specified condition.
```sql
SELECT COUNT(*) AS TotalRows FROM Students;
```
SUM
Returns the sum of a numeric column.
```sql
SELECT SUM(Age) AS TotalAge FROM Students;
```
AVG
Returns the average value of a numeric column.
```sql
SELECT AVG(Age) AS AverageAge FROM Students;
```
MAX
Returns the maximum value in a set of values.
```sql
SELECT MAX(Age) AS MaxAge FROM Students;
```
MIN
Returns the minimum value in a set of values.
```sql
SELECT MIN(Age) AS MinAge FROM Students;
```
5. Conditional Functions
Functions to perform actions based on conditions.
CASE
Returns values based on conditions.
```sql
SELECT
StudentID,
CASE
WHEN Age < 20 THEN 'Teenager'
WHEN Age >= 20 AND Age < 30 THEN 'Young Adult'
ELSE 'Adult'
END AS AgeGroup
FROM Students;
```
COALESCE
Returns the first non-null value in a list of expressions.