Selecting Rows from Tables

1. Selecting Rows from Tables Now that we know how to specify columns, how do we specify rows in a SELECT statement?

2. What we know so far • SELECTING COLUMNS • ORDERING ROWS • SELECTING UNIQUE VALUES

3. SELECTING COLUMNS • SELECT * FROM table_name; • Select ALL columns • SELECT column_name [, column_name …] FROM table_name; • Select SOME columns

4. ORDERING ROWS • SELECT * FROM table_name ORDER BY column_name [DESC] [, column_name [DESC]…]; • SELECT column_name [, column_name …] FROM table_name ORDER BY column_number [DESC] [, column_number [DESC] …];

5. SELECTING UNIQUE VALUES • SELECT DISTINCT column_name FROM table_name • Select unique values from a column • SELECT COUNT(*) FROM table_name; • Count number of rows in a table.

6. ROWS • Now that we can determine which COLUMNS appear in a query, and the ORDER of the ROWS, how do we specify WHICH ROWS appear?

7. The ‘WHERE’ clause • The ‘WHERE’ clause appears after the specification of the table name: • SELECT column_specification FROM table_name where_clause [ORDER BY …]; • The WHERE clause looks at EACH ROW to determine if it will be output. • The specified condition must evaluate to TRUE in order for the ROW to appear. • Only the Columns specified for the Row are output.

8. How to define the WHERE condition • Compare columns to values: • WHERE column1 > 10 • WHERE Status = ‘VALID’ • WHERE NOT Name = ‘Smith’ • Compare columns to one another • WHERE column1 > column2 • WHERE maxSalary = currentSalary

9. For more complicated conditions, use OR and AND. • WHERE LastName = ‘Smith’ AND FirstName = ‘James’ • WHERE LastName = ‘Smith’ OR LastName = ‘Bernardo’

10. To USE more than one AND or OR: • Use Parentheses • WHERE FirstName = ‘James’ AND ( LastName = ‘Monroe’ OR LastName = ‘Madison’ )

11. Putting it all together • Decide on the columns (and order of columns) you will output. • SELECT column_specification FROM table_name WHERE where_condition ORDER BY order_specification; • Determine the condition for including rows. • SELECT column_specification FROM table_name WHERE where_condition ORDER BY order_specification; • Specify the order of the included rows. • SELECT column_specification FROM table_name WHERE where_condition ORDER BY order_specification;

12. Exercises • Use this table, which we will call “Electives”

13. Exercise 1 Select all records from the table where there are seats left. SELECT * FROM Electives WHERE __________;

14. Exercise 2 Select all records where there are seats left, and the room is either 516 or 522. SELECT * FROM Electives WHERE ________

15. Exercise 3 Select the room numbers and meeting times of courses that are not full (have seats left) SELECT _________ FROM Electives WHERE ___________________

16. Exercise 4 Select the coursenames that either ‘Dinallo’ teaches and order them by room number. SELECT coursename FROM Electives WHERE _______________ ORDER BY ______________

17. Exercise 5 • Select the courses and seats left that are not on the ‘third’ floor (e.g. room is not 300-399) ordered by number of seats left, with the most seats left listed first.