SELECT DECK 2 Flashcards
What’s the basic form of a query in SQL?
SELECT . . . FROM . . . WHERE
What happens if you use the asterisk wildcard after the SELECT keyword in a query?
SELECT returns all columns.
Unless specified otherwise, the results of a query are returned in (Alpha, Numeric, Random) order?
Random.
What clause is used to set a sort order on returned query results?
ORDER BY
What keyword is added to ORDER BY to specify a descending sort?
DESC
What is the default sort for ORDER BY?
Ascending.
In this example, what does select_list represent? SELECT select_list FROM source_list
The list of columns from which data is returned by the query.
In this example, what does source_list represent? SELECT select_list FROM source_list
The list of tables or views from which the query gathers data.
What is a VIEW?
A VIEW is a database object which presents data gathered from other tables or views.
What is a SCHEMA OBJECT?
A SCHEMA OBJECT is a logical strcture stored within a SCHEMA.
What is a SCHEMA?
A SCHEMA is a description of data, including tables, fields, tuples and relationships within a database.
What is a SCHEMA in Oracle?
Same as a normal SCHEMA, except that in Oracle it’s also synonymous with a User.
In SQL, a . . . is the basic unit of storage which holds all user-accessible data.
TABLE
In SQL, each TABLE contains . . . that represent individual RECORDS.
ROWS
In SQL, each TABLE contains ROWS that represent individual . . .
RECORDS
In SQL, rows are composed of COLUMNS that represent individual . . . for each record.
FIELDS
In SQL, rows are composed of . . . that represent individual FIELDS for each record.
COLUMNS
What’s the Relational Algebra name for a ROW?
Tuple
What’s the Relational Algebra name for a COLUMN?
Attribute
What’s the Relational Algebra name for a TABLE?
Relation
What is an INDEX?
I’ll have to plug a definition in here.
What is a SEQUENCE?
A SEQUENCE is created to generate a serial list of unique integers for numeric columns, each of which is an ID for a single record.
What is a SYNONYM?
A SYNONYM is an alias for schema objects.
What are STORED SUBPROGRAMS?
Store subprograms are procedures and functions stored within the db.
What’s another name for STORED SUBPROGRAMS?
Schema-level subprograms.
What’s another name for SCHEMA-LEVEL SUBPROGRAM?
Stored subprograms.
What is a SCHEMA-LEVEL SUBPROGRAM?
Schema-level subprograms are procedures and functions stored within the db.
Can a STORED SUBPROGRAM be called by an external client app?
A stored subprogram can be called by any client app that can access the db.
What is a TRIGGER?
A TRIGGER is a stored subprogram that is run automatically when specified events occur in a table or view.
What is a PACKAGE?
A PACKAGE is a group of related subprograms, along with the explicit cursors and variables they use, stored in a db for continued use.
Can a PACKAGE be invoked by an external app?
A PACKAGE can be invoked by any client app that can access the DB.
In a SELECT clause, how are individual columns specified for output?
By listing the column names after the SELECT keyword and separating them with commas.
How can you specify string matching in a query?
Use the LIKE operator in the WHERE clause.
What does the percent-sign wildcard match in the LIKE operator?
Any number of chars.
How do you define the string to be matched with the LIKE operator?
Surround it, including any wildcards, with single quotes.
What operator is used to test whether a column or expression falls within a range of two boundary values?
BETWEEN
How is the BETWEEN operator used?
BETWEEN lowval AND highval
What operator is used to test whether an item is among a set of literal values?
IN
How is the IN operator used?
The list of literal values is comma-separated and enclosed by parens.
Can the IN operator test for char or date values?
Yes, but they must be delimited with single quotes.
How do you specify char or date values to test for with the IN operator?
They must be delimited with single quotes.
What are the two wildcard chars used by the LIKE operator?
% and _ (percent and underscore)
What is the meaning of the % wildcard in the LIKE operator?
It specifies zero or more chars.
What is the meaning of the _ wildcard in the LIKE operator?
It specifies exactly one char.
What happens if you use only the “%” wildcard in the LIKE operator?
The query returns every nonNULL value.
What happens if you use no wildcard for the LIKE operator’s search string?
In that case, LIKE behaves as an equality operator.
What if you want to use LIKE to search for a percent or underscore?
Escape it with a leading backslash.
What is the traditional escape char in a LIKE operator?
The backslash ( \ ).
How do you declare an alternative escape symbol in the LIKE operator?
Single-quote the char with the keyword ESCAPE after the search clause.
