SQL (Structured Query Language) is used to manage and manipulate relational databases.
Here are the fundamental types of SQL statements, categorized by their purpose:
1. Data Definition Language (DDL) - For defining and managing database structures:
CREATE: Used to create new database objects like tables, databases, indexes, views, or
stored procedures.
o CREATE DATABASE database_name; (Creates a new database)
o CREATE TABLE table_name ( column1 datatype [constraints],
column2 datatype [constraints], ... ); (Creates a new table)
SQL
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
DepartmentID INT
);
ALTER: Used to modify the structure of an existing database object.
o ALTER TABLE table_name ADD column_name datatype [constraints];
(Adds a new column)
o ALTER TABLE table_name DROP COLUMN column_name; (Deletes a column)
o ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
(Changes the data type of a column)
SQL
ALTER TABLE Employees
ADD Email VARCHAR(100);
ALTER TABLE Employees
ALTER COLUMN DateOfBirth DATETIME;
DROP: Used to delete existing database objects.
o DROP DATABASE database_name; (Deletes an entire database)
o DROP TABLE table_name; (Deletes a table and all its data)
SQL
DROP TABLE Employees;
TRUNCATE: Removes all rows from a table, but keeps the table structure. It's faster
than DELETE for removing all rows because it doesn't log individual row deletions.
SQL
TRUNCATE TABLE Employees;
RENAME: Used to rename database objects.
o ALTER TABLE old_table_name RENAME TO new_table_name; (Renames a
table)
o ALTER TABLE table_name RENAME COLUMN old_column_name TO
new_column_name; (Renames a column - syntax can vary slightly by database
system)
2. Data Manipulation Language (DML) - For manipulating data within database
objects:
SELECT:Used to retrieve data from one or more tables. This is the most frequently
used DML command.
o SELECT * FROM table_name; (Selects all columns and all rows)
o SELECT column1, column2 FROM table_name WHERE condition; (Selects
specific columns with a filter)
SQL
SELECT FirstName, LastName, Email
FROM Employees
WHERE DepartmentID = 101;
SELECT *
FROM Products
ORDER BY Price DESC;
SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) > 5;
INSERT: Used to add new rows of data into a table.
o INSERT INTO table_name (column1, column2) VALUES (value1,
value2); (Inserts specific values into specified columns)
o INSERT INTO table_name VALUES (value1, value2, ...); (Inserts
values into all columns in order)
SQL
INSERT INTO Employees (EmployeeID, FirstName, LastName,
DateOfBirth, DepartmentID)
VALUES (1, 'John', 'Doe', '1985-03-15', 101);
INSERT INTO Products VALUES (10, 'Laptop', 1200.00, 50);
UPDATE: Used to modify existing data in a table.
o UPDATE table_name SET column1 = new_value1, column2 =
new_value2 WHERE condition;
SQL
UPDATE Employees
SET Email = '[Link]@[Link]', DepartmentID = 102
WHERE EmployeeID = 1;
DELETE: Used to remove rows of data from a table.
o DELETE FROM table_name WHERE condition; (Deletes specific rows)
o DELETE FROM table_name; (Deletes all rows from a table)
SQL
DELETE FROM Employees
WHERE EmployeeID = 1;
DELETE FROM Orders
WHERE OrderDate < '2024-01-01';
3. Data Control Language (DCL) - For controlling access to data:
GRANT:Used to give users or roles specific permissions (privileges) to access and
manipulate database objects.
SQL
GRANT SELECT, INSERT ON Employees TO user_hr;
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
REVOKE: Used to remove previously granted permissions.
SQL
REVOKE DELETE ON Employees FROM user_hr;
4. Transaction Control Language (TCL) - For managing transactions:
COMMIT: Saves all changes made during the current transaction permanently to the
database.
ROLLBACK: Undoes all changes made during the current transaction, restoring the
database to its state before the transaction began.
SAVEPOINT: Sets a point within a transaction to which you can later roll back.
SQL
START TRANSACTION; -- or BEGIN TRANSACTION;
INSERT INTO Accounts (AccountID, Balance) VALUES (1, 1000);
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1;
SAVEPOINT after_withdraw;
INSERT INTO Transactions (TransactionID, AccountID, Amount, Type)
VALUES (101, 1, -200, 'Withdrawal');
-- If an error occurs here, you can ROLLBACK TO after_withdraw;
COMMIT;
Common Clauses and Operators Used with SQL Statements:
WHERE: Filters rows based on a specified condition.
ORDER BY: Sorts the result set.
GROUP BY: Groups rows that have the same values in specified columns into summary
rows.
HAVING: Filters groups based on a specified condition (used with GROUP BY).
JOIN: Combines rows from two or more tables based on a related column between
them (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).
LIKE: Used for pattern matching.
IN: Specifies multiple possible values for a column.
BETWEEN: Selects values within a given range.
AND, OR, NOT: Logical operators.
COUNT(), SUM(), AVG(), MIN(), MAX(): Aggregate functions.
This comprehensive list covers the most common and essential SQL statements you'll
encounter and use in database management. The specific syntax might vary slightly
depending on the database system (e.g., MySQL, PostgreSQL, SQL Server, Oracle).
Generate code to prototype this with Canvas