SQL SELECT: Choosing Rows and Columns with WHERE

Hello students, welcome to Kwickprep. Your school has marks of every student stored in a table. How do you find only the top scorers of section A in one line? Today we will learn select, where, the logical operators and, or and not, and two neat tricks called alias and distinct.

All our examples use one table called student. It has five columns: roll, name, section, marks and city. Kabir was absent for the test, so his marks are NULL. NULL means no value at all. It is not zero, and it is not a blank space.

A query is a question we ask the database. Select tells the database which columns we want. From tells it which table to look in. Here we ask for name and marks from student, separated by a comma. Every row comes back, but only these two columns. A semicolon ends the query.

The star sign is a short way to say all columns. Select star from student shows the whole table, all five columns and all five rows. It is handy for a quick look. In real work, name only the columns you need, because big tables have many columns.

Before we filter rows, learn four writing rules. Keywords like select and from can be in capitals or small letters, but capitals are easier to read. Text values always go in single quotes. Numbers are written without quotes. And each query ends with a semicolon.

Where picks rows using a condition, and a condition uses relational operators. One equals sign means equal to, and in SQL it compares, it does not store. Less than followed by greater than means not equal to, and exclamation equals also works in MySQL. Greater than and less than compare sizes. Greater than or equal to and less than or equal to also accept equal values.

Now we add a where clause. A clause is one part of a query. Where marks greater than seventy keeps only rows where the condition is true. Riya, Aman and Meera pass the check. Notice that Kabir does not appear. His marks are NULL, and NULL is never greater than seventy.

Where works with text too. City equals Surat, with Surat in single quotes, finds students from Surat. The answer is Riya and Neha. Pause and predict. What if we wrote marks greater than or equal to seventy two in the last query? The same three names, because Aman has exactly seventy two.

To join two conditions, we use logical operators. And keeps a row only when both conditions are true. Or keeps a row when at least one condition is true. Not flips a condition, so true becomes false and false becomes true.

Let us find section A students who scored above eighty. Neha is in section A, but sixty five is not above eighty, so she is out. Aman scored well, but he is in section B. Only Riya and Meera pass both checks.

With or, one true condition is enough. Kabir lives in Delhi, so he is kept, even without marks. Meera scored ninety one, so she is kept too. Similarly, where not city equals Surat would give Aman, Kabir and Meera. When you mix and with or, and is checked first, so use brackets to be clear.

An alias is a temporary new name for a column in the output. We write it after the word as. Here, the name column shows the heading Student. We can even do arithmetic, like marks plus five, and call it Bonus. The table itself does not change. The alias lives only in this result.

Five students live in only three cities. Select city would show Surat and Pune twice. Distinct removes the repeated values, so each city appears only once. Write distinct straight after select.

Exams often test these mistakes, so watch for them. Text without quotes gives an error, because SQL thinks Surat is a column name. Double equals is Python, while SQL uses one equals sign. Marks equals NULL never finds a row, and we will learn is NULL in the next lesson. Distinct must come right after select, not after the column.

Let us revise what we learned today. Select and from choose the columns and the table. Where keeps only the rows where the condition is true. The relational operators are equals, not equal, greater than, less than and their or equal forms. And needs both conditions, or needs either one, and not flips a condition. As gives a column a new name, and distinct removes repeated values. Practise each query on your own table and predict the output first.