SQL practice: Northwind Database.
An interactive SQL practice session using the Northwind database.
The Northwind database is a sample database that is commonly used for practicing SQL queries. It consists of several tables that represent different aspects of a fictional company’s operations, such as customers, orders, products, employees, etc.
In this SQL practice module, I’m using a built-in web-based environment at www.sql-practice.com which is a great site for practicing SQL queries.
There are 13 questions available, ranging from easy to hard-to-perform SQL queries. Let’s take a look at the schema:
Now, let’s look briefly at the available tables and their primary and foreign keys.
After checking, I’m making a comment on the primary key and foreign key.
SELECT * FROM products; — Product_id, category_id, supplier_id
SELECT * FROM customers; — customer_id
SELECT * FROM categories; — category_id
SELECT * FROM suppliers; — suplier_id
SELECT * FROM orders; — order_id, customer_id, employee_id
SELECT * FROM employees; — employee_id
Let’s start answering the questions on the website.
Q.1 — Show the category_name and description from the categories table sorted by category_name.
select category_name, description
from categories
order by category_name;
Q.2 — Show all the contact_name, addresses, city of all customers who are not from ‘Germany’, ‘Mexico’, ‘Spain’
select contact_name, address, city
from customers
where country not in (‘Germany’, ‘Mexico’, ‘Spain’);
Q.3 — Show order_date, shipped_date, customer_id, Freight of all orders placed on 2018 Feb 26
select order_date, shipped_date, customer_id, freight
from orders
where order_date = ‘2018–02–26’;
Q.4 — Show the employee_id, order_id, customer_id, required_date, shipped_date from all orders shipped later than the required date.
select employee_id, order_id, customer_id, required_date, shipped_date
from orders
where shipped_date > required_date;
Q.5 — Show all the even numbered Order_id from the orders table.
select order_id
from orders
where mod(order_id, 2) = 0;
Q.6 — Show the city, company_name, contact_name of all customers from cities which contain the letter ‘L’ in the city name, sorted by contact_name.
select city, company_name, contact_name
from customers
where city like ‘%L%’
order by contact_name;
Q.7 — Show the company_name, contact_name, and fax number of all customers that have a fax number. (not null)
select company_name, contact_name, fax
from customers
where fax is not null;
Q.8 — Show the first_name, last_name of the most recently hired employee.
select first_name, last_name, Max(hire_date) as hire_date
from employees;
Q.9 — Show the average unit price rounded to 2 decimal places, the total units in stock, total discontinued products from the products table.
select
round(avg(unit_price), 2) as avg_unit_price,
sum(units_in_stock) as total_unit_in_stock,
sum(discontinued) as discontinued_product
from products;
Q.10 — Show the ProductName, CompanyName, CategoryName from the products, suppliers, and categories table.
select p.product_name, s.company_name, c.category_name
from products p
join categories c on p.category_id = c.category_id
join suppliers s on p.supplier_id = s.supplier_id;
Q.11 — Show the category_name and the average product unit price for each category rounded to 2 decimal places.
select c.category_name, round(avg(p.unit_price), 2) as avg_unit_price
from categories c
join products p on p.category_id = c.category_id
group by category_name;
Q.12 /* Show the city, company_name, contact_name from the customers and suppliers table merged together. Create a column which contains ‘customers’ or ‘suppliers’ depending on the table it came from. */
select city, company_name, contact_name, ‘customers’ as identity
from customers
union
select city, company_name, contact_name, ‘suppliers’ as identity
from suppliers;
Q.13 /* Show the employee’s first_name and last_name, a “num_orders” column with a count of the orders taken, and a column called “Shipped” that displays “On Time” if the order shipped on time and “Late” if the order shipped late.
Order by employee last_name, then by first_name, and then descending by number of orders.*/
select e.first_name, e.last_name,
count(o.order_id) as num_orders,
case
when o.required_date > o.shipped_date then ‘On Time’
else ‘Late’
end as shipped
from employees e
join orders o on e.employee_id = o.employee_idgroup by shipped, first_name
order by last_name, first_name, num_orders desc
In summary, I had to use a wide range of SQL queries to answer those business questions, from simple SELECT statements to more complex queries involving [Distinct, Group By, Having, In, Join, Like, Null, Union, Where, joins, subqueries, Max, case, and aggregations].
If you’ve read this far, I appreciate you taking the time to review my work.
