Contact Us

Kockpit is here to help you

Business Form

Chetna Madaan

Exploring Advanced SQL Concepts

Structured Query Language (SQL) is the language databases understand. You may already know the basics of SQL, like how to retrieve information from a database, but there's a whole world of advanced SQL waiting to be explored.

Advanced SQL takes your database skills to the next level. While SQL basics help you fetch and manage data, advanced topics empower you to do more. 

With advanced SQL, you’ll learn to work with functions that help you analyze data creatively, simplify complicated queries, and connect information from different tables effortlessly. You'll also discover how to make your queries run faster and ensure data stays secure. 

Advanced SQL is the path to becoming a data wizard, capable of solving complex problems and uncovering valuable insights within your database.

What are SQL Joins and its Types?

Joins are clauses that are used to join one or more tables. There are basically five types of SQL joins, and they are mentioned below along with their respective syntaxes.


Types of SQL Joins: 

  1. (INNER) JOIN: Returns records from both tables that have matching values.

Syntax:

SELECT columns

FROM table1

INNER JOIN table2 ON table1.column_name = table2.column_name;


  1. LEFT (OUTER) JOIN: Retrieve all records from the left table and include any matching records from the right table.

Syntax: 

SELECT columns

FROM left_table

LEFT JOIN right_table ON left_table.column_name = right_table.column_name;


  1. RIGHT (OUTER) JOIN: Retrieve all records from the right table and include any matching records from the left table.

Syntax: 

SELECT columns

FROM left_table

RIGHT JOIN right_table ON left_table.column_name = right_table.column_name;


  1. FULL (OUTER) JOIN: When there is a match in the left (table1) or right (table2) table records, the FULL OUTER JOIN keyword returns all records.

Syntax: 

SELECT columns

FROM table1

FULL JOIN table2 ON table1.column_name = table2.column_name;




  1. Self-Join: A self-join is similar to a regular join, but the table is joined with itself.

Syntax: 

SELECT t1.column_name, t2.column_name

FROM table_name AS t1

INNER JOIN table_name AS t2 ON t1.some_column = t2.some_column;


Now, let’s take a look at different types of Subquery and what they really are.

Subquery in SQL

A subquery is a query that is contained within or nested within another query. In other words, a Subquery is a query that is embedded in the WHERE clause of another SQL query.

Syntax: 

SELECT columns FROM table

WHERE column OPERATOR (SELECT columns FROM table WHERE condition);


Correlated Subquery: 

Correlated subqueries are used for row-by-row processing. Each subquery is run once for each row of the outer query.

Syntax: 

SELECT column1, column2, …. FROM table1 outer WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = outer.expr2);


Nested Subqueries versus Correlated Subqueries: 

In a normal nested subquery, the inner SELECT query runs first and executes once, returning values for the main query to use. However, a correlated subquery runs once for each candidate row considered by the outer query. In other words, the outer query drives the inner query.

Common Table Expressions (CTEs) in SQL

A common table expression, or CTE, is a named temporary result set created by a simple SELECT statement that can be used in another SELECT statement. Each SQL CTE is analogous to a named query, the results of which are saved in a virtual table (a CTE) to be referenced later in the main query.

Syntax: 

WITH my_cte AS (SELECT a,b,c FROM T1) 

SELECT a,c FROM my_cte WHERE ....


Recursive Queries in SQL:

Recursive queries, also known as recursive common table expressions (CTEs), are a useful SQL feature for working with hierarchical or recursive data structures. These queries allow you to retrieve data from a single table that has a parent-child relationship.

Syntax: 

WITH RECURSIVE cte_name (non-recursive columns) AS (

    -- Anchor Member

    SELECT non-recursive columns FROM your_table WHERE condition

    UNION ALL

    -- Recursive Member

    SELECT non-recursive columns FROM your_table INNER JOIN cte_name ON join_condition

)

SELECT * FROM cte_name;


Index in SQL

Indexes make it faster to find and fetch data from a database. The indexes are not visible to users; they are only used to speed up searches and queries.

Syntaxes: 

  1. CREATE INDEX Syntax: Creates a table index. Duplicate values are allowed.

Syntax: 

CREATE INDEX index_name

ON table_name (column1, column2, ...);


  1. CREATE UNIQUE INDEX Syntax: Creates a distinctive index on a table. Duplicate values are not allowed.

Syntax: 

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);


  1. DROP INDEX Statement: The DROP INDEX statement is used to delete a table index.

Syntax: 

DROP INDEX table_name.index_name;


Types of Indexing:

  • Clustered

  • Non-clustered

Clustered Indexes:

Clustered indexes are a single index per table that uses the primary key to organize the data.

Note: When the primary key is defined, the clustered index is created automatically.

Non-Clustered Indexes

Non-clustered indexes are sorted references from the main table for a specific field that hold pointers back to the table's original entries.

Note: Created the same as the main Syntax mentioned above.

Window Functions in SQL

