Microsoft Access 2010

1. MicrosoftAccess 2010 Chapter 7 Using SQL

2. Objectives • Change the font or font size for SQL queries • Create SQL queries • Include fields in SQL queries • Include simple and compound criteria in SQL queries • Use computed fields and built-in functions in SQL queries • Sort the results in SQL queries Using SQL

3. Objectives • Use aggregate functions in SQL queries • Group the results in SQL queries • Join tables in SQL queries • Use subqueries • Compare SQL queries with Access-generated SQL • Use INSERT, UPDATE, and DELETE queries to update a database Using SQL

4. Project – Using SQL – pg. 418 read Using SQL

5. Housekeeping • Create a Chapter 7 folder • Copy Camashaly Design database from your chapter 5 folder to the chapter 7 folder • Open the database from Chapter 7 folder • Click on any query • Click on the view button, and choose SQL view • For this chapter, we will be CREATING these in SQL view Using SQL

6. General Project Guidelines • Select the fields for the query • Determine which table or tables contain these fields • Determine criteria • Determine sort order • Determine grouping • Determine any update operations to be performed Using SQL

7. SQL Background • SQL – Structured Query Language • Originally developed under the the name SEQUEL @ IBM in the mid-1970’s. • In 1980 – renamed SQL • You will be typing entries into a BLANK window.. Rather than using a design grid. • Let’s get started! Using SQL

8. Changing the Font Size - Do page 421 Using SQL

9. SQL Commands • The basic form of SQL expressions is quite simple: SELECT-FROM-WHERE • The command begins with a SELECT clause, which consists of the word, SELECT, followed by a list of those fields you want to include • Next, the command contains a FROM clause, which consists of the word, FROM, followed by a list of the table or tables involved in the query • Finally, there is an optional WHERE clause, which consists of the word, WHERE, followed by any criteria that the data you want to retrieve must satisfy Using SQL

10. Creating a New SQL Query – pages 422-426 Using SQL

11. Using a Criterion Involving a Numeric Field • To restrict the records to be displayed, include the word WHERE followed by a criterion as part of the command • If the field involved is a numeric field, you simply type the value Using SQL

12. Simple Criteria • A simple criterion has the form: field name, comparison operator, then either another field name or a value Using SQL

13. Using a Comparison Operator Using SQL

14. Using a Criterion Involving a Text Field • If the criterion involves a text field, the value must be enclosed in single quotation marks Using SQL

15. Using a Wildcard • In most cases, the conditions in WHERE clauses involve exact matches • The LIKE operator uses one or more wildcard characters to test for a pattern match • One common wildcard in Access, the asterisk (*), represents any collection of characters • Do page 427-430 Using SQL

16. Compound Criteria • Compound criteria are formed by connecting two or more simple criteria using AND, OR, and NOT • When simple criteria are connected by the word AND, all the simple criteria must be true in order for the compound criterion to be true • When simple criteria are connected by the word OR, the compound criterion will be true whenever any of the simple criteria are true • Preceding a criterion by the word NOT reverses the truth or falsity of the original criterion Using SQL

17. Using a Compound Criterion do pg. 431-433 Using SQL

18. Using a Computed Field • The one in the book (pg. 434) is wrong. • Do this one instead and save it as Ch7q10 • SELECT [Client Number], [Client Name], [Amount Paid], [Current Due], [Amount Paid] + [Current Due] AS [Total Cost] FROM [Client] WHERE [Current Due] > 0 ; Using SQL

19. Sorting • To sort the output, you include an ORDER BY clause, which consists of the words ORDER BY followed by the sort key • Do pages 436-438 Using SQL

20. Omitting Duplicates When Sorting • The DISTINCT operator eliminates duplicate values in the results of a query • To use the operator, you follow the word DISTINCT with the field name in parentheses Using SQL

21. DISTINCT • Pg. 440 is incorrect • So do this one instead… SELECT [Business Analyst Number] FROM [Client] ORDER BY [Business Analyst Number] ; • Now add the word DISTINCT after the word SELECT. • Look at the difference • Save the second query as Ch7q14 Using SQL

22. Using a Built-In Function • SQL has built-in functions, also called aggregate functions, to perform various calculations • COUNT • SUM • AVG • MAX • MIN Using SQL

23. Assigning a Name to the Results of a Function • You can assign a name to the results of a function • To do so, follow the expression for the function with the word AS and then the name to be assigned to the result Using SQL

24. Using Multiple Functions in the Same Command Using SQL

25. Functions • Do pages 441-443 Using SQL

26. Using Grouping • Grouping means creating groups of records that share some common characteristic • When you group rows, any calculations indicated in the SELECT command are performed for the entire group • GROUP BY clause – to get subtotals • Do pages 445-446 Using SQL

27. Joining Tables – pages 447-451 • Many queries require data from more than one table • Make sure to read the part about Aliases before doing page 451. Using SQL

28. Subqueries – page 452 • A subquery is a query within another query Using SQL

29. Using an INSERT Command • You can add records to a table using the SQL INSERT command • The command consists of the words INSERT INTO followed by the name of the table into which the record is to be inserted • Next is the word VALUE followed by the values for the fields in the record • Values for Text fields must be enclosed within quotation marks Using SQL

30. Using an UPDATE Command • You can update records in SQL by using the UPDATE command • The command consists of UPDATE, followed by the name of the table in which records are to be updated • Next, the command contains one or more SET clauses, which consist of the word SET, followed by a field to be updated, an equal sign, and the new value Using SQL

31. Using a DELETE Command • You can delete records in SQL using the DELETE command • The command consists of DELETE FROM, followed by the name of the table from which records are to be deleted • Finally, you include a WHERE clause to specify the criteria Using SQL

32. INSERTing records in a table with SQL • The queries on pages 455-456 are for a table we don’t have. Use these instead • INSERT INTO [Business Analyst] Values ('40', 'Coleman', 'Sandra', 'ABC', 'Starkville', 'MS', '39759', 25000, 5000) ; Save this query as CH7q24 Go look at the Business Analyst table and make sure I am there! MUST SHOW ME! Using SQL

33. UPDATEing records using SQL • Type the following query UPDATE [Business Analyst] SET [Incentive YTD] = 10000 WHERE [Business Analyst Number] = '40' ; Save the query as Ch7q25 Now, go check the table and see that it updated! MUST SHOW ME! Using SQL

34. Deleting Records in SQL • Type the following DELETE FROM [Business Analyst] WHERE [Business Analyst Number] = '40' ; Now, check the table to make sure I am gone! Save this query as Ch7q26 MUST SHOW ME! Using SQL

35. What Next? • Homework • www.scsite.com/ac2010 • Do Flash Cards.. You get what you make • Due next class meeting Labs IN THE LAB lab 3: Philamar Training database. Page 462. That’s all for SQL chapter. Your final exam will be ONLY SQL… FINAL EXAM – Wednesday, Dec. 12 @ 10:15.. Using SQL