Premium
Log in
SQL (Structured Query Language) is the standard language for interacting with relational databases. It is used to define, manage, and retrieve data from databases. With SQL, you can perform the following operations:
SQL commands are divided into three main categories:
DDL (Data Definition Language):
DML (Data Manipulation Language):
DCL (Data Control Language):
CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100), age INT );
CREATE TABLE enrollments ( enrollment_id INT PRIMARY KEY, student_id INT, course_id INT, FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) );
Normalization is the process of organizing the structure of a database to reduce data redundancy and ensure data integrity.
Before normalization:
After normalization:
Students table
Courses table
Enrollments table
JOIN is an operation in SQL that allows combining rows from two or more tables based on related columns between them.
INNER JOIN:
Returns records that have matching values in both tables..
LEFT JOIN (or LEFT OUTER JOIN):
Returns all records from the left table and the matching records from the right table. If there is no match, it returns NULL for the right table.
RIGHT JOIN (or RIGHT OUTER JOIN):
Returns all records from the right table and the matching records from the left table. If there is no match, it returns NULL for the left table.
FULL OUTER JOIN:
Returns all records where there is a match in one of the tables.
CROSS JOIN:
Performs a Cartesian product of two tables, combining each row of the first table with each row of the second table.
A subquery is an SQL query nested inside another query. It is used to perform operations whose results are needed for the main query.
SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'IT');
SELECT sub.department, COUNT(*) FROM ( SELECT department_id AS department FROM employees ) sub GROUP BY sub.department;
Use the DISTINCT keyword in the SELECT statement to return only unique records.
Пример:
SELECT DISTINCT position FROM employees;
Example:
SELECT department_id, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department_id;
Example:
SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
An Index is a special data structure that improves the speed of data retrieval operations from a table by creating pointers to the data.
Indexes speed up read operations but can slow down write operations (inserts, updates, deletes) since the indexes need to be updated when the data changes.
-- Creating an index on the name column in the employees table CREATE INDEX idx_employees_name ON employees(name);
Example:
DELETE FROM employees WHERE salary < 30000;
Example:
TRUNCATE TABLE employees;
A Transaction is a sequence of operations performed as a single logical unit, which must be fully completed or fully rolled back.
Atomicity:
Consistency:
Isolation:
Durability:
A Trigger r is a stored procedure that is automatically executed when a specific event occurs in the database, such as an INSERT, UPDATE, or DELETE on a particular table.
CREATE TRIGGER trg_after_insert_employee AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log (employee_id, action, action_time) VALUES (NEW.id, 'INSERT', NOW()); END;
A VIEW is a virtual table based on the result of an SQL query. A view does not store data itself but provides a specific way to view data from one or more tables.
CREATE VIEW employee_details AS SELECT e.id, e.name, d.name AS department, e.salary FROM employees e JOIN departments d ON e.department_id = d.id;
SELECT * FROM employee_details WHERE salary > 50000;
The LIKE operator is used in WHERE clauses to search for rows that match a specific pattern. Wildcards are used in the patterns:
SELECT * FROM employees WHERE name LIKE 'А%';
SELECT * FROM employees WHERE name LIKE '%e';
SELECT * FROM employees WHERE name LIKE '_а%';
Aggregate functions perform calculations on a set of values and return a single value. They are often used in combination with the GROUP BY clause.
SELECT COUNT(*) FROM employees;
SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id;
SELECT MAX(salary) FROM employees;
SELECT SUM(amount) FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';
Syntax
SELECT column_list FROM table1 UNION SELECT column_list FROM table2;
Syntax
SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2;
Example of a stored procedure:
CREATE PROCEDURE GetEmployeeByID(IN emp_id INT) BEGIN SELECT * FROM employees WHERE id = emp_id; END;
Calling the procedure:
CALL GetEmployeeByID(1);
Example of a function:
CREATE FUNCTION GetEmployeeSalary(emp_id INT) RETURNS DECIMAL(10,2) BEGIN DECLARE salary DECIMAL(10,2); SELECT e.salary INTO salary FROM employees e WHERE e.id = emp_id; RETURN salary; END;
Using the function:
SELECT name, GetEmployeeSalary(id) FROM employees;
Use Indexes:
Avoid SELECT *:
Optimize JOIN and WHERE Conditions:
Use Result Limits (LIMIT):
Avoid Subqueries Where Joins Are Possible::
Cache frequently used data:
Profiling and Query Analysis:
Constraints ensure data integrity and reliability in a table by defining rules for the data in columns.
Types of constraints:
NOT NULL:
Example:
CREATE TABLE products ( product_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL );
UNIQUE:
Example:
CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );
PRIMARY KEY:
FOREIGN KEY:
Example:
CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(user_id) );
CHECK:
Example:
CREATE TABLE employees ( id INT PRIMARY KEY, age INT CHECK (age >= 18) );
DEFAULT:
CREATE TABLE tasks ( task_id INT PRIMARY KEY, status VARCHAR(20) DEFAULT 'Pending' );
SQL Injection is a method of attacking a database where an attacker inserts malicious SQL code through input fields, allowing unauthorized SQL queries to be executed.
1. Parameterized queries (Prepared Statements):
Example (in Java using JDBC):
String sql = "SELECT * FROM users WHERE username = ? AND password = ?"; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); ResultSet rs = stmt.executeQuery();
2. Using ORM (Object-Relational Mapping):
3. Input Validation and Filtering:
4. Restricting access rights:
5. Using stored procedures:
A relational database is a database based on the relational model of data. In such a database, data is stored in tables, and relationships between data are defined using keys.
Example:
SELECT * FROM employees e INNER JOIN departments d ON e.department_id = d.id;
LEFT OUTER JOIN (LEFT JOIN):
Example:
SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.id;
RIGHT OUTER JOIN (RIGHT JOIN):
Example:
SELECT * FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;
FULL OUTER JOIN (FULL JOIN):
SELECT * FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.id;
NULL is a special value in SQL that represents the absence of data or an unknown value.
To check for NULL, use the IS NULL or IS NOT NULL operator.
-- Finding records with an unknown birth date SELECT * FROM employees WHERE birth_date IS NULL; -- Finding records with a known birth date SELECT * FROM employees WHERE birth_date IS NOT NULL;
COALESCE
Returns the first non-NULL value from the list.
COALESCE(val1[, val2, ...., val_n])
ISNULL
Returns 1 or 0 depending on whether the expression is NULL.
ISNULL(value)
IFNULL
Returns the first argument if it is not NULL. Otherwise, it returns the second argument.
IFNULL(value, alternative_value)
The CASE operator is used to implement conditional logic in SQL queries. It allows you to return values based on conditions, similar to the IF-ELSE statement.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END
SELECT name, salary, CASE WHEN salary >= 80000 THEN 'High' WHEN salary >= 50000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees;
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT;
BEGIN TRANSACTION; DELETE FROM orders WHERE order_date < '2022-01-01'; -- If you change your mind ROLLBACK;
Memory and performance:
Usage:
Example:
CREATE TABLE products ( code CHAR(10), -- Fixed-length product code name VARCHAR(100) -- Variable-length product name );
Inserting data:
INSERT INTO products (code, name) VALUES ('A123', 'Lenovo Laptop');
In the code column, the value will be padded with spaces up to 10 characters.
A temporary table is a table that exists only for the duration of the current session or connection and is automatically deleted when the session ends or the connection is closed.
CREATE TEMPORARY TABLE TempTable ( id INT, name VARCHAR(100) );
-- inserting data into the temporary table INSERT INTO #TempTable (id, name) VALUES (1, 'Иван'), (2, 'Петр'); -- selecting data from the temporary table SELECT * FROM #TempTable; -- the temporary table will be automatically deleted after the session ends
Window functions are functions that perform calculations over a set of rows (a window) related to the current row and return a result for each row without grouping the data.
A detailed explanation of how window functions work can be found in our course.
CTE (common table expression) is a temporary named result set defined in an SQL query using the WITH keyword.
It enhances the readability and structure of complex queries.
WITH CTEName (column1, column2, ...) AS ( -- your query SELECT ... ) SELECT * FROM CTEName;
A detailed explanation of CTEs can be found in our course.
A FOREIGN KEY is a constraint that establishes a relationship between a column or set of columns in one table and a column or columns in another table (typically a primary key).
It ensures referential integrity by guaranteeing that values in the foreign key column correspond to existing values in the related table.
Prevents insertion of invalid data It is impossible to insert a value into the foreign key column if that value does not exist in the related table.
Prevents deletion of related records It is impossible to delete a record from the parent table if records in the child table reference it, unless additional actions are taken.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100), DepartmentID INT, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) );
The ALTER TABLE command with the ADD operator is used to add a new column to an existing table.
ALTER TABLE table_name ADD column_name data_type [constraints];
Suppose we have a table employees, and we want to add a column email of type VARCHAR(255).
ALTER TABLE employees ADD email VARCHAR(255);
Adding a column with a NOT NULL constraint and a default value:
ALTER TABLE employees ADD date_of_birth DATE NOT NULL DEFAULT '1900-01-01';
Note
When adding a column with a NOT NULL constraint,
if there are already existing rows in the table, you must specify a default value; otherwise, an error will occur.
A correlated subquery is a subquery that depends on the outer query. It is executed for each row of the outer query, using values from that row.
Suppose there are tables employees and departments.
SELECT e.name, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id );
More information about correlated subqueries can be found in our course.
DELETE FROM table_name [WHERE condition];
TRUNCATE TABLE table_name;
DROP TABLE table_name;
A Self-Join is a type of join in SQL where a table is joined with itself. It is useful when you need to compare rows within the same table or handle hierarchical data.
Consider the following employee data:
To get a list of employees and their managers:
SELECT e.name AS Employee, m.name AS Manager FROM Employee e LEFT JOIN Employee m ON e.managerId = m.employeeId;
In this query, we join the Employee table with itself to match each employee with their manager.
Backing up and restoring a database are critical operations to ensure data safety and the ability to recover it in case of failure or loss.
The method of backup depends on the database management system (DBMS) you are using.
Below are examples for some popular DBMS.
Backup using the mysqldump utility:
mysqldump -u username -p mydatabase > backup.sql
Backup using the pg_dump utility:
pg_dump -U username mydatabase > backup.sql
Restoring a database from the backup.sql file:
mysql -u username -p mydatabase < backup.sql
Restoring a database using the psql utility:
psql -U username mydatabase < backup.sql
A many-to-many relationship in relational databases occurs when one record in the first table can be associated with multiple records in the second table, and vice versa.
In SQL, such relationships are implemented using a junction table (also known as a linking or associative table) that connects the two main tables via foreign keys.
Consider a scenario with Student and Course tables where one student can enroll in multiple courses, and one course can be taken by multiple students.
CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(100) );
CREATE TABLE Course ( CourseID INT PRIMARY KEY, Title VARCHAR(100) );
CREATE TABLE StudentCourse ( StudentID INT, CourseID INT, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID) );
As a result:
The GRANT and REVOKE commands in SQL are used to manage user access rights to database objects.
They allow granting or revoking specific privileges to or from users or roles, ensuring security and control over who can perform what actions in the database.
The GRANT command provides users or roles with specific privileges on database objects.
GRANT privileges ON object TO user [WITH GRANT OPTION];
Example:
To grant user user1 the privilege to select data from the employees table:
GRANT SELECT ON employees TO user1;
The REVOKE command removes previously granted privileges from users or roles.
REVOKE privileges ON object FROM user;
Example:
To revoke the SELECT privilege from user user1 on the employees
REVOKE SELECT ON employees FROM user1;
Hash functions in SQL are used to transform input data of arbitrary length into a fixed-length string. This transformation is called hashing, and it is widely used to ensure security, data integrity, and optimize comparison and search operations.
Hash functions allow detecting whether data has been altered. By creating a hash of the original data and comparing it with the current hash, changes can be identified.
Hash values can be used for quick comparison of large amounts of data or to create indexes for faster searches.
Hashing helps generate unique identifiers for records based on their content.
-- Create a users table CREATE TABLE Users ( UserID INT PRIMARY KEY, Username VARCHAR(50) NOT NULL, PasswordHash VARBINARY(512) NOT NULL ); -- When adding a new user, hash the password INSERT INTO Users (UserID, Username, PasswordHash) VALUES (1, 'user1', HASHBYTES('SHA2_512', 'password123')); -- Verify that the entered password matches the hash in the database SELECT UserID FROM Users WHERE Username = 'user1' AND PasswordHash = HASHBYTES('SHA2_512', 'password123');
Store hashes in binary format (VARBINARY) instead of string format (VARCHAR) to preserve accuracy and save space.
Remember that hashing is a one-way function; the original data cannot be recovered from the hash.
An SQL trigger is a database object representing a special type of stored procedure that automatically executes when a specific event occurs in the database. These events may include INSERT, UPDATE, or DELETE operations on tables or views.
A trigger is automatically activated in response to a specified event (e.g., adding a new record to a table).
Within a trigger, you can access old and new data values through special pseudo-tables: OLD and NEW.
Suppose we have an Employees table, and we want to automatically save a history of salary changes in the SalaryHistory table whenever employee data is updated.
Creating tables:
-- Employees table CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100), Salary DECIMAL(10, 2) ); -- Salary history table CREATE TABLE SalaryHistory ( EmployeeID INT, OldSalary DECIMAL(10, 2), NewSalary DECIMAL(10, 2), ChangeDate DATETIME DEFAULT CURRENT_TIMESTAMP );
Creating a trigger::
DELIMITER $$ CREATE TRIGGER trg_AfterSalaryUpdate AFTER UPDATE ON Employees FOR EACH ROW BEGIN IF OLD.Salary <> NEW.Salary THEN INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary) VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary); END IF; END$$ DELIMITER ;