Flashcards
SIT772.3 - STRUCTURED QUERY LANGUAGE
Cards in this deck (27)
- 1SQLStructured Query Language
- 2RelationA set of n-tuples (a1, a2... an)
where each ai is related to di
di is a data set. - 3Attributes and relation instanceEach attribute of a relation has a name , the set of allowed values for the attribute is called the domain, values usually need to be atomic (ie indivisable).
- 4r(R)Represents a relation r in the relational schema (R).
Element t of r is a tuple represented in a table. - 5Unary Operation- involving a single table
- selection (simple select statements, get values where they satisfy certain conditions)
- projection (selecting certain columns from a table)
*Selection and projection usually combined, get the values from certain columns where certain conditions exist. - 6Cartesian ProductConcatenation of every row in the first table with every row in the second (represented as R X S)
- 7Theta JoinCartesian Product with a condition applied
- 8Inner JoinA Theta Join where the is a match of the Primary and Foreign Keys.
- 9Natural JoinAn Inner Join without the repetition of redundant data (ie showing two columns with the same values, like those that would have been used in the join)
- 10Left Outer JoinInner Join plus the rows of the left-hand side table that do not satisfy the condition
- 11Right Outer JoinInner Join plus the rows of the right-hand side table that do not satisfy the condition
- 12Combination of Unary and Binary OperationsJoins combined with selections and projections.
- Union
- Intersection
- Difference - 13UnionTakes the set of rows in one table, combines them with the other and eliminates duplicates, the participating relations must be compatible
(ie have the same number of columns, same data types, same domains and same column names.
- Basically appends one table to another. - 14IntersectionSelects the set of rows that are common to each table, the participating relations must be compatible.
- 15DifferenceSelects the set of rows that are in one relation, but not the other.
(ie opposite of intersection)
The participating relations must be compatible. - 16DifferenceSelects the set of rows that are in one relation but not the other (ie opposite of intersection), again the participating relations must be compatible.
- 17DDLData Definition Language
create, alter, drop - 18DMLData Manipulation Language
Retrieve, insert, update, delete - 19DCLData Control Language
- 20Saving Table ChangesUpdates are not saved to disk until:
- Program is closed
- Database is closed
- Commit statement is executed - 21RollbackUsed to restore database to previous condition, only applicable if COMMIT command has not been used to permanently store data changes
- 22Natural JoinSame as Inner Join but eliminates duplicate columns
- 23Inner JoinOnly rows satisfying the condition from both tables
- 24Sub QueriesA query inside a query is a subquery - the query in brackets (the subquery) is executed first.
The entire query can be called a NESTED query.
Is always on the right-side of a comparison expression
A correlated sub-query has some relationship to the outer query (eg, there is a where clause that makes reference to a value from the outer query)
- The inner query in a correlated query will run for every row returned by the outer query. - 25Embedded SQLSQL that is executed from within a host language program.
- 26SELECT (syntax)SELECT (column list)
FROM (table list)
WHERE (conditions list) - 27Comparison Operators=, !=, <, >, <>, >=, <=
Information
- Description
- SIT772 Flashcards on SIT772.3 - STRUCTURED QUERY LANGUAGE, created by Amber Saxon on 03/02/2020.
- Created by
- Amber Saxon
- Views
- 0
- Rating