Chapter 2

1. Chapter 2 Sorting and Restricting the Data

2. Objectives At the end of this chapter you should be able to: • Limit the rows retrieved by a query • Sort the rows retrieved by a query Chapter 2 Sorting and Restricting data

3. Limiting Rows Using SELECT with a WHERE clause Restrict the Rows Chapter 2 Sorting and Restricting data

4. The SQL Statement SELECT clientcode, regcode, clientcompanyname FROM client WHERE regcode = ‘SE’; Chapter 2 Sorting and Restricting data

5. General form using a WHERE clause SELECT [DISTINCT] {*, column [alias],…} FROM table WHERE condition(s); Chapter 2 Sorting and Restricting data

6. Example SELECT consultlname, clientcompanyname, classcode FROM const_company WHERE constlname = ‘Marian’; CONSTLNAME CLIENTCOMPANYNAME CLASSCODE -------------------- -------------------- ---------- Marian MidWest Bank DBA Marian MidWest Bank DBA Marian MidWest Bank DBA Marian MidWest Bank DBA Marian MidWest Bank DBA Chapter 2 Sorting and Restricting data

7. Character Strings and Dates • Character Strings and Dates are enclosed in single quotation marks. • Character values are case sensitive and date values are format sensitive • The default date display format is DD-MON-YY Chapter 2 Sorting and Restricting data

8. Using Comparison Operators CONSTLNAME CLIENTCOMPANYNAME CLASSCODE CONTHRSWKED -------------------- -------------------- ---------- ----------- George Florida St. PRC 89 Marian MidWest Bank DBA 24 Young Petes Books DBA 16 Young Petes Books DBA 87 SQL> SELECT constlname, clientcompanyname, classcode, conthrswked FROM const_company WHERE conthrswked < 90; Chapter 2 Sorting and Restricting data

9. Using Comparison Operatorscontinued CONSTLNAME CLIENTCOMPANYNAME CLASSCODE CONTHRSWKED -------------------- -------------------- ---------- ----------- George Florida St. PRC 89 Harris NY Library INT 101 Young Petes Books DBA 87 SQL> SELECT constlname, clientcompanyname, classcode, conthrswked FROM const_company WHERE conthrswked BETWEEN 50 and 120; Chapter 2 Sorting and Restricting data

10. Use the LIKE Operator • Use the LIKE Operator to perform wildcard search of valid search strings values. • Search conditions can contain either literal characters or numbers • % denotes zero or many characters • _ denotes one character Chapter 2 Sorting and Restricting data

11. Using Pattern Matching with LIKE CONSTLNAME CLIENTCOMPANYNAME -------------------- -------------------- Wills Rockwell Int Diaz Universal Honda SQL> SELECT DISTINCT constlname, clientcompanyname FROM const_company WHERE constlname LIKE '_i%’; Chapter 2 Sorting and Restricting data

12. Combining LOGICAL and COMPARISION Operators CONSTLNAME CLIENTCOMPANYNAME CONTHRSWKED -------------------- -------------------- ----------- Park Rockwell Int 180 Wills Rockwell Int 190 Wills Rockwell Int 180 SQL> SELECT constlname, clientcompanyname, conthrswked FROM const_company WHERE clientcompanyname LIKE 'Rock%' AND conthrswked >170; Chapter 2 Sorting and Restricting data

13. Combining LOGICAL and COMPARISION Operators CONSTLNAME CLIENTCOMPANYNAME CONTHRSWKED -------------------- -------------------- ----------- Park Rockwell Int 180 Wills Rockwell Int 190 Wills Rockwell Int 180 Diaz Universal Honda 181 Park Universal Honda 190 SQL> SELECT constlname, clientcompanyname, conthrswked FROM const_company WHERE (clientcompanyname like 'Rock%' OR clientcompanyname LIKE 'Univ%') AND conthrswked >170; The parentheses are necessary Chapter 2 Sorting and Restricting data

14. Using an IN selection CONSTLNAME CLASSCODE -------------------- ---------- Diaz NET Marian DBA Park DBA Wills DBA Young DBA SELECT DISTINCT constlname, classcode FROM const_company WHERE classcode IN ('DBA','NET'); Chapter 2 Sorting and Restricting data

15. Using a NOT IN selection CONSTLNAME CLASSCODE -------------------- ---------- George PRC Harris INT Johnson INT Morris PRC Park PRJ SELECT DISTINCT constlname, classcode FROM const_company WHERE classcode NOT IN ('DBA','NET'); Chapter 2 Sorting and Restricting data

16. Sorting the Query: ORDER BY CONSTLNAME CLASSCODE TOTAL_HRS -------------------- ---------- ---------- Diaz NET 1276 George PRC 542 Harris INT 705 Johnson INT 1174 Marian DBA 574 Morris PRC 1084 Park DBA 1292 Park PRJ 1292 Wills DBA 1314 Young DBA 423 SELECT constlname, classcode, total_hrs FROM constclasshrs ORDER BY constlname; Chapter 2 Sorting and Restricting data

17. Sorting the Query: ORDER BY CONSTLNAME CLASSCODE TOTAL_HRS -------------------- ---------- ---------- Wills DBA 1314 Park DBA 1292 Young DBA 423 Marian DBA 574 Johnson INT 1174 Harris INT 705 Diaz NET 1276 Morris PRC 1084 George PRC 542 Park PRJ 1292 SELECT constlname, classcode, total_hrs FROM constclasshrs ORDER BY 2; You can just use the position of the column you want to sort by. Chapter 2 Sorting and Restricting data

18. Sorting the Query: ORDER BY CONSTLNAME CLASSCODE TOTAL_HRS -------------------- ---------- ---------- Wills DBA 1314 Park DBA 1292 Park PRJ 1292 Diaz NET 1276 Johnson INT 1174 Morris PRC 1084 Harris INT 705 Marian DBA 574 George PRC 542 Young DBA 423 SELECT constlname, classcode, total_hrs FROM constclasshrs ORDER BY total_hrs DESC; You can specify ascending or descending. Chapter 2 Sorting and Restricting data

19. Chapter 2 Summary SELECT [DISTINCT] {*, column [alias],…} FROM table WHERE condition(s) ORDER BY{column, expr, alias,…}|[ASC|DESC]; Chapter 2 Sorting and Restricting data