Introduction to SQL Commands for Noobs
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases.
It provides various types of commands that allow users to define, manipulate, control, and query data.
These commands are categorized into five main types:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL),
- Data Query Language (DQL).
Hazem Abbas
1. Data Definition Language (DDL)
DDL commands are used to define and modify the structure of database objects such as tables, indexes, and views. The primary DDL commands are:
RENAME: Used to rename an existing database object.
ALTER TABLE Employees RENAME TO Staff;
TRUNCATE: Used to delete all rows from a table without removing the table structure.
TRUNCATE TABLE Employees;
DROP: Used to delete a table or database object.
DROP TABLE Employees;
ALTER: Used to modify the structure of an existing table.
ALTER TABLE Employees
ADD COLUMN Salary decimal(10, 2);
CREATE: Used to create a new table or database object.
CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
BirthDate date
);
2. Data Manipulation Language (DML)
DML commands are used to manipulate the data within database objects. The primary DML commands are:
MERGE: Used to combine insert, update, and delete operations into a single statement.
MERGE INTO Employees AS Target
USING NewEmployees AS Source
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
UPDATE SET Target.FirstName = Source.FirstName,
Target.LastName = Source.LastName,
Target.BirthDate = Source.BirthDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (EmployeeID, FirstName, LastName, BirthDate)
VALUES (Source.EmployeeID, Source.FirstName, Source.LastName, Source.BirthDate);
DELETE: Used to remove rows from a table.
DELETE FROM Employees
WHERE EmployeeID = 1;
UPDATE: Used to modify existing data in a table.
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;
INSERT: Used to add new rows to a table.
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate)
VALUES (1, 'John', 'Doe', '1980-01-01');
3. Data Control Language (DCL)
DCL commands are used to control access to data within the database. The primary DCL commands are:
REVOKE: Used to remove privileges from users.
REVOKE SELECT, INSERT, UPDATE ON Employees FROM User1;
GRANT: Used to give privileges to users.
GRANT SELECT, INSERT, UPDATE ON Employees TO User1;
4- Transaction Control Language (TCL)
TCL commands are used to manage transactions in the database. The primary TCL commands are:
SAVEPOINT: Used to set a point within a transaction to which you can later roll back.
SAVEPOINT Savepoint1;
ROLLBACK: Used to undo changes made during the current transaction.
ROLLBACK;
COMMIT: Used to save all changes made during the current transaction.
COMMIT;
5. Data Query Language (DQL)
DQL commands are used to query and retrieve data from the database. The primary DQL command is:
SELECT: Used to retrieve data from one or more tables.
SELECT FirstName, LastName, BirthDate
FROM Employees
WHERE EmployeeID = 1;
Conclusion
Understanding and effectively using SQL commands is essential for managing and manipulating data within relational databases. Each category of SQL commands serves a specific purpose, from defining database structures to controlling access and managing transactions.
By mastering these commands, you can efficiently handle a wide range of database operations.
Hazem Abbas
