Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Unlock the full potential of your study sessions with our comprehensive resource for "Oracle 12c SQL 3rd edition" by Joan Casteel. Access our online answers key and solution manual to explore expertly solved problems and step-by-step answers. Dive into the complete test bank and chapter solutions, perfect for both instructors and students. Whether you're looking for the instructor manual or the textbook, our platform offers free download options for easy access. Enhance your understanding with detailed questions and answers, and solutions PDF, tailored to guide you through Oracle 12c SQL concepts effortlessly.

What’s the difference between a key-preserved and a non-key-preserved table?
How can Oracle 11g determine whether clauses of a SELECT statement belong to an outer query or a subquery?
To perform these assignments, refer to the tables in the JustLee Books database. 1. Determine how many books are in the Cooking category. 2. Display the number of books with a retail price of more than $30.00. 3. Display the most recent publication date of all books sold by JustLee
What’s the name of the table provided by Oracle 11g for completing queries that don’t involve a table? a. DUMDUM b. DUAL c. ORAC d. SYS
Why are functions in this chapter referred to as “single-row” functions?
To perform these assignments, refer to the tables in the JustLee Books database. Generate and test two SQL queries for each of the following tasks: a) the SQL statement needed to perform the stated task with the traditional approach, and b) the SQL statement needed to perform the stated task with
To perform the following assignments, refer to the tables created in the JLDB_Build_8.sql script at the beginning of the chapter. Give the SQL statements and output for the following data requests: 1. Which customers live in New Jersey? List each customer’s last name, first name, and
Create and execute SQL statements to perform the following actions, using the JustLee Books database: 1. Create a new user account. The account name should be a combination of your first initial and your last name. 2. Attempt to log in to Oracle 11g with the newly created account. 3.
Which of the following displays a list of all system privileges available in Oracle 11g? a. SESSION_PRIVS b. SYS_PRIVILEGE_MAP c. V$SYSTEM_PRIVILEGES d. SYSTEM_PRIVILEGE_MAP
Which of the following statements is most accurate?a. Authentication procedures prevent any data stored in the Oracle 11g database from being stolen or damaged. b. Authentication procedures are used to limit unauthorized access to the Oracle 11g database. c. Oracle 11g authentication
How can you remove a user account from Oracle 11g?
What is the purpose of a role in Oracle 11g?
What types of privileges are available in Oracle 11g? Define each type.
If you’re logged in to Oracle 11g, how can you determine which privileges are currently available to your account?
How is a user password assigned in Oracle 11g?
If the CACHE or NOCACHE options aren’t included in the CREATE SEQUENCE command, which of the following statements is correct? a. Oracle 11g generates 20 integers automatically and stores them in memory. b. No integers are cached by default. c. Only one integer is cached at a
Oracle 11g automatically creates an index for which type of constraints? a. NOT NULL b. PRIMARY KEY c. FOREIGN KEY d. UNIQUE KEY e. Mone of the above f. Only a and b g. Only b and d
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
When does Oracle 11g automatically create an index for a table?
Which of the following is not an E-R model relationship? a. Some-to-many b. One-to-one c. One-to-many d. Many-to-many
Define a one-to-many relationship.
What is the purpose of an E-R model?
A complex view can contain which of the following?a. Data from one or more tablesb. An expressionc. A GROUP BY clause for data retrieved from one tabled. Five columns from one tablee. All of the abovef. None of the above
A simple view can contain which of the following?a. Data from one or more tablesb. An expressionc. A GROUP BY clause for data retrieved from one tabled. Five columns from one tablee. All of the abovef. None of the above
If you try to add a row to a complex view that includes a GROUP BY clause, you get which of the following error messages?a. Virtual column not allowed hereb. Data manipulation operation not legal on this viewc. Cannot map to a column in a non-key-preserved tabled. None of the above—no error
Which of the following is correct?a. The ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.b. The ORDER BY clause can’t be used in an inline view.c. The DISTINCT keyword can’t be used in an inline view.d. The WITH READ ONLY option must be used with an inline view.
To assign names to the columns in a view, you can do which of the following?a. Assign aliases in the subquery, and the aliases are used for the column names.b. Use the ALTER VIEW command to change column names.c. Assign names for up to three columns in the CREATE VIEW clause before the subquery is
A TOP-N analysis is performed by determining the rows with:a. The highest ROWNUM valuesb. A ROWNUM value greater than or equal to Nc. The lowest ROWNUM valuesd. A ROWNUM value less than or equal to N
You can’t perform any DML operations on which of the following?a. Views created with the WITH READ ONLY optionb. Views that include the DISTINCT keywordc. Views that include a GROUP BY claused. All of the above allow DML operations.e. None of the above allow DML operations.
Which of the following DML operations can’t be performed on a view containing a group function?a. INSERTb. UPDATEc. DELETEd. All of the above can be performed on a view containing a group function.e. None of the above can be performed on a view containing a group function.
This question is based on successful execution of the following command:CREATE VIEW changenameAS SELECT customer#, lastname, firstnameFROM customersWITH CHECK OPTION;Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.If the CHANGENAME view needs to include the
This question is based on successful execution of the following command:CREATE VIEW changenameAS SELECT customer#, lastname, firstnameFROM customersWITH CHECK OPTION;Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.The INSERT command can’t be used with the
This question is based on successful execution of the following command:CREATE VIEW changenameAS SELECT customer#, lastname, firstnameFROM customersWITH CHECK OPTION;Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.Which of the following DML operations could never
This question is based on successful execution of the following command:CREATE VIEW changenameAS SELECT customer#, lastname, firstnameFROM customersWITH CHECK OPTION;Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.Which of the following columns serves as the
This question is based on successful execution of the following command:CREATE VIEW changenameAS SELECT customer#, lastname, firstnameFROM customersWITH CHECK OPTION;Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.Which of the following is a correct statement?a.
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;Assuming one of the orders
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;Which of the following is
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;If a record is deleted from
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;Which of the following
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;Which of the following is
The City Jail Technologies Department is constructing an application to allow users in the Crime Analysis Unit to query data more easily. This system requires creating a number of views, described in the following list. Provide the SQL statement to perform each task and test your views with a
The Marketing Department of JustLee Books is about to begin an aggressive marketing campaign to generate sales to repeat customers. The strategy is to look at existing customers’ previous purchases, and based on the categories from which these customers have made purchases, JustLee Books will
Delete the REORDERINFO view.
Issue a rollback command to undo any changes made with the preceding DML operations.
Delete the record in the REORDERINFO view containing your name. (If you weren’t able to perform #6 successfully, delete one of the contacts already listed in the table.) Was an error message displayed when performing this step? If so, what was the cause of the error message?
Select one of the books in the REORDERINFO view and try to change its ISBN. Was an error message displayed when performing this step? If so, what was the cause of the error message?
Try to change the name of a contact person in the REORDERINFO view to your name. Was an error message displayed when performing this step? If so, what was the cause of the error message?
Create a view that lists the ISBN and title for each book in inventory along with the name and phone number of the person to contact if the book needs to be reordered. Name the view REORDERINFO.
Attempt to view the structure of the HOMEWORK13 view.
Create a view called HOMEWORK13 that includes the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make sure the view is created even if the FIRSTATTEMPT table doesn’t exist.
Change the CONTACT view so that no users can accidentally perform DML operations on the view.
Create a view that lists the name and phone number of the contact person at each publisher. Don’t include the publisher’s ID in the view. Name the view CONTACT.
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;Assuming there’s only a
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM customers JOIN orders USING (customer#)WHERE shipdate IS NULLWITH CHECK OPTION;Which of the following
What happens to the data displayed by a view when the view is deleted?
What’s unique about materialized views compared with other views?
What command can be used to modify a view?
What’s the difference between a key-preserved and a non-key-preserved table?
How do you ensure that no user can change the data displayed by a view?
List the guidelines for DML operations on complex views.
What’s the purpose of the WITH CHECK OPTION constraint?
When should the FORCE keyword be used in the CREATE VIEW command?
Under what circumstances is a DML operation not allowed on a simple view?
How is a simple view different from a complex view?
The city’s Crime Analysis unit has submitted the following data requests. Provide the SQL statements using subqueries to satisfy the requests. Test the statements and show execution results.1. List the name of each officer who has reported more than the average number of crimes officers have
Currently, JustLee Books bills customers for orders by enclosing an invoice with each order when it’s shipped. A customer then has 10 days to send in the payment. Of course, this practice has resulted in the company having to list some debts as “uncollectible.” By contrast, most other online
Determine which books were published by the publisher of The Wok Way to Cook.
Determine the number of different customers who have placed an order for books written or cowritten by James Austin.
Determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by JustLee Books.
List the shipping city and state for the order that had the longest shipping delay.
List the title of all books in the same category as books previously purchased by customer 1007. Don’t include books this customer has already purchased.
Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books.
Determine which orders had a higher total amount due than order 1008.
Determine which orders were shipped to the same state as order 1014
Determine which books cost less than the average cost of other books in the same category.
List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books.
Which operator is used to process a correlated subquery?a. EXISTSb. INc. LINKd. MERGE
Given the following SQL statement, which statement is most accurate?SELECT customer# FROM customersJOIN orders USING (customer#)WHERE shipdate-orderdate IN(SELECT MAX (shipdate-orderdate) FROM ordersWHERE shipdate IS NULL);a. The SELECT statement fails and returns an Oracle error message.b. The
Given the following query, which statement is correct?SELECT order# FROM ordersWHERE order# IN (SELECT order# FROM orderitemsWHERE isbn = '9959789321');a. The statement doesn’t execute because the subquery and outer query don’t reference the same table.b. The outer query removes duplicates in
In a MERGE statement, an INSERT is placed in which conditional clause?a. USINGb. WHEN MATCHEDc. WHEN NOT MATCHEDd. INSERTS aren’t allowed in a MERGE statement.
Which of the following statements is correct?a. If the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value.b. A multiple-column subquery can be used only in the outer query’s FROM clause.c. A subquery can contain only one
Which of the following SQL statements lists all books written by the author of The Wok Way to Cook?a. SELECT title FROM booksWHERE isbn IN (SELECT isbn FROM bookauthorHAVING authorid IN 'THE WOK WAY TO COOK);b. SELECT isbn FROM bookauthorWHERE authorid IN (SELECT authorid FROM booksJOIN bookauthor
A subquery must be placed in the outer query’s HAVING clause if:a. The inner query needs to reference the value returned to the outer query.b. The value returned by the inner query is to be compared to grouped data in the outer query.c. The subquery returns more than one value to the outer
What is the purpose of the following query?SELECT isbn, title FROM booksWHERE (pubid, category) IN (SELECT pubid, categoryFROM books WHERE title LIKE '%ORACLE%');a. It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same
Which of the following statements is correct?a. The IN comparison operator can’t be usedwith a subquery that returns only one row of results.b. The equals (=) comparison operator can’t be used with a subquery that returns more than one row of results.c. In an uncorrelated subquery, statements
Which query determines the customers who have ordered themost books from JustLee Books?a. SELECT customer# FROM ordersJOIN orderitems USING (order#)HAVING SUM (quantity) = (SELECTMAX (SUM (quantity)) FROM ordersJOIN orderitems USING (order#)GROUP BY customer#) GROUP BY customer#;b. SELECT customer#
Which of the following operators is used to find all values greater than the highest value returned by a subquery?a. >ALLb. <ALLc. >ANYd. <ANYe. IN
Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005?a. SELECT customer# FROM ordersJOIN books USING (isbn)WHERE isbn = (SELECT isbn FROM orderitemsJOIN books USING (isbn)WHERE customer# = 1001 OR customer# = 1005));b. SELECT customer# FROM
In which of the following situations is using a subquery suitable?a. When you need to find all customers living in a particular region of the countryb. When you need to find all publishers who have toll-free telephone numbersc. When you need to find the titles of all books shipped on the same date
Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez?a. SELECT customer# FROM customersWHERE referred = (SELECT referred FROM customersWHERE firstname = 'JORGE'AND lastname = 'PEREZ');b. SELECT referred FROM
Which of the following statements is correct?a. If a subquery is used in the outer query’s FROM clause, the data in the temporary table can’t be referenced by clauses used in the outer query.b. The temporary table created by a subquery in the outer query’s FROM clause must be assigned a table
Which of the following statements is valid?a. SELECT title FROM booksWHERE retail < (SELECT cost FROM booksWHERE isbn = '9959789321');b. SELECT title FROM booksWHERE retail = (SELECT cost FROM booksWHERE isbn = '9959789321' ORDER BY cost);c. SELECT title FROM booksWHERE category IN (SELECT cost
Which of the following queries determines which customers have ordered the same books as customer 1017?a. SELECT order# FROM ordersWHERE customer# = 1017;b. SELECT customer# FROM ordersJOIN orderitems USING (order#)WHERE isbn = (SELECT isbn FROM orderitemsWHERE customer# = 1017);c. SELECT customer#
Which of the following operators is considered a single-row operator?a. INb. ALLc. <>d. <>ALL
Which of the following is a valid SELECT statement?a. SELECT order# FROM ordersWHERE shipdate = SELECT shipdate FROM ordersWHERE order# = 1010;b. SELECT order# FROM ordersWHERE shipdate = (SELECT shipdate FROM orders)AND order# = 1010;c. SELECT order# FROM ordersWHERE shipdate = (SELECT shipdate
Which query identifies customers living in the same state as the customer named Leila Smith?a. SELECT customer# FROM customersWHERE state = (SELECT state FROM customersWHERE lastname = 'SMITH');b. SELECT customer# FROM customersWHERE state = (SELECT state FROM customersWHERE lastname = 'SMITH'OR
What type of situation requires using a subquery?
What’s the difference between correlated and uncorrelated subqueries?
When should a subquery be nested in a HAVING clause?
How can Oracle 12c determine whether clauses of a SELECT statement belong to an outer query or a subquery?

Showing 1 - 100 of 566

Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Step by Step Answers