CIST 1220 SQL Final Exam Study Guide
Studied by 0 people
Call with Kai
Knowt Play
Learn
Practice Test
Spaced Repetition
Match
Flashcards1/218
There's no tags or description
Looks like no tags are added yet.
Name | Mastery | Learn | Test | Matching | Spaced |
|---|
No study sessions yet.
219 Terms
Which of the following terms refers to a group of related records?a. character
b. database
c. file
d. field
c. File
Which of the following is used to uniquely identify each record?
a. primary key
b. row
c. partial dependency
d. account number
a. Primary Key
Which of the following is used to create and maintain the physical database?
a. Database Management System (DBMS)
b. Data mining
c. Systems Development Life Cycle (SDLC)
d. E-R Model
a.
Database Management System (DBMS)
Which of the following committees are responsible for establishing SQL guidelines?
a. ANSI and ASCII
b. OSI and ASCII
c. IEEE and OSI
d. ANSI and ISO
d. ANSI and ISO
Which of the following is an example of an attribute?
a. a person's hair color
b. the people who live in a particular town
c. the patients in a doctor's office
d. vendors
a. a person's hair color
What name is used to denote a common field that exists between two tables, but is also the primary key for one of the tables?
a. composite primary key
b. foreign key
c. duplicate key
d. distinct key
b. Foreign Key
What represents a characteristic or attribute that is being collected about an entity?
a. record
b. row
c. field
d. both a and b
c. Field
The multi-step process used when creating a new system is referred to as
a. E-R Modeling
b. the Systems Development Life Cycle
c. data mining
d. SQL
b. the Systems Development Life Cycle
In which step of the Systems Development Life Cycle (SDLC) are the logical and physical components defined?
a. systems implementation and review
b. systems design
c. systems analysis
d. systems recovery
b. systems design
In which step of the Systems Development Life Cycle (SDLC) is the solution to the identified problem determined and understood?
a. systems design
b. systems analysis
c. systems investigation
d. systems implementation and review
b. Systems analysis
Data in first normal form (1NF) does not contain which of the following?
a. repeating groups
b. both a and b
c. partial dependencies
d. primary key
a. Repeating Groups
Data in second normal form (2NF) may contain which of the following?
a. both a and b
b. repeating groups
c. partial dependencies
d. transitive dependencies
d. Transitive Dependencies
What is added to a relational database to eliminate many-to-many relationships?
a. secondary entity
b. primary entity
c. bridging table
d. transitive dependency
c. Bridging Table
Two tables can be linked or joined together through a common field.
True
False
True
Data mining refers to
a. selling data to other organizations
b. analyzing data already stored in a database
c. discovering new data to include in the database
d. all of the above
b. Analyzing data the already stored in the database
If entity A in Figure 2 represents customers and entity B represents automobiles, which of the following statements is correct?
a. Each customer can own many cars and each car can be owned by many customers.
b. Each customer can only own one car, but each car can be owned by many customers.
c. Each customer can own many cars, but each car can be owned by only one customer.
d. Each customer can only own one car and each car can only be owned by one customer.
c. Each customer can own many cars, but each car can be owned by only one customer.
A ____ is a storage structure designed to hold a collection of data.
a. database
b. column
c. table
d. row
a. database
A ____ is a group of interrelated files.
a. field
b. database
c. record
d. character
b. database
A DBMS includes which of the following capabilities?
a. multiuser access
b. data dictionary
c. security
d. all of the above
d. all of the above
What is used to indicate the end of an SQL statement?
a. *
b. :
c. /
d. ;
d. ;
Which of the following commands is used to issue a query?
a. SELECT
b. QUER
c. QUESTION
d. FROM
a. SELECT
To indicate which database table contains the data to be selected by a query, the table name should be listed in the ____ clause.
a. SELECT
b. HAVING
c. WHERE
d. FROM
c. FROM
Which of the following is a mandatory clause in a SELECT statement?
a. both a and b are mandatory
b. HAVING
c. FROM
d. WHERE
c. FROM
Which of the following symbols represents all the columns contained in a table?
a. ^
b. /
c. *
d. ;
c. *
Which of the following commands can be used to view the structure of a table?
a. DESCRIBE
b. SELECT
c. CONCATENATION
d. none of the above
a. DESCRIBE
Which of the following statements is correct?
a. Character columns are listed first in the output, followed by numeric columns.
b. The columns will be listed in the results in the same order they are listed in the SELECT clause of the SELECT statement.
c. The columns will be listed in the results in the same order they are stored in the database table.
d. Numeric columns are listed first in the output, followed by character columns.
b. The columns will be listed in the results in the same order they are listed in the SELECT clause of the SELECT statement.
If a column alias contains a blank space, it must be enclosed in ____.
a. percent signs (% %)
b. double quotation marks (" ")
c. asterisks ( )
d. single quotation marks (' ')
b. Double Quotation Marks (" ")
A column alias must be contained within double quotation marks (" ") if it contains which of the following?
a. blank spaces
b. special symbols
c. characters that should be displayed in lower or mixed case
d. all of the above
d. All of the above
What is the correct solution for the arithmetic expression (2+8)/2*9/3 using the order of operations employed by Oracle 12c when solving equations?
a. 1.336
b. 15
c. 14
d. 4.8
b. 15
Which of the following keywords can be included in a SELECT statement to suppress duplicate data?
a. UNIQ
b. DISTINCT
c. SUPPRESS
d. all of the above
b. DISTINCT
Which of the following symbols can be used to combine data from different columns into one column of output?
a. %
b. ^
c. *
d. ||
d. ||
Combining the contents of two or more columns is known as ____.
a. suppression
b. selection
c. projection
d. concatenation
d. concatenation
Which keywords are used to complete the deletion of a column previously marked with SET UNUSED?
a. DROP SET COLUMNS
b. DELETE UNUSED COLUMNS
c. DROP UNUSED COLUMNS
d. UNSET UNUSED COLUMNS
c. DROP UNUSED COLUMNS
Which of the following is a valid column name?
a. VARCHAR2
b. NEW_COLUMN
c. CHAR
d. DISTINCT
b. NEW_COLUMN
Which of the following is a valid table name?
a. DISTINCT
b. SELECT
c. CHAR
d. MYTABLE
d. MYTABLE
Which of the following rules apply to table names in Oracle 12c?
a. Names can begin with a number.
b. Names can contain a number sign (#).
c. Names can be up to 225 characters in length.
d. Names can contain blank spaces.
b. Names can contain a number sign (#)
What is the maximum width that can be assigned to a DATE column?
a. 12
b. 1
c. 8
d. none of the above
d. none of the above
If a column is defined as NUMBER(5,2), which of the following is true?
a. The column can store a total of 7 digits, with 5 on the left side of the decimal point, and 2 on the right.
b. The column can store a total of 7 digits, all of them on the right side of the decimal point.
c. The column can store a total of 5 digits, with 3 on the left side of the decimal point, and 2 on the right.
d. The column can store a total of 5 digits, all of them on the left side of the decimal point.
c. The column can store a total of 5 digits, with 3 on the left side of the decimal point, and 2 on the right.
Which command instructs Oracle 12c to create a new table?
a. CREATE TABLE...FROM
b. CREATE TABLE
c. ALTER TABLE
d. CREATE NEW TABLE
b. CREATE TABLE
Which command instructs Oracle 12c to create a new table from existing data?
a. CREATE TABLE...AS
b. CREATE TABLE...FROM
c. CREATE TABLE
d. CREATE NEW TABLE
a. CREATE TABLE...AS
Which of the following is a valid SQL statement?
a. CREATE TABLE newname (colA DATE, colB VARCHAR2(3);
b. CREATE TABLE newname (colA, colB);
c. CREATE TABLE newname as SELECT * FROM books;
d. CREATE TABLE newname (colA NUMBER, colB DATE);
d. CREATE TABLE newname (colA NUMBER, colB DATE);
When creating a table, which of the following statements is correct?
a. A user can have two tables with the same name, as long as the column names are different.
b. The schema of the table must be explicitly stated.
c. Default values cannot be assigned to all columns in a table.
d. The column list must be enclosed in parentheses ( ).
d. The column list must be enclosed in parentheses ( ).
If you are creating a new table containing four columns from an existing table and want to change the name of one of the columns when it is created in the new table, add a column list containing ____ names in the CREATE TABLE clause.
a. one
b. five
c. zero
d. four
d. Four
If you are creating a new table from data contained in an existing table, new column names can be specified by including a list of column names ____.
a. in the subquery
b. after the AS clause
c. after the subquery
d. before the AS clause
d. before the AS clause
Which of the following keywords uses a subquery to create a new table using existing database tables?
a. ALTER TABLE
b. CREATE TABLE...AS
c. CREATE TABLE...FROM
d. GENERATE TABLE
b. CREATE TABLE...AS
Which of the following keywords cannot be used to modify an existing table?
a. ALTER TABLE...ADD
b. ALTER TABLE...DROP COLUMN
c. ALTER TABLE...AS
d. ALTER TABLE...MODIFY
c. ALTER TABLE...AS
Which of the following commands can be used to make structural changes to an existing table?
a. ALTER TABLE
b. CHANGE TABLE
c. FIX TABLE
d. MODIFY TABLE
a. ALTER TABLE
Which of the following keywords can be used to change the size, datatype, and/or default value of an existing column?
a. RESET
b. ADD
c. MODIFY
d. CHANGE
c. MODIFY
When using the ALTER TABLE...DROP COLUMN command, which of the following is not correct?
a. The command's effect on the table is permanent.
b. You cannot delete the last remaining column in a table.
c. The command can be used to delete multiple columns from a table.
d. The command deletes both the column and its contents.
c. The command can be used to delete multiple columns from a table.
Which command is used to rename a table you own?
a. RENAME...TO
b. ALTER TABLE...RENAME
c. ALTER TABLE...SET NAME
d. RENAME...AS
a. RENAME...TO
Which of the following commands will drop any columns marked as unused from the TABLEA table?
a. DROP UNUSED COLUMNS:
b. ALTER TABLE tablea DROP (unused);
c. ALTER TABLE tablea DROP UNUSED COLUMNS;
d. DROP COLUMN FROM tablea WHERE column_status = UNUSED;
c. ALTER TABLE tablea DROP UNUSED COLUMNS;
Which of the following keywords is used to remove a database table in Oracle 12c?
a. DROP TABLE
b. ALTER TABLE...DROP
c. DELETE TABLE
d. TRUNCATE TABLE
a. DROP TABLE
Which of the following statements about the FOREIGN KEY constraint is incorrect?
a. The constraint exists between two tables, called the parent table and the child table.
b. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
c. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.
d. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
b. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
Which of the following statements about a PRIMARY KEY is incorrect?
a. It identifies which column(s) uniquely identify each record.
b. none of the above
c. It can be NULL, as long as the FOREIGN KEY contains a value.
d. Each data value must be unique.
c. It can be NULL, as long as the FOREIGN KEY contains a value.
Which of the following is the standard abbreviation for the constraint NOT NULL?
a. nnv
b. nl
c. nv
d. nn
d. nn
Which clause will allow you to delete a constraint?
a. MODIFY
b. DELETE
c. DISABLE
d. DROP
d. DROP
Which clause will allow you to disable a constraint?
a. DROP
b. STOP
c. MODIFY
d. DISABLE
d. DISABLE
Which command can be used to determine whether or not a column is allowed to contain a NULL value?
a. DESCRIBE
b. DEFINED_CONSTRAINTS
c. DISPLAY
d. DISPLAY_CONSTRAINTS
a. DESCRIBE
When a constraint is created at the table level, the constraint definition is provided ____ the column definition list.
a. after
b. between columns in
c. in the middle of
d. before
a. after
The UNIQUE constraint differs from the PRIMARY KEY constraint in what way?
a. The UNIQUE constraint can be created at either the column level or the table level.
b. The UNIQUE constraint allows NULL values.
c. The UNIQUE constraint ensures that a specific condition is true before a data value is added to a table.
d. The UNIQUE constraint does not allow NULL values.
b. The UNIQUE constraint allows NULL values.
The NOT NULL constraint can only be created at the ____ level.
a. table
b. column
c. both a and b
d. database
b. Column
Only one ____ constraint can exist for each table.
a. CHECK
b. PRIMARY KEY
c. UNIQUE
d. FOREIGN KEY
b. PRIMARY KEY
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a FOREIGN KEY constraint.
a. N
b. C
c. R
d. K
c. R
If a constraint applies to more than one column, the constraint must be created at the ____ level.
a. column
b. row
c. table
d. database
c. table
The purpose of the ____ constraint is to ensure that two records do not have the same value stored in the same column. However, it can contain NULL values.
a. CHECK
b. FOREIGN KEY
c. PRIMARY KEY
d. UNIQUE
d. UNIQUE
Which of the following is the standard abbreviation for the constraint FOREIGN KEY?
a. fkey
b. fk
c. frk
d. fky
b. fk
If more than one column is listed in the INSERT INTO clause, the column names must be separated by ____.
a. parentheses
b. single quotation marks
c. double quotation marks
d. commas
d. commas
If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
a. none of the above
b. UPDATE
c. SET
d. WHERE
d. WHERE
Regarding the INSERT statement, which of the following is correct?
a. The data values to be inserted are enclosed in parentheses ( ) after the VALUES keyword.
b. Column names are enclosed in parentheses ( ).
c. Non-numeric data is enclosed in single quotation marks.
d. all of the above
d. all of the above
The ____ command can be used to view the contents of a record when it is anticipated that the record will need to be modified. It places a shared lock on the record(s) to be changed and prevents any other user from acquiring a lock on the same record(s).
a. COMMIT...FOR UPDATE
b. SELECT...LOCK TABLE
c. SELECT...FOR UPDATE
d. COMMIT...LOCK TABLE
c. SELECT...FOR UPDATE
The ____ command will prevent two users from trying to make changes to the same table at the same time.
a. LOCK TABLE
b. EXCLUSIVE LOCK
c. TABLE LOCK
d. SHARED LOCK
a. LOCK TABLE
The effect of which of the following commands can never be reversed by the ROLLBACK command?
a. CREATE TABLE
b. COMMIT
c. ALTER TABLE
d. all of the above
d. all of the above
The row(s) to be updated by the UPDATE command is specified by the ____ clause.
a. COL
b. SET
c. UPDATE
d. WHERE
d. WHERE
When does a COMMIT command explicitly occur?
a. When the user executes COMMIT;.
b. When the user executes ROLLBACK;.
c. When the user issues a DDL command such as CREATE or ALTER TABLE.
d. When the user exists the system.
a. When the user executes COMMIT;.
When inserting a row into a table, how can you indicate that a row contains a NULL value?
a. In the VALUES clause, substitute two single quotation marks for the NULL value.
b. both a and b
c. In the VALUES clause, include the keyword NULL in the position where the value should be listed.
d. In the VALUES clause, include a blank space in the position where the value should be listed.
b. both a and b
Which of the following commands is used to add rows to a table?
a. ADD
b. UPDATE
c. INSERT
d. ENTER
c. INSERT
Which of the following commands is used to modify existing data in a table?
a. INSERT
b. UPDATE
c. ADD
d. MODIFY
b. UPDATE
Which of the following keywords is omitted from the INSERT command if the data to be added to a table is already contained in another table?
a. VALUES
b. none of the above
c. INSERT
d. INTO
a. VALUES
Which of the following statements about the INSERT keyword is incorrect?
a. The keywords INSERT INTO are followed by the table name.
b. If more than one column is listed, column names must be separated by commas.
c. The VALUES clause identifies the data values that will be inserted into the table.
d. Oracle 12c does not allow column names to be omitted from the INSERT INTO clause.
d. Oracle 12c does not allow column names to be omitted from the INSERT INTO clause.
A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.
a. substitution value
b. substitution condition
c. substitution clause
d. substitution variable
d. Substitution variable
Commands used to modify data are called ____ commands.
a. data control language (DCL)
b. data definition language (DDL)
c. data modification language (DML)
d. data manipulation language (DML)
d. data manipulation language (DML)
If more than one column is listed in the INSERT INTO clause, the column names must be separated by ____.
a. parentheses
b. single quotation marks
c. double quotation marks
d. commas
d. Commas
If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
a. none of the above
b. UPDATE
c. SET
d. WHERE
d. WHERE
Regarding the INSERT statement, which of the following is correct?
a. The data values to be inserted are enclosed in parentheses ( ) after the VALUES keyword.
b. Column names are enclosed in parentheses ( ).
c. Non-numeric data is enclosed in single quotation marks.
d. all of the above
d. All of the above
The ____ command can be used to view the contents of a record when it is anticipated that the record will need to be modified. It places a shared lock on the record(s) to be changed and prevents any other user from acquiring a lock on the same record(s).
a. COMMIT...FOR UPDATE
b. SELECT...LOCK TABLE
c. SELECT...FOR UPDATE
d. COMMIT...LOCK TABLE
c. SELECT...FOR UPDATE
The ____ command will prevent two users from trying to make changes to the same table at the same time.
a. LOCK TABLE
b. EXCLUSIVE LOCK
c. TABLE LOCK
d. SHARED LOCK
a. LOCK TABLE
The effect of which of the following commands can never be reversed by the ROLLBACK command?
a. CREATE TABLE
b. COMMIT
c. ALTER TABLE
d. all of the above
d. All of the above
The row(s) to be updated by the UPDATE command is specified by the ____ clause.
a. COL
b. SET
c. UPDATE
d. WHERE
d. WHERE
When does a COMMIT command explicitly occur?
a. When the user executes COMMIT;.
b. When the user executes ROLLBACK;.
c. When the user issues a DDL command such as CREATE or ALTER TABLE.
d. When the user exists the system.
a. When the user executes COMMIT;.
When inserting a row into a table, how can you indicate that a row contains a NULL value?
a. In the VALUES clause, substitute two single quotation marks for the NULL value.
b. both a and b
c. In the VALUES clause, include the keyword NULL in the position where the value should be listed.
d. In the VALUES clause, include a blank space in the position where the value should be listed.
b. both a and b
Which of the following commands is used to add rows to a table?
a. ADD
b. UPDATE
c. INSERT
d. ENTER
c. INSERT
Which of the following commands is used to modify existing data in a table?
a. INSERT
b. UPDATE
c. ADD
d. MODIFY
b. UPDATE
Which of the following keywords is omitted from the INSERT command if the data to be added to a table is already contained in another table?
a. VALUES
b. none of the above
c. INSERT
d. INTO
a. VALUES
Which of the following statements about the INSERT keyword is incorrect?
a. The keywords INSERT INTO are followed by the table name.
b. If more than one column is listed, column names must be separated by commas.
c. The VALUES clause identifies the data values that will be inserted into the table.
d. Oracle 12c does not allow column names to be omitted from the INSERT INTO clause.
d. Oracle 12c does not allow column names to be omitted from the INSERT INTO clause.
Which command will create an index?
a. CREATE INDEX indexname INCREMENT BY (columnname)
b. CREATE INDEX indexname REFERENCE (columnname)
c. CREATE INDEX indexname IN tablename (columnname)
d. CREATE INDEX indexname ON tablename (columnname)
d. CREATE INDEX indexname ON tablename (columnname)
A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.
a. INCREMENT BY
b. GENERATE BY
c. DECREASE BY
d. DECREMENT BY
a. INCREMENT BY
A(n) ____ synonym is used by an individual to reference objects owned by that person.
a. duplicated
b. private
c. unique
d. public
b. Private
An index can be created ____.
a. explicitly
b. only at instance startup
c. both a and b
d. implicitly
c. both a and b
An index is not implicitly created when which type of constraint is created?
a. NOT NULL
b. none of the above
c. PRIMARY KEY
d. UNIQUE
a. NOT NULL
If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.
a. objective
b. private
c. PUBLIC
d. functional
b. Private