Database Exercises (December 10)--New Ket Network

1. For SQL statements that meet the SQL92 standard: SELECT foo,count (foo) from pokes WHERE foo>10 GROUP by foo has order by Foo, which should be executed in the order of ()

From->where->group By->having->select->order by
Analytical:

The parsing order of standard SQL is:

(1). FROM clause, assemble data from different data sources (2). The WHERE clause to filter the records based on the specified criteria (3). The GROUP BY clause divides the data into multiple groupings (4). Use an aggregate function for calculations (5). Use the HAVING clause to filter the groupings (6). Computes the expression (7) for all of the Select. Use order by to sort the result set

can also be understood according to the basic logic idea: Step-by-step reduction of the range, and then calculate the results in the selected range, processing the calculated results. 1, first determine the wide range, select the table, that is, from;2, and then step to reduce the range, from the table to determine the range of conditions, with where, then group by, then Having;3, determine the final range, in the scope of the calculation of the number of topics required to calculate, with select;4, after calculation , sort the results of the calculation with order by.

2. In which state can the database be run to prevent data loss? Archivelog status

Parsing: About archive logs: When Oracle wants to archive a filled online log file group, it creates an archive log (archived redo log).
It has the following uses for database backup and recovery: Database fallback and online and archived log files, which guarantee that all committed things can be restored in the operating system and disk failures. In case of database open and normal system use, if archive log is saved permanently, online fallback can be made and used.
The database can be run in two different ways: Noarchivelog or Archivelog mode database in the Noarchivelog mode, the online log can not be archived, if the database is run in Archivelog mode, the online log can be implemented to archive.

3. The following is a description of the SQL binding variable, which is the wrong word? D

A bound variable refers to the use of variables in SQL statements, change the value of variables to change the results of SQL statement execution
b Using bound variables, you can reduce the parsing of SQL statements and reduce the resources consumed by the database engine on SQL statement parsing.
C uses bound variables to improve programming efficiency and reliability, reducing the number of accesses to the database
D using bound variables, the query optimizer will estimate more real than the literal variables

Parsing: Binding variables are relative text variables, the so-called text variables refers to the SQL directly write query conditions, such SQL in different conditions need to be resolved repeatedly, binding variable refers to the use of variables instead of direct writing conditions, query bind value is passed at run time, and then binding execution. The advantage is to reduce the hard parsing, reduce the contention of CPU, save Shared_pool; The disadvantage is that it is difficult to use Histogram,sql optimization

4. The following statement:

SELECT  from

= Order  by Inventory.description

The implementation of the error, ask the following measures which can correct this error? A

A. Using the table's alias in the ORDER BY clause

B. Removing the alias of a table in the WHERE clause

C. Using table names instead of table aliases in the WHERE clause

D. Remove the table name from the ORDER BY clause, as long as the field name

Analytical:如果为表分配了别名，那么 Transact-SQL 语句中对该表的所有显式引用都必须使用别名，而不能使用表名。

Database Exercises (December 10)--New Ket Network