Introduction to SQL
SQL (Structured Query Language) is a standard language for accessing and manipulating databases. It is widely used in database management systems (DBMS) such as MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. SQL allows users to create, read, update, and delete data within a database, among other functionalities.
Basic Concepts
Databases and Tables
- Database: A database is a structured collection of data stored electronically. It is organized in such a way that it can be easily accessed, managed, and updated.
- Table: A table is a collection of related data entries and it consists of columns and rows.
Example:
A table named Employees:
| EmployeeID | FirstName | LastName | Age | Department |
|---|---|---|---|---|
| 1 | John | Doe | 30 | HR |
| 2 | Jane | Smith | 25 | IT |
| 3 | Sam | Brown | 35 | Finance |
SQL Syntax
SQL syntax is used to write queries. Here are some basic SQL commands:
SELECT
The SELECT statement is used to select data from a database. The data returned is stored in a result table, sometimes called the result set.
SELECT column1, column2, ...
FROM table_name;
SELECT FirstName, LastName
FROM Employees;
| FirstName | LastName |
|---|---|
| John | Doe |
| Jane | Smith |
| Sam | Brown |
WHERE
The WHERE clause is used to filter records.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SELECT FirstName, LastName
FROM Employees
WHERE Age > 30;
| FirstName | LastName |
|---|---|
| Sam | Brown |
INSERT INTO
The INSERT INTO statement is used to insert new records into a table.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
INSERT INTO Employees (FirstName, LastName, Age, Department)
VALUES ('Alice', 'Johnson', 28, 'IT');
UPDATE
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Employees
SET Age = 29
WHERE FirstName = 'Alice';
DELETE
The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name
WHERE condition;
DELETE FROM Employees
WHERE FirstName = 'Alice';
Advanced Concepts
Joins
JOIN clauses are used to combine rows from two or more tables, based on a related column between them.
Types of Joins:
- 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 matched records from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table.
- FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table.
Example:
Tables: Employees:
| EmployeeID | FirstName | LastName | Age | DepartmentID |
|---|---|---|---|---|
| 1 | John | Doe | 30 | 1 |
| 2 | Jane | Smith | 25 | 2 |
| 3 | Sam | Brown | 35 | 3 |
Departments:
| DepartmentID | DepartmentName |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Finance |
INNER JOIN Example:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
| FirstName | LastName | DepartmentName |
|---|---|---|
| John | Doe | HR |
| Jane | Smith | IT |
| Sam | Brown | Finance |
Indexes
An index is used to speed up the performance of queries. It is a data structure that provides quick lookup of data in a column or columns.
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX idx_lastname
ON Employees (LastName);
Views
A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table.
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
CREATE VIEW EmployeeDetails AS
SELECT FirstName, LastName, DepartmentID
FROM Employees;
Transactions
Transactions are a sequence of SQL statements that are treated as a single unit. The transaction ensures that either all operations are completed successfully, or none of them are.
Syntax:
- BEGIN TRANSACTION: Starts a new transaction.
- COMMIT: Saves the work done in the transaction.
- ROLLBACK: Undoes the work done in the current transaction.
BEGIN TRANSACTION;
UPDATE Employees SET Age = 31 WHERE EmployeeID = 1;
DELETE FROM Employees WHERE EmployeeID = 3;
COMMIT;
Stored Procedures
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
CREATE PROCEDURE GetEmployees
AS
SELECT FirstName, LastName, Age
FROM Employees;
GO;
Triggers
A trigger is a special kind of stored procedure that automatically runs when an event occurs in the database server.
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT | AFTER UPDATE | AFTER DELETE
AS
sql_statement;
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'Row inserted in Employees table';
END;
SQL is a powerful tool for managing and manipulating databases. From basic operations like selecting and filtering data to advanced concepts like joins, indexes, views, transactions, stored procedures, and triggers, SQL provides a comprehensive set of tools for database management. Understanding SQL is essential for anyone working with databases, as it is the foundational language for querying and updating data efficiently and effectively. Happy coding !❤️