Introduction to SQL

SQL is an abbreviation that means Structured Query Language, and is a domain-specific programming language designed for managing and manipulating relational databases. It is widely used for managing and querying data in relational database management systems (RDBMS) or for stream processing in a relational data stream management system (RDSMS).

In this article, I'm gonna tell you some about some objectives of SQL:

• Explore  basic commands and functions of SQL
• How to use SQL for data administration (to create tables, indexes, and views)
• How to use SQL for data manipulation (to add, modify, delete, and retrieve data)
• How to use SQL to query a database to extract useful information

Introduction to SQL functions

SQL functions fit into two broad categories:

Data definition language  -  SQL includes commands to:

• Create database objects, such as tables, indexes, and views
• Define access rights to those database objects

•    Includes commands to insert, update, delete, and retrieve data within database tables
  
  

  DDL (Data Definition Language)

SQL includes commands for defining and managing the structure of a database, such as creating tables, altering table structures, and defining relationships between tables.

• CREATE – is used to create the database or its objects (like table, index, function, views, store procedure, and triggers)
• DROP – is used to delete objects from the database
• ALTER -is used to alter the structure of the database
• TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed
• COMMENT –is used to add comments to the data dictionary
• RENAME –is used to rename an object existing in the database
• CREATE INDEX - create an index for the table

CREATE VIEW - create a dynamic subset of rows/columns from one or more tables

DML (Data Manipulation Language)

SQL includes commands for manipulating data within a database, such as inserting, updating, and deleting records.

• SELECT – is used to retrieve data from the database
• INSERT – is used to add data to a table
• UPDATE – is used to change or update existing data within a table
• DELETE – is used to delete records from a database table
  
  

Data Types

SQL Constraints

• NOT NULL constraint  - Ensures that column does not accept nulls
• UNIQUE constraint   - Ensures that all values in a column are unique
• DEFAULT constraint  - Assigns value to attribute when a new row is added to the table
• CHECK constraint   - Validates data when an attribute value is entered

Data Manipulation Commands

1. Adding table rows
2. Listing table rows
3. Updating table rows
4. Restoring table contents
5. Deleting table rows
6. Inserting table rows with a select subquery

SQL statement lifecycle

The main database modules responsible for processing a SQL statement are:

• the Parser
• the Optimizer
• the Executor

Adding Table Rows

INSERT   - Used to enter data into a table

Syntax:

INSERT INTO table_name (column1, column2, column3) VALUES ( value1, value2, value3);

Listing Table Rows

SELECT  Used to list contents of table

Syntax:

SELECT  *  FROM table_name;

SELECT column_1, column_2 FROM table_name ; 

• column_1, column_2:  names of the fields of the table
• table_name:  from where we want to fetch

Updating Table Rows

UPDATE  Modify data in a table

Syntax:

UPDATE table_name SET column_1 = 'value1', column_2 = 'value2'  WHERE column_id = 'someId';

• table_name: name of the table
• column1: name of the first, second, third column...
• value1: the new value for the first, second, third column...
• condition: condition to select the rows for which the values of columns need to be updated.

Deleting Table Rows

DELETE  Deletes a table row

Syntax:

DELETE FROM table_name WHERE some_condition;

1. table_name: name of the table

2. some_condition: condition to choose a particular record.

WHERE condition is optional

If the WHERE condition is not specified, all rows from the specified table will be deleted

Inserting Table Rows with a Select Subquery

INSERT

Inserts multiple rows from another table (source)  Uses SELECT subquery Query that is embedded (or nested) inside another query Executed first

INSERT INTO table_name SELECT columnlist FROM table_name_1;

Selecting Rows with  Conditional Restrictions

SELECT <COLUMN NAME>

FROM <TABLE NAME>

WHERE <CONDITION>

GROUP BY <COLUMN LIST>

HAVING <CRITERIA FOR FUNCTION RESULTS>

ORDER BY <COLUMN LIST>

Special Operators

• BETWEEN  - used to check whether attribute value is within a range
• IS NULL - used to check whether attribute value is null
• LIKE - used to check whether attribute value matches given string pattern
• IN -used to check whether attribute value matches any value within a value list
• EXISTS -  Used to check if the subquery returns any rows

Advanced Select Queries

SQL provides useful functions that can:

• Count 
• Find minimum and maximum values
• Calculate averages

SQL allows users to limit queries to only those entries having no duplicates or entries whose duplicates may be grouped

Virtual Tables: Creating a View

• View is a virtual table based on SELECT query, can contain columns, computed columns, aliases, and aggregate functions from one or more tables
• Base tables are tables on which view is based 
• Create view by using CREATE VIEW command

1. Data Integrity: SQL enforces data integrity by supporting constraints such as primary keys, foreign keys, unique constraints, and check constraints. These constraints help ensure the accuracy and reliability of the data stored in the database.

2. Normalization: SQL databases are designed to support the principles of normalization, which is a process of organizing data to reduce redundancy and improve data integrity.

3. Transactions: SQL supports transactions, which are sequences of one or more SQL statements that are executed as a single unit. Transactions ensure the consistency and integrity of the database.

4. Security: SQL provides security features to control access to data and database objects. This includes user authentication, authorization, and encryption.

Popular relational database management systems that use SQL include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite, among others. Each of these systems implements SQL with some variations, and they may also include additional features beyond the standard SQL language.