Stephen Williams

SQL for Beginners

---

If you are looking for more advanced SQL, → SQL (Intermediate Level)     SQL (Advanced Level)

---

Structured Query Language (SQL) - is a standardized programming language designed for managing and manipulating data in relational databases. It is the primary language used to interact with and extract information from the databases.

This guide is designed to get you comfortable with the fundamentals of retrieving and manipulating data from a relational database.
We will use a simple two-table schema for all our examples: the Employees table and the Departments table. These tables are linked by the dept_id field, which serves as a foreign key (FK) in the Employees table referencing the primary key (PK) in the Departments table.

Database Schema

Employees Table

emp_id	first_name	last_name	dept_id (FK)	salary	hire_date
101	Alice	Smith	1	70,000	2024-01-15
102	Bob	Johnson	2	65,000	2024-03-20
103	Charlie	Williams	1	85,000	2023-07-10

Departments Table

dept_id (PK)	dept_name	location
1	Engineering	New York
2	Sales	San Francisco

The core of beginner SQL is the SELECT statement, which allows you to ask the database for information.

1. Retrieving All Data (SELECT *)

The most basic command is to select all columns (*) and all rows from a table using the FROM clause.

 SELECT * FROM Employees;

2. Selecting Specific Columns

Often, you only need certain pieces of information. Instead of *, list the column names you want, separated by commas.

 SELECT 
  FirstName, Salary 
 FROM 
  Employees;
        

3. Filtering Data (WHERE Clause)

The WHERE clause is your primary tool for filtering records based on specific criteria.

 SELECT 
   FirstName, LastName, Salary 

 WHERE 
   Salary > 70000;
    

4. Combining Filters (AND, OR, NOT)

Use logical operators to narrow down your search even further.

 SELECT 
  FirstName, DepartmentID, Salary

 FROM 
  Employees

 WHERE 
  DepartmentID = 1 
  AND Salary < 80000;
    

5. Filtering with Lists (IN) and Ranges (BETWEEN)

 SELECT 
  FirstName, DepartmentID
 FROM 
  Employees
 WHERE 
  DepartmentID IN (1, 2);
    

6. Finding Patterns (LIKE)

The LIKE operator uses wildcards (%) to search for patterns.

 SELECT 
  FirstName, LastName

 FROM 
  Employees

 WHERE 
  LastName LIKE 'S%';
    

7. Sorting Results

The ORDER BY clause sorts the result set in ascending ASC or descending DESC order.

 SELECT 
  FirstName, Salary

 FROM 
  Employees

 ORDER BY 
  Salary DESC;
    

8. Basic Data Manipulation (DML)

Learn to create table, (add, change, and remove) data.

Create table creates new table, assigns the data type to columms and primary key

 CREATE Table Employees (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  dept_id INT,
  salary DECIMAL(10, 2),
  hire_date DATE
  );
    

Insert statements add new records

 INSERT INTO 
 Employees 
  (EmployeeID, FirstName, LastName, DepartmentID, Salary, HireDate)

 VALUES 
  (104, 'David', 'Lee', 2, 60000, '2024-05-01');
    

Update statements modify existing records,

 UPDATE 
  Employees

 SET 
  Salary = 75000

 WHERE 
  EmployeeID = 101;
    

Delete statements to remove existing records,

 DELETE 
  Employees

 SET 
  Salary = 75000

 WHERE 
  EmployeeID = 101;
    

---