MySQL Tutorial

1. Schema
schema is a logical structure that organizes and groups database objects, such
as tables, views, indexes, stored procedures, functions, and more. It serves as a
namespace within a database, allowing for better management, organization, and
control of database objects.
1. Key Features of a Schema
1. Namespace:
o Objects within a schema are uniquely identified by their name and the
schema they belong to.
o Example: schema_name.table_name.
2. Separation:
o Schemas allow the logical separation of objects within a database,
making it easier to manage and secure them.
3. Ownership and Security:
o Each schema is owned by a specific database user or role.
o Permissions can be assigned at the schema level to control access to all
objects within the schema.
4. Multi-Schema Support:
o Many databases allow multiple schemas in a single database, such as in
PostgreSQL, SQL Server, MySQL, and Oracle.

2. Why Use Schemas?

1. Organization:
o Group related objects together, e.g., separating tables for different
applications or modules.
2. Security:
o Grant permissions to specific schemas to limit access.
 3. Collaboration:
o Multiple users can work within the same database while maintaining
separate namespaces.
4. Avoid Naming Conflicts:
o Different schemas can have objects with the same name.
MySQL:
 In MySQL, a schema is synonymous with a database.
 Create a schema (or database):
Syntax:
CREATE DATABASE inventory;
 Use a schema:
USE inventory;
Schema vs Database
 Schema: A logical grouping of objects within a single database.
 Database: A container that holds multiple schemas and manages data storage.

2. Database:

3. Table:
Collection of rows and columns, Data’s are presented in the rows and
columns.

4. Few Databases:
1. Oracle
2. MS SQL
3. MySQL – invented by Oracle
4. DB2
5. Sybase
6. MongoDB
7. PostgreSQL
5. Why Database?
Before 1970, they stored in flat file.
In Database, almost single line which fetch the data you need
1. Fast
2. Reliable
3. Secure
4. Better memory conceptions

6. Stored procedure
A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again. ... You can also pass parameters to a stored
procedure, so that the stored procedure can act based on the parameter value(s)
that is passed

7. BLOB and CLOB

BLOB CLOB

The full form of Blob is a Binary Large The full form of Clob is Character Large
OBject. OBject.

This is used to store large binary data. This is used to store large textual data.

This stores values in the form of This stores values in the form of
binary streams. character streams.

Using this you can stores files like Using this you can store files like text
videos, images, gifs, and audio files. files, PDF documents, word documents
etc.

MySQL supports this with the MySQL supports this with the following
following datatypes: datatypes:
 TINYBLOB  TINYTEXT
 BLOB  TEXT
 BLOB CLOB

 MEDIUMBLOB  MEDIUMTEXT
 LONGBLOB  LONGTEXT

In JDBC API it is represented by In JDBC it is represented by [Link]

[Link] Interface. Interface.

The Blob object in JDBC points to the The Blob object in JDBC points to the
location of BLOB instead of holding its location of BLOB instead of holding its
binary data. character data.

To store Blob JDBC To store Clob JDBC (PreparedStatement)

(PreparedStatement) provides provides methods like:
methods like:
 setClob()
 setBlob()
 setCharacterStream()
 setBinaryStream()

And to retrieve (ResultSet) Blob it And to retrieve (ResultSet) Clob it

provides methods like: provides methods like:
 getBlob()  getClob()
 getBinaryStream  getCharacterStream()

8. SQL Syntax and Statemen Structure

1. SQL Keywords and Identifiers
In SQL, keywords and identifiers play a crucial role in writing and understanding
queries. Here's a breakdown of these terms:
1. Keywords
 Definition: Reserved words in SQL that have a specific meaning and
functionality.
 Purpose: Used to perform various operations like defining, manipulating, and
querying data.
 Examples:
 o Data Definition Language (DDL):
 CREATE, DROP, ALTER, TABLE
o Data Manipulation Language (DML):
 SELECT, INSERT, UPDATE, DELETE
o Data Query Language (DQL):
 SELECT, WHERE, ORDER BY, GROUP BY
o Data Control Language (DCL):
 GRANT, REVOKE
o Transaction Control Language (TCL):
 COMMIT, ROLLBACK, SAVEPOINT
Common SQL Keywords

Category Keywords

DDL CREATE, DROP, ALTER, TRUNCATE

DML INSERT, UPDATE, DELETE, MERGE

DQL SELECT, DISTINCT, WHERE, HAVING

DCL GRANT, REVOKE

TCL COMMIT, ROLLBACK, SAVEPOINT

Miscellaneous CASE, WHEN, THEN, AS, NULL, LIKE, IN, BETWEEN

2. Identifiers
 Definition: Names used to identify database objects such as tables, columns,
databases, schemas, indexes, views, and constraints.
 Purpose: Enable users to reference database objects in queries.
 Examples:
o Table names: employees, sales_data
o Column names: employee_id, salary
o Schema names: hr, finance
Rules for Identifiers
 1. Naming Conventions:
o Should begin with a letter (a–z, A–Z).
o Can include letters, numbers (0–9), and underscores (_).
o Avoid starting with numbers or using special characters like $, @, #.
2. Case Sensitivity:
o SQL keywords are case-insensitive (SELECT is the same as select).
o Identifiers' case sensitivity depends on the database (e.g., MySQL is
case-insensitive by default, PostgreSQL is case-sensitive).
3. Reserved Words:
o Avoid using SQL keywords as identifiers unless enclosed in delimiters
(e.g., backticks in MySQL or double quotes in PostgreSQL).
SELECT "SELECT" FROM my_table; -- Using a keyword as a column name
4. Length Restrictions:
o Varies by database, but typically identifiers should not exceed 128
characters.
o

Differences Between Keywords and Identifiers

Aspect Keywords Identifiers

Purpose Defines SQL operations Names database objects

Reserved Reserved and predefined User-defined

Examples SELECT, WHERE, CREATE employee_id, sales_data

Case Sensitivity Case-insensitive (mostly) Depends on the database

2. Practical Examples
Keywords in Action
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 50000
 ORDER BY salary DESC;
Keywords: SELECT, FROM, WHERE, ORDER BY, DESC.
Identifiers in Action
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
salary DECIMAL(10, 2)
);
Identifiers: employees, employee_id, first_name, salary.

2. Statement, Clauses and Predicates

