1 / 23

Advanced Accounting Information Systems

Advanced Accounting Information Systems. Day 11 Understanding the SQL Language September 18, 2009. Announcements. Assignment 2 due Monday Business Week activities continue. Catch up on topics we skipped. Database structures Ways to organize individual records in a database Hierarchical

elmo-conrad
Download Presentation

Advanced Accounting Information Systems

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. Advanced Accounting Information Systems Day 11 Understanding the SQL Language September 18, 2009

  2. Announcements Assignment 2 due Monday Business Week activities continue

  3. Catch up on topics we skipped Database structures Ways to organize individual records in a database Hierarchical Network Relational

  4. Catch up on topics we skipped Data warehouse Identify similarities between data warehouse and a fine restaurant (p. 158-159)

  5. Objectives – Chapter 6 – Understanding the SQL Language Basic SQL features and functions SELECT statement fundamentals Operators and functions DDL and DML statements

  6. Questions for today Five basic features of SELECT

  7. SQL Structure DDL DML and DQL

  8. SQL Structure SQL developed in 1980s incorporates both DDL and DML standard per ANSI and ISO Included in many mainstream DBMS DB2 Oracle MySQL Informix Microsoft SQL Server

  9. SQL Structure Basic features DDL DML Command operators Functions Transaction control

  10. SQL Structure Two basic options for executing SQL commands Interactive (or dynamic) Embedded Query mode

  11. Interactive SQL Character based interface (see Figure 6.1) Menu-driven interface (see Figure 6.2)

  12. Embedded SQL Best suited to activities that must be performed periodically SQL commands are part of an executable program Can also be used in application programs

  13. Command Basics Command syntax Command keywords (command clauses) Command parameters Results set (relational set) SELECT FROM WHERE

  14. Error messages Command did not complete successfully Why do errors return give so little information?

  15. SELECT fundamentals DBMS analyzes the declarative SQL SELECT statement and creates an access path – plan for what steps to take to respond to the query SELECT FROM WHERE ;

  16. Using SELECT to evaluate expressions Select 5 + 7 total_value

  17. Understanding Operators and Functions Unary operators Binary operators Arithmetic operators Comparison and logical operators

  18. DDL Create

  19. DML INSERT into TABLENAME VALUES Insert into INVOICE (INVNO, INVDATE, AMOUNT) VALUES (324, 5/25/09, 13.30)

  20. DML UPDATE TABLE SET COLUMN-NAME = UPDATE INVOICE SET AMOUNT = 13.50 WHERE INVNO = 325;

  21. DML DELETE FROM TABLENAME WHERE SEARCH-CONDITION DELETE FROM INVOICE WHERE INVNO = 800

  22. DQL SELECT LIST ATTRIBUTES FROM TABLE WHERE QUALIFYING CONDITIONS GROUP BY ORDER BY WHAT IS THE OTHER NAME FOR QUALIFYING CONDITIONS? USE * TO retrieve entire record in one table Retrieving other values Expression Function

  23. Questions for Monday What is the difference between comparison and logical operators? Identify and describe the use of the three basic DML commands

More Related