Section 1
Preview this deck
index
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 (55)
Section 1
(50 cards)
index
a mechanism for increasing the speed of data search and data retrieval on relations with a large number of records CREATE INDEX name ON table(column) ; DROP INDEX name;
SQL Data Types
each column of each relation has a specified data type: - CHAR (n): fixed length n-character string - VARCHAR (n): variable length character string with a maximum size of n characters - INT: integer - NUMERIC (x,y): number with x digits, y of which are after the decimal - DATE: date values (year, month, day)
conformity
the extent to which data conforms to its specified format
data quality
characterized by: - accuracy - uniqueness - timeliness - completeness - consistency - conformity
INSERT INTO
used to populate the created relations with data *the order of the columns in the CREATE TABLE statement determines the order of the values listed *data types CHAR and VARCHAR must be surrounded by quotation marks *primary key values must be entered first INSERT INTO tablename VALUES ( 1 , 'hello' );
GROUP BY
used in conjunction with aggregate functions and enables summarization across groups of related data *whatever columns are listed before the aggregate function must also be listed in GROUP BY
delete/update cascade
allows a record to be deleted/updated if its primary key value is referred to by a foreign key FOREIGN KEY (column) REFERENCES table ON DELETE CASCADE ON UPDATE CASCADE ;
Nested Query
must be used in all cases where an aggregate function is being performed except after SELECT and HAVING SELECT columns FROM tablename WHERE column < (AVG (column) FROM tablename) ;
AGGREGATE FUNCTIONS
COUNT: counts the number of records SUM: only numeric values AVG: only numeric values MIN: also date and character values MAX: also date and character values SELECT COUNT(*) FROM tablename; SELECT COUNT(DISTINCT column), AVG(column) FROM tablename;
consistency
the extent to which the data properly conforms to and matches up with the other data ALWAYS THE RESULT OF ANOTHER DATA QUALITY PROBLEM
CHECK
a user-defined constraint in a CREATE TABLE statement that determines what values from an INSERT INTO statement are entered into the database
LEFT OUTER JOIN
all records from the relation on the left part of the JOIN statement will be shown, even if they do not have a join condition value that matches a value in the other relation SELECT columns FROM atable LEFT OUTER JOIN btable ON atable.column=btable.column ;
FULL OUTER JOIN
all records from both relations will be shown SELECT columns FROM atable FULL OUTER JOIN btable ON atable.column=btable.column ;
ALTER TABLE
used in cases when you want to change the structure of the relation after it has already been created ex. adding a column ALTER TABLE tablename ADD (column DATA TYPE) ; ex. dropping a table ALTER TABLE tablename DROP (column) ;
IS NULL
used in queries that contain comparisons with an empty value in a column of a record SELECT * FROM table WHERE column IS NULL ;
UNION
combines the two sets and eliminates the duplicates
union compatible
when sets of columns contain the same number of columns and the data types of the columns in one set match the data types of the columns in the other set
IN
used for comparison of a value with a set of values SELECT columns FROM tablename WHERE column IN (nested query) ;
delete/update restrict
does not allow a record to be deleted/updated if its primary key value is referred to by a foreign key. this is the default setting in SQL
Data Manipulation Language (DML)
used to manipulate the data within the database. includes commands for inserting, modifying, deleting, and retrieving data
accuracy
the extent to which data correctly reflects the real world instances it is supposed to depict ex. spelling typo
delete/update set-to-null
all of the records where the foreign key value refers to the primary key of the record being deleted/updated is, the foreign key is set to null
uniqueness
each real-world instance is represented only once in the data collection ex. duplicates
MINUS
shows the items that appear in the first set but not the second
view
a mechanism in SQL that allows the structure of a query to be saved in the RDBMS *CREATE VIEW and DELETE VIEW CREATE VIEW name AS query ; DROP VIEW name ;
CREATE TABLE
used for creating and connecting relational tables *create tables with primary keys first CREATE TABLE tablename ( column DATA TYPE NOT NULL, PRIMARY KEY (column), FOREIGN KEY (column) REFERENCES table (column) );
timeliness
the degree to which data is aligned with the proper time window in its representation of the real world ex. out-of-date
WHERE operators
= equal to < less than > greater than <= less than or equal to >= greater than or equal to != or <> not equal to
Data Definition Language (DDL)
used to create and modify the structure of the database. enables the implementation of the relational schema as an actual relational database ex. commands: CREATE, ALTER, DROP
linear search
finds a particular value by checking elements sequentially and one at a time until the searched-for value is found
INTERSECT
only shows the items that appear in both sets
completeness
the degree to which all the required data is present in the data collection ex. missing value that should not be
delete/update set-to-default
all of the records where the foreign key value refers to the primary key of the record being deleted/updated is, the foreign key is set to a predetermined default value
UPDATE
used for modifying data stored in database relations UPDATE tablename SET column= x WHERE column= condition ;
LIKE
used in conjunction with WHERE to retrieve records whose values partially match a certain criteria SELECT * FROM tablename WHERE column LIKE '%Boot%' ;
WHERE
determines which rows should be retrieved and which rows should not
DISTINCT
used directly after SELECT to prevent repeating items multiple times SELECT DISTINCT column FROM tablename;
JOIN
facilitates the querying of multiple tables SELECT columns FROM table1, table2 WHERE table1.column=table2.column;
EXISTS
used to check if the result of the inner correlated query is empty SELECT * FROM table WHERE EXISTS (nested query)
DELETE
used for deleting data stored in database relations DELETE FROM tablename WHERE column=condition ;
ORDER BY
used to sort the results of a query by one or more columns *default is ascending SELECT columns FROM tablename ORDER BY column DESC;
RIGHT OUTER
all records from the relation on the right part of the statement will be shown, even in they do not have a match on the left side of the statement SELECT columns FROM atable RIGHT OUTER JOIN btable ON atable.column=btable.column ;
DROP TABLE
used to remove a table from a database *drop tables with foreign keys first DROP TABLE tablename;
SELECT
the most commonly used SQL statement, used for the retrieval of data from the database relations SELECT columns FROM tablename;
self-JOIN
when a relation containing a foreign key refers to its own primary key. must use aliases
order of SELECT keywords
SELECT FROM WHERE GROUP BY HAVING ORDER BY
HAVING
determines which groups will be displayed and which groups will not be displayed in the result of the query *HAVING is to groups as WHERE is to columns
CONSTRAINT
used in a CREATE TABLE statement to name a primary or foreign key
INNER JOIN
same thing as a regular join statement. only shows records that have a match on both sides of the relation
set operators
union, intersection, and difference. these are used to combine the results of two or more SELECT statements that are union compatible
Section 2
(5 cards)