190 likes | 271 Views
Chapter 2. Sorting and Restricting the Data. 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. Limiting Rows Using SELECT with a WHERE clause. Restrict the Rows. The SQL Statement.
E N D
Chapter 2 Sorting and Restricting the Data
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
Limiting Rows Using SELECT with a WHERE clause Restrict the Rows Chapter 2 Sorting and Restricting data
The SQL Statement SELECT clientcode, regcode, clientcompanyname FROM client WHERE regcode = ‘SE’; Chapter 2 Sorting and Restricting data
General form using a WHERE clause SELECT [DISTINCT] {*, column [alias],…} FROM table WHERE condition(s); Chapter 2 Sorting and Restricting data
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
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
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
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
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
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
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
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
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
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
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
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
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
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