<!--

<style>

html, body, .ui-content {
background-color: #222121;
color: #ddd;
}

::selection {
background-color: #d46b95;
color: black;
}

.markdown-body:not(.next-editor) pre {
padding: 16px;
background-color: #353535;
color: #e0def4;
}
.markdown-body:not(.next-editor) strong {
color: #d46b95;
}
.markdown-body:not(.next-editor) em {
color: #fbaa77;
}
.markdown-body:not(.next-editor) code {
color: #eee;
background-color: #353535;
}
.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
color: #dfdad9;

}

.markdown-body h1:hover,
.markdown-body h2:hover,
.markdown-body h3:hover,
.markdown-body h4:hover,
.markdown-body h5:hover,
.markdown-body h6:hover {
color: #ffff90;
}

.markdown-body hr {
height: 0em;
padding: 0;
margin: 24px 0;
background-color: #212121;
border: 0;
border-top: 3px dashed #8c8b8b;
}

pre {
background-color:#353535;

}

code {
background-color:#353535
}

.hljs-keyword {
color: #ffaacc;
}

.hljs-type {
color: #9ccfd8
}
.hljs-number {
color: #fbaa77
}

.hljs-operator {
color: #fbea77
}

.hljs-string {
color: #a3be8c
}

.hljs-built_in {
color: #fbea77
}

.markdown-body h1,
.markdown-body h2 {
border-bottom-color: #ffffff69;
}

.markdown-body h1 .octicon-link,
.markdown-body h2 .octicon-link,
.markdown-body h3 .octicon-link,i
.markdown-body h4 .octicon-link,
.markdown-body h5 .octicon-link,
.markdown-body h6 .octicon-link {
color: #fff;
}

.markdown-body img {
background-color: transparent;
}

.ui-toc-dropdown .nav>.active:focus>a, .ui-toc-dropdown .nav>.active:hover>a, .ui-toc-dropdown .nav>.active>a {
color: white;
border-left: 2px solid white;
}

.expand-toggle:hover,
.expand-toggle:focus,
.back-to-top:hover,
.back-to-top:focus,
.go-to-bottom:hover,
.go-to-bottom:focus {
color: white;
}

.ui-toc-dropdown {
background-color: #212121;
}

.ui-toc-label.btn {
background-color: #191919;
color: white;
}

.ui-toc-dropdown .nav>li>a:focus,
.ui-toc-dropdown .nav>li>a:hover {
color: white;
border-left: 1px solid white;
}

.markdown-body blockquote {
color: #bcbcbc;
}

.markdown-body table tr {
background-color: #5f5f5f;
}

.markdown-body table tr:nth-child(2n) {
background-color: #4f4f4f;
}

.markdown-body code,
.markdown-body tt {
color: #eee;
background-color: rgba(230, 230, 230, 0.36);
}

a,
.open-files-container li.selected a {
color: #5EB7E0;
}

</style> -->

---

## Relational Query Language
* Query language is employed to request information from the database.
* Operate at a level higher than standard programming languages.

### Categories of Query Languages

**Imperative Query Language:**
Perform sequence of operations to get desired result, state variables are updated.

**Functional Query Language:**
Computation expressed as evaluation of functions, doesn't update db state.
- 

**Declarative Query Language:**
No sequence of steps or functional calls, computation described using mathematical logic.

## Relational Algebra

Relational Algebra is a procedural language comprising operations that take one or two relations as input and produce a new relation as their result. Key aspects include:

- Allows users to specify basic retrieval requests or queries.
- The result of an operation is a new relation, possibly formed from one or more input relations.
- The algebra is "closed," meaning all objects are relations.
- Operations produce new relations, further manipulable using the same algebra.
- A sequence of operations forms a relational algebra expression, yielding a relation representing the result of a database query.

### Operators
**Unary Operators:**

**Select (σ):**
- Selects tuples that satisfy a given predicate.
- Notation: σ_p\(r\), where p is the selection predicate.
- Commutative
- Horizontal partition
- 

**Project (π):**
- Returns its argument relation with certain attributes left out.
- Notation: π_{A1, A2, ..., Ak}\(r\), where A1, A2, ..., Ak are attribute names.
- not commutative
- Verical partition
- Duplicates removed
- 

**Rename (ρ):**
- Provides a name for the result of relational-algebra expressions.
- Notation: ρ_x\(E\) or ρ_{x(A1, A2, .. An)}\(E\).
- 
- 

**Assignment Operator (←):**
- Allows writing relational-algebra expressions by assigning parts of it to temporary relation variables.
- 

**Binary Operators:**

- Union (U)
- Intersection (∩)
- Set Difference (–)
- Cartesian Product (X)
- Join (⋈)

## The Cartesian-Product Operation

- The Cartesian-product operation (denoted by X) combines information from any two relations.
- Example: the Cartesian product of the relations `instructor` and `teaches` is written as:
- `instructor X teaches`
- A tuple is constructed for each possible pair of tuples: one from the `instructor` relation and one from the `teaches` relation.
- Attributes with the same name in both relations are distinguished by attaching the name of the relation from which the attribute originally came.
- Example: `instructor.ID` and `teaches.ID`

**The Join Operation**

- The join operation combines a select operation and a Cartesian-Product operation into a single operation.
- Consider relations `r (R)` and `s (S)`.
- Let "theta" be a predicate on attributes in the schema `R union S`. The join operation `r ⋈𝜃 s` is defined as:
- $𝑟⋈_𝜃𝑠 = 𝜎_𝜃 (𝑟 × 𝑠)$

## What are Set Operations?

- **Union Operation U**
- **Set-Intersection Operation ∩**
- **Set Difference Operation ─**

### Set Operations: Union Operation

- The union operation combines two relations.
- Notation: `r U s`
- For `r U s` to be valid:
1. `r` and `s` must have the same arity (same number of attributes).
2. Attribute domains must be compatible (e.g., 2nd column of `r` deals with the same type of values as the 2nd column of `s`).

**Example:**
Find all courses taught in the Fall 2017 semester or in the Spring 2018 semester or both.

**Query:**
$$ \Pi_{\text{course_id}}(\sigma_{\text{semester="Fall"} \land \text{year=2017}}(\text{section})) \cup \Pi_{\text{course_id}}(\sigma_{\text{semester="Spring"} \land \text{year=2018}}(\text{section})) $$

**Table:**

- The result includes all tuples in either `R` or `S` or both. Duplicate tuples are eliminated.
- `R` and `S` must be "type compatible" (or UNION compatible).
- Each pair of corresponding attributes must be type compatible.

### Set Operations: Intersection Operation

- The set-intersection operation finds tuples in both input relations.
- Notation: $r \cap s$
- Example: Find courses taught in both Fall 2017 and Spring 2018 semesters.
- Query: $\Pi_{\text{course_id}}(\sigma_{\text{semester="Fall"} \land \text{year=2017}}(\text{section})) \cap \Pi_{\text{course_id}}(\sigma_{\text{semester="Spring"} \land \text{year=2018}}(\text{section}))$

### Set Operations: Set Difference

- The set-difference operation finds tuples in one relation but not in another.
- Notation: \( r - s \)
- Set differences must be taken between compatible relations.
- Example: Find courses taught in Fall 2017 but not in Spring 2018.
- Query: $$ \Pi_{\text{course_id}}(\sigma_{\text{semester="Fall"} \land \text{year=2017}}(\text{section})) - \Pi_{\text{course_id}}(\sigma_{\text{semester="Spring"} \land \text{year=2018}}(\text{section})) $$

### Equivalent Queries

- There is more than one way to write a query in relational algebra.
- Example: Find information about courses taught by Physics department instructors with a salary greater than $90,000.
- **Query 1:** $$ \sigma_{\text{dept_name="Physics"} \land \text{salary > 90,000}}(\text{instructor}) $$
- **Query 2:** $$ \sigma_{\text{dept_name="Physics"}}(\text{instructor}) \cap \sigma_{\text{salary > 90,000}}(\text{instructor}) $$

- There is more than one way to write a query in relational algebra.
- Example: Find information about courses taught by Physics department instructors.
- **Query 1:** $$ \sigma_{\text{dept_name="Physics"}}(\text{instructor} \bowtie \text{instructor.ID = teaches.ID teaches}) $$
- **Query 2:** $$ \sigma_{\text{dept_name="Physics"}}(\text{instructor}) \bowtie \text{instructor.ID = teaches.ID teaches} $$

**Schema:**
- **Instructor {ID, name, dept name, salary}**
- **teaches (ID, course id, sec id, semester, year)**

## Aggregate Functions:

Aggregate functions take a collection (a set or multiset) of values as input and return a single value. There are five types of aggregate functions:

1. AVERAGE
2. MINIMUM
3. MAXIMUM
4. SUM
5. COUNT

### Types of Aggregate Functions

- A type of request that cannot be expressed in basic relational algebra
- Used in simple statistical queries that summarize information from the database tuples.

### Aggregate Functions and Grouping

