1 / 66

Information Retrieval Using SQL

Information Retrieval Using SQL. Structured Query Language. SQL (Structured Query Language). In 1986, SQL was accepted by ANSI (the American National Standards Institute) as the national standard query language for relational databases .

howie
Télécharger la présentation

Information Retrieval Using SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Information RetrievalUsing SQL Structured Query Language

  2. SQL (Structured Query Language) • In 1986, SQL was accepted by ANSI (the American National Standards Institute) as the national standard query language for relational databases. • Since that time, a number of microcomputer implementations have become available. • Microcomputer versions of software that originally ran on minicomputers (for example, Oracle, Informix, Ingres) . • Implementations that were written directly for a microcomputer (Examples: dBASE and. R:BASE,)

  3. Virtual Tables • SQL views are implemented as virtual tables • only view definitions are stored on disk (in the data dictionary); • view tables are created only when referenced.

  4. The SQL SELECT Command • SQL supports a very powerful command, SELECT, which is used to locate and retrieve data. • The SQL SELECT is not the same as the select operation from the relational algebra • while the relational select retrieves all columns of one or more rows from asingle table, • the SQL SELECT command • can retrieve from multiple tables (using a join), • can project specific columns from the result, and • can perform some computations. • In fact, in many SQL implementations, SELECT can perform all nine of the relational algebra operations described before.

  5. The SQL SELECT Command • SELECT is a non-procedural commandin that • you specify what you want the SQL command processor to do • but not exactly how it should perform the query. SQL figures out on its own the order in which it should perform those operations. • Every DBMS that supports SQL has program code that functions as a SQL command processor. • Its job is to perform the relational algebra operations needed to obtain the result of the query. • It determines the most efficient way to perform relational algebra operations (a query optimizer.)

  6. The SELECT Format • The general form of the SELECT command has a number of options: SELECT [DISTINCT] column_namel [,column_name2]... FROM table_name [, table_name2]... [WHERE predicate] [ORDER BY column_name1 [,column_name2]... [GROUP BY column_name1 [,column_name2]... [HAVING predicate] • To make multiple lines possible, each SQL implementation has some way of letting you indicate that a command is ready to be processed (example: semicolon in Oracle).

  7. Simple SQL Retrieval • In its simplest form, SELECT must specify which columns should be included in the output and the table from which they should come. • The list of columns to be included in the result can specify all columns in the table • by using the wild card, an asterisk (*) • or can contain the names of the columns. • Example (Figure 8.1): SELECT * FROM Shift_Driven

  8. Figure 8.1 SOL SELECT command—example 1 SELECT * FROM Shift_Driven

  9. Simple SQL Retrieval • Instead of displaying every column in a table with *, specify exactly which columns you want to see by • including the column names immediately after SELECT. • For example (Figure 8.2) SELECT Shift_Name, WkDate FROM Shift_Drivcn

  10. SELECT ShiftJName, WkDate FROM Shift_Driven Figure 8.2 SQL SELECT command—example 2

  11. Simple SQL Retrieval • SELECT can be instructed to return only unique rows by adding the keyword DISTINCT to the query. • Example (Figure 8.3) SELECT DISTINCT Shift_Name, WkDate FROM Shift_Driven • After sorting rows by every column in the table, the SQL processor scans the result row by row, looking for adjacent, identical rows to remove them. • Using DISTINCT, the result tables are sorted in the order in which the columns were listed after the SELECT keyword in the query.

  12. SELECT DISTINCT Shift Name, WkDate FROM Shift Driven Figure 8.3 SQL SELECT command — example 3

  13. Simple SQL Retrieval • The order of rows in the virtual table produced by a SELECT can bechanged before the result table is displayed on the screen by using the ORDER BYclause. • Example (Figure 8.4) SELECT DISTINCT' Shift_Name, WkDate FROM Shift_Driven ORDER BY WkDate, Shift_Name • The rows appear in the order in which they are given after ORDER BY.

  14. SELECT DISTINCT Shift JSame, WkDate FROM Shift_Driven ORDER BY WkDate, Shift Name Figure 8.4 SQL Select command—example 4

  15. Simple SQL Retrieval • Output can also be sorted in descending order by using the keyword DESC after any attribute that is to be sorted in that manner. • Example, to produce Figure 8.5 in descending order by date the query might be written; SELECT DISTINCT Shift_Name, WkDate FROM Shift_Driven ORDER BY WkDate DESC, Shift_Name

  16. SLECT DISTINCT Shift_Name, WkDate FROMShift_Driven ORDER BY WkDate DESC, Shift Name :Figure 8.5 SQL Select command—example 5

  17. Restricting Rows with WHERE

  18. The WHERE clause • Criteria for retrieving specific rows from one or more tables can be included in a SELECT command by using a WHERE clause. • The keyword is followed by an expression(known as apredicate) that identifies which rows should be retrieved. • Predicates ask a SQL command processor to perform the relational algebra operations • select, • join, and • difference

  19. Simple WHERE Clauses • The simplest form of the WHERE clause asks a SQL command processor to perform a relational algebra select. • It contains a logical expression against which rows are evaluated. Logical operators include: <, <=, =, >=, >, AND, OR, and NOT.Parentheses () are used as well. • Example (Figure 8.6), to see all the shifts reserved for driver number 10: SELECT Shift_Name, WkDate FROM Shif_Driven WHERE Driver# = 10

  20. SELECT Shift_Name, WkDate FROM Shift_Driven WHERE Driver# = 10 ShiWkDate day11/15/94 day11/16/94 day11/17/94 day11/18/94 Figure 8.6 SQL SELECT command — example 6

  21. Joining Tables • The WHERE clause is used to request a join. • Example: Suppose that an office worker doesn't remember a driver number, but only has the driver’s name? • SQL must be told to join the Driver and Shift Driven tables over the driver number (i.e., following the primary – Foreign keys relationship). • The query might be issued as: SELECT Shift_Name, WkDate FROM Driver, Shift_Driven WHERE Driver_Name= 'Zilog, Charlie1 AND Driver.Driver# = Shift_Driven.Driver #

  22. Joining Tables • As well as joining two different tables, a WHERE clause can be used to join a table to itself. • Example (Figure 8.7): This technique might be used to find the ones of all drivers that have driven or are scheduled to drive two specific cabs. • The SQL is written (T1 and T2 are aliases of Shift_Driven): SELECT DISTINCT Driver_Name FROM Driver, Shift_DrivenTl, Shift_Driven T2 WHERE Driver.Driver# = Tl .Driver# AND T1.Driver# = T2.Driver# AND T1.Cab# = ‘104' ANDT2.Cab# = '108'

  23. SELECT DISTINCT Driver_Name FROM Driver, Shift_DrivenTl, Shift_Driven T2 WHERE Driver.Driver# = Tl.Driver# AND Tl.Driver# = T2.Driver# AND T1.Cab# = '104' AND T2.Cab# = '108' Driver_Name Bailey, Max Figure 8.7 SQL SELECT command—example 7

  24. Special WHERE Clause Operators • Predicatesin a WHERE clause may contain additional operators: • IN and NOT IN (used to specify a set of values), • LIKE and NOT LIKE (used for pattern matching), • BETWEEN and NQT BETWEEN (used to specify a range), and • IS NULL and IS NOT NULL. • Example (Figure 8.8): to retrieve data about the two cabs with license '345 YAO' and '111 ABC',the SQL query could be written: SELECT Cab#, Make_Model, Year, Curr_Odom FROM Cab WHERE Plate IN ('345 YAO‘, '111 ABC')

  25. SELECT Cab#, Make_Model, Year, Curr_Odom FROM Cab WHERE Plate IN ('345 YAO‘, '111 ABC') CabMake_Model 1YeCurr_O 002 Checker sedan 83 0 105 Checker sedan 73 286003 Figure 8.8 SQL SELECT command—example 8 Note: The same predicate could have been written as: Plate = '345 YAO' OR Plate = '111 ABC'

  26. Special WHERE Clause Operators • NOT IN is a shorthand for a series of negative AND expressions containing the same attribute. • Example (Figure 8.9): Suppose that FTC's chief mechanic needs to see data on all cabs except cabs numbered 006, 108, and 378. That datacan be retrieved with: SELECT Cab#, Make_Model, Plate, Curr_Odom FROM Cab WHERE Cab# NOT IN ('006', '108', '378')

  27. SELECT Cab#, Make_Model, Plate, Curr_0dom FROM Cab WHERE Cab# NOT IN ('006','108','378') The NOT IN could be rewritten as a series of ANDs or a negated OR: Cab# != '006' AND Cab# != '108' AND Cab# != '378‘ • NOT (Cab# - '006' OR Cab# = '108' OR Cab# = '378') Figure 8.9 SQL SELECT command—example 9

  28. Special WHERE Clause Operators • BETWEEN and NOT BETWEEN can also be used as shorthand in combinations of standard logical operators. • Example (Figure 8.10): To retrieve the names of all drivers who are scheduled to drive the day shift over a three-day period, a SQL might be written: SELECT Dtiver_Name FROM Driver, Shift_Driven WHERE Driver.Drivert# = Shift_Driven.Driver# AND Shift_Name = 'day’ AND WkDate BETWEEN 11/15/94 AND 11/17/94

  29. SELECT DISTINCT Driver^Name FROM Driver, Shift_Driven WHERE Driver.Driver/ = shift_Driven.Driver# AND Shift_Name = 'day' AND WkDate BETWEEN 11/15/94 AND 11/17/94 Abelman, John Bailey, Max Baker, Mary Ann lastman, Rich Erlich, Martin Killer, Phyllis Phong, Quen Santiago, Jorge Tnieu, Lin Wong, David Zilog, Charlie Figure 8.10 SQL SELECT command—example 10 The predicate can also be written as two logical inequalities linked with AND: WkDate >= 11/15/94 AND WkDate <= 11/17/94

  30. Special WHERE Clause Operators • In a similar manner, a list of all drivers not scheduled to drive during three-day period can be obtained by simply negating the BETWEEN operator. (Figure 8.11) SELECT Driver_Name FROM Driver, Shift_Driven WHERE Driver.Driver# = Shift,Driven. Driver# AND Shift_Name = 'day' AND WkDate NOT BETWEEN 11/15/94 AND 11/17/94

  31. SELECT DISTINCT Driver_Name FROM Driver, Shift_Driven WHERE Driver. Driver* = Shift_Driven.# AND Shift_Name = 'day' AND WkDate NOT BETWEEN 11/15/94 AND 11/17/94 • Bailey, Max • Baker, Mary Ann • Erlich, Martin • Lewis, John • Miller, Phyllis • Santiago, Jorge • Thieu, Lin Vin • Wong, David • Zilog, Charlie • Figure 8.11 SQL SELECT command—example 11 Can be rewritten to obtain the same results as either: WkDate < 11/J 5/94OR WkDate > 1 I/I7/94 Or: • NOT (WkDate < 11/15/94 AND WkDate < 11/17/94)

  32. Special WHERE Clause Operators • The operators LIKE and NOT LIKE allow wild-cards to be used as part of character constants in logical expressions. • SQL supports two wild-card characters: % (for missing characters) and _ (underscore, for a single character). • Example (Figure 8.12): If FTC mechanic wants to see information about all cabs that have received new parts, he might use the SQL query: SELECT Cab#, Maint_Type, Maim_Date FROM Maint_Perf WHERE Maint_Type LIKE 'new%*

  33. SELECT Cab#, Maint_Type, Maint_Date FROM Maint_Perf WERE Maint_Type LIKE 'new%' • CabMaint_TypeMaint_Pa • 002 new engine9/18/94 • 404 new upholstery 10/12/94 • 404 new windshield10/12/94 • Figure 8.12 SOL SELECT command—example 12

  34. Special WHERE Clause Operators • To retrieve information about all cabs whose maintenance involved something other than new equipment, the query would be written (Figure 8.13): SELECT Cab#, Maint_Type, Mainr_Dare FROM Maint_Perf WHERE Maint_TypeNOT LIKE ‘new%’

  35. SELECT Cab#, Maint_Type, Maint_Date FROM Maint_Perf WHERE Maint_Type NOT LIKE 'new%‘ • Cab Maint_TypeMaint_Da • 002 tune_up 9/18/94 • 238 tune_up 9/28/94 • 378 wheel bearings 9/28/94 • 104 tune_up 10/12/94 • 215 tune_up 10/12/94 • 404 tune_up 10/12/94 • 006 tune_up 10/15/94 • 108 tune_up 11/2/94 • 045 tune_up 11/14/94 • 105 tune_up 11/15/94 • 378 tune_up 11/15/94 • 144 inspect damage 11/15/94 Figure 8.13 SQL SELECT command—example 13

  36. Special WHERE Clause Operators • The IS NULL and IS NOT NULL operators are fairly straightforward. • For example, if FTC's office clerk needs to see the names of all drivers for whom there is no phone number, the query might be written: SELECT Driver_Name FROM Driver WHERE Driver_phone IS NULL

  37. Summary Queries • SELECT can perform a number of summary functions on data, returning grouped information. • Example (Figure 8.14): a table reporting the total number of miles driven in each day and shift contained in Shift Driven, can be obtained by using: SELECT WkDate, Shift_Name, SUM(End_Odom -Start_Odom) FROM Shift_Driven GROUP BY WkDate, Shift_Name

  38. ELECT WkDate, Shift_Name, SUM(End_Odom - Start_0dom) FROM Shift_Driven GROUP BY WkDate, Shift_Same Figure 8.14 SOL SELECT command—example 14

  39. Summary Queries • Most SQL implementations provide several useful functions: • AVG (computes the average value of each group) • MAX (returns the maximum value in each group) • M1N (returns the minimum value in each group) • COUNT (returns the number of members in the group)

  40. Summary Queries • The table in Figure 8.14 contains some undesirable data, those zeros that correspond to rows for future reservations. To make the result more meaningful we might wish to exclude all shifts that haven't been driven: SELECT WkDate, ShifLName, SUM(End_Odom - Srart_Odom) FROM Shift_Driven WHERE WkFlag = 'T' GROUP BY WkDate, Shift_Name • The addition of the WHERE clause restricts the rows to those with a WkFIag producing the more meaningful table that appears in Figure 8.15.

  41. ELECT WkDate, Shift_Name, SUM(End_Odom - Start_0dom) FROM Shift_Driven • WHERE WkFlag = 'T' GROUP BY WkDate, Shift_Name Figure 8.15 SOL SELECT command—example 15

  42. Summary Queries • The HAVING clause can be used to restrict which groups are included final table. • For example (Figure 8.16), to report the average mileage for only the day and evening shifts: SELECT WkDate, Shift_Name, AVG(End_Odom - Start_Odom) FROM Shift_Driven GROUP BY WkDate, Shift_Name HAVING Shift_Name != 'ngt'

  43. SELECT WkDate, Shift_Name, AVG(End_Odom - Start_Odom) FROM Shift_Driven GROUP BY WkDate, Shift_Name HAVING Shift_Name != 'ngt' Figure 8.16 SQL SELECT command—example 16

  44. SELECT WkDate, Shift_Name, AVG(End_Odom - Start_Odom) FROM Shift_Driven WHERE WkFlag = ‘T’ GROUP BY WkDate, Shift_Name HAVING Shift_Name != 'ngt' Figure 8.17 SQL SELECT command—example 17

  45. Subqueries • A subquery isa complete SELECT command used as part of a WHERE clause predicate. • Use: increasing efficiency or the only way. • There are two major types of subqueries: • Uncorrelated subqueries are typically used to increase the efficiency of queries that could be expressed in another way. • Correlated subqueries provide the only way to obtain a particular type of result that can't be obtained any other way.

  46. Subqueries • An uncorrelated subquery is evaluated completely before the SQL command processor proceeds to the remainder of the query. • The subquery table is a temporary table and is not played or stored in main memory for the user. • ANY is • a special operator used with subqueries; it can be paired with any of the standard logical operators.

  47. Subqueries • For example, a query to retrieve the cab and license plate numbers of all cabs made before 1980 could written: SELECT Cab#, Plate FROM Cab WHERE Year = ANY (SELECT Year FROM Cab WHERE Year < '80') • The resultof the subquery is (Figure 8.18) • a table containing the year of manufacture of each cab that was made before 1980.

  48. SELECT Cab#, Plate FROM Cab WHERE Year = ANY (SELECT Year FROM Cab WHERE Year < '80') CabPlate 104 356 QLT 105 111 ABC 378771 TOW Figure 8.18 SQL SELECT command—example 18

  49. Subqueries • ANY, used in the this example, is equivalent to IN. • This is true as long only one attribute is being compared. • If more than one attribute is involved, operator must be IN. • Rows will be included in the final result • only if their year of manufacture is equal to any of the entries in the table returned by the subquery.

  50. Subqueries • A subquery isn't limited to the same table as the outer query. • When a subquery is based on a different table than the outer query, it can take the place of a join. • For example (Figure 8.19, to see the names of all drivers who were scheduled to drive on November 15, 1994, a query could be written as: SELECT Driver_Name FROM Driver WHERE Driver# IN (SELECT Driver# FROM Shift_Driven WHERE WkDate = 11/15/94)

More Related