In SQL, statements, clauses, and predicates are fundamental components of writing
queries. Here's what each of these terms means:
1. Statement
An SQL statement is a complete instruction that performs a specific task. It can be a
query, data modification, or a command to manage the database structure.
Examples:
 SELECT * FROM employees; (query statement)
 INSERT INTO employees (id, name) VALUES (1, 'John'); (data modification
statement)
 CREATE TABLE employees (id INT, name VARCHAR(50)); (data definition
statement)

2. Clause
A clause is a component of an SQL statement. It defines specific parts of the SQL
statement and gives additional instructions or conditions.
Common SQL Clauses:
 SELECT: Specifies the columns to retrieve.
Example: SELECT name, salary FROM employees;
  FROM: Specifies the source table.
Example: FROM employees
 WHERE: Filters rows based on a condition.
Example: WHERE salary > 5000
 GROUP BY: Groups rows sharing a value into summary rows.
Example: GROUP BY department
 HAVING: Filters groups after aggregation.
Example: HAVING COUNT(*) > 5
 ORDER BY: Sorts the result.
Example: ORDER BY name ASC

3. Predicate
A predicate is a condition or logical expression used within clauses like WHERE,
HAVING, or ON (in joins). It evaluates to TRUE, FALSE, or NULL.
Examples of Predicates:
 Comparison Predicate:
Example: salary > 5000
 Range Predicate:
Example: age BETWEEN 30 AND 50
 Membership Predicate:
Example: department IN ('IT', 'HR')
 Null Predicate:
Example: manager_id IS NULL
 Pattern Matching Predicate:
Example: name LIKE 'J%'
 Existence Predicate:
Example: EXISTS (SELECT 1 FROM departments WHERE manager_id =
[Link])

3. Example SQL Query with Clauses and Predicates:

  Clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
 Predicates: salary > 5000, department = 'IT', AVG(salary) > 6000
3. Comments and Formatting Best Practices
Good comments and proper formatting in SQL enhance readability, maintainability,
and collaboration. Here are some best practices for commenting and formatting SQL
code:
1. Comments in SQL
SQL supports two types of comments:
Single-line Comments
Begin with -- and continue until the end of the line.

Example:

Multi-line Comments
Enclosed between /* and */. Use for longer explanations or block comments.
Example:
Best Practices for Comments
 Be concise but meaningful: Explain the "why," not just the "what."
🚫 -- This is a query.
✅ -- Retrieve IT employees earning above 5000.
 Avoid redundant comments: Don’t state the obvious.
🚫 -- Select name and salary from employees.
✅ -- Fetch employee details for salary analysis.
 Update comments when code changes: Outdated comments can be
misleading.

2. SQL Formatting Best Practices

a. Use Consistent Indentation
Proper indentation improves readability, especially for complex queries.
Example:

b. Capitalize Keywords
Write SQL keywords in uppercase for clarity.
Example:
c. Use Line Breaks for Logical Segments
Break long statements into multiple lines for better readability.
Example:

d. Align Conditions for Readability

Align AND/OR conditions under the WHERE clause for better clarity.
Example:

e. Use Aliases Wisely

Use short, meaningful aliases to simplify column and table references.

Example:

f. Avoid Hardcoding Values

Use variables or placeholders for better maintainability.
Example:
g. Group Clauses Logically
Write clauses in the typical SQL order:
1. SELECT
2. FROM
3. JOIN (if any)
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY
Example:
3. Example: Well-Formatted and Commented SQL

By following these practices, your SQL code becomes more structured,

understandable, and maintainable for you and your team.

9. Data Types:

Data type Description

CHAR(size) A FIXED length string (can contain letters, numbers, and

special characters). The size parameter specifies the
column length in characters - can be from 0 to 255.
Default is 1

VARCHAR(size) A VARIABLE length string (can contain letters, numbers,

and special characters). The size parameter specifies the
maximum column length in characters - can be from 0 to
65535

BINARY(size) Equal to CHAR(), but stores binary byte strings.

The size parameter specifies the column length in bytes.
 Default is 1

BOOL Zero is considered as false, nonzero values are considered

as true.

BOOLEAN Equal to BOOL

SMALLINT(size) A small integer. Signed range is from -32768 to 32767.

Unsigned range is from 0 to 65535. The size parameter
specifies the maximum display width (which is 255)

MEDIUMINT(size) A medium integer. Signed range is from -8388608 to

8388607. Unsigned range is from 0 to 16777215.
The size parameter specifies the maximum display width
(which is 255)

INT(size) A medium integer. Signed range is from -2147483648 to

2147483647. Unsigned range is from 0 to 4294967295.
The size parameter specifies the maximum display width
(which is 255)

INTEGER(size) Equal to INT(size)

BIGINT(size) A large integer. Signed range is from -

9223372036854775808 to 9223372036854775807.
Unsigned range is from 0 to 18446744073709551615.
The size parameter specifies the maximum display width
(which is 255)

FLOAT(size, d) A floating point number. The total number of digits is

specified in size. The number of digits after the decimal
point is specified in the d parameter. This syntax is
deprecated in MySQL 8.0.17, and it will be removed in
future MySQL versions

FLOAT(p) A floating point number. MySQL uses the p value to

determine whether to use FLOAT or DOUBLE for the
resulting data type. If p is from 0 to 24, the data type
becomes FLOAT(). If p is from 25 to 53, the data type
becomes DOUBLE()

DOUBLE(size, d) A normal-size floating point number. The total number of

 digits is specified in size. The number of digits after the
decimal point is specified in the d parameter

DOUBLE
PRECISION(size, d)

DECIMAL(size, d) An exact fixed-point number. The total number of digits is

specified in size. The number of digits after the decimal
point is specified in the d parameter. The maximum
number for size is 65. The maximum number for d is 30.
The default value for size is 10. The default value for d is
0.

DEC(size, d) Equal to DECIMAL(size,d)

10. SQL: Different Type of Queries

Type:
1. DDL – Data Definition Language
a. Various SQL Commands
1. Create
2. Alter
3. Drop
4. Truncate
5. Rename

b. CREATE INDEX and DROP INDEX

c. Data Types and Table Storage Engines

2. DML – Data Manipulation Language

a. Various DML SQL Commands
1. Insert
2. Update
3. Delete
 4. Merge

b. BULK INSERT
Here’s a detailed overview of BULK INSERT and data import/export techniques
in MySQL, focusing on efficient methods to handle large datasets.

1. BULK INSERT in MySQL

While MySQL does not have a BULK INSERT command like SQL Server, it achieves
similar functionality using LOAD DATA INFILE and mysqlimport.

3. 2. Using LOAD DATA INFILE for Bulk Inserts

