IT 214 SQL Flash Cards

aggregate functions

Calculations such as Min, Max, Avg, and Sum that are performed on a group of records

IS NULL

Finds rows that contain a null value in the specified column

IS NOT NULL

Finds rows that do not contain a null value in the specified column

ANY

If you precede the subqery by the _____ operator the condition is true only if it satisfies any value (one or more) produced by the subquery

ALL

If you precede the subquery by the ______ operator, the condition is true only if it satisfies all values produced by the subquery

database administrator

In a business organization, a person or an entire group known as the ____ is charged with managing the database.

NOT

Preceding a condition by the _________ operator reverses the truth of the original condition.

open source software

Software for which the source code is freely and publicly available

data dictionary

System catalog or catalog

SHOW TABLES

The SQL command to list all the tables in the database

SHOW INDEX

The SQL command to list the indexes associated with a table

SHOW GRANTS

The SQL command to list the privileges granted to a user

CREATE INDEX

The SQL command used to create an index

CREATE VIEW

The SQL command used to define a view

DROP VIEW

The SQL command used to delete a view

DROP INDEX

The SQL command used to delete an index

CREATE UNIQUE INDEX

The SQL command used to ensure that only unique values are allowed in a non-primary key column

GRANT

The SQL command used to give users access privileges to data in the database

REVOKE

The SQL command used to revoke the access privileges of users to data in the database

IN clause

Uses the IN operator to find a value in a group of values specified in the conditions

DROP TABLE

A SQL command used to delete database tables

foreign key

A column in one table whose values match the primary key of another table

computed column

A column that does not exist in the database but can be computed using data in existing columns

union compatible

A condition where two tables have the same number of columns and their corresponding columns have identical data types and lengths

null data value (null)

A data value used to represent situations in which an actual value is unknown, unavailable or not applicable

script file

A file that contains one or more SQL commands

index

A file that relates key values to records that contain those key values; the main mechanism for increasing the efficiency with which data is retrieved from the database

left outer join

A join in which all rows from the table on the left will be included regardless of whether they match rows from the table on the right

right outer join

A join in which all rows from the table on the right will be included regardless of whether they match rows from the table on the left

outer join

A join in which rows that do not have matching values in common columns are nevertheless included in the result table.

inner join

A join that compare the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause

transaction

A logical unit of work

subquery

A query that is embedded (or nested) inside another query. Also known as a nested query or an inner query.

nested query

A query that is embedded in another query.

defining query

A query which indicates the rows and columns to include in a view

integrity constraint

A rule for the data in the database

statement history

A special area of memory in which the most recently entered command is stored

correlated subquery

A subquery that executes once for each row in the outer query.

row-and-column subset view

A view that consists of a subset of the rows and columns in some base table

full outer join

All of the rows in both joined tables appear in the join results regardless of whether they have a match in the join condition.

view

An application program's or an individual user's picture of the database

unique index

An index that ensures the uniqueness of values in a non-primary key column

catalog

An object that stores information about the tables in the database

system catalog

An object that stores information about the tables in the database

DESC

To sort in descending order, you follow the name of the sort key with the _______ operator.

MySQL

a popular open-source DBMS product that is license-free for most applications

query

a question represented in a way that the DBMS can understand

Database Management System (DBMS)

a software program that lets you create a database and then use it to add, change, delete, sort, and view the data in a database.

INSERT

adds rows to a table.

VALUES

command followed by the specific values to be inserted in parenthesis.

UPDATE

command to change a value in a table

UPDATE

command to change rows for which a specific condition is true

DELETE

command to delete a row from a table

SHOW COLUMNS

command to list all the columns in a table

SELECT

command to view data in a table

Count

counts the number of rows in a table

data type

indicates the type of data that can be stored in a field

Database

is a structure that contains different categories of information and the relationships between these categories.

Structured Query Language (SQL)

one of the most popular and widely used languages for retrieving and manipulating database data.

USE

to activate the default database, execute __________ command followed by the name of the database

compound condition

you form a __________________ by connecting two or more simple conditions with the AND, OR, and NOT operators.

database administration

The process of managing a database

integrity support

The process of specifying integrity constraints for a database that the DBMS will enforce

Cartesian product

The product of two tables is the combination of all rows in the first table and all rows in the second table.

child

The table containing the foreign key

parent

The table referenced by a foreign key

CREATE DATABASE

This statement is used to create a database.

CREATE TABLE

This statement is used to describe the layout of a table

DELETE

To remove data from the database

ROLLBACK

To reverse changes made during current work session

COMMIT

To save changes made during current work session

difference

The ____ of two tables uses the ______ operator to create a temporary table containing the set of all rows that are in the first table but that are not in the second table.

Intersection

The _____ of two tables use the ______ operator to create a temporary table containing all rows that are in both tables

UNION

The _____ of two tables uses the _____ operator to create a temporary table containing every row that is in either the first table, the second table, or both tables

EXISTS

The _____ operator checks for the existence of rows that satisfy some criterion

DISTINCT

The _____ operator eliminates duplicate values in the results of a query.

LIKE

The ______ operator uses one or more wildcard characters to test for a pattern match.

IN

The _______ operator allows you to specify multiple values in a WHERE clause and shorthand for multiple OR conditions

OR

The ________ operator connects the simple conditions where the compound condition with be true whenever any one of the simple conditions is true.

BETWEEN

The __________operator lets you specify a range of values in a condition.

AND

The ________operator that connects simple conditions where all the simple conditions must be true in order for the compound condition to be true.

NOT NULL

The clause in a CREATE TABLE command used to indicate which columns cannot contain null values

ADD clause

The clause of the ALTER TABLE command used to add a new column

MODIFY clause

The clause of the ALTER TABLE command used to change the characteristics of a column

ORDER BY clause

The clause that allows query results to be sorted in a specified order

GROUP BY clause

The clause that groups rows based on the specified column

FROM clause

The clause that indicates the table from which to retrieve the specified columns

HAVING clause

The clause that limits a condition to the groups that are included

WHERE clause

The clause that specifies any conditions for the query

SELECT clause

The clause that specifies the columns to retrieve in the query

sort key (key)

The column on which data is to be sorted when the ORDER BY clause is used

ALTER TABLE

The command used to change a table's structure

simple condition

The condition in the preceding WHERE clause is called a _____________.

base table

The existing permanent tables in a relational database

minor sort key (secondary sort key)

The less important column

major sort key (primary sort key)

The more important column

security

The prevention of unauthorized access to a database

self-join

The process of joining a table to itself

Alias

When tables are listed in the FROM clause, you can give each table an alternative name that can be used in the rest of the statement