SQL: Practice Exercises for COUNT Function

If you want to test your skills using the SQL COUNT function, try some of our practice exercises.

These practice exercises allow you to test your skills with the COUNT function. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer.

Get started!

Return to Tutorial

Practice Exercise #1:

Based on the products table populated with the following data, count the number of products that have a category_id of 50.

CREATE TABLE products
( product_id int NOT NULL,
  product_name char(50) NOT NULL,
  category_id int,
  CONSTRAINT products_pk PRIMARY KEY (product_id)
);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(1,'Pear',50);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(2,'Banana',50);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(3,'Orange',50);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(4,'Apple',50);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(5,'Bread',75);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(6,'Sliced Ham',25);

INSERT INTO products
(product_id, product_name, category_id)
VALUES
(7,'Kleenex',null);

Solution for Practice Exercise #1:

Although inefficient in terms of performance, the following SQL SELECT statement would return the number of products who have a category_id of 50.

Try It

SELECT COUNT(*) AS total
FROM products
WHERE category_id = 50;

These are the results that you should see:

total
4

A more efficient implementation of the same solution would be the following SELECT statement:

Try It

SELECT COUNT(1) AS total
FROM products
WHERE category_id = 50;

Now, the SQL COUNT function does not need to retrieve all of the fields from the table (ie: employee_number, employee_name, and salary), but rather whenever the condition is met, it will retrieve the numeric value of 1. Thus, increasing the performance of the SQL statement.

Practice Exercise #2:

Based on the suppliers table populated with the following data, count the number of distinct city values in the suppliers table:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  city char(50),
  state char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(100, 'Microsoft', 'Redmond', 'Washington');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(200, 'Google', 'Mountain View', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(300, 'Oracle', 'Redwood City', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(400, 'Kimberly-Clark', 'Irving', 'Texas');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(500, 'Tyson Foods', 'Springdale', 'Arkansas');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(600, 'SC Johnson', 'Racine', 'Wisconsin');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(700, 'Dole Food Company', 'Westlake Village', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(800, 'Flowers Foods', 'Thomasville', 'Georgia');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(900, 'Electronic Arts', 'Redwood City', 'California');

Solution for Practice Exercise #2:

The following SELECT statement would return the number of distinct cities in the suppliers table:

Try It

SELECT COUNT(DISTINCT city) AS total
FROM suppliers;

These are the results that you should see:

total
8

Practice Exercise #3:

Based on the orders table populated with the following data, count the number of orders for each order_date in the orders table:

CREATE TABLE orders
( order_id int NOT NULL,
  customer_id int,
  order_date date,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

INSERT INTO orders
(order_id, customer_id, order_date)
VALUES
(1,7000,'2016/04/18');

INSERT INTO orders
(order_id, customer_id, order_date)
VALUES
(2,5000,'2016/04/18');

INSERT INTO orders
(order_id, customer_id, order_date)
VALUES
(3,8000,'2016/04/19');

INSERT INTO orders
(order_id, customer_id, order_date)
VALUES
(4,4000,'2016/04/20');

INSERT INTO orders
(order_id, customer_id, order_date)
VALUES
(5,null,'2016/05/01');

Solution for Practice Exercise #3:

The following SELECT statement would return the number of orders for each order_date in the orders table:

Try It

SELECT order_date, COUNT(*) AS total
FROM orders
GROUP BY order_date;

These are the results that you should see:

order_date	total
2016/04/18	2
2016/04/19	1
2016/04/20	1
2016/05/01	1

A more efficient implementation of the same solution would be the following SELECT statement:

Try It

SELECT order_date, COUNT(1) AS total
FROM orders
GROUP BY order_date;