The LOAD DATA INFILE statement imports data from a text file (e.g., CSV) into a
database table. It is the fastest way to load large datasets in MySQL.
c. Syntax:

Example:
Import data from [Link] into a table named employees:

d. Data Import Technique

3. Using mysqlimport Utility
The mysqlimport utility is a command-line tool for importing files into MySQL tables.
It is a wrapper for the LOAD DATA INFILE command.
Syntax:
Example:
Import [Link] into the employees table:

e. Data Export Technique

Export Data Using SELECT INTO OUTFILE
The SELECT INTO OUTFILE statement exports query results to a file on the MySQL
server.
Syntax:

Example:
Export the employees table to a CSV file:

5. Data Import/Export with Tools

a. MySQL Workbench
1. Data Import:
 o Use the Data Import Wizard to load CSV files into tables.
o Navigate to Server > Data Import.
2. Data Export:
o Use the Data Export Wizard to export tables to CSV or SQL files.
o Navigate to Server > Data Export.
b. MySQL Dump Utility
 Use mysqldump to export databases or tables.
Export a Database:

Export a Table:

6. Error Handling and Permissions

f. Common Errors and Solutions:
 ERROR 1290 (secure_file_priv restriction):
o MySQL restricts file imports/exports to a specific directory. Check
secure_file_priv:

o Move your file to the permitted directory or disable the restriction.

 Enable LOAD DATA LOCAL INFILE:
o For local files, enable the --local-infile option:

g. Granting Permissions:
Ensure the MySQL user has the necessary permissions:

7. Optimizing Bulk Data Import

1. Disable Indexes Temporarily:
 2. Batch Processing: If the file is too large, split it into smaller chunks.
3. Use Transactions: Wrap LOAD DATA operations in a transaction for better
control.
4. Use MyISAM for Temporary Loads: If possible, use a temporary MyISAM table
for loading and later transfer data to the target InnoDB table.

8. Practical Workflow
h. a. Import Workflow:
1. Prepare the file ([Link]):

2. Create a table:

3. Import the file:

b. Export Workflow:
 1. Export the table to a file:

3. DQL – Data Query Language

1. Select

4. TCL – Transaction Control Language

1. Transaction
2. Commit – save changes permanently
3. Rollback – undo changes
4. Savepoint

a. Transaction Management
Transaction management is a crucial aspect of database systems and other
applications that involve data manipulation. It ensures that data remains consistent
and reliable even when multiple operations are performed concurrently or in the
face of system failures.
What is a Transaction?
In the context of databases, a transaction is a sequence of one or more
operations performed as a single logical unit of work. These operations could
include:
 Reading data: Retrieving information from the database.
 Writing data: Inserting, updating, or deleting data in the database.

ACID Properties
To guarantee data integrity, transactions must adhere to the ACID properties:
 Atomicity: A transaction is treated as a single, indivisible unit of work. Either
all operations within the transaction are completed successfully, or none are.
If any part of the transaction fails, the entire transaction is rolled back, and the
database is restored to its previous state.
 Consistency: A transaction must maintain the database's integrity constraints.
It ensures that the database transitions from one valid state to another. If a
transaction violates any constraints, it is rolled back.
  Isolation: Transactions should be isolated from each other, meaning that
concurrent transactions should not interfere with each other's execution. Each
transaction should operate as if it were the only transaction running on the
database.
 Durability: Once a transaction is committed (successfully completed), the
changes made to the database are permanent and will survive even system
failures such as power outages or crashes.
Why is Transaction Management Important?
Transaction management is essential for several reasons:
 Concurrency Control: In environments where multiple users or applications
access the database simultaneously, transaction management prevents data
corruption and ensures that transactions are executed in a consistent and
predictable manner.
 Error Recovery: If a system failure occurs during a transaction, transaction
management allows the database to recover to a consistent state by rolling
back any incomplete transactions.
 Data Integrity: By enforcing the ACID properties, transaction management
guarantees that data remains accurate and reliable, even in the face of errors
or concurrent access.

Transaction Management Techniques

Database systems employ various techniques to manage transactions, including:
 Concurrency Control Mechanisms: These mechanisms, such as locking and
timestamping, regulate the access of concurrent transactions to shared data,
preventing conflicts and ensuring isolation.
 Logging: The database system maintains a log of all transaction operations.
This log is used for recovery purposes in case of system failures.
 Two-Phase Commit (2PC): This protocol ensures that distributed transactions
(transactions involving multiple databases) are committed atomically.
Example
Consider a banking transaction where money is transferred from account A to
account B. This transaction involves two operations:
1. Debit the amount from account A.
2. Credit the amount to account B.
If a system failure occurs after the first operation but before the second, transaction
management ensures that the debit operation is rolled back, preventing an
inconsistency where money is deducted from one account but not added to the
other.

1. Commands for Transaction Management:

 o START TRANSACTION: Begins a new transaction.
o COMMIT: Saves changes made during the transaction.
o ROLLBACK: Undoes changes made during the transaction.
o SAVEPOINT: Sets a savepoint within a transaction.
o ROLLBACK TO SAVEPOINT: Rolls back to a specific savepoint.

5. Transaction Management Commands

1. START TRANSACTION
 Begins a new transaction.
Syntax:

2. COMMIT
 Permanently saves changes made during the transaction.
Syntax:

3. ROLLBACK
 Reverts all changes made during the transaction since the last commit.
Syntax:

4. SAVEPOINT
 Creates a checkpoint within a transaction that you can roll back to without
rolling back the entire transaction.
Syntax:

5. ROLLBACK TO SAVEPOINT
 Reverts changes to the specified savepoint.
Syntax:

6. RELEASE SAVEPOINT
 Removes a savepoint.
Syntax:
 7. SET AUTOCOMMIT
 By default, MySQL runs in autocommit mode, where each statement is
treated as a transaction and committed automatically.
To disable autocommit:

To enable autocommit:

6. Example of Transaction Management

Basic Example

 Explanation: The transaction ensures both updates succeed. If any error

occurs, changes are not committed.

Example with Rollback

  Explanation: If no rows are updated, the transaction is rolled back.

Using SAVEPOINT

 Explanation: The rollback reverts to step1, but changes before the savepoint
remain.

7. Benefits of Transactions
 Prevents partial updates.
 Ensures data integrity.
 Allows for error handling in complex operations.
 Provides greater control over database operations.

b. Implicit and Explicit Transactions

Transaction management in SQL can be categorized into two types: implicit
transactions and explicit transactions. The distinction lies in how and when the
transaction boundaries (start and end) are defined.
 8. Implicit Transactions
