SQL (Structured Query Language) is a query language used for managing and manipulating relational databases. It provides a standardized way to interact with relational database management systems (RDBMS) and perform various operations such as querying data, defining database structures, inserting, updating, and deleting data, and managing database security.
SQL is a declarative language, meaning you specify what data you want to retrieve or manipulate, and the database management system determines how to perform the operation. It is widely used in managing data in applications, websites, and other software systems that rely on relational databases.
Key features and uses of SQL include:
Data Definition Language (DDL): SQL includes DDL statements to define and manage database objects such as tables, views, indexes, and constraints.
Data Manipulation Language (DML): SQL provides DML statements to insert, update, delete, and retrieve data from tables.
Data Control Language (DCL): SQL includes DCL statements to grant or revoke privileges and manage access control to the database objects.
Transaction Control Language (TCL): SQL includes TCL statements to control the flow and outcome of transactions, ensuring data integrity and consistency.
Data Query Language (DQL): SQL commands that are used to retrieve and manipulate data from a database.
📝 Note: In this article I will use PostgreSql, but you can apply to another RDBMS.
If you want more productive open your sql command liner and write sql scripts.
📌 Now let's deep dive SQL queries.
🧊 DDL -> Data Definition Language
DDL is a set of SQL commands used to create, modify, and delete database structures but not data.
COMMANDS
✅ CREATE: Creates a new database, table, view, index, or other database objects.
✅ ALTER: Modifies the structure of an existing database object, such as a table or column.
✅ DROP: Deletes a database object, such as a table or view.
✅ TRUNCATE: Removes all data from a table, but keeps the table structure intact. (NOTE: CAN’T ROLLBACK)
✅ RENAME: Renames a database object.
✅ COMMENT: This is used to add comments to the data dictionary.
1️⃣ CREATE
Let’s create a database table named “student” with columns to store student information such as ID, name, surname, age, course, mark, birthplace, and creation timestamp.
CREATE TABLE student
(
id BIGSERIAL PRIMARY KEY,
name VARCHAR(32),
surname VARCHAR(32),
age INT,
course VARCHAR(32),
mark NUMERIC(4, 2),
birth_place TEXT,
created_at TIMESTAMP
);2️⃣ALTER
Add a new column called “mail” to the existing “student” table.
ALTER TABLE student
ADD COLUMN mail VARCHAR(32);Rename the column “mail” in the “student” table to “email”.
ALTER TABLE student
RENAME COLUMN mail TO email;Remove the column “email” from the “student” table
ALTER TABLE student
DROP COLUMN email;3️⃣COMMENT
Add a comment to the “student” table.
COMMENT ON TABLE student is 'This table store student information';Add a comment to the “name” column of the “student” table.
COMMENT ON COLUMN student.name is 'Name of student ex: Ali';4️⃣TRUNCATE
Remove all rows and data from the “student” table
TRUNCATE TABLE student;4️⃣DROP
Delete the “student” table from the database, including all its data and structure.
DROP TABLE student;🧩 DML -> Data Manipulation Language
DML is a category of SQL commands that are used to manipulate and modify data within a database.
COMMANDS
✅ INSERT: Inserts new data into a table.
✅ UPDATE: Modifies existing data in a table.
✅ DELETE: Deletes data from a table.
1️⃣ INSERT
Let’s insert a new record into the “student” table with the values ‘John’ for the name column, ‘Computer Science’ for the course column, and 84.5 for the mark column.
INSERT INTO student (name, course, mark)
VALUES ('John', 'Computer Science', 84.5);Insert multiple records into the “student” table with each record containing values for the name, surname, age, course, mark, birth_place, and created_at columns, using the provided values and the current timestamp for the created_at column.
INSERT INTO student (name, surname, age, course, mark, birth_place, created_at)
VALUES ('John', 'Doe', 20, 'Mathematics', 85.5, 'New York', CURRENT_TIMESTAMP),
('Jane', 'Smith', 22, 'Computer Science', 88.2, 'London', CURRENT_TIMESTAMP),
('Michael', 'Johnson', 19, 'Chemistry', 91.8, 'Los Angeles', CURRENT_TIMESTAMP),
('Emily', 'Brown', 21, 'Biology', 79.9, 'Sydney', CURRENT_TIMESTAMP),
('David', 'Wilson', 23, 'Computer Science', 88.1, 'Toronto', CURRENT_TIMESTAMP),
('Sarah', 'Anderson', 20, 'History', 75.6, 'Paris', CURRENT_TIMESTAMP),
('Daniel', 'Taylor', 22, 'English Literature', 82.3, 'Berlin', CURRENT_TIMESTAMP),
('Olivia', 'Martinez', 23, 'Sociology', 89.7, 'Madrid', CURRENT_TIMESTAMP),
('Jacob', 'Garcia', 21, 'Computer Science', 99.4, 'Rome', CURRENT_TIMESTAMP),
('Sophia', 'Lopez', 23, 'Sociology', 84.6, 'Tokyo', CURRENT_TIMESTAMP);2️⃣ UPDATE
Update the “mark” column of the “student” table to 95.7 for the record with an “id” value of 1.
UPDATE student
SET mark = 95.7
WHERE id = 1;Updates the “mark” and “created_at” columns of the “student” table, setting the mark to 70 and the created_at to the current timestamp, for the records where the course is ‘Computer Science’ and the mark is less than 70.
UPDATE student
SET mark = 70,
created_at = CURRENT_TIMESTAMP
WHERE course = 'Computer Science'
AND mark < 70;3️⃣ DELETE
Delete the record from the “student” table where the id value is 2.
DELETE
FROM student
WHERE id = 2;Delete records from the “student” table where the name column is NULL (empty or not provided)
DELETE
FROM student
WHERE name ISNULL;🌏 DQL -> Data Query Language
DQL is a category of SQL commands that are used to retrieve an manipulate data from a database.
COMMANDS
✅ SELECT: Retrieves data from one or more tables based on specified conditions.
✅ FROM: Specifies the table(s) from which to retrieve data.
✅ WHERE: Filters the rows based on specified conditions.
✅ GROUP BY: Groups the result set by one or more columns.
✅ HAVING: Filters the grouped result set based on specified conditions.
✅ ORDER BY: Sorts the result set based on specified columns.
1️⃣ SELECT, FROM, WHERE …
Retrieve all records from the “student” table, displaying all columns and rows.
SELECT *
FROM student;Retrieve the “name”, “age”, and “course” columns from the “student” table, displaying only those specific columns for each row.
SELECT name, age, course
FROM student;Retrieves the “name”, “age”, and “course” columns from the “student” table, but only for rows where the “age” is greater than 20.
SELECT name, age, course
FROM student
WHERE age > 20;Retrieve the “name”, “age”, and “course” columns from the “student” table for rows where the “age” is greater than 20 or the “name” is equal to ‘John’.
SELECT name, age, course
FROM student
WHERE age > 20
or name = 'John';Retrieve the “name”, “course”, and “mark” columns from the “student” table for rows where the “mark” is between 80 and 90 (inclusive).
SELECT name, course, mark
FROM student
WHERE mark BETWEEN 80 and 90;Retrieve the “name”, “course”, and “mark” columns from the “student” table for rows where the “mark” is between 80 and 90 (inclusive) and the “course” is ‘Computer Science’.
SELECT name, course, mark
FROM student
WHERE mark BETWEEN 80 and 90
AND course = 'Computer Science';Retrieve the “name”, “course”, and “mark” columns from the “student” table for rows where the “course” is (not) either ‘Computer Science’, ‘Sociology’, or ‘History’.
SELECT name, course, mark
FROM student
WHERE course IN ('Computer Science', 'Sociology', 'History');
SELECT name, course, mark
FROM student
WHERE course NOT IN ('Computer Science', 'Sociology', 'History');Retrieve all columns and rows from the “student” table where the “name” starts with the letter ‘S’, ends with the letter ‘a’, contains the substring ‘al’ anywhere within it.
SELECT *
FROM student
WHERE name LIKE 'S%';
SELECT *
FROM student
WHERE name LIKE '%a';
SELECT *
FROM student
WHERE name LIKE '%al%';Retrieve the “name”, “age”, and “course” columns from the “student” table and sorts the results in descending order based on the “age” column.
SELECT name, age, course
FROM student
ORDER BY age DESC;Retrieve the “name”, “age”, and “course” columns from the “student” table, sorts the results in descending order based on the “age” column, and limits the result to only one row, which will be the row with the highest age.
SELECT name, age, course
FROM student
ORDER BY age DESC
LIMIT 1;Retrieve distinct values from the “course” column of the “student” table, returning a list of unique course names without any duplicates.
SELECT DISTINCT course
FROM student;Retrieve the “course” column and the count of records for each course from the “student” table, groups the results by course, and filters the results to only include courses that have a count greater than 1 (i.e., courses with more than one student).
SELECT course, COUNT(*)
FROM student
GROUP BY course
HAVING COUNT(*) > 1;Retrieve the “course” column and the average mark (calculated using AVG) as “average_mark” from the “student” table for rows where the age is greater than 20 and the mark is greater than 80, groups the results by course, and sorts the results in descending order based on the average mark.
SELECT course, AVG(mark) AS average_mark
FROM student
WHERE age > 20
AND mark > 80
GROUP BY course
ORDER BY average_mark DESC;
🌏 TCL -> Transaction Control Language
TCL is a category of SQL commands that are used to manage transactions within a database.
COMMANDS
✅ COMMIT: Saves the changes made within a transaction.
✅ ROLLBACK: Discards the changes made within a transaction.
✅ SAVEPOINT: Sets a named point in a transaction to which it can be rolled back.
✅ SET TRANSACTION: Sets characteristics for a transaction, such as isolation level.
🧮 Let’s discuss firstly transaction.
A transaction is a logical unit of work that consists of one or more database operations.
Transactions help ensure data consistency and reliability by guaranteeing that only valid and complete sets of operations are applied to the database. They provide a way to handle complex operations and maintain the integrity of data in multi-user or concurrent environments.
Transaction must follow ACID properties
ACID
Atomicity
A transaction is atomic, meaning it is treated as an indivisible and discrete operation. Either all the operations within a transaction are executed successfully, or none of them are. If any operation within the transaction fails, the entire transaction is rolled back, and the database is restored to its state before the transaction began.
Consistency
A transaction ensures that the database transitions from one consistent state to another consistent state. The database must satisfy a set of predefined rules, constraints, and relationships after the transaction completes successfully.
Isolation
Transactions operate independently of each other. While a transaction is being executed, it should not be affected by or interfere with concurrent transactions. Isolation ensures that the intermediate state of a transaction is not visible to other transactions until it is committed.
Durability
Once a transaction has been successfully completed, its effect will remain in the database even if the database fails. Thus, if a transaction is completed but the database crashes before writing data to disk, the data will be updated when the system returns to service.
-- Start the transaction
BEGIN;
-- Update the mark for a student with id 1 to 90
UPDATE student SET mark = 90 WHERE id = 1;
-- Create a savepoint named "updated_student_mark"
SAVEPOINT updated_student_mark;
-- Delete students with age less than 18
DELETE FROM student WHERE age < 18;
-- Create a savepoint named "deleted_student"
SAVEPOINT deleted_student;
-- Insert a new student record for Alice Smith
INSERT INTO student (name, surname, age, course) VALUES ('Alice', 'Smith', 22, 'English');
-- Update the mark to 85 for students with the course 'Mathematics'
UPDATE student SET mark = 85 WHERE course = 'Mathematics';
-- Create a savepoint named "inserted_and_updated_student"
SAVEPOINT inserted_and_updated_student;
-- Rollback the changes to the "deleted_student" savepoint
ROLLBACK TO deleted_student;
-- Commit the changes to the database
COMMIT; The code
Initiates a transaction block with “BEGIN”
Updates the “mark” column of the student with an “id” of 1 to 90
Creates a savepoint named “updated_student_mark”
Deletes rows from the “student” table where the age is less than 18
Creates a savepoint named “deleted_student”
Inserts a new record for “Alice Smith” with an age of 22 and a course of ‘English’
Updates the “mark” column to 85 for rows with a course of ‘Mathematics’
Creates a savepoint named “inserted_and_updated_student”
Rolls back the changes to the “deleted_student” savepoint
Commits the changes to the database.
🌏 DCL -> Data Control Language
DCL is a category of SQL commands that are used to control access to the database and manage database objects.
COMMANDS
✅ GRANT: Grants specific privileges to a user or role.
✅ REVOKE: Revokes previously granted privileges from a user or role.
Firstly create an user
CREATE USER user1 WITH PASSWORD 'password';If you want to delete the user follow this command
ALTER USER user1 SET ROLE = admin;Grants the SELECT and INSERT privileges on the “student” table to the user “user1”, allowing them to perform SELECT and INSERT operations on the table.
-- GRANT privilege(s) ON object TO user or role;
GRANT SELECT, INSERT ON student TO user1;Revokes the INSERT privilege on the “student” table from the user “user1”, removing their ability to perform INSERT operations on the table.
-- REVOKE privilege(s) ON object FROM user or role;
REVOKE INSERT ON student FROM user1;🎊🎊🎉🎉 Congratulations you finished and learned SQL commands.
Good job 👍
