Section 1
Preview this deck
UNIQUE (attribute)
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (71)
Section 1
(50 cards)
UNIQUE (attribute)
specifies alternate (unique) keys, also known as candidate keys
CLOB
variable-length string data type that is used for large text values, such as documents
Data Definition Language (DDL)
syntax similar for defining data structures, especially database schemas
DELETE
removes tuples from a relation; often used with a WHERE clause that specifies a condition on which to remove tuples
EXCEPT ALL
set operation that returns all rows in the first SELECT statement without removing duplicates that are not returned by the second SELECT statement
CHECK
specifies row-based constraint that is applied to each row individually and checked whenever a row is inserted or modified
CONSTRAINT
used to specify rules for data in a table
qualify
this is what you do when you need to specify an attribute name with a particular table; represented by dot notation
SQL data types
numeric, character string, bit string, Boolean, date, and time
inner join
type of join where tuple is included in the result only if a matching tuple exists in the other relation
DISTINCT
keyword used in SELECT clause to remove duplicate tuples from query result
CASCADE ON UPDATE
change the value of the referencing foreign key attribute(s) to the updated (new) primary key value for all the referencing tuples
CASCADE ON DELETE
delete all the referencing tuples
EXISTS
boolean function added to WHERE clause to select tuples in an outer query based on the empty/non-empty result of a correlated nested query
LEFT OUTER JOIN
every tuple in the left table must appear in the result; if it does not have a matching tuple, it is padded with NULL values for the attributes of the right table
catalog
named collection of schemas
join condition
type of condition that matches a column in one table with a column in another table; usually by the same attribute name
INTERSECT
set operation that only returns the rows selected by all queries
SET NULL, CASCADE, and SET DEFAULT
types of referential triggered actions
GROUP BY
specifies the grouping attributes so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s)
selection condition
type of condition that matches a column in a single table
UPDATE
used to modify attribute values of one or more selected tuples; used with SET and WHERE
BETWEEN
comparison operator used to compare with range of values
EXCEPT
set operation that returns all rows in the first SELECT statement that are not returned by the second SELECT statement; can also be referred to as MINUS because it's applying set difference
NULL types
unknown value, unavailable/withheld value, N/A attribute
CREATE TABLE
specify a new relation by giving it a name and specifying its attributes and initial constraints
UNIQUE (query)
boolean function used in WHERE clause which returns TRUE if there are no duplicate tuples in the result
BLOB
variable-length bitstring data type that is used for large binary values, such as images
select-project-join query
query that involves only selection and join conditions plus projection attributes
base tables
relations declared through CREATE TABLE statements and are stored as a files by the DBMS
UNION
set operation that combines the result sets of 2 or more SELECT statements, removing duplicates
RIGHT OUTER JOIN
every tuple in the right table must appear in the result; if it does not have a matching tuple, it is padded with NULL values for the attributes of the left table
view / virtual tables
relations created through the CREATE VIEW statement and which may or may not correspond to actual physical files; single table that is derived from other tables
SQL schema
identified by a name and includes an authorization identifier to indicate the ownership user or account, as well as descriptors for each element
WITH
allows a user to define a table that will only be used in a particular query
grouping attributes
attributes that tuples in a partition/group share the same value of
mapping
describes the basic form of a SELECT statement and thus, all SQL queries
aggregate functions
used to summarize information from multiple tuples into a single-tuple summary; used in conjunction with grouping, but they can also be applied to all the selected tuples in a query without a GROUP BY clause
INFORMATION_SCHEMA
special schema which provides information on all the schemas in the catalog and all the element descriptors in these schemas
Data Manipulation Language (DML)
computer programming language used for inserting, deleting, and updating data in a database
LIKE
comparison used after an attribute name to match a string
ORDER BY
specifies an order for displaying the result of a query
CROSS JOIN
specifies Cartesian product
INSERT INTO
used to add a single tuple (row) to a relation (table)
PRIMARY KEY
specifies one or more attributes that make up the primary key of a relation
HAVING
specifies a condition on the groups being selected rather than on the individual tuples
FOREIGN KEY
specifies referential integrity
alias
a name that is given to a table in FROM clause in order to rename it
elements
tables, types, constraints, views, domains, and other constructs (such as authorization grants) that describe the schema
correlated queries
describes a set of queries where a condition in the WHERE clause of a nested query references some attribute of a relation declared in the outer query; nested query is evaluated once for each tuple (or combination of tuples) in the outer query
Section 2
(21 cards)