Questions and Answers of Oracle 12c SQL

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM
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 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
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
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
This question is based on successful execution of the following statement:CREATE VIEW changeaddressAS SELECT customer#, lastname, firstname, order#,shipstreet, shipcity, shipstate, shipzipFROM
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
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
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
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
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
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
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
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
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
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))
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
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
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
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.
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
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
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
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.
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