SQL Tutorial: Introduction and Basics

 

SQL Tutorial: Introduction and Basics

SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases. SQL allows users to perform a wide range of operations such as querying, updating, inserting, and deleting data.

Key SQL Concepts

1. Database: A collection of data organized in tables.
2. Table: A structure within a database that holds data in rows and columns.
3. Row: A record in the table.
4. Column: A field in the table that holds a specific type of data.

SQL Basic Operations

The main SQL operations can be categorized into CRUD operations:

• Create (Insert data)
• Read (Query data)
• Update (Modify data)
• Delete (Remove data)

1. Creating a Database and Table

-- Create a new database
CREATE DATABASE MyDatabase;

-- Use the created database
USE MyDatabase;

-- Create a new table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Position VARCHAR(50)
);

2. Inserting Data

You can insert records into a table using the INSERT INTO statement.

-- Insert a single row into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Position)
VALUES (1, 'John', 'Doe', 30, 'Software Developer');

-- Insert multiple rows at once
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Position)
VALUES 
    (2, 'Jane', 'Smith', 28, 'Project Manager'),
    (3, 'Mike', 'Johnson', 35, 'Data Analyst');

3. Querying Data

To retrieve data from a table, you use the SELECT statement. The SELECT statement allows you to specify which columns you want to retrieve and from which table.

-- Retrieve all data from the Employees table
SELECT * FROM Employees;

-- Retrieve specific columns (FirstName and Position)
SELECT FirstName, Position FROM Employees;

-- Filter results using a WHERE clause
SELECT * FROM Employees WHERE Age > 30;

4. Updating Data

To modify existing records in a table, use the UPDATE statement.

-- Update a specific record
UPDATE Employees 
SET Position = 'Senior Developer' 
WHERE EmployeeID = 1;

5. Deleting Data

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

-- Delete a specific record
DELETE FROM Employees WHERE EmployeeID = 2;

-- Delete all records from the table (but not the table itself)
DELETE FROM Employees;

SQL Clauses

1. WHERE: Used to filter records based on conditions.

   SELECT * FROM Employees WHERE Age > 30;
   

2. ORDER BY: Used to sort the result set.

   SELECT * FROM Employees ORDER BY Age DESC;
   

3. LIMIT: Used to limit the number of records returned.

   SELECT * FROM Employees LIMIT 5;
   

4. AND / OR: Used for combining multiple conditions in a WHERE clause.

   SELECT * FROM Employees WHERE Age > 30 AND Position = 'Software Developer';
   

SQL Joins

A join is used to combine rows from two or more tables based on a related column.

1. INNER JOIN: Returns records that have matching values in both tables.

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

2. LEFT JOIN: Returns all records from the left table and the matched records from the right table. If there’s no match, NULL values are returned.

   SELECT Employees.FirstName, Departments.DepartmentName
   FROM Employees
   LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
   

3. RIGHT JOIN: Returns all records from the right table and the matched records from the left table. If there’s no match, NULL values are returned.

   SELECT Employees.FirstName, Departments.DepartmentName
   FROM Employees
   RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
   

4. FULL OUTER JOIN: Returns records when there’s a match in either the left or right table.

   SELECT Employees.FirstName, Departments.DepartmentName
   FROM Employees
   FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
   

Aggregate Functions

SQL provides several functions to perform calculations on data.

1. COUNT: Returns the number of rows.

   SELECT COUNT(*) FROM Employees;
   

2. SUM: Returns the sum of a numeric column.

   SELECT SUM(Age) FROM Employees;
   

3. AVG: Returns the average of a numeric column.

   SELECT AVG(Age) FROM Employees;
   

4. MIN and MAX: Return the minimum and maximum values of a column.

   SELECT MIN(Age), MAX(Age) FROM Employees;
   

Advanced SQL Topics

1. Subqueries: A query nested within another query.

   SELECT FirstName, LastName 
   FROM Employees 
   WHERE Age = (SELECT MAX(Age) FROM Employees);
   

2. Group By: Used to group rows that have the same values in specified columns.

   SELECT Position, COUNT(*) 
   FROM Employees 
   GROUP BY Position;
   

3. Having: Used with GROUP BY to filter groups.

   SELECT Position, COUNT(*) 
   FROM Employees 
   GROUP BY Position
   HAVING COUNT(*) > 1;
   

4. Indexes: Used to speed up the retrieval of data from a table.

   CREATE INDEX idx_employee_name ON Employees (FirstName, LastName);
   

5. Transactions: A transaction ensures that a series of operations are executed in a way that maintains data integrity.

   -- Begin transaction
   BEGIN;
   
   -- Perform some queries
   UPDATE Employees SET Age = Age + 1 WHERE EmployeeID = 1;
   
   -- Commit the transaction
   COMMIT;
   

6. Normalization: The process of organizing data in a way that reduces redundancy and improves efficiency. It involves dividing large tables into smaller tables and defining relationships between them.

---

SQL Data Types

Common data types used in SQL include:

• INT: Integer number
• VARCHAR(n): Variable-length string with a maximum length of n
• CHAR(n): Fixed-length string
• DATE: Date in YYYY-MM-DD format
• DATETIME: Date and time
• FLOAT: Floating-point number
• BOOLEAN: True/False values

---

Conclusion

SQL is a powerful language for managing and manipulating data in relational databases. It provides a rich set of features for creating, reading, updating, and deleting data. Mastery of SQL is essential for anyone working with databases, whether you're a developer, data analyst, or database administrator.

If you want more advanced examples or have specific questions, feel free to ask!