Chapter 7 Introduction to Structured Query Language (SQL), Database Design Chapter 7 Introduction to SQL, CH 8 ITSS 4300, Advanced SQL, ITSS 4300 Chapter 10
Section 1
Preview this deck
Atomicity
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 (220)
Section 1
(50 cards)
Atomicity
-All operations of a transaction must be completed -If not, the transaction is aborted
Command §
CREATE SCHEMA AUTHORIZATION {creator}; § Seldom used directly
Common SQL Data Types
Numeric NUMBER(L,D) or NUMERIC(L,D) Character CHAR(L) • VARCHAR(L) or VARCHAR2(L) •DATE Date
Primary Key and Foreign Key §
Primary key attributes contain both a NOT NULL and a UNIQUE specification § RDBMS will automatically enforce referential integrity for foreign keys § Command sequence ends with semicolon §
DEFAULT
Assigns value to attribute when a new row is added to table.
ON Clause
Performs the equality check for common columns of the two tables
Problems in Concurrency Control
-lost update -uncommitted data -inconsistent retrievals
Authentication
Process DBMS uses to verify that only registered users access the data. Required for the creation tables.
Inconsistent retrievals
-Occurs when a transaction accesses data before and after one or more other transactions finish working with such data 1. EX: -First transaction accesses data -Second transaction alters the data -First transaction accesses the data again 2. Transaction might read some data before they are changed and other data after changed 3. Yields inconsistent results
ANSI SQL
Allows use of following clauses to cover CASCADE, SET NULL, or SET DEFAULT; ON DELETE and ON UPDATE.
Categories of SQL function §
Data definition language (DDL) § Data manipulation language (DML) §
Numeric
NUMBER (L,D) or NUMBERIC (L,D)
Natural join (2)
an equi-join in which one of the duplicate columns is eliminated in the result table Only return records from each tables that have a match in the join condition with the other table
Equi-join
a join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table
Syntax to create table §
CREATE TABLE tablename();
Transaction
1. Logical unit of work that must be entirely completed or aborted 2. Consists of: -SELECT statement -Series of related UPDATE statements -Series of INSERT statements -Combination of SELECT, UPDATE, and INSERT statements EX: Accounting
Subquery example
SELECT FROM WHERE .... IN (SELECT DISTINCT CustomerID FROM Order_T) *distinct operator ensures that it only appears one in the final result*
Structured Query Language (SQL) §
Nonprocedural language with basic command vocabulary set of less than 100 words § Differences in SQL dialects are minor
Transaction Log
1. Keeps track of all transactions that update the database 2. DBMS uses the information stored in a log for: -Recovery requirement triggered by a ROLLBACK statement -A program's abnormal termination -A system failure
Serializability
Ensures that the schedule for the concurrent execution of several transactions should yield consistent results
Outer join
a join in which rows that do not have matching values in common columns are nonetheless included in the result table
Features of table creating command sequence §
NOT NULL specification § UNIQUE specification §
Creating Table Structures §
Use one line per column (attribute) definition § Use spaces to line up attribute characteristics and constraints § Table and attribute names are capitalized §
Isolation
Data used during transaction cannot be used by second transaction until the first is completed
Joining vs. Subquery technique (2 each)
Joining technique- useful when data from several relations are to be retrieved and displayed, and the relationships are not necessarily nested * combines all rows from each table, then removes rows that dont match the conditions* Subquery technique - allows you to display data from only the tables mentioned in the outer query. *executes then returns a result and then the result is sent to the outer query*
Lost update
Occurs in two concurrent transactions when: -Same data element is updated -One of the updates is lost
Self Join
Usually used with unary relationships Same tables is used on both sides of the join; distinguished using aliases
The Database Schema §
Logical group of database objects related to each other §
Structures query language
Categories: data definition, data manipulation. Nonprocedural language with basic command vocabulary set of less than 100 words. Differences in SQL dialects are minor.
Union join
includes all data from each table that was joined
Primary key
Attributes contain both a NOT NULL and a UNIQUE specification.
Consistency
Permanence of database's consistent state
Creating table structures
Use one line per column definition. Spaces line up attribute characteristics and constraints. Names are capitalized. Features create command sequence. Syntax to create table.
ANSI SQL allows use of following clauses to cover CASCADE, SET NULL, or SET DEFAULT §
ON DELETE and ON UPDATE
Character
CHAR (L), VARCHAR(L), VARCHAR2(L)
Tasks to be completed before using a new RDBMS
Create a new database structure; RDBMS creates physical files that will hold database; differed from one RDBMS to another.
Durability
Ensures that once transactions are committed, they cannot be undone or lost
Concurrency Control
-Coordination of the simultaneous transactions execution in a multiuser database system -Objective - Ensures serializability of transactions in a multiuser database environment
Tasks to be Completed Before Using a New RDBMS Create database structure
RDBMS creates physical files that will hold database § Differs from one RDBMS to another §
Authentication
Process DBMS uses to verify that only registered users access the data § Required for the creation tables § User should log on to RDBMS using user ID and password created by database administrator
Foreign key
RDBMS will automatically enforce referential integrity foreign keys.
Uncommitted data
Occurs when: -Two transactions are executed concurrently -First transaction is rolled back after the second transaction has already accessed _______
UNIQUE
Ensures that all values in column are unique.
Join
a relational operation that causes two or more tables with a common domain to be combined into a single table or view
NOT NULL
Ensures that column does not accept nulls.
Database schema
Logical group of database objects related to each other. Command: CREATE SCHEMA AUTHORIZATION {creator};
LEFT OUTER JOIN clause (2)
Causes customer data to appear even if there is no corresponding order data * will include customer rows with no matching order rows unlike inner join*
§
Subquery (def + options3)
Placing an inner query (SELECT statement) inside an outer query Options: In a condition of the WHERE clause As a "table" of the FROM clause Within the HAVING clause
Inner Join Clause (2)
Alternative to WHERE clause and is used to match the PK and FK Only return rows from each table that have matching rows in the other
Section 2
(50 cards)
Wildcard character (*)
Substitute for other characters/command
ROLLBACK: command to restore the database
Syntax- ROLLBACK; Undoes the changes since last COMMIT command
Syntax to create SQL indexes § CREATE INDEX
indexname ON tablename(); §
UPDATE: command to modify data
UPDATE table name SET column name=expression [column name=expression] [WHERE condition list];
SQL Constraints •
Ensures that column does not accept nulls NOT NULL • Ensures that all values in column are unique UNIQUE • Assigns value to attribute when a new row is added to table DEFAULT • Validates data when attribute value is entered CHECK
WHERE condition
Specifies the rows to be selected
INSERT: command to insert data into table
Syntax- INSERT INTO table name VALUE(); Used to add table rows with NULL and NOT NULL attributes.
UPDATE: Command to modify data •
Syntax - UPDATE tablename SET columnname = expression [, columnname = expression ] [WHERE conditionlist ];
SQL 2008 enhancements (3)
added statistical functions new data types XML processing capabilities
Composite index: §
Is based on two or more attributes § Prevents data duplication §
Two-phase locking governing rules
-Two transactions cannot have conflicting locks -No unlock operation can precede a lock operation in the same transaction -No data are affected until all locks are obtained
Levels of locking
1. Database-level lock 2. Table-level lock 3. Page-level lock -Page or diskpage: Directly addressable section of a disk 4. Row-level lock 5. Field-level lock -Look at slides #25-#28
COMMIT: command to save changes
Syntax- COMMIT [WORK]; Ensures database update integrity.
CASE Syntax (2)
Can be constructed using an expression that equates to a value or a predicate. Based on threevalue logic ( true, false, don't know) NULLIF & COALESCE are the keywords for CASE expression
Scheduler
1. Establishes the order in which the operations are executed within concurrent transactions -Interleaves the execution of database operations to ensure serializability and isolation of transactions 2. Based on concurrent control algorithms to determine the appropriate order 3. Creates serialization schedule -Serializable schedule: Interleaved execution of transactions yields the same results as the serial execution of the transactions
Deadlock
Occurs when two transactions wait indefinitely for each other to unlock data -Known as deadly embrace
DELETE: Command to delete •
Syntax - DELETE FROM tablename • [WHERE conditionlist ];
Inserting Table Rows with a SELECT Subquery
Used to add multiple rows using another table as source §
Functions
routines that return values and take input parameters called explicitly by user; return a value
ROLLBACK: Command to restore the database •
Syntax - ROLLBACK; • Undoes the changes since last COMMIT command
Routines
Program modules that execute on demand Can be either procedures or functions
Avoiding deadlocks
1. Control techniques -Deadlock prevention -Deadlock detection -Deadlock avoidance 2. Choice of deadlock control method depends on database environment
CHECK
Validates data when attributes value is entered.
Two-phase locking phases
1. Growing phase - Transaction acquires all required locks without unlocking any data 2. Shrinking phase - Transaction releases all locks and cannot obtain any new lock
Data Manipulation Commands INSERT: Command to insert data into table •
Syntax - INSERT INTO tablename VALUES(); • Used to add table rows with NULL and NOT NULL attributes
SELECT: Command to list the contents •
Syntax - SELECT columnlist FROM tablename ; • Wildcard character (*): Substitute for other characters/command
Transaction (4)
A discrete unit of work that must be completely processed or not processed at all -May involve multiple updates -If any update fails, then all other updates must be cancelled *Atomic; all complete or none of it*
COMMIT: Command to save changes •
Syntax - COMMIT [WORK]; • Ensures database update integrity
Correlated (4) vs. noncorrelated (3) Subqueies
Noncorrelated: - dont depend on data from outer query - execute once for the entire outerquery * inner query executes once then the outer query* Correlated: - Use data from outer query - Execute once for each row in outerquery -can use EXISTS operator *Reference to a table in the outerquery* *Outerquery executes for each row of the outerquery; innerquery executes once*
Union Queries (2)
Combine the output (union of multiple queries) together into a single result table *data types of the attributes in the SELECT clauses of both queries must be identical*
Query efficiency considerations (3)
1. Instead of SELECT *, identify the specific attributes in the SELECT clause -->this helps reduce network traffic of result set 2. Limit the number of subqueries; try to make everything done in a single query if possible 3. If data is to be used many times, make a separate query and store it as a view
SQL Indexes §
When primary key is declared, DBMS automatically creates unique index §
Procedures
routines that do not return values and can take input or output parameters called explicitly by user
Syntax §
INSERT INTO tablename SELECT columnlist FROM tablename §
Two-phase locking(2PL)
1. Defines how transactions acquire and relinquish locks 2. Guarantees serializability but does not prevent deadlocks
DROP INDEX
Index name; syntax to delete an index.
Guidelines for better Query design (7)
1. Understand how indexes are used in query processing 2. Keep optimizer statistics up-to-date 3. Use compatible data types for fields and literals 4. Write simple queries 5. Break complex queries into multiple simple parts 6. Don't nest one query inside another query 7. Don't combine a query with itself
SELECT: command to list the contents
Syntax- SELECT columnist FROM table name;
CREATE INDEX
Index name ON table name (); syntax to create SQL indexes.
SQL indexes
When primary key is declared, DBMS automatically creates unique index.
SQL commands for transactions (3)
BEGIN TRANSACTION/END TRANSACTION -Marks boundaries of a transaction COMMIT -Makes all updates permanent ROLLBACK -Cancels updates since the last COMMIT
Syntax to delete an index § DROP INDEX
indexname;
WHERE condition •
Specifies the rows to be selected
Composite index
Based on two or more attributes, prevents data duplication.?
Data Dictionary Facilities (3)
System tables that store metadata Users usually can view some of these tables Users are restricted from updating them
Triggers
Routines that execute in response to a database event (INSERT, UPDATE, or DELETE) Not called explicitly; event driven
Lock manager
Responsible for assigning and policing the locks used by the transactions
SELECT subquery
INSERT INTO table name SELECT column list FROM table name; Used to add multiple rows using another table as source.
DELETE: command to delete
Syntax- DELETE FROM table name [WHERE conditionalist];
Tips to develop queries (8)
1. Be familiar with the data model (entities and relationships) 2. Understand the desired results 3. Know the attributes desired in results 4. Identify the entities that contain desired attributes 5. Review ERD 6. Construct a WHERE equality for each link 7. Fine tune with GROUP BY and HAVING clauses if needed 8. Consider the effect on unusual data
Section 3
(50 cards)
Dynamic SQL
Ability for an application program to generate SQL code on the fly, as the application is running
The Rule of Precedence
Establish the order in which computations are completed. Performs operations within parentheses, power operations, multiplications and divisions, additions and subtractions.
Copying parts of tables
SQL permits copying contents of selected table columns. Data need not be reentered manually into newly created tables. Rows are added to new table using rows from another table.
Changing a column's data characteristics
Use ALTER; changes in column's characteristics are permitted if changes do not alter the existing data type. Syntax- ALTER TABLE table name MODIFY(column name(characteristic));
Subquery
Query embedded/nested inside another query.
Comparison Operators: Computed Columns and Column Aliases §
SQL accepts any valid expressions/formulas in the computed columns § Computed column, an alias, and date arithmetic can be used in a single query
Embedded SQL
Including hard-coded SQL statements in a program written in another language such as C or Java
IN
Checks whether attribute value matches any value within a value list
The Rule of Precedence
Establish the order in which computations are completed
Advanced data updates
If a relationship is established between entries and existing columns, the relationship can assign values to appropriate slots. Arithmetic operators are useful.
Copying Parts of Tables §
SQL permits copying contents of selected table columns § Data need not be reentered manually into newly created table(s) § Table structure is created § Rows are added to new table using rows from another table
Dropping a column §
Use ALTER and DROP § Some RDBMSs impose restrictions on the deletion of an attribute
EXISTS
Checks if subquery returns any rows.
Between
Checks whether attribute value is within a range
Adding a column §
Use ALTER and ADD § Do not include the NOT NULL clause for new column §
Changing Column's Data Characteristics §
Use ALTER to change data characteristics § Changes in column's characteristics are permitted if changes do not alter the existing data type § Syntax § ALTER TABLE tablename MODIFY (columnname(characterstic)) ;
Joining Tables With an Alias §
Alias identifies the source table from which data are taken § Any legal table name can be used as alias § Add alias after table name in FROM clause § FROM tablename alias
Following syntax enables to specify which rows to select
SELECT columnlist § FROM tablelist § [WHERE conditionlist ];
ALTER TABLE
To make changes in the table structure; add, modify, drop. Used to add table constraints and remove table constraints.
Deleting a Table from the Database
Can drop a table only if it is not the one side of any relationship § RDBMS generates a foreign key integrity violation error message if the table is dropped
Optional WHERE clause
Adds conditional restrictions to the SELECT statement
Ordering a Listing §
ORDER BY clause is useful when listing order is important § Syntax - SELECT columnlist FROM tablelist [WHERE conditionlist ] [ORDER BY columnlist [ASC | DESC]]; § Cascading order sequence : Multilevel ordered sequence § Created by listing several attributes after the ORDER BY clause
Joining Database Tables §
Performed when data are retrieved from more than one table at a time § Equality comparison between foreign key and primary key of related tables § Tables are joined by listing tables in FROM clause of SELECT statement § DBMS creates Cartesian product of every table in the FROM clause
Grouping Data §
Frequency distributions created by GROUP BY clause within SELECT statement § Syntax - SELECT columnlist FROM tablelist [WHERE conditionlist ] [GROUP BY columnlist ] [HAVING conditionlist ] [ORDER BY columnlist [ASC | DESC]];
Alias
Alternate name given to a column or table in any SQL statement to improve the readability. Computed column, can be used in a single query.
Special Operators
BETWEEN • Checks whether attribute value is within a range IS NULL • Checks whether attribute value is null LIKE • Checks whether attribute value matches given string pattern IN • Checks whether attribute value matches any value within a value list EXISTS • Checks if subquery returns any rows
Listing Unique Values §
DISTINCT clause: Produces list of values that are unique § Syntax - SELECT DISTINCT columnlist FROM tablelist ; § Access places nulls at the top of the list § Oracle places it at the bottom § Placement of nulls does not affect list contents
Adding primary key and foreign key designations
ALTER TABLE command followed by a keyword that produces
ALTER TABLE command §
Followed by a keyword that produces the specific change one wants to make § Options include ADD, MODIFY, and DROP §
HAVING Clause §
Extension of GROUP BY feature § Applied to output of GROUP BY operation § Used in conjunction with GROUP BY clause in second SQL command set § Similar to WHERE clause in SELECT statement
Comparison Operators §
Add conditional restrictions on selected table contents § Used on: § Character attributes § Dates
LIKE
Checks whether attribute value matches given string pattern
Arithmetic operators §
Perform: § Operations within parentheses § Power operations § Multiplications and divisions § Additions and subtractions
Alias
Alternate name given to a column or table in any SQL statement to improve the readability
Selecting Rows Using Conditional Restrictions
Used to select partial table contents by placing restrictions on the rows §
IS NULL
Checks whether attribute value is null
Subquery
Query embedded/nested inside another query
Changing a column's data type
Use ALTER; some RDBMSs do not permit changes to data types unless column is empty. Syntax- ALTER TABLE table name MODIFY (column name(data type));
Advanced Data Definition Commands §
Keywords use with the command § ADD - Adds a column § MODIFY - Changes column characteristics § DROP - Deletes a column § Used to: § Add table constraints § Remove table constraints
Selecting rows using conditional restrictions
SELECT column list FROM table list [WHERE condition list]; Used to select partial table contents by placing restrictions on the rows.
Recursive Joins §
Recursive query : Table is joined to itself using alias § Use aliases to differentiate the table from itself
Additional SELECT Query Keywords §
Logical operators work well in the query environment § SQL provides useful functions that: § Counts § Find minimum and maximum values § Calculate averages § SQL allows user to limit queries to entries: § Having no duplicates § Whose duplicates may be grouped
SELECT command
Acts as a subquery and is executed first
Adding Primary and Foreign Key Designations §
Syntax to add or modify columns § ALTER TABLE tablename § {ADD | MODIFY} ( columnname datatype [ {ADD | MODIFY} columnname datatype ] ) ; § ALTER TABLE tablename § ADD constraint [ ADD constraint ] ;
DROP TABLE
Deletes table from database § Syntax - DROP TABLE tablename ;
Advanced Data Updates §
UPDATE command updates only data in existing rows § If a relationship is established between entries and existing columns, the relationship can assign values to appropriate slots § Arithmetic operators are useful in data updates § In Oracle, ROLLBACK command undoes changes made by last two UPDATE statements
Changing Column's Data Type §
ALTER can be used to change data type § Some RDBMSs do not permit changes to data types unless column is empty § Syntax - § ALTER TABLE tablename MODIFY (columnname(datatype)) ;
Optional WHERE clause §
Adds conditional restrictions to the SELECT statement
ALTER TABLE
command: To make changes in the table structure
Comparison operators
Add conditional restrictions on selected table contents. Used on character attributes and dates.
Section 4
(50 cards)
In Oracle, you can use the SQL*Plus command SHOW ERRORS to help you diagnose errors found in PL/SQL blocks. T/F
True
Which statement describes a feature of Oracle sequences? a. Oracle sequences are tied to columns and tables. b. Oracle sequences generate a character string that can be assigned to tables. c. An Oracle sequence uses the identity column property to automatically number rows. d. Dropping a sequence does not delete values assigned to table attributes; it deletes only the sequence object from the database.
d. Dropping a sequence does not delete values assigned to table attributes; it deletes only the sequence object from the database.
Which SQL format would be best used for a small, numeric data type? a. INTEGER b. SMALLINT c. NUMERIC(L,D) d. CHAR(L)
b. SMALLINT
When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____. a. no rows will be deleted b. the first row will be deleted c. the last row will be deleted d. all rows will be deleted
d. all rows will be deleted
The _____ constraint assigns a value to an attribute when a new row is added to a table. a. NOT NULL b. CASCADE c. UNIQUE d. DEFAULT
d. DEFAULT
No matter what language you use, if it contains embedded SQL statements, it is called the _____ language. a. base b. static c. host d. view
c. host
In Oracle, _____ make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping. a. dynamic SQL b. stored procedures c. embedded SQL d. Procedural Language SQL
d. Procedural Language SQL
To remedy the lack of procedural functionality in SQL, and to provide some standardization within the many vendor offerings, the SQL-99 standard defined the use of persistent stored modules. T/F
True
The tables on which a view, or a virtual table derived from a SELECT query, are based are called _____ tables. a. indexed b. core c. relation d. base
d. base
The _____ command permanently saves all changes—such as rows added, attributes modified, and rows deleted—made to any table in the database. a. COMMIT b. SELECT c. ROLLBACK d. UPDATE
a. COMMIT
Triggers can only be used to update table values. T/F
False
Using the _____ command, SQL indexes can be created on the basis of any selected attribute. a. CREATE INDEX b. UPDATE CASCADE c. SELECT d. VARCHAR
a. CREATE INDEX
The DECLARE section in the trigger is used to declare any variables used inside the trigger code. T/F
True
When writing SQL table-creating command sequences, the entire table definition is enclosed in _____. a. asterisks b. commas c. quotations d. parentheses
d. parentheses
A statement-level trigger is assumed if a designer omits the FOR EACH ROW keywords. T/F
True
The _____ specification creates an individual index on a respective attribute; use it to avoid having duplicated values in a column. a. UNIQUE b. NOT NULL c. UPDATE d. VARCHAR
a. UNIQUE
The _____ constraint is used to validate data when an attribute value is entered. a. UNIQUE b. CASCADE c. CHECK d. SET NULL
c. CHECK
A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event. T/F
True
The Oracle equivalent to an MS Access AutoNumber is a(n) _____. a. auto-number b. sequence c. TO_NUMBER function d. trigger
b. sequence
Automating business procedures and automatically maintaining data integrity and consistency are trivial in a modern business environment. T/F
False
The Oracle string concatenation function is _____. a. CONCAT b. + c. || d. &&
c. ||
A(n) _____ cursor is automatically created in procedural SQL when the SQL statement returns only one value. a. implicit b. dynamic c. explicit d. static
a. implicit
A persistent stored module is stored and executed on the database client machine. T/F
False
A sequence is not associated with a table and can be dropped from a database with a DROP SEQUENCE command. T/F
True
Stored procedures must have at least one argument. T/F
False
Every PL/SQL block must be given a name. T/F
False
Just like database triggers, stored procedures are stored in the database T/F
True
Words used by a system that cannot be used for any other purpose are called _____ words. For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns. a. reserved b. unique c. null d. character
a. reserved
The _____ command is used to restore the database to its previous condition. a. ROWCOUNT b. BACKUP c. COMMIT d. ROLLBACK
d. ROLLBACK
Which command would be used to delete the table row where the P_CODE is 'BRT-345'? a. DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345'; b. REMOVE FROM PRODUCT WHERE P_CODE = 'BRT-345'; c. ERASE FROM PRODUCT WHERE P_CODE = 'BRT-345'; d. ROLLBACK FROM PRODUCT WHERE P_CODE = 'BRT-345';
a. DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';
An explicit cursor must return two or more rows. T/F
False
BEFORE means before the changes are made in memory but after the changes are permanently saved to disk. T/F
False
In Oracle, _____ retrieves the current value of a sequence. a. NEXTVAL b. CURRVAL c. VARCHAR d. VARCHAR2
b. CURRVAL
When you create a new database, the RDBMS automatically creates the data _____ tables in which to store the metadata and creates a default database administrator. a. index b. chapter c. dictionary d. appendix
c. dictionary
MySQL allows multiple triggering conditions per trigger. T/F
False
The CREATE TABLE command lets you define constraints when you use the CONSTRAINT keyword, known as a(n) _____ constraint. a. column b. table c. index d. cell
b. table
A row-level trigger is assumed if we omit the FOR EACH ROW keywords and a statement-level trigger required the use of the FOR EACH ROW keyword. T/F
False
SQL supports the conditional execution of procedures (IF-THEN-ELSE statements) that are typically supported by a programming language. T/F
False
A view is a virtual table based on a SELECT query. T/F
True
The most useful feature of PL/SQL blocks is that they let a designer create code that can be named, stored, and executed by the DBMS. T/F
True
The PL/SQL block starts with the _____ section. a. IS b. OPEN c. DECLARE d. BEGIN
c. DECLARE
A _____ is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server. a. cursor-style process b. statement-level trigger c. base table d. persistent storage module (PSM)
d. persistent storage module (PSM)
The _____ pseudo-column is used to select the next value from a sequence. a. CURRVAL b. NEXTVAL c. NEXT d. GET_NEXT
b. NEXTVAL
All changes in a table structure are made using the _____ TABLE command, followed by a keyword that produces the specific changes a user wants to make. a. ALTER b. COMMIT c. UPDATE d. ROLLBACK
a. ALTER
Oracle recommends _____ for creating audit logs. a. triggers b. stored procedures c. stored functions d. tables
a. triggers
One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions. T/F
True
_____ is a cursor attribute that returns TRUE if the last FETCH returned a row, and FALSE if not. a. %ROWCOUNT b. %NOTFOUND c. %FOUND d. %ISOPEN
c. %FOUND
SQL requires the use of the _____ command to enter data into a table. a. INSERT b. SELECT c. COMMIT d. NOT NULL
a. INSERT
A table can be deleted from the database by using the _____ TABLE command. a. DROP b. DELETE c. MODIFY d. ERASE
a. DROP
You cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor. This is known as _____. a. batch updating b. referential integrity c. authentication d. cross joining
b. referential integrity
Section 5
(20 cards)