Window functions perform aggregate and ranking functions on a specific window (set of rows). The OVER clause is used together with window functions to define that window. The OVER clause accomplishes two tasks: 

  • Rows are partitioned to form a set of rows. (The clause PARTITION BY is used.) 

  • Rows within those partitions are ordered in a specific order. (The ORDER BY clause is employed.)

Aggregate Window Function

  1. AVG () Function: Calculates the average of a specific column within a given window frame.

Syntax: 

AVG(column_name) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN n PRECEDING AND m FOLLOWING)


  1. MAX() Function: Returns the maximum value from a specific column within a specified window frame.

Syntax: 

MAX(column_name) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN n PRECEDING AND m FOLLOWING)


  1. MIN() Function: Returns the minimum value from a specified column within a window frame.

Syntax: 

MIN(column_name) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN n PRECEDING AND m FOLLOWING)


  1. SUM() Function: Calculates the sum of a specific column within a specified window frame.

Syntax: 

SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN n PRECEDING AND m FOLLOWING)


  1. COUNT() Function: Count the number of rows within a particular window frame or partition.

Syntax: 

COUNT(expression) OVER (PARTITION BY partition_expression ORDER BY order_expression frame_specification)



Ranking Window Function

  1. ROW_NUMBER() Function: Assigns a unique row number to each row in the result set, beginning with 1 for the first row.

Syntax: 

ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column)


  1. RANK() Function: Based on the specified ordering, assigns a unique rank to each row. Ties are assigned the same rank, and the following rank is skipped.

Syntax: 

RANK() OVER (PARTITION BY partition_column ORDER BY order_column)


  1. DENSE_RANK() Function: It works in the same way as RANK(), but instead of skipping ranks in the event of a tie, it assigns ranks in series.

Syntax: 

DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column)


  1. PERCENT_RANK() Function: Calculates a row's relative rank within the result set. It returns a value between 0 and 1, with 0 representing the lowest and 1 representing the highest.

Syntax: 

PERCENT_RANK() OVER ([PARTITION BY partition_expression, ... ] ORDER BY sort_expression)


  1. NTILE() Function: Divides the result set into a specified number of roughly equal "buckets" and assigns a bucket number to each row.

Syntax: 

NTILE(n) OVER ([PARTITION BY partition_expression, ... ] ORDER BY sort_expression)


Value Window Function

  1. LEAD() Function: LEAD() retrieves a specific column's value from the next row within the window frame.

Syntax: 

LEAD(column_name, n) OVER (PARTITION BY partition_column ORDER BY order_column)


  1. LAG() Function: LAG() retrieves a specific column's value from the previous row within the window frame.

Syntax: 

LAG(column_name, n) OVER (PARTITION BY partition_column ORDER BY order_column)


  1. FIRST_VALUE() Function: Returns the value of a particular column from the first row of a window frame.

Syntax: 

FIRST_VALUE(column_name) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)


  1. LAST_VALUE() Function: Returns the value of a specific column from the last row in a window frame.

Syntax: 

LAST_VALUE(column_name) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)


  1. Nth_VALUE() Function: Allows you to retrieve the value of a specified expression from the row at a specific position (N) within the ORDER BY clause-defined window frame.

Syntax: 

NTH_VALUE(expression, N) OVER ([PARTITION BY partition_expression, ... ] ORDER BY sort_expression ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)


What Does Trigger Mean in SQL?

A trigger is a database-stored procedure that is automatically invoked whenever a special event occurs in the database.

For example, A trigger can be triggered when a row is inserted into a specified table or when specific table columns are updated. To put it simply, a trigger is a collection of SQL statements with specific names that are stored in system memory.

Syntax: 

CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name [FOR EACH ROW] [WHEN (condition)] BEGIN

   -- Trigger action statements

END;


Syntax to list Triggers:

FROM sys.triggers, SELECT name, is_instead_of_trigger IF type = ‘TR’;


Types of Triggers:

  1. DDL Triggers: The DDL triggers are activated by Data Definition Language (DDL) command events such as Create_table, Create_view, drop_table, Drop_view, and Alter_table.

  2. DML Triggers: The Data Manipulation Language (DML) command events beginning with Insert, Update, and Delete activate the DML triggers corresponding to insert_table, update_view, and delete_table.

  3. Logon Triggers: A logon trigger is a type of SQL Server database trigger that is executed automatically when a user logs into a SQL Server instance.


BEFORE and AFTER Trigger

BEFORE triggers execute the trigger action prior to the execution of the triggering statement. 

AFTER triggers execute the trigger action after the triggering statement has been executed.

Stored Procedure in SQL

A stored procedure is a piece of SQL code that has been prepared and can be saved and reused multiple times.

Syntax: 

CREATE PROCEDURE procedure_name @parameter1 data_type, @parameter2 data_type, ... AS BEGIN

    -- SQL statements and procedural logic here

END;

Syntax to execute a Stored Procedure:

EXEC procedure_name;


Syntax to execute a Stored Procedure with parameters: 

