Intro
SELECT is one of the most common commands used in SQL
Use it to retrieve data from the database
Syntax
*Not all keywords are required*
SELECT -- columns to show /*REQUIRED*/
FROM -- tables to use /*REQUIRED*/
WHERE -- rows to pick /*OPTIONAL*/
GROUP BY -- column to total /*OPTIONAL*/
HAVING -- totals to pick /*OPTIONAL*/
ORDER BY -- columns to sort /*OPTIONAL*/
Sample Data
This is the table we will use as an example:
SELECT * FROM ar_invoices
| invoice# | customer# | salesperson# | invoice_date | invoice_amount | amount_paid | current_month_interest_charges | total_interest_charges |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2019-06-17 10:43:53.727 | 100.00 | 1.00 | 0.00 | 0.00 |
| 2 | 2 | 2 | 2019-05-28 10:43:53.727 | 50.00 | 0.00 | 5.00 | 10.00 |
| 3 | 3 | 2 | 2019-06-17 10:43:53.727 | 70.00 | 0.00 | 10.00 | 20.00 |
| 4 | 2 | 1 | 2019-07-07 10:43:53.727 | 80.00 | 0.00 | 0.00 | 0.00 |
Narrow your data
SELECT salesperson#, amount_paid FROM ar_invoices
| salesperson# | amount_paid |
|---|---|
| 1 | 1.00 |
| 2 | 0.00 |
| 2 | 0.00 |
| 1 | 0.00 |
DISTINCT
Use the DISTINCT statement to return only distinct (different) values
In this example salesperson# 2 has both amount_paid values = 0 so they get amalgamated
whereas salesperson# 1 has 2 distinct values for amount_paid
SELECT DISTINCT salesperson#, amount_paid FROM ar_invoices
| salesperson# | amount_paid |
|---|---|
| 1 | 0.00 |
| 1 | 1.00 |
| 2 | 0.00 |
TOP
Use the TOP statement to limit the number of records returned
Syntax
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition
SELECT TOP 2 invoice_amount FROM ar_invoices ORDER BY invoice_amount DESC
| invoice_amount |
|---|
| 100.00 |
| 80.00 |