SQL 2000 Transact – WHERE clause

SQL Transact – WHERE clause

The 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:
WHERE country = ‘UK’.

— SQL transact statement to filter UK customers from Northwind

 

USE Northwind

SELECT * from customers

WHERE country = ‘UK’

GO

 

Learning Points

Note 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 Points

Note 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 <>
Where country <> ‘USA’


Guy’s Out Takes

If you like a challenge, troubleshoot this transact statement and see if you can correct the error.

SQL Transact Out Take 1

What 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 2

What 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