Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Which of the following privileges allows a user to truncate tables in a database?a. DROP ANY TABLEb. TRUNCATE ANY TABLEc. CREATE TABLEd. TRUNC TABLE
Which of the following privileges must be granted to a user’s account before the user can connect to the Oracle 12c database?a. CONNECTb. CREATE SESSIONc. CONNECT ANY DATABASEd. CREATE ANY TABLE
Which of the following statements creates a user account named DeptHead?a. CREATE ROLE deptheadIDENTIFIED BY apassword;b. CREATE USER deptheadIDENTIFIED BY apassword;c. CREATE ACCOUNT depthead;d. GRANT ACCOUNT depthead;
Which of the following statements is most accurate?a. Authentication procedures prevent any data stored in the Oracle 12c database from being stolen or damaged.b. Authentication procedures are used to limit unauthorized access to the Oracle 12c database.c. Oracle 12c authentication doesn’t
Which of the following statements assigns the role CUSTOMERREP as the default role for Maurice Cain?a. ALTER ROLE mcainDEFAULT ROLE customerrep;b. ALTER USER mcainTO customerrep;c. SET DEFAULT ROLE customerrepFOR mcain;d. ALTER USER mcainDEFAULT ROLE customerrep;e. SET ROLE customerrepFOR mcain;
Which of the following commands can be used to change a password for a user account?a. ALTER PASSWORDb. CHANGE PASSWORDc. MODIFY USER PASSWORDd. ALTER USER…PASSWORDe. None of the above
How can you remove a user account from Oracle 12c?
What happens if you revoke an object privilege that was granted with the WITH GRANT OPTION? What if the privilege is removed from a user who had granted the same object privilege to three other users?
How can you assign a password to a role?
What is the purpose of a role in Oracle 12c?
What types of privileges are available in Oracle 12c? Define each type.
If you’re logged in to Oracle 12c, how can you determine which privileges are currently available to your account?
What is a privilege?
How is a user password assigned in Oracle 12c?
What does a database account with the CREATE SESSION privilege allow the user to do?
What is the purpose of data security?
1. The head DBA has requested the creation of a sequence for the primary key columns of the Criminals and Crimes tables. After creating the sequences, add a new criminal named Johnny Capps to the Criminals table by using the correct sequence. (Use any values for the remainder of columns.) A crime
To perform the following activity, refer to the tables in the JustLee Books database. Using the training you have received and speculating on query needs, determine appropriate uses for indexes and sequences in the JustLee Books database. Assume all tables will grow quite large in the number of
Change the setting of MY_FIRST_SEQ so that the minimum value that can be generated is –1000.
Issue a SELECT statement that displays NEXTVAL for MY_FIRST_SEQ three times. Because the value isn’t being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the third SELECT?
Create a sequence that generates integers starting with the value 5. Each value should be three less than the previous value generated. The lowest possible value should be 0, and the sequence shouldn’t be allowed to cycle. Name the sequence MY_FIRST_SEQ.
Add a new customer row by using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name = Frank, and zip = 23567.Data from Question 1:Create a sequence for populating the Customer# column of the CUSTOMERS table.
Many queries search by the number of days to ship (number of days between the order and shipping dates). Create an index that might improve the performance of these queries.
Create a B-tree index on the customer’s Lastname column. Verify that the index exists by querying the data dictionary. Remove the index from the database.
Create a bitmap index on the CUSTOMERS table to speed up queries that search for customers based on their state of residence. Verify that the index exists, and then delete the index.
Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN synonym and MY_FIRST_SEQ.
Create a private synonym that enables you to reference the MY_FIRST_SEQ object as NUMGEN.
A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below. • Tablename: email_log • Columns: emailid (numeric), emaildate (datetime), customer# (numeric) • Primary key: emailid column, define as an
Create a sequence for populating the Customer# column of the CUSTOMERS table. When setting the start and increment values, keep in mind that data already exists in this table. The options should be set to not cycle the values and not cache any values, and no minimum or maximum values should be
Which of the following commands creates a private synonym?a. CREATE PRIVATE SYNONYMb. CREATE NONPUBLIC SYNONYMc. CREATE SYNONYMd. CREATE PUBLIC SYNONYM
What can be referenced to determine whether an index is used to perform a query?a. USER_INDEXES viewb. Query source codec. Explain pland. Database access plan
Which of the following isn’t a valid option for the CREATE SEQUENCE command?a. ORDERb. NOCYCLEc. MINIMUMVALd. NOCACHEe. All of the above are valid options.
Which of the following can be used to determine whether an index exists?a. DESCRIBE indexname;b. The USER_INDEXES viewc. The INDEXES tabled. The USER_INDEX viewe. All of the abovef. None of the above
Which of the following is a valid command?a. CREATE INDEX book_profit_idxON (retail-cost) WHERE (retail-cost) > 10;b. CREATE INDEX book_profit_idxON (retail-cost);c. CREATE FUNCTION INDEX book_profit_idxON books WHERE (retail-cost) > 10;d. Both a and ce. None of the above
If the CACHE or NOCACHE options aren’t included in the CREATE SEQUENCE command, which of the following statements is correct?a. Oracle 12c generates 20 integers automatically and stores them in memory.b. No integers are cached by default.c. Only one integer is cached at a time.d. The command will
Which node of the B-tree index contains ROWIDs?a. Branch blocksb. Root blockc. Leaf blocksd. None of the above because the primary key is used to identify rows.
Which of the following settings can’t be modified with the ALTER SEQUENCE command?a. INCREMENT BYb. MAXVALUEc. START WITHd. MINVALUEe. CACHE
Oracle 12c automatically creates an index for which type of constraints?a. NOT NULLb. PRIMARY KEYc. FOREIGN KEYd. UNIQUE KEYe. None of the abovef. Only a and bg. Only b and d
If a column has low selectivity, this means:a. The column contains many distinct values.b. The column contains a small number of distinct values.c. A WHERE clause is always used in a query on the column.d. The selectivity of a column can’t be determined.
If a column has high selectivity or cardinality, which index type is most appropriate?a. IOTb. B-treec. Bitmapd. Function-based index
When is creating an index manually inappropriate?a. When queries return a large percentage of rows in the resultsb. When the table is smallc. When the majority of table operations are updatesd. All of the abovee. Only a and c
Which of the following statements is true?a. A gap can appear in a sequence created with the NOCACHE option if the system crashes before a user can commit a transaction.b. Any unassigned sequence values appears in the USER_SEQUENCE data dictionary table as unassigned.c. Only the user who creates a
Given the table created in Question 6, which of the following commands can Juan use to create a synonym that allows anyone to access the table without having to identify his schema in the table reference?a. CREATE SYNONYM thetable FOR juan.mytable;b. CREATE PUBLIC SYNONYM
Suppose the user Juan creates a table called MYTABLE with four columns. The first column has a PRIMARY KEY constraint, the second column has a NOT NULL constraint, the third column has a CHECK constraint, and the fourth column has a FOREIGN KEY constraint. Given this information, how many indexes
Which of the following is a valid SQL statement?a. INSERT INTO publisher VALUES (pubsequence.nextvalue, 'HAPPY PRINTING', 'LAZY LARRY', NULL);b. CREATE INDEX a_new_index ON (firstcolumn*.02);c. CREATE SYNONYM pub FOR
Which of the following generates an integer in a sequence?a. NEXTVALb. CURVALc. NEXT_VALUEd. CURR_VALUEe. NEXT_VALf. CUR_VAL
Which of the following commands can you use to modify an index?a. ALTER SESSIONb. ALTER TABLEc. MODIFY INDEXd. ALTER INDEXe. None of the above
Which syntax is correct for removing a public synonym?a. DROP SYNONYM synonymname;b. DELETE PUBLIC SYNONYM synonymname;c. DROP PUBLIC SYNONYM synonymname;d. DELETE SYNONYM synonymname;
Which of the following generates a series of integers that can be stored in a database?a. A number generatorb. A viewc. A sequenced. An indexe. A synonym
What is the purpose of a synonym?
What command is used to modify an index?
What is an IOT and under what circumstances might it be useful?
Under what circumstances should you not create an index for a table?
When does Oracle 12c automatically create an index for a table?
What is the difference between the B-tree and bitmap index structures?
When is an index appropriate for a table?
How can you indicate that the values generated by a sequence should be in descending order?
How can gaps appear in values generated by a sequence?
How can a sequence be used in a database?
Execute the CityJail_5.sql script to rebuild the CRIMINALS and CRIMES tables of the City Jail database. The statements at the beginning of this script drop existing tables in your schema with the same table names. Review the script so that you€™re familiar with the table structure and
Currently, the contents of the Category column in the BOOKS table are the actual name for eachcategory. This structure presents a problem if one user enters COMPUTER for the Computercategory and another user enters COMPUTERS. To avoid this and other problems that mightoccur, the database designers
Execute a command that undoes the previous deletion.
Delete Order# 1005. You need to address both the master order record and the related detail records.
Execute a command that undoes the change in Step 7.
Execute the script and set the following values: isbn = 1059831198 and cost = $20.00.
Create a script using substitution variables that allows a user to set a new cost amount for a book based on the ISBN.
Add a new row in the ORDERS table with the following data: Order# = 1023 and Customer# = 1009. Describe the error raised and what caused the error.
Add a new row in the ORDERS table with the following data: Order# = 1022, Customer# = 2000, and Order date = August 6, 2009. Describe the error raised and what caused the error.
Save the changes permanently to the database.
Modify the zip code on order 1017 to 33222.
Add a new row in the ORDERS table with the following data: Order# = 1021, Customer# = 1009, and Order date = July 20, 2009.
What is the maximum number of records that can be modified with a single UPDATE command?a. 1b. 2c. 3d. Unlimited
Which of the following is a correct statement?a. If you attempt to add a record that violates a constraint for one of the table’s columns, only the valid columns for the row are added.b. A subquery nested in the VALUES clause of an INSERT INTO command can return only one value without generating
What is the maximum number of rows that can be deleted from a table at one time?a. 1b. 2c. 3d. Unlimited
A rollback occurs automatically when:a. A DDL command is executed.b. A DML command is executed.c. The user exits the system.d. None of the above
Which of the following is the standard extension used for a script file?a. .sptb. .srtc. .scriptd. .sql
You accidentally deleted all the orders in the ORDERS table. How can the error be corrected after a COMMIT command has been issued?a. ROLLBACK;b. ROLLBACK COMMIT;c. REGENERATE RECORDS orders;d. None of the above restores the deleted orders.
How many rows can be added to a table by executing the INSERT INTO…VALUES command?a. 1b. 2c. 3d. unlimited
Which of the following commands removes all orders placed before April 1, 2009?a. DELETE FROM orders WHERE orderdate <‘01-APR-09’;b. DROP FROM orders WHERE orderdate <‘01-APR-09’;c. REMOVE FROM orders WHERE orderdate <‘01-APR-09’;d. DELETE FROM orders WHERE orderdate
Assume you have added eight new orders to the ORDERS table. Which of the following is true?a. Other users can view the new orders as soon as you execute the INSERT INTO command.b. Other users can view the new orders as soon as you issue a ROLLBACK command.c. Other users can view the new orders as
Which of the following releases a lock currently held by a user on the HOMEWORK10 table?a. A COMMIT command is issued.b. A DDL command is issued to end a transaction.c. The user exits the system.d. A ROLLBACK command is issued.e. all of the abovef. none of the above
You issue the following command: INSERT INTO homework10 (col1, col2, col3) VALUES (‘A’, NULL, ‘C’). The command will fail if which of the following statements is true?a. Col1 has a PRIMARY KEY constraint enabled.b. Col2 has a UNIQUE constraint enabled.c. Col3 is defined as a DATE column.d.
Which of the following commands locks the HOMEWORK10 table in EXCLUSIVE mode?a. LOCK TABLE homework10 EXCLUSIVELY;b. LOCK TABLE homework10 IN EXCLUSIVE MODE;c. LOCK TABLE homework10 TO OTHER USERS;d. LOCK homework10 IN EXCLUSIVE MODE;e. Both b and d lock the table in EXCLUSIVE mode.
Which of the following input values results in a successful INSERT of O’hara?a. ‘O^hara’b. ‘O’’hara’ (two single quotes following the O)c. ‘O”hara’ (a double quote following the O)d. Data values can’t contain quotes.
Which of the following symbols designates a substitution variable?a. &b. $c. #d. –
Assuming the HOMEWORK10 table has three columns (Col1, Col2, and Col3, in this order), which of the following commands stores a NULL value in Col3 of the HOMEWORK10 table?a. INSERT INTO homework10 VALUES (‘A’, ‘B’, ‘C’);b. INSERT INTO homework10 (col3, col1, col2) VALUES
Which of the following statements places a shared lock on at least a portion of a table named HOMEWORK10?a. SELECT * FROM homework10 WHERE col2 IS NULL FOR UPDATE;b. INSERT INTO homework10 (col1, col2, col3) VALUES (‘A’, ‘B’, ‘C’);c. UPDATE homework10 SET col3 = NULL WHERE col1 =
Which of the following statements deletes all rows in the HOMEWORK10 table?a. DELETE * FROM homework10;b. DELETE *.* FROM homework10;c. DELETE FROM homework10;d. DELETE FROM homework10 WHERE amid = ‘*’;e. Both c and d delete all rows in the HOMEWORK10 table.
Which of the following commands can be used to add rows to a table?a. INSERT INTOb. ALTER TABLE…ADDc. UPDATEd. SELECT…FOR UPDATE
Which of the following is a valid SQL statement?a. SELECT * WHERE amid = ‘J100’ FOR UPDATE;b. INSERT INTO homeworkl0 VALUES (SELECT * FROM acctmanager);c. DELETE amid FROM acctmanager;d. rollback;e. all of the above
Which of the following is a correct statement?a. A commit is issued implicitly when a user exits SQL Developer or SQL*Plus.b. A commit is issued implicitly when a DDL command is executed.c. A commit is issued automatically when a DML command is executed.d. All of the above are correct.e. Both a and
What two methods can be used to activate a column’s DEFAULT option in an INSERT command?
What happens if a user attempts to add data to a table, and the addition would cause the record to violate an enabled constraint?
When should the VALUES clause be omitted from the INSERT INTO command?
How are NULL values included in a new record being added to a table?
How does Oracle 12c identify a substitution variable in an SQL command?
If you add a record to the wrong table, what’s the simplest way to remove the record from the table?
Explain the difference between explicit and implicit locks.
When do changes generated by DML operations become stored in database tables permanently?
Which command can you use to change the existing data in a table?

Showing 300 - 400 of 566

Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Step by Step Answers