Unit 3: Structured Query Language

3.1 Introduction and Basic Commands

Structured Query Language (SQL) is the standard language for relational database management systems (RDBMS). It is used to store, manipulate, and retrieve data stored in a relational database. SQL provides a set of commands that allow users to interact with the database efficiently, abstracting the complex underlying data structures.

3.1.1 SQL Data Types

Data types specify the type of data that can be stored in a particular column of a table. Choosing the right data type is crucial for data integrity, storage optimization, and performance. The primary categories of SQL data types are:

Numeric Data Types: Used to store numbers.
- INT or INTEGER: Stores whole numbers. Example: EmployeeID INT.
- FLOAT(m, d) or REAL: Stores approximate numeric data with floating precision.
- DECIMAL(p, s) or NUMERIC(p, s): Stores exact numeric data. p is precision (total digits), and s is scale (digits after decimal). Example: Salary DECIMAL(10, 2).
String or Character Data Types: Used to store text.
- CHAR(n): Fixed-length character string. Padded with spaces if the input is shorter than n.
- VARCHAR(n): Variable-length character string. More space-efficient for varying text lengths.
- TEXT: Used for storing large amounts of text data.
Date and Time Data Types: Used to store date and time values.
- DATE: Stores date in YYYY-MM-DD format.
- TIME: Stores time in HH:MM:SS format.
- DATETIME or TIMESTAMP: Stores a combination of date and time.

3.1.2 Data Definition Language (DDL) Commands

Data Definition Language (DDL) consists of SQL commands that can be used to define the database schema. DDL commands deal with descriptions of the database schema and are used to create, modify, or destroy the structure of database objects.

CREATE: Used to create the database or its objects (like tables, indexes, functions, views, store procedures, and triggers).
```
SQL
```
ALTER: Used to alter the structure of the database. It can add, delete, or modify columns in an existing table.
```
SQL
```
TRUNCATE: Used to remove all records from a table, including all spaces allocated for the records. The table's structure remains intact. It is an operation much faster than DELETE because it does not log individual row deletions.
```
SQL
```
DROP: Used to delete objects from the database. A DROP command deletes the entire structure of the table along with its data, indexes, and privileges.
```
SQL
```

3.1.3 Data Manipulation Language (DML) Commands

Data Manipulation Language (DML) commands deal with the manipulation of data present within the database objects.

INSERT: Used to insert new records into a database table.
```
SQL
```
UPDATE: Used to modify existing data within a table. It is crucial to use a WHERE clause with this command; otherwise, all rows will be updated.
```
SQL
```
DELETE: Used to delete records from a database table. Similar to UPDATE, failing to include a WHERE clause will result in the deletion of all records in the table.
```
SQL
```

3.1.4 Data Query Language (DQL)

Data Query Language (DQL) is used to fetch data from the database.

SELECT: Used to select data from a database. The data returned is stored in a result table, often called the result-set.
```
SQL
```

3.1.5 Privilege Commands: GRANT and REVOKE

These commands fall under Data Control Language (DCL), which manages rights, permissions, and other controls of the database system.

GRANT: Gives specific users access privileges to a database or specific objects within it.
```
SQL
```
REVOKE: Withdraws the access privileges given to a user by the GRANT command.
```
SQL
```

3.1.6 Other Miscellaneous Commands

DESCRIBE (DESC): Used to display the structure of a table, including column names, data types, and constraints.
```
SQL
```
DISTINCT: Used in conjunction with the SELECT statement to eliminate duplicate records and fetch only unique records from a specific column.
```
SQL
```
ORDER BY: Used to sort the result-set in either ascending (ASC) or descending (DESC) order based on one or more columns.
```
SQL
```
GROUP BY: Groups rows that have the same values in specified columns into summary rows. It is almost always used with aggregate functions to perform calculations on each group.
```
SQL
```
HAVING: Added to SQL because the WHERE keyword cannot be used to filter aggregate functions. HAVING filters the summarized results generated by GROUP BY.
```
SQL
```

3.2 SQL Views

A view is a virtual table based on the result-set of an SQL query. It contains rows and columns, just like a real table. The fields in a view are fields drawn from one or more real tables in the database. Views encapsulate complex queries and enhance security by restricting user access to specific rows and columns without giving them access to the underlying tables.

Creating a View:

SQL

Using a View: You can query a view exactly as you would query a normal table.

SQL

Advantages of Views:

Security: They hide complex queries and restrict access to sensitive data fields.
Simplicity: End-users can query a view without needing to formulate or understand the underlying multi-table joins.
Consistency: Changes to the underlying table structure can often be hidden from the view interface, maintaining backward compatibility for applications.

3.3 SQL Functions

Functions in SQL are blocks of code that perform calculations on data and return a single value or a formatted result set. They are broadly categorized into Aggregate Functions and Scalar/String Functions.

1. Aggregate Functions: Operate on a collection of values (an entire column) and return a single summarizing value.

