Learn SQL

There are many ways in which you can learn SQL and there exists extensive resources on every aspect of SQL. We prefer a hands-on learning experience. Our advice is to learn the basics and do contextual learning. In other words, start interacting with your data from as quickly as possible and learn the functions and areas of SQL that YOU actually need.

We have compiled a SQL tutorial introducing the basic concepts of SQL, which should get you started. Subsequently, we recommend that you define a problem or find a dataset that is relevant to you and your organization and start creating value almost immediately.

Remember, becoming proficient in SQL takes time and practice, so don't get discouraged if you find some concepts challenging at first. With patience and persistence, you'll be able to navigate databases with ease.

Below is a summary of the core concepts:

  1. Introduction to Databases and SQL

    • Lesson: Discuss the history and purpose of SQL. Explore a simple relational database structure with tables, columns, and relationships.

    • Example: Show a database diagram and explain the tables and relationships.

    • Solution: Consider a simple relational database for a company. This database might include three tables: Employees, Departments, and Projects.

      • Employees table includes columns like EmployeeID, LastName, FirstName, BirthDate, and DepartmentID. Here, EmployeeID is a unique identifier (also known as the primary key) for each employee.
      • Departments table includes DepartmentID and DepartmentName. DepartmentID is a unique identifier for each department.
      • Projects table includes ProjectID, ProjectName, and LeadEmployeeID. ProjectID is a unique identifier for each project.

      There are relationships among these tables:

      • The Employees table is related to the Departments table through the DepartmentID. This is called a foreign key, which is used to link two tables together. In this case, it links each employee to a department.
      • The Projects table is related to the Employees table through the LeadEmployeeID. This foreign key links each project to an employee who is leading that project.

      These relationships between tables are what make the database relational. They allow us to use SQL to make complex queries across multiple tables. For instance, we could easily find out the department in which an employee works, or the projects that an employee is leading.

  2. SQL Syntax and Data Types

    • Lesson: Explain SQL's basic syntax and introduce data types (INT, VARCHAR, DATE, BOOLEAN, etc.).

    • Example: Create a new table:

          CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(100),
    BirthDate DATE,
    IsFullTime BOOLEAN
);

    • Solution: The new Employees table should have four columns with the respective data types.

  3. Retrieving Data with SELECT

    • Lesson: Explain how to use the SELECT statement to retrieve data.
    • Example: Retrieve all data from the Employees table: SELECT * FROM Employees;
    • Solution: This statement returns all rows in the Employees table.

  4. Filtering Data with WHERE

    • Lesson: Explain the use of WHERE to filter results.
    • Example: Retrieve full-time employees: SELECT * FROM Employees WHERE IsFullTime = True;
    • Solution: This query returns all rows where IsFullTime is True.

  5. Sorting Data with ORDER BY

    • Lesson: Explain how to use ORDER BY to sort results.
    • Example: Retrieve employees ordered by name: SELECT * FROM Employees ORDER BY Name;
    • Solution: This query returns all rows sorted by the Name column in ascending order.

  6. Combining Tables with JOIN

    • Lesson: Discuss how JOIN combines rows from two or more tables.

    • Example: If there's a second table Departments, retrieve employee names with their department names:

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

    • Solution: This query returns a list of employee names and their respective department names.

  7. Aggregating Data with GROUP BY and Aggregate Functions

    • Lesson: Teach how to use GROUP BY and aggregation functions (COUNT, SUM, AVG, MAX, MIN).

    • Example: Get the count of employees in each department:

      SELECT Departments.DepartmentName, COUNT(Employees.ID) as EmployeeCount
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.ID
GROUP BY Departments.DepartmentName;

    • Solution: This query returns a list of departments and the number of employees in each department.

  8. Manipulating Data

    • Lesson: Show how to insert, update, and delete data.

    • Example: Insert a new employee, update their department, and then delete the record:

      INSERT INTO Employees (ID, Name, BirthDate, IsFullTime) VALUES (1, 'John Doe', '1990-01-01', True);
UPDATE Employees SET DepartmentID = 2 WHERE ID = 1;
DELETE FROM Employees WHERE ID = 1;

    • Solution: These queries insert a new employee, change their department, and then remove the employee from the table.

  9. Creating and Modifying Database Objects

    • Lesson: Explain how to create and modify tables.

    • Example: Create a Projects table, then add a Deadline column:

      CREATE TABLE Projects (ID INT, ProjectName VARCHAR(100));
ALTER TABLE Projects ADD COLUMN Deadline DATE;

    • Solution: These queries create a new table called Projects with two columns (ID and ProjectName) and then add a new column (Deadline) to the Projects table.

  10. Advanced SQL Concepts

    • Lesson: Discuss subqueries, stored procedures, and indexing.

    • Example: Write a query using a subquery to find employees who work in departments with more than five employees, create a simple stored procedure to insert a new department, and create an index on the Name column of the Employees table:

      SELECT * FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(ID) > 5);

CREATE PROCEDURE CreateDepartment @DepartmentName varchar(100)
AS
INSERT INTO Departments (DepartmentName) VALUES (@DepartmentName);

CREATE INDEX idx_Employees_Name ON Employees (Name);

    • Solution: The first query returns employees in departments with more than five employees. The second query creates a new stored procedure that inserts a new department. The last query creates an index on the Name column of the Employees table to improve query performance.

Was this page helpful?