Querying through views in SQL - Mini Project: Build & Apply

Querying through Views

📖 Scenario: You work at a small bookstore that keeps track of book sales and customer information in a database. To make it easier for the sales team to get quick summaries, you want to create a view that shows total sales per customer.

🎯 Goal: Create a view named customer_sales_view that shows each customer's customer_id, customer_name, and their total sales_amount. Then write a query to select all data from this view.

📋 What You'll Learn

Create a table named customers with columns customer_id (integer) and customer_name (text).

Create a table named sales with columns sale_id (integer), customer_id (integer), and amount (numeric).

Insert the exact data provided into both tables.

Create a view named customer_sales_view that joins customers and sales and sums the amount per customer.

Write a query to select all columns from customer_sales_view.

💡 Why This Matters

🌍 Real World

Views help businesses create easy-to-use summaries of complex data, making it faster for teams to get insights without writing complicated queries every time.

💼 Career

Database developers and analysts often create and query views to improve data accessibility and performance in real-world applications.

1

Create tables and insert data

Create a table called customers with columns customer_id (integer) and customer_name (text). Then create a table called sales with columns sale_id (integer), customer_id (integer), and amount (numeric). Insert these exact rows into customers: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Insert these exact rows into sales: (101, 1, 50.00), (102, 2, 30.00), (103, 1, 20.00), (104, 3, 40.00).

SQL

-- Create customers and sales tables and insert data
-- Your code here

Need a hint?

Use CREATE TABLE statements for both tables. Use INSERT INTO with multiple rows for inserting data.

2

Create the view for customer sales

Create a view named customer_sales_view that shows customer_id, customer_name, and the total sales amount as total_sales. Use a JOIN between customers and sales and group by customer_id and customer_name.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  customer_id INTEGER,
  amount NUMERIC
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO sales (sale_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 2, 30.00),
  (103, 1, 20.00),
  (104, 3, 40.00);

-- Create the view customer_sales_view below
-- Your code here

Need a hint?

Use CREATE VIEW with a SELECT statement that joins the two tables and groups by customer.

3

Query the view

Write a query to select all columns from the view customer_sales_view.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  customer_id INTEGER,
  amount NUMERIC
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO sales (sale_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 2, 30.00),
  (103, 1, 20.00),
  (104, 3, 40.00);

CREATE VIEW customer_sales_view AS
SELECT
  customers.customer_id,
  customers.customer_name,
  SUM(sales.amount) AS total_sales
FROM customers
JOIN sales ON customers.customer_id = sales.customer_id
GROUP BY customers.customer_id, customers.customer_name;

-- Select all columns from customer_sales_view below
-- Your code here

Need a hint?

Use SELECT * FROM customer_sales_view; to get all data from the view.

4

Add an ORDER BY clause to the query

Modify the query to select all columns from customer_sales_view and order the results by total_sales in descending order.

SQL

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE sales (
  sale_id INTEGER,
  customer_id INTEGER,
  amount NUMERIC
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO sales (sale_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 2, 30.00),
  (103, 1, 20.00),
  (104, 3, 40.00);

CREATE VIEW customer_sales_view AS
SELECT
  customers.customer_id,
  customers.customer_name,
  SUM(sales.amount) AS total_sales
FROM customers
JOIN sales ON customers.customer_id = sales.customer_id
GROUP BY customers.customer_id, customers.customer_name;

-- Modify the query to order by total_sales descending
-- Your code here

Need a hint?

Add ORDER BY total_sales DESC after the SELECT statement to sort results from highest to lowest sales.