Guide to SQL (Structured Query Language)

[Sneakyone]

Guide to SQL (Structured Query Language)

**SQL (Structured Query Language)** is a standard programming language used to manage and manipulate relational databases. It allows users to create, read, update, and delete data within a database (often referred to as CRUD operations). This guide will walk you through the basics of SQL, how to create tables, query data, and perform common database tasks.

Step 1: What is SQL?

SQL is a domain-specific language used in programming and designed for managing data held in relational database management systems (RDBMS). SQL is widely used in database management and offers various functions to retrieve, insert, update, and delete data in a database.

Basic SQL Operations:
- SELECT: Used to retrieve data from a database.
- INSERT: Used to add new data into a table.
- UPDATE: Used to modify existing data.
- DELETE: Used to remove data from a table.

Step 2: SQL Data Types

When creating tables, each column must have a data type that defines the kind of data it will store.

Common SQL Data Types:
1. INT: Stores whole numbers.
2. VARCHAR(n): Stores variable-length strings (up to n characters).
3. TEXT: Stores long text strings.
4. DATE: Stores dates (YYYY-MM-DD format).
5. DECIMAL(p,s): Stores decimal numbers with precision and scale (p = total digits, s = digits after the decimal).
6. BOOLEAN: Stores true or false values.

Example:

Code:

CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATE
);

Step 3: Creating Tables

A table in a relational database is a collection of related data, organized into rows and columns. The following example demonstrates how to create a table.

Syntax for Creating a Table:

Code:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Example: Creating a table for employees.

Code:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Position VARCHAR(50),
    HireDate DATE,
    Salary DECIMAL(10, 2)
);

- ID: The primary key, a unique identifier for each employee.
- Name: Stores the employee's name.
- Position: Stores the employee’s job position.
- HireDate: Stores the date the employee was hired.
- Salary: Stores the employee’s salary.

Step 4: Inserting Data

To add records (data) to the table, we use the `INSERT INTO` statement.

Syntax for Inserting Data:

Code:

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

Example: Adding an employee to the "Employees" table.

Code:

INSERT INTO Employees (ID, Name, Position, HireDate, Salary)
VALUES (1, 'John Doe', 'Manager', '2023-08-01', 65000.00);

This inserts a new employee with an ID of 1, named John Doe, working as a manager, hired on August 1, 2023, with a salary of 65,000.

Step 5: Selecting (Querying) Data

The `SELECT` statement is used to query data from one or more tables. It allows you to retrieve specific columns, rows, or filtered data based on conditions.

Syntax for Querying Data:

Code:

SELECT column1, column2
FROM table_name
WHERE condition;

Example 1: Selecting all columns from the "Employees" table.

Code:

SELECT * FROM Employees;

Example 2: Selecting only the "Name" and "Salary" of employees earning more than $50,000.

Code:

SELECT Name, Salary
FROM Employees
WHERE Salary > 50000;

Step 6: Updating Data

To modify existing data in the database, use the `UPDATE` statement.

Syntax for Updating Data:

Code:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example: Updating the salary of an employee with ID 1.

Code:

UPDATE Employees
SET Salary = 70000
WHERE ID = 1;

This increases John Doe’s salary to 70,000.

Step 7: Deleting Data

To remove records from the table, use the `DELETE` statement.

Syntax for Deleting Data:

Code:

DELETE FROM table_name
WHERE condition;

Example: Deleting an employee with ID 1.

Code:

DELETE FROM Employees
WHERE ID = 1;

This deletes John Doe from the "Employees" table.

Step 8: Filtering Data with WHERE Clause

The `WHERE` clause allows you to filter records based on specific conditions.

Example 1: Selecting employees hired after January 1, 2020.

Code:

SELECT * FROM Employees
WHERE HireDate > '2020-01-01';

Example 2: Selecting employees with the position of "Manager."

Code:

SELECT * FROM Employees
WHERE Position = 'Manager';

Step 9: Using Aggregate Functions

SQL provides aggregate functions to perform calculations on data, such as summing values or counting records.

Common Aggregate Functions:
1. COUNT(): Returns the number of rows.
2. SUM(): Returns the sum of a numeric column.
3. AVG(): Returns the average value.
4. MIN(): Returns the smallest value.
5. MAX(): Returns the largest value.

Example 1: Counting the number of employees.

Code:

SELECT COUNT(*)
FROM Employees;

Example 2: Calculating the total salary for all employees.

Code:

SELECT SUM(Salary)
FROM Employees;

Step 10: Joining Tables

In relational databases, data is often spread across multiple tables. The `JOIN` clause is used to combine data from two or more tables based on a related column.

Types of Joins:
1. INNER JOIN: Returns records with matching values in both tables.
2. LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table.
3. RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table.
4. FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either table.

Example of INNER JOIN: Joining two tables: "Employees" and "Departments."

Code:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Step 11: Creating Indexes

Indexes improve the performance of queries by allowing the database to quickly locate rows in a table. An index is created on columns used frequently in queries.

Syntax for Creating an Index:

Code:

CREATE INDEX index_name
ON table_name (column1, column2);

Example: Creating an index on the "Name" column in the "Employees" table.

Code:

CREATE INDEX idx_name
ON Employees (Name);

Step 12: Dealing with NULL Values

A `NULL` value represents missing or unknown data in a table. You can handle `NULL` values in queries using the `IS NULL` or `IS NOT NULL` operators.

Example 1: Selecting employees with missing hire dates.

Code:

SELECT * FROM Employees
WHERE HireDate IS NULL;

Example 2: Selecting employees with known hire dates.

Code:

SELECT * FROM Employees
WHERE HireDate IS NOT NULL;

Conclusion

This guide introduces you to the basics of SQL, including how to create tables, insert data, query databases, and perform essential database operations like updating and deleting records. SQL is a powerful tool for managing and manipulating relational databases, and mastering it will help you efficiently handle data-driven tasks. As you progress, explore more advanced SQL concepts like subqueries, views, triggers, and stored procedures to deepen your knowledge.