—– SQL —–

Introduction

SQL, or Structured Query Language, is a standardized programming language used for managing and manipulating relational databases. It enables users to create, read, update, and delete data within a database. SQL is integral to database management systems (DBMS) like MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database. These categories help in managing and manipulating databases and their objects.

• DDL: Deals with schema and database structure.
• DML: Manages data within the schema objects.
• DCL: Handles permissions and access control.
• TCL: Manages transactions and their control.
• DQL: Focuses on querying the data.

Different Types of Keys

In SQL, keys are crucial elements in database design that establish relationships between tables and ensure the integrity and uniqueness of data. A key in SQL is a field (or combination of fields) that uniquely identifies a record in a table. Keys are used to enforce constraints, maintain data integrity, and establish relationships between tables.

Coding Best Practices

OLAP vs OLTP

Concepts and learning about OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are primarily relevant to SQL. Here’s how each technology relates to OLAP and OLTP:

Normalization vs Denormalizaion

The images illustrate the execution order of SQL queries, highlighting the difference between the logical and actual execution order of SQL clauses.

Sequence

The images illustrate the execution order of SQL queries, highlighting the difference between the logical and actual execution order of SQL clauses.

———————————— SYNTAX ———————————

SELECT

The SELECT statement in SQL is used to query data from a database. It is one of the most commonly used statements and allows you to specify exactly what data you want to retrieve and how it should be displayed. Here is a detailed breakdown of how to use the SELECT statement, including its syntax and various options:

WHERE

In SQL, the WHERE clause is used to filter records based on specific conditions. Here’s an explanation of how different types of conditions.

• Numerical Operators: Used for numerical comparisons (=, !=, <, <=, >, >=).
• Boolean: Combine multiple conditions (AND, OR, NOT).
• String: Used for textual comparisons and pattern matching (=, !=, LIKE, ILIKE).
• Regular Expression: Advanced pattern matching using regex syntax (supported in some SQL databases with operators like ~, ~*, or REGEXP).

GROUP BY — HAVING

The GROUP BY clause is used to group rows that have the same values in specified columns into aggregated data, while the HAVING clause is used to filter groups based on certain conditions.

• GROUP BY: Can work seperately.
• HAVING: Can not work Seperately. It can work along with GroupBy.

The HAVING clause is used to filter groups created by the GROUP BY clause. It’s similar to the WHERE clause, but WHERE cannot be used with aggregate functions, while HAVING can.

TIME STAMP:

A SQL timestamp is a data type used in relational databases to store date and time information with high precision. It includes both the date and the time, down to fractions of a second, making it useful for recording exact moments of events. SQL timestamps are commonly used for logging actions, tracking changes in data, and managing time-sensitive transactions. They ensure temporal accuracy and can be manipulated using various SQL functions for querying, formatting, and comparing date-time values.