Sample Database and Basic Commands

To make the examples easy to follow, we will use a hypothetical Products

table with the following structure:

ProductI Product Categor UnitPric StockQu LastUpdat

D Name y e antity ed

1 Laptop Electroni 1200.00 50 2023-01-15

cs

2 Mouse Electroni 25.50 200 2023-01-15

cs

3 T-Shirt Apparel 15.00 100 2023-02-20

4 Jeans Apparel 50.00 75 2023-02-20

5 Coffee Applianc 89.99 20 2023-03-01

Maker es

Creating and Accessing a Database

First, you need to create a database to hold your tables and then select it
for use.
●​ Creating a database: The CREATE DATABASE command is used to
create a new, empty database.
CREATE DATABASE Store;​

●​ Accessing a database: The USE command tells the system which

database you want to work with.
USE Store;​
(Note: These commands create or select the database and do not return a
visual output in the same way a SELECT query does.)

The SELECT Command

The SELECT command is the most common way to retrieve data from a
table. You specify the columns you want to see and the table they are in.
●​ Selecting all columns: The asterisk * is a wildcard that retrieves all
columns from the table.
SELECT * FROM Products;​

Output:

ProductI Product Categor UnitPric StockQu LastUpd

D Name y e antity ated

1 Laptop Electroni 1200.00 50 2023-01-

cs 15

2 Mouse Electroni 25.50 200 2023-01-

cs 15

3 T-Shirt Apparel 15.00 100 2023-02-

20

4 Jeans Apparel 50.00 75 2023-02-

20

5 Coffee Applianc 89.99 20 2023-03-

Maker es 01
●​ Selecting specific columns: You can list the specific columns you
need.
SELECT ProductName, UnitPrice FROM Products;​
Output:

ProductName UnitPrice

Laptop 1200.00

Mouse 25.50

T-Shirt 15.00

Jeans 50.00

Coffee Maker 89.99

●​ Selecting multiple specific columns:
SELECT ProductID, ProductName, StockQuantity FROM Products;​

Output:

ProductID ProductName StockQuantity

1 Laptop 50

2 Mouse 200

3 T-Shirt 100

4 Jeans 75

5 Coffee Maker 20

Use of the DISTINCT Keyword

The DISTINCT keyword is used in a SELECT statement to return only
unique values, eliminating duplicates from the result set.
●​ Finding unique categories:
SELECT DISTINCT Category FROM Products;​

Output:

Category

Electronics

Apparel

Appliances
●​ Finding unique unit prices:
SELECT DISTINCT UnitPrice FROM Products;​

Output:

UnitPrice

1200.00

25.50

15.00

50.00

89.99

Simple Calculations and Scalar Expressions

You can perform basic arithmetic calculations directly within a SELECT
statement.
●​ Simple calculation for total value of stock:
SELECT ProductName, UnitPrice, StockQuantity, UnitPrice *
StockQuantity AS TotalValue FROM Products;​

Output:

ProductName UnitPrice StockQuantity TotalValue

Laptop 1200.00 50 60000.00

Mouse 25.50 200 5100.00

T-Shirt 15.00 100 1500.00

Jeans 50.00 75 3750.00

Coffee Maker 89.99 20 1799.80

●​ Scalar expression with a literal:
SELECT ProductName, 'In Stock' AS Status FROM Products;​

Output:

ProductName Status

Laptop In Stock

Mouse In Stock

T-Shirt In Stock

Jeans In Stock
 Coffee Maker In Stock

Using Column Aliases

A column alias gives a temporary, more readable name to a column or a
calculation in the result set.
●​ Aliasing a column name:
SELECT ProductName AS 'Product Name', UnitPrice AS 'Price Per Unit'
FROM Products;​

Output:

Product Name Price Per Unit

Laptop 1200.00

Mouse 25.50

T-Shirt 15.00

Jeans 50.00

Coffee Maker 89.99

●​ Aliasing a calculation result:
SELECT (UnitPrice * StockQuantity) AS 'Total Inventory Value' FROM
Products WHERE ProductName = 'Jeans';​

Output:

Total Inventory Value

3750.00
Handling NULLs
NULL is a special value that represents missing or unknown data. You must
use IS NULL or IS NOT NULL to check for it.
●​ Searching for NULL values:
SELECT ProductName FROM Products WHERE StockQuantity IS NULL;​

(Note: There are no NULL values in our sample data, so the query would
return no rows.)

Output:
(No records returned)
●​ Finding records that are not NULL:
SELECT ProductName FROM Products WHERE StockQuantity IS NOT
NULL;​

Output:

ProductName

Laptop

Mouse

T-Shirt

Jeans

Coffee Maker

Putting Text in Query Output

You can concatenate text with column values to create a more descriptive
output.

SELECT 'The ' || ProductName || ' costs $' || UnitPrice AS

ProductDescription FROM Products;​

Output:

ProductDescription

The Laptop costs $1200.00

The Mouse costs $25.50

The T-Shirt costs $15.00

The Jeans costs $50.00

The Coffee Maker costs $89.99

The WHERE Clause

The WHERE clause is used to filter records and retrieve only those that
satisfy a specified condition.
●​ Using a relational operator (>):
SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice >
50;​

Output:

ProductName UnitPrice

Laptop 1200.00
 Coffee Maker 89.99
●​ Using a different relational operator (<):
SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice <
50;​

Output:

ProductName UnitPrice

Mouse 25.50

