Lab Tutorial 5: Scaler Functions

Scalable functions in SQL are functions that take a set of values as input and return a single value. Common examples include COUNT, SUM, AVG, MIN, and MAX. Here’s how you can demonstrate these scalar functions using the sample bookstore database:

Sample Database (continued):

Assume you have the same sample database as before:

1. books:
   • Columns: book_id, title, author, price, quantity.
2. customers:
   • Columns: customer_id, name, email, phone.
3. orders:
   • Columns: order_id, customer_id, order_date.
4. order_items:
   • Columns: order_item_id, order_id, book_id, quantity, unit_price.

Sample Queries for Scalar Functions:

1. Count the total number of books in the books table.

COUNT(*) returns the number of rows in the books table, effectively giving the total number of books.

SELECT COUNT(*) AS total_books FROM books;

2. Calculate the total price of all books in the books table.


SUM(price) calculates the sum of the price column in the books table, giving the total price of all books.

SELECT SUM(price) AS total_price FROM books;

3. Calculate the average price of books in the books table.

AVG(price) calculates the average value of the price column in the books table, giving the average price of books.

SELECT AVG(price) AS average_price FROM books;

4. Find the minimum price among all books in the books table.


MIN(price) returns the smallest value in the price column of the books table, giving the minimum price among all books.

SELECT MIN(price) AS min_price FROM books;

5. Find the maximum price among all books in the books table.

MAX(price) returns the largest value in the price column of the books table, giving the maximum price among all books.

SELECT MAX(price) AS max_price FROM books;