Bartleby Sitemap - Textbook Solutions
All Textbook Solutions for Oracle 12c: SQL
1RQ2RQ3RQ4RQDiscuss the problems that can be caused by data redundancy.Explain the role of a primary key.7RQ8RQWhat type of relationship can’t be stored in a relational database? Why?
Identify at least three reasons an organization might analyze historical sales data stored in its database.
1MC2MC3MC4MC5MC6MC7MCWhich of the following has no partial or transitive dependencies?
unnormalized data
1NF
2NF
3NF
9MCWhich of the following has no partial dependencies but can contain transitive dependencies? a. unnormalized data b. 1NF c. 2NF d. 3NFWhich of the following has no repeating groups but can contain partial or transitive dependencies?
unnormalized data
1NF
2NF
3NF
12MC13MCA unique identifier for a data row that consists of more than one field is commonly called a:
primary plus key
composite primary key
foreign key
none of the above
Which of the following symbols represents an optional relationship in an E-R model? a. a straight line b. a dashed line c. a straight line with a crows foot at both ends d. a straight line with a crows foot at one end16MCWhich of the following represents a field in a table?
a record
a row
a column
an entity
Which of the following defines a relationship in which data can have multiple occurrences in each entity?
one-to-one
one-to-many
many-to-many
none of the above
When part of the data in a table depends on a field in the table that isn’t the table’s primary key, it’s known as:
transitive dependency
partial dependency
psychological dependency
a foreign key
Which of the following is used to join data contained in two or more tables? a. primary key b. unique identifier c. common field d. foreign keyWhich tables and fields would you access to determine which book titles have been purchased by a customer and when the order shipped?
2HOA3HOA4HOAAccess path A database table is composed of records and fields hold data. Data is stored in records. A field is a part of a record and contains a single piece of data for the subject of the record. The access path for finding the list of books is AUTHOR: AuthorID, Lname BOOKAUTHOR: AuthorID, ISBN BOOKS: Pubdate, ISBN6HOA7HOA8HOA9HOA10HOA1RQWhat are the two required clauses for a SELECT statement?What is the purpose of the SELECT statement?
What does an asterisk () in the SELECT clause of a SELECT statement represent?What is the purpose of a column alias?How do you indicate that a column alias should be used?
When is it appropriate to use a column alias?
What are the guidelines to keep in mind when using a column alias?
9RQWhat is a NULL value?
Which of the following SELECT statements displays a list of customer names from the CUSTOMERS table?
SELECT customer names FROM customers;
SELECT “Names” FROM customers;
SELECT firstname, lastname FROM customers;
SELECT firstname, lastname, FROM customers;
SELECT firstname, lastname, “Customer Names” FROM customers;
Which clause is required in a SELECT statement?
WHERE
ORDER BY
GROUP BY
FROM
all of the above
Which of the following is not a valid SELECT statement?
SELECT lastname, firstname FROM customers;
SELECT * FROM orders;
Select FirstName NAME from CUSTOMERS;
SELECT lastname Last Name FROM customers;
Which of the following symbols represents concatenation? a. b. || c. [] d. ''5MC6MCWhich of the following is a valid SELECT statement? a. SELECT TITLES TITLE! FROM BOOKS; b. SELECT customer# FROM books; c. SELECT title AS Book Title from books; d. all of the aboveWhich of the following symbols is used in a SELECT clause to display all columns from a table?
/
&
*
"
9MCWhen must a comma be used in the SELECT clause of a query? a. when a field name is followed by a column alias b. to separate the SELECT clause and the FROM clause when only one field is selected c. Its never used in the SELECT clause. d. when listing more than one field name and the fields arent concatenated e. when an arithmetic expression is included in the SELECT clause11MCWhich clause is not required in a SELECT statement?
SELECT
FROM
WHERE
All of the above clauses are required.
Which of the following lines of the SELECT statement contains an error? 1 SELECT title, isbn, 2 Pubdate "Date of Publication" 3 FROM books; a. line 1 b. line 2 c. line 3 d. There are no errors.Which of the following lines of the SELECT statement contains an error? 1 SELECT ISBN, 2 retail-cost 3 FROM books; a. line 1 b. line 2 c. line 3 d. There are no errors.Which of the following lines of the SELECT statement contains an error? 1 SELECT title, cost, 2 cost2 3 'With 200% Markup' 4 FROM books; a. line 1 b. line 2 c. line 3 d. line 4 e. There are no errors.Which of the following lines of the SELECT statement contains an error?
1 SELECT name, contact,
2 "Person to Call", phone
3 FROM publisher;
line 1
line 2
line 3
There are no errors.
Which of the following lines of the SELECT statement contains an error?
1 SELECT ISBN, || 'is the ISBN for the book named' ||
2 title
3 FROM books;
line 1
line 2
line 3
There are no errors.
Which of the following lines of the SELECT statement contains an error?
1 SELECT title, category
2 FORM books;
line 1
line 2
There are no errors.
Which of the following lines of the SELECT statement contains an error? 1 SELECT name, contact 2 "Person to Call", phone 3 FROM publisher; a. line 1 b. line 2 c. line 3 d. There are no errors.Which of the following lines of the SELECT statement contains an error?
1 SELECT *
2 FROM publishers;
line 1
line 2
There are no errors.
1HOA2HOA3HOA4HOA5HOA6HOA7HOACreate a list of each book title stored in the BOOKS table and the category in which each book belongs. Reverse the sequence of the columns so that the category of each book is listed first.
9HOAList all information for each order item. Include an item total, which can be calculated by multiplying the Quantity and Paideach columns. Use a column alias for the calculated value to show the heading Item Total in the output.Which command is used to create a table based on data already contained in an existing table?
2RQWhat guidelines should you follow when naming tables and columns in Oracle 12c?What is the difference between dropping a column and setting a column as unused?
5RQWhat happens to the existing rows of a table if the DEFAULT value of a column is changed?
Explain the difference between truncating a table and deleting a table.
8RQWhat happens if you try to decrease the scale or precision of a NUMBER column to a value less than the data already stored in the field?
Are a table and the data contained in the table erased from the system permanently if a DROP TABLE command is issued on the table?
Which of the following is a correct statement?
You can restore the data deleted with the DROP COLUMN clause, but not the data deleted with the SET UNUSED clause.
You can’t create empty tables—all tables must contain at least three rows of data.
A table can contain a maximum of 1000 columns.
The maximum length of a table name is 265 characters.
2MCWhich of the following is not a correct statement?
A table can be modified only if it doesn’t contain any rows of data.
The maximum number of characters in a table name is 30.
You can add more than one column at a time to a table.
You can’t recover data contained in a table that has been truncated.
Which of the following is not a valid SQL statement?
CREATE TABLE anothernewtable (newtableid VARCHAR2 (2));
CREATE TABLE anothernewtable (date, anotherdate)
AS (SELECT orderdate, shipdate FROM orders);
CREATE TABLE anothernewtable (firstdate, seconddate)
AS (SELECT orderdate, shipdate FROM orders);
All of the above are valid statements.
Which of the following is true? a. If you truncate a table, you cant add new data to the table. b. If you change the default value of an existing column, all existing rows containing a NULL value in the same column are set to the new DEFAULT value. c. If you delete a column from a table, you cant add a column to the table with the same name as the previously deleted column. d. If you add a column to an existing table, its always added as the last column of the table.Which of the following commands creates a new table containing a virtual column? a. CREATE TABLE newt able AS (SELECT order#, title, quantity, retail FROM orders); b. CREATE TABLE newt able (price NUMBER (3), total NUMBER (8, 2)); c. CREATE TABLE newt able (calc1 NUMBER (4), calc2 NUMBER (4); d. CREATE TABLE newt able (cola NUMBER (3), colb NUMBER (3), cole AS (cola+colb));Which of the following commands drops any columns marked as unused from the SECUSTOMERORDERS table?
DROP COLUMN FROM secustomerorders WHERE column_status = UNUSED
ALTER TABLE secustomerorders DROP UNUSED COLUMNS;
ALTER TABLE secustomerorders DROP (unused);
DROP UNUSED COLUMNS;
Which of the following statements is correct?
A table can contain a maximum of only one column marked as unused.
You can delete a table by removing all columns in the table.
Using the SET UNUSED clause allows you to free up storage space used by a column.
None of the above statements are correct.
Which of the following commands removes all data from a table but leaves the tables structure intact? a. ALTER TABLE secustomerorders DROP UNUSED COLUMNS; b. TRUNCATE TABLE secustomerorders; c. DELETE TABLE secustomerorders; d. DROP TABLE secustomerorders;Which of the following commands changes a table’s name from OLDNAME to NEWNAME?
RENAME oldname TO newname;
RENAME cable FROM oldname TO newname;
ALTER TABLE oldname MODIFY TO newname;
CREATE TABLE newname (SELECT * FROM oldname);
11MCWhich of the following is not a valid statement?
You can change the name of a table only if it doesn’t contain any data.
You can change the length of a column that doesn’t contain any data.
You can delete a column that doesn’t contain any data.
You can add a column to a table.
Which of the following characters can be used in a table name? a. b. ( c. % d. !Which of the following is true?
All data in a table can be recovered if the table is dropped with the PURGE option.
All data in a table can be recovered from the recycle bin if the table is dropped.
All data in a table is lost if the table is dropped.
All of the above statements are true.
15MCWhich of the following commands creates a new table containing two columns?
CREATE TABLE newname (coll DATE, col2 VARCHAR2);
CREATE TABLE newname AS (SELECT title, retail, cost FROM books);
CREATE TABLE newname (col1, co12);
CREATE TABLE newname (col1 DATE DEFAULT SYSDATE, co12 VARCHAR2 (1));
Which of the following is a valid table name? a. 9NEWTABLE b. DATE9 c. NEWTABLE d. None of the above are valid table names.18MCWhich object in the data dictionary enables you to verify DEFAULT column settings?
DEFAULT_COLUMNS
DEF_TAB_COLUMNS
USER_TAB_COLUMNS
None of the above
20MCCreate a new table containing the category code and description for the categories of books sold by JustLee Books. The table should be called CATEGORY, and the columns should be CatCode and CatDesc. The CatCode column should store a maximum of 2 characters, and the CatDesc column should store a maximum of 10 characters.
Create a new table containing these four columns: Emp#, Lastname, Firstname, and Job_class. The table name should be EMPLOYEES. The Job_class column should be able to store character strings up to a maximum length of four, but the column values shouldn’t be padded if the value has less than four characters. The Emp# column contains a numeric ID and should allow a five-digit number. Use column sizes you consider suitable for the Firstname and Lastname columns.
Add two columns to the EMPLOYEES table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the system date. The second column, named EndDate, contains employees date of termination.Modify the Job_class column of the EMPLOYEES table so that it allows storing a maximum width of two characters.5HOARename the EMPLOYEES table as JL_EMPS.
Create a new table containing these four columns from the existing BOOKS table: ISBN, Cost, Retail, and Category. The name of the ISBN column should be ID, and the other columns should keep their original names. Name the new table BOOK_PRICING.8HOATruncate the BOOK_PRICING table, and then verify that the table still exists but no longer contains any data.Delete the BOOK_PRICING table permanently so that it isnt moved to the recycle bin. Delete the JL_EMPS table so that it can be restored. Restore the JL_EMPS table and verify that its available again.What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?How can you verify the constraints that exist for a table?A table can have a maximum of how many PRIMARY KEY constraints?Which type of constraint can be used to make certain the category for a book is included when a new book is added to inventory?Which type of constraint should you use to ensure that every book has a profit margin between 15% and 25%?How is adding a NOT NULL constraint to an existing table different from adding other types of constraints?
When must you define constraints at the table level rather than the column level?8RQWhat is the difference between disabling a constraint and dropping a constraint?10RQ1MCWhich of the following is not a valid constraint type? a. PRIMARY KEYS b. UNIQUE c. CHECK d. FOREIGN KEYWhich of the following SQL statements is invalid and returns an error message?
ALTER TABLE books ADD CONSTRAINT books_pubid_uk UNIQUE (pubid);
ALTER TABLE books ADD CONSTRAINT books_pubid_pk PRIMARY KEY (pubid);
ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid);
ALTER TABLE books ADD CONSTRAINT books_pubid_fk FOREIGN KEY (pubid) REFERENCES publisher (pubid);
All of the above statements are invalid.
What is the maximum number of PRIMARY KEY constraints allowed for a table? a. 1 b. 2 c. 30 d. 2555MCHow many NOT NULL constraints can be created at the table level by using the CREATE TABLE command?
0
1
12
30
255
The FOREIGN KEY constraint should be added to which table?
the table representing the “one” side of a one-to-many relationship
the parent table in a parent-child relationship
the child table in a parent-child relationship
the table that doesn’t have a primary key
What is the maximum number of columns you can define as a primary key when using the column-level approach to creating a table? a. 0 b. 1 c. 30 d. 255Which of the following commands can you use to rename a constraint?
RENAME
ALTER CONSTRAINT
MOVE
NEW NAME
None of the above commands can be used.
10MCIn the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entries in the specified columns to be unique, which of the following statements is correct? a. The constraint can be created only with the ALTER TABLE command. b. The constraint can be created only with the table-level approach. c. The constraint can be created only with the column-level approach. d. The constraint can be created only with the ALTER TABLE MODIFY command.Which type of constraint should you use on a column to allow entering only values above 100? a. PRIMARY KEY b. UNIQUE c. CHECK d. NOT NULLWhich of the following commands can be used to enable a disabled constraint? a. ALTER TABLE MODIFY b. ALTER TABLE ADD c. ALTER TABLE DISABLE d. ALTER TABLE ENABLEWhich of the following keywords allows the user to delete a record from a table, even if rows in another table reference the record through a FOREIGN KEY constraint? a. CASCADE b. CASCADE ON DELETE c. DELETE ON CASCADE d. DROP e. ON DELETE CASCADEWhich of the following data dictionary objects should be used to view information about the constraints in a database?
USER_TABLES
USER_RULES
USER_COLUMNS
USER_CONSTRAINTS
None of the above objects should be used.
Which of the following types of constraints cant be created at the table level? a. NOT NULL b. PRIMARY KEY c. CHECK d. FOREIGN KEY e. None of the above constraints can be created at the table level.Suppose you created a PRIMARY KEY constraint at the same time you created a table and later decide to name the constraint. Which of the following commands can you use to change the constraint’s name?
ALTER TABLE … MODIFY
ALTER TABLE … ADD
ALTER TABLE … DISABLE
None of the above commands can be used.
You’re creating a new table consisting of three columns: Col1, Col2, and Col3. Col1 should be the primary key and can’t have any NULL values, and each entry should be unique. Col3 must not contain any NULL values either. How many total constraints do you have to create?
1
2
3
4
Which of the following types of restrictions can be viewed with the DESCRIBE command?
NOT NULL
FOREIGN KEY
UNIQUE
CHECK
Which of the following is the valid syntax for adding a PRIMARY KEY constraint to an existing table?
ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (columnname);
ALTER TABLE tablename ADD CONSTRAINT (columnname) PRIMARY KEY constraintname;
ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY;
None of the above is valid syntax.
1HOA2HOA3HOAAdd a column named Base_salary with a datatype of NUMBER(7,2) to the STORE_REPS table. Ensure that the amount entered is above zero.Create a table named BOOK_STORES to include the columns listed in the following chart.
Add a constraint to make sure the Rep_ID value entered in the BOOK_STORES table is a valid value contained in the STORE_REPS table. The Rep_ID columns of both tables were initially created as different datatypes. Does this cause an error when adding the constraint? Make table modifications as needed so that you can add the required constraint.
Change the constraint created in Assignment #6 so that associated rows of the BOOK_STORES table are deleted automatically if a row in the STORE_REPS table is deleted.
Create a table named REP_CONTRACTS containing the columns listed in the following chart A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep_ID and Store_ID columns.Produce a list of information about all existing constraints on the STORE_REPS table.
Issue the commands to disable and then enable the CHECK constraint on the Base_salary column.
Which command should you use to copy data from one table and have it added to an existing table?
Which command can you use to change the existing data in a table?3RQ4RQ5RQ6RQ7RQ8RQ9RQ10RQWhich of the following is a correct statement? a. A commit is issued implicitly when a user exits SQL Developer or SQLPlus. 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 b are correct. f. Both a and c are correct.Which of the following is a valid SQL statement?
SELECT * WHERE amid = ‘J100’ FOR UPDATE;
INSERT INTO homeworkl0 VALUES (SELECT * FROM acctmanager);
DELETE amid FROM acctmanager;
rollback;
all of the above
3MCWhich 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.5MC6MC7MC8MC9MC10MC11MC12MC13MC14MC15MC16MC17MC18MC19MCWhat is the maximum number of records that can be modified with a single UPDATE command?
1
2
3
unlimited
Add a new row in the ORDERS table with the following data: Order# = 1021, Customer# = 1009, and Order date = July 20, 2009.2HOASave the changes permanently to the database.
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.
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.
6HOA7HOAExecute a command that undoes the change in Step 7.9HOAExecute a command that undoes the previous deletion.1RQ2RQ3RQ4RQ5RQ6RQ7RQ8RQ9RQ10RQ1MC2MC3MC4MC5MC6MC7MC8MC9MC10MC11MC12MC13MC14MC15MC16MC17MC18MC19MC20MC1HOAAdd 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.
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 shouldnt be allowed to cycle. Name the sequence MY_FIRST_SEQ.4HOA5HOAA 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 Identity Column
Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected)
Emaildate = current date, customer# = 1007
Emailid = specify to use the column default value, emaildate = current date, customer# = 1008
Emailid = 25, emaildate = current date, customer# = 1009
7HOA8HOA9HOA10HOA11HOA1RQ2RQ3RQ4RQ5RQ6RQ7RQ8RQ9RQ10RQ1MC2MC3MC4MC5MC6MC7MC8MC9MC10MC11MC12MC13MC14MC15MC16MC17MC18MC19MC20MC1HOA2HOAAssign privileges to the new account that allow connecting to the database, creating new tables, and altering an existing table.4HOA5HOA6HOA7HOA8HOA9HOA10HOA1RQ2RQ3RQ4RQ5RQ6RQ7RQ8RQ9RQ10RQ1MC2MC3MC4MC5MC6MC7MC8MC9MC