- Use of the Aggregate Functional operation ℱ:
- $ℱ_{MAX Salary}$ (EMPLOYEE) retrieves the maximum salary value from the EMPLOYEE relation.
- $ℱ_{MIN Salary}$ (EMPLOYEE) retrieves the minimum salary value from the EMPLOYEE relation.
- $ℱ_{SUM Salary}$ (EMPLOYEE) retrieves the sum of salary values from the EMPLOYEE relation.
- $ℱ_{COUNT SSN}$, $ℱ_{AVERAGE Salary}$ (EMPLOYEE) computes the count (number) of employees & their average salary.
- Grouping can be combined with Aggregate Functions. For example, for each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY.
- Grouping attribute placed to left of symbol
- Aggregate functions to right of symbol

### Count Function

- Count function is used to count the number of tuples in a relation.
- It can work on both numeric and non-numeric data types.

### Sum Function
- Sum function is used to calculate the sum of all selected columns.
- It works on numeric fields only.

### Average Function
- Average function is used to calculate the average value of the numeric type.
- It returns the average of all **non-Null values.**

### Minimum Function and Maximum Function
- MIN function is used to find the minimum value of a certain column.
[image](https://hackmd.io/_uploads/By--BQ78p.png)

- MAX function is used to find the maximum value of a certain column.
[image](https://hackmd.io/_uploads/SyC-B7X8a.png)

---

### Grouping

- In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation, where the subgroups are based on some attribute values.
- For example, finding the average salary of employees in each department.
- SQL has a GROUP BY clause for this purpose.

[image](https://hackmd.io/_uploads/By6b8mm8p.png)

[image](https://hackmd.io/_uploads/rJbQUXQUT.png)

[image](https://hackmd.io/_uploads/BJ0FImXLp.png)

---

## SQL Language

- SQL is considered one of the major reasons for the commercial success of relational databases.
- The term "SQL" comes from the word "SEQUEL," which was the original term used in the paper "SEQUEL TO SQUARE" by Chamberlin and Boyce. IBM could not copyright that term, so they abbreviated it to SQL and copyrighted the term SQL.
- Now popularly known as "Structured Query Language."
- SQL is a practical rendering of the relational data model with syntax.

### Types of Database Languages

- **Data Definition Language (DDL):** Used to define the database structure or schema.
- **Data Manipulation Language (DML):** Used for managing data within schema objects.
- **Data Control Language (DCL):** Abstract of Data Control Language, including commands such as GRANT, concerned with rights, permissions, and other controls of the database system.
- **Transaction Control Language (TCL):** Used to run the changes made by the DML statement.

## Schema Concepts in SQL

- SQL schema is identified by a schema name and includes an authorization identifier and descriptors for each element.
- Schema elements include tables, constraints, views, domains, and other constructs.
- QL ends with a semicolon.

### CREATE SCHEMA Statement

CREATE SCHEMA Lib AUTHORIZATION 'Jsmith';

CREATE TABLE Lib.Books (
  book_id INT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255)
);

## Catalog

- Named collection of schemas in an SQL environment.
- SQL also has the concept of a cluster of catalogs.

## Attribute Data Types in SQL

### Basic Data Types

#### Numeric Data Types

- Integer numbers: **INTEGER, INT, and SMALLINT.**
- Floating-point (real) numbers: **FLOAT or REAL, and DOUBLE PRECISION.**

#### Character-string Data Types

- Fixed length: **CHAR(n), CHARACTER(n).**
- Varying length: **VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n).**

#### Bit-string Data Types

- Fixed length: **BIT(n).**
- Varying length: **BIT VARYING(n).**

#### Boolean Data Type

- Values of **TRUE or FALSE or NULL.**

#### DATE Data Type

- Components are **YEAR, MONTH, and DAY in the form YYYY-MM-DD.**

### Additional Data Types

#### Timestamp Data Type

- Includes the **DATE and TIME(HH:MM:SS) fields.**
- Plus a minimum of **six positions for decimal fractions of seconds.**
- Optional WITH TIME ZONE qualifier.

#### INTERVAL Data Type

- Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp.
- DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison.

SELECT INTERVAL 2 YEAR + INTERVAL 3 MONTH + INTERVAL 5 DAY;

### Attribute Data Types in SQL Explained

- The SQL standard supports a variety of built-in types, including:
- char(n): A fixed-length character string with a user-specified length n.
- varchar(n): A variable-length character string with a user-specified maximum length n.
- int: An integer (a finite subset of the integers that is machine-dependent).
- smallint: A small integer (a machine-dependent subset of the integer type).

### Attribute Data Types in SQL Explained

- Numeric(p, d): A fixed-point number with user-specified precision.(p is total number of digits and d is after decimal)
- numeric(3,1) allows 44.5
- Real, double precision: Floating-point and double-precision floating-point numbers with machine-dependent precision.
- Float(n): A floating-point number with a precision of at least n digits.
- Null: Each type may include a special value called the null value.
- ENUM: Data type in MySQL, a string object allowing limitation of the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, meaning each column may have one of the specified possible values.
- ENUM ('value_1', 'value_2', 'value_3')

> Float, double and real use floating point representation, and the parameters represents the format of the number in queries, whereas numeric and decimal use fixed point noation ot store numbers*

### Single Example covers all data types

  CREATE TABLE example (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  char_column CHAR(50) NOT NULL,
  varchar_column VARCHAR(255) NOT NULL,
  tinytext_column TINYTEXT NOT NULL,
  text_column TEXT NOT NULL,
  mediumtext_column MEDIUMTEXT NOT NULL,
  longtext_column LONGTEXT NOT NULL,
  binary_column BINARY(20) NOT NULL,
  varbinary_column VARBINARY(20) NOT NULL,
  tinyblob_column TINYBLOB NOT NULL,
  blob_column BLOB NOT NULL,
  mediumblob_column MEDIUMBLOB NOT NULL,
  longblob_column LONGBLOB NOT NULL,
  enum_column ENUM('value1', 'value2', 'value3') NOT NULL,
  set_column SET('value1', 'value2', 'value3') NOT NULL,
  bool_column BOOLEAN NOT NULL DEFAULT 0,
  date_column DATE NOT NULL,
  datetime_column DATETIME NOT NULL,
  timestamp_column TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  time_column TIME NOT NULL,
  year_column YEAR(4) NOT NULL,
  float_column FLOAT(10,6) NOT NULL DEFAULT '0.000000',
  double_column DOUBLE(10,6) NOT NULL DEFAULT '0.000000'
);

### Char vs Varchar

- The char data type stores fixed-length strings.
- For example, if an attribute A of type char(10) stores a string "Avi," seven spaces are appended to make it 10 characters long.
- In contrast, if attribute B of type varchar(10) stores "Avi," no spaces would be added.

### Large-Object Types

- Many current-generation database applications need to store attributes that can be large (of the order of many kilobytes), such as a photograph, or very large (of the order of many megabytes or even gigabytes), such as a high-resolution medical image or video clip.
- SQL provides large-object data types for character data (clob) and binary data (blob).

### Advanced Data Types like CLOB, BLOB

- For example, we may declare attributes book review clob(10KB), image blob(10MB), movie blob(2GB).
- For result tuples containing large objects (multiple megabytes to gigabytes), it is inefficient or impractical to retrieve an entire large object into memory.
- LOBs in the database are stored in a way that optimizes space and provides efficient access within the database tablespaces.
- Internal LOBs (BLOBs, CLOBs) also provide transactional support (Commit, Rollback, and so on) of the database server.

### BLOB vs CLOB

- **Binary Large Object (BLOB):**
- Stores any kind of data in binary format such as images, audio, and video.
- **Character Large Object (CLOB):**
- Stores string data in the database having character set format. Used for a large set of characters/strings or documents that use the database character.

### Advanced Data Types like CLOB, BLOB

#### Blob

- The full form of Blob is a Binary Large Object.
- This is used to store large binary data.
- Stores values in the form of binary streams.
- Using this, you can store files like videos, images, gifs, and audio files.
- MySQL supports this with the following datatypes: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.

#### Clob

- The full form of Clob is Character Large Object.
- This is used to store large textual data.
- Stores values in the form of character streams.
- Using this, you can store files like text files, PDF documents, word documents, etc.
- MySQL supports this with the following datatypes: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

### Domains in SQL

- **Domain:**
- Name used with the attribute specification.
- Makes it easier to change the data type for a domain used by numerous attributes.
- Improves schema readability.
- Example: `CREATE DOMAIN SSN_TYPE AS CHAR(9)`.


CREATE TYPE type_name [UNDER supertype_name] AS (
  attribute_1 data_type,
  attribute_2 data_type,
  ...
);

### SQL Data Definition, Data Types, Standards

#### Terminology:

- Table, row, and column used for relational model terms relation, tuple, and attribute respectively.
- CREATE statement:
- Main SQL command for data definition.
- The language has features for Data definition, Data Manipulation, Transaction control, Indexing, Security specification (Grant and Revoke), Active databases, Multi-media, Distributed databases, etc.

### Data Definition Language (DDL)

- The SQL DDL provides commands for defining table schemas, deleting tables, and modifying table schemas.
- DDL includes commands such as CREATE, ALTER, and DROP statements.
- DDL is used to CREATE, ALTER, OR DROP the database objects (Table, Views, Users).
- **CREATE:**
- To create objects in the database.
- **ALTER:**
- Alters the structure of the database.
- **DROP:**
- Deletes objects from the database.
- **TRUNCATE:**
- Removes all records from a table, including all spaces allocated for the records (removes complete data from an existing table, but the table structure remains).
- **COMMENT:**
- Adds comments to the data dictionary.
- **RENAME:**
- Renames an object.

#### CREATE TABLE

- **Syntax:**

  CREATE TABLE table_name (
    Col_name1 datatype(),
    Col_name2 datatype(),
    ...
    Col_namen datatype(),
  );

- **Other:**
- Create table from an existing table:

    CREATE TABLE new_table_name AS SELECT * FROM existing_table;

    CREATE TABLE new_table_name LIKE existing_table;)

#### ALTER TABLE

1. **ADD:**
- **Syntax:**
- `ALTER TABLE table_name ADD Col_name datatype()...;`
- can put after and tell which column
2. **MODIFY:**
- **Syntax:**
- `ALTER TABLE table_name MODIFY (column_name datatype()...);`

3. **DROP:**
- **Syntax:**
- `ALTER TABLE table_name DROP COLUMN column_name;`

4. **RENAME:**
- **Syntax:**
- `ALTER TABLE table_name RENAME COLUMN (Old_fieldname TO New_fieldname...);`

5. **DESCRIBE TABLE**

- **Syntax:**
- `DESCRIBE table_name`

6. **SHOW CREATE TABLE:**

- **Syntax:**
- `SHOW CREATE TABLE table_name;`
- Lists complete information of the table including constraints, index, etc.

7. **DROP TABLE**

- **Syntax:**
- `DROP TABLE table_name;`
- Complete table structure will be dropped.

8. **RENAME**

- **Syntax:**
- `RENAME TABLE table_name TO new_table_name;`

9. **TRUNCATE**

- **Syntax:**
- `TRUNCATE TABLE table_name;`
- Delete complete data from an existing table. Table structure remains.

---

## Specifying Constraints in SQL

- Constraints in SQL refer to the conditions and restrictions applied on the database.
- Before inserting data into the database, conditions are checked.
- Data will be inserted only if the applied condition holds true.

### Basic constraints:

1. **Key constraint:** A primary key value cannot be duplicated.
2. **Entity Integrity Constraint:** A primary key value cannot be null.
3. **Referential integrity constraints:** The "foreign key" must have a value already present as a primary key or may be null.

#### Other Restrictions on attribute domains:

- Default value of an attribute: `DEFAULT <value>`
- NULL is not permitted for a particular attribute (`NOT NULL`)
- CHECK clause: `Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21)`

### PRIMARY KEY clause

- Specifies one or more attributes that make up the primary key of a relation.
- Example: `Dnumber INT PRIMARY KEY;`

### UNIQUE clause

- Specifies alternate (secondary) keys.
- Example: `Dname VARCHAR(15) UNIQUE;`

### FOREIGN KEY clause

- Default operation: reject update on violation.
- Attach referential triggered action clause.
- Options include SET NULL, CASCADE, and SET DEFAULT.

Using the Keyword `CONSTRAINT`:

- Name a constraint for later altering.
- Example:

 CREATE TABLE EMPLOYEE (
...,
Dno INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK PRIMARY KEY (Ssn),
CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE
 );

**Example for Specifying Constraints in SQL**

CREATE TABLE DEPT_LOCATIONS (
  Dnumber INT NOT NULL,
  Dlocation VARCHAR(15) NOT NULL DEFAULT 'Banglore',
  PRIMARY KEY (Dnumber, Dlocation),
  CONSTRAINT fk_dno FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
);

---
### Referential actions

**Delete actions of rows in the parent table:**
- ON DELETE NO ACTION
- ON DELETE CASCADE
- ON DELETE SET NULL
- ON DELETE SET DEFAULT

**Update action of rows in the parent table:**
- ON UPDATE NO ACTION
- ON UPDATE CASCADE
- ON UPDATE SET NULL
- ON UPDATE SET DEFAULT

---

### Specifying Constraints on Tuples Using CHECK

Additional Constraints on individual tuples within a relation using CHECK.
Example: `CHECK (Dept_create_date <= Mgr_start_date);`

### Schema Change Statements in SQL

Schema evolution commands: adding or dropping tables, attributes, constraints, and other schema elements.
Example: `ALTER TABLE people ADD COLUMN Job VARCHAR(12);`

### The DROP Command

`DROP` command is used to drop named schema elements (tables, domains, types, or constraints).
Options: `CASCADE` and `RESTRICT`.
Example: `DROP SCHEMA COMPANY CASCADE;`
If a base relation within a schema is no longer needed, the relation and its definition can be deleted.
Example: `DROP TABLE DEPENDENT CASCADE;`

### MySql Commands

To create a new database in MySQL: `CREATE DATABASE [IF NOT EXISTS] database_name`
Example: `CREATE DATABASE employeedb;`

To show the created database in MySQL: `SHOW CREATE DATABASE employeedb;`
To check created databases: `SHOW DATABASES;`

### Describe in SQL

The `DESCRIBE` command in MySQL provides a detailed overview of a table's structure.
- `DESCRIBE table_name;`
- `SHOW COLUMNS FROM table_name;`
- `DESC table_name;`

### The USE command in MySQL

The `USE` command is used to select a particular database: `USE database_name;`

### The ALTER table command

Alter table actions include:
- Adding or dropping a column (attribute).
- Changing a column definition.
- Adding or dropping table constraints.
Example: `ALTER TABLE people ADD COLUMN Job VARCHAR(12);`

**Alter table actions:**
1. ADD a column in the table.
2. Add multiple columns in the table.
3. MODIFY column in the table.
4. DROP column in the table.
5. RENAME column in the table.
6. RENAME table.
7. Altering (Changing) a Column Definition or a Name.

### Change the column position of MySQL table

To change the column position of MySQL table without losing column data:
`ALTER TABLE yourTableName MODIFY yourColumnName1 data type AFTER yourColumnName2;`

### Changing a Column’s Default Value

To change a column's default value:
- `ALTER TABLE table_name MODIFY c CHAR(10);`
- `ALTER TABLE table_name MODIFY j BIGINT NOT NULL DEFAULT 100;`
- `ALTER TABLE table_name ALTER i SET DEFAULT 1000;`

### Adding and Dropping Constraints

Change constraints specified on a table.
Add or drop a named constraint.
Example:
- Adding constraint: `ALTER table people add constraint people_gender_fk foreign key (gender) references gender_tab(gender);`
- Dropping constraint: `ALTER TABLE Persons DROP CONSTRAINT PK_Person;`

### Dropping Columns, Default Values

To drop a column:
`ALTER TABLE EMPLOYEE DROP COLUMN Address CASCADE;`

Default values can be dropped and altered:
- `ALTER TABLE DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;`
- `ALTER TABLE DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT '333445555';`

### Drop table

To remove a table from an SQL database:
- `DROP TABLE table1;`
- Options: `DROP TABLE IF EXISTS table1;`
- Options for multiple tables: `DROP TABLE IF EXISTS table1, table2, table3;`

### Truncate table

To remove all rows (complete data) from a table:
`TRUNCATE TABLE table_name;`

### Drop vs Truncate

### Rename table

To rename a table:
`RENAME TABLE table_name to new_table_name;`
Example: `RENAME TABLE products TO products_old, products_new TO products;`

---

## View

- Views in SQL are considered as virtual tables. A view also contains rows and columns.
- To create a view, we can select fields from one or more tables present in the database.
- A view can either have specific rows based on a certain condition or all the rows of a table.
- We define a view in SQL by using the `CREATE VIEW` command: `CREATE VIEW v AS <query expression>;` where `<query expression>` is any legal query expression. The view name is represented by `v`.

### Use Cases of View

- **Consider a clerk who needs to access all data in the instructor table, except salary.**

  CREATE VIEW faculty AS
  SELECT ID, name, dept_name FROM instructor;

- The view table conceptually contains the tuples in the query result, but it is not precomputed and stored.
- The database system stores the query expression associated with the view table.
- Whenever the view table is accessed, its tuples are created by computing the query result.

**Create a view that lists all course sections offered by the Physics department in the Fall 2017 semester with the building and room number of each section.**

CREATE VIEW physics_fall_2017 AS
SELECT course.course_id, sec_id, building, room_number
FROM course, section
WHERE course.course_id = section.course_id
AND course.dept_name = 'Physics'
AND section.semester = 'Fall';

**Retrieve all Physics courses offered in the Fall 2017 semester in the Watson building**

SELECT course_id
FROM physics_fall_2017
WHERE building = 'Watson';

**Retrieve the course ID and room number of all Physics courses offered in the Fall 2017 semester in the Watson building.**

CREATE VIEW physics_fall_2017_watson AS
SELECT course_id, room_number
FROM physics_fall_2017
WHERE building = 'Watson';

### Advantages of View

- **Complexity:** Views help to reduce complexity. Different views can be created on the same base table for different users.
- **Security:** It increases security by excluding sensitive information from the view.
- **Query Simplicity:** It helps to simplify commands from the user. A view can draw data from several different tables and present it as a single table.
- **Consistency:** A view can present a consistent, unchanged image of the structure of the database. Views can be used to rename columns without affecting the base table.
- **Data Integrity:** If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.
- **Storage Capacity:** Views take very little space to store the data.
- **Logical Data Independence:** Views can make the application and database tables to a certain extent independent.

### Disadvantages of View

- You cannot `INSERT` if the base table has any `NOT NULL` columns that do not appear in the view.
- You cannot `INSERT` or `UPDATE` if any of the columns referenced in the `INSERT` or `UPDATE` contains group functions or columns defined by an expression.
- You can't execute `INSERT`, `UPDATE`, `DELETE` statements on a view if with `READ ONLY` option is enabled.
- You can't create a view using temporary tables.
- You cannot `INSERT`, `UPDATE`, or `DELETE` if the view contains group functions `GROUP BY`, `DISTINCT`, or a reference to a pseudo column `row_num`.
- You can't pass parameters to the SQL server views.
- You can't associate rules and defaults with views.

### Add User

    CREATE USER 'user'@'localhost'
    IDENTIFIED BY 'password';

### Granting privileges

- MySQL has a feature that provides many control options to the administrators and users on the database.
- Now, we are going to learn about granting privileges to a user account. MySQL provides `GRANT` statements to give access rights to a user account.
- The SQL standard includes the privileges `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.
- To grant privileges to a user: `GRANT <privilege list> ON <table name or view name> TO <user/role list>;`

### Checking privileges

- **To check the privileges assigned to a specific user, use the following command**
- Command: `SHOW GRANTS FOR user_name;`
- If we want to assign all privileges to all databases in the current server
- Command: `GRANT ALL ON mystudentdb.* TO user_name;`

### Granting privileges list

- We can choose access rights from the below list on which privileges can be applied:
- `SELECT`: Enables us to view the result set from a specified table.
- `INSERT`: Enables us to add rows in a given table.
- `DELETE`: Enables us to remove rows from a table.
- `CREATE`: Enables us to create tables/schemas.
- `ALTER`: Enables us to modify tables/schemas.
- `UPDATE`: Enables us to modify a table.
- `DROP`: Enables us to drop a table.
- `INDEX`: Enables us to create indexes on a table.
- `ALL`: Enables us to give ALL permissions except GRANT privilege.
- `GRANT`: Enables us to change or add access rights.

**You can only grant others the permissions that you already have**

### Revoking privileges

- **To revoke an authorization, we use the REVOKE statement. It takes a form almost identical to that of GRANT.**
- Command: `REVOKE <privilege list> ON <table name or view name> FROM <user/role list>;`

---

## Database Modification

DML commands in Structured Query Language change the data present in the SQL database. We can easily access, store, modify, update, and delete the existing records from the database using DML commands.

### DML Commands:
- **SELECT Command**

### SELECT COMMAND

- The SELECT clause lists the attributes desired in the result of a query.
- It corresponds to the projection operation of relational algebra.
- The FROM clause is used to specify the table name or the relation name.
- Example: Find the names of all instructors:

  SELECT fname, Lname
  FROM EMPLOYEE;

### INSERT COMMAND

* In its simplest form, INSERT is used to add a single tuple (row) to a relation (table).
* We must specify the relation name and a list of values for the tuple.
* There are two forms of the INSERT statement.

**Form 1:**
The values should be listed in the same order in which the corresponding attributes were specified in the `CREATE TABLE` command.
Syntax:

INSERT INTO EMPLOYEE VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', '98 Oak Forest, Katy, TX', 'M', 37000, '653298653', 4);

- Allows the user to specify explicit attribute names that correspond to the values provided in the INSERT command.
- This is useful if a relation has many attributes but only a few of those attributes are assigned values in the new tuple.
- However, the values must include all attributes with NOT NULL specification and no default value.
- Attributes with NULL allowed or DEFAULT values are the ones that can be left out.
Syntax:

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn) VALUES ('Richard', 'Marini', 4, '653298653');

**Another variation of the INSERT command:**

A variation of the INSERT command inserts multiple tuples into a relation in conjunction with creating the relation and loading it with the result of a query.
Example:

INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

**Another variation:**

We can use the command given below to backup the data present in a table. The condition is that the schema of both the backup table and the original should must be the same.
Example:

INSERT INTO WORKS_ON_INFO_Backup (SELECT * FROM WORKS_ON_INFO);

### UPDATE COMMAND

- The UPDATE command is used to modify attribute values of one or more selected tuples.
- As in the DELETE command, a WHERE clause in the UPDATE command selects the tuples to be modified from a single relation.
- An additional SET clause in the UPDATE command specifies the attributes to be modified and their new values.
- Several tuples can be modified with a single UPDATE command.

**Syntax:**

UPDATE PROJECT SET Plocation = 'Bellaire', Dnum = 5 WHERE Pnumber = 10;

Give all employees in the ‘Research’ department a 10% raise in salary.

UPDATE EMPLOYEE SET Salary = Salary * 1.1 WHERE Dno = 5;

### DELETE COMMAND

- The DELETE command removes tuples from a relation.
- It includes a WHERE clause, similar to that used in an SQL query, to select the tuples to be deleted.
- Tuples are explicitly deleted from only one table at a time.
- However, the deletion may propagate to tuples in other relations if referential triggered actions are specified in the referential integrity constraints of the DDL.
- Depending on the number of tuples selected by the condition in the WHERE clause, zero, one, or several tuples can be deleted by a single DELETE command.
- A missing WHERE clause specifies that all tuples in the relation are to be deleted. However, the table remains in the database as an empty table.
- We must use the DROP TABLE command to remove the table definition.

DELETE FROM EMPLOYEE WHERE Lname = 'Zelaya';

Delete all employee records [It is similar to truncating the employee table]

DELETE FROM EMPLOYEE;

---

## DML :Additional


**SQL Query Writing: order of writing**
The order of writing SQL queries refers to the sequence in which you write the various parts of a query to retrieve data from a database. Here is a brief overview of the order in which different parts of an SQL query are typically written:
* SELECT: This is usually the first keyword in a SQL query and is used to specify the columns or expressions that you want to retrieve from the database.
* FROM: This is the next keyword in the query and is used to specify the table or tables from which you want to retrieve the data.
* JOIN: If you want to combine data from multiple tables, you use the JOIN keyword to specify the type of join you want to perform and the tables you want to join.
* WHERE: This keyword is used to filter the data based on specific criteria. You can specify one or more conditions to filter the data.
* GROUP BY: This keyword is used to group the data based on one or more columns in the SELECT statement. You can also use aggregate functions like SUM or COUNT to perform calculations on the grouped data.
* HAVING: This keyword is used to filter the grouped data based on specific criteria. It works similarly to the WHERE keyword but filters the data after it has been grouped.
* ORDER BY: This keyword is used to sort the data based on one or more columns. You can sort the data in ascending or descending order.
* LIMIT/OFFSET: These keywords are used to limit the number of rows returned by the query or to skip a certain number of rows.
It's important to note that the order of writing SQL queries is not always rigid and can vary depending on the specific query and the database being used. However, following a standard order can make your queries more organized and easier to understand.

### Select-FROM-WHERE Structure

- **SELECT**
- [ALL | DISTINCT | DISTINCTROW ]
- [HIGH_PRIORITY]
- [ FROM table_references [PARTITION partition_list] ]
- [ WHERE where_condition]
- [ GROUP BY \{col_name | expr | position\}]
- [ HAVING where_condition [ORDER BY \{col_name | expr | position\} [ASC | DESC] ]
- [ LIMIT \{ [offset,] row_count | row_count OFFSET offset \} ]

### Select & From clause

- The SELECT clause lists the attributes desired in the result of a query.
- Also returns NULL values.
- It corresponds to the projection operation of relational algebra.
- The FROM clause is used to specify the table name or the relation name.

**Example:**

  SELECT Fname, Lname FROM EMPLOYEE;

### Select & Distinct clause

- SQL allows duplicates in relations as well as in query results.
- To force the elimination of duplicates, insert the keyword DISTINCT after SELECT.

SELECT DISTINCT Fname, Lname, Address FROM EMPLOYEE;

### Select clause

- The SELECT clause can contain arithmetic expressions involving the operations, +, –, *, / and operating on constants or attributes of tuples.

SELECT fname, salary/2 FROM EMPLOYEE;

### Where clause

- The WHERE clause specifies conditions that the result must satisfy.
- It corresponds to the selection predicate of relational algebra

SELECT fname, salary FROM employee WHERE dno = 5 AND salary < 70000;

### Group By clause

- The GROUP BY clause in SQL is used with SQL aggregate functions such as MIN, MAX, AVG, SUM and COUNT.
- The GROUP BY clause will return the summarized data into possible categories according to the fixed query.

SELECT COUNT(ssn), gender FROM employee GROUP BY Gender;

### Having clause

- HAVING clause in SQL is also used with SQL aggregate functions such as MIN, MAX, AVG, SUM and COUNT.
- HAVING clause is always used with GROUP BY clause.

SELECT COUNT(ssn), dno FROM employee GROUP BY dno HAVING COUNT(ssn) > 2;

### Order By clause

- ORDER BY clause is a simple keyword that can be used with SQL aggregate functions, HAVING clause, or GROUP BY clause.
- The main function of the ORDER BY clause is to sort the result in either ascending or descending order.

SELECT fname, lname, salary FROM EMPLOYEE ORDER BY salary DESC;

### Rename

- SQL allows renaming relations and attributes using the AS clause: old-name AS new-name

SELECT fname, lname, salary*12 AS Annual_Income FROM EMPLOYEE;

### String Comparison

- SQL includes a string-matching operator for comparisons on character strings.
- The operator LIKE uses patterns that are described using two special characters:
- percent (%): The % character matches any substring.
- underscore (_): The _ character matches any character.

SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE '%Houston,TX%';

>Match the string “100%” like '100 %' escape '\’ in that above we use backslash () as an escape character

*Not case Sensitive*

---

## SET Operators in SQL

### Types of Set Operations:

1. **Union**
2. **Union All**
3. **Intersect**
4. **Intersect All**
5. **Except**
6. **Except All**

#### **Union**

- The SQL UNION operation is used to combine the results of two or more SQL SELECT queries.
- In the UNION operation, the **number of columns** and **datatypes** must be the **same** in both the tables on which the UNION operation is being applied.
- It **eliminates duplicate rows** from its result set.

SELECT grade FROM student_course
WHERE course = 'Physics'
UNION
SELECT grade FROM student_course
WHERE course = 'Mathematics';

#### **Union All**

- The UNION ALL operation is similar to the UNION operation. It **returns the set without removing duplicates** and sorting the data.

SELECT DISTINCT grade FROM student_course
WHERE course = 'Physics'
UNION ALL
SELECT DISTINCT grade FROM student_course
WHERE course = 'Mathematics';

#### **Intersect**

- It is used to combine two SELECT statements.
- The INTERSECT operation returns common rows from both the SELECT statements.
- In the Intersect operation, the **number of columns** and **corresponding datatypes** must be the **same.**
- It has **no duplicates.**

SELECT grade FROM student_course
WHERE course = 'Physics'
INTERSECT
SELECT grade FROM student_course
WHERE course = 'Mathematics';

#### **Intersect All**

- INTERSECT ALL is similar to the INTERSECT operation. It helps retain duplicates.
- If a particular value is present **c1 times** in the first set(or relation) and **c2 times** in the second set(or relation), the number of duplicates or copies present in the result would be **min(c1,c2).**

SELECT grade FROM student_course
WHERE course = 'Physics'
INTERSECT ALL
SELECT grade FROM student_course
WHERE course = 'Mathematics';

### Except

- It combines the result of two SELECT statements.
- EXCEPT operator is used to display the rows that are present in the first query but absent in the second query.
- It has no duplicates.

SELECT grade FROM student_course
WHERE course = 'Physics'
EXCEPT
SELECT grade FROM student_course
WHERE course = 'Mathematics';

#### Except All

- EXCEPT ALL is similar to the EXCEPT operation. It helps retain duplicates.
- If a particular value is present **c1 times** in the first set(or relation) and **c2 times** in the second set(or relation), the number of duplicates or copies present in the result would be **max(c1-c2,0)**.

SELECT grade FROM student_course
WHERE course = 'Mathematics'
EXCEPT ALL
SELECT grade FROM student_course
WHERE course = 'Physics';

---
## Null values

- SQL has various rules for dealing with NULL values.
- Generally, NULL has one of the three representations:
- Unknown value
- Unavailable or withheld value
- Not applicable attributes
- SQL does not distinguish among the different meanings of NULL.
- Each individual NULL value is considered to be different from every other NULL value in the various database records.
- Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations.

SELECT Hours FROM WORKS_ON;
SELECT Hours + 10 FROM WORKS_ON;

If any operand in an arithmetic operation is NULL

In the second query, if Hours is NULL, the result for that row will be NULL (regardless of the constant 10 being added).

### Null values in comparison operations

- Comparisons involving NULL values pose a greater difficulty.
- To handle comparisons involving NULL values in SQL, a third logical value UNKNOWN, in addition to TRUE and FALSE, is used.
- Any comparison operation involving NULL values would result in an UNKNOWN.
- Three-valued logic truth table
- Truth table for NOT, AND, OR operators.

- If the **WHERE predicate** evaluates to **FALSE or UNKNOWN** for a tuple, the **tuple is not added to the result.**
- Tuples with **NULL values for the join attributes** are **not included** in the result **(unless it is an OUTER JOIN).**
- SQL uses comparison operators **IS** and **IS NOT** to check if an attribute value is **NULL or not**, respectively.

SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;

Is null and is not null

SELECT Essn, Pno FROM WORKS_ON WHERE Hours > 10 IS UNKNOWN;

>Only those tuples whose Hours attribute value is NULL will return UNKNOWN on performing the comparison.

### Null values in distinct clause

- When a query uses the SELECT DISTINCT clause, duplicate tuples are eliminated.
- When comparing values of corresponding attributes from two tuples, the values are treated as identical if either both are non NULL and equal in value, or both are NULL.
- DISTINCT retains only one copy of such distinct tuples.
- **Treatment of NULL above is different from the way NULL values are treated in predicates, where a comparison “NULL=NULL” would return UNKNOWN, rather than TRUE.**
- **The approach of treating tuples as identical if they have the same values for all attributes, even if some of the values are NULL, is also used for the set operations UNION, INTERSECTION, and EXCEPT.**
> Union all will have multiple NULL values if needed
Multiple null values becomes 1 null value in select distinct

---
## Aggregate functions - Introduction

Aggregate functions are used to summarize information from multiple tuples into a single-tuple summary. Grouping is used to create subgroups of tuples before summarization.

### Built-in aggregate functions:
- **COUNT:** returns the number of tuples or values specified in a query
- **SUM:** returns the sum of a set (or multiset) of numeric values
- **MAX:** returns the maximum value from a set(or multiset) of numeric values
- **MIN:** returns the minimum value from a set(or multiset) of numeric values
- **AVG:** returns the average of a set(or multiset) of numeric values

**NOTE:** Multiset is a collection similar to a set wherein the multiplicity of every value is greater than or equal to 1. It can contain duplicate values.

## Aggregate functions - Example

The functions MAX and MIN can also be used with attributes that have non-numeric domains if the domain values have a total ordering among one another. DATE, TIME, TIMESTAMP, and alphanumeric strings are examples of such non-numeric domains.

### Example:

Find the sum of salaries of all employees, the maximum salary, the minimum salary, and the average salary.

SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM EMPLOYEE;

## AS clause

The AS clause can be used to rename column names to more meaningful ones.

### Example:

SELECT SUM(Salary) AS Total_Sal, MAX(Salary) AS Highest_Sal, MIN(Salary) AS Lowest_Sal, AVG(Salary) AS Average_Sal FROM EMPLOYEE;

## COUNT - Example

### Example:
Retrieve the total number of employees in the company.

### Output:
SELECT COUNT(*) FROM EMPLOYEE;

Example: Find the number of employees in the ‘Research’ department.

SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME = 'Research';

In general, COUNT(*) can be used to find the number of rows present in the result of the query.

## Use of DISTINCT

The COUNT function can be used to count the number of values in a column as well.

### Example:

Count the number of distinct salary values in the database.

SELECT COUNT(DISTINCT Salary) FROM EMPLOYEE;

Note the usage of the DISTINCT in the previous query. To understand its significance, we shall run the same query without using DISTINCT.

SELECT COUNT(Salary) FROM EMPLOYEE;

SQL, by default, does not eliminate duplicates in the result of a query. Duplicate retention can be specified using the ALL keyword. If duplicate elimination is required, it must be explicitly specified by using the DISTINCT keyword.

## Aggregate functions – Handling of Null values

In general, when an aggregate function is applied to a collection of values, **NULL values are discarded before the calculation.** If the collection **becomes empty because all the values are NULL**, **COUNT returns zero** and **other aggregate functions return NULL.**

## Aggregate functions in nested queries

Aggregate functions can also be used in selection conditions involving nested queries. They can be used in correlated nested queries, which are then placed in the WHERE clause of an outer query.

### Example:

Retrieve the names of all employees who have two or more dependents.

SELECT Lname, Fname FROM EMPLOYEE
WHERE ( SELECT COUNT(*) FROM DEPENDENT WHERE Ssn = Essn ) >= 2;

SQL has two functions that are applied to Boolean values – SOME and ALL. The **SOME** function **returns True** if at **least one element in the collection is True.** The **ALL** function **returns True** if **all the elements in the collection are True.**

## Grouping

All tuples that have the same values for all grouping attributes are placed in the same group. If **NULLs exist in the grouping attribute**, then a **separate group is created** for all tuples with a NULL value in the grouping attribute. Aggregate functions can be applied independently to each group to produce summary information about each group.

## Group By clause

Grouping can be done in SQL using the GROUP BY clause. The GROUP BY clause specifies the grouping attributes. They should be present in the SELECT clause as well so that the value of the grouping attributes will appear in the result relation along with the aggregated values.

### Example:

For each department, retrieve the department number, the number of employees in the department, and their average salary.

SELECT Dno, COUNT(*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno;

## Having clause

**Where** : Single tuple
**Having** : Group of Tuples

For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.

SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno GROUP BY Pnumber, Pname HAVING COUNT(*) > 2;

**Where** executes first, and then **having**

## Where predicate and Having predicate

SELECT Dno, COUNT(*) FROM EMPLOYEE
WHERE Salary>40000 GROUP BY Dno HAVING COUNT(*) > 2;

The correct query is given below:

SELECT Dno, COUNT(*) FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN
( SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT(*) > 2)
GROUP BY Dno;

---

## Nested Queries

A nested query is a complete ```SELECT-FROM-WHERE``` block within another SQL query, known as the outer query. They are used to fetch values from the database for comparison in the outer query.

Nested queries can appear in various SQL clauses such as ```WHERE```, ```FROM```, ```SELECT```, etc.

SQL provides the ```IN``` operator for set membership checks and the ```NOT IN``` operator for the opposite.

Example: Retrieve the project numbers of projects involving an employee with the last name 'Smith' as a manager or worker.

Query:

SELECT DISTINCT Pnumber
FROM PROJECT WHERE Pnumber IN
( SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith' )
OR Pnumber IN
( SELECT Pno FROM WORKS_ON, EMPLOYEE
WHERE Essn = Ssn AND Lname = 'Smith' );

*Attribute type and number of attributes must match to use `IN`*

Nested queries return a table (relation) that is a set (or multiset) of tuples. In some scenarios, a nested query may return a table with a single attribute and tuple.

**For single values, we can treat the result as a scalar and use operations applicable to singular values.**

## Additional Set Comparison Operators

Apart from ```IN``` and ```NOT IN```, there are other operators of the form ```ANY | ALL | SOME``` for comparing a single value to a set or multiset.

Example:

SELECT Lname, Fname FROM EMPLOYEE
WHERE Salary > ALL ( SELECT Salary FROM EMPLOYEE WHERE Dno = 5 );

## Correlated Nested Queries

Correlated nested queries reference attributes of a relation in the outer query's WHERE clause. They are evaluated for each tuple of the outer relation.

Example:

SELECT E.Fname, E.Lname FROM EMPLOYEE AS E
WHERE E.Ssn IN
( SELECT D.Essn FROM DEPENDENT AS D WHERE E.Gender = D.Gender );

## EXISTS and UNIQUE

Example:

SELECT Fname, Lname FROM EMPLOYEE
WHERE EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn = Essn );

## Not Exists

Example: Retrieve the names of employees who have no dependents.

Query:

SELECT Fname, Lname FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn = Essn );

---

## Subqueries in the FROM Clause


SQL allows subquery expressions in the FROM clause, where a subquery returns a relation that can be inserted into a SELECT-FROM-WHERE expression.

### Using Group By & Having Clauses
Query:

SELECT Dno, ROUND(AVG(Salary),2) as avg_salary
FROM EMPLOYEE
GROUP BY Dno
HAVING AVG(Salary) > 32000;

### Using Nested Queries
Query:

SELECT Dno, avg_salary
FROM (SELECT Dno, ROUND(AVG(Salary),2) FROM EMPLOYEE GROUP BY Dno)
AS dept_avg_salary(Dno, avg_salary)
WHERE avg_salary > 32000;

### SQL Implementations
Nested subqueries in the FROM clause are supported by most SQL implementations. Some, like **MySQL** and **PostgreSQL**, **require naming subquery** relations in the **FROM** clause. (**Assigning an alias**)

SELECT Dno, avg_salary
FROM
(SELECT Dno, ROUND(AVG (salary),2)
FROM EMPLOYEE
GROUP BY Dno)
AS dept_avg_salary(Dno, avg_salary)
WHERE avg_salary > 32000;

## WITH Clause


The WITH clause defines a temporary relation available only to the query it occurs in.

### Example: Highest Hours on a Project
Query:

WITH max_work(max_hours) AS
(SELECT MAX(Hours) FROM WORKS_ON)
SELECT Essn, Pno, Hours
FROM WORKS_ON, max_work
WHERE Hours = max_hours;

![image](https://hackmd.io/_uploads/rkOXIwmU6.png)

---
## Join Expressions


-Join operations combine tables based on common columns. They are often used as subquery expressions in the FROM clause.

In relational databases, joins are commonly performed between a primary key in one table and a foreign key in another table. This relationship helps to maintain data integrity and establish meaningful connections between different tables.

![image](https://hackmd.io/_uploads/rkSr5wXIp.png)

### Natural Join
An implicit join that combines tables based on columns with the same name and data type.

#### Points to Remember:
- No need to specify column names.
- Resultant table always contains unique columns.
- Possible to perform a natural join on more than two tables.
- Do not use the ON clause.

**Example: List employees with department names.**
Query: ```SELECT employee_name, employee_salary, Department_name FROM employee NATURAL JOIN Department;```
![image](https://hackmd.io/_uploads/ByQv9vmL6.png)

**Example: List customers with loan amount and address.**
Query: ```SELECT CustName, CustID, amount, mobile, address FROM Borrower NATURAL JOIN loan NATURAL JOIN cust_info;```

### Inner Join
Combines records from two related tables based on common columns.

**Example: Inner join considering employee and department tables.**
Query: ```SELECT e.Employee_ID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e INNER JOIN department d ON e.Employee_ID = d.Employee_ID;```
![image](https://hackmd.io/_uploads/r1-Y5PQIa.png)

**Example: Inner join considering loan and borrower tables.**
Query: ```SELECT l.Loan_ID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l JOIN Borrower b ON l.Loan_ID = b.Loan_ID;```



### Outer Join

An extension of the join operation that avoids loss of information.

There are three different types of outer join in SQL:

- Left Outer Join
- Right Outer Join
- Full Outer Join

### Outer Join - LEFT Outer Join

In LEFT OUTER JOIN, every tuple in the left table must appear in the result. If it does not have a matching tuple, it is padded with NULL values for the attributes of the right table.



#### Example:

Write a query to perform a left outer join considering the employee table as the left table and the department table as the right table.

SELECT e.Employee_ID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name
FROM employee e LEFT OUTER JOIN department d ON e.Employee_ID = d.Employee_ID;

![image](https://hackmd.io/_uploads/ryiAcw7IT.png)



### Right Outer Join


#### Example:

Write a query to perform a right outer join considering the employee table as the left table and the department table as the right table.

SELECT e.Employee_ID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name
FROM employee e RIGHT OUTER JOIN department d ON e.Employee_ID = d.Employee_ID;

![image](https://hackmd.io/_uploads/HJ8MoPmUp.png)




### Full Outer Join

* If we use a full outer join to combine two different tables, then we will get all the records from both tables. We will get all the records from the left table as well as the right table. 
* MySQL doesn't support FULL OUTER JOIN directly. So to implement a full outer join in MySQL, we will execute two queries in a single query. 
* The first query will be of LEFT OUTER JOIN,
* The second query will be of RIGHT OUTER JOIN. 
* We will **combine the first and the second query with the UNION operator** to see the results of FULL OUTER JOIN.


#### Example:

Write a query to perform a full outer join considering the loan table as the left table and the borrower table as the right table.

SELECT l.Loan_ID, l.Branch, l.Amount, b.CustID, b.CustName
FROM Loan l LEFT OUTER JOIN Borrower b ON l.Loan_ID = b.Loan_ID
UNION
SELECT l.Loan_ID, l.Branch, l.Amount, b.CustID, b.CustName
FROM Loan l RIGHT OUTER JOIN Borrower b ON l.Loan_ID = b.Loan_ID;

---


## Indexes

An index on an **attribute of a relation** is a data structure that **allows the database system to find those tuples** in the relation that have a **specified value for that attribute efficiently, without scanning through all the tuples of the relation**. Indices are not required for correctness, since they are **redundant data structures.** Indices form part of the **physical schema of the database**, as **opposed to its logical schema.**

### Index Concept in SQL

However, the drawback of using indexes is that they may **slow down** the execution time of **UPDATE and INSERT** statements, but they **speed up** the execution time of **SELECT and WHERE** statements.

### Why SQL Index?

SQL Indexes can search the information of the large database quickly. This concept is a quick process for those columns, including different values. This data structure sorts the data values of columns (fields) either in ascending or descending order. When indexes are used with smaller tables, the performance of the index may not be recognized.

### Creating Indexes

Syntax:

CREATE INDEX Index_Name ON Table_Name (column_name1, column_name2, ..., column_nameN);

Example: To define an index named 'ind_department' and 'ind_gender' index on the 'employee' relation:

CREATE INDEX ind_gender ON employee(gender);

### Checking Created Indexes

To get indexes of a table in MySQL database, you can use the SHOW INDEXES command line:

Syntax:

SHOW INDEXES FROM table_name;
show indexes from employee;

![image](https://hackmd.io/_uploads/SJNI2v7Lp.png)


### Using `UNIQUE` with Index

If we wish to declare that the search key is a **candidate key**, we add the `UNIQUE` attribute to the index definition. This feature is redundant if the database system supports the unique declaration of the SQL standard.

Command:

CREATE UNIQUE INDEX ssn ON employee(ssn);

### Dropping Indexes

The index name specified for an index is required to drop an index. The `DROP INDEX` command takes the form:

Syntax:

DROP INDEX index_name ON table_name;

## Roles

In SQL, a role is a named group of privileges or permissions that can be assigned to one or more users or other roles. Roles are a way to **simplify and manage permissions by grouping them together.** Here are some key points about roles in SQL:

- **Permissions Aggregation:** Roles allow you to aggregate a collection of permissions or privileges into a single entity.
- **Simplify User Management:** Roles simplify user management, especially in environments with many users and complex permission structures.
- **Hierarchical Roles:** In some database management systems, roles can be organized hierarchically, where a role can contain other roles.
- **Dynamic and Static Roles:** Some database systems support both dynamic and static roles. Dynamic roles are temporary and are assigned when a user logs in or during a session.

### Roles vs Users

In SQL, "role" and "user" are two distinct concepts used for managing access and permissions within a database management system.

**User:**
- A user is an individual or entity that interacts with the database.
- Users are typically associated with specific individuals or applications that need to perform operations on the database.
- Each user has a unique username and, in some database systems, a password for authentication.



**Role:**
- A role is a named **group of users or other roles.**
- Roles are used to simplify and manage permissions and access control in a database.
- Roles can be nested, meaning you can have roles within roles, creating a hierarchical permission structure.

Here's how roles and users are typically used together:

- You create roles and assign specific permissions to these roles.
- You add individual users to the roles they need.
- Users then inherit the permissions associated with the roles they belong to.

### Roles in SQL

Any authorization that can be granted to a user can be granted to a role. Roles are granted to users just as authorizations are. Roles can be created, granted privileges, and revoked privileges.

Example:

CREATE ROLE Assistant_employee;
GRANT SELECT ON courses TO Assistant_employee;
SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@localhost USING Assistant_employee;

> It displays the permissions that the "root" user inherits from the "Assistant_employee" role.

Roles can also be granted to other roles, and privileges can be revoked from roles.

Example:

CREATE ROLE MD;
GRANT MD TO user_name;
REVOKE INSERT, UPDATE, DELETE ON database_name FROM MD;
DROP ROLE MD;

---

## Functions

- We have already seen several functions that are built into the SQL language.
- In this section, we show how developers can write their own functions and procedures, store them in the database, and then invoke them from SQL statements.
- Functions and procedures allow “business logic” to be stored in the database and executed from SQL statements.
- These can be defined either by the procedural component of SQL or by an external programming language such as Java, C, or C++.
- Functions that are created by the user in the system database or a user-defined database are known as user-defined functions.
- The UDF functions accept parameters, perform actions, and return the result.

## Database Management Systems

- These functions help us to simplify our development by encapsulating complex business logic and making it available for reuse anywhere based on the needs.
- The user-defined functions make the code needed to query data a lot easier to write.
- They also improve query readability and functionality, as well as allow other users to replicate the same procedure.

## Functions

- A function in SQL Server always accepts parameters, either single or multiple and returns a single value or table.
- The functions are useful in the simplification of our code.
- Suppose we might have a complex computation that appears in a number of queries.
- In such a case, we can build a function that encapsulates the formula and uses it in each query instead of in each query.

### Syntax:

CREATE FUNCTION schema_name.function_name (parameter_list)
RETURNS data_type AS
BEGIN
statements
RETURN value
END

- `function_name`: It is the name of the function that needs to be created in MySQL.
- `parameter1, parameter2,…`: We can pass the optional parameters to the functions that need to be declared while creating it in the () brackets.
- A function can contain none, one, or more than one parameter.
- `BEGIN` and `END` – `BEGIN` keyword marks the beginning of the function while `END` marks the completion of the function in MYSQL.

## How to Delete function?

DROP FUNCTION IF EXISTS function_name;

## Scalar Functions

- RETURN Datatype – We can return any type of value from the execution of the function.
- The type of value that will be returned needs to be specified after the RETURN clause.
- Once, MySQL finds the RETURN statement while execution of the function, execution of the function is terminated and the value is returned.
- `DETERMINISTIC` – The function can be either deterministic or non-deterministic which needs to be specified here.
- When the function returns the same value for the same values of the parameter then it is called deterministic. However, if the function returns a different value for the same values of functions then we can call that function to be nondeterministic.
- Functions will be NON-DETERMINISTIC by default.

## Example:

CREATE FUNCTION Dept_size(deptno INT)
RETURNS VARCHAR(7)
BEGIN
DECLARE No_of_emps INT;
SELECT COUNT(*) INTO No_of_emps FROM EMPLOYEE WHERE Dno = deptno;
IF No_of_emps > 3 THEN RETURN 'HUGE';
ELSEIF No_of_emps > 2 THEN RETURN 'LARGE';
ELSEIF No_of_emps > 1 THEN RETURN 'MEDIUM';
ELSE RETURN 'SMALL';
END IF;
END

### Query:

SELECT Dname, Dnumber, Dept_size(Dnumber) FROM department;

![image](https://hackmd.io/_uploads/HJd0avmUT.png)

-- Create a sample function that returns a table
CREATE FUNCTION GetEmployeeList()
RETURNS TABLE (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50)
)
AS

BEGIN
-- Your logic to retrieve data goes here
RETURN QUERY SELECT
EmployeeID,
FirstName,
LastName,
Department
FROM Employee;
END;

---

## Cursors

A database cursor is a mechanism that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition, and removal of database records. A **cursor in SQL is a temporary work area created in system memory** when a SQL statement is executed. A **SQL cursor is a set of rows together with a pointer that identifies a current row.** It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words, one row at a time. **In other words, a cursor can hold more than one row but can process only one row at a time. The set of rows the cursor holds is called the active set.**

### Implicit Cursors

- Auto-created by Oracle when SQL is executed if there is no explicit cursor used.
- Users or programmers cannot control the information or programs in it.
- Associated with INSERT, UPDATE, and DELETE types of DML operation statements.
- Attributes: SQL%FOUND, SQL%NOTFOUND, %ISOPEN, %ROWCOUNT.

### Explicit Cursors

- User-defined cursors which help to gain more control over the context part.
- Defined in the declaration area of the SQL block.
- Created on SELECT statements that return multiple records.
- Attributes: SQL%FOUND, SQL%NOTFOUND, %ISOPEN, %ROWCOUNT.

### Properties:

- **READ ONLY:** Using these cursors, you cannot update any table.
- **Non-Scrollable:** Using these cursors, you can retrieve records from a table in one direction, i.e., from top to bottom.
- **Asensitive:** These cursors are insensitive to the changes that are made in the table, i.e., the modifications done in the table are not reflected in the cursor. If we have created a cursor holding all the records in a table and, meanwhile, if we add some more records to the table, these recent changes will not be reflected in the cursor we previously obtained.

### Usage in MySQL:

The following diagram illustrates how to use a cursor in MySQL:

1. First, declare a cursor.
2. Next, open the cursor.
3. Then, fetch rows from the result set into a target.
4. After that, check if there is more row left to fetch. If yes, go to step 3; otherwise, go to step 5.
5. Finally, close the cursor.

### Cursor Syntax:

DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...;
CLOSE cursor_name;

When the FETCH statement is called, the next row is read in the result set each time. But a time comes when it reaches the end of the set and no data is found there, so to handle this condition with MYSQL cursor we need to use a NOT FOUND handler.

### NOT FOUND Handler Syntax:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET variable_name = 1;

### Cursor Example:

Consider the following Tutorials table:

CREATE TABLE tutorials (
ID INT,
TITLE VARCHAR(100),
AUTHOR VARCHAR(40),
DATE VARCHAR(40)
);

Create another table to back up the data:

CREATE TABLE backup (
ID INT,
TITLE VARCHAR(100),
AUTHOR VARCHAR(40),
DATE VARCHAR(40)
);

Write a procedure that backs up the contents of the tutorials table to the backup table using cursors:

DELIMITER //
CREATE PROCEDURE ExampleProc()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tutorialID INTEGER;
DECLARE tutorialTitle, tutorialAuthor, tutorialDate VARCHAR(20);
DECLARE cur CURSOR FOR SELECT * FROM tutorials;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
label: LOOP
FETCH cur INTO tutorialID, tutorialTitle, tutorialAuthor, tutorialDate;
INSERT INTO backup VALUES(tutorialID, tutorialTitle, tutorialAuthor, tutorialDate);
IF done = 1 THEN LEAVE label;
END IF;
END LOOP;
CLOSE cur;
END//
DELIMITER ;

Call the above procedure as shown below:

CALL ExampleProc;

---

## Triggers - Introduction

A trigger is a statement that the system executes automatically as a side effect of a modification to the database. Defining a trigger involves:

- Specifying when a trigger needs to be executed. This consists of two components:
  - **Event** – which causes the trigger to be checked
  - **Condition** – which must be satisfied for trigger execution to proceed
- Specifying the action to be taken when the trigger executes

Once we enter a trigger into the database, the system executes it whenever the specified event occurs and the corresponding condition is satisfied.

### Need for Triggers

Triggers can be used to implement certain integrity constraints that cannot be specified using the constraint mechanism of SQL. Triggers are also useful mechanisms for alerting humans or for starting certain tasks automatically when certain conditions are met.

Consider a scenario wherein a student has taken up a new course and a new tuple needs to be inserted in the TAKES relation. We could design a trigger that updates the tuple in the STUDENT relation for the student taking the course by adding the number of credits for the course to the student’s total credits, whenever a new tuple is inserted in the TAKES relation.

Triggers usually cannot perform updates outside the database. However, some database systems provide built-in support for sending email from triggers. Triggers can be used in various applications, such as maintaining database consistency, monitoring database updates, and updating derived data automatically.

## Triggers - Syntax

### Syntax:

CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE) ON table_name
FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;

- The `CREATE TRIGGER` statement is used to create a trigger with the name `trigger_name` on the relation `table_name`.

### Explanation:

- The first line of the syntax specifies the name of the trigger.
- The second line specifies the event which causes the trigger to be checked.
  - `table_name` specifies the relation which the trigger must check for modification.
  - `INSERT | UPDATE | DELETE` specifies the type of modification to be checked, i.e., whether the trigger must be checked on insertion, updation, or deletion of tuples.
  - `AFTER | BEFORE` specifies when the trigger must be checked, i.e., before or after the specified relation undergoes modification.
- The third line specifies that the trigger code would explicitly iterate over each modified row.
- The part of the query between the `BEGIN` and `END` keywords corresponds to the action to be taken on the modified tuples. This involves declaring any variables that would be used and specifying the SQL statements to be executed. Actions could be the execution of stored procedures as well.

## Triggers - Example

### Example: Create a trigger that checks if the entered marks are valid or not whenever an entry is made in the Marks_sample table.

DELIMITER //
CREATE TRIGGER CheckMarks
BEFORE INSERT ON Marks_sample
FOR EACH ROW
BEGIN
IF NEW.marks < 0 OR NEW.marks > 100 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid marks: Marks must be between 0 and 100';
END IF;
END;
//
DELIMITER ;

### How it works:

- The given SQL statement creates a trigger `CheckMarks`. It gets executed whenever there is an insertion in the `Marks_sample` relation. The keyword `BEFORE` specifies that the trigger execution occurs before the triggering operation (in this case, insertion of tuples in the `Marks_sample` relation) is executed.
- The trigger action gets executed for every row that is inserted. This is specified by the `FOR EACH ROW` clause.
- The trigger checks if the `Marks` value of the inserted tuple is lesser than 0 or greater than 100 (i.e., it checks if the `Marks` value is invalid). If the specified condition is satisfied, the trigger raises an error, an appropriate error message is displayed, and the tuple is not inserted (trigger execution occurs before insertion). If the condition is not satisfied, the tuple is inserted.

### How it works:

- Let’s try inserting into the `Marks_sample` relation using the statement:

INSERT INTO Marks_sample VALUES (1,'C03’,101);

The tuple does not get inserted as the `Marks` value is greater than 100.

The tuple does not get inserted as the `Marks` value is greater than 100.

### How it works:

Note that if the `Marks` value is NULL, then the tuple gets inserted.

INSERT INTO Marks_sample VALUES (1,'C03’,NULL);

## Triggers - Example

### Example: Implement a trigger that mimics the behavior of ON UPDATE CASCADE.

DELIMITER //
CREATE TRIGGER update_marks_on_cascade
BEFORE UPDATE ON Student_sample
FOR EACH ROW
BEGIN
SET FOREIGN_KEY_CHECKS = 0;
UPDATE Marks_sample SET SRN = NEW.SRN WHERE SRN = OLD.SRN;
SET FOREIGN_KEY_CHECKS = 1;
END;
//
DELIMITER ;

### How it works:

- The trigger `update_marks_on_cascade` updates the `SRN` values of tuples in the `Marks_sample` relation which are related to the tuples (in the `Student_sample` relation) being modified.
- To see its effect, let’s execute the given query:

UPDATE Student_sample SET SRN = 5 WHERE name = 'Harry';

The trigger automatically updates the `SRN` values of all tuples in the `Marks_sample` relation that are related to the tuple with name ‘Harry’.

The trigger automatically updates the `SRN` values of all tuples in the `Marks_sample` relation that are related to the tuple with name ‘Harry’.

If this trigger would not have been created (and if ON UPDATE CASCADE was not specified in the foreign key constraint in the `Marks_sample` table), then:

- Updating the `SRN` of any tuple in the `Student_sample` relation that has related tuples in the `Marks_sample` relation would have thrown an error as this updation will violate the foreign key constraint.
- However, updating the `SRN` of tuples that do not have any related tuples in the `Marks_sample` relation would not throw an error.

## Triggers - Example

### Example: Implement a trigger that stores all tuples deleted from the `Marks_sample` table in the `Marks_history` table

DELIMITER //
CREATE TRIGGER backup_marks_info
BEFORE DELETE
ON Marks_sample
FOR EACH ROW
BEGIN
INSERT INTO Marks_history SELECT * FROM Marks_sample WHERE SRN = OLD.SRN AND COURSE = OLD.COURSE;
END;
//
DELIMITER ;

### How it works:

- Let’s see the effect of this trigger by executing the given query:

DELETE FROM Marks_sample WHERE SRN = 2;

#### Before:

#### After:

Marks_sample Marks_history Marks_sample Marks_history
(empty set)

(empty set)

---

## Recursive Queries

Recursive queries often use recursive cases to handle iterative operations or hierarchical data structures. The term "recursive case" is not a standard SQL or programming terminology but rather a concept used to describe the part of a recursive query that performs the iterative or recursive step. In the context of recursive queries, you can think of it as the part of the query that defines how the recursion should continue until a termination condition is met.

In SQL, recursive queries are typically implemented using Common Table Expressions (CTEs) with the `WITH RECURSIVE` clause. Here's the general syntax for creating a recursive query:

WITH RECURSIVE cte_name (column_list) AS (
-- Anchor member: The initial query that forms the base case
SELECT ...
FROM ...
WHERE ...
UNION ALL
-- Recursive member: The query that refers to the CTE itself
SELECT ...
FROM cte_name -- Refer to the CTE within itself
WHERE ...
)
-- The final SELECT statement outside the CTE to retrieve the result
SELECT ...
FROM cte_name;

Explanation of each part of the syntax:
- `cte_name`: The name you give to the Common Table Expression (CTE).
- `column_list`: List of columns in the CTE's result set.
- Anchor member: The initial query forming the base case.
- `UNION ALL`: Combines the anchor member results with the recursive member results.
- Recursive member: The query referring to the CTE itself, creating the recursion. Must include a termination condition.
- Final SELECT statement: Retrieves the final result set from the CTE.

### Example: Countdown Query

WITH RECURSIVE Countdown (n) AS (
-- Anchor member
SELECT 5
UNION ALL
-- Recursive member
SELECT n - 1
FROM Countdown
WHERE n > 1
)
-- Final SELECT statement
SELECT n FROM Countdown;

#### Output:
5
4
3
2
1

### Recursive Relationship in Organizational Hierarchy

An example of a recursive relationship between tuples of the same type is the relationship between an employee and a supervisor. This relationship is described by the foreign key `Super_ssn` of the `EMPLOYEE` relation.

An example of a recursive operation is to retrieve all supervisees of a supervisory employee at all levels. Example shows such table `SUP_EMP` with 2 columns (Supervisor, Supervisee(any level)).

---