98-364 3 Manipulating Data

COMMIT

Marks the end of a successful implicit or explicit transaction. Makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements

SELECT

SQL command for retrieving any data from the database. Composing this statement is akin to filling in the blanks.

space

SQL treats this as an identifier, or match word, such as "value" or "select". if used in a statement, enclose the words in square brackets or double quotes for example [Game of Thrones] or "Game of Thrones"

BEGIN TRAN

using this is one of the best ways to keep data from being deleted, truncated or accidentally updated during regular maintenance tasks. BEGIN TRAN before an actual SQL statement is a good practice. Marks the starting point of an explicit, local transaction

INSERT

used to add new records in a table. should specify columns and values.

UPDATE

used to change records in a table. should specify table name, attribute, value and conditions.

WHERE

used to extract only those records that fulfill a specified criterion.

Referential Integrity

where a foreign key value matches the primary key in another table. does not allow deletion of tables unless all of the related tables are deleted using a cascading delete.

TRUNCATE

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions.

EXCEPT and INTERSECT Clauses

Returns distinct rows by comparing the results of two queries. Distinct always

UNION, EXCEPT and INTERSECT basic rules

The number and order of the columns must be the same in each of the queries in the clause. The data types you use must be compatible.

SELECT, FROM

The only required components of the SQL SELECT query.

SELECT *

chooses all columns from within a table.

JOIN

combine related data from multiple table sources. combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. a means for combining fields from two tables (or more) by using values common to each.

UNION clause

combines the result of two or more SELECT statements. combines the results of two or more queries into a resulting single set that includes ALL the rows belonging to the query in that union. Similar to FULL OUTER JOIN. (x(x)x)

INNER JOIN

most common of the join statements. selects ALL rows from both tables as long as there is a match between the columns in both tables. Any of the columns among the tables can be used in select clause. Similar to INTERSECT except works on the actual table and it's not distinct unless specified. ( (x) )

CROSS JOIN

produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if WHERE clause is not used. This kind of result is called as Cartesian Product. If, the WHERE clause is used, it functions like an INNER JOIN.

FULL OUTER JOIN

returns ALL rows from the left table and from the right table. combines the result of both LEFT and RIGHT joins Similar to UNION except works on the actual table and returns ALL rows not distinct unless specified. (x(x)x)

LEFT OUTER JOIN

returns ALL rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match. Similar to EXCEPT however works on the actual table, returns ALL rows not distinct unless specified, and includes NULL. (x( ) )

RIGHT OUTER JOIN

returns ALL rows from the right table, with the matching rows in the left table. The result is NULL in the left side when there is no match. ( ( )x)

EXCEPT

returns distinct rows from the LEFT input query that aren't output by the right input query. Similar to LEFT OUTER JOIN except it creates a temporary table and it's distinct without nulls. (without) (x( ) )

INTERSECT

returns distinct rows that are output by BOTH the left and right input queries operator. Similar to INNER JOIN except it creates a temporary table and it's distinct. (with) ( (x) )

ROLLBACK

reverses an explicit or implicit transaction to the beginning of the transaction, or to a save point inside the transaction. used to erase all data modifications made from the start of the transaction or to a save point. It also frees resources held by the transaction.

AND & OR Operators

used to filter records based on more than one condition. Combining conditions. used to gain more from a query than simply one set of parameters.

SELF JOIN

used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

FROM

used to list the tables and any joins required for the SQL statement.

NOT Clause

used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement. Can also use <> for the same results.

DROP

used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

DELETE

used to remove records in a table.

BETWEEN Clause

used to retrieve records that satisfy a range condition and also contain a value within a range of another specified value.