COUNT(): Returns the number of rows that match a specified criterion.
SUM(): Returns the total sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MIN(): Returns the smallest value of the selected column.
MAX(): Returns the largest value of the selected column.

SQL

2. Scalar/String/Numeric Functions: Operate on a single value and return a single calculated value for each row.

UPPER() / LOWER(): Converts a string to uppercase or lowercase.
LENGTH(): Returns the number of characters of a text field.
SUBSTRING(): Extracts a substring from a string.
ROUND(): Rounds a numeric field to the specified number of decimal places.
NOW() / SYSDATE(): Returns the current system date and time.

SQL

3.4 SQL Operators

Operators are reserved words or characters used primarily in an SQL statement's WHERE clause to perform operations, such as logical comparisons and arithmetic calculations.

Arithmetic Operators: Used to perform mathematical operations on numeric data. (+, -, *, /, % for modulo).
Comparison Operators: Used to compare two values.
- = (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: True if all conditions separated by AND are TRUE.
- OR: True if any of the conditions separated by OR is TRUE.
- NOT: Reverses the boolean value; true if the condition is NOT TRUE.
- BETWEEN: Selects values within a specified, inclusive range.
- LIKE: Used to search for a specified pattern in a column (using % for zero or more characters and _ for a single character).
- IN: Allows you to specify multiple discrete values in a WHERE clause.

Example of Operators in Use:

SQL

3.5 Set Operators

Set operators combine the result sets of two or more independent SELECT statements into a single composite result set. The queries being combined must have the same number of columns in the SELECT statement, and the corresponding columns must have identical or compatible data types.

UNION: Combines the result sets and automatically removes any duplicate rows.
```
SQL
```
UNION ALL: Combines the result sets but keeps all duplicate rows. It operates faster than UNION because it skips the deduplication step.
```
SQL
```
INTERSECT: Returns only the rows that are present in both result sets.
```
SQL
```
MINUS: Returns only the distinct rows from the first result set that are not present in the second result set. (Note: While Oracle uses MINUS, many RDBMS like SQL Server and PostgreSQL use EXCEPT for the identical functionality.)
```
SQL
```

3.6 Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them (usually primary and foreign keys). Joins are fundamental to querying normalized relational databases.

INNER JOIN: Returns records that have matching values in both tables. If there is no match, the row is excluded.
```
SQL
```
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
```
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 if there is no match.
```
SQL
```
FULL (OUTER) JOIN: Returns all records when there is a match in either the left or right table. Unmatched records will contain NULLs for the missing side.
```
SQL
```
CROSS JOIN: Returns the Cartesian product of the two tables. Every row from the first table is combined with every row from the second table. If table A has 5 rows and table B has 5 rows, the cross join will have 25 rows.
```
SQL
```
SELF JOIN: A regular join, but the table is joined with itself. This is useful for hierarchical data or comparing rows within the same table.
```
SQL
```

3.7 SQL Constraints

Constraints are structural rules applied to data columns on a table. They are used to strictly limit the type of data that can go into a table, ensuring the accuracy, reliability, and integrity of the database.

3.7.1 Need of Constraints

Constraints guarantee data integrity by preventing invalid data entry into tables. If an action (INSERT, UPDATE, DELETE) violates a constraint, the database engine actively aborts the action. Constraints are critical for maintaining the logical consistency of relational databases, such as ensuring that an order cannot be created for a non-existent customer.

3.7.2 Domain Integrity Constraints

These constraints validate data for a specific column, ensuring it conforms to a defined domain (a valid set of values or structure).

NOT NULL: Ensures that a column cannot have a NULL value. Every single row must contain a valid value for this field.
CHECK: Ensures that all values in a column satisfy a specific, defined condition.

SQL

3.7.3 Entity Integrity Constraints

These constraints ensure that each row in a table is distinct and uniquely identifiable.

UNIQUE: Ensures that all values in a column are entirely different. Unlike a Primary Key, a table can have multiple UNIQUE constraints, and it can usually accept a single NULL value (depending on the specific RDBMS).
PRIMARY KEY: A combination of NOT NULL and UNIQUE. It strictly uniquely identifies each record in a table. A relational database table can have only ONE primary key constraint.

SQL

3.7.4 Referential Integrity Constraints

Referential integrity constraints enforce structural relationships between tables.

FOREIGN KEY (Reference Key): A field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table with the foreign key is the child table, and the table with the primary key is the parent table. It prevents actions that would destroy links between tables and ensures that the child table cannot hold an invalid reference.

SQL

Summary In this unit, we comprehensively covered the foundational structures of Structured Query Language. We explored various data types and the core command categories: DDL for schema structure, DML for data manipulation, DQL for querying, and DCL for database privileges. We delved into powerful features such as views for abstracting complexity, functions for evaluating data, and a wide array of operators for advanced filtering. We also learned how to combine independent queries with set operators and relational data with joins. Finally, we emphasized the absolute necessity of data integrity by establishing domain, entity, and referential constraints.