Basic SQL Syntax and Queries

Great job setting up your environment! Now, let's jump into the basics of SQL syntax and learn how to write simple queries to pull data from your database.

What's Inside

1. SQL Query Structure
   1. The SELECT Statement
   2. The FROM Clause
2. Filtering Data with WHERE
   1. Comparison Operators
   2. Logical Operators
3. Ordering and Limiting Results
   1. ORDER BY Clause
   2. LIMIT Clause
4. Basic Aggregate Functions
   1. COUNT, SUM, AVG
   2. MIN, MAX

1. SQL Query Structure

Every SQL query starts with the SELECT and FROM clauses. The SELECT part tells SQL what columns you want, and the FROM part tells it which table to look in.

1.1 The SELECT Statement

The SELECT statement is how you ask for data.

SELECT column1, column2, ...
FROM table_name;

**Example:**

SELECT FirstName, LastName
FROM Employees;

1.2 The FROM Clause

The FROM clause tells SQL which table to get the data from.

2. Filtering Data with WHERE

Want only certain records? Use the WHERE clause to filter your data.

2.1 Comparison Operators

Here are some common comparison operators:

• = Equal
• <> Not equal
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to

**Example:**

SELECT *
FROM Products
WHERE Price > 50;

2.2 Logical Operators

Combine conditions with logical operators:

• AND
• OR
• NOT

**Example:**

SELECT *
FROM Products
WHERE Price > 50 AND Category = 'Electronics';

3. Ordering and Limiting Results

3.1 ORDER BY Clause

Sort your results with the ORDER BY clause. Choose ascending (ASC) or descending (DESC) order.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC|DESC;

**Example:**

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName ASC;

3.2 LIMIT Clause

Want only a few records? Use the LIMIT clause.

SELECT column1, column2, ...
FROM table_name
LIMIT number;

**Example:**

SELECT *
FROM Products
LIMIT 10;

Note: In some SQL versions like SQL Server, use TOP instead of LIMIT:

SELECT TOP 10 *
FROM Products;

4. Basic Aggregate Functions

Aggregate functions let you do calculations on your data.

4.1 COUNT, SUM, AVG

• COUNT(column_name): Counts the number of rows.
• SUM(column_name): Adds up all the values.
• AVG(column_name): Finds the average value.

**Example:**

SELECT COUNT(*) AS TotalProducts
FROM Products;

SELECT SUM(Price) AS TotalRevenue
FROM Orders;

SELECT AVG(Price) AS AveragePrice
FROM Products;

4.2 MIN, MAX

• MIN(column_name): Finds the smallest value.
• MAX(column_name): Finds the largest value.

**Example:**

SELECT MIN(Price) AS LowestPrice
FROM Products;

SELECT MAX(Price) AS HighestPrice
FROM Products;