1 / 29

SQL – Lesson II

SQL – Lesson II. Grade 12. The SELECT statement. The SELECT statement is used to query the database and retrieve data that matches the criteria that you specify. The SELECT statement has five main clauses to choose from…. SELECT statement format:.

sylvester-riddle
Télécharger la présentation

SQL – Lesson II

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 – Lesson II Grade 12

  2. The SELECT statement The SELECT statement is used to query the database and retrieve data that matches the criteria that you specify The SELECT statement has five main clauses to choose from… SELECT statement format: SELECT [ALL | DISTINCT] column1[,column2]FROMtable1[,table2] [WHERE "conditions"][GROUP BY "column-list"][HAVING "conditions][ORDER BY "column-list" [ASC | DESC] ]

  3. Lets’ break it down… The above statement will select all of the values in the name, age, and salary columns from the employee table whose age is greater than 50. Example: SELECT name, age, salary FROM employee WHERE age > 50; Comparison Operators

  4. Like what? The LIKE “matching” operator can also be used in the conditional selection of the where clause… Example: SELECT name, title, dept FROM employee WHERE title LIKE 'Pro%'; So what about : ALL and DISTINCT? …are keywords used to select either ALL (default) or the “DISTINCT" / unique records in your query results. Let’s try a few…

  5. items_ordered Exercises • From the “itemsOrdered”table, select a list of all items purchased for “customerId” 10449. Display the customerid, item, and price for this customer. • Select all columns from the itemsOrdered table for whoever purchased a Tent. • Select the customerId, orderDate, and item values from the itemsOrdered table for any items in the item column that start with the letter "S". • Select the distinct items in the itemsOrdered table. In other words, display a listing of each of the unique items from the itemsOrdered table. Answers : #1 SELECT customerId, item, price FROM itemsOrdered WHERE customerId=10449; #2 SELECT * FROM items_ordered WHERE item = 'Tent'; #3 SELECT customerId, orderDate, item FROM itemsOrdered WHERE item LIKE 's%'; #4 SELECT DISTINCT item FROM itemsOrdered;

  6. Aggregate Functions Aggregate functions are used to “compare To” a returned column of numeric data". We need to understand these functions before we can cover the "GROUP BY“clause. For example:

  7. SELECT AVG(salary) FROM employee; SELECT AVG(salary) FROM employee WHERE title = 'Programmer'; SELECT Count(*) FROM employees; This statement will return the number of rows in the employees table. Exercises • Select the maximum price of any item ordered in the “itemsOrdered” table. • Hint: Select the maximum price only.> • 2. Select the average price of all of the items ordered that were purchased in the month of Dec. • 3. What are the total number of rows in the itemsOrdered table? • 4. For all of the tents that were ordered in the itemsOrdered table, what is the price of the lowest tent? • Hint: Your query should return the price only.

  8. Answers • #1 • SELECT max(price) FROM itemsOrdered; • #2 • SELECT avg(price) FROM itemsOrdered WHERE orderDate LIKE '%Dec%'; • #3 • SELECT count(*) FROM itemsOrdered; • #4 • SELECT min(price) FROM itemsOrdered WHERE item = 'Tent';

  9. The GROUP BY clause… ..will gather all of the rows together that contain data in the specified column(s). It will also allow aggregate functions to be performed on the one or more columns. For example: Let's say you want to retrieve a list of the highest paid salaries in each dept: SELECT max(salary), dept FROM employee GROUP BY dept; The above statement selects the maximum salary for the people in each unique department. Let’s look at the syntax: SELECT column1, SUM(column2) FROM "list-of-tables“ GROUP BY "column-list";

  10. GROUP BY - Multiple Grouping Columns - What if? What if you ALSO want to display their last name for the query below: • SELECT max(salary), dept FROM employee GROUP BY dept; What you'll need to do is: • SELECT surname, max(salary), dept FROM employee GROUP BY dept, surname; This is a called "multiple grouping columns". Another example? Show them items table. Let's say you want to group everything of quantity 1 together, everything of quantity 2 together, everything of quantity 3 together, etc. If you would like to determine what the largest cost item is for each grouped quantity (all quantity 1's, all quantity 2's, all quantity 3's, etc.), you would enter: SELECT quantity, max(price) FROM items GROUP BY quantity;

  11. Exercises • How many people are in the various provinces in the customers table? Select the province and display the number of people in each. Hint: count is used to count rows in a column, sum works on numeric data only. • From the itemsOrdered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups. 3. How many orders did each customer make? Use the itemsOrdered table. Select the customerId, number of orders they made, and the sum of their orders. Answers #1 SELECT province, count(province) FROM customers GROUP BY province; #2 SELECT item, max(price), min(price) FROM itemsOrdered GROUP BY item; #3 SELECT customerId, count(customerId), sum(price) FROM itemsOrdered GROUP BY customerId;

  12. The HAVING clause… …allows you to specify conditions on the rows for each group. In other words, which rows should be selected will be based on the conditions you specify. Let’s look at the syntax: SELECT column1, SUM(column2)FROM "list-of-tables"GROUP BY "column-list"HAVING "condition"; HAVING can best be described by example. SELECT dept, avg(salary)FROM employeeGROUP BY dept; But, let's say that you want to ONLY calculate & display the average if their salary is over 20000: SELECT dept, avg(salary) FROM employee GROUP BY dept HAVING avg(salary) > 20000;

  13. Exercises 1. How many people are in each province in the customers.tbl that have more than one person in the province? Select province and display the number of how many people are in each if it's greater than 1. 2. From the itemsOrdered.tbl, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00. 3. How many orders did each customer make? Use the itemsOrdered table. Select customerId, number of orders they made, and the sum of their orders if they purchased more than 1 item.

  14. Answers #1 SELECT province, count(province) FROM customers GROUP BY province HAVING count(province) > 1; #2 SELECT item, max(price), min(price) FROM itemsOrdered GROUP BY item HAVING max(price) > 190.00; #3 SELECT customerId, count(customerId), sum(price) FROM itemsOrdered GROUP BY customerId HAVING count(customerId) > 1;

  15. The ORDER BY clause… is an optional clause which allows you to display the results of your query in a sorted order…  Let’s look at the syntax: SELECT column1, SUM(column2)FROM "list-of-tables"ORDER BY "column-list" [ASC | DESC]; For example: SELECT employeeId, dept, name, age, salaryFROM employeeInfoWHERE dept = 'Sales'ORDER BY salary; Or, if you would like to order based on 2 or more columns… SELECT employee_id, dept, name, age, salaryFROM employee_infoWHERE dept = 'Sales'ORDER BY salary, age DESC;

  16. Exercises • Select the surname, firstname, and city for all customers in the customers table. Display the results in Ascending Order according to the lastname. • Same thing as above, but display the results in Descending order. • Select the item and price for all of the items in the itemsOrdered table that the price is greater than 10.00. Display the results in Ascending order based on the price. Answers #1 SELECT lastname, firstname, city FROM customers ORDER BY lastname; #2 SELECT lastname, firstname, city FROM customers ORDER BY lastname DESC; #3 SELECT item, price FROM itemsOrdered WHERE price > 10.00 ORDER BY price ASC;

  17. Combining conditions and Boolean Operators The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met SELECT column1, SUM(column2)FROM "list-of-tables"WHERE "condition1" AND "condition2"; Similarly… The OR operator can be used to join two or more conditions in the WHERE clause also. For example SELECT employeeId, firstname, surname, title, salaryFROM employeeInfoWHERE salary >= 50000.00 AND title = 'Programmer'; Please feel free to use brackets to separate your conditions…it makes it easier to read… SELECT employeeId, firstname, surname, title, salaryFROM employeeInfoWHERE salary >= 50000.00 AND title = 'Programmer';

  18. And/ OR continued… Another Example: SELECT firstname, surname, title, salaryFROM employeeInfoWHERE (title = 'Sales') OR (title = 'Programmer'); Some few Exercises … • Select the customerId, orderDate, and item from itemsOrdered.tbl • for all items unless they are 'Shoes‘ or ‘Shot-Guns'. • Display the rows as long as they are not either of these two items. • 2. Select the item and price of all items that start with the letters 'S', 'P', or 'F'.

  19. #1 SELECT customerId, orderDate, item FROM itemsOrdered WHERE (item <> 'shoes') AND (item <> ‘shot-guns'); NB: You do want to use an AND here. If you were to use an OR here, then either side of the OR will be true, and EVERY row will be displayed. For example, when it encounters ' shot-guns ', it will evaluate to True since ' shot-guns ' are not equal to 'shoes'. #2 SELECT item, price FROM itemsOrdered WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%'); Answers

  20. IN and BETWEEN Conditional Operators… You can also use NOT IN to exclude the rows in your list. SELECT col1, SUM (col2)FROM "list-of-tables"WHERE col3 IN (list-of-values); SELECT col1, SUM(col2)FROM "list-of-tables"WHERE col3 BETWEEN value1 AND value2; …are used to test whether or not a value is "in" the list of values provided after the keyword IN An example: SELECT employeeId, surname, salaryFROM employeeInfoWHERE surname IN (Naidoo', ‘Naidu', ‘Moodley', ‘Pompilio'); Alternatively… SELECT employeeId, surname, salaryFROM employee_infoWHERE surname = Naidoo' OR surname = ‘Naidu' OR surname = ‘Moodley' OR lastname = ‘Pompilio';

  21. The BETWEEN operator …is used to test to see whether or not a value is “between” a value!? For example: SELECT employeeId, age, surname, salaryFROM employeeInfoWHERE age BETWEEN 30 AND 40; Alternatively… SELECT employeeId, age, surname, salaryFROM employeeInfoWHERE age >= 30 AND age <= 40; NB.: You can also use NOT BETWEEN to exclude the values between your range. Let’s try a few…

  22. Exercises… • Select the date, item, and price from the itemsOrdered table for all of the rows that have a price value ranging from 10.00 to 80.00. • Select the firstName, city, and province from the customers table for all of the rows where the state value is either: KZN, WC, EC, Gauteng, or Northern Cape . Answers #1 SELECT orderDate, item, price FROM itemsOrdered WHERE price BETWEEN 10.00 AND 80.00; #2 SELECT firstName, city, province FROM customers  WHERE province IN (KZN', ‘WC', ‘EC', ‘Gauteng', ‘Northern Cape');

  23. Mathematical Operators The basics… Some few others… Example: SELECT round(salary), firstnameFROM employee_info

  24. Now you try… Select the item and per unit price for each item in the items_ordered table. Hint: Divide the price by the quantity. The answer: Exercise #1 select item, sum(price)/sum(quantity)from items_orderedgroup by item;

  25. Table Joins, a must To put it simply, the "Join" makes relational database systems "relational". A "Join" can be recognized in a SQL SELECT statement if it has more than one table after the FROM keyword. NB.:Joins can be explained easier by demonstrating what would happen if you worked with one table only NB>:Let's say you have a one-table database that is used to keep track of all of your customers and what they purchase from your store: unnecessary "redundant data" An ideal database would have two tables: One for keeping track of your customers And the other to keep track of what they purchase: Normalistation…

  26. This column, which contains the unique customer number will be used to JOIN the two tables. "Customer_info" table: Notice  "Purchases" table: SELECT customer_info.firstname, customer_info.lastname, purchases.itemFROM customer_info, purchasesWHERE customer_info.customer_number = purchases.customer_number; his particular "Join" is known as an "Inner Join The correct syntax though would be.. SELECT customer_info.firstname, customer_info.lastname, purchases.itemFROM customer_info INNER JOIN purchasesON customer_info.customer_number = purchases.customer_number;

  27. Another example and an exercise… SELECT employee_info.employeeid, employee_info.lastname, employee_sales.comissionFROM employee_info, employee_salesWHERE employee_info.employeeid = employee_sales.employeeid; employee_sales employee_info employeeid employeeid Exercises • Write a query using a join to determine which items were ordered by each of the customers • in the customers table. Select the customerid, firstname, lastname, order_date, item, and • price for everything each customer purchased in the items_ordered table. • 2. Repeat exercise #1, however display the results sorted by state in descending order.

  28. Answers • Exercise #1 • SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price FROM customers, items_ordered WHERE customers.customerid = items_ordered.customerid; • Exercise #2 • SELECT customers.customerid, customers.firstname, customers.state, items_ordered.item FROM customers, items_ordered WHERE customers.customerid = items_ordered.customerid ORDER BY customers.state DESC; So, what about OUTER JOINS?

  29. NEXT YEAR LOSERS!!!

More Related