SQL Basics

Syntax

SELECT	<the column you want to display>
FROM	<the table containing the data>

• SELECT is a keyword (a reserved word)
• Returns all rows from a table
• The result is unsorted
• The result is not filtered
• SQL keywords are NOT case-sensitive
• Extra spaces are ignored

Select the product names from the Products table

SELECT	prod_name
FROM	Products;

• To select multiple columns, separate each column name in the SELECT statement with a comma

Select the product ID, name and price from the Products table

SELECT	prod_id,
	prod_name,
	prod_price
FROM	Products;

• To select all columns, use an asterisk (*) in the SELECT statement

Select all columns from the Products table

SELECT	*
FROM	Products;

If you do not need every row in a table, limit the number of rows returned

• SQL Server / MS Access: put the TOP keyword followed by the number of rows in the SELECT statement

SELECT TOP 5 cust_name
FROM Customers;

• MySQL / MariaDB / PostgreSQL / SQLite: put the LIMIT keyword followed by the number of rows after the FROM clause

SELECT cust_name
FROM Customers
LIMIT 5;

• Oracle: use a WHERE clause with ROWNUM to limit the number of rows

SELECT cust_name
FROM Customers
WHERE rownum <= 5;

• DB2 / Oracle: put FETCH FIRST X ROWS ONLY after the FROM clause

SELECT cust_name
FROM Customers
FETCH FIRST 5 ROWS ONLY;

For more variations, see W3Schools

Select the first 5 rows from the Products table

SELECT prod_name
FROM   Products
LIMIT  5;

• Use DISTINCT in the SELECT statement to return unique rows
• DISTINCT applies to all columns in the SELECT statement

Select unique vend_id values from the Products table

SELECT	DISTINCT vend_id 
FROM	Products;

• A single-line comment: -- comment

-- This query is straightforward
SELECT * -- All columns are selected here
FROM Customers;
-- LIMIT 1;

• A multi-line comment: /* comment block */

/* Do not remove this query yet
SELECT * 
FROM Customers;
LIMIT 1; */

1. Select everything (all rows and columns) from the OrderItems table (18)

2. Select prod_name and prod_price from the Products table (9)

3. Select order_num from the Orders table (5)

4. Select unique order_num values from the OrderItems table (5)

5. Select all columns from the first 5 rows of the OrderItems table (5)