(SQL) Advance Database 2 (Prelim)

Common Table Expression (CTE)

specifies a temporary named result set.

WITH

CTE Can be defined using the ____ operator

SELECT, INSERT, UPDATE, or DELETE

Can be referenced within another _____, ______, ______, statement

ORDER BY, INTO, and OPTION

Clauses like _____, _____, ____ cannot be used in CTE queries

Non-Recursive CTE

•This type of CTE doesn’t use repeated procedural loops/recursion

•This type of CTE Easier to understand

Recursive CTE

This type of CTE uses recursion

•This type of CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy

Subqueries

A query (SELECT statement) inside another query

In WHERE clause

work as part of the row selection process.

• A subquery often found in the _______ clause

• Also called nested subqueries.

In FROM clause

Returns a temporary or virtual table

• Useful in data warehousing application

• Also called an inline view or derived table

In SELECT clause

a subquery that is nested in the list of another SELECT statement

In IN operator

allows users to match one item from any of those in the list

ANY operator

This operator returns true if any of the subquery values satisfy the condition

ALL operator

This operator returns true if all the subquery values meet the condition

Correlated Subqueries

are used to select data from a table referenced in the outer query.

• Cannot be executed independently as a simple subquery

• A ______ subquery is executed repeatedly, once for each row evaluated by the outer query.

• Also known as a repeating subquery

In EXISTS operator

used to check whether a subquery produces any rows of query results.

• Commonly used with correlated subqueries

Views

a virtual table that is constructed from other tables or views and saved as an object in the database

Index

Used to speed up searches/queries, resulting in high performance

Frequency of search

creating an index to a particular column that is frequently searched can give performance benefits.

Size of table

putting an index on a relatively large table that contains a great number of rows can improve performance.

Number of updates

a database that is frequently updated should have fewer indexes as it slows the performance of inserts, updates, and deletes

Space considerations

create an index only if necessary, because indexes take up spaces within the database.

Single-Column Indexes

This type of index based on only one table column

Ex: CREATE INDEX ix_CustomerID ON Customers (CustomerID)

Unique Indexes

This type of index does not allow any duplicate values to be inserted into the table

Ex:

CREATE UNIQUE INDEX ix_ItemID ON Items (ItemID)

Composite Indexes

This type of index based on two or more columns of a table.

Ex: CREATE INDEX ix_OrderRecords ON Orders (OrderID,OrderDate, CustomerID, ItemID)

Dropping Index

deleting an index can be done using the DROP command.

Ex.DROP INDEX Orders.ix_OrderRecords