Your first query: SELECT, WHERE and ORDER BY on the orders table

Your data is too big for Excel. Picture a Monday morning. Your manager asks for every shipped order from the online shop, sorted with the biggest ones on top. You open last month's export and it has four hundred thousand rows. Excel takes a long time to open it, the filter menu freezes, and you still have to sort and copy the result into a clean sheet. There's a calmer way to do this. The orders don't have to live in a spreadsheet at all. They can sit in a database, and you can ask it a question in a short, readable language called SQL. SQL usually answers in a moment, and it gives the same answer every time you run it. In this lesson you'll write that exact query yourself.

What you will learn. By the end of this lesson you'll be able to do three things. First, you'll use SELECT to choose which columns appear in your answer. It works like hiding the columns you don't need in Excel. Second, you'll use WHERE to keep only the rows that match a rule, just like the filter arrows on a header row. Third, you'll use ORDER BY to sort the result, the same job as the Sort button. Those three words cover a large share of the everyday questions analysts ask. You already know the ideas from Excel. Here you're only learning new names for them.

Meet SQLite. A few words first. A database is a place where tables are stored together. A table is like one Excel sheet, except every column has a fixed name and every row follows the same layout. A query is a question you write in SQL, and the database answers it with a table of rows. In this course we use SQLite. It's free and it's tiny. The whole database is a single file on your computer, so there's no server to set up and no one from IT to ask. It's also built into Python, into phones, and into many apps you already use.

The orders table. Here's the table we'll query today. It's called orders, and each row is one order placed in the online shop. The id column is the order number. Order date is the day the customer paid. Customer is the buyer's first name. Amount is the order total in dollars. Status says where the order is now: shipped, pending, or cancelled. The real shop has hundreds of thousands of rows, but we'll use just eight, shown here, so you can check every answer by eye. Keep this picture in mind. Every query we write only reads from this table. It never changes the data.

SELECT picks columns. Here's your first query. It says: select customer and amount from orders. FROM tells SQLite which table to read. SELECT lists the columns you want back, separated by commas, and they come out in the order you wrote them. The semicolon at the end marks where the query stops. If you want every column, write SELECT, then a star, then FROM orders. The star means all columns. That's handy for a quick look, but naming the columns keeps your result tidy. SQL also doesn't care about capital letters in its keywords. Writing them in capitals is simply a habit that makes queries easier to read.

WHERE filters rows. Now let's keep only some of the rows. It works like the filter arrow in Excel. WHERE comes after FROM and states a rule. Only rows where the rule is true come back. Here the rule is status equals shipped, so pending and cancelled orders drop out. Notice the single quotes around shipped. That's how SQL knows it's a piece of text and not a column name. For numbers you don't need quotes. You can write amount greater than fifty, or amount less than or equal to twenty. And you can stack rules. Status equals shipped AND amount greater than fifty needs both rules to be true, while OR only needs one of them.

ORDER BY sorts the result. The last piece is sorting. ORDER BY names the column to sort on. By default SQL sorts in ascending order, which means smallest first, or A to Z for text. Add the word DESC, short for descending, and the order flips so the largest amount comes first. You can also sort on more than one column. For example, ORDER BY customer, then a comma, then amount DESC groups each customer's orders together, with their biggest order on top. One more thing to know: without ORDER BY, SQL promises no order at all. If order matters to your report, always ask for it.

Worked example: all three together. Let's answer the manager's question. Here are the eight orders. Now we type the full query: select id, customer and amount from orders, where status equals shipped, order by amount descending. Watch the order in which SQLite actually works, because it's not the order we wrote it in. First it reads the table named in FROM. Then WHERE runs, and only shipped orders survive, so Ben's and Dina's go away. Next SELECT runs, and we keep three columns. Last, ORDER BY puts the biggest amount on top. And five rows come back, with Omar's two hundred and ten dollar order leading the list.

Try it from Python. You don't need Python for SQL, but if you have it, SQLite is already built in, so this is a quick way to practise. The first lines create an in-memory database, which is a database that lives only while the script runs. Then we make the orders table and load our eight rows into it. The query in the middle is pure SQL. This time we ask for every order over fifty dollars, whatever its status, largest first. Can you predict the result before looking? Pause the video and try. Here's the answer: Omar, Ben, Sara, Dina, then Ana at sixty. Pending and cancelled orders are included here because this time we didn't filter on status.

Common mistake: text without quotes. Here's the mistake almost everyone makes in their first week. You write where status equals shipped and leave out the quotes. SQLite reads the bare word shipped as the name of a column, can't find one, and stops with the error "no such column: shipped". The fix is to put single quotes around any text value, like shipped, Cairo, or a date such as twenty twenty-six, zero one, zero three. Numbers never need quotes. A second, related habit is to watch the spelling and capitals inside the quotes. For text comparisons, Shipped with a capital S isn't the same as lowercase shipped, so match the data exactly.

Recap. Let's wrap up. SELECT chooses which columns you see, and FROM tells SQLite which table to read. WHERE keeps only the rows that match your rule. Text goes in single quotes, and you can combine rules with AND and OR. ORDER BY sorts the result. Ascending is the default, and DESC puts the largest values first. You write the keywords in the order SELECT, FROM, WHERE, ORDER BY, even though SQLite filters before it picks columns. With just these three words you can already answer a lot of the questions that used to take you a filter, a sort, and a copy and paste in Excel.

Your exercise. Now it's your turn. Using the same eight-row orders table, write one query that shows the order date, customer and amount for pending orders only, with the newest order first. Here's a hint: dates written as year, month, day sort correctly as text, so ORDER BY order date with DESC works. You can run it in the Python script from this lesson or in any SQLite tool. When it works, you should get two rows, with Ben's order from the eighth of January on top. In the next lesson we'll summarise orders the way a pivot table does, with GROUP BY.