EXEC procedure_name parameter1_value, parameter2_value, …


https://cdn.revealbi.io/wp-content/uploads/2021/08/what-is-stored-procedure.png

Transactions in SQL

A transaction is a unit or sequence of operations on a database. Transactions are completed in a logical order, whether manually by a user or automatically by a database program.


Properties of Transactions:

The four standard properties of transactions are listed below and are commonly abbreviated as ACID.

  • Atomicity − ensures that all operations within the work unit are successfully completed. Otherwise, the transaction is aborted at the point of failure, and all previous operations are rolled back.

  • Consistency − ensures that the database changes states properly after a successfully committed transaction.

  • Isolation − allows transactions to run independently and transparently.

  • Durability − ensures that the result or effect of a committed transaction is preserved in the event of a system failure.


Transactional Control Commands

To control transactions, use the following commands:

  • COMMIT− to save the changes.

  • ROLLBACK− to roll back the changes.

  • SAVEPOINT− ROLLBACK POINTS are created within the groups of transactions.

  • SET TRANSACTION− Places a name on a transaction.

DCL (Data Control Language) Commands in SQL

DCL includes commands like GRANT and REVOKE that deal with the database system's rights, permissions, and other controls. 


List of  DCL commands: 

  1. GRANT: This command grants users access to the database.

Syntax: 

GRANT permission(s) ON object TO user/role;


  1. REVOKE: This command removes the user's access privileges granted by the GRANT command.

Syntax: 

REVOKE permission(s) ON object FROM user/role;  

Materialized Views in SQL

A materialized view, also known as a materialized query table in relational database management systems (RDBMS), is a database object that stores the results of a query or computation in a persistent, pre-computed form.

Materialized views are used to improve query performance by storing and refreshing data in such a way that the need to recompute results on each query execution is reduced.

Dynamic SQL

Dynamic SQL is a programming technique that allows you to write SQL queries while they are running. Dynamic SQL can be used to create general and versatile SQL queries.

The syntax for dynamic SQL is to make it string as below:

'SELECT statement';


Steps to use Dynamic SQL:

  1. Declare two variables, @var1 to hold the table name and @var2 to hold the dynamic SQL:

DECLARE 

@var1 NVARCHAR(MAX), 

@var2 NVARCHAR(MAX);


  1. Set the @var1 variable's value to table_name:

SET @var1 = N'table_name';


  1. Add the SELECT statement to the table name parameter to generate the dynamic SQL:

SET @var2= N'SELECT * FROM ' + @var1;


  1. Using the @var2 parameter, run the sp_executesql stored procedure:

EXEC sp_executesql @var2;


SQL and NoSQL Databases

SQL (Structured Query Language) and NoSQL (Not Only SQL) are two categories of database management systems that differ in terms of data structure, data models, and usage.

A comparison of SQL and NoSQL databases follows

Aspect

SQL (Structured Query Language)

NoSQL (Not Only SQL)

Data Model

Relational: Tabular, rows, and columns

Various, including document, key-value, column-family, and graph models

Schema

Strict schema with predefined structure

Flexible or schema-less with dynamic or evolving structure

Query Language

SQL (Structured Query Language)

Proprietary query languages or APIs

ACID Transactions

ACID-compliant (Atomicity, Consistency, Isolation, Durability)

May support ACID, but often prioritize other features

Scalability

Typically vertical scaling (adding more resources to a single server)

Typically horizontal scaling (adding more servers to a distributed system)

Data Integrity and Consistency

Strong consistency and transaction support

Eventual consistency, with trade-offs in consistency for performance

Use Cases

Suitable for structured, well-defined data and applications that require complex queries

Suited for unstructured or semi-structured data, high scalability, and applications with changing data needs

Examples

MySQL, PostgreSQL, Oracle, SQL Server

MongoDB, Cassandra, Redis, Couchbase

Relationships and Joins

Relational databases support complex relationships and JOIN operations

NoSQL databases generally avoid complex joins and promote denormalization

Flexibility in Data Handling

Limited flexibility in handling complex data structures

Offers more flexibility to handle diverse and evolving data structures

Community and Ecosystem

Mature with well-established communities and tools

Rapidly evolving with a diverse range of databases and communities

Maintenance and Scalability Effort

Typically, requires significant maintenance and expertise

May require less maintenance, but may need more architectural design for scaling

Cost

Often higher licensing and operational costs

May have lower licensing and operational costs, but can vary

Data Synchronization and Integration

Relational databases may require ETL processes for data integration

NoSQL databases may offer built-in support for data synchronization and integration


Conclusion

Advanced SQL cheat sheets serve as quick reference guides for database professionals. They reduce complex database information to simple, quick information. This guide can help you with things like speeding up your queries, protecting your data, and handling advanced SQL tasks. It indicates how to use cool features such as stored procedures and window functions for data analysis. You can also learn how to protect your data from malicious software and how to use materialized views to get data faster. Essentially, these cheat sheets are extremely helpful in becoming an SQL pro.