Concepts of Database Management Sixth Edition

1. Concepts of Database ManagementSixth Edition Chapter 3 The Relational Model 2: SQL

2. Concepts of Database Management 2 Objectives Introduce Structured Query Language (SQL) Use simple and compound conditions in SQL Use computed fields in SQL Use built-in SQL functions Use subqueries in SQL

3. Concepts of Database Management 3 Objectives (continued) Group records in SQL Join tables using SQL Perform union operations in SQL Use SQL to update database data Use an SQL query to create a table in a database

4. Concepts of Database Management 4 Introduction SQL (Structured Query Language) Allows users to query a relational database Must enter commands to obtain the desired results Standard language for relational database manipulation

5. Concepts of Database Management 5 Getting Started with SQL If you are completing the work in this chapter using Microsoft Office Access 2003, Microsoft Office Access 2007, or MySQL version 4.1 or higher, the following sections contain specific information about your DBMS

6. Concepts of Database Management 6 Getting Started with Microsoft Office Access 2003 and 2007 If you are using the Access 2003 or 2007 version of the Premiere Products database provided with the Data Files for this text: Tables in the database have already been created You will not need to execute the CREATE TABLE commands to create the tables or the INSERT commands to add records to the tables

7. Concepts of Database Management 7 Getting Started with Microsoft Office Access 2003 and 2007 (continued) To execute the SQL commands shown in the figures in Access 2003 Open the Premiere Products database Click Queries on the Objects bar in the Database window, and then double-click the “Create query in Design view” option Click the Close button in the Show Table dialog box Click the View button list arrow on the Query Design toolbar, and then click SQL View The query opens in SQL view, ready for you to type your SQL commands

8. Concepts of Database Management 8 Getting Started with Microsoft Office Access 2003 and 2007 (continued) To execute SQL commands shown in the figures in Access 2007: Open the Premiere Products database Click the Create tab on the Ribbon Click the Query Design button in the Other group Click the Close button in the Show Table dialog box Click the View button arrow in the Results group on the Query Design Tools tab, then click SQL View The Query1 tab displays the query in SQL view, ready for you to type your SQL commands After entered the SQL commands, click Run to show the query result Click SQL View from the View pull down window to go back to original SQL commands

9. Concepts of Database Management 9 Getting Started with MySQL MySQL-Premiere script provided with the Data Files for this text will: Activate the database Create the tables Insert the records To run a script in MySQL: Type the SOURCE command followed by the name of the file Press the Enter key

10. Concepts of Database Management 10 Getting Started with MySQL (continued) Before typing commands in MySQL, you must activate the database by typing the USE command followed by the name of the database The most recent command entered in MySQL is stored in a special area of memory called the statement history

11. Concepts of Database Management 11 Table Creation SQL CREATE TABLE command Creates a table by describing its layout Typical restrictions placed on table and column names by DBMS Names cannot exceed 18 characters Names must start with a letter Names can contain only letters, numbers, and underscores (_) Names cannot contain spaces

12. Concepts of Database Management 12 Table Creation (continued) INTEGER Numbers without a decimal point SMALLINT (small integer) Uses less space than INTEGER DECIMAL(p,q) P number of digits; q number of decimal places CHAR(n) Character string n places long DATE Dates in DD-MON-YYYY or MM/DD/YYYY form

13. Concepts of Database Management 13 Simple Retrieval SELECT-FROM-WHERE: SQL retrieval command SELECT clause: lists fields to display FROM clause: lists table or tables that contain data to display in query results WHERE clause (optional): lists any conditions to be applied to the data to retrieve Simple condition: field name, a comparison operator, and either another field name or a value

14. Concepts of Database Management 14 Simple Retrieval (continued)