Introduction to SQL

SQL (Structured Query Language) is used to manage and manipulate relational databases. It allows you to create, update, retrieve, and delete data in databases like MySQL, PostgreSQL, SQL Server, and Oracle.

SQL Tutorial for Beginners

SQL Data Types

Each column in a SQL table must have a data type. Common types include:

  • INT, BIGINT – Integers
  • VARCHAR(n) – Variable-length strings
  • TEXT – Long text
  • DATE, TIME, DATETIME – Date and time values
  • BOOLEAN – True/false values

DDL (Data Definition Language)

DDL commands used to define, modify, and manage database structures such as tables, schemas, and indexes. DDL changes are auto-committed, can’t roll them back in most databases.

CREATE – It creates a new database object (table, view, index, etc.)

CREATE TABLE employees ( 
    id INT PRIMARY KEY, 
    name VARCHAR(100), 
    salary DECIMAL(10, 2) 
);

ALTER – It modifies an existing database object.

ALTER TABLE employees 
ADD department VARCHAR(50);

DROP – It deletes an existing database object permanently.

DROP TABLE employees;

TRUNCATE – It removes all records from a table, but keeps the table structure.

TRUNCATE TABLE employees;

RENAME – It renames a database object.

RENAME TABLE employees TO Staff;

DML (Data Manipulation Language)

DML commands are used to insert, update, delete, and retrieve data from database tables.

INSERT – It adds new records into a table.

INSERT INTO employees 
(id, name, salary) 
VALUES (1, 'john doe', 50000);

UPDATE – It modifies existing records in a table.

UPDATE employees SET 
salary = 55000 WHERE id = 1;

DELETE – It removes records from a table.

DELETE FROM employees WHERE id = 1;

SELECT – It retrieves data from one or more tables.

SELECT * FROM employees;

DQL (Data Query Language)

It is mainly used to fetch data from a database. SELECT is a key DQL command which is used to retrieves data from one or more tables.
While SELECT is technically part of DML in some databases, it is categorized separately as DQL because it only queries data without changing it.

We can use SELECT with different conditions, sorting, grouping, and joins.

SELECT name, salary FROM employees;
SELECT * FROM employees 
WHERE salary > 50000;
SELECT * FROM employees 
ORDER BY name ASC;
SELECT department, COUNT(*) 
FROM employees GROUP BY department;

DCL (Data Control Language)

It is used to provide control access to data in the database, like – permissions and security.
DCL is used by DBAs (Database Administrators) to manage who can read or modify data.

GRANT – This statement is used to give permissions to users or roles on database objects like tables, views, procedures, or even the entire database.

REVOKE – This statement removes the access permissions from the users.

TCL (Transaction Control Language)

TCL commands are used to manage transactions in a database to ensure data integrity and consistency.
These commands are work only with DML operations (like INSERT, UPDATE, DELETE).

NOTE :- DDL commands (like CREATE, DROP, ALTER) in most database, the system automatically commits any pending changes, means they are saved to the database and can’t be undone by rolling back the transaction.

TCL Commands :

COMMIT – It changes all changes made during the current transaction.

COMMIT;

ROLLBACK – It undoes changes made in the current transaction.

ROLLBACK;

SAVEPOINT – It sets a save point within a transaction to which you can roll back later.

SAVEPOINT sp1;

RELEASE SAVEPOINT – It removes a save point.

RELEASE SAVEPOINT sp1;

SQL Constraints

Constraints in SQL are the rules applied to columns in a table to enforce data integrity and accuracy.

NOT NULL – It ensures that a column cannot have NULL values.

CREATE TABLE students (
id INT NOT NULL,
name VARCHAR(100) NOT NULL
);

UNIQUE – It ensures all values in a column are different.

CREATE TABLE user (
email VARCHAR(100) UNIQUE
);

PRIMARY KEY – It combines NOT NULL and UNIQUE. Uniquely identifies each row.

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);

FOREIGN KEY – It ensures values in a column match values in another table’s column.

CREATE TABLE orders ( 
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id)
REFERENCES customers(id)
);

CHECK – It ensures that all values meet a specific condition.

CREATE TABLE products (
price DECIMAL(10, 2),
CHECK (price > 0)
);

DEFAULT – It assigns a default value if none is provided during insert.

CREATE TABLE accounts (
status VARCHAR(20)DEFAULT
'active'
);

Joins

JOINS in SQL are used to combine rows from two or more tables based on a related column between them, usually a foreign key.

INNER JOIN – It returns rows with matching values in both tables.

SELECT customers.name, 
orders.product FROM customers 
INNER JOIN orders ON 
customer.id = orders.cutomer_id;

LEFT JOIN – It is also called LEFT OUTER JOIN. It returns all rows from the left table and matching rows from the right table.

SELECT customer.name, 
orders.product FROM customers 
LEFT JOIN orders ON
customer.id = orders.customer_id;

RIGHT JOIN – It is also called RIGHT OUTER JOIN. It returns all rows from the right table and matching rows from the left table.

SELECT customer.name, 
orders.product FROM customers 
RIGHT JOIN orders ON 
customer.id = orders.customer_id;

FULL JOIN – It is also called FULL OUTER JOIN. It returns all rows when there is a match in one of the tables.

SELECT customer.name, 
orders.product FROM customers 
FULL OUTER JOIN orders ON 
customers.id = orders.customer_id;

CROSS JOIN – It returns the cartesian product of both tables, which simply means every combination.

SELECT customers.name, 
orders.product FROM customers 
CROSS JOIN orders;

Subqueries

SQL Subqueries are queries nested inside another query. They are used to perform operations that depend on the result of another query.

Types of SQL Subqueries:-

1. Single-row Subquery: It will return only one row.

SELECT name FROM employees 
WHERE salary = (SELECT MAX(salary)
 FROM employees
);

2. Multiple-row Subquery: It will return multiple rows.

SELECT name FROM employees 
WHERE department_id IN (
SELECT department_id FROM departments 
WHERE location = 'New York'
);

3. Multiple-column Subquery: It will returns multiple columns.

SELECT name FROM employees 
WHERE (department_id, job_id) 
IN (SELECT department_id, job_id 
FROM job_history
);

4. Correlated Subquery: It depends on the outer query. It runs once for each row selected by the outer query.

SELECT name FROM employees e 
WHERE salary > (SELECT AVG(salary) 
FROM employees WHERE 
department_id = e.department_id
);

5. Nested Subqueries: It is a subquery inside another subquery.

SELECT name FROM employees 
WHERE department_id = (
SELECT department_id FROM departments 
WHERE location_id = (
SELECT location_id FROM locations 
WHERE city = 'Boston')
);

6. Subquery in FROM clause (Inline View):

SELECT dept_name, avg_salary FROM
(SELECT department_id, AVG(salary) AS avg_salary 
FROM employees GROUP BY department_id) AS dept_avg 
JOIN departments ON 
dept_avg.department_id = departments.department_id;

7. Subquery in SELECT clause

SELECT name,(SELECT department_name 
FROM departments d 
WHERE d.department_id = e.department_id) 
AS dept FROM employees e;

Will update soon….

Functions

Views

Indexes

SQL Keys

Normalization

Stored Procedure

Trigger

Union vs Union All

Group By and Having Clause

Window Functions

Scroll to Top