(BIS) SQL Question Bank

 How many customers are in the Customers table?

8

Use the Orders table. In the Orders table, what does each row represent?

One order

Use both tables. Which column appears in both tables and connects them?

customer_id

Use both tables. Order O00902 has customer_id C0367. Who placed this order?

Barbara Alvarez

In a query, which word tells SQL which table to look in?

FROM

Which word keeps only the rows that match a condition (for example, only Texas customers)?

WHERE

In the query below, what does the mean? (select * from customers)

Show all columns

What does COUNT(*) do?

Counts how many rows there are

What does the following do? ORDER BY order_total DESC;

Sorts orders from biggest total to smallest

What does the following do? GROUP BY ship_mode;

Puts orders into groups by ship mode so each group can be counted or added up

Use the Customers table. How many names does this query return? SELECT first_name FROM Customers WHERE state = 'TX';

4

Use the Orders table. What does this query return? SELECT order_status FROM Orders WHERE order_id = 'O00902'

Cancelled

Use the Orders table. What does this query return? SELECT payment_method FROM Orders WHERE order_id = 'O00587';

Gift Card

Use the Orders table. What number does this query return? SELECT COUNT(*) FROM Orders WHERE payment_method = 'Credit Card';

5

Use the Customers table. Which names does this query return? SELECT first_name FROM Customers WHERE segment = 'Home Office';

Heather and Carol

Use the Orders table. What number does this query return? SELECT COUNT(*) FROM Orders;

12

Use the Customers table. What does this query return? SELECT city FROM Customers WHERE first_name = 'Anthony';

Fort Worth

“Show everything in the Customers table.” Which query answers this?

SELECT * FROM Customers

“How many orders are there?” Which query answers this?

SELECT COUNT(*) FROM Orders

“Show the customers who live in Texas.” Which query answers this?

SELECT * FROM Customers WHERE state = 'TX'

“What is the total dollar amount of all orders?” Which query answers this?

SELECT SUM(order_total) FROM Orders

“Show orders from the biggest total to the smallest.” Which query answers this?

SELECT * FROM Orders ORDER BY order_total DESC

Use the Customers table. A manager asks, “How many customers do we have?” The AI writes the query below, which returns 8. Is the AI right? SELECT COUNT(*) FROM Customers;

Yes. The table has 8 customers

Use the Customers table. A manager asks, “Show our Texas customers.” The AI’s query returns all 8 customers, including people in Atlanta and Chicago. Is the AI right?

No. It also shows customers from other states, so it is missing WHERE state = 'TX'

The AI gives you a query. What should you do first?

Run it in SQLite and check the result against the table

Why should you check an AI’s SQL answer?

AI can make mistakes

Use the Orders table. Which ship mode has the most orders? SELECT ship_mode, COUNT(*) FROM Orders GROUP BY ship_mode;

Standard (7 orders)

Use the Customers table. What number appears next to Central? SELECT region, COUNT(*) FROM Customers GROUP BY region;

5

Use the Orders table. What number does this query return? SELECT SUM(order_total) FROM Orders;

2441.93

What does SUM do?

Adds numbers together

Use the Customers table. In the Customers table, what does each row represent?

One customer

Use the Customers table. Which column tells you the city a customer lives in?

city

Use the Orders table. Which column shows how much money an order was for?

order_total

Which word starts a query and lists the columns you want to see?

SELECT

What does LIMIT 3 do?

Shows only the first 3 rows

In WHERE state = 'TX', why is TX inside single quotes?

Because it is text

What does the semicolon ( ; ) at the end of a query mean?

The query ends here

Use the Customers table. What does this query return? SELECT last_name FROM Customers WHERE first_name = 'Heather';

Castillo

Use the Customers table. What does this query return? SELECT state FROM Customers WHERE city = 'Orlando';

FL

Use the Orders table. What does this query return? SELECT ship_mode FROM Orders WHERE order_id = 'O01036';

Two-Day

Use the Customers table. What number does this query return? SELECT COUNT(*) FROM Customers WHERE segment = 'Corporate';

2

“Show each customer’s first name and city.” Which query answers this?

SELECT first_name, city FROM Customers

“How many customers do we have?” Which query answers this?

SELECT COUNT(*) FROM Customers

“Show only the cancelled orders.” Which query answers this?

SELECT * FROM Orders WHERE order_status = 'Cancelled'

“Show the first 5 orders.” Which query answers this?

SELECT * FROM Orders LIMIT 5

Use the Orders table. A manager asks, “How many orders do we have?” The AI’s query returns 12. Is the AI right?

Yes. The table has 12 orders

Use the Customers table. A manager asks, “Which customers live in California?” The AI’s result shows one row: Charles Bennett, Los Angeles, CA. Is the AI right?

Yes, Charles is the only California customer

You run an AI-written query and SQLite shows an error message. What should you do?

Read the error, ask the AI to fix the query, and run it again

Use the Orders table. Which payment method has the most orders? SELECT payment_method, COUNT(*) FROM Orders GROUP BY payment_method;

Credit Card (5 orders)

Use the Customers table. What number appears next to Consumer? SELECT segment, COUNT(*) FROM Customers GROUP BY segment;

4