CIST 1220 SQL Final Exam Study Guide
A(n) single-row subquery can be nested in a(n) SELECT clause. True False
True
An ORDER BY clause can be used to perform "TOP-N" analysis. True False
True
Two tables can be linked or joined together through a common field. True False
True
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
The ____________ function calculates the average of the numeric values in a specified column. A. AVG B. AVERAGE C. MEAN D. AG
a. AVG
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 keywords is used to create a Cartesian join? a. CROSS JOIN b. NATURAL JOIN c. JOIN...USING d. OUTER JOIN
a. CROSS JOIN
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 SQL statements will delete the private synonym named MYSYNON? a. DROP SYNONYM mysynon; b. DROP PRIVATE SYNONYM mysynon; c. DELETE PRIVATE SYNONYM mysynon; d. DROP PUBLIC SYNONYM mysynon;
a. DROP SYNONYM mysynon;
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 functions allows for different options, depending upon whether a NULL value exists? a. NVL2 b. IFNVL c. IFNL d. NVL
a. NVL2
The MAX function can be used with which type of columns? A. numeric B. date C. character D. all of the above
a. Numeric
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
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
When the WHERE clause contains multiple types of operators, which of the following is resolved first? a. arithmetic operations b. comparison operators c. union operators d. logical operators
a. arithmetic operation
The order in which NULL values appear in the results can be overridden by which of the following keywords? a. both a and b b. NULLS FIRST c. NULLS LAST d. NSEQ
a. both a and b
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 statements about creating a username is incorrect? a. A user name can contain the symbols _ , $, and #. b. A user name cannot contain numbers. c. A user name can contain up to 30 characters. d. A user name can contain upper-case letters.
b. A user name cannot contain numbers.
If the DISTINCT keyword is not included in the STDDEV function, the _____________ keyword will be assumed. A.UNIQUE B.ALL C.NONULLS D. none of the above
b. ALL
If the DISTINCT keyword is not included in the SUM function, the ___________ keyword will be assumed. UNIQUE ALL RANDOM none of the above
b. ALL
Which of the following will add a password to a role? a. ALTER USER username PASSWORD password; b. ALTER ROLE rolename IDENTIFIED BY password; c. ALTER USER username SET ROLE rolename; d. ALTER ROLE rolename SET USER username
b. ALTER ROLE rolename IDENTIFIED BY password;
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
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 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
The NOT NULL constraint can only be created at the ____ level. a. table b. column c. both a and b d. database
b. Column
Which type of view is created from the following command? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books; a. derived b. complex c. inline d. simple
b. Complex
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
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
The _____________ clause is used to indicate that groups should be created. A. ORDER BY B. GROUP BY C. AGGREGATED D. GROUPING
b. GROUP BY
Which of the following is a valid column name? a. VARCHAR2 b. NEW_COLUMN c. CHAR d. DISTINCT
b. NEW_COLUMN
The VARIANCE function can be used with _____________ columns. A. alphanumeric B. numeric C. date D. none of the above
b. Numeric
Only one ____ constraint can exist for each table. a. CHECK b. PRIMARY KEY c. UNIQUE d. FOREIGN KEY
b. PRIMARY KEY
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
A ____ is a group of interrelated files. a. field b. database c. record d. character
b. database
What is used to indicate the end of an SQL statement? a. * b. : c. / d. ;
d. ;
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
When sorting the results in ascending order, which of the following values will be presented first in the output? a. character b. NULL c. date d. numeric
d. Numeric
Which of the following refers to the process of ensuring that individuals trying to access the system are who they say they are, thus preventing them from illegally accessing data? a. accreditation b. individualization c. authorization d. authentication
d. authentication
A cross join between two tables, containing four rows each, will display eight rows in its output. True False
False
The COUNT(*) function is only used to count the number of records containing NULL values. True False
False
The SUBSTR function is used to substitute one character string for another in a set of data True False
False
A(n) ___________ clause cannot include a group function. A. WHERE B. HAVING C. SELECT D. both a and c
A. WHERE
Single-row operators can be used with multiple-row subqueries that return only one column of results. True False
False
When a multiple-column subquery is used in the WHERE clause of the outer query, the column names listed on the left side of the comparison operator must be enclosed in double-quotation marks. True False
False
When a positive value is assigned to the INCREMENT BY clause of the CREATE SEQUENCE command, numeric values are generated in descending order. True False
False
A Cartesian join can be created by not including a joining condition in the WHERE clause of a SELECT statement. True False
True
A positive number is used in the ROUND function to indicate that numeric data should be rounded to the indicated position to the right of the decimal point. True False
True
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 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
Which of the following commands can be used to remove a sequence from a database? a. DROP SEQUENCE b. REMOVE SEQUENCE c. DROP SEQ d. DELETE SEQ
a. DROP SEQUENCE
Which of the following commands will allow a use to connect to the Oracle database? a. GRANT CREATE SESSION TO username; b. GRANT SESSION TO username; c. GRANT CREATE SESSION FOR username; d. GRANT CREATE LOGON TO username;
a. GRANT CREATE SESSION TO username;
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ___________ clause will be processed last. HAVING GROUP BY WHERE SELECT
a. HAVING
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
Which of the following represents the number of days that have passed since January 1, 4712 B.C.? a. Julian date b. none of the above c. Cartesian date d. Extended date
a. Julian Date
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 >ALL operator indicates that a value must be ____ value returned by the subquery. query? a. more than the highest b. more than the lowest c. less than the highest d. less than the lowest
a. More than the highest
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
The STDDEV function can be used with ____________ columns. numeric character date all of the above
a. Numeric
The SUM function can only be used with ____________ data. numeric B. character C. date D. alphanumeric
a. Numeric
When functions are nested, the ____ function is solved last. a. outer b. single-row c. multiple-row d. inner
a. Outer
Which of the following is used to create an outer join in a WHERE clause? a. outer join operator (+) b. RIGHT OUTER JOIN keywords c. FULL OUTER JOIN keywords d. LEFT OUTER JOIN keywords
a. Outer Join Operator (+)
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 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
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
Which of the following commands is used to issue a query? a. SELECT b. QUER c. QUESTION d. FROM
a. SELECT
Oracle12c will begin each sequence with the value of one, unless another value is specified in the ____ clause. a. START WITH b. INTERVAL SIZE c. INCREMENT BY d. VALUE START
a. START WITH
A(n) ____ is used to combine the results of two queries. a. set operator b. concatenation operator c. logical operator d. comparison operator
a. Set Operator
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
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;.
Which of the following functions can be used to fill a character string to a specific width? a. both a and b b. RPAD c. LFILL d. LPAD
a. both a and b
A ____ is a storage structure designed to hold a collection of data. a. database b. column c. table d. row
a. database
The EXISTS operator must be listed ____ a. none of the above b. on the left side of the comparison operator c. on the right side of the comparison operator d. in the SELECT clause
a. none of the above
Which of the following search conditions can be used to identify records that do not have data stored in a column named ColB? a. ColB = NULL b. ColB IS NULL c. ColB = ' ' d. ColB IS NOT NULL
a. ColB IS NOT NULL
The ____ keywords are used to grant object privileges to users and roles. a. GRANT...ON...TO b. GRANT...WITH...TO c. GRANT...FOR...TO d. CREATE...WITH...TO
a. GRANT...ON...TO
If a view was created with the WITH CHECK OPTION constraint, to remove the constraint you will need to ____. a. re-create the view without the option b. use the ALTER VIEW command c. rename the view d. drop the option
a. re-create the view without the option
Which command will delete a sequence? a. ALTER SEQUENCE...DELETE b. DROP SEQUENCE sequencename c. DELETE SEQUENCE sequencename d. ALTER SEQUENCE...DROP
b. DROP SEQUENCE sequencename
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 join based upon a column from each table containing equivalent data is known as a(n) ____. a. all of the above b. equality join c. inequality join d. outer join
b. Equality Join
Which of the following SQL statements will grant the SELECT privilege for the ORDERS table to all database users? a. GRANT SELECT ON orders TO *; b. GRANT SELECT ON orders TO PUBLIC; c. GRANT SELECT ON orders TO EVERYONE; d. none of the above
b. GRANT SELECT ON orders TO PUBLIC;
When a multiple-column subquery is included in the WHERE or HAVING clause of the outer query, which operator is used by the outer query to evaluate the results of the subquery? a. BETWEEN b. IN c. none of the above d. >
b. IN
Which of the following functions can be used to convert a character string to lower-case letters? a. LOW b. LOWER c. SMALLCAP d. SMALLER
b. LOWER
Which of the following is used to establish a minimum or maximum value, respectively, for a sequence? a. STARTWITH | ENDWITH b. MINVALUE | MAXVALUE c. MINIMUMVALUE | MAXIMUMVALUE d. BEGINVALUE | ENDVALUE
b. MINVALUE | MAXVALUE
The type of view that actually replicates data is called a(n) ____ view. a. inline b. materialized c. complex d. simple
b. Materialized
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 (#)
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
Which of the following commands can be used to revoke system or object privileges previously granted to a user? a. UNGRANT b. REVOKE c. DROP d. REMOVE
b. REVOKE
Which of the following commands will revoke the SELECT privilege for the ORDERS table previously granted to user RTHOMAS? a. REVOKE SELECT FOR orders FROM rthomas; b. REVOKE SELECT ON orders FROM rthomas; c. REVOKE SELECT FROM rthomas; d. UNGRANT SELECT FROM rthomas;
b. REVOKE SELECT ON orders FROM rthomas;
Which of the following SQL statements will allow a user to enable the DBA role? a. ENABLE dba; b. SET ROLE dba; c. none of the above d. SET dba;
b. SET ROLE dba;
The _____________ function is used to calculate the total amount stored in a numeric field. A. TOTAL B. SUM C. AVERAGE D. ADD
b. SUM
The process of retrieving only certain rows based upon a specified condition is known as ____. a. projection b. selection c. retrieval d. derivation
b. Selection
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
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.
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.
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 statements about performing DML operations on complex views is correct? a. both b and c b. Values cannot be inserted into columns that are based on arithmetic expressions. c. Values cannot be inserted into columns containing date data. d. Values cannot be inserted into columns containing an ORDER BY clause.
b. Values cannot be inserted into columns that are based on arithmetic expressions.
A(n) ____ stores a query and is used to access data in the underlying tables. a. function b. view c. constraint d. argument
b. View
Which of the following keywords must be included with the GRANT command to allow the user to grant object privileges to other users? a. WITH ADMIN OPTION b. WITH GRANT OPTION c. WITH DBA OPTION d. WITH SYSADMIN OPTION
b. WITH GRANT OPTION
Which of the following can be considered potential threats to an organization's data? a. disgruntled employees b. all of the above c. natural disaster d. computer criminals
b. all of the above
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 is the standard abbreviation for the constraint FOREIGN KEY? a. fkey b. fk c. frk d. fky
b. fk
A(n) ____ table is the table that contains the primary key the view uses to uniquely identify each record being displayed by the view. a. non primary-keyed b. key-preserved c. primary d. non key-preserved
b. key-preserved
A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view. a. primary b. non key-preserved c. key-preserved d. non primary-keyed
b. non key-preserved
Which of the following is a valid SQL statement when referencing a sequence? a. INSERT INTO orderitems VALUES (currentval, 1, 811794939, 1); b. none of the above c. INSERT INTO orderitems VALUES (nextvalue, 1, 811794939, 1); d. INSERT INTO orderitems VALUES (currval, 1, 811794939, 1);
b. none of the above
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
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
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 search patterns would find dates occurring only in the year 2003? a. *03 b. _03% c. %03 d. %03%
c. %03
Which of the following search patterns could be used to find the word HELLO in Oracle12c? a. HEL* b. %HEL?O c. %H% d. _EL*
c. %H%
Which of the following symbols represents all the columns contained in a table? a. ^ b. / c. * d. ;
c. *
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
The ____ command will assign a default role to a user. a. ALTER ROLE rolename MODIFY USER username; b. ALTER DEFAULT ROLE rolename GRANT username; c. ALTER USER username DEFAULT ROLE rolename; d. ALTER USER username MODIFY ROLE rolename;
c. ALTER USER username DEFAULT ROLE rolename;
Which of the following commands will change a user's password? a. ALTER USERNAME username PASSWORD newpassword; b. ALTER USERNAME username IDENTIFIED BY newpassword; c. ALTER USER username IDENTIFIED BY newpassword; d. ALTER USER username IDENTITY newpassword;
c. ALTER USER username IDENTIFIED BY newpassword;
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
With a MERGE statement, a series of ____ actions can occur with a single SQL statement. a. PML b. XML c. DML d. SGML
c. DML
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
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.
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
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
Which of the following terms refers to a group of related records?a. character b. database c. file d. field
c. File
If the output is to be restricted based upon a group function, a(n) _____________ clause must be included in the SQL statement. A. GROUP BY B. WHERE C. HAVING D. ORDER BY
c. HAVING
The ____ operator indicates that the records processed by the outer query must match one of the values returned by the subquery. a. b. >ANY c. IN d. >ALL
c. IN
The ____ clause is used to specify the interval between two sequential values. a. INTERVAL SIZE b. STEP BY c. INCREMENT BY d. START WITH
c. INCREMENT BY
Which of the following commands is used to add rows to a table? a. ADD b. UPDATE c. INSERT d. ENTER
c. INSERT
If a group function is used in the SELECT clause, any ______________ listed in the SELECT clause must also be listed in the GROUP BY clause. A. aggregate function B. single-row function C. individual column D. all of the above
c. Individual Column
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 functions can be used to remove a specific set of characters from the left side of a set of data values? a. both a and b b. FULLTRIM c. LTRIM d. RTRIM
c. LTRIM
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
The following SQL statement contains which type of subquery? SELECT title, retail, category, cataverage FROM books NATURAL JOIN (SELECT category, AVG(retail) cataverage FROM books GROUP BY a. single-row b. multiple-row c. multiple-column d. correlated
c. Multiple-Column
Which of the following keywords is used to actually generate a sequence value? a. NEXTVALUE b. GENERATE c. NEXTVAL . d. GENERATEVAL
c. NEXTVAL
What is the default mode for the CREATE VIEW command? a. COMPILE b. NOCOMPILE c. NOFORCE d. FORCE
c. NOFORCE
If the two values compared by the NULLIF function are equal, what value does the function return? a. Both values in the comparison b. The first value in the comparison c. NULL d. The second value in the comparison
c. NULL
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
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
Which of the following keywords will return the value of the date according to the computer? a. SYSTEMDATE b. DATESYS c. SYSDATE d. DATE
c. SYSDATE
Which of the following types of views cannot contain grouped data? a. inline view b. complex c. simple d. all of the above
c. Simple
Which of the following describes a type of view that is based upon a subquery that only references one table and does not include any group functions, expressions, or GROUP BY clauses? a. "TOP-N" analysis b. complex view c. simple view d. inline view
c. Simple View
Which of the following types of views cannot include a GROUP BY clause? a. all of the above clause b. complex view c. simple view d. inline view
c. Simple View
Which of the following functions can be used to change the format of a date that has been entered by a user to the default storage format use by Oracle12c? a. DEFAULT b. DATE c. TO_DATE d. TO_CHAR
c. TO_DATE
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.
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 of the following set operators will display the results of the combined SQL statements without suppressing duplicate rows? a. MINUS b. UNION c. UNION ALL d. INTERSECT
c. UNION ALL
Which of the following functions can be used to convert a character string to upper-case letters? a. UPPERCAP b. UPPERCASE c. UPPER d. UP
c. UPPER
Which of the following statements about views is incorrect? a. Views assist users who do not have the training to issue complex SQL inquiries. b. A view can be referenced in a SELECT...FROM statement, just like any table. c. Views are database objects that actually store data. d. Views restrict users' access to sensitive data.
c. Views are database objects that actually store data.
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed first. A. HAVING B. GROUP BY C. WHERE D. ORDER BY
c. WHERE
Which of the following indicates the processing order for the indicated clauses? a. WHERE, HAVING, GROUP BY b. GROUP BY, HAVING, WHERE c. WHERE, GROUP BY, HAVING d. HAVING, WHERE, GROUP BY
c. Where, Group by, Having
Which of the following can be used in a WHERE clause? a. multiple-column subquery b. multiple-row subquery c. all of the above d. single-row subquery
c. all of the above
The OR REPLACE clause is not required if ____. a. the view is not based on a group function b. the CREATE command does not specify the WITH CHECK OPTION option c. another view does not exist with the same name d. the view does not contain data from more than one table
c. another view does not exist with the same name
An index can be created ____. a. explicitly b. only at instance startup c. both a and b d. implicitly
c. both a and b
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
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 SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? a. INSERT INTO prices (title, cost, retail, profit) VALUES ('A SECOND BOOK', 49.99, 39.99, -10); b. none of the above c. INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99); d. INSERT INTO prices (title, cost, retail, profit) VALUES ('A NEW BOOK', 49.99, 69.99, 20);
c. INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99);
The ____ command is used to revoke a role. a. ALTER rolename REVOKE username; b. DROP rolename FROM username; c. REVOKE rolename FROM username; d. DROP rolename REVOKE FROM username;
c. REVOKE rolename FROM username;
Which of the following subqueries returns more than one row of results to the outer query? a. single-row subquery b. correlated subquery c. multiple-row subquery d. multiple-column subquery
c. multiple-row subquery
If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____. a. use the ALTER VIEW command b. drop the option c. re-create the view without the option d. rename the view
c. re-create the view without the option
Which operators can be combined with other comparison operators to treat the results of a subquery as a set of values, rather than as individual values? a. EXISTS and IN b. IN and ANY c. IN and ALL d. ALL and ANY
d. ALL and ANY
Which of the following commands can be used to assign a default role to a user? a. RESET DEFAULT b. ASSIGN c. ROLE ASSIGNMENT d. ALTER USER
d. ALTER USER
Which of the following SQL statements will assign the DBA role as the default role for user RTHOMAS? a. ALTER USER rthomas SET DEFAULT TO DBA; b. SET ROLE DBA; c. none of the above d. ALTER USER rthomas DEFAULT ROLE dba;
d. ALTER USER rthomas DEFAULT ROLE dba;
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
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
Functions that return one result per group of rows are called __________ functions. A. group B. multiple-row C. aggregate D. all of the above
d. All of the above
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
Which of the following can be used with date columns? A. MIN B. MAX C. COUNT D. all of the above
d. All of the above
A group function can be nested inside a(n) A. group function B. single-row function C. order function D. both a and b
d. Both a and b
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)
Which command will establish a new role? a. CREATE ROLE rolename WITH privilege; b. CREATE NEW ROLE rolename; c. CREATE ROLE rolename FOR username; d. CREATE ROLE rolename;
d. CREATE ROLE rolename;
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);
Which of the following commands is used to establish a user account? a. CREATE USERNAME username IDENTITY password; b. CREATE NEW USER username IDENTIFIED BY password; c. CREATE USER username PASSWORD password; d. CREATE USER username IDENTIFIED BY password;
d. CREATE USER username IDENTIFIED BY password;
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
A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view. a. inline b. outer c. simple d. complex
d. Complex
Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data? a. "TOP-N" analysis b. inline view c. simple view d. complex view
d. Complex View
Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query? a. uncorrelated subquery b. single-row subquery c. multiple-column subquery d. correlated subquery
d. Correlated Subquery
Which of the following functions can be used to specify a list of alternative actions that should be taken depending upon a stated value? a. IFTHEN b. DCD c. SELECTIF d. DECODE
d. DECODE
Which clause will allow you to disable a constraint? a. DROP b. STOP c. MODIFY d. DISABLE
d. DISABLE
DML operations are not allowed on a view that is created with the ____ keyword. a. NATURAL JOIN b. JOIN c. REPLACE d. DISTINCT
d. DISTINCT
Which clause will allow you to delete a constraint? a. MODIFY b. DELETE c. DISABLE d. DROP
d. DROP
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
The subquery used to create a(n) ____ view can contain an ORDER BY clause. a. complex b. derived c. simple d. inline
d. Inline
The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category; a. multiple-row subquery b. none of the above c. single-row subquery d. inline view
d. Inline View
Which of the following is a valid table name? a. DISTINCT b. SELECT c. CHAR d. MYTABLE
d. MYTABLE
A(n) ____ subquery is one that can return several rows of results. a. correlated b. single-row c. uncorrelated d. multiple-row
d. Multiple-Row
When sorting the results in descending order, which of the following values will be presented first in the output? a. numeric b. date c. character d. NULL
d. NULL
Which of the following functions can be used to substitute another value for a NULL value during calculations? a. NULLVAL b. NULVAL c. NLV d. NVL
d. NVL
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 of the following terms refers to a collection of privileges? a. grouping b. groupset c. accesset d. role
d. Role
The __________ function is based upon the concept of a normal distribution. A. MIN B. MAX C. SUM D. STDDEV
d. STDDEV
Which of the following is used to return a portion of a character string? a. SUBSTRING b. PARTSTR c. PORTION d. SUBSTR
d. SUBSTR
The outer query receives its input from the ____. a. outer view b. inner view c. nested function d. subquery
d. Subquery
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 ( ).
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
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
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
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
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
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 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
Which of the following keywords is used to create an equality join? a. NATURAL JOIN b. JOIN...ON c. JOIN...USING d. all of the above
d. all of the above
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
A subquery must include a(n) ____ clause. a. WHERE b. SELECT c. FROM d. both a and b
d. both a and b
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
Combining the contents of two or more columns is known as ____. a. suppression b. selection c. projection d. concatenation
d. concatenation
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)
When the WHERE clause contains multiple types of operators, which of the following is resolved last? a. comparison operators b. arithmetic operations c. search patterns d. logical operators
d. logical operators
Which of the following is the standard abbreviation for the constraint NOT NULL? a. nnv b. nl c. nv d. nn
d. nn
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
Which of the following SQL statements will remove the SELECT ANY TABLE privilege from the database so it can never be granted to another user? a. REMOVE SELECT ANY TABLE; b. REVOKE SELECT ANY TABLE FROM data_dictionary; c. REVOKE SELECT ANY TABLE; d. none of the above
d. none of the above
Which of the following symbols can be used to combine data from different columns into one column of output? a. % b. ^ c. * d. ||
d. ||
Which command will create a sequence named NEWSEQUENCE to generate a series of integers? a. CREATE SEQUENCE STARTING WITH 5; b. CREATE SEQUENTIAL newsequence STARTING WITH 5; c. none of the above d. CREATE SEQUENCE newsequence;
d. CREATE SEQUENCE newsequence;
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
