Databases Demystified- P3

Thể loại: Cơ sở dữ liệu
Lượt xem: 67,700Lượt tải: 9Số trang: 50

Mô tả tài liệu

Tham khảo tài liệu 'databases demystified- p3', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Tóm tắt nội dung

In reviewing the Example 3-11 results, you probably noticed that a lot of rows were Figure 3-21 Example 3-11, “Multiple Joins; Calculated Columns” (top) and the query CHAPTER 3 Forms-Based Database Queries 81 function to operate on a column, it will be invoked for each row and therefore return a single value for each row the query handles. Sometimes the term column function is used to remind us that the function is being applied to a table or view column. example of an ordinary column function is ROUND, which can be used to round The following table shows If we use an aggregate function by itself in a query, we get one row back for the en- rows in the query, we need to include a GROUP BY specification to tell the RDBMS to group the rows by the values in one or more columns, and to apply the aggregate for every row in the query, but to, at the same time, aggregate rows by cludes an aggregate function, then every column in the query results must either be formed using an aggregate function or be named in the GROUP BY column list. To create the Example 3-12 query from the Example 3-11 query, follow these steps: This column name The completed panel and query results are shown in Figure 3-22. When tables have a recursive relationship built in to them, we must use a self-join (joining a table to itself) in order to resolve the relationship. In the Employees table, the ReportsTo column is a foreign key to EmployeeID in the same table and shows must be at least one employee in the table who has no manager listed, so we need this to be an outer join if all employees in the table are to appear in the results. Follow these steps to create the query for Example 3-13: 2. Using the Show Table dialog box, add the Employees table to the query twice. 3. Microsoft Access is not going to know how to join this table to itself, so we CHAPTER 3 Forms-Based Database Queries 83 Figure 3-22 Example 3-12, “Aggregate Functions” (top), and the query results Employees table. 6. Select the LastName column from the Employees_1 table. The completed panel and query results are shown in Figure 3-23. Figure 3-23 Example 3-13, “Self-Joins” (top), and the query results (bottom) For example, once a query is saved in the Microsoft Access database, it can be included in other queries. 2. The object types in Microsoft Access that relate strictly to database a. Tables 3. When a table is deleted from the Microsoft Access Relationships panel: c. It remains in the database, but all data rows are deleted. d. Relationships belonging to the table are also deleted. CHAPTER 3 Forms-Based Database Queries 85 d. Are inherited in queries as table joins a. Lists all tables in the database and allows for the metadata about tables b. Lists only tables stored in the database c. Lists tables and/or queries stored in the database d. Lists only queries stored the database e. Provides the ability to show (display) or hide (not display) tables 6. A column in the results of a Microsoft Access query can be formed from: a. A table column b. A view column c. All the rows in the table being displayed 8. When sequencing (sorting) of rows is not included in a database query, the rows returned by the query are in: b. The order in which the rows were added to the table(s) d. Ascending sequence by the first column in the results e. Ascending sequence by the first index on the table(s) b. All the rows in the table d. All the rows in the table except those where the Region is “CA” c. Connected with a logical AND if they are in the same field’s column d. Connected with a logical OR if they are in the same field’s column The join connector between tables in a Microsoft Access query: a. May be manually created by dragging a column from one table or view to a column of another table or view e. Will cause a Cartesian product if not defined between two tables or When an outer join is used, column data from tables (or views) where no b. Displays as blank for character column types a. Combines data from multiple columns together c. May be applied to table columns but not to calculated columns d. Is a special type of database query function e. Requires that every column in a query be either an aggregate function or CHAPTER 3 Forms-Based Database Queries 87 e. Involve joining a table to itself The column name of a calculated column in the query results: c. Is the first column name used in the formula if not provided in the Tables may be joined: b. Using any column in either table (theoretically) d. Only to other tables a. Are called views in most other relational databases d. Are highly flexible commands for retrieval of database data e. Provide a way to generate SQL statements When a column is deleted from a Microsoft Access query: a. The column is only removed from the current query. b. The column is removed from all queries that reference it. c. The column is removed from the table and all queries that reference it. d. An error message is displayed if the column is used in any other queries. e. The column remains in the query but is marked so the column data will a. Results when a join between two tables in a query is not defined b. Results when a join between two tables in a query is incorrectly defined c. Results whenever a table is joined to itself d. Results when each row in one table is joined to every row in another to SQL SQL in this chapter. noted in the examples, every command and feature demonstrated meets current SQL bles like the ones Oracle provides in order to run the exact statements included in this By convention, all the SQL statements are shown in uppercase. Oracle is not case sensitive for either SQL commands or database object names, so whenever you type a data value that is to be stored in the database or is to be used to find data in the database, you must type it in the proper case. database objects. SQL ing, Oracle will not run an SQL statement unless it ends with a semicolon or a slash SQL statements may be divided into the following categories: • Data Query Language (DQL) Statements that query the database but do not alter any data or database objects. statement. stored in database objects (that is, tables). • Data Definition Language (DDL) Statements that create and modify database objects. statements. • Data Control Language (DCL) Statements that manage privileges that database users have regarding the database objects. 1982, with the query language now named SQL (System Query Language). ject features required for SQL to operate on an object-relational database, as well as Whenever you migrate SQL statements Getting Started with Oracle SQL of SQL statements and the presentation of results: SQL Plus and the SQL Plus For example, Sybase has a tool called iSQL, whereas Microsoft SQL Server count so you may access a database and run the various SQL statements demon- Oracle’s SQL Plus has a GUI version, which runs on Windows platforms, and a Programs | Oracle - OraHome92 | Application Development | SQL Plus. Once started, SQL Plus provides a Log On window that prompts for the needed if you are running SQL Plus on the same computer that is running the data- After SQL Plus has connected to the database, a window similar to the one to connect to the database as the SYSTEM user and do the following: 1. Unlock the HR database user account with this SQL command: 2. Change the HR database user password with this SQL command (the password SQL statements and SQL Plus commands may be entered at the SQL> prompt. SQL Plus Other SQL Plus commands control the format of the output of SQL statements, such as setting page titles, for- SQL Plus commands are beyond One very useful SQL Plus command we will look at, however, is the DESCRIBE This command lists all the columns in a table or view along with the data type for each. DESCRIBE command for the EMPLOYEES table. One of the common difficulties database users have with SQL Plus is that lines Another is that the SQL statements Figure 4-2 provides an example SQL Plus may be run from the Windows Command Shell using the following mand run from the Windows Command Shell version of SQL Plus is shown in Fig- Recognizing the need for a better user interface, Oracle developed SQL Plus Figure 4-1 DESCRIBE command output for the EMPLOYEES table Figure 4-2 SQL Plus window with wrapped lines Once connected, the SQL Plus Worksheet panel appears, as shown in Figure 4-4. SQL statements may be typed in the upper window, and the results are shown in the SQL statement, scrolling back and forth through a history of recent statements, and Figure 4-3 SQL Plus window, command-line version The SQL Plus Worksheet panel is used for the presentation of the examples that mat and run SQL statements, they don’t provide an easy way for you to see the names Figure 4-4 SQL Plus Worksheet panel Finding Database Objects Using Catalog Views Oracle provides a comprehensive set of catalog views that may be queried to show the names and definitions of all database objects available to a database user. By issuing a SELECT statement against any of these views, you may display infor- • USER_TABLES Contains one row of information for each table in the This view contains a lot of columns, but the one of most interest, TABLE_NAME, is the first column in the view. example of selecting everything from the USER_TABLES view. The SQL SELECT statement, shown in Figure 4-5, is described in more • USER_VIEWS Contains one row of information for each view in the the text of the SQL statement that forms the view. Figure 4-5 Selecting from the USER_TABLES view Viewing Database Objects Using For those less inclined to type SQL commands, Oracle provides a GUI tool known as should connect to the database as the SYSTEM user. with the Oracle database username provided by the DBA. Here are the exact steps to follow to get to the EMPLOYEES table as shown in 3. Click the plus sign (+) next to Databases in the left column to expand the list of databases. 4. Click the plus sign (+) next to the name of your Oracle database (ORA9I in this example) to expand the list of database object types. database. 8. Click the plus sign (+) next to Tables to expand the list of tables in the 9. Click the EMPLOYEES table to display its description in the right panel. of databases and database objects that appears in the column along the left margin of Oracle database user gets their own schema). Tables type) shows a list of objects of that type in the selected schema, and clicking did by clicking the EMPLOYEES table object). You’ve seen a little bit of the SQL SELECT statement so far. The SELECT Statement The SELECT statement retrieves data from the database. • SELECT Lists the columns that are to be returned in the results • FROM Lists the tables or views from which data is to be selected • WHERE Provides conditions for the selection of rows in the results In Oracle SQL, all database object names (tables, views, synonyms, etc.) may be written in any case, but Oracle For example, both Sybase and MS SQL Server can considered different tables: EMPLOYEES, Employees, employees. Example 4-1: Listing All Employees The asterisk (*) symbol may be used in place of a column list in order to select all columns in a table or view. data independence because any new column will be automatically selected the next Note also that in SQL syntax, tables, views, and synonyms (an alias for a table or view) are all referenced in the same way. example, must be unique among all tables, views, and synonyms defined in particu- Figure 4-7 shows the Example 4-1 SQL statement and its results. Example 4-2: Limiting Columns to Display To specify the columns to be selected, provide a comma-separated list following the describe the columns desired in the query results, and although many times these ex- pressions are merely column names from tables or views, they may also be any con- stant or formula that SQL can interpret and form into data values for the column. examples that follow show you how to use formulas and constants to form query col- Figure 4-8 shows the SQL for selecting the LAST_NAME, FIRST_NAME, Figure 4-8 Example 4-2, “Limiting Columns to Display” rows in the query results unless the desired sequence is specified in the query. SQL, providing a comma-separated list following the ORDER BY keyword does Figure 4-9 shows the SQL from Figure 4-8 with row sequencing added. • The column(s) named in the ORDER BY list do not have to be included in the query results (that is, the SELECT list). • Instead of column names, the relative position of the columns in the results may the source table or view, however. because someone changing the query at a later time might shuffle columns the columns used for sorting results. BY clause achieves the same query results: ORDER BY 1,2. SQL uses the WHERE clause for the selection of rows to display. WHERE clause, all rows found in the source tables and/or views are displayed. Figure 4-10 shows a simple WHERE clause that selects only rows where SALARY SQL provides the BETWEEN operator to assist in finding ranges of values. For searching character columns, SQL provides the LIKE operator, which compares lowing table provides some examples: Figure 4-12 shows the use of the LIKE operator to display only rows where the ure 4-13 shows a WHERE clause that selects rows having either a FIRST_NAME that the DEPARTMENTS table in the sample HR schema contains several sales de- WHERE clause of our SELECT statement. A safer approach is to use an SQL query to find the applicable department IDs when the query is run and then use that list of IDs to find the employees. If we place the preceding SELECT statement in the WHERE clause of a query that lists the employee information of interest, we arrive at the query shown in Figure 4-15. The statement shown in Example 4-8 is known as a noncorrelated subselect be- tions in an upcoming SQL example. Joining Tables As you learned previously in Example 3-8, we need to join tables (or views) whenever we need data from more than one table in our query results. In SQL, you specify joins by listing the tables or views to be joined in a comma-separated list in the FROM clause of the SELECT statement. RDBMS how to match rows in the tables (or views) being joined. get 2889 rows simply by joining employees and departments? Example 4-10: The Inner Join of Two Tables the DBMS to match the DEPARTMENT_ID column in the EMPLOYEES table (the foreign key) to the DEPARTMENT_ID column in the DEPARTMENTS table (the pri- However, if there are 107 employees, why did we only get 106 in Example 4-10? case, we want all rows from the EMPLOYEES table, even if no matching row is found in the DEPARTMENTS table for some employees. Example 4-11: Outer Joins in Oracle DEPARTMENTS table row for an employee, we want the data from the EMPLOYEES table to display anyway, with the DEPARTMENT_NAME from the DEPARTMENTS table set to null. Here is the adjusted SQL statement: SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, DEPARTMENT_NAME SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, DEPARTMENT_NAME Example 4-12: Limiting Join Results Example 4-10, such that only employees who work in departments with “Sales” in When a table has a recursive relationship, we need to join the table to itself in order to follow the relationship in our query results. The EMPLOYEES table has such a rela- tionship in that the MANAGER_ID column contains the EMPLOYEE_ID value of In our example, every employee has a manager in the table except for the owner of the company, as shown in Figure 4-19. column in the results. The query was coded as an inner join, so the one employee who rows, the entire table is considered one group, so only one row is returned in the If we add DEPARTMENT_ID to the query without adding a GROUP BY clause, be confusing, but notice the placement of the asterisk under the SQL statement. row containing the values for the other columns (those columns being formed with DEPARTMENT_ID along with the aggregate column results (the minimum, maxi- Figure 4-21 Example 4-15, “Mixed Aggregate and Normal Columns (Error)” ORDER BY clause must include calculated columns, just use the expression for the col- column in the query results and therefore does not exist at the time the query runs. The DML statement types in SQL are INSERT, UPDATE, and DELETE. commands allow you to add, change, and remove rows of data in the tables. In terms of the RDBMS, a transaction is a series of one or more SQL statements that SQL provides support for transactions with the COMMIT and ROLLBACK statements. by the semicolon that ends every SQL statement. In Oracle, a transaction is automatically started for each database user session as soon as the user connects to the database. statement. The database user must issue a BEGIN TRANSACTION statement to The INSERT statement in SQL is used to add new rows of data to tables. statement may also insert rows via a view, provided the following conditions are met: • If the view joins multiple tables, the columns referenced by the INSERT statement must all be from the same table. • The view must include all the mandatory table columns in the base table. If there are columns with NOT NULL constraints that do not appear in the view, it is impossible to provide values for those columns and therefore The INSERT statement takes two basic forms: one where column values are pro- vided in the statement itself, and the other where values are selected from a table or Example 4-17: INSERT with VALUES Clause The INSERT with VALUES clause form of the INSERT statement can only create one row each time it is run because the values for that one row of data are provided in the statement itself. Note the column list following the INSERT keyword. the list, the column values must be provided in the correct order (that is, the same as the order in which the columns are physically ordered in the table), and you cannot skip any column values. The statement may malfunction if anyone adds columns to the ta- Following the column list is the keyword VALUES and then a list of the values for the columns. fined in the table or view (if a column list was not provided). NULL may be used to assign null values to columns in the list. The INSERT with subquery form of the INSERT statement creates one row in the target table for each row retrieved from the source table or view. In the example that follows, rows in an imaginary table called EMPLOYEE_INPUT are used to insert data into the EMPLOYEES table: ate the EMPLOYEE_INPUT table in the Data Definition Language (DDL) section Example 4-19: The Update Statement The UPDATE statement in SQL is used to update the data values for table (or view) columns listed in the statement. scope of the statement to rows matching its conditions; otherwise, the statement at- tempts to update every row in the table (or view) named in the statement. shows an example of the UPDATE statement. new value for the column. umn name, or any other expression that SQL can resolve to a column value. SET clause references multiple columns, the column names and values must be in a The UPDATE statement may include a WHERE clause to limit the rows affected by the statement. UPDATE statement will attempt to update every row in the table (or view). The DELETE statement removes one or more rows from a table. The statement may also reference a view, but only if the view is based on a single table (in other words, views that join multiple tables cannot be referenced). reference columns because the statement automatically clears all column data for delete all the rows in the referenced table. Data Definition Language (DDL) Statements Data Definition Language (DDL) statements define the database objects but do not insert or update any data stored within those objects (DML statements serve that • CREATE Creates a new database object of the type named in the statement • DROP Drops (destroys) an existing database object of the type named in the statement • ALTER Changes the definition of an existing database object of the type The CREATE TABLE Statement TABLE statement adds a new table to the database. EMPLOYEES table: CREATE TABLE EMPLOYEE_INPUT ( Note that a comma-separated list of columns is provided, along with the data type EMPLOYEE_ID column of this table in the “Primary Key Constraints” section a little This example shows the ANSI standard components of the CREATE TABLE statement. For example, in Oracle, the be allocated to the table, and a TABLESPACE clause may be included to specify the tablespace that will hold the table’s data. The ALTER TABLE Statement The ALTER TABLE statement may be used to change many aspects of the definition of a database table. made using the ALTER TABLE statement: • Adding columns to the table • Removing columns from the table • Altering the data type for existing table columns Here is an example of a referential constraint definition using the ALTER TABLE statement: ALTER TABLE EMPLOYEE_INPUT EMPLOYEES table to define the DEPARTMENT_ID column as a foreign key to the primary key column (DEPARTMENT_ID) of the DEPARTMENTS table. the table never have duplicate values. cial database object containing the key value from one or more table columns and pointers to the table rows that match the key value. searching of a table based on the key value. constraint for the EMPLOYEES table: ALTER TABLE EMPLOYEE_INPUT In addition to primary keys, we can force uniqueness of other column(s) in a table A table may have only one primary key constraint, but in ALTER TABLE EMPLOYEE_INPUT ALTER TABLE EMPLOYEE_INPUT single column in a table. true whenever the column data in the table is changed or else an error message is dis- SALARY column in the EMPLOYEES table is always greater than zero: ALTER TABLE EMPLOYEES ALTER TABLE EMPLOYEES The CREATE VIEW Statement statement can be saved as a view in the database. all the tables, views, and synonyms in the database schema. The following example creates a view for the query shown in Figure 4-18: SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, DEPARTMENT_NAME Running the following SQL statement will select the data from the view, which The CREATE INDEX statement creates an index on one or more table columns. more key columns. added to or deleted from the database or indexed column values are updated. The following example creates an index on the DEPARTMENT_ID column in the EMPLOYEE_INPUT table: If the column values in the index will always be unique, the UNIQUE keyword may constraint may be added to the table, which indirectly creates the unique index. The DROP statement is used to remove database objects from the database when For table deletions, the CASCADE CONSTRAINTS til the table is re-created. DROP TABLE EMPLOYEE_INPUT CASCADE CONSTRAINTS; such as creating new user accounts or connecting to the database specific objects, such as selecting from the EMPLOYEES table or updating the DEPARTMENTS table system and object privileges a database user needs in administering a database. Privileges are given to users in SQL using the GRANT statement. The following statement grants the CONNECT privilege to user OE (one of the The following statement grants the select, insert, and update privileges on the EMPLOYEES table in the HR schema to user OE. d. Data Query Language (DQL) 3. SQL Plus is a. Oracle’s SQL3-compliant language c. Functionally equivalent to the SQL Plus Worksheet d. Oracle’s client software for running SQL e. A set of Oracle extensions to SQL a. Selects all rows in the source table or view b. Selects all columns in the source table or view e. Lists only the definition of the table or view 5. In SQL, row order in query results: e. May only be specified for columns in the query results b. Selects rows added to a table during a time interval e. Is an Oracle extension to SQL e. Returns no rows in the result set d. Returns all rows in one of the two tables An SQL statement containing an aggregate function: b. May also include ordinary columns d. May also include calculated columns e. May not involve joining multiple tables a. Must contain a column list c. May create multiple table rows e. Creates a new table An UPDATE statement without a WHERE clause: b. Updates no rows in a table c. Updates every row in a table e. Updates every column in a table A DELETE statement with a column list: b. Deletes every row in the table c. Deletes every column in the table c. Creates a database object e. May be used to drop a table column d. Restricts a database user’s privileges The Database Databases are