Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Which command should you use to copy data from one table and have it added to an existing table?
You have designed and created tables for the City Jail database. These tables don€™t include any constraints. Review the information to determine what constraints you might need for the City Jail database. First, using the format in the following chart, create a list of constraints
Create two tables based on the E-R model shown in Figure 4-41 and the business rules in the following list for a work order tracking database. Include all the constraints in the CREATE TABLE statements. You should have only two CREATE TABLE statements and no ALTER TABLE statements. Name all
Issue the commands to disable and then enable the CHECK constraint on the Base_salary column.
Produce a list of information about all existing constraints on the STORE_REPS table.
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. Column
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.
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
Create a table named BOOK_STORES to include the columns listed in the following chart.
Add 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.
Change the STORE_REPS table so that only a Y or N can be entered in the Comm column.
Change the STORE_REPS table so that NULL values can’t be entered in the name columns (First and Last).
Modify the following SQL command so that the Rep_ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column. (The Comm column indicates whether the sales representative earns commission.)CREATE TABLE store_reps(rep_ID NUMBER (5),last VARCHAR2 (15),first
Which of the following is the valid syntax for adding a PRIMARY KEY constraint to an existing table?a. ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (columnname);b. ALTER TABLE tablename ADD CONSTRAINT (columnname) PRIMARY KEY constraintname;c. ALTER TABLE tablename ADD [CONSTRAINT
Which of the following types of restrictions can be viewed with the DESCRIBE command?a. NOT NULLb. FOREIGN KEYc. UNIQUEd. CHECK
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?a. 1b. 2c. 3d. 4
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?a. ALTER TABLE…MODIFYb. ALTER TABLE…ADDc. ALTER TABLE…DISABLEd. None of the above commands
Which of the following types of constraints can’t be created at the table level?a. NOT NULLb. PRIMARY KEYc. CHECKd. FOREIGN KEYe. None of the above constraints can be created at the table level.
Which of the following data dictionary objects should be used to view information about the constraints in a database?a. USER_TABLESb. USER_RULESc. USER_COLUMNSd. USER_CONSTRAINTSe. None of the above objects should be used.
Which 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. CASCADEb. CASCADE ON DELETEc. DELETE ON CASCADEd. DROPe. ON DELETE CASCADE
Which of the following commands can be used to enable a disabled constraint?a. ALTER TABLE…MODIFYb. ALTER TABLE…ADDc. ALTER TABLE…DISABLEd. ALTER TABLE…ENABLE
Which type of constraint should you use on a column to allow entering only values above 100?a. PRIMARY KEYb. UNIQUEc. CHECKd. NOT NULL
In 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
Which of the following is a valid SQL statement?a. CREATE TABLE tablel (col1 NUMBER PRIMARY KEY, col2 VARCHAR2 (20) PRIMARY KEY, col3 DATE DEFAULT SYSDATE, col4 VARCHAR2 (2));b. CREATE TABLE tablel (col1 NUMBER PRIMARY
Which of the following commands can you use to rename a constraint?a. RENAMEb. ALTER CONSTRAINTc. MOVEd. NEW NAMEe. None of the above commands can be used.
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. 0b. 1c. 30d. 255
The FOREIGN KEY constraint should be added to which table?a. The table representing the "one" side of a one-to-many relationshipb. The parent table in a parent-child relationshipc. The child table in a parent-child relationshipd. The table that doesn’t have a primary key
How many NOT NULL constraints can be created at the table level by using the CREATE TABLE command?a. 0b. 1c. 12d. 30e. 255
Which of the following is a valid SQL command?a. ALTER TABLE books ADD CONSTRAINT UNIQUE (pubid);b. ALTER TABLE books ADD CONSTRAINT PRIMARY KEY (pubid);c. ALTER TABLE books MODIFY (pubid CONSTRAINT NOT NULL);d. ALTER TABLE books ADD FOREIGN KEY CONSTRAINT (pubid) REFERENCES publisher (pubid);e.
What is the maximum number of PRIMARY KEY constraints allowed for a table?a. 1b. 2c. 30d. 255
Which of the following SQL statements is invalid and returns an error message?a. ALTER TABLE books ADD CONSTRAINT books_pubid_uk UNIQUE (pubid);b. ALTER TABLE books ADD CONSTRAINT books_pubid_pk PRIMARY KEY (pubid);c. ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid);d. ALTER TABLE
Which of the following is not a valid constraint type?a. PRIMARY KEYSb. UNIQUEc. CHECKd. FOREIGN KEY
Which of the following statements is correct?a. A PRIMARY KEY constraint allows NULL values in the primary key column(s).b. You can enable a dropped constraint if you need it in the future.c. Every table must have at least one PRIMARY KEY constraint, or Oracle 12c doesn’t allow the table to be
What is the simplest way to determine whether a particular column can contain NULL values?
What is the difference between disabling a constraint and dropping a constraint?
To which table do you add a FOREIGN KEY constraint if you want to make certain every book ordered exists in the BOOKS table?
When must you define constraints at the table level rather than the column level?
How is adding a NOT NULL constraint to an existing table different from adding other types of constraints?
Which type of constraint should you use to ensure that every book has a profit margin between 15% and 25%?
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?
A table can have a maximum of how many PRIMARY KEY constraints?
How can you verify the constraints that exist for a table?
What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?
You designed the new database for City Jail. Now you need to create all the tables for the database. First, create all the tables using the information outlined in Section A. Second, make the modifications outlined in Section B. Save all SQL statements used to accomplish these tasks.Section
The management of JustLee Books has approved implementing a new commission policy and benefits plan for the account managers. The following changes need to be made to the existing database:Two new columns must be added to the ACCTMANAGER table: one to indicate the commission classification assigned
Delete the BOOK_PRICING table permanently so that it isn’t moved to the recycle bin. Delete the JL_EMPS table so that it can be restored. Restore the JL_EMPS table and verify that it’s available again.
Truncate the BOOK_PRICING table, and then verify that the table still exists but no longer contains any data.
Mark the Category column of the BOOK_PRICING table as unused. Verify that the column is no longer available.
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.
Rename the EMPLOYEES table as JL_EMPS.
Delete the EndDate column from the EMPLOYEES table.
Modify the Job_class column of the EMPLOYEES table so that it allows storing a maximum width of two characters.
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.
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
Create 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
Which of the following SQL statements changes the size of the Title column in the BOOKS table from the current length of 30 characters to the length of 35 characters?a. ALTER TABLE books CHANGE title VARCHAR(35);b. ALTER TABLE books MODIFY (title VARCHAR2(35));c. ALTER TABLE books MODIFY title
Which object in the data dictionary enables you to verify DEFAULT column settings?a. DEFAULT_COLUMNSb. DEF_TAB_COLUMNSc. USER_TAB_COLUMNSd. None of the above
Which of the following is a valid datatype?a. CHAR3b. VARCHAR4(3)c. NUMd. NUMBER
Which of the following is a valid table name?a. 9NEWTABLEb. DATE9c. NEW”TABLEd. None of the above are valid table names.
Which of the following commands creates a new table containing two columns?a. CREATE TABLE newname (coll DATE, col2 VARCHAR2);b. CREATE TABLE newname AS (SELECT title, retail, cost FROM books);c. CREATE TABLEnewname (coll, col2);d. CREATE TABLE newname (coll DATE DEFAULT SYSDATE, col2 VARCHAR2(1));
Which of the following commands is valid?a. RENAME customer# TO customernumber FROM customers;b. ALTER TABLE customers RENAME customer# TO customernum;c. DELETE TABLE customers;d. ALTER TABLE customers DROP UNUSED COLUMNS;
Which of the following is true?a. All data in a table can be recovered if the table is dropped with the PURGE option.b. All data in a table can be recovered from the recycle bin if the table is dropped.c. All data in a table is lost if the table is dropped.d. All of the above statements are true.
Which of the following characters can be used in a table name?a. –b. (c. %d. !
Identify at least three reasons an organization might analyze historical sales data stored in its database.
What type of relationship can’t be stored in a relational database? Why?
List the steps of the normalization process.
Describe how a foreign key is different from a primary key.
Explain the role of a primary key.
Discuss the problems that can be caused by data redundancy.
Define a one-to-many relationship.
Give an example of three entities that might exist in a database for a medical office, and list some attributes that would be stored in a table for each entity.
What is the purpose of an E-R model?
Which of the following is not a valid statement?a. You can change the name of a table only if it doesn’t contain any data.b. You can change the length of a column that doesn’t contain any data.c. You can delete a column that doesn’t contain any data.d. You can add a column to a table.
The default width of a VARCHAR2 field is:a. 1b. 30c. 255d. None—there’s no default width for a VARCHAR2 field.
Which of the following commands changes a table’s name from OLDNAME to NEWNAME?a. RENAME oldname TO newname;b. RENAME table FROM oldname TO newname;c. ALTER TABLE oldname MODIFY TO newname;d. CREATE TABLE newname (SELECT * FROM oldname);
Which of the following commands removes all data from a table but leaves the table’s 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 statements is correct?a. A table can contain a maximum of only one column marked as unused.b. You can delete a table by removing all columns in the table.c. Using the SET UNUSED clause allows you to free up storage space used by a column.d. None of the above statements are
Which of the following commands drops any columns marked as unused from the SECUSTOMERORDERS table?a. DROP COLUMN FROM secustomerorders WHERE column_status = UNUSED;b. ALTER TABLE secustomerorders DROP UNUSED COLUMNS;c. ALTER TABLE secustomerorders DROP (unused);d. DROP UNUSED COLUMNS;
Which of the following commands creates a new table containing a virtual column?a. CREATE TABLE newtable AS (SELECT order#, title, quantity, retail FROM orders);b. CREATE TABLE newtable (price NUMBER(3), total NUMBER(8,2));c. CREATE TABLE newtable (calcl NUMBER(4), calc2 NUMBER(4);d. CREATE TABLE
Which of the following is true?a. If you truncate a table, you can’t 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
Which of the following is not a valid SQL statement?a. CREATE TABLE anothernewtable (newtableid VARCHAR2(2));b. CREATE TABLE anothernewtable (date, anotherdate) AS (SELECT orderdate, shipdate FROM orders);c. CREATE TABLE anothernewtable (firstdate, seconddate) AS (SELECT orderdate, shipdate FROM
Which of the following is not a correct statement?a. A table can be modified only if it doesn’t contain any rows of data.b. The maximum number of characters in a table name is 30.c. You can add more than one column at a time to a table.d. You can’t recover data contained in a table that has
Which of the following is a valid SQL statement?a. ALTER TABLE secustomersspent ADD DATE lastorder;b. ALTER TABLE secustomerorders DROP retail;c. CREATE TABLE newtable AS(SELECT * FROM customers);d. ALTER TABLE drop column *;
Which of the following is a correct statement?a. You can restore the data deleted with the DROP COLUMN clause, but not the data deleted with the SET UNUSED clause.b. You can’t create empty tables—all tables must contain at least three rows of data.c. A table can contain a maximum of 1000
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?
What 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?
If you add a new column to an existing table, where does the column appear in relation to existing columns?
Explain the difference between truncating a table and deleting a table.
What happens to the existing rows of a table if the DEFAULT value of a column is changed?
How many columns can be dropped in one ALTER TABLE command?
What is the difference between dropping a column and setting a column as unused?
What guidelines should you follow when naming tables and columns in Oracle 12c?
List four datatypes supported by Oracle 12c, and provide an example of data that could be stored by each datatype.
Which command is used to create a table based on data already contained in an existing table?
The management of JustLee Books has submitted two requests. The first is for a mailing list of all customers stored in the CUSTOMERS table. The second is for a list of the percentage of profit generated by each book in the BOOKS table. The requests are as follows:1. Create a mailing list from the
List 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.
Create a list of authors that displays the last name followed by the first name for each author. The last names and first names should be separated by a comma and a blank space.
Create 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.
List the customer number from the ORDERS table for each customer who has placed an order with the bookstore. List each customer number only once.
Determine which categories are represented in the current book inventory. List each category only once.

Showing 400 - 500 of 566

Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Step by Step Answers