The Basic SELECT
Where every course in SQL begins, the SELECT statement. We’ll then venture into other basic functions.
Let’s practice some SQL queries on the Northwinds Database.
Concepts
Basic SELECT
Example 1
Query
SELECT * from employees;Result: returns all rows and all columns.
Example 2
Query
SELECT employee_id, last_name, first_name from employees;Result: returns selected columns.
Expressions
Example 1 - Functions
Query
SELECT last_name, first_name, hire_date, current_date,
date_part('month', hire_date)
from employees;Result: returns selected column and a new column with just the month of the date the employee was hired.
Example 2 - Math Equations
Query
SELECT order_id, unit_price, quantity,
unit_price * quantity
from order_details;Result: returns selected column and a new column with the unit_price times the quantity (i.e. total sales).
Column Aliases
Example 1 - Functions (with Alias)
Query
SELECT last_name, first_name, hire_date, current_date,
date_part('month', hire_date) as "Hire Month"
from employees;Result: returns selected column and a new column with just the month of the date the employee was hired.
Example 2 - Math Equations (with Alias)
Query
SELECT order_id, unit_price, quantity,
unit_price * quantity as "Total Value"
from order_details;Result: returns selected column and a new column with the unit_price times the quantity (i.e. total sales).
Concatenation
Example 1 - Function
Query
SELECT concat(first_name, ' ', last_name) as "Name"
from employees;Result: returns a new column named “Name” with the employee’s first and last name concatenated together with a space between the name portions.
Example 2 - Syntax
Query
SELECT first_name || ' ' || last_name as "Name"
from employees;Result: returns a new column named “Name” with the employee’s first and last name concatenated together with a space between the name portions.
Labs
Lab 1
Question 1
Question
List all the products in the North Winds database showing product_id, product_name, quantity_per_unit, unit_price, and units_in_stock.
Answer:
SELECT product_id, product_name, quantity_per_unit, unit_price, units_in_stock
FROM products;Question 2
Question
For all employees at North Winds, list the first name and last name concatenated together with a blank space in-between, and the YEAR when they were hired.
Answer:
SELECT concat(first_name, ' ', last_name) as name,
date_part('year', hire_date)
FROM employees;Question 3
Question
For all products in the North Winds database, list the product_name, unit_price, units_in_stock, and the total value of the inventory of that product as “total_value”.
Hint: total_value = units_in_stock * unit_price
Answer:
SELECT product_name, unit_price, units_in_stock,
units_in_stock * unit_price as total_value
FROM products;Question 4
Question
For all employees at North Winds, list the first and last name concatenated together with a blank space between under column header “name”, and the name of the month (spelled out) for each employee’s birthday.
Answer:
SELECT concat(first_name, ' ', last_name) as name,
--- date_part('month', birth_date) will return the number month
--- to_char(birth_date, 'month') will return the month spelled out, in lowercase
--- to_char(birth_date, 'Month') will return the month spelled out, in proper case
--- to_char(birth_date, 'MONTH') will return the month spelled out, in uppercase
to_char(birth_date, 'Month')
FROM employees;Lab 2
Question 1
Question:
List the customerid, companyname, and country for all customers NOT in the USA.
Answer:
select customer_id, company_name, country
from customers
where not country = 'U.S.A';Question 2
Question:
For all products in the Northwinds database, list the productname, unitprice, unitsinstock, and the total value of the inventory of that product as “Total Value” for all products with a Total Value greater than $1000.
(HINT: total value = unitsinstock * unitprice)
Answer:
select product_name, unit_price, units_in_stock,
(unit_price * units_in_stock) as total_value
from products
where (unit_price * units_in_stock) > 1000;Question 3
Question:
List the productid, productname, and unitprice for all products whose categoryid is an ODD number.
(HINT: categoryid is a one digit integer less than 9)
Answer:
select product_id, product_name, unit_price, category_id
from products
where not (category_id % 2) in (0);Question 4
Question:
List the productid, productname, and quantityperunit for all products that come in bottles.
Answer:
select product_id, product_name, quantity_per_unit
from products
where quantity_per_unit like '%bottle%';Question 5
Question:
List the orderid, customerid, and shippeddate for orders that shipped to Canada in December 1996 through the end of January 1997.
Answer:
select order_id, customer_id, shipped_date
from products
where (ship_country = 'Canada')
and (shipped_date between '1996-12-01' and '1997-01-31');Lab 3
Question 1
Question:
List the employeeid, firstname + lastname concatenated as ‘employee’, and the age of the employee when they were hired.
Answer:
select employee_id, concat(first_name, ' ', last_name) as employee,
age(hire_date, birth_date)
from employees;Question 2
Question:
Run a query to calculate your age as of today.
Answer:
select age(to_date('19941201', 'YYYYMMDD'));Question 3
Question:
List the employeeid, firstname + lastname concatenated as ‘employee’, and hire date for all employees hired in 1993.
Answer:
select employee_id, concat(first_name, ' ', last_name) as employee,
hire_date
from employees
where hire_date between '19930101' and '19931231';
Comments
Example 1 - Single Line Comment
Query
Result: single line comment only visible in the query code text, not in output.
Example 2 - Multi-Line Comment
Query
Result: multi-line comment only visible in the query code text, not in output.