Database Systems Final Exam

SQL

is a domain-specific programming language designed for managing and communicating with data held in a relational database management system

SQL is used for:

-creating databases

-adding, modifying and deleting database structures

-inserting, deleting and modifying records in databases

-querying databases

Data Definition Language (DDL)

used to create and modify the structure of the database

example: CREATE ALTER or DROP

Data Manipulation Language (DML)

used to insert, modify, delete and retrieve data

example: INSERT INTO UPDATE or DELETE

Data Retrieval Language (DRL)

used to query or retrieve data from a database

example: SELECT

Data Control Language (DCL)

used for data access control

Transaction Control Language

used for managing database transactions

Keywords

These have a very specific meaning in the language. The statement must have at least one. These often define the operation that is performed

Identifiers

These refer to the names of the tables and columns that are called or manipulated

Null Value

is a marker for data that has not been entered or is missing, unknown, or inapplicable

Not Null Constraint

is only a column constraint and not a table constraint; if you don't specify a _____, the column will accept null values by default

Unique Constraint

forces each value of a column or table to be unique

Check Constraint

allows for limiting a particular column based on a particular value rules

These include:

minimum or maximum value

specified value

range of values

ALTER table

provides the ability to:

-add/drop a column

-alter a column's data type

-rename a column

-rename a table

-add, alter or drop a column's default value or null ability constraint

-add, alter or drop column or table constraints such as primary key, foreign key, unique and check constraint

Select Statement

is the basic building block of any data query request. It is command that pulls specific data from a particular table within the database highlighted

Limit Statement

it limits the number of rows that are shown

Order By

is used to sort by data in a particular column or columns

Select Distinct

eliminates duplicate values

Where

can be used in combination with boolean comparators for columns that are numeric or integers

Like

allows to find similar text, but is case sensitive

%

wildcard placeholder for text used with LIKE or ILIKE statements

ILIKE

same as LIKE statement but not sensitive to capitalization

IN

equivalent to multiple OR statements

Between

can be used to find a range instead of using > and <

NOT

can negate IN, Between, LIKE or conditional statement

derived column

is the result of a calculation and is created with a SELECT --- clause expression that is something other than a simple reference to the column

COALESCE

replaces NULL values with another value

CAST

changes the data type of a column in the query

Concatenating Strings

combining strings together

Trim

removes trailing and leading blank spaces

Upper

displays columns as UPPER case

Lower

displays column as lower case

INITCAP

displays column as proper case

Length

Returns the length in characters of a string

Substring

is any sequence of contiguous characters from the source string

Right

pulls the right n characters of a string

Left

returns left n characters of a string

Mid

Returns the characters n from the left and o from the right

Position

returns the position of a given string

replace

replaces value 1 with value 2 in a column

count

returns the number of times a column appears

count distinct

returns the number of unique columns in a array

avg

calculates the average of a column

sum

adds the values in a column

max

returns the maximum value in a column

min

returns the minimum value in a column

Group By

returns a single value for each value in the column(s) specified in the ____ clause

Having

if you want to use a filter that contains an aggregate function, you can't use WHERE

Date_Part

pulls out a specific date part from a date

Extract

same as date_part

current_date/time

returns the current date or current time

age

returns the difference between two date/time columns

make_date/time

creates a date or time

NOW

returns the current date and time

Date_Trunc

truncates date/time to specified interval

Joins

allows you to combine data from two relational tables for additional insights

Inner Join

selects all rows from both tables as long as there is a match between the columns in both tables

Left (or Right) Join

returns all rows from the left (or right) table, even if there are no matches in the right (or left) table

Full joins

selects all rows from both tables even if there is no match

Cross Joins

returns all rows from the first table in which each row from the first table is combined with all rows from the second table

natural joins

special case of inner join; compares all the columns in one table with corresponding columns that have the same name in the other table

Where

It's possible to use the WHERE syntax instead of ON

Set Operations

UNION, INTERSECT, & EXCEPT

used to combine the results of two or more SELECT statements that are union compatible

Union

used to combine the union compatible results of two SELECT statements by listing all rows from the result of the first SELECT statement and all rows from the result of the other SELECT statement

Intersect

combines the results of two SELECT statements that are union compatible by listing every row that appears in the result of BOTH the SELECT statements

Except

combines the results of two queries into a single result that has the rows that belong to only the first query

Subqueries

when you embed one SELECT statement inside another

nested subquery

is a type of subquery where the outer query uses the results of the inner query to select data

referential integrity constraint

in each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null

Delete Restrict

option does not allow a record to be deleted if its primary key value is referred by a foreign key

Delete Cascade

option allows a record to be deleted if its primary key value is referred to by a foreign key value

Delete Set-to-Null

option allows a record to be delete if its primary key value is referred to by a foreign key value

returns it to a null value

Delete Set-to-Default

option allows a record to be deleted if its primary key value is referred to by a foreign key value

allows you to pick the number

Update Restrict

option does not allow a record to be updated if its primary key value if its primary key value is referred to by a foreign key

Update Cascade

option allows a record to be updated if its primary key value is referred to by a foreign key value

Update Set-to-Null

option allows a record to be updated if its primary key value is referred to by a foreign key value

sets value to null

update set-to-default

option allows a record to be updated if its primary key value is referred to by a foreign key value

allows you to set the number

Index

mechanism for increasing the speed of data search and data retrieval on relations with a large number of records

accuracy

the extent to which data correctly reflects the real-world instances it is supposed to depict

uniqueness

requires each real-world instances to be represented only once in the data collection

completeness

the degree to which all the required data is present in the data collection

consistency

the extent to which the data properly conforms to and matches up with the other data

timeliness

the degree to which the data is aligned with the proper time window in its representation real world

conformity

the extent to which the data conforms to its specified format

preventive data quality actions

actions taken to preclude data quality problems

corrective data quality actions

actions taken to correct the data quality problems

data entry forms

enable data input and retrieval for end users

data manipulation forms & search forms

can be used to delete and update items as well

reports

present the data and calculations on the data from one or more tables from the database in a formatted way

application development component

used to develop front-end applications

view materialized

saving a view as an actual physical table

catalog

the data dictionary created by the DBMS

authentication

login procedure using user ID and password

access privileges

assigned to the database user account

authorization matrix

implements the access privileges

provided by the DBMS but managed by the DBA

encryption

scrambles data so that information becomes unreadable unless someone has an encryption key

encryption key

information scrambling algorithm

decryption key

reverts the informations to its original state

backup

saving additional physical copies of the data

recovery

recovering the content of the database after a failure