What is 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’.