1. Definition:
An implicit transaction is automatically started by the database when certain
SQL statements are executed. The transaction remains active until you
explicitly commit or roll it back.
2. Characteristics:
o The transaction begins automatically when executing certain SQL
commands.
o Requires manual COMMIT or ROLLBACK to end the transaction.
o Suitable for environments where transaction control is necessary but
not explicitly defined.
3. Common SQL Statements That Trigger Implicit Transactions:
o INSERT
o UPDATE
o DELETE
o MERGE
o SELECT INTO
4. Enabling Implicit Transactions: To enable implicit transactions in MySQL, set
the autocommit mode to 0:
Example:
INSERT INTO Customers (Name) VALUES ('Alice'); -- Implicit transaction:
Automatically committed if successful
UPDATE Products SET Price = 10 WHERE ID = 1; -- Implicit transaction:
Automatically committed if successful
9. Explicit Transactions
1. Definition:
An explicit transaction is one where the transaction boundaries are manually
defined using START TRANSACTION, COMMIT, and ROLLBACK.
2. Characteristics:
o You explicitly define the start and end of a transaction.
 o Offers precise control over transactional operations.
o Commonly used in complex operations where multiple statements
need to be grouped together.
3. Commands:
o START TRANSACTION: Begins a new transaction.
o COMMIT: Saves changes permanently.
o ROLLBACK: Reverts changes to the last commit or savepoint.
Example:

Rollback Example:

10. Key Differences

Feature Implicit Transactions Explicit Transactions

Automatically starts for Manually started with START

Start of Transaction
certain commands TRANSACTION

Must be explicitly Must be explicitly committed or

Commit/Rollback
committed or rolled back rolled back

Full control over transaction

Control Limited control
boundaries
Feature Implicit Transactions Explicit Transactions

Auto commit Requires SET Independent of auto commit

Dependency AUTOCOMMIT = 0 setting

Simple, single-statement Complex, multi-statement

Use Cases
operations operations requiring atomicity

c. Transaction Log Management

The transaction log in MySQL, commonly referred to as the binary log (binlog),
is a crucial component for managing and tracking changes to the database. It records
all changes made to the database, ensuring data integrity, recovery, and replication.

11. What is a Transaction Log?

 A transaction log keeps a record of all modifications made to the database
during transactions.
 It enables:
1. Data Recovery: Restores the database to a consistent state in case of
failures.
2. Replication: Transfers changes to replicas in a master-slave setup.
3. Auditing: Tracks changes for auditing purposes.
In MySQL, the binary log serves as the primary mechanism for transaction logging.

12. Key Components of MySQL Transaction Log Management

d. 1. Binary Log (Binlog)
 The binlog is used to log all SQL statements that modify data (e.g., INSERT,
UPDATE, DELETE).
 It is also used for:
o Point-in-time recovery.
o Replication between master and replica servers.
 To enable the binary log, include the following in the MySQL configuration file
([Link] or [Link]):
ini
Copy code
[mysqld]
log_bin = /path/to/binlog
e. 2. InnoDB Undo Logs
 The undo logs are internal logs used by the InnoDB storage engine to track
changes for transactions that are not yet committed.
 They enable features like rollback and MVCC (Multiversion Concurrency
Control).
f. 3. Redo Logs
 Redo logs are used to ensure durability by recording all changes made to the
data for recovery purposes in case of a crash.
 They are stored in ib_logfile0, ib_logfile1, etc.

13. Configuring Transaction Logs

g. Binary Log Configuration
 To enable and configure the binary log:
ini
Copy code
[mysqld]
log_bin = /var/log/mysql/[Link]
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
o log_bin: Enables binary logging.
o binlog_format: Specifies the logging format (ROW, STATEMENT, or
MIXED).
o expire_logs_days: Sets the number of days to retain the binary logs.
o max_binlog_size: Limits the size of each binary log file.
 h. InnoDB Log Configuration
 To configure redo and undo logs:
ini
Copy code
[mysqld]
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_undo_logs = 128

14. Managing and Monitoring Transaction Logs

i. 1. Checking Binary Logs
 List available binary logs:
sql
Copy code
SHOW BINARY LOGS;
 View the binary log file in use:
sql
Copy code
SHOW MASTER STATUS;
j. 2. Purging Old Logs
 To delete old binary logs:
sql
Copy code
PURGE BINARY LOGS TO 'mysql-bin.000010';
 To purge logs older than a specific date:
sql
Copy code
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
 k. 3. Viewing Log Contents
 Use mysqlbinlog to view the contents of a binary log:
bash
Copy code
mysqlbinlog /path/to/mysql-bin.000001
l. 4. Disabling Binary Logs Temporarily
 You can disable binary logging for a specific session:
sql
Copy code
SET SESSION sql_log_bin = 0;

15. Best Practices for Transaction Log Management

1. Enable Binary Logging:
o Essential for recovery, replication, and auditing.
2. Optimize Log Sizes:
o Set appropriate sizes for binary logs and InnoDB logs to balance
performance and disk usage.
3. Purge Old Logs:
o Regularly clean up logs to prevent disk space issues. Use
expire_logs_days for automatic purging.
4. Monitor Log Health:
o Regularly monitor binary and InnoDB logs for corruption or excessive
growth.
5. Secure Logs:
o Restrict access to transaction logs to prevent unauthorized access.

16. Recovery with Transaction Logs

m. Point-in-Time Recovery
1. Restore the latest backup.
 2. Replay the binary logs to apply changes since the backup:
bash
Copy code
mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u username -p

17. DCL – Data Control Language

1. Grant -
2. Revoke
11. Delete vs Truncate

Delete Truncate
1 It is possible to delete only particular Truncate can only delete all the
record or all the records records, it is not possible to delete
particular record
2 Delete operation is slower than Truncate Operation is very faster
Truncate
3 Deleted recorded can be rolled back. With Truncate command, it is not
possible to roll back the records.

12. Drop vs Truncate

[Link] DROP TRUNCATE

The DROP command is used to Whereas the TRUNCATE

remove table definition and its command is used to delete all
1. contents. the rows from the table.

While the TRUNCATE command

In the DROP command, table does not free the table space
2. space is freed from memory. from memory.

Whereas the TRUNCATE is also

DROP is a DDL(Data Definition a DDL(Data Definition Language)
3. Language) command. command.

4. In the DROP command, view of While in this command, view of

 [Link] DROP TRUNCATE

table does not exist. table exist.

