Section 1
Preview this deck
data-type selection
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
4 years ago
Date created
Mar 14, 2020
Cards (87)
Section 1
(50 cards)
data-type selection
dictated by the nature of the data and by the intended use
Multilevel ordered sequence -attributes, separated by commas, after the ORDER BY clause.
cascading order sequence
The primary key attributes contain
a NOT NULL and a UNIQUE specification(entity integrity requirement)
CREATE TABLE command
Creates a new table in the user's database schema
The command sequence ends with
a semicolon
you cannot have an invalid entry in the foreign key column
referential integrity
AGGREGATE FUNCTIONS
Used with SELECT to return mathematical summaries on columns
data manipulation
(adding, modifying, deleting, and retrieving data)
NOT NULL specification is made at the table level and stored in the data dictionary, application programs can use this information
create the data dictionary validation automatically.
Having command
Restricts the selection of grouped rows based on a condition
Like operator
Checks whether an attribute value matches a given string pattern
In an enterprise RDBMS
every user ID is associated with a database schema.
enforce a first level of security by allowing each user to see only the tables that belong to that user.
Schemas
Character data are ? to process in queries
quicker
If you are working with a composite primary key, all of the primary key's attributes are contained within
parentheses and are separated with commas
The NOT NULL specifications for the attributes ensure
a data entry will be made
CREATE TABLE AS command
Creates a new table based on a query in the user's database schema
spaces are used to line up
the attribute characteristics and constraints
count
Returns the number of rows with non-null values for a given column
Date arithmetic capability is particularly useful in
billing
Create View command
Creates a dynamic subset of rows/columns from one or more tables
most important advantages of SQL is its ability to
produce complex free-form queries
Authentication is the process through which
the DBMS verifies that only registered users may access the database.
even though zip codes contain all digits, they must be defined as ? because some zip codes begin with the digit zero (0), and a numeric data type would cause the leading zero to be dropped.
character data
If you are using Oracle, issue a ROLLBACK command to
undo the changes made by the last two UPDATE statements
Integers are (whole) counting numbers
cannot be used if you want to store numbers that require decimal places.
table and attribute names are
fully capitalized
A comma is used to separate
each table element (attributes, primary key, and foreign key) definition
You can drop a table only if (foreign key integrity violation)
that table is not the "one" side of any relationship
If INTEGER is not supported
use NUMBER
in a 1:M relationship, you must always create the table
for the 1 side first
update command
Modifies an attribute's values in one or more table's rows
P_INDATE should be a ? field rather than a character field
(Julian) DATE which allows you to make simple date comparisons and to perform date arithmetic
exists operator
Checks whether a subquery returns any rows
The order of the primary key components is important because
the indexing starts with the first-mentioned attribute
Character data type
CHAR(L), VARCHAR(L) or VARCHAR2(L)
The ORDER BY clause is especially useful
when the listing order is important
CREATE SCHEMA AUTHORIZATION command
Creates a database schema
rollback command
Restores data to their original values
When adding a column, be careful not to include
NOT NULL clause for the new column
At the heart of SQL is the
query which covers both questions and actions
data definition
(creating tables, indexes, and views)
After importing tables you can reestablish the integrity rules
by using the ALTER command
a schema is a logical grouping of database objects
tables and indexes that are related to each other.
Numeric data type
NUMBER(L,D), INTEGER, SMALLINT, DECIMAL(L,D)
Where command
Restricts the selection of rows based on a conditional expression
The ON UPDATE CASCADE specification ensures that change is automatically applied to all
foreign key references throughout the system (cascade) to ensure that referential integrity is maintained
check command
Validates data in an attribute
commit command
Permanently saves data changes
SQL is a language; that is, the user specifies what must be done, but not how it is to be done.
nonprocedural
Section 2
(37 cards)