Structured Query Language (SQL)
Structured Query Language (SQL) is the standard language used to interact with
relational databases.
It allows users to store, retrieve, update and manage data efficiently through
simple commands.
It is known for its user-friendly syntax and powerful capabilities, SQL is widely
used across industries.
SQL Working
We interact with databases using SQL queries. DBMS tools like MySQL and SQL
Server have their own SQL engine and an interface where users can write and
execute SQL queries.
Components of a SQL System
Database: A structured collection of data stored in tables with rows and columns.
Tables: Store data and apply rules to keep it accurate and consistent.
Indexes: Help the database find data faster without scanning the whole table.
Views: Virtual tables created from SELECT queries for easy access to data.
Stored Procedures: Pre-saved SQL code that runs tasks and improves
performance and security.
Transactions: Group of SQL actions that either all succeed or all fail to keep
data safe.
Security & Permissions: Control who can view, change, or manage database
data.
Joins: Combine data from different tables based on relationships.
Rules for Writing SQL Queries:
There are certain rules for SQL which would ensure consistency and functionality
across databases. By following these rules, queries will be well formed and well
executed in any database.
Semicolon (;): Ends an SQL statement.
Case-Insensitive: SQL keywords like SELECT and INSERT are not case-
sensitive.
Whitespace: Spaces and new lines are allowed for better readability.
Reserved Words: Do not use SQL keywords as names, or write them in
quotes/backticks.
Comments :
Single-line: -- comment
Multi-line: /* comment */
Data Constraints: Use NOT NULL, UNIQUE, PRIMARY KEY, etc., to ensure data
accuracy.
String Values: Enclose strings in single quotes ('text').
Naming Rules:
We Start with a letter
We can only use Max 30 characters
We only use numbers and underscores ( _ )
SQL Commands | DDL, DQL, DML, DCL and TCL
Commands
SQL commands are fundamental building blocks used to perform given operations
on database. The operations include queries of data. creating a table, adding data to
tables, dropping the table, modifying the table and set permission for users.
SQL Commands are mainly categorized into five categories:
1. DDL - Data Definition Language
DDL (Data Definition Language) consists of SQL commands that can be used for
defining, altering and deleting database structures such as tables, indexes and
schemas. It simply deals with descriptions of the database schema and is used to
create and modify the structure of database objects in the database
Command Description Syntax
Create database or its objects (table, CREATE TABLE table_name
CREATE index, function, views, store (column1 data_type, column2
procedure and triggers) data_type, ...);
DROP Delete objects from the database DROP TABLE table_name;
ALTER TABLE table_name ADD
ALTER Alter the structure of the database COLUMN column_name
data_type;
TRUNCATE Remove all records from a table, TRUNCATE TABLE table_name;
including all spaces allocated for the
Command Description Syntax
records are removed
COMMENT ON TABLE
COMMENT Add comments to the data dictionary
table_name IS 'comment_text';
Rename an object existing in the RENAME TABLE old_table_name
RENAME
database TO new_table_name;
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for employee
ID, first name, last name and hire date.
2. DQL - Data Query Language
DQL is used to fetch data from the database. The main command is SELECT, which
retrieves records based on the query. The output is returned as a result set (a
temporary table) that can be viewed or used in applications.
Comman
d Description Syntax
It is used to retrieve data from SELECT column1, column2, ...FROM
SELECT the database table_name WHERE condition;
Indicates the table(s) from SELECT column1
FROM
which to retrieve data. FROM table_name;
Comman
d Description Syntax
SELECT column1
Filters rows before any grouping FROM table_name
WHERE
or aggregation WHERE condition;
SELECT column1,
Groups rows that have the same AVG_FUNCTION(column2)
GROUP BY values in specified columns. FROM table_name
GROUP BY column1;
SELECT column1,
AVG_FUNCTION(column2)
HAVING Filters the results of GROUP BY FROM table_name
GROUP BY column1
HAVING condition;
SELECT DISTINCT column1, column2,
Removes duplicate rows from ...
DISTINCT
the result set FROM table_name;
SELECT column1
Sorts the result set by one or
ORDER BY FROM table_name
more columns
ORDER BY column1 [ASC | DESC];
By default, it sorts in ascending SELECT * FROM table_name LIMIT
LIMIT number;
order unless specified as DESC
Note: DQL has only one command, SELECT. Other terms like FROM, WHERE,
GROUP BY, HAVING, ORDER BY, DISTINCT and LIMIT are clauses of SELECT,
not separate commands.
Example:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees first and last names, along with their hire dates, from
the employees table, specifically for those in the 'Sales' department, sorted by hire
date.
3. DML - Data Manipulation Language
DML commands are used to manipulate the data stored in database tables. With
DML, you can insert new records, update existing ones, delete unwanted data or
retrieve information.
Comman
d Description Syntax
INSERT INTO table_name (column1,
INSERT Insert data into a table
column2, ...) VALUES (value1, value2, ...);
Update existing data UPDATE table_name SET column1 = value1,
UPDATE
within a table column2 = value2 WHERE condition;
Delete records from a DELETE FROM table_name WHERE
DELETE
database table condition;
Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into employees table with first name 'Jane', last
name 'Smith' and department 'HR'.
4. DCL - Data Control Language
DCL (Data Control Language) includes commands such as GRANT and REVOKE
which mainly deal with the rights, permissions and other controls of the database
system. These commands are used to control access to data in the database by
granting or revoking permissions.
Comman
d Description Syntax
Assigns new privileges to a user GRANT privilege_type
account, allowing access to specific [(column_list)] ON [object_type]
GRANT
database objects, actions or object_name TO user [WITH
functions. GRANT OPTION];
Removes previously granted REVOKE [GRANT OPTION FOR]
privileges from a user account, privilege_type [(column_list)] ON
REVOKE
taking away their access to certain [object_type] object_name FROM
database objects or actions. user [CASCADE];
Example:
GRANT SELECT, UPDATE ON employees TO user_name;
This command grants the user user_name the permissions to select and update
records in the employees table.
5. TCL - Transaction Control Language
Transactions group a set of tasks into a single execution unit. Each transaction
begins with a specific task and ends when all the tasks in the group are successfully
completed. If any of the tasks fail, transaction fails. Therefore, a transaction has only
two results: success or failure.
Command Description Syntax
BEGIN BEGIN TRANSACTION
Starts a new transaction
TRANSACTION [transaction_name];
Saves all changes made during
COMMIT COMMIT;
the transaction
Undoes all changes made
ROLLBACK ROLLBACK;
during the transaction
Command Description Syntax
Creates a savepoint within the
SAVEPOINT SAVEPOINT savepoint_name;
current transaction
Example:
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made and a savepoint is set. If
needed, the transaction can be rolled back to the savepoint before being committed.
Data Definition Language (DDL) is a subset of SQL used to define, modify, and manage database
structures (schema) rather than the data itself. It includes commands
like CREATE, ALTER, DROP, TRUNCATE, and RENAME to structure tables, indexes, and
schemas. DDL statements are typically auto-committed
Key Aspects of DDL:
Purpose: Defines the framework of the database, including tables, indexes, and constraints.
Common Commands:
o CREATE:
Creates new databases, tables, or views
o ALTER: Modifies existing structures (e.g., adding columns).
o DROP: Deletes objects from the database.
o TRUNCATE: Removes all records from a table while keeping the structure.
o RENAME: Renames database objects.
Applications: Used for managing database constraints (primary/foreign keys), creating indexes
for performance, and setting up database security (grant/revoke).
DDL differs from DML (Data Manipulation Language), which deals with the actual data within
the table (e.g., INSERT , UPDATE , DELETE )
Data Manipulation Language (DML) is a subset of SQL used to manage and manipulate data
within database objects (tables, views). Core DML commands include INSERT (add
data), UPDATE (modify data), and DELETE (remove data), with SELECT often included for
retrieving records. It focuses on row-level data manipulation.
Key Aspects of DML:
Core Commands:
o INSERT: Adds new rows to a table.
o UPDATE: Modifies existing data within a table
o DELETE: Removes records or rows.
o SELECT: Retrieves data, though sometimes classified under DQL (Data Query Language).
Types of DML:
o High-Level (Non-Procedural): User specifies what data is needed (e.g., SQL).
o Low-Level (Procedural): User specifies how to access the data.
DML vs. DDL: While DML operates on the data within the rows, Data Definition
Language (DDL) manages the structure of the database (tables, indexes).
Transactions: DML operations often form transactions that may require management
via TCL (Transaction Control Language).