SELECT
statement to read data.DISTINCT
keyword and WHERE
clause for filtering.AS
.SELECT *
statement that returns all fields followed by a FROM table_name
clause indicating the table you want the data to come from.SELECT
, SELECT DISTINCT
FROM
, WHERE
SELECT
, DISTINCT
, FROM
, WHERE
, LIKE
When creating a query, you might organize your thoughts with the following questions first:
- What database will you be using?
- What table(s) within the database will you be querying?
- Will you need to join any tables to get the information you want or filter by a specific field?
- What fields in the table are you interested in?
- Will you need to filter your data or are you interested in a specific time range?
- What will your query do, in plain English?
SELECT
Statement¶-- Select the database.
USE fruits_db;
-- Inspect the columns and data types from a table.
DESCRIBE fruits;
-- Another way to Inspect the columns and data types from a table.
SHOW COLUMNS
FROM fruits;
-- Return all of the rows and columns from a table.
SELECT *
FROM fruits;
-- Select specific column(s) and all of the rows from those column(s).
SELECT name
FROM fruits;
SELECT name, quantity
FROM fruits;
SELECT DISTINCT
Statement¶-- Use chiplotle database to demo a db with duplicates.
USE chipotle;
-- Inspect the columns and data types from a table.
DESCRIBE orders;
-- Return all of the rows and columns from a table. (4622 records returned)
SELECT *
FROM orders;
/*
Select specific column(s) and all of the rows from those column(s).
(4622 records returned)
*/
SELECT item_name
FROM orders;
SELECT
item_name,
item_price
FROM orders;
/*
Return only the unique values from a column using the DISTINCT keyword
(50 records returned)
*/
SELECT DISTINCT item_name
FROM orders;
WHERE
Clause¶/*
Filter so that only records with the value 'Chicken Bowl' in item_name are returned.
(726 records returned)
*/
SELECT *
FROM orders
WHERE item_name = 'Chicken Bowl';
-- Why doesn't the query below run? Never forget this lesson!
SELECT *
FROM orders
WHERE item_price = $4.45;
/*
Filter using the primary key column; only one record will be returned because the value must be unique.
*/
SELECT *
FROM orders
WHERE id = 15;
WHERE
Clause¶-- Filter using a WHERE clause with the BETWEEN & AND operators. (Returns 39 records)
SELECT *
FROM orders
WHERE quantity BETWEEN 3 AND 5;
-- Filter using a WHERE statement >, <, <> operators.
-- (returns 870 records)
SELECT *
FROM orders
WHERE order_id > 1500;
-- (returns 267 records)
SELECT *
FROM orders
WHERE quantity <> 1;
AS
¶-- Create an alias for a column using the AS keyword. (Returns 267 records)
SELECT
item_name AS 'Multiple Item Order',
quantity AS Number
FROM orders
WHERE quantity >= 2;
/*
Notice that if I have spaces in my column alias, I have to put it in single quotes.
If I do not have a space in my colum alias, I do not have to put it in quotes.
*/