Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

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. SELECT books.title, orderitems.quantityFROM books JOIN orderitemsON books.isbn = orderitems.isbn;d.
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. line 1b. line 3c. line 4d. line 5
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 operator.b. To include unmatched records in the results, the record is paired with a NULL record in
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. SELECT title, nameFROM books NATURAL JOIN publisherd. All the above SQL statements are valid.
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, publisherWHERE pubname = 1;c. SELECT *FROM books b, publisher pJOIN tables ON b.pubid = p.pubidWHERE name =
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, nameFROM books b JOIN publisher pON b.pubid = p.pubid;d. SELECT isbn, nameFROM books JOIN
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 books INTERSECTSELECT giftFROM promotion;c. SELECT titleFROM books UNION ALLSELECT giftFROM
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. line 4e. line 5
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 customersUNIONSELECT category FROM books;b. SELECT DISTINCT zip, categoryFROM customers c, orders o,
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 lastname, firstname, order#FROM customers c OUTER JOIN orders oON c.customer# = o.customer#ORDER BY
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. line 4e. line 5
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# = orders.customer#;b. SELECT zip, order#FROM customers, 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, o.quantityFROM books b, orders od, orderitems oWHERE orders.order# = orderitems.order#AND orderitems.isbn =
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 publisherUSING (pubid);c. SELECT title, giftFROM books, promotionWHERE retail >= minretailAND
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 publisher;c. SELECT title, giftFROM books, promotionWHERE retail >= minretailAND retail <=
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 JOIN promotion;d. All of the above
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 because case study assignments in previous chapters include table creation challenges. This script
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 with future requests. The following are two of the requests that were made:1. A manager at JustLee
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 logical operator, and c) A search pattern operation.
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 operator, and c) Another operator not used in a or b.
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 books WHERE pubdate NOT < '20-MAR-05';d. SELECT * FROM books WHERE pubdate NOT < 'MAR-20-05';e.
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 books WHERE title = '%AS_E%';d. SELECT * FROM books WHERE title = 'AS_E%';
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 title, retail-cost "Profit" FROM booksWHERE profit => 10.00ORDER BY "Profit" desc;c. SELECT title,
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 profit >= 12;d. SELECT * FROM books WHERE retail-cost => 12.00;e. None of the above
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 WHERE orderdate < '05-04-09' AND shipdate IS NULL;c. SELECT * FROM orders WHERE orderdate <
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 AND pubdate < '06-02-2004';b. SELECT * FROM books WHERE category = 'FITNESS' AND pubid = 4 OR
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. SELECT * FROM books WHERE pubdate > '20-MAR-05';d. SELECT * FROM books WHERE pubdate >
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 title LIKE 'H_N_C%';d. SELECT * FROM books WHERE title LIKE '_H%';
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 * FROM customers WHERE firstname = '%N%';d. SELECT * FROM customers WHERE firstname LIKE '_B%';
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. SELECT * FROM books WHERE pubid = 2 AND NOT retail < 35;c. SELECT * FROM books WHERE pubid IN (1, 2,
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 = 1 OR retail >= 25;c. SELECT * FROM books WHERE pubid = 1 AND WHERE retail > 25;d. SELECT *
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 lastname FROM customers WHERE lastname > 'R';d. SELECT * FROM customers WHERE lastname < 'V';
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 FROM customersSORT BY desc state, city;c. SELECT firstname, lastname FROM customersORDER BY state
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 = "NULL";d. None of the statements are valid.
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 address, city, state, zip FROM customers WHERE lastname= "SMITH";d. All the above statements are valid
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 department duties outlined in the table, develop a plan to assign privileges to employees in all four
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 managers, who are responsible for the company’s marketing activities (for example, promotions
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 SESSION privilege from a user account, the user can’t connect to the database.c. If a user is granted
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 INTOFROM elopez;d. DROP insertINTO booksFROM elopez;
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

Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Step by Step Answers