1 / 71

SQL

SQL. Chapters 6 & 7. Sql queries. Querying Data. Retrieving Data Sorting Data Filtering Retrieved Data Combining filters Wildcard filters Creating Calculated Fields Using Functions Grouping Data Filtering groups Accessing Data from Multiple Tables Subqueries Joins

rhona
Download Presentation

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. SQL Chapters 6 & 7

  2. Sql queries

  3. Querying Data • Retrieving Data • Sorting Data • Filtering Retrieved Data • Combining filters • Wildcard filters • Creating Calculated Fields • Using Functions • Grouping Data • Filtering groups • Accessing Data from Multiple Tables • Subqueries • Joins • Creating Data Views

  4. Retrieving data

  5. Querying Tables • Basic Format: SELECT column(s) FROM table [WHERE condition(s)]; • Rules: • SELECT must be first clause • FROM must be second clause • Case does not matter (in SQL Server) • Table/column names must be spelled as in the database • Use double quotes for object names, single quotes for literal strings

  6. Selecting all rows, specific columns • SELECT column1[, column2, column3,…,columnX] FROM table; • List all customer IDs on orders SELECTcustomer_ID FROMorder_t;

  7. Selecting All Rows, all columns • SELECT * FROM table; • Display information about all orders SELECT * FROM order_t;

  8. Selecting Unique Rows • SELECT DISTINCT column(s) FROM table; • List all customers who've placed orders. SELECTDISTINCTCustomer_IDFROMOrder_t;

  9. Sorting data

  10. Sorting Results • ORDER BY • Orders results in ASC or DESC order of column(s) • List customer names and addresses in descending order of customer's name. SELECT Customer_name, Customer_address FROMCustomer_t ORDER BYCustomer_nameDESC; • List product numbers, descriptions, and quantities on hand for all products. Show products with highest quantities first, and then in alphabetical order of product description. SELECTProduct_id, product_description, qty_on_hand FROMProduct_t ORDER BYqty_on_hand DESC, product_description ASC;

  11. filtering data

  12. Search Conditions • For retrieving specific rows: • Comparison Operators • Boolean Operators • Special Operators • Calculated Fields (Expressions) • SELECT column(s) FROM table WHERE <search condition(s)>;

  13. Comparison Operators, con't... • Comparison Operators: = equal to > greater than < less than >= greater than or equal to <= less than or equal to <> not equal to

  14. Comparison Operators, cont… • Show order IDs and dates of all orders placed by customer 1. SELECTOrder_ID, Order_Date FROM Order_t WHERE Customer_ID = 1; • Show the time that order #1002 was placed. SELECTOrder_id, CONVERT(varchar(8), Order_Date, 8) AS "Order Time" FROM Order_t WHEREOrder_id = 1002;

  15. Boolean Operators • Boolean Operators • AND all search conditions must be met • OR any search condition must be met • NOT a search condition must not be met • Show orders placed by customer 1 with order ID(s) higher than 1005. SELECT * FROM Order_t WHERECustomer_ID= 1 ANDOrder_ID> 1005;

  16. Order of evaluation • NOT, AND, OR • Use parentheses to ensure desired ordering • Show orders placed by CUSTOMERS EXCEPT customer 1, and with order ID(s) higher than 1007 • Show orders placed by customers except customer 1, that have EITHER an order ID higher than 1007 OR an order date on the 24TH of the month

  17. Special Operators • Shortcuts • IN • BETWEEN • Wildcard matching • LIKE • NULL • TOP

  18. Special Operators, cont… • IN Find dates that customers 1, 3, & 5 placed orders. SELECT Customer_ID, Order_Date FROM Order_t WHERE Customer_ID IN (1,3,5); • BETWEEN Find dates of orders placed by customers 1 thru 5. SELECTCustomer_ID, Order_Date FROMOrder_t WHERECustomer_IDBETWEEN 1 AND 5;

  19. Special Operators, cont… • LIKE Show customers with 'furn' in their names. SELECTCustomer_Name FROMCustomer_t WHERECustomer_NameLIKE '%furn%'; Show customers with 'furn' + 5 characters in their names. SELECTCustomer_Name FROMCustomer_t WHERECustomer_NameLIKE '%furn_____';

  20. Special Operators, cont… • Null • unknown • not applicable List customers who do not have an address listed. SELECT Customer_ID, Customer_Name FROM Customer_t WHERECustomer_addressIS NULL; • Beware… SELECT Customer_Name FROMCustomer_t WHERECustomer_address = NULL;

  21. Special operators, cont… • TOP n • Displays first N rows of query results Display the first 3 orders… SELECT TOP 3 * FROMorder_t; SELECT TOP 3 * FROMorder_t ORDER BY order_date;  In the table???  That we ever received???

  22. Special operators, cont… • TOP n WITH TIES • Displays query result rows that have tie/duplicate values • Therefore may return more than N rows • ORDER BY is required Which 2 products have the highest inventory? SELECT TOP 2product_id, product_description, qty_on_hand FROMproduct_t ORDER BY qty_on_handDESC; Which products have the 2 highest inventory levels? SELECT TOP 2 WITH TIESproduct_id, product_description, qty_on_hand FROMproduct_t ORDER BY qty_on_handDESC;

  23. Calculated fields, expressions

  24. Using Column Aliasing • Assign friendly names to existing columns • Assign names to derived columns • Only exists for duration of query • SELECTcolumn_nameAS "alias_name" [, ….] FROM table; • Show order information for customers 1, 3, and 4. Label the order dates "Date of Order". SELECTOrder_ID, Order_DateAS "Date of Order", customer_id FROM Order_t WHERE customer_idIN (1, 3, 4) ORDER BY customer_id;

  25. Using table aliasing • Assign friendly (shortened) names to tables • Allows same table to be referenced multiple times in a query • Only exists for duration of query • SELECTcolumn_name(s)[, ….] FROM table table_alias; • List descriptions of all products with any kind of “natural” finish. SELECTProduct_description FROMProduct_tP WHEREP.product_finishLIKE 'Natural%';

  26. Expressions • Manipulating column values in a query • Effect is Temporary • Show the effect of increasing product prices by 10% for those products that are currently priced under $300. SELECTProduct_ID, Standard_PriceAS "Old Price", Standard_Price*1.1 AS "New Price" FROMProduct_t WHEREStandard_Price < 300;

  27. Concatenating values • Connecting text values together • Show the name, full address (street, city, state, zip) and number of orders placed by each customer in Florida. SELECTcustomer_name, customer_address + ', ' + city + ', ' + state + ', ' + postal_codeAS "address", orders_placed FROMcustomer_t WHERE state = 'fl';

  28. Sql server cast function • Converts data from one datatype to another • Use for all other datatype conversions • CAST ( value AS target_data_type[ ( length ) ] ) • Examples: • SELECT 'The list price is: ' + CAST(standard_price AS varchar) FROM product_t; • Print 'old zip: ' + CAST(@czip AS varchar)…;

  29. functions

  30. Functions • Display, convert, manipulate values • Scalar • Text • Date/Time • Year, Month, Day • … • Mathematical • Floor, Ceiling, Round • …. • System • System_User • GetDate() • … • Aggregate • COUNT • MIN • MAX • SUM • AVG

  31. scalar functions

  32. DATES • Work with dates in ways other than mm/dd/yy • Can also use CONVERT function to do this • General Information on SQL Server Date functions: • http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx • http://www.w3schools.com/SQl/sql_dates.asp • Examples of using Date functions • http://jahaines.blogspot.com/2009/06/date-functions.html • http://sqlserverpedia.com/wiki/Built-in_Functions_-_Date_and_Time_Functions

  33. Dates, cont… • Show the year customer 1 last placed an order. SELECTTOP 1YEAR(order_date) AS "Year Last Ordered" FROMorder_t WHEREcustomer_id = 1 ORDER BY order_dateDESC; • Show the different months that orders were placed. SELECT DISTINCTMONTH(order_date) FROMorder_t; SELECT DISTINCT DATENAME(month, order_date) AS "Month" FROMorder_t;

  34. Dates, cont… • How many months have elapsed between our order dates and today? SELECT DATEDIFF(MONTH, order_date, GETDATE()) FROMorder_t; * syntax is DATEDIFF(datepart, start_date, end_date) ** see http://www.w3schools.com/sql/func_datediff.asp for examples of how to subtract other date parts such as times • Show how old, in years, our orders are. Show results in descending order of age. SELECTorder_id,YEAR(getdate()) - YEAR(order_date) AS "Order Age in Years" FROMorder_t ORDER BY "Order Age in Years" DESC;

  35. numeric • Performs mathematical functions • Show the effects of decreasing product prices by 3.5%. SELECTproduct_id, standard_price, standard_price*.965 FROMproduct_t; SELECTproduct_id, standard_price, ROUND(standard_price*.965, 2) FROMproduct_t; SELECTproduct_id, standard_price, FLOOR(standard_price*.965) FROMproduct_t;

  36. SYSTEM • Retrieves information maintained by SQL Server • Who is the user currently logged on? SELECTSYSTEM_USER; • What is today's date? SELECTGETDATE(); • What orders were placed in the current month of any year? SELECT * FROMorder_t WHERE MONTH(order_date) = MONTH(GETDATE());

  37. Aggregate functions

  38. Aggregate Functions • Produce aggregated data • COUNT • MIN • MAX • SUM • AVG

  39. Count • Counts number of rows/values retrieved from query • How many orders are there? SELECT COUNT(*) FROMOrder_t; • Show how many orders were placed after Nov 1st 2000. SELECT COUNT(*) FROMOrder_t WHEREOrder_Date > '01-NOV-00'; • How many orders have customer IDs? SELECT COUNT (Customer_ID) FROMOrder_t; • How many different customers have placed orders? SELECT COUNT (DISTINCTCustomer_ID) FROMOrder_t;

  40. Min and Max • Finds minimum/maximum value of attribute Find date of most recent order. SELECT MAX(Order_Date) FROMOrder_t; Show the earliest year that an order was ever placed. SELECTMIN (YEAR(Order_Date)) FROMOrder_t; Show the date that customer 1 first placed an order. SELECTMIN(Order_Date) FROM Order_t WHERE Customer_ID= 1;

  41. Sum and Avg • SUM (totals values for specific attribute) How many products are in inventory? SELECT SUM(qty_on_hand) FROMProduct_t; • AVG (finds average value of an attribute) What's the average price of our products? SELECT AVG(Standard_Price) FROMProduct_t; List the average product price and lowest product quantity on hand of our products. SELECT AVG(Standard_Price),MIN(qty_on_hand) FROM Product_t;

  42. Grouping data

  43. Categorizing Results • GROUP BY • groups output according to an attribute • can perform operations on groups of rows • List the average product price and lowest product quantity on hand for each product finish we offer. SELECTProduct_finish, AVG(Standard_Price), MIN(qty_on_hand) FROM Product_t; SELECT Product_finish,AVG(Standard_Price),MIN(qty_on_hand) FROMProduct_t GROUP BYProduct_finish;

  44. More on Group By’s… • How many customers are there in each state? SELECT COUNT(customer_id) FROM customer_t GROUP BY state; SELECT COUNT(customer_id), state FROM customer_t GROUP BY state; • How many customers are in each postal_code area of each state? SELECT COUNT(customer_id), state, postal_code FROM customer_t GROUP BY state; SELECT COUNT(customer_id), state, postal_code FROM customer_t GROUP BY state, postal_code;

  45. Categorizing Results, con't... • HAVING • Like a WHERE clause for groups List the average product price and lowest product quantity on hand for each product finish that has (a) at least one product with two or more units on hand, and(b) at least 3 products with that finish. SELECT Product_finish, AVG(Standard_Price), MIN(Qty_on_hand) FROM Product_t GROUP BY Product_finish HAVING MIN (Qty_On_hand) >= 2 AND COUNT (Product_id) >=3;

  46. Accessing data in multiple tables

  47. Querying Multiple Tables • Nested Queries (aka Subqueries) • Joins • (Inner) Join • Self Join • (Outer) Left Join • Checking Non-Existence Conditions • NOT IN • NOT EXISTS • Creating Views of data

  48. subqueries • Placing an "inner" query in WHERE clause • Inner queries must SELECT only one column • Column selected by "inner" query must match column in WHERE clause of “outer" query Show all orders placed by customers who live in states that do NOT end in 'A'. Show orders in descending order. SELECTOrder_ID FROMOrder_t WHERECustomer_IDIN (SELECTCustomer_ID FROMCustomer_t WHERE State NOT LIKE '_A') ORDER BYOrder_IDDESC;

  49. Subqueries, cont… Which customers have not placed orders? Show customer IDs and names, sorted by name. SELECT customer_id, customer_name FROMcustomer_t WHERE customer_idNOT IN (SELECT _____________ FROM order_t) ORDER BY customer_name;

  50. Subqueries, cont… Revisit: Which 2 products have the highest inventory? SELECT TOP 2WITH TIES product_id, product_description, qty_on_hand FROMproduct_t ORDER BY qty_on_handDESC; SELECTproduct_id, product_description, qty_on_hand FROM Product_t WHEREqty_on_handIN (SELECT DISTINCT TOP 2 qty_on_hand FROMproduct_t ORDER BYqty_on_handDESC);

More Related