1 / 56

Access SQL Feature

Access SQL Feature. Using SQL. Objectives. Change the font or font size for SQL queries Include fields and criteria in SQL queries Use computed fields and built-in functions in SQL queries Sort the results in SQL queries Use multiple functions in the same command. Objectives.

tola
Télécharger la présentation

Access SQL Feature

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. Access SQL Feature Using SQL

  2. Objectives • Change the font or font size for SQL queries • Include fields and criteria in SQL queries • Use computed fields and built-in functions in SQL queries • Sort the results in SQL queries • Use multiple functions in the same command Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  3. Objectives • 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 Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  4. Plan Ahead • 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 Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  5. Starting Access • Click the Start button on the Windows Vista taskbar to display the Start menu • Click All Programs at the bottom of the left pane on the Start menu to display the All Programs list and then click Microsoft Office in the All Programs list to display the Microsoft Office list. • Click Microsoft Office Access 2007 in the Microsoft Office list to start Access and display the Getting Started with Microsoft Office Access window. • If the Access window is not maximized, click the Maximize button on its title bar to maximize the window Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  6. Opening a Database • With your USB flash drive connected to one of the computer’s USB ports, click the More button to display the Open dialog box • If the Folders list is displayed below the Folders button, click the Folders button to remove the Folders list • If necessary, click Computer in the Favorite Links section and then double-click UDISK 2.0 (E:)to select the USB flash drive, Drive E in this case, as the new open location. (Your drive letter might be different.) • Click JSP Recruiters to select the file name • Click the Open button to open the database • If a Security Warning appears, click the Options button to display the Microsoft Office Security Options dialog box • With the option button to enable the content selected, click the OK button to enable the content Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  7. Changing the Font Size • Click the Office Button to display the Office Button menu, and then click Access Options to display the Access Options dialog box • Click Object Designers to display the Object Designers options • In the Query design area, Click the Size box arrow, and then click 10 in the list that appears to change the size to 10 • Click the OK button to close the Access Options dialog box Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  8. Changing the Font Size Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  9. Creating a New SQL Query • Hide the Navigation Pane • Click Create on the Ribbon to display the Create tab • Click the Query Design button on the Create tab to create a query • Close the Show Table dialog box without adding any tables • Click the View button arrow to display the View menu • Click SQL View to view the query in SQL view Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  10. Creating a New SQL Query Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  11. Including Only Certain Fields • Type SELECT [Client Number],[Client Name],[Amount Paid],[Current Due] as the first line of the command, and then press the ENTER key • Type FROM [Client] as the second line, press the ENTER key and then type a semicolon (;) on the third line • Click the View button to view the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  12. Including Only Certain Fields Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  13. Preparing to Enter a New SQL Query • Click the View button arrow to display the View button menu • Click SQL View to return to SQL view Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  14. Including All Fields • Delete the current command, type SELECT * as the first line of the command, and then press the ENTER key • Type FROM [Client] as the second line, press the ENTER key, and type a semicolon on the third line • Click the View button to view the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  15. Including All Fields Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  16. Using Criterion Involving a Numeric Field • Click the View button arrow, click SQL View to return to SQL view, and then delete the current command • Type SELECT [Client Number],[Client Name] as the first line of the command • Type FROM [Client] as the second line • Type WHERE [Current Due]=0 as the third line and then type a semicolon on the fourth line • Click the View button to view the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  17. Using Criterion Involving a Numeric Field Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  18. Using a Criterion Involving a Text Field • Return to SQL view, delete the previous query, and type SELECT [Client Number],[Client Name] as the first line of the command • Type FROM [Client] as the second line • Type WHERE [City]='Berls' as the third line and type a semicolon on the fourth line • Click the View button to view the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  19. Using a Criterion Involving a Text Field Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  20. Using a Compound Criterion • Return to SQL view, delete the previous query, and type SELECT [Client Number],[Client Name] as the first line of the command • Type FROM [Client] as the second line • Type WHERE [City]='Tarleton’ as the third line • Type AND [Current Due]=0 as the fourth line and type a semicolon on the fifth line • Click the View button to view the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  21. Using a Compound Criterion Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  22. Using NOT in a Criterion • Return to SQL view and delete the previous query • Type SELECT [Client Number],[Client Name] as the first line of the command • Type FROM [Client] as the second line • Type WHERE NOT [City]= 'Tarleton' as the third line and type a semicolon on the fourth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  23. Using NOT in a Criterion Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  24. Using a Computed Field • Return to SQL view and delete the previous query • Type SELECT [Client Number], [Client Name],[Amount Paid] +[Current Due] AS [Total Amount] as the first line of the command • Type FROM [Client] as the second line • Type WHERE [Current Due]>0 as the third line and type a semicolon on the fourth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  25. Using a Computed Field Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  26. Sorting the Results • Return to SQL view and delete the previous query • Type SELECT [Client Number], [Client Name],[Amount Paid],[Current Due], [Recruiter Number] as the first line of the command • Type FROM [Client] as the second line • Type ORDER BY [Recruiter Number],[Amount Paid] as the third line and type a semicolon on the fourth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  27. Sorting the Results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  28. Using a Built-In Function • Return to SQL view and delete the previous query • Type SELECT COUNT(*) as the first line of the command • Type FROM [Client] as the second line • Type WHERE [Recruiter Number]='21' as the third line and type a semicolon on the fourth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  29. Using a Built-In Function Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  30. Using Multiple Functions in the Same Command • Return to SQL view and delete the previous query • Type SELECT COUNT(*), SUM([Amount Paid]) as the first line of the command • Type FROM [Client] as the second line and type a semicolon on the third line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  31. Using Multiple Functions in the Same Command Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  32. Using Grouping • Return to SQL view and delete the previous query • Type SELECT [Recruiter Number],SUM([Amount Paid]) AS [Total Paid],SUM([Current Due]) AS [Total Due] as the first line of the command • Type FROM [Client] as the second line • Type GROUP BY [Recruiter Number] as the third line • Type ORDER BY [Recruiter Number] as the fourth line and type a semicolon on the fifth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  33. Using Grouping Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  34. Restricting the Groups that Appear • Return to SQL view and delete the previous query • Click the beginning of the fourth line (ORDER BY [Recruiter Number]) and press the ENTER key to insert a new blank line • Click the beginning of the new blank line, and then type HAVING SUM([Current Due])>40000 as the new fourth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  35. Restricting the Groups that Appear Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  36. Joining Tables • Return to SQL view and delete the previous query • Type SELECT [Client Number], [Client Name],[Client]. [Recruiter Number],[First Name],[Last Name] as the first line of the command • Type FROM [Client], [Recruiter] as the second line • Type WHERE [Client].[Recruiter Number]=[Recruiter].[Recruiter Number] as the third line and type a semicolon on the fourth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  37. Joining Tables Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  38. Restricting the Records in a Join • Return to SQL view and delete the previous query • Click immediately prior to the semicolon on the last line • Type AND [Current Due] > 0 and press the ENTER key • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  39. Restricting the Records in a Join Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  40. Joining a Table to Itself • Return to SQL view and delete the previous query. • Type SELECT F.[Client Number],F.[Client Name], S.[Client Number],S.[Client Name],F.[City] as the first line of the command • Type FROM [Client] F, [Client] S as the second line • Type WHERE F.[City]=S.[City] as the third line • Type AND F.[Client Number]<S. [Client Number] as the fourth line and type a semicolon on the fifth line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  41. Joining a Table to Itself Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  42. Using a Subquery • Return to SQL view and delete the previous query • Type SELECT [Recruiter Number],[First Name],[Last Name] as the first line of the command • Type FROM [Recruiter] as the second line • Type WHERE [Recruiter Number] IN as the third line Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  43. Using a Subquery • Type (SELECT [Recruiter Number] as the fourth line • Type FROM [Client] as the fifth line • Type WHERE [City]='Berls') as the sixth line and type a semicolon on the seventh line • View the results Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  44. Using a Subquery Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  45. Using an INSERT Command • If necessary, return to SQL view and delete the existing query • Type INSERT INTO [Seminar Offerings] as the first line of the command • Type VALUES as the second line • Type ('PR11','S01',8,0) as the third line and type a semicolon on the fourth line Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  46. Using an INSERT Command • Run the query by clicking the Run button • When Access displays a message indicating the number of records to be inserted, click the Yes button to insert the records Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  47. Using an INSERT Command Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  48. Using an UPDATE Command • Delete the existing query • Type UPDATE [Seminar Offerings] as the first line of the command • Type SET [Hours Spent]=2 as the second line • Type WHERE [Client Number]= 'PR11' as the third line • Type AND [Seminar Number]= 'S01' as the fourth line and type a semicolon on the fifth line Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  49. Using an UPDATE Command Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

  50. Using a DELETE Command • Delete the existing query • Type DELETE FROM [Seminar Offerings] as the first line of the command • Type WHERE [Client Number]= 'PR11' as the second line • Type AND [Seminar Number]= 'S01' as the third line and type a semicolon on the fourth line • Run the query by clicking the Run button • When Access displays a message indicating the number of records to be deleted, click the Yes button to delete the records Microsoft Office 2007: Complete Concepts and Techniques - Windows Vista Edition

More Related