lecture_8.pdf

STRUCTURED QUERY LANGUAGE (SQL): SQL is a transform-oriented and non-procedure language designed to use relations to transform inputs into required outputs. SQL has two major components  A Data Definition Language (DDL) for defining the database structure  A Data Manipulation Langu...

STRUCTURED QUERY LANGUAGE (SQL): SQL is a transform-oriented and non-procedure language designed to use relations to transform inputs into required outputs. SQL has two major components  A Data Definition Language (DDL) for defining the database structure  A Data Manipulation Language (DML) for retrieving and updating data SQL contains only these definitional and manipulative commands. It does not contain flow control commands. There is no IF... THEN … ELSE, GOTO, WHILE … DO or other commands to provide a flow control. Due to this lock of computational completeness, SQL can be used in two ways:  Use SQL interactively by entering the statements at the terminal.  Embed SQL statements in a procedural language. SQL DML Statements Data manipulation statements in SQL are  SELECT To query data in the database  INSERT To insert data into a table  UPDATE To update data in table  DELETE To delete data from table Example: Consider the following DB tables that represent the stock control management system Stock Item Relation Each stock item is represented by its stock code, stock name, minimum stock level, sale price and amount in hand Sales Relation There are four branches of the company that sells the commonly defined product. The relation for sales includes date, branch numbers, stock code and quantity sold. Branches Relation The four branch of the firm involved in sales are listed with branch code, branch name, and town. Stock Items: Stock Stock Min Stock Sale Stock in Code Name Level Price Hand MON 15 15” Monitor 4 $150 3 KEY 04 Keyboard 12 $20 28 DIS 20 20 GB Disk 6 $90 14 MB 100 Main Board 2 $300 1 CD 52 52×CD Rom 5 $60 7 MS 10 Mouse 24 $9 42 CDWR 700 MB CD 100 $0.8 245 SC 64 64 MB VGA 3 $50 2 Sales Relation: Branch- Stock Quantity Date Number Code Sold 1/12/03 B2 MB 100 1 1/12/03 B3 CD 52 1 1/12/03 B2 CDWR 5 1/12/03 B1 CDWR 10 1/12/03 B4 CDWR 2 1/12/03 B1 KEY 04 1 1/12/03 B2 MS 10 1 2/12/03 B2 CD 52 1 2/12/03 B4 DIS 20 1 2/12/03 B1 SC 64 1 2/12/03 B3 MON 15 1 2/12/03 B4 CD 52 1 2/12/03 B1 CDWR 12 2/12/03 B2 MB 100 1 3/12/03 B1 CDWR 5 3/12/03 B2 CDWR 2 3/12/03 B4 CDWR 4 3/12/03 B3 CDWR 2 3/12/03 B3 KEY 04 1 3/12/03 B2 KEY 04 2 3/12/03 B2 MS 10 3 3/12/03 B1 CD 52 1 B2 KEY 04 1 Branch Relation: Branch-Code Branch-Name Town B1 Shopping Center Branch Nicosia B2 Kyrenia Str. Branch Nicosia B3 Famagusta Branch Famagusta B4 Harbor Branch Kyrenia The SQL statements will use the above tables for the course. The SELECT Statement The purpose of the SELECT statement is to retrieve and display data from one or more database tables. It is extremely powerful command capable of performing the equivalent of the relational algebra‟s selection, projection and join in a single statement. The general format of the SELECT statement is: SELECT [DISTENCT/ALL] {*/[Column – Expression]} FROM Table name(s) [WHERE condition] [GROUP BY Column – list][HAVING Condition] [ORDER BY Column – list] SELECT Specifies which columns are to appear in the output FROM Specifies table(s) to be used WHERE Filters the rows subject to some conditions GROUP BY Forms groups of rows with the same column value HAVING Filters the groups subject to some conditions ORDER BY Specifies the order of the output The order of the clauses in the SELECT statement cannot be changed. The only mandatory clauses are SELECT and FROM, the remainders are optional Considering the stock control DB, the SELECT statement can be used to retrieve data as shown below. i) Retrieve all columns. All rows in the stocks SELECT Stock-Code, Stock-Name, Stock-Min-Level, Stock-Price, Stock-Quantity FROM Stock ; OR SELECT * FROM Stock ; The result is Stock Stock Stock Min Sale Stock in Code Name Level Price Hand MON 15 15” Monitor 4 $150 3 KEY 04 Keyboard 12 $20 28 DIS 20 20 GB Disk 6 $90 14 MB 100 Main Board 2 $300 1 CD 52 52×CD Rom 5 $60 7 MS 10 Mouse 24 $9 42 CDWR 700 MB CD 100 $0.8 245 SC 64 64 MB VGA 3 $50 2  ii) Retrieve specific columns. All rows in stocks Example: List all rows of Stocks by Stock-Name, Stock-Price SELECT Stock-Name, Stock-Price FROM Stock ; The result is: Stock Stock- Name Price 15” Monitor $150 Keyboard $20 20 GB Disk $90 Main Board $300 52×CD Rom $60 Mouse $9 700 MB CD $0.8 64 MB VGA $50 iii) Retrieve specific columns. Unique rows in sales Example: List all Branch-Number in sales SELECT DISTINCT Branch-Number FROM Stock ; The result is: Branch- Number B2 B3 B1 B4 iv) Retrieve all columns. All rows with calculated fields Example: List all the stocks with current value in hand; list Stock-Code, Stock- Value SELECT Stock-Code, Stock-Price * Stock-Quantity FROM Stocks ; The result is: Stock Col 2 Code MON 15 $450 KEY 04 $560 : : : : SC 64 $100 The row selection: Very often certain search condition is imposed on the rows to restrict the selection process. WHERE clause is used for setting up a search condition. There are 5 basic search conditions:  Comparison Compare the value of one expression to the value of another expression  Range Test whether the value of an expression falls within a specified range of value  Set Membership Test whether the value of an expression equals one of a set of values  Pattern Match Test whether a string matches a specified pattern  Null Tests whether a column has a Null (Unknown) value  v) Compression. Search condition Example: List all stocks whose price is greater than $90 by Stock-Code, Stock- Name, Stock-Price SELECT Stock-code, Stock-Name, Stock-Price FROM Stock WHERE Stock-Price > 90 ; The result is: Stock- Stock- Stock- Code Name Price MON 15 15” Monitor $150 MB 100 Main Board $300 In conditional statements the following logical operators are used = Equals < Less than > Greater than ≤ Less than or equal ≥ Greater than or equal Not equal to More complex predicates can be generated using AND, OR and NOT. The rule of evaluation such conditional expressions are as follow.  An expression is evaluated left to right  Expression inserted in brackets are evaluated first  NOTs are evaluated before ANDs and Ors  ANDs are evaluated before ORs  vi) Compound comparison. Search condition Example: List all sales of 64 MB VGA or Keyboard by Sales-Date, Branch- Number, Stock-Code SELECT Sales-Date, Branch-Number, Stock-Code FROM Sales WHERE Stock-Code = “SC 64” OR Stock-Code = “KEY 04” ; The result is: Branch- Stock Date Number Code 1/12/03 B1 KEY 04 1/12/03 B1 SC 64 3/12/03 B3 KEY 04 3/12/03 B2 KEY 04 vii) Range search condition. (BETWEEN / NOT BETWEEN) Example: List all stocks whose Stock-Quantity is between 5 and 50 by Stock-Code, Stock-Quantity. SELECT Stock-Code, Stock-Quantity FROM Stocks WHERE Stock-Quantity BETWEEN 5 AND 50 ; The result is: Stock- Stock- Code Sold KEY 04 28 DIS 20 14 CD 52 7 MS 10 42 viii) Set membership search condition. (IN / NOT IN) Example:  List Branch-Name in Nicosia and Kyrenia SELECT Branch-Name, Branch-Town From Branch WHERE Branch-Town IN (“Nicosia”, “Kyrenia”) ; The result is: Branch-Name Branch-Town Shopping Center Branch Nicosia Kyrenia Str. Branch Nicosia Harbor Branch Kyrenia ix) Pattern match search condition. (LIKE / NOT LIKE). In pattern match search condition, the string to be searched for can be any portion taken from any character position with any length between 1 & n (n is the length of the string to search for). % sign is used for a wild character and underscore ( _ ) is used for a single character For example: Address LIKE „H%‟ means the first character must be “H” but the rest of the string can be any thing Address LIKE „H_ _ _ _‟ means that there must be exactly 4 characters in the string. First of which must be “H” Address LIKE „%e‟ means any sequence of characters of length at least 1 with the last character an “e” Address LIKE „%Nicosia%‟ means a sequence of characters of any length containing “Nicosia” Address „H%‟ means the first character cannot be “H” NOT LIKE Example: List all the stocks whose Stock-Name starts wit “M”, by Stock-Code, Stock-Price SELECT Stock-Code, Stock-Name, Stock-Price FROM Stocks WHERE Stock-Code LIKE “M%” ; The result is: Stock- Stock- Code Price MB 100 $300 MS 10 $9 x) Null search condition. (IS NULL/IS NOT NULL) Supposing that we have an entry in the Sales that don‟t have a date, the blank inserted in the date field would have Null value and not ( Ø ) or " ". There fore we can't test it against these values. Example: List the entries in the Sales by Branch-Number, Stock-Code where there is no date entry. SELECT Stock-Code, Branch-Number FROM Sales WHERE Date IS Null ; The result is: Stock- Branch- Code Number KEY 04 B2 xi) Sorting the results. (ORDER BY) The sorting may be in ASC (ascending) or DESC (descending) order. The sorting may be used on single column or multiple columns Example: List the branches in ASC order of Branch-town. By branch-code, Branch- Name, branch-town. SELECT Branch-Number, Branch-Name, Branch-Town FROM Branch ORDER BY Branch-Town ASC The result is: Branch-Code Branch-Name Town B3 Famagusta Branch Famagusta B4 Harbor Branch Kyrenia B1 Shopping Center Branch Nicosia B2 Kyrenia Str. Branch Nicosia