While in this command,

In the DROP command, integrity integrity constraints will not be
5. constraints will be removed. removed.

While in this command, undo

In the DROP command, undo space is used but less than
6. space is not used. DELETE.

The DROP command is quick to

perform but gives rise to While this command is faster
7. complications. than DROP.

13.

14. Constraints:
SQL constraints are used to specify rules for data in a table.

[Link]
Will not allow the duplicate values.

[Link] Null
The column will not all the null values (it means column should have
some value)

[Link] Key
Combination of both Unique and Not Null
 [Link] Key
The FOREIGN KEY constraint prevents invalid data from being inserted
into the foreign key column, because it has to be one of the values
contained in the parent table.

[Link]

[Link]

15. Where class:

1. Relational operators:
<, <=, >, >= =, !=

2. Logical Operators:
And, or and Not

3. Like and Not like

4. In, not in

5. Between, Not between

6. Wildcards

16. Join

Joining the two or more than the tables and retrieve required no of columns from
the tables.

1. Inner Join
The INNER JOIN is one of the most commonly used joins in SQL. It retrieves
records that have matching values in both tables being joined. If a row in one table
does not have a corresponding row in the other table, it will not be included in the
result.

Syntax:

 table1 and table2: The tables being joined.

 ON: Specifies the condition for matching rows between the tables.

2. Key Characteristics
 Returns rows where there is a match in both tables.
 Rows without matches in either table are excluded from the result.

Example
Tables:
Another Example:
employees:

employee_id name department_id

1 Alice 10

2 Bob 20

3 Charlie NULL

departments:

department_id department_name

10 HR

20 IT

30 Finance

Query with INNER JOIN:

Result:

name department_name

Alice HR

Bob IT

 Explanation:
o Alice and Bob have matching department_id values in the departments
table.
o Charlie does not have a matching department_id, so their row is
excluded.
3. Alias for Simplicity
You can use table aliases to make the query easier to read:

4. Joining Multiple Tables

You can join more than two tables using multiple INNER JOIN clauses:

5. Key Use Cases

1. Retrieving related data from multiple tables.
2. Filtering data to include only rows with matching relationships.
3. Building complex queries involving multiple join
6. Left Join

Syntax:

Example:
7. Right Join

Syntax:

Example:

8. Full Join / Outer Join

Syntax:

Example:
 9. Cross Join
The CROSS JOIN is used to combine every row from one table with every row
from another table, resulting in a Cartesian product of the two tables. Unlike other
types of joins, it does not require any condition to match rows.
Syntax

 table1 and table2: The tables being combined.

10. Key Characteristics

 Produces a Cartesian product, which means the number of rows in the result
set is equal to the product of the number of rows in the two tables.
 Typically used for scenarios where all combinations of rows are needed.
 Can generate a large result set if the tables are large.

Example
Tables:
products:

product_id product_name

1 Laptop

2 Smartphone

regions:
region_id region_name

101 North America

102 Europe

Query with CROSS JOIN:

Result:

product_name region_name

Laptop North America

Laptop Europe

Smartphone North America

Smartphone Europe

 Explanation:
o Each row in the products table is combined with every row in the
regions table.

11. Implicit CROSS JOIN

If no join condition is specified, a CROSS JOIN is implied by default:

This also produces a Cartesian product.

12. Use Cases
1. Testing and Debugging:
o Used to generate all possible combinations of rows for testing queries
or understanding data relationships.
2. Special Scenarios:
 o Calculating combinations (e.g., generating price combinations for
products in different regions).
o Creating datasets for statistical analysis.
3. Simulating Combinations:
o For example, pairing each salesperson with every product.

13. Key Considerations

 Size of Result Set: Be cautious when using CROSS JOIN with large tables as it
can generate an enormous number of rows.
o Number of rows in the result = (Rows in Table 1) × (Rows in Table 2).
 Usually, a CROSS JOIN is followed by a filtering condition using a WHERE
clause to limit the results.

14. Example with Filtering

Result:

product_name region_name

Laptop Europe

Smartphone Europe
  Explanation: The result is limited to combinations where the region is Europe.
15. Self Join
Syntax:

Example:

16. Equi Join

Syntax:

Example:

17. What is the difference between Equi Join and Inner Join in SQL?

An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have
equivalent values for the specified columns.

An inner join is a join of two or more tables that returns only those rows (compared using a comparison operator) that
satisfy the join condition.

Pictorial representation : EQUI JOIN Vs. INNER JOIN

 18. Subqueries
Noncorrelated and Correlated Subqueries
Subqueries can be categorized into two types:
 A noncorrelated (simple) subquery obtains its results independently of its
containing (outer) statement.
 A correlated subquery requires values from its outer query in order to execute
the inner query.

17. Non-correlated Subqueries

A noncorrelated subquery executes independently of the outer query. The subquery
executes first, and then passes its results to the outer query, For example:
=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state
FROM states);
Vertica executes this query as follows:
1. Executes the subquery SELECT state FROM states (in bold).
2. Passes the subquery results to the outer query.
A query's WHERE and HAVING clauses can specify noncorrelated subqueries if the
subquery resolves to a single row, as shown below:
In WHERE clause
=> SELECT COUNT(*) FROM SubQ1 WHERE SubQ1.a = (SELECT y from SubQ2);
In HAVING clause
=> SELECT COUNT(*) FROM SubQ1 GROUP BY SubQ1.a HAVING SubQ1.a = (SubQ1.a
& (SELECT y from SubQ2)

18. Correlated Subqueries

A correlated subquery typically obtains values from its outer query before
inner query executes. When the subquery returns, it passes its results to the outer
query.
You can use an outer join to obtain the same effect as a correlated subquery.
In the following example, the subquery needs values from
the [Link] column in the outer query:
1. Ex:
=> SELECT name, street, city, state FROM addresses
WHERE EXISTS (SELECT * FROM states WHERE [Link] = [Link]);
Vertica executes this query as follows:
1. The query extracts and evaluates each [Link] value in the outer
subquery records.
2. Then the query—using the EXISTS predicate—checks the addresses in the
inner (correlated) subquery.
3. Because it uses the EXISTS predicate, the query stops processing when it finds
the first match.
When Vertica executes this query, it translates the full query into a JOIN WITH SIPS.

2. Ex:
SELECT employee_number, name
FROM employees emp
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department = [Link]);

In the above case, for each employee, the inner query calculates the average salary
for their department.

19. Super Key vs

