Databases Demystified- P3

Databases Demystified- P3

Thể loại: Cơ sở dữ liệu
Lượt xem: 67,714Lượ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 3-21 Example 3-11, “Multiple Joins; Columns” (top) and the 3 Database Queries to operate on a column, it will be invoked for each row and therefore return a single value for each row the query the term column used to remind us that the function is being applied to a table or view 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 to tell the RDBMS to group the rows by the values in one or more columns, and to apply the 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 column name The completed panel and query results are shown in Figure tables have a recursive built in to them, we must use a a table to itself) in order to resolve the the Employees ReportsTo column is a foreign key to in the same table and 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 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 3 Database Queries 83 Figure 3-22 Example 3-12, (top), and the query table. 6. Select the LastName column from the completed panel and query results are shown in Figure 3-23 Example 3-13, (top), and the query results example, once a query is saved in the database, it can be included in other The object types in Microsoft Access that relate strictly to Tables 3. When a table is deleted from the Microsoft Access panel: c. It remains in the database, but all data rows are belonging to the table are also 3 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 Lists tables and/or queries stored in the Lists only queries stored the 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 When (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 Ascending sequence by the first column in the Ascending sequence by the first index on the 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 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 May be applied to table columns but not to Is a special type of database query Requires that every column in a query be either an aggregate function 3 Database Queries 87 e. Involve joining a table to column name of a column in the query Is the first column name used in the formula if not provided in may be Using any column in either table Only to other tables a. Are called views in most other Are highly flexible commands for retrieval of database data e. Provide a way to generate SQL 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 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 Results when a join between two tables in a query is Results whenever a table is joined to itself d. Results when each row in one table is joined to every row in SQL SQL in this in the examples, every command and feature meets current SQL bles like the ones Oracle provides in order to run the exact included in this By all the SQL are shown in is not case sensitive for either SQL commands or database object names, 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 Oracle will not run an SQL statement unless it ends with a semicolon or a slash SQL may be divided into the following Data Query Language (DQL) that query the database but do not alter any data or database in database objects (that is, Data Language (DDL) that create and Data Control Language (DCL) that manage users have regarding the database with the query language now named SQL (System Query features required for SQL to operate on an database, as well you migrate SQL Started with Oracle SQL of SQL and the of results: SQL Plus and the SQL Plus For example, Sybase has a tool called iSQL, whereas Microsoft SQL so you may access a database and run the various SQL SQL Plus has a GUI version, which runs on Windows and | Oracle - OraHome92 | | SQL started, SQL Plus provides a Log On window that prompts for if you are running SQL Plus on the same computer that is running the 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 Unlock the HR database user account with this SQL Change the HR database user password with this SQL command (the and SQL Plus commands may be entered at the SQL> SQL Plus the format of the output of SQL such as setting page titles, for- SQL Plus commands are very useful SQL Plus command we will look at, however, is the command lists all the columns in a table or view along with the data type for command for the EMPLOYEES of the common database users have with SQL Plus is that is that the SQL 4-2 provides an Plus may be run from the Windows Command Shell using the run from the Windows Command Shell version of SQL Plus is shown in the need for a better user Oracle developed SQL 4-1 DESCRIBE command output for the EMPLOYEES 4-2 SQL Plus window with wrapped the SQL Plus Worksheet panel appears, as shown in Figure 4-4. SQL may be typed in the upper window, and the results are shown in the SQL scrolling back and forth through a history of recent 4-3 SQL Plus window, SQL Plus Worksheet panel is used for the of the examples that mat and run SQL they don’t provide an easy way for you to see the 4-4 SQL Plus Worksheet Database Objects Using Catalog provides a set of catalog views that may be queried to show the names and of all database objects available to a database user. By issuing a SELECT statement against any of these views, you may display infor- • Contains one row of for each table in the This view contains a lot of columns, but the one of most is the first column in the of selecting from the view. The SQL SELECT shown in Figure 4-5, is described in more • Contains one row of for each view in the the text of the SQL statement that forms the 4-5 Selecting from the 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 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 Click the plus sign (+) next to the name of your Oracle database (ORA9I in this example) to expand the list of database object Click the plus sign (+) next to Tables to expand the list of tables in the 9. Click the EMPLOYEES table to display its 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 type) shows a list of objects of that type in the selected schema, and by clicking the EMPLOYEES table seen a little bit of the SQL SELECT statement so far. The SELECT SELECT statement retrieves data from the SELECT Lists the columns that are to be returned in the results • FROM Lists the tables or views from which data is to be WHERE Provides for the selection of rows in the Oracle SQL, all names (tables, views, synonyms, etc.) may be written in any case, but example, both Sybase and MS SQL Server different tables: 4-1: Listing All asterisk (*) symbol may be used in place of a column list in order to select in a table or because any new column will be selected the next Note also that in SQL syntax, tables, views, and alias for a table or view) are all in the same must be unique among all tables, views, and synonyms defined in 4-7 shows the Example 4-1 SQL statement and its 4-2: Limiting Columns to specify the columns to be selected, provide a list following the columns desired in the query results, and although many times these are merely column names from tables or views, they may also be any or formula that SQL can interpret and form into data values for the that follow show you how to use formulas and constants to form query 4-8 shows the SQL for selecting the 4-8 Example 4-2, “Limiting Columns to in the query results unless the desired sequence is specified in the providing a list following the ORDER BY keyword 4-9 shows the SQL from Figure 4-8 with row 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, someone changing the query at a later time might shuffle columns used for sorting clause achieves the same query results: ORDER BY 1,2. SQL uses the WHERE clause for the selection of rows to clause, all rows found in the source tables and/or views are 4-10 shows a simple WHERE clause that selects only rows where provides the BETWEEN operator to assist in finding ranges of searching character columns, SQL provides the LIKE operator, which table provides some 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 the table in the sample HR schema contains several sales de- WHERE clause of our SELECT safer approach is to use an SQL query to find the IDs when the query is run and then use that list of IDs to find the we place the preceding SELECT statement in the WHERE clause of a query the employee of interest, we arrive at the query shown in Figure 4-15. The statement shown in Example 4-8 is known as a subselect be- tions in an upcoming SQL Tables As you learned in Example 3-8, we need to join tables (or views) need data from more than one table in our query SQL, you specify joins by listing the tables or views to be joined in a list in the of the SELECT how to match rows in the tables (or views) being 2889 rows simply by joining employees and 4-10: The Inner Join of Two DBMS to match the column in the EMPLOYEES table key) to the column in the table (the if there are 107 why did we only get 106 in Example we want all rows from the EMPLOYEES table, even if no matching row is found in the table for some 4-11: Outer Joins in table row for an employee, we want the data from table to display anyway, with the from table set to is the adjusted SQL 4-12: Limiting Join 4-10, such that only employees who work in with “Sales” in When a table has a recursive we need to join the table to itself in order to follow the in our query EMPLOYEES table has such a in that the column contains the value of In our example, every employee has a manager in the table except for the owner of the company, as shown in Figure in the query was coded as an inner join, so the one employee who rows, the entire table is one group, so only one row is returned in the If we add to the query without adding a GROUP BY but notice the placement of the asterisk under the SQL the values for the other columns (those columns being formed along with the aggregate column results (the minimum, 4-21 Example 4-15, “Mixed Aggregate and Normal Columns BY clause must include columns, just use the for the 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 allow you to add, change, and remove rows of data in the terms of the RDBMS, a is a series of one or more SQL that SQL provides support for with the COMMIT and the semicolon that ends every SQL Oracle, a is started for each database user session as soon as the user connects to the database user must issue a BEGIN statement to The INSERT statement in SQL is used to add new rows of data to may also insert rows via a view, provided the following are met: • If the view joins multiple tables, the columns by the 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 that do not appear in the view, it is to provide values for those columns and INSERT statement takes two basic forms: one where column values are in the statement itself, and the other where values are selected from a table 4-17: INSERT with VALUES INSERT with VALUES clause form of the INSERT statement can only row each time it is run because the values for that one row of data are provided in the statement the column list following the INSERT list, the column values must be provided in the correct order (that is, the same as the order in which the columns are ordered in the table), and you cannot skip any column statement may if anyone adds columns to the the column list is the and then a list of the values for the in the table or view (if a column list was not 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 table for each row retrieved from the source table or view. In the example that follows, rows in an imaginary table called are used to insert data into the table in the Data Language (DDL) 4-19: The Update UPDATE statement in SQL is used to update the data values for table (or listed in the of the statement to rows matching its the statement to update every row in the table (or view) named in the an example of the UPDATE value for the name, or any other that SQL can resolve to a column clause 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 will attempt to update every row in the table (or DELETE statement removes one or more rows from a statement may also reference a view, but only if the view is based on a single table (in other that join multiple tables cannot be columns because the statement clears all column data all the rows in the Language (DDL) Language (DDL) define the database objects but do or update any data stored within those objects (DML serve that • CREATE Creates a new database object of the type named in the DROP Drops an existing database object of the type named in the ALTER Changes the of an existing database object of the type The CREATE TABLE statement adds a new table to the TABLE ( Note that a list of columns is provided, along with the data column of this table in the “Primary Key section a example shows the ANSI standard of the CREATE example, in Oracle, the be allocated to the table, and a clause may be included to specify that will hold the table’s data. The ALTER TABLE ALTER TABLE statement may be used to change many aspects of the a database using the ALTER TABLE Adding columns to the table • Removing columns from the table • Altering the data type for existing table is an example of a using the ALTER TABLE table to define the column as a foreign key to the primary key column of the table never have duplicate database object the key value from one or more table columns to the table rows that match the key of a table based on the key for the EMPLOYEES TABLE addition to primary keys, we can force of other column(s) in a table A table may have only one primary key but in ALTER TABLE TABLE column in a whenever the column data in the table is changed or else an error message is column in the EMPLOYEES table is always greater than TABLE TABLE CREATE VIEW can be saved as a view in the the tables, views, and synonyms in the database following example creates a view for the query shown in Figure the following SQL statement will select the data from the view, which The CREATE INDEX statement creates an index on one or more table key to or deleted from the database or indexed column values are following example creates an index on the column in table: If the column values in the index will always be unique, the UNIQUE keyword may be added to the table, which creates the unique DROP statement is used to remove database objects from the database when For table the CASCADE the table is TABLE CASCADE as creating new user accounts or to the objects, such as selecting from the EMPLOYEES table or and object a database user needs in a are given to users in SQL using the GRANT following statement grants the CONNECT privilege to user OE (one of the The following statement grants the select, insert, and update on table in the HR schema to user OE. d. Data Query Language (DQL) 3. SQL Plus is a. Oracle’s to the SQL Plus Oracle’s client software for running SQL e. A set of Oracle 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 of the table or view 5. In SQL, row order in query May only be specified for columns in the query Selects rows added to a table during a time 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 an aggregate May also include ordinary May also include 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 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 are