Write a query to count the number of rows in the orders table.

Objective:

Write a SQL query to count the number of rows in a table named orders.

Step 1: Create the orders Table

Let’s assume we are working with a basic orders table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    product_name VARCHAR(100),
    quantity INT,
    order_date DATE
);

Step 2: Insert Dummy Data

Add some dummy records into the orders table:

INSERT INTO orders (order_id, customer_name, product_name, quantity, order_date) VALUES
(1, 'Alice', 'Laptop', 1, '2023-01-05'),
(2, 'Bob', 'Smartphone', 2, '2023-02-10'),
(3, 'Charlie', 'Tablet', 1, '2023-03-15'),
(4, 'David', 'Monitor', 3, '2023-04-20'),
(5, 'Eve', 'Keyboard', 4, '2023-05-25');

Step 3: Write the SQL Query to Count Rows

Now we write the query to count the total number of rows (orders):

SELECT COUNT(*) AS total_orders FROM orders;

Output:

This means there are 5 rows (orders) in the table.

Explanation:

• COUNT(*): Counts all rows in the table.
• AS total_orders: Gives a meaningful alias to the output column.

Optional: Count with Conditions

You can also count rows based on a condition. For example:

-- Count orders with quantity more than 2
SELECT COUNT(*) AS big_orders FROM orders WHERE quantity > 2;

Would return:

(From David and Eve)