Dr Juan Beltran

SQL 101

Introduction

Structured Query Language (SQL) is a standardized programming language used for managing relational databases and performing various operations on the data in them. This tutorial will guide you through the basics to advanced SQL commands.

Part I: SQL Basics

1.1 Setting Up

First, we need to set up a database. For this tutorial, we’ll be using SQLite, a software library that provides a relational database management system.

You can install SQLite from its official website. After installation, you can interact with SQLite via the command-line shell.

1.2 Creating a Database

To create a database in SQLite, you use the following command:

sqlite3 TestDB.db

This command creates a new database named TestDB.db. If the database exists, it opens the database.

1.3 Creating a Table

To create a table in SQL, we use the CREATE TABLE statement.

CREATE TABLE Employees (
    ID int,
    Name text,
    Age int,
    Salary real
);

This creates a table named Employees with four columns: ID, Name, Age, and Salary.

1.4 Inserting Data

We can insert data into the table using the INSERT INTO statement.

INSERT INTO Employees (ID, Name, Age, Salary) 
VALUES (1, 'John Doe', 30, 50000.00 );

This command inserts a new row into the Employees table.

1.5 Querying Data

You can retrieve data using the SELECT statement.

SELECT * FROM Employees;

This command selects all records from the Employees table.

Part II: Intermediate SQL

2.1 Updating Data

You can update existing records using the UPDATE statement.

UPDATE Employees
SET Salary = 55000.00
WHERE ID = 1;

This command updates the salary of the employee with ID 1.

2.2 Deleting Data

You can delete records using the DELETE statement.

DELETE FROM Employees WHERE ID = 1;

This command deletes the employee with ID 1.

2.3 Ordering Data

You can sort the result set using the ORDER BY keyword.

SELECT * FROM Employees ORDER BY Salary DESC;

This command selects all employees and orders them by Salary in descending order.

Part III: Advanced SQL

3.1 Joins

SQL joins are used to combine rows from two or more tables.

SELECT Employees.Name, Orders.OrderID
FROM Employees
INNER JOIN Orders ON Employees.ID = Orders.EmployeeID;

This INNER JOIN keyword selects records that have matching values in both tables.

3.2 Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

SELECT AVG(Salary) AS AverageSalary FROM Employees;

This command calculates the average salary of all employees.

3.3 Group By

The GROUP BY statement groups rows that have the same values in specified columns into aggregated data.

SELECT COUNT(ID), Age
FROM Employees
GROUP BY Age;

This command counts the number of employees of each age.

3.4 Subquery

A Subquery or Inner query or Nested query is a query within another SQL query.

SELECT Name 
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

This command finds employees who earn more than the average

salary.

Conclusion

SQL is a powerful language with vast capabilities in managing and manipulating data in relational databases. With a solid understanding of SQL, from basics to advanced topics, you’ll be well-equipped to handle any data thrown your way!