SQL Transact – WHERE clauseThe natural structure of a table, a spreadsheet or a grid is to have rows and columns. With a large table you may only be interested in specific data. The WHERE transact command is ideal to filter rows which match your criteria. Lesson 1 – Filtering the table to only display UK customers.Situation: You have a table with multi-national customers, buy you want a query which will list only those in the UK. The crucial clause is: — SQL transact statement to filter UK customers from Northwind
USE Northwind SELECT * from customers WHERE country = ‘UK’ GO
Learning PointsNote 1: WHERE just filters rows, you still get all the columns. Note 2: The clause Where looks at country, and only selects those which = ‘UK’ Note 3: We use single quotes ‘UK’, double quotes would lead to an error. See how when the command is entered properly, ‘UK’ turns red in the query analyzer. Lesson 2 – Filtering the table to display only high priced items.— SQL statement to find unit price greater than 20 from productS table
USE Northwind SELECT * from products WHERE unitprice > 20 GO
Learning PointsNote 1: With numeric values no speech marks are needed. Note 2: This filter reduces the rows from about 77 to 37 Other conditions to try.Other conditions you may like to try: NOT, for example is expressed by <> Guy’s Out TakesIf you like a challenge, troubleshoot this transact statement and see if you can correct the error. SQL Transact Out Take 1What is wrong here? — SQL statement to find unit price greater than 20 from product table
USE Northwind SELECT * from product WHERE unitprice > 20 GO
Answer: A tiny spelling error with product. It should be the plural productS SQL Transact Out Take 2What is wrong here? — SQL Statement to find customers who are NOT in the USA.
USE Northwind SELECT * from customers WHERE country not = ‘USA’ GO
Answer: Substitute <> for not = Correction: WHERE country <> ‘USA’
|
|
See also