Candidate Key
Super Key Candidate Key
Can have one or more attributes, and Is a minimal super key, meaning it
may include extra, unnecessary contains no unnecessary columns
columns
Every candidate key is a super key, but All candidate keys are super keys, but
not every super key is a candidate key not all super keys are candidate keys.
There can be many super keys, some of Candidate keys are the smallest possible
which may have redundant columns. set of attributes that uniquely identify a
record
Example: {ID}, {Email} if both are
Example: {ID, Name}, {ID, Email}
minimal
20. Scalar Functions
1. Numeric Functions

MySQL provides a wide range of numeric functions that allow you to perform
mathematical calculations and manipulate numeric data. Here's a summary of
commonly used numeric functions in MySQL:

a. Arithmetic Functions
Function Description Example Result
ABS(x) Returns the absolute value of x. ABS(-10) 10

CEIL(x) or Returns the smallest integer greater

CEIL(4.3) 5
CEILING(x) than or equal to x.

Returns the largest integer less than

FLOOR(x) FLOOR(4.7) 4
or equal to x.
ROUND(x, d) Rounds x to d decimal places. ROUND(123.456, 2) 123.46

SIGN(x) Returns the sign of x (-1, 0, 1). SIGN(-42) -1

Truncates x to d decimal places TRUNCATE(123.456,

TRUNCATE(x, d) 123.45
without rounding. 2)

b. Mathematical Constants and Functions

Function Description Example Result
PI() Returns the value of π (pi). PI() 3.141593

Returns e^x (Euler's number raised to

EXP(x) EXP(1) 2.718282
the power of x).
LOG(x) Returns the natural logarithm of x. LOG(2.718282) 1

LOG10(x) Returns the base-10 logarithm of x. LOG10(1000) 3

POW(x, y) or
POWER(x, y)
Returns x raised to the power of y. POW(2, 3) 8

SQRT(x) Returns the square root of x. SQRT(16) 4

 c. Trigonometric Functions
Function Description Example Result
SIN(x) Returns the sine of x (in radians). SIN(PI()/2) 1

COS(x) Returns the cosine of x (in radians). COS(PI()) -1

TAN(x) Returns the tangent of x (in radians). TAN(PI()/4) 1

ASIN(x) Returns the arcsine of x (in radians). ASIN(1) 1.5708 (π/2)

ACOS(x) Returns the arccosine of x (in radians). ACOS(-1) 3.141593 (π)

Returns the arctangent of x (in

ATAN(x) ATAN(1) 0.785398 (π/4)
radians).
DEGREES(x) Converts x from radians to degrees. DEGREES(PI()/2) 90

RADIANS(x) Converts x from degrees to radians. RADIANS(180) 3.141593 (π)

d. Random Numbers
Function Description Example Result

Returns a random float value between

RAND() RAND() 0.548813 (varies)
0 and 1.

Returns a random float value seeded Consistent result for the

RAND(x) RAND(10)
by x. seed.

e. Bitwise Functions
Function Description Example Result
BIT_AND(x, y) Performs a bitwise AND operation on x and y. BIT_AND(5,3) 1

BIT_OR(x, y) Performs a bitwise OR operation on x and y. BIT_OR(5,3) 7

BIT_XOR(x, y) Performs a bitwise XOR operation on x and y. BIT_XOR(5,3) 6

f. Numeric Type Conversion

Function Description Example Result
CONV(x, from_base, Converts number x from one CONV(15, 10, 2) 1111
 Function Description Example Result
to_base) base to another.

Converts x to an unsigned CAST(123.45 AS

CAST(x AS UNSIGNED) 123
integer. UNSIGNED)

Usage Example

Result:

2. String Functions:

MySQL provides a variety of string functions to manipulate and process string

data. Below is a comprehensive list of commonly used string functions:

a. String Manipulation Functions

Function Description Example Result
CONCAT('Hello', ' ', Hello
CONCAT(str1, str2, ...) Concatenates strings. 'World') World

CONCAT_WS(separator, Concatenates strings CONCAT_WS('-', 2024-

str1, str2, ...) '2024', '12', '30') 12-30
with a specified
 Function Description Example Result

separator.

Returns the leftmost

LEFT(str, length) length characters of the LEFT('MySQL', 3) MyS
string.

Returns the rightmost

RIGHT(str, length) length characters of the RIGHT('MySQL', 3) SQL
string.

Extracts a substring
SUBSTRING(str, pos, SUBSTRING('MySQL', 2,
len) starting at pos for len 3)
ySQ
characters.

Removes
TRIM(str) or leading/trailing spaces
TRIM(' MySQL ') MySQL
TRIM([remstr] FROM str) or specified characters
from a string.
LTRIM(str) Removes leading spaces. LTRIM(' MySQL') MySQL

RTRIM(str) Removes trailing spaces. RTRIM('MySQL ') MySQL

REPLACE('Hello
REPLACE(str, from_str, Replaces occurrences of Hello
World', 'World',
to_str) from_str with to_str. SQL
'SQL')

Inserts newstr into str

INSERT(str, pos, len, INSERT('Hello', 2, 2,
newstr) starting at pos, replacing 'i') Hiilo
len characters.

b. String Case Conversion

Function Description Example Result

Converts a string to
UPPER(str) UPPER('mysql') MYSQL
uppercase.

Converts a string to
LOWER(str) LOWER('MySQL') mysql
lowercase.
 Function Description Example Result