T-Shirt 15.00
●​ Using logical operators (AND and OR):
-- Using AND to get products with a price over 50 and in the 'Apparel'
category​
SELECT ProductName, UnitPrice, Category FROM Products WHERE
UnitPrice > 50 AND Category = 'Apparel';​

Output:

ProductName UnitPrice Category

Jeans 50.00 Apparel

-- Using OR to get products that are 'Electronics' OR have a price less than
30​
SELECT ProductName, UnitPrice, Category FROM Products WHERE
Category = 'Electronics' OR UnitPrice < 30;​

Output:
 ProductName UnitPrice Category

Laptop 1200.00 Electronics

Mouse 25.50 Electronics

T-Shirt 15.00 Apparel

●​ Using a more complex logical condition:
-- Find products that are Electronics with a price over 100, or products that
are Appliances.​
SELECT ProductName, UnitPrice, Category FROM Products WHERE
(Category = 'Electronics' AND UnitPrice > 100) OR Category =
'Appliances';​

Output:

ProductName UnitPrice Category

Laptop 1200.00 Electronics

Coffee Maker 89.99 Appliances

Conditions Based on Ranges, Lists, and Patterns

●​ Condition based on a range: The BETWEEN operator.
-- Find products with a price between $20 and $60 (inclusive)​
SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice
BETWEEN 20 AND 60;​

Output:
 ProductName UnitPrice

Mouse 25.50

Jeans 50.00

-- Find products with a stock quantity between 50 and 150​

SELECT ProductName, StockQuantity FROM Products WHERE
StockQuantity BETWEEN 50 AND 150;​

Output:

ProductName StockQuantity

Laptop 50

T-Shirt 100

Jeans 75
●​ Condition based on a list: The IN operator.
-- Find products in either the 'Electronics' or 'Apparel' categories​
SELECT ProductName, Category FROM Products WHERE Category IN
('Electronics', 'Apparel');​

Output:

ProductName Category

Laptop Electronics

Mouse Electronics

T-Shirt Apparel
 Jeans Apparel

-- Find products with a ProductID of 1, 3, or 5​

SELECT ProductName, ProductID FROM Products WHERE ProductID
IN (1, 3, 5);​

Output:

ProductName ProductID

Laptop 1

T-Shirt 3

Coffee Maker 5
●​ Condition based on a pattern: The LIKE operator.
-- Finds products where the name starts with 'T'​
SELECT ProductName FROM Products WHERE ProductName LIKE
'T%';​

Output:

ProductName

T-Shirt

-- Finds products where the name ends with 's'​

SELECT ProductName FROM Products WHERE ProductName LIKE '%s';​

Output:
 ProductName

Jeans

Mouse

-- Finds products where the name has 'ee' in it​

SELECT ProductName FROM Products WHERE ProductName LIKE
'%ee%';​

Output:

ProductName

Coffee Maker

-- Finds products where the second letter of the name is 'o' (using the '_'
wildcard)​
SELECT ProductName FROM Products WHERE ProductName LIKE
'_o%';​

Output:

ProductName

Mouse

Coffee Maker

Sorting Results using ORDER BY

The ORDER BY clause is used to sort the result set in ascending (ASC) or
descending (DESC) order.
●​ Sorting by a single column in descending order:
SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice
DESC;​

Output:

ProductName UnitPrice

Laptop 1200.00

Coffee Maker 89.99

Jeans 50.00

Mouse 25.50

T-Shirt 15.00
●​ Sorting by multiple columns:
-- Sort by Category (ascending) and then by UnitPrice (descending) within
each category​
SELECT Category, ProductName, UnitPrice FROM Products ORDER BY
Category ASC, UnitPrice DESC;​

Output:

Category ProductName UnitPrice

Appliances Coffee Maker 89.99

Apparel Jeans 50.00

Apparel T-Shirt 15.00

 Electronics Laptop 1200.00

Electronics Mouse 25.50

Example Functions
SQL provides many built-in functions for manipulating strings, numbers,
and dates.
●​ String Functions
-- Convert product names to uppercase​
SELECT UPPER(ProductName) AS 'Uppercase Name' FROM Products;​

Output:

Uppercase Name

LAPTOP

MOUSE

T-SHIRT

JEANS

COFFEE MAKER

-- Extract the first 3 characters of the product name​

SELECT SUBSTRING(ProductName, 1, 3) AS 'Product Code' FROM
Products;​

Output:
 Product Code

Lap

Mou

T-S

Jea

Cof
●​ Numeric Functions
-- Calculate the average price​
SELECT AVG(UnitPrice) AS 'Average Price' FROM Products;​

Output:

Average Price

276.10

-- Calculate the total stock quantity for all products​

SELECT SUM(StockQuantity) AS 'Total Stock' FROM Products;​

Output:

Total Stock

445

-- Count the number of products in the 'Electronics' category​

SELECT COUNT(*) FROM Products WHERE Category = 'Electronics';​
Output:

COUNT(*)

2
●​ Date and Time Functions
-- Calculate the number of days since the product was last updated (using
a hypothetical current date of '2023-04-01')​
SELECT ProductName, DATEDIFF('2023-04-01', LastUpdated) AS 'Days
Since Update' FROM Products;​

Output:

ProductName Days Since Update

Laptop 76

Mouse 76

T-Shirt 40

Jeans 40

Coffee Maker 31

-- Extract the month from the LastUpdated date​

SELECT ProductName, MONTH(LastUpdated) AS 'Last Updated Month'
FROM Products;​

Output:
ProductName Last Updated Month

Laptop 1

Mouse 1

T-Shirt 2

Jeans 2

Coffee Maker 3