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

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

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

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

Hint

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

Practice

1. Why are built-in functions important in SQL?
COUNT(), SUM(), and UPPER() are examples of these functions.

easy

A. They simplify common tasks like counting and text changes.

B. They make the database slower by adding extra steps.

C. They require writing complex code every time.

D. They only work with numbers, not text or dates.

Solution

1. Step 1: Understand what built-in functions do

   Built-in functions perform common tasks like counting rows or changing text case without extra code.

2. Step 2: Identify the benefit in SQL queries

   Using these functions makes queries simpler and clearer, saving time and effort.

3. Final Answer:

   They simplify common tasks like counting and text changes. -> Option A

4. Quick Check:

   Built-in functions simplify tasks = C [OK]

Hint: Built-in functions do common tasks quickly [OK]

Common Mistakes:

• Thinking built-in functions slow down queries
• Believing built-in functions only work with numbers
• Assuming built-in functions require complex code

2. Which of the following is the correct syntax to convert a text column name to uppercase in SQL?

easy

A. SELECT UPPERCASE(name) FROM table;

B. SELECT TOUPPER(name) FROM table;

C. SELECT UPPER(name) FROM table;

D. SELECT UCASE(name) FROM table;

Solution

1. Step 1: Recall the standard SQL function for uppercase

   The standard function to convert text to uppercase is UPPER().

2. Step 2: Check the syntax correctness

   The correct syntax is SELECT UPPER(column) FROM table;. Other options are not standard SQL functions.

3. Final Answer:

   SELECT UPPER(name) FROM table; -> Option C

4. Quick Check:

   Uppercase function = UPPER() [OK]

Hint: Use UPPER() to convert text to uppercase [OK]

Common Mistakes:

• Using non-standard function names like TOUPPER or UCASE
• Adding extra letters to function names
• Forgetting parentheses after function name

3. Given the table sales with a column amount, what will this query return?

SELECT COUNT(amount), SUM(amount) FROM sales WHERE amount > 100;

medium

A. The count and sum of amounts greater than 100.

B. The total number of rows and sum of all amounts in the table.

C. Only the sum of amounts greater than 100.

D. An error because COUNT and SUM cannot be used together.

Solution

1. Step 1: Understand the WHERE clause effect

   The WHERE clause filters rows where amount > 100, so only those rows are counted and summed.

2. Step 2: Analyze COUNT and SUM functions

   COUNT(amount) counts rows with non-null amounts > 100, and SUM(amount) adds those amounts.

3. Final Answer:

   The count and sum of amounts greater than 100. -> Option A

4. Quick Check:

   WHERE filters rows, COUNT and SUM apply to filtered rows = A [OK]

Hint: WHERE filters rows before COUNT and SUM apply [OK]

Common Mistakes:

• Thinking COUNT counts all rows ignoring WHERE
• Believing SUM works without filtering
• Assuming COUNT and SUM cannot be used together

4. Identify the error in this SQL query:

SELECT SUM(amount FROM sales;

medium

A. SUM requires two arguments.

B. SUM cannot be used without GROUP BY.

C. Table name should come before the function.

D. Missing closing parenthesis after amount.

Solution

1. Step 1: Check function syntax

   The function SUM() requires parentheses enclosing the column name.

2. Step 2: Locate the syntax error

   The query misses the closing parenthesis after amount, causing a syntax error.

3. Final Answer:

   Missing closing parenthesis after amount. -> Option D

4. Quick Check:

   SUM syntax needs parentheses closed = A [OK]

Hint: Check all parentheses in functions carefully [OK]

Common Mistakes:

• Forgetting to close parentheses in functions
• Thinking GROUP BY is always needed with SUM
• Assuming SUM needs multiple arguments

5. You want to find the average sale amount but only for sales made in the last 30 days. Which SQL query correctly uses built-in functions to do this?

Options:
A) SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - INTERVAL '30' DAY;
B) SELECT AVG(amount) FROM sales WHERE sale_date > DATE_SUB(CURRENT_DATE, 30);
C) SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - 30;
D) SELECT AVG(amount) FROM sales WHERE sale_date > NOW() - INTERVAL 30 DAY;

hard

A. SELECT AVG(amount) FROM sales WHERE sale_date > DATE_SUB(CURRENT_DATE, 30);

B. SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - INTERVAL '30' DAY;

C. SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - 30;

D. SELECT AVG(amount) FROM sales WHERE sale_date > NOW() - INTERVAL 30 DAY;

Solution

1. Step 1: Understand date interval syntax

   Standard SQL uses CURRENT_DATE - INTERVAL '30' DAY to get the date 30 days ago.

2. Step 2: Check each option's correctness

   SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - INTERVAL '30' DAY; uses correct standard syntax. SELECT AVG(amount) FROM sales WHERE sale_date > DATE_SUB(CURRENT_DATE, 30); uses MySQL-specific DATE_SUB but missing interval unit. SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - 30; subtracts integer directly, invalid. SELECT AVG(amount) FROM sales WHERE sale_date > NOW() - INTERVAL 30 DAY; uses NOW() with interval but missing quotes around 30.

3. Final Answer:

   SELECT AVG(amount) FROM sales WHERE sale_date > CURRENT_DATE - INTERVAL '30' DAY; -> Option B

4. Quick Check:

   Standard SQL date interval syntax = A [OK]

Hint: Use INTERVAL with quotes for date calculations [OK]

Common Mistakes:

• Using incorrect or missing interval syntax
• Subtracting numbers directly from dates
• Mixing functions from different SQL dialects