SQL Tutorial: A Comprehensive Guide

  1. Overview
  2. Sql
  3. SQL Tutorial: A Comprehensive Guide

SQL (Structured Query Language) is a powerful standard language used to interact with relational databases. It enables users to store, manipulate, and retrieve data efficiently. This tutorial covers fundamental and advanced SQL concepts across various database systems, including MySQL, SQL Server, MS Access, Oracle, Postgres, and more.

Basic SQL Operations

1. SELECT

The SELECT statement is used to fetch data from a database.

SELECT column1, column2 FROM table_name;

2. SELECT DISTINCT

Retrieves unique values from a column.

SELECT DISTINCT column_name FROM table_name;

3. WHERE

Filters records based on specified conditions.

SELECT * FROM table_name WHERE condition;

4. ORDER BY

Sorts the result set in ascending (ASC) or descending (DESC) order.

SELECT * FROM table_name ORDER BY column_name ASC;

5. AND, OR, NOT

Logical operators for combining multiple conditions.

SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;
SELECT * FROM table_name WHERE NOT condition;

6. INSERT INTO

Adds new records to a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

7. NULL Values

Checks for NULL values.

SELECT * FROM table_name WHERE column_name IS NULL;

8. UPDATE

Modifies existing records.

UPDATE table_name SET column1 = value1 WHERE condition;

9. DELETE

Removes records.

DELETE FROM table_name WHERE condition;

Advanced SQL Operations

10. SELECT TOP

Fetches a specified number of records.

SELECT TOP 10 * FROM table_name;

11. MIN and MAX

Returns the smallest or largest value.

SELECT MIN(column_name), MAX(column_name) FROM table_name;

12. COUNT, SUM, AVG

Performs aggregate calculations.

SELECT COUNT(column_name), SUM(column_name), AVG(column_name) FROM table_name;

13. LIKE

Searches for a pattern.

SELECT * FROM table_name WHERE column_name LIKE 'A%';

14. Wildcards

Used with LIKE for flexible pattern matching.

  • % matches any number of characters.
  • _ matches a single character.

15. IN

Checks if a value exists in a list.

SELECT * FROM table_name WHERE column_name IN ('value1', 'value2');

16. BETWEEN

Filters records within a range.

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

17. Aliases

Creates temporary names for tables or columns.

SELECT column_name AS alias_name FROM table_name;

Joins

Combines rows from two or more tables.

  • INNER JOIN: Returns records with matching values in both tables.
  • LEFT JOIN: Includes all records from the left table and matched records from the right table.
  • RIGHT JOIN: Opposite of LEFT JOIN.
  • FULL JOIN: Combines results of LEFT JOIN and RIGHT JOIN.
  • SELF JOIN: Joins a table to itself.

Union and Grouping

18. UNION

Combines results from multiple SELECT statements.

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

19. GROUP BY

Groups rows sharing the same value.

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

20. HAVING

Filters groups.

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

Subqueries and CASE

21. EXISTS

Tests for the existence of records.

SELECT * FROM table_name WHERE EXISTS (subquery);

22. CASE Expression

Conditional logic in queries.

SELECT column_name, 
       CASE 
           WHEN condition THEN result1
           ELSE result2
       END
FROM table_name;

Database and Table Management

Database Operations

  • CREATE DATABASE: Creates a new database.
  • DROP DATABASE: Deletes a database.
  • BACKUP DATABASE: Creates a backup.

Table Operations

  • CREATE TABLE: Defines a new table.
  • DROP TABLE: Deletes a table.
  • ALTER TABLE: Modifies table structure.

Constraints

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT

Advanced Features

Indexing and Auto-Increment

  • CREATE INDEX: Improves query performance.
  • AUTO_INCREMENT: Automatically generates unique values.

Stored Procedures

Reusable SQL code blocks.

CREATE PROCEDURE procedure_name AS BEGIN SQL_STATEMENTS END;

Comments

Single-line (--) and multi-line (/* */) comments.

Security and Optimization

  • Prevent SQL Injection using parameterized queries.
  • Use views and indexes to optimize performance.

Exploring Further

  • Dates: Manipulate and format dates.
  • Views: Create virtual tables.
  • Hosting: Manage database hosting.

Mastering SQL involves continuous practice and exploring its vast features. This tutorial serves as a foundational guide to building and managing robust database systems.

Was this article helpful?