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.