SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to query, insert, update, and delete data, as well as manage database structures such as tables, indexes, and relationships.
Here is a basic SQL tutorial covering key concepts and commands:
1. Basic SQL Syntax
SQL statements are usually written in uppercase, but SQL is not case-sensitive. The basic syntax for SQL commands follows this structure:
COMMAND KEYWORD column_name FROM table_name WHERE condition;
2. Database Operations
2.1 SELECT: Retrieve Data
The SELECT statement is used to retrieve data from a database table.
SELECT column1, column2, ... FROM table_name;
Example:
SELECT name, age FROM employees;
To select all columns, you can use the wildcard *:
SELECT * FROM employees;
2.2 WHERE: Filter Data
You can filter records with the WHERE clause.
SELECT column1, column2 FROM table_name WHERE condition;
Example:
SELECT name, age FROM employees WHERE age > 30;
2.3 INSERT: Insert Data
The INSERT INTO statement is used to add new rows to a table.
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO employees (name, age, department) VALUES ('John Doe', 35, 'HR');
2.4 UPDATE: Modify Data
The UPDATE statement is used to modify existing records.
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Example:
UPDATE employees SET age = 36 WHERE name = 'John Doe';
2.5 DELETE: Remove Data
The DELETE statement is used to remove records from a table.
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM employees WHERE age < 25;
3. Advanced SQL Operations
3.1 JOIN: Combine Data from Multiple Tables
A JOIN operation is used to combine rows from two or more tables based on a related column between them.
SELECT columns FROM table1 JOIN table2 ON table1.column_name = table2.column_name;
Types of joins:
- INNER JOIN: Returns only matching rows.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
- FULL JOIN: Returns all rows when there is a match in either table.
Example:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
3.2 GROUP BY: Aggregate Data
The GROUP BY clause groups rows that have the same values into summary rows (like sum, count, average).
SELECT column, COUNT(*) FROM table_name GROUP BY column;
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;
3.3 HAVING: Filter Grouped Data
HAVING is used to filter the results after applying the GROUP BY clause.
SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 5;
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
3.4 ORDER BY: Sort Data
The ORDER BY clause is used to sort the result set by one or more columns.
SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;
Example:
SELECT name, age FROM employees ORDER BY age DESC;
3.5 LIMIT: Restrict the Number of Rows
The LIMIT clause is used to specify the number of records to return.
SELECT * FROM table_name LIMIT number;
Example:
SELECT * FROM employees LIMIT 5;
4. SQL Functions
4.1 Aggregate Functions
SQL provides several aggregate functions for summarizing data:
COUNT(): Returns the number of rows.SUM(): Returns the total sum.AVG(): Returns the average.MIN(): Returns the minimum value.MAX(): Returns the maximum value.
Example:
SELECT AVG(age) FROM employees;
4.2 String Functions
SQL also provides string functions:
CONCAT(): Concatenates two or more strings.UPPER(),LOWER(): Converts text to uppercase or lowercase.LENGTH(): Returns the length of a string.
Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
4.3 Date Functions
SQL offers date functions to manipulate and query date/time values:
CURDATE(): Returns the current date.NOW(): Returns the current date and time.DATE_ADD(),DATE_SUB(): Adds or subtracts days to a date.
Example:
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;
5. SQL Constraints
Constraints are used to ensure data integrity in the database. Common constraints include:
- PRIMARY KEY: Uniquely identifies each row.
- FOREIGN KEY: Ensures referential integrity between two tables.
- UNIQUE: Ensures all values in a column are unique.
- NOT NULL: Ensures a column cannot have a NULL value.
- CHECK: Ensures that values meet a certain condition.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18)
);
Conclusion
SQL is a powerful language for interacting with relational databases. This tutorial covered the basics, including how to retrieve, insert, update, and delete data, as well as advanced operations such as joins, grouping, and sorting.
With practice, you will become proficient at writing more complex queries and managing your databases efficiently.