Contents
What is SQL?
Structured Query Language
SQL, or Structured Query Language, is a standardized domain-specific language used for managing and manipulating relational databases. It is the most widely used language for interacting with databases due to its simplicity and powerful capabilities. SQL is designed to handle structured data, which is data that can be organized into tables consisting of rows and columns.
Key Features and Capabilities of SQL:
Data Querying
Definition: SQL allows users to retrieve data from one or more tables using the SELECT
statement. This can involve simple retrievals or complex queries involving multiple conditions, joins, and subqueries.
Example: Retrieve all records from a table named employees
.
SELECT * FROM employees;
Retrieves all columns and rows from the employees
table.
Example: Retrieve names and salaries of employees where salary is greater than 50000.
SELECT name, salary FROM employees WHERE salary > 50000;
Selects name
and salary
columns for employees whose salary is greater than 50000.
Data Manipulation
Definition: SQL provides commands for inserting (INSERT
), updating (UPDATE
), and deleting (DELETE
) data within the database. These operations are essential for maintaining and modifying the stored data.
Example: Insert a new record into the employees
table.
INSERT INTO employees (name, age, department_id, salary)
VALUES ('John Doe', 30, 2, 60000);
Inserts a new employee record with specified name
, age
, department_id
, and salary
values.
Example: Update the salary of an employee.
UPDATE employees
SET salary = 65000
WHERE name = 'John Doe';
Updates the salary of the employee named ‘John Doe’ to 65000.
Example: Delete an employee record.
DELETE FROM employees
WHERE name = 'John Doe';
Deletes the record of the employee named ‘John Doe’.
Data Definition
Definition: SQL includes commands for defining the structure of the database, such as creating (CREATE
), altering (ALTER
), and dropping (DROP
) tables and other database objects like indexes and views.
Example: Create a new table named employees
.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
department_id INT,
salary DECIMAL(10, 2)
);
Creates a new employees
table with specified columns and data types.
Example: Add a new column to the employees
table.
ALTER TABLE employees
ADD COLUMN hire_date DATE;
Adds a new column hire_date
of type DATE
to the employees
table.
Example: Drop the employees
table.
DROP TABLE employees;
Deletes the employees
table from the database.
Data Control:
Definition: SQL supports commands for controlling access to data within the database. This includes granting and revoking permissions using GRANT
and REVOKE
statements, ensuring that only authorized users can perform certain actions.
Example: Grant SELECT permission on the employees
table to user john
.
GRANT SELECT ON employees TO john;
Grants the user john
permission to perform SELECT operations on the employees
table.
Example: Revoke INSERT permission on the employees
table from user john
.
REVOKE INSERT ON employees FROM john;
Revokes the user john
‘s permission to perform INSERT operations on the employees
table.
Transactions Management
Definition: SQL enables users to manage transactions, which are sequences of operations performed as a single logical unit of work. Commands like BEGIN
, COMMIT
, and ROLLBACK
are used to ensure data integrity and consistency.
Example: Execute a transaction to transfer funds between accounts.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;
Begins a transaction, updates balances of two accounts, and commits the changes.
Example: Rollback a transaction if an error occurs.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
-- Simulate an error
ROLLBACK;
Begins a transaction, attempts to update an account, and rolls back if an error occurs.
Built-in Functions
Definition: SQL provides a wide range of built-in functions for performing calculations, manipulating strings, handling dates and times, and more. These functions enhance the ability to process and analyze data directly within the database.
Example: Calculate the average salary of employees.
SELECT AVG(salary) AS average_salary FROM employees;
Calculates the average salary of all employees and labels the result as average_salary
.
Example: Concatenate first and last names of employees.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
Concatenates first_name
and last_name
with a space in between and labels the result as full_name
.
Example: Extract the year from a date.
SELECT YEAR(hire_date) AS hire_year FROM employees;
Extracts the year from the hire_date
and labels the result as hire_year
.
Joins and Subqueries
Definition: SQL supports various types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN) and subqueries (queries within queries), allowing complex data relationships and retrievals to be expressed efficiently.
Example: Retrieve employee names and their department names using an INNER JOIN.
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
Uses an INNER JOIN to retrieve employee names and their corresponding department names.
Example:
Retrieve names of employees who work in departments located in ‘New York’ using a subquery.
SELECT name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'New York'
);
Uses a subquery to select names of employees whose departments are located in ‘New York’.
Conclusion:
SQL is a crucial tool for anyone working with relational databases. Its ability to efficiently query, manipulate, and control data makes it an essential skill for database administrators, developers, and data analysts. The language’s standardized nature ensures that SQL skills are transferable across different database management systems, including MySQL, PostgreSQL, SQL Server, and Oracle.