1 / 186

Chapter 6

Chapter 6. Structured Query Language. SQL. The relational operators define permissible data manipulation functions. They are not a specification for a data access language. These operators imply a certain functionality. Introduction to SQL . IBM in the mid-1970s as SEQUEL

Albert_Lan
Download Presentation

Chapter 6

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. Chapter 6 Structured Query Language

  2. SQL The relational operators define permissible data manipulation functions. They are not a specification for a data access language. These operators imply a certain functionality.

  3. Introduction to SQL • IBM in the mid-1970s as SEQUEL • SQL is a standard • slight variations among implementations • data access language that is embedded in application programs • result of an SQL statement is a relation • Transform-oriented language

  4. Introduction to SQL • Four verbs • SELECT, UPDATE, DELETE, INSERT • Basic CRUD functionality • Create - INSERT • Read - SELECT • Update - UPDATE • Delete - DELETE

  5. SELECT “We must SELECT the illusion which appeals to our temperament, and embrace it with passion, if we want to be happy.” Cyril Connolly

  6. Introduction to SQL General Form: SELECT [DISTINCT] Item(s) FROM table(s) [WHERE predicate] [GROUP BY field(s) [HAVING predicate]] [ORDER BY field(s)]; [Optional parameters]

  7. Simple Retrieval (SELECT) Retrieve rows from SUPPLIER for suppliers located in Finland SELECT * FROM SUPPLIER WHERE SUPPLIER.SupplierCity = ‘FINLAND’;

  8. Simple Retrieval (SELECT) SELECT * FROM SUPPLIER WHERE SUPPLIER.SupplierCity = ‘FINLAND’; Do you think using * (all columns) is, in general, a good practice? In applications? For exploring a table yes, but embedding it in an application is risky. Order of columns can change, column names can change. It’s usually best to enumerate the column names in the SELECT.

  9. SELECT “What is known as success assumes nearly as many aliases as there are those who seek it.“ Stephen Birmingham

  10. Simple Retrieval (SELECT) Often you want to present the columns in the result not in their native format, but in an alternative, more application specific format. We do this with a column Alias.

  11. Simple Retrieval (SELECT) SELECT OrderDetail.OrderID FROM [Order Details] AS OrderDetail WHERE OrderDetail.UnitPrice = 14 We can also use a Table Alias In this case, we can now reference the Order Details table in the query without the troublesome [ ]’s.

  12. Simple Retrieval (SELECT) I often use Aliases to abbreviate long object names. As a short hand, it is convenient; but it makes the query much less readable and maintainable. Meaningful aliases, just like meaningful identifiers in programming, is important.

  13. Simple Retrieval (SELECT) SELECT Orders.OrderID, [Order Details].OrderDate FROM Orders, [Order Details] WHERE Order.OrderID=[Order Details].OrderID AND [Order Details].UnitPrice = 14 Becomes… SELECT O.OrderID, O.OrderDate FROM Orders O, [Order Details] OD WHERE O.OrderID=OD.OrderID AND OD.UnitPrice = 14

  14. Simple Retrieval (WHERE) The WHERE clause specifies a condition or conditions that restricts the rows return in the result set.

  15. Simple Retrieval (WHERE) WHERE clauses can be as complex as they need to be. List the OrderID, OrderDate, and RequiredDate of orders for employee 5, shipped to the USA, for either customers SAVEA or RATTC, that has a freight cost exceeding $50. SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = 'USA' AND (CustomerID = 'SAVEA' OR CustomerID = 'RATTC') AND Freight > 50

  16. Simple Retrieval (WHERE) An alternative means of expressing the OR clause below is with the IN() clause. SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = 'USA' AND (CustomerID = 'SAVEA' OR CustomerID = 'RATTC') AND Freight > 50 Becomes… SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = 'USA' AND CustomerID IN ('SAVEA','RATTC') AND Freight > 50

  17. Simple Retrieval (WHERE) The IN() clause is an example of an uncorrelated subquery. The expression evaluates to TRUE if the test expression matches any values in the list. test_expression [NOT] IN (value1, value2,…valueN) The list can be “hard-coded” as below, or it can be the result of a SELECT statement. The only restriction is that the SELECT statement has to return an enumerated list, not a matrix (a list of values versus a table). SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = 'USA' AND CustomerID IN ('SAVEA','RATTC') AND Freight > 50

  18. Simple Retrieval (WHERE) test_expression [NOT] IN (value1, value2,…valueN) The list can be “hard-coded” as below, or it can be the result of a SELECT statement. The only restriction is that the SELECT statement has to return an enumerated list, not a matrix (a list of values versus a table). SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = 'USA' AND CustomerID IN (SELECT CustomerID FROM Customers WHERE Condition = value) AND Freight > 50 We’re going to spend a great deal more time on uncorrelated subqueries later…

  19. Simple Retrieval (WHERE) Speaking of complex WHERE clauses, how would you answer this question? List the EmployeeID, OrderID, and OrderDate for Employees 1 and 2 where they have orders placed on the same day.

  20. Simple Retrieval (WHERE) How would you output the data such that the employee records were sequential rather than side-by-side? List the EmployeeID, OrderID, and OrderDate for Employees 1 and 2 where they have orders placed on the same day.

  21. Simple Retrieval (WHERE) “The difference BETWEEN the right word and the almost right word is the difference BETWEEN lightning and a lightning bug. “ Mark Twain

  22. Simple Retrieval (WHERE) The BETWEEN clause specifies a range of values to test against the test expression. test_expression [NOT] BETWEEN begin_value AND end_value

  23. Simple Retrieval (WHERE) “All women become LIKE their mothers. That is their tragedy. No man does. That's his. “ Oscar Wilde

  24. Simple Retrieval (WHERE) The LIKE clause performs character pattern matching. match_expression [NOT] LIKE pattern List the CustomerID and Phone number of customers that have a phone number with area code (503).

  25. Simple Retrieval (WHERE) The LIKE clause performs character pattern matching. Return all the Quarterly Productivity results: WHERE Report LIKE ‘Q_ProductivityResult’

  26. Simple Retrieval (WHERE) A word about NULL… “The very impossibility in which I find myself to prove that God is not, discovers to me his existence.” Voltaire

  27. Simple Retrieval (WHERE) • NULL implies that an attribute value has not been supplied. It is not empty string or zero. It has no existence. • Permitting NULL “values” in your database introduces ambiguity regarding the “existence” of a value. • Null can mean: • The value is unknown • The value is not appropriate • The value is known to be blank

  28. Simple Retrieval (WHERE) DBMS’s provide a NULL function to check for NULL values. In SQL Server this function is IS [NOT] NULL List the Employee name of the employee who doesn’t report to anyone.

  29. Simple Retrieval (WHERE) List the Employee name of the employee who doesn’t report to anyone. Should this be NULL? What does it mean that Fuller has a Null value for ReportsTo?

  30. Simple Retrieval (WHERE) “ORDER is never observed; it is disorder that attracts attention because it is awkward and intrusive.” Eliphas Levi ”

  31. Simple Retrieval (ORDER BY) Although the order of rows and columns is unimportant in a “true” relation, the order of the result set is. ORDER BY specifies the sort for the result set. ORDER BY { order_by_expression [ ASC | DESC ] ORDER BY can sort on multiple columns.

  32. Simple Retrieval (ORDER BY)

  33. Simple Retrieval (ORDER BY) Note that the sort fields don’t have to appear in the SELECT statement

  34. Simple Retrieval (ORDER BY) The optimizer first did a scan of the available indexes. SQL Server automatically creates an index on the primary key. If the data are to be sorted by a field frequently, you would want to create and index on that field to improve sorting performance.

  35. Simple Retrieval (Built-in Functions) Also known as aggregate functions, these functions perform standard calculations on rows or groups of rows of records. Aggregate functions return a single value. With the exception of COUNT, aggregate functions ignore NULLs. Some of the functions in SQL Server are: AVG, COUNT, MAX, MIN, SUM

  36. Simple Retrieval (Built-in Functions) Display the average product UnitPrice. SELECT AVG(UnitPrice) AverageUnitPrice FROM Products Display The number of orders placed by a specific customer.

  37. Simple Retrieval (Built-in Functions) What is the total sales for a specific product?

  38. Simple Retrieval (Built-in Functions) What is the highest and lowest UnitPrice in the Products table?

  39. Simple Retrieval (Built-in Functions) List the ProductID and it’s UnitPrice for the product that has the highest UnitPrice. Why doesn’t this work?

  40. Simple Retrieval (Built-in Functions) Here’s a more challenging one. List the average number of years the employees in the employee table with job_lvl greater than 200 have been with the publisher (assuming no one has retired). That is, compute the difference between the hire_date and the current date in years and take the average.

  41. Simple Retrieval (GROUP BY) It is often the case that we want to perform aggregate functions on groups of records. GROUP BY divides a table into groups. Groups can consist of column names or results or computed columns. For example, if you order the Products table in the Northwind database, you can see that the rows can be group by CategoryID

  42. Simple Retrieval (GROUP BY) CategoryID can be used to group the rows. GROUP BY enables us to perform operations on those groups.

  43. Simple Retrieval (GROUP BY) Count the number of products in each category in the Products table.

  44. Simple Retrieval (HAVING) The HAVING clause specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. [ HAVING < search_condition > ]

  45. Simple Retrieval (Date Functions) “And summer's lease hath all too short a date.” William Shakespeare

  46. BETWEEN Use BETWEEN to pull rows in a given date range. For example, in the Northwind database, list the product names of the products ordered in the first week of January 1997. SELECT PRODUCTNAME FROM PRODUCTS WHERE PRODUCTID IN (SELECT PRODUCTID FROM ORDERS, [ORDER DETAILS] ORDERDETAIL WHERE ORDERS.ORDERID=ORDERDETAIL.ORDERID AND ORDERDATE BETWEEN '1997-01-01' AND '1997-01-07')

  47. Date functions But how to get this output? List the orders in the month of January 1997 that shipped later than 10 days from the order date. ORDERID ORDERDATE SHIPPEDDATE Days ------- ---------------------- ----------------------- ------- 10400 1997-01-01 00:00:00.000 1997-01-16 00:00:00.000 15 10405 1997-01-06 00:00:00.000 1997-01-22 00:00:00.000 16 10407 1997-01-07 00:00:00.000 1997-01-30 00:00:00.000 23

  48. Date Functions List the orders in the month of January 1997 that shipped later than 10 days from the order date. SELECT ORDERS.ORDERID, ORDERDATE, SHIPPEDDATE, DATEDIFF(DAY,ORDERDATE, SHIPPEDDATE) FROM ORDERS WHERE ORDERDATE BETWEEN '1997-01-01' AND '1997-01-07' AND DATEDIFF(DAY,ORDERDATE, SHIPPEDDATE) > 10 Syntax DATEDIFF ( datepart ,startdate ,enddate )

More Related