What is SQL?
SQL (Structured Query Language) is a standard programming language used to communicate with and manage data in relational database management systems (RDBMS) like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle.
What SQL is used for:
Query data from a database (
SELECT
)Insert new records (
INSERT
)Update existing records (
UPDATE
)Delete records (
DELETE
)Create and modify database structures like tables and indexes (
CREATE
,ALTER
,DROP
)Control access to data (
GRANT
,REVOKE
)
What are the different types of SQL statements?
1. Data Definition Language (DDL)
These statements define the structure of the database objects like tables, indexes, and views.
Statements Purpose
CREATE Creates a new table, view, or database
ALTER Modifies an existing object
DROP Deletes an object
TRUNCATE Removes all records from a table
RENAME Renames a database object
2. Data Manipulation Language (DML)
These statements are used to manipulate data within tables.
Statements Purpose
SELECT Retrieves data
INSERT Adds new records
UPDATE Modifies existing records
DELETE Removes records
3. Data Control Language (DCL)
These statements manage access to the data in the database.
Statements Purpose
GRANT Gives user access privileges
REVOKE Withdraws access privileges
4. Transaction Control Language (TCL)
These statements manage changes made by DML statements and control transaction behavior.
Statements Purpose
COMMIT Saves all changes made in the transaction
ROOLBACK Undoes changes if something goes wrong
SAVEPOINT Sets a point to roll back to later
SET TRANSACTION Defines transaction properties
5. Data Query Language (DQL)
Some categorize SELECT
separately as DQL since it's used strictly for querying (not changing) data.
Statements Purpose
SELECT Retrieves data from tables
What is the difference between WHERE and HAVING?
WHERE
Filters rows before any grouping or aggregation.
Used with SELECT, UPDATE, DELETE statements.
Cannot be used with aggregate functions (like
SUM()
,AVG()
, etc.)
Example:
Filters individual rows where department = 'Sales'
.
HAVING
Filters groups after aggregation (
GROUP BY
).Used only with
GROUP BY
or aggregate functions.Can use aggregate functions like
COUNT()
,SUM()
.
Example:
Key Characteristics of a Primary Key:
Uniqueness: Each value must be unique across the table.
Not Null: A primary key column cannot contain NULL values.
Only one per table: A table can have only one primary key, but it can consist of multiple columns (called a composite key).
Example: