SQL review

SQL

题型：选择 简答 写sql

1.ER Diagrams,show all database the processes**[Typical DBMS processes: Listener, user, scheduler, lock manager, optimizer]**know each of this processes.be familiar with that

[Typical DBMS processes: Listener, user, scheduler, lock manager, optimizer]

Database Processor – Optimizer, Database Statistics

The Scheduler
Special DBMS program
Purpose is to establish order of operations within which concurrent transactions are executed

Lock manager
Responsible for assigning and policing the locks used by transactions

Database Query Optimizer
built-in software determines efficient way to execute SQL statement.

evaluation condation,statement transformation,Choice of optimizer goals/access paths/join orders

estimate plan,chose lowest cost

Performance Tuning DB Processing
Query Optimizer
Database Statistics
Query Processing
Indexing

2,be famillier with major statement[add trigger update delete drop ]

.

3data integrety、 entity integrety, referiential Integreety
how do we introduce that in to data base

Concurrency Control:Ensures database integrity – entity integrity, referential integrity and attribute integrity.

Primary key attributes contain both a NOT NULL and a UNIQUE specification,
RDBMS will automatically enforce referential integrity for foreign keys

Adding Primary and Foreign Key Designations When table is copied,integrity rules do not copy Primary and foreign keys are manually defined on new table

Can drop a table only if it is not the “one” side of any relationship,Otherwise, RDBMS generates an error message
-Foreign key integrity violation

trigger:Maintaining referential integrity.

4,different type of sql command [ddl dml dcl] 每个command属于哪个类？

Data definition language(DDL)

Commands that define a database, including creating, altering, and dropping tables and establishing constraints
Define schema objects, primarily table structures and indexes.

◦ CREATE
◦ ALTER
◦ DROP

Data manipulation language(DML)

◦ INSERT
◦ SELECT
◦ COMMIT
◦ UPDATE
◦ ROLLBACK
◦ DELETE

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data

5.select statement[the order of select statement,the ? how many are there in the select statement?哪6个？顺序是？

SELECT Clauses

1.SELECT(required)
◦ List the columns (and expressions) that should be returned from the query
2.FROM(required)
◦ Indicate the table(s) or view(s) from which data will be obtained
3.WHERE
◦ Indicate the conditions under which a row will be included in the result
4.GROUP BY
◦ Used with aggregate functions to summarize data
5.HAVING
◦ Filters the result of aggregate functions in grouped information, similar to WHERE
6. ORDER BY
◦ Sorts the result according to specified criteria

6.index[what are index,why are they use,（type:retreat,hash不会考）

indexes
Crucial in speeding up data access
Facilitate searching, sorting, and using aggregate functions as well as join operations
Ordered set of values that contains index key and pointers
Indexes are used when:
Indexed column appears by itself in search criteria of WHERE or HAVING clause
Indexed column appears by itself in GROUP BY or ORDER BY clause
MAX or MIN function is applied to indexed column
Data sparsity on indexed column is high
Measure of how likely an index will be used
General guidelines for indexes:
Create indexes for each attribute in WHERE, HAVING, ORDER BY, or GROUP BY clause
Do not use in small tables or tables with low sparsity
Declare primary and foreign keys so optimizer can use indexes in join operations
Declare indexes in join columns other than PK/FK

Create Index
CREATE INDEX empnum_NDX1 ON employee(emp_num);

Row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. If the statement accesses only columns of the index, then Oracle Database reads the indexed column values directly from the index, rather than from the table.

Indexes and Query Optimization
Data sparsity: number of different values a column could possibly have
Indexes implemented using:
Hash indexes
B-tree indexes
Bitmap indexes
DBMSs determine best type of index to use

When primary key is declared, DBMS automatically creates unique index Often need additional indexes
Using CREATE INDEX command, SQL indexes can be created on basis of any selected attribute Composite index
Index based on two or more attributes Often used to prevent data duplication
Indexes are often placed on table columns used for selection criteria to help Oracle identify records more efficiently.
Index scan: More efficient to use index to access table than to scan all rows in table sequentially

最后一课：what is the property of transaction? what is the purpose of transition log,
the connect bars 【very important be familiar 】

Transaction Properties (ACIDS)

Atomicity: All operations of a transaction must be completed
Consistency: Permanence of database’s consistent state
Isolation: Data used during transaction cannot be used by second transaction until the first is completed
Durability: Once transactions are committed, they cannot be undone
Serializability: Concurrent execution of several transactions yields consistent results. Multiuser databases are subject to multiple concurrent transactions

The Transaction Log

A record for the beginning of transaction

For each transaction component:
Type of operation being performed (update, delete, insert)
Names of objects affected by transaction
“Before” and “after” values for updated fields
Pointers to previous and next transaction log entries for the same transaction
Ending (COMMIT) of the transaction

If transaction committed after checkpoint: Use transaction log to redo the transaction

What Is a Transaction?

Logical unit of work that must be either entirely completed or aborted
Successful transaction changes database from one consistent state to another
One in which all data integrity constraints are satisfied
Most real-world database transactions are formed by two or more database requests

多选题：给一个query,问result/会发生什么？

difference between stored procedure and trigger?

Stored Procedures

Named collection of procedural and SQL statements.

Advantages?

network traffic,transmission,code isolation sharing,errors cost,maintenance

(reduce network traffic and increase performance.
No transmission of individual SQL statements over network
Reduce duplication by code isolation and sharing.
Minimize chance of errors and cost of application development and maintenance)

triggers

Block of PL/SQL code stored within the database.
Fired by database transaction.
Cannot be called directly.

When would a trigger be useful?

values for derived,validity checks,referential integrity,constraints,auditing,update insert call stored procedures

(Generating values for derived columns.
Performing validity checks.
Implementing business rules.
Maintaining referential integrity.
Enforce constraints.
Used to provide auditing features.
Can be used to update, insert, and call other stored procedures.)

how to make explain plan,how to update statistics,what it does

EXPLAIN PLAN FOR (your query);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Create Index
CREATE INDEX empnum_NDX1 ON employee(emp_num);

Create or Update Statistics
ANALYZE TABLE employee COMPUTE STATISTICS;

difference between decision support data and operation data[what is the difference ,why are you treat them differently]
Operational vs Decision support data:

time span: operational data cover a short time frame, decision support data longer time
granularity(level of aggregation): decision support data presented at different levels of aggregation, from highly summarized to near-atomic
dimensionality: Opdata focus on individual transactions; Dsdata focus on the effects of the transactions over time.

Collect & store operational data > aggregate operational data into decision support data > analyze decision support data to generate information >…

Operational data: mostly stored in relational db, optimized to support transactions representing daily operations

Operational data vs analytical data
- Extract operational data for BI processing.
- BI effectiveness depends on quality of data gathered at operational level
- Operational data seldom well-suited for decision support tasks
- Need reformat data in order to be useful for business intelligence

what is view, what is the advantage of view,why use view

Views

Oracle object that gives the user a logical view of data.
Logical representation of a subset of data from one or more tables.
Users can view data from multiple tables without using complex join conditions.
Hide the names of the underlying tables so the user doesn’t know where the data came from.
Stored as a SELECT statement in the Data Dictionary.

Views: Why uses views?

Mask complexity
Enhance the confidentiality of data: the SELECT and WHERE clauses can filter the expressions and data available to the user.
Some information can only be produced through views (joining views)
Can be used for reports

Views

Can use the name of a view anywhere a table name is expected in a SQL statement.
Dynamically updated view is re-created on demand each time it is invoked.

Virtual Tables: Creating a View
View: Virtual table based on a SELECT query
Base tables: Tables on which the view is based

Syntax:
CREATE VIEW viewname AS SELECT query
See all the views and the SQL query associated with that view.

SELECT * FROM ALL_VIEWS
SQL Example File
Create Views
Select data from the view

different bottleneck database, different area that could cause bottleneck

Goal: Execute queries as fast as possible; Operate at optimum level with minimal bottlenecks
Good database performance starts with good database design

Delay introduced in the processing of an I/O operation that slows the system
CPU
RAM
Hard disk
Network
Application code

difference between data warehousing and data mart

Data warehouse

Integrated, subject-oriented, time-variant and nonvolatile collection of data
Provides support for decision making
Usually a read-only database optimized for data analysis and query processing
Requires time, money, and considerable managerial effort to create
Data marts

Small, single-subject data warehouse subset
More manageable data set than data warehouse
Provides decision support to small group of people
Typically lower cost and lower implementation time than data warehouse

articulate how a query processes [database processes tuning,through 3 phases,[parsing,Execution,Fetching] what happens during these phases,

know what ddl is doing during these phases

DBMS processes queries in three phases

Parsing
DBMS parses the query and chooses the most efficient access/execution plan

(Break down query,
Transform
optimizer efficient way to access data
Validated for syntax compliance
Analyzed and decomposed into components
Prepared for execution)

Execution
DBMS executes the query using chosen execution plan

(All I/O operations indicated in access plan are executed
Locks acquired
Data retrieved and placed in data cache
Transaction management commands processed
Rows of resulting query result set are returned to client
DBMS may use temporary table space to store temporary data)

Fetching
DBMS fetches the data and sends the result back to the client
(具体内容和上面一样)

写query,给4-5个table,

give me list of student that last name is smith

写query that would join student table and the core table together

how many classes are being offered during fall

不会根据suntax扣分 但需要group 在in place