Why built-in functions matter in SQL - See It in Action

Why Built-in Functions Matter in SQL

📖 Scenario: You work at a small bookstore. You have a table of books with their prices and quantities. You want to find useful information quickly, like the total value of all books in stock and the average price.

🎯 Goal: Build SQL queries step-by-step that use built-in functions to calculate totals and averages from the bookstore's inventory.

📋 What You'll Learn

Create a table called books with columns title, price, and quantity

Insert the exact data rows provided

Use the built-in function SUM() to find the total value of all books in stock

Use the built-in function AVG() to find the average price of books

Use SELECT statements with the built-in functions to get the results

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use built-in SQL functions to analyze inventory and sales data quickly.

💼 Career

Knowing how to use built-in functions is essential for database analysts, developers, and anyone working with data.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns title (text), price (decimal), and quantity (integer). Then insert these exact rows: ('The Alchemist', 10.99, 5), ('1984', 8.99, 8), ('Clean Code', 30.00, 3).

SQL

-- Create the books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Calculate total value of all books in stock

Write a SQL SELECT statement that uses the built-in function SUM() to calculate the total value of all books in stock. Multiply price by quantity for each book and sum these values. Name the result column total_value.

SQL

CREATE TABLE books (
  title TEXT,
  price DECIMAL(5,2),
  quantity INTEGER
);

INSERT INTO books (title, price, quantity) VALUES
('The Alchemist', 10.99, 5),
('1984', 8.99, 8),
('Clean Code', 30.00, 3);

-- Write SELECT with SUM(price * quantity) as total_value
-- Your code here

Need a hint?

Use SUM() to add up all price * quantity values.

3

Calculate average price of books

Write a SQL SELECT statement that uses the built-in function AVG() to find the average price of all books in the books table. Name the result column average_price.

SQL

CREATE TABLE books (
  title TEXT,
  price DECIMAL(5,2),
  quantity INTEGER
);

INSERT INTO books (title, price, quantity) VALUES
('The Alchemist', 10.99, 5),
('1984', 8.99, 8),
('Clean Code', 30.00, 3);

SELECT SUM(price * quantity) AS total_value FROM books;

-- Write SELECT with AVG(price) as average_price
-- Your code here

Need a hint?

Use AVG() to calculate the average of the price column.

4

Combine total value and average price in one query

Write a SQL SELECT statement that uses both SUM(price * quantity) as total_value and AVG(price) as average_price in the same query from the books table.

SQL

CREATE TABLE books (
  title TEXT,
  price DECIMAL(5,2),
  quantity INTEGER
);

INSERT INTO books (title, price, quantity) VALUES
('The Alchemist', 10.99, 5),
('1984', 8.99, 8),
('Clean Code', 30.00, 3);

SELECT SUM(price * quantity) AS total_value FROM books;

SELECT AVG(price) AS average_price FROM books;

-- Write SELECT with SUM(price * quantity) AS total_value and AVG(price) AS average_price
-- Your code here

Need a hint?

You can select multiple built-in functions in one query by separating them with commas.