Questions and Answers of Oracle 12c SQL

Determine which orders haven’t yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed.
Create a list that displays the title of each book and the name and phone number of the contact at the publisher’s office for reordering each book.
Which of the following SQL statements is valid?a. SELECT books.title, orderitems.quantityFROM books b, orderitems oWHERE b.isbn = o.ibsn;b. SELECT title, quantityFROM books b JOIN orderitems o;c.
What is the maximum number of characters allowed in a table alias?a. 10b. 30c. 255d. 256
Which line in the following SQL statement raises an error?1. SELECT name, title2. FROM books b, publisher p3. WHERE books.pubid = publisher.pubid4. AND5. (retail > 25 OR retail-cost > 18.95);a.
Which of the following statements about an outer join between two tables is true?a. If the relationship between the tables is established with a WHERE clause, both tables can include the outer join
Which of the following SQL statements is not valid?a. SELECT isbnFROM booksMINUSSELECT isbnFROM orderitems;b. SELECT isbn, nameFROM books, publisherWHERE books.pubid (+) = publisher.pubid(+);c.
Which of the following lists all books published by the publisher named Printing Is Us?a. SELECT titleFROM books NATURAL JOIN publisherWHERE name = 'PRINTING IS US';b. SELECT titleFROM books,
Which of the following SQL statements is not valid?a. SELECT b.isbn, p.nameFROM books b NATURAL JOIN publisher p;b. SELECT isbn, nameFROM books b, publisher pWHERE b.pubid = p.pubid;c. SELECT isbn,
If the CUSTOMERS table contains seven records and the ORDERS table has eight records, how many records does the following query produce?SELECT*FROM customers CROSS JOIN orders;a. 0b. 8c. 7d. 15e. 56
Given the following query:SELECT title, giftFROM books CROSS JOIN promotion;Which of the following queries is equivalent?a. SELECT title, giftFROM books NATURAL JOIN promotion;b. SELECT titleFROM
Which line in the following SQL statement raises an error?1. SELECT name, title2. FROM books JOIN publisher3. WHERE books.pubid = publisher.pubid4. AND5. cost < 45.95a. line 1b. line 2c. line 3d.
Given the following query:SELECT DISTINCT zip, categoryFROM customers NATURAL JOIN orders NATURAL JOIN orderitemsNATURAL JOIN books;Which of the following queries is equivalent?a. SELECT zip FROM
Given the following query:SELECT lastname, firstname, order#FROM customers LEFT OUTER JOIN ordersUSING (customer#)ORDER BY customer#;Which of the following queries returns the same results?a. SELECT
Which line in the following SQL statement raises an error?1. SELECT name, title2. FROM books NATURAL JOIN publisher3. WHERE category = 'FITNESS'4. OR5. books.pubid = 4;a. line 1b. line 2c. line 3d.
Given the following query:SELECT zip, order#FROM customers NATURAL JOIN orders;Which of the following queries is equivalent?a. SELECT zip, order#FROM customers JOIN ordersWHERE customers.customer# =
Which of the following queries is valid?a. SELECT b.title, b.retail, o.quantityFROM books b NATURAL JOIN orders odNATURAL JOIN orderitems oWHERE od.order# = 1005;b. SELECT b.title, b.retail,
The following SQL statement contains which type of join?SELECT title, order#, quantityFROM books FULL JOIN orderitemsON books.isbn = orderitems.isbn;a. Equalityb. Self-joinc. Non-equalityd. Outer join
Which of the following queries contains a non-equality join?a. SELECT title, authoridFROM books, bookauthorWHERE books.isbn = bookauthor.isbnAND retail > 20;b. SELECT title, nameFROM books JOIN
Which of the following queries contains an equality join?a. SELECT title, authoridFROM books, bookauthorWHERE books.isbn = bookauthor.isbnAND retail > 20;b. SELECT title, nameFROM books CROSS JOIN
Which of the following operators is not allowed in an outer join?a. ANDb. =c. ORd. >
Which of the following queries creates a Cartesian join?a. SELECT title, authoridFROM books, bookauthor;b. SELECT title, nameFROM books CROSS JOIN publisher;c. SELECT title, giftFROM books NATURAL
What are the differences between the JOIN…USING and JOIN…ON approaches for joining tables?
What’s the difference between an equality and a non-equality join?
How many join conditions are needed for a query that joins five tables?
What’s the difference between the UNION and UNION ALL set operators?
In an OUTER JOIN query, the outer join operator (+) is placed after which table?
What’s the purpose of a column qualifier? When are you required to use one?
Why are the NATURAL JOIN keywords not an option for producing a self-join?
Describe problems you might encounter when using the NATURAL JOIN keywords to perform join operations.
How many rows are returned in a Cartesian join between one table having 5 records and a second table having 10 records?
Explain the difference between an inner join and an outer join.
Run the CityJail_8.sql file provided by your instructor to ensure that all necessary tables and constraints are available for this case study. This script isn’t included in student data files
During an afternoon at work, you receive various requests for data stored in the database. As you fulfill each request, you decide to document the SQL statements you used to find the data to assist
List the title and publish date of any computer book published in 2005. Perform the task of searching for the publish date by using three different methods: a) A range operator, b) A
Use a search pattern to find any book title with “A” for the second letter and “N” for the fourth letter. List each book’s ISBN and title. Sort the list by title in descending order.
Display the book title and category for all books in the Children and Cooking categories. Create three different queries to accomplish this task: a) A search pattern operation, b) A logical
List all customers who were referred to the bookstore by another customer. List each customer’s last name and the number of the customer who made the referral.
List all authors whose last name contains the letter pattern “IN.” Put the results in order of last name, then first name. List each author’s last name and first name.
Which orders were placed on or before April 1, 2009? List each order number and order date. Write this query in two different ways.
Which customers live in Georgia or New Jersey? Put the results in ascending order by last name. List each customer’s customer number, last name, and state. Write this query in two different ways.
Which books aren’t in the Fitness category? List each book title and category.
Which orders shipped after April 1, 2009? List each order number and the date it shipped.
Which customers live in New Jersey? List each customer’s last name, first name, and state.
Which of the following returns all books published after March 20, 2005?a. SELECT * FROM books WHERE pubdate > 03-20-2005;b. SELECT * FROM books WHERE pubdate > '03-20-2005';c. SELECT * FROM
Which of the following includes the book HOW TO GET FASTER PIZZA in the query results?a. SELECT * FROM books WHERE title LIKE '%AS_E%';b. SELECT * FROM books WHERE title LIKE 'AS_E%';c. SELECT * FROM
Which of the following lists each book having a profit of at least $10.00 in descending order by profit?a. SELECT * FROM books WHERE profit => 10.00 ORDER BY "Profit" desc;b. SELECT
Which of the following lists books generating at least $12.00 in profit?a. SELECT * FROM books WHERE retail-cost >12;b. SELECT * FROM books WHERE retail-cost <=12;c. SELECT * FROM books WHERE
Which of the following symbols represents any number of characters in a pattern search?a. *b. ?c. %d. –
Which of the following finds all orders placed before April 5, 2009 that haven’t yet shipped?a. SELECT * FROM orders WHERE orderdate < '04-05-09' AND shipdate = NULL;b. SELECT * FROM orders
Which of the following lists all books published before June 2, 2004 and all books published by Publisher 4 or in the Fitness category?a. SELECT * FROM books WHERE category = 'FITNESS' OR pubid = 4
Which of the following SQL statements returns all books published after March 20, 2005?a. SELECT * FROM books WHERE pubdate > 03-20-2005;b. SELECT * FROM books WHERE pubdate > '03-20-2005';c.
Which of the following clauses is used to display query results in a sorted order?a. WHEREb. SELECTc. SORTd. ORDERe. None of the above
Which of the following returns the book HANDCRANKED COMPUTERS in the results?a. SELECT * FROM books WHERE title = 'H_N_%';b. SELECT * FROM books WHERE title LIKE "H_N_C%";c. SELECT * FROM books WHERE
Which of the following represents exactly one character in a pattern search?a. ESCAPEb. ?c. –d. %e. None of the above
Which of the following includes a customer with the first name BONITA in the results?a. SELECT * FROM customers WHERE firstname = 'B%';b. SELECT * FROM customers WHERE firstname LIKE '%N%';c. SELECT
Which of the following doesn’t include the display of books published by Publisher 2 and having a retail price of at least $35.00?a. SELECT * FROM books WHERE pubid = 2, retail >= $35.00;b.
What’s the default sort sequence for the ORDER BY clause?a. Ascendingb. Descendingc. The order in which records are stored in the tabled. There’s no default sort sequence.
Which of the following displays all books published by Publisher 1 with a retail price of at least $25.00?a. SELECT * FROM books WHERE pubid = 1 AND retail >= 25;b. SELECT * FROM books WHERE pubid
Which of the following doesn’t return a customer with the last name THOMPSON in the query results?a. SELECT lastname FROM customers WHERE lastname = "THOMPSON";b. SELECT * FROM customers;c. SELECT
Which of the following returns a list of all customers’ names sorted in descending order by city within state?a. SELECT name FROM customersORDER BY desc state, city;b. SELECT firstname, lastname
Which of the following SQL statements is valid?a. SELECT order# FROM orders WHERE shipdate = NULL;b. SELECT order# FROM orders WHERE shipdate = 'NULL';c. SELECT order# FROM orders WHERE shipdate =
Which clause is used to restrict rows or perform selection?a. SELECTb. FROMc. WHEREd. ORDER BY
Which of the following SQL statements isn’t valid?a. SELECT address || city || state || zip "Address" FROM customers WHERE lastname='SMITH';b. SELECT * FROM publisher ORDER BY contact;c. SELECT
Because % is a wildcard character, how can the LIKE operator search for a literal percent sign (%) in a character string?
When should an underscore symbol ( _ ) be used with the LIKE operator?
When should a percent sign (%) be used with the LIKE operator?
What’s the effect of using the NOT operator in a WHERE clause?
When should single quotation marks be used in a WHERE clause?
The IN comparison operator is similar to which logical operator?
Which operator should you use to find NULL values?
Which operator can you use to find any books with a retail price of at least $24.00?
Which clause of an SQL query displays the results in a specific sequence?
Which clause of an SQL query is used to restrict the number of rows returned?
The City Jail organization is preparing to deploy the new database to four departments. The departments and associated duties for the database are described in the following chart:Based on the
Use the JustLee Books database to perform the following activity:There are three major classifications for employees who don’t work for the Information Systems Department of JustLee Books: account
Delete the user account created in Assignment 1.
Delete the CUSTOMERREP role from the Oracle 12c database.
Remove the CUSTOMERREP role from the account created in Assignment 1.
Revoke the privilege to delete rows in the ORDERS and ORDERITEMS tables from the CUSTOMERREP role.
Log in to Oracle 12c with the new account created in Assignment 1. Determine the privileges currently available to the account.
Assign the account created in Assignment 1 the CUSTOMERREP role.
Using an account with the required privileges, create a role named CUSTOMERREP that allows inserting new rows in the ORDERS and ORDERITEMS tables and deleting rows from these tables.
Assign privileges to the new account that allow connecting to the database, creating new tables, and altering an existing table.
Attempt to log in to Oracle 12c with the newly created account.
Create a new user account. The account name should be a combination of your first initial and your last name.
Which of the following is an object privilege?a. DELETE ANYb. INSERT ANYc. UPDATE ANYd. REFERENCES
Which of the following can be used to change the role that’s currently enabled for a user?a. SET DEFAULT ROLEb. ALTER ROLEc. ALTER SESSIONd. SET ROLE
Which of the following displays a list of all system privileges available in Oracle 12c?a. SESSION_PRIVSb. SYS_PRIVILEGE_MAPc. V$SYSTEM_PRIVILEGESd. SYSTEM_PRIVILEGE_MAP
Which of the following commands can be used to eliminate the RECEPTIONIST role?a. DELETE ROLE receptionist;b. DROP receptionist;c. DROP ANY ROLE;d. None of the above
Which of the following is true?a. If the DBA changes the password for a user while the user is connected to the database, the connection terminates automatically.b. If the DBA revokes the CREATE
Which of the following identifies a collection of privileges?a. An object privilegeb. A system privilegec. DEFAULT privileged. A role
Which of the following is used to grant all object privileges for an object to a specified user?a. ALLb. PUBLICc. ANYd. OBJECT
Which of the following privileges can be granted only to a user, not to a role?a. SELECTb. CREATE ANYc. REFERENCESd. READe. WRITE
Which of the following is an object privilege?a. CREATE SESSIONb. DROP USERc. INSERT ANY TABLEd. UPDATE
Which of the following options allows a user to grant system privileges to other users?a. WITH ADMIN OPTIONb. WITH GRANT OPTIONc. DBAd. ASSIGN ROLESe. SET ROLE
Which of the following options requires a user to change his or her password at the next login?a. CREATE USERb. ALTER USERc. IDENTIFIED BYd. PASSWORD EXPIRE
Which of the following commands is used to assign a privilege to a role?a. CREATE ROLEb. CREATE PRIVILEGEc. GRANTd. ALTER PRIVILEGE
Which of the following commands eliminates only the user ELOPEZ’s ability to enter new books in the BOOKS table?a. REVOKE insertON booksFROM elopez;b. REVOKE insertFROM elopez;c. REVOKE INSERT
Which of the following tables or views displays the current enabled privileges for a user?a. SESSION_PRIVSb. SYSTEM_PRIVILEGE_MAPc. USER_ASSIGNED_PRIVSd. V$ENABLED_PRIVILEGES

Showing 200 - 300 of 566