INITCAP(str) CONCAT(UPPER(LEFT('mysql
(Not native; Capitalizes the first letter
',1)),LOWER(SUBSTRING('m Mysql
use CONCAT) of each word. ysql',2)))

c. String Length and Position

Function Description Example Result

Returns the byte length of a

LENGTH(str) LENGTH('MySQL') 5
string.

Returns the character length

CHAR_LENGTH(str) CHAR_LENGTH('MySQL') 5
of a string.

LOCATE(substr, str, Returns the position of the

LOCATE('S', 'MySQL') 3
pos) first occurrence of substr.
POSITION(substr IN POSITION('S' IN
str) Similar to LOCATE. 'MySQL')
3

Returns the position of the

INSTR(str, substr) INSTR('MySQL', 'y') 2
first occurrence of substr.

d. Padding and Repeating Strings

Function Description Example Result

LPAD(str, len, Left-pads the string str with padstr LPAD('SQL', 5,

00SQL
padstr) to length len. '0')

RPAD(str, len, Right-pads the string str with RPAD('SQL', 5,

SQL--
padstr) padstr to length len. '-')

REPEAT(str, count) Repeats a string count times. REPEAT('My', 3) MyMyMy

e. String Comparison
Function Description Example Result

STRCMP(str1, Compares two strings STRCMP('abc',

-1
str2) lexicographically. 'abd')

LIKE Checks if a string matches a 'Hello' LIKE 'H%' 1 (TRUE)

 Function Description Example Result

pattern.

Checks if a string does not match 'Hello' NOT LIKE 0

NOT LIKE
a pattern. 'H%' (FALSE)

f. String Encoding and Decoding

Function Description Example Result

Returns the ASCII code of the first

ASCII(str) ASCII('A') 65
character in the string.
CHAR(N, ...) Converts ASCII codes to characters. CHAR(65, 66, 67) ABC

Returns a hexadecimal representation of

HEX(str) HEX('ABC') 414243
the string.

Converts a hexadecimal string back to its

UNHEX(hex_str) UNHEX('414243') ABC
original form.

Usage Example

Result:
 3. Date Functions
MySQL provides a variety of date and time functions to handle, format, and
manipulate date and time values. Here's a detailed list of commonly used date
functions:

a. Current Date and Time

Function Description Example Result

CURDATE() Returns the current date. CURDATE() 2024-12-30

CURRENT_DATE() Synonym for CURDATE(). CURRENT_DATE() 2024-12-30

Returns the current date 2024-12-30

NOW() NOW()
and time. 14:25:36

CURRENT_TIMESTA CURRENT_TIMESTAM 2024-12-30

Synonym for NOW().
MP() P() 14:25:36

CURTIME() Returns the current time. CURTIME() 14:25:36

CURRENT_TIME() Synonym for CURTIME(). CURRENT_TIME() 14:25:36

b. Extracting Parts of Dates

Function Description Example Result

YEAR(date) Extracts the year from a date. YEAR('2024-12-30') 2024

Extracts the month (1-12) from

MONTH(date) MONTH('2024-12-30') 12
a date.

DAY(date) or Extracts the day of the month

DAY('2024-12-30') 30
DAYOFMONTH(date) (1-31).

Extracts the hour (0-23) from a

HOUR(time) HOUR('14:25:36') 14
time or datetime.

Extracts the minute (0-59) from

MINUTE(time) MINUTE('14:25:36') 25
a time or datetime.

Extracts the second (0-59) from

SECOND(time) SECOND('14:25:36') 36
a time or datetime.

Returns the weekday index DAYOFWEEK('2024-

DAYOFWEEK(date) 2
(1=Sunday, 7=Saturday). 12-30')
Function Description Example Result

Returns the day of the year (1- DAYOFYEAR('2024-12-

DAYOFYEAR(date) 365
366). 30')

Returns the week number (0-

WEEK(date) WEEK('2024-12-30') 52
53).

Returns the quarter (1-4) of the QUARTER('2024-12-

QUARTER(date) 4
year. 30')

c. Adding and Subtracting Dates

Function Description Example Result

DATE_ADD(date, Adds a time interval DATE_ADD('2024-12-30',

2024-01-09
INTERVAL value unit) to a date. INTERVAL 10 DAY)

DATE_SUB(date, Subtracts a time DATE_SUB('2024-12-30',

2024-12-20
INTERVAL value unit) interval from a date. INTERVAL 10 DAY)

ADDDATE(date, Synonym for ADDDATE('2024-12-30',

2025-01-30
INTERVAL value unit) DATE_ADD(). INTERVAL 1 MONTH)

SUBDATE(date, Synonym for SUBDATE('2024-12-30',

2024-11-30
INTERVAL value unit) DATE_SUB(). INTERVAL 1 MONTH)

d. Formatting Dates
Function Description Example Result

Formats a date
DATE_FORMAT(date, DATE_FORMAT('2024-12- 30-Dec-
according to the given
format) 30', '%d-%b-%Y') 2024
format.

Converts a string to a
STR_TO_DATE(str, STR_TO_DATE('30-12- 2024-
date using the
format) 2024', '%d-%m-%Y') 12-30
specified format.
 e. Common Format Specifiers:
Specifier Description Example

%Y Year (4 digits). 2024

%y Year (2 digits). 24

%M Full month name. December

%b Abbreviated month name. Dec

%d Day of the month (2 digits). 30

%H Hour (24-hour format). 14

%h Hour (12-hour format). 02

%i Minutes. 25

%s Seconds. 36

%p AM or PM. PM

f. Calculating Differences
Function Description Example Result

DATEDIFF(date1, Returns the difference in DATEDIFF('2024-12-30',

5
date2) days between two dates. '2024-12-25')

TIMEDIFF(time1, Returns the difference TIMEDIFF('14:25:36',

02:25:36
time2) between two times. '12:00:00')

Returns the difference TIMESTAMPDIFF(DAY,

TIMESTAMPDIFF(unit,
between two dates or '2024-12-25', '2024-12- 5
datetime1, datetime2)
times in the specified unit. 30')

g. Other Date Functions

Function Description Example Result

Returns the last day of the LAST_DAY('2024-12- 2024-12-

LAST_DAY(date)
month for the given date. 15') 31

MAKEDATE(year, Creates a date from the year and MAKEDATE(2024, 2024-12-

Function Description Example Result

dayofyear) day of the year. 365) 30

MAKETIME(hour, Creates a time from the given MAKETIME(14, 25,

14:25:36
minute, second) values. 36)

Usage Example

Result:

21. 3 levels of abstraction

22. PreparedStatement
The PreparedStatement interface is a sub interface of Statement. It is used to
execute parameterized query.
String sql="insert into emp values(?,?,?)";
Why use PreparedStatement?
Improves performance: The performance of the application will be faster if you use
PreparedStatement interface because query is compiled only once.
An important advantage of PreparedStatements is that they prevent SQL injection
attacks.
Try to find sample SQL Injection attacks
Metadata Objects:
The Question marks in Preparedstatment is called Metadata Objects

23. CallableStatement
CallableStatement interface is used to call the stored procedures and functions

24. StoredProcedure vs Function

Stored Procedure Function

is used to perform business logic. is used to perform calculation.

must not have the return type. must have the return type.

may return 0 or more values. may return only one values.

We can call functions from the procedure. Procedure cannot be called from
function.

Procedure supports input and output Function supports only input

 parameters. parameter.

Exception handling using try/catch block can Exception handling using try/catch
be used in stored procedures. can't be used in user defined
functions.

25. Transactions
Transaction represents a single unit of work
The ACID properties describes the transaction management well. ACID stands for
Atomicity, Consistency, Isolation and Durability.
Atomicity means either all successful or none.
Consistency ensures bringing the database from one consistent state to another
consistent state.
Isolation ensures that transaction is isolated from other transaction.
Durability means once a transaction has been committed, it will remain so, even in
the event of errors, power loss etc.

26. Advantage of Transaction Mangaement

fast performance It makes the performance fast because database is hit at the time
of commit.

27. Common Table Expression

A Common Table Expression (CTE) in SQL is a temporary result set that you can
reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often
used to simplify complex queries, improve readability, and organize query logic.
28. Views
In PostgreSQL (and many other relational database management systems), a view is
a virtual table that is defined by a SQL query. Unlike a physical table, a view does not
store data itself but rather provides a way to present data from one or more tables in
a specific format. Views can simplify complex queries, encapsulate business logic,
and enhance security by restricting access to specific data.
29. Sequence
a sequence is a database object used to generate a sequence of unique integer
values. Sequences are often used to generate unique primary key values for tables
automatically
Index
an index is a database object that enhances the speed of data retrieval operations on
a table at the cost of additional storage space and overhead during data modification
operations (inserts, updates, and deletes). Indexes are essential for optimizing query
performance, especially for large datasets.

Types of Indexes
 B-tree Index:
 The default and most common type of index.
 Suitable for equality and range queries.
  Supports =, <, <=, >, >=, and BETWEEN operators.
 Hash Index:
 Suitable for equality comparisons (=).
 Not as commonly used because it does not support range queries.
 GIN (Generalized Inverted Index):
 Suitable for indexing composite values, like arrays, JSONB, and full-text search.
 Efficient for containment queries (e.g., checking if an array contains a specific
value).
 GiST (Generalized Search Tree):
 Suitable for complex data types, such as geometric data types and full-text
search.
 Supports various types of queries depending on the operator class.
 SP-GiST (Space-Partitioned Generalized Search Tree):
 Suitable for data that can be divided into non-overlapping partitions.
 Useful for certain types of geometric and text search operations.
 BRIN (Block Range INdex):
 Suitable for very large tables where the data has some natural ordering.
 Efficient for range queries and less storage-intensive.
 Expression Index:
 An index on the result of an expression or function, rather than directly on
column values.
 Partial Index:
 An index that covers only a subset of rows in a table, based on a specified
condition.
30. Cursor
a cursor is a database object used to retrieve a set of rows generated by a query and
to process them one at a time. Cursors are particularly useful when dealing with
large datasets where you want to process each row individually without loading the
entire result set into memory at once.
Key Characteristics of Cursors
1. Row-by-Row Processing:
o Cursors allow you to fetch and process rows one at a time, which is
useful for operations that require iterative processing of each row in a
result set.
2. Memory Efficiency:
o By not loading the entire result set into memory, cursors help manage
memory usage efficiently, especially with large datasets.
3. State Management:
o Cursors maintain their position within the result set, allowing you to
fetch subsequent rows sequentially.
Declaring and Using Cursors
Cursors are typically used within PostgreSQL functions and stored procedures. Here’s
a basic outline of how to work with cursors:
1. Declare a Cursor:
o Define a cursor to hold the result set of a query.
2. Open the Cursor:
o Execute the query and establish the result set for the cursor.
3. Fetch from the Cursor:
o Retrieve rows from the cursor one at a time or in blocks.
4. Close the Cursor:
o Release the cursor and associated resources.

Example
Here’s a detailed example demonstrating the use of a cursor in a PostgreSQL
function:
1. Creating a Sample Table:
31. Windows Functions
In SQL, window functions are a powerful tool that allows you to perform calculations
across a set of rows that are related to the current row, without causing rows to
become grouped as with standard aggregate functions. This means you can access
and compare data from multiple rows while retaining the individual rows in the
result set.
Key Concepts
 Window: The "window" refers to the set of rows on which the function
operates. This window is defined relative to the current row.
 OVER() Clause: This clause is essential for window functions. It defines the
window by specifying how the rows are partitioned and ordered.
 PARTITION BY: This clause divides the rows into partitions or groups. The
window function is applied to each partition independently.
 ORDER BY: This clause specifies the order of rows within each partition. This is
crucial for functions that depend on the order of rows, like ROW_NUMBER()
or LAG().
Types of Window Functions
1. Aggregate Window Functions: These functions perform aggregate
calculations (like SUM(), AVG(), COUNT(), MIN(), MAX()) over a window of
rows.
o Example: Calculate the running total of sales for each day.
2. Ranking Window Functions: These functions assign a rank to each row within
a partition based on a specified order.
o ROW_NUMBER(): Assigns a unique sequential integer to each row
within a partition.
o RANK(): Assigns a rank to each row within a partition, with gaps in the
ranking if there are ties.
o DENSE_RANK(): Assigns a rank to each row within a partition, without
gaps in the ranking even if there are ties.
o NTILE(n): Divides the rows in each partition into n approximately equal
groups and assigns a group number to each row.
 3. Value Window Functions: These functions access values from other rows
within the window.
o LAG(column, offset, default): Accesses the value of a column from a
row that is offset rows before the current row.
o LEAD(column, offset, default): Accesses the value of a column from a
row that is offset rows after the current row.
o FIRST_VALUE(column): Returns the first value of a column in the
window.
o LAST_VALUE(column): Returns the last value of a column in the
window.
Example
Let's say you have a table called "Employees" with columns like "Department,"
"EmployeeID," and "Salary."
SQL
SELECT
Department,
EmployeeID,
Salary,
AVG(Salary) OVER (PARTITION BY Department) AS AverageDepartmentSalary
FROM
Employees;
This query calculates the average salary for each department and displays it
alongside each employee's salary. The PARTITION BY Department clause ensures that
the average salary is calculated separately for each department.
Benefits of Window Functions
 Simplified Queries: Window functions can simplify complex queries that
would otherwise require subqueries or self-joins.
 Improved Performance: In many cases, window functions can be more
efficient than equivalent queries using subqueries or joins.
 Enhanced Data Analysis: Window functions enable advanced data analysis
tasks like calculating running totals, moving averages, and rankings.
Window functions are a valuable tool in SQL for performing complex calculations and
analysis on data. They provide a concise and efficient way to access and compare
data from multiple rows without losing the individual row context.

32. SQLSTATE
Class P0 — PL/pgSQL
Error

P0000 plpgsql_error

P0001 raise_exception

P0002 no_data_found

P0003 too_many_rows

P0004 assert_failure