Section 1
Preview this deck
aggregate function
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 (120)
Section 1
(50 cards)
aggregate function
used to compute a single value from a set of rows
data type (e.g., text and numbers)
specifies the type of data that a column can hold
Database
a collection of electronically stored organized files that allows access, retrieval, and use of data
keys
uniquely identify a row or record in a table , and create a relationship between two tables
Asterisk wildcard character (*)
When used with the LIKE operator, you can match any number of occurrences of any character.
IN operator
a miscellaneous operator that provides a shorter method for specifying a range of conditions
Primary Key
a field that uniquely identifies a record in a table
Primary Key
a field whose value uniquely identifies every row in a table
Group By clause
clusters data into groups and then performs an aggregate function on the group
query
a question or command to extract data from a database.
Brackets wildcard character ( [] )
When used with the LIKE operator, you can specify a set of characters that match a character in a specific position.
client/server database
a system that divides processing between client computers and a database server
LIKE operator
A character operator. When used in conjunction with asterisks or brackets, matches parts of a value within a table.
EXP ( ) arithmetic function
returns a value of e (an exponent) raised to the power of a given number
IS NULL
used to check whether an attribute value is null
SUM ( ) aggregate function
sums values in a column
Calculated fields
lets you perform mathematical calculations on retrieved data
Row
a record that represents a collection of information relation to an item in a table
DESC keyword
Used to sort data in descending order
AVG ( ) aggregate function
returns the average in a column
syntax
rules that govern how programming language should be written
Query
a question or command users pose concerning data from the database
SEQUEL
Structured English Query Language; former name for SQL (Structured Query Language)
Grouping
separate data into logical sets
field
column in a database table that runs vertically within a table
client
a single user computer that interfaces with the multiple-user server.
COUNT ( ) aggregate function
counts the number of rows in a column
SELECT
the most commonly used statement in SQL
Normalization
three step technique used to organize data attributes in a more efficient, reliable, flexible, and maintainable structure.
DISTINCT keyword
used to display only unique values in a column
Cartesian Product
Reflects the selection of data from two or more tables without a specification of a relationship between tables; causes each row in the first table to be multiplies by the total number of rows in the second table
Alias
a substitute name for a field or value. To assign an an alias, you use the AS keyword
NOT operator
A logical operator used to match any condition opposite of the one defined.
SQL
a nonprocedural language that is used to manipulate and retrieve data from a relational DBMS (database management system); describes what needs to be processed.
subquery
A query that is embedded (or nested) inside another query. Also known as a nested query or an inner query.
Query
question or command
MIN ( ) aggregate function
finds the lowest number in a column
ORDER BY clause
Retrieves the name of one or more columns from the SELECT statement, and sorts (ascending or descending) the output.
arithmetic
function that executes a mathematical operation based on input values; provided as arguments, and return a numeric value as the result
MAX ( ) aggregate function
finds the highest number in a column
Syntax
rules that govern how you write a programming language
ASC keyword
Used to sort data in ascending order.
natural join
used to eliminate multiple occurrences of data
table
a structured file that contains rows and columns
syntax error
An error that results when an instruction does not follow the syntax rules or grammar of the programming language.
foreign key
link records of one type with those of another type
Inner join
matches values of a column in one table to the corresponding values in another table
Datepart ( )
Returns an integer containing a specific part of a date
Server
a multiple-user computer that provides shared database connection, interfacing, and processing services.
keys
Uniquely identify a row or record in a table.
Section 2
(50 cards)
Or logical operator
requires at least one expression on either side of the "OR" to be true, in order for data to be returned
VALUES
term used to tell the DBMS what to insert.
DROP COLUMN
used to remove a column from a table
IF keyword
used to check or set conditions on values of data retrieved from the database
SAVE TRANSACTION
indicates a section within a transaction to roll back to if an error occurs
ADD COLUMN
used to insert a new column
INSERT
used to add data (rows) to tables
input parameters
used to accept input from the user
field size
The maximum number of characters that can be entered into a cell (field)
INTO
used to specify a table name
underscore "( _ )"
used to match single characters
NULL
when you add rows to a table, a column can have no value
UNION ALL
returns results of two queries with duplicate rows
BETWEEN(set operator)
provides a shorter method of checking for a range of values
Syntax rules
rules that govern how a programming language must be written
outer join
A join that includes all of the rows from one table and only those rows from the other table that match the join field in the first table.
Declaration
identifying all variables that will be used in the stored procedure
Savepoint
enables you to pinpoint specific points within a transaction to roll back to. This allows you to avoid rolling back the entire transaction. You create Savepoints using SAVE TRANSACTION
create table
used to instruct the DBMS to create a new table
local variable
used to store values located within a stored procedure
DROP PROCEDURE
command to delete a stored procedure
compile
converting program commands into a set of instructions that the computer can understand
left outer join
where the query includes all of the rows from the table on the left and only those records from the table on the right that match the join field in the left table
keywords (parts of a query)
reserved words that allow you to communicate with a database
Transaction processing
used to ensure that SQL statements process fully or not at all
DROP TABLE
deletes a table
primary key
a field who value uniquely identifies every row in a table
clause (parts of a query)
a segment of an SQL statement combined to form a complete SQL statement
BEGIN TRANSACTION
indicates the beginning of a transaction
COMMIT TRANSACTION
indicates the ending of a transaction, and saves the transaction
self join
Joins a table to itself
Transaction
SQL statements that must be completed as a group in a logical order
CREATE PRODCEDURE
used to create a new procedure
ALTER TABLE
used to modify a table after it has been created
The SELECT keyword
Used to tell the database which column(s) to display.
statement (parts of a query)
keywords and data supplied within an SQL query
and (logical operator)
requires both expression on either side of the AND statement to be true in order for data to be returned
INTERSECT
given two queries, returns only the rows found by both queries
IN (set operator)
provides a shorter method for specifying a range of conditions
UNION (set operator)
returns the results of the two queries without duplicate rows
MINUS (set operator)
given two queries, returns the rows from the first query that weren't present in the second query
Query
a question or command you pose concerning data from the database
Not (logical operator)
used to match any condition opposite of the one defined.
EXECUTE
used to run a stored procedure. Parameters that need to be passed to the procedure must be specified within the EXECUTE Statement
from
used to tell the database which table to retrieve the columns from
NOT NULL
when you add rows to a table, you must insert a value in the specified column
like
parts of a value when in conjunction with wildcard characters such as asterisks or brackets
data type
Specifies the type of data that a column can hold (e.g., text and numbers).
NULL
term used to specify no value
column
represents a category of information within a table
Section 3
(20 cards)