Oracle

Oracle Database to Track Students Grade

Build an Oracle database to track students grade in a class:
The database tracks
*Student Information
*Instructor Information
*Class information
*Grading Breakdown
*Students' Grades

Different queries will show
*List of students with semester grades
*List of students who received an A
*Average grades
*Above average grades

MIS 562 Week 7 Homework Roles and Privileges

Part 1
Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS

Question SQL statement or Answer
1. Determine what privileges your account has been granted through a role. (10 points)

2. Determine what system privileges your account has been granted. (10 points)

3.Execute the following statement then determine what table privileges your account has been granted. (15 points)
Grant select on student to public;

4. Determine what system privileges the DVONLINE role has. (10 points)

5. Analyze the following query and write a description of the output it produces. (15 points)
SELECT COUNT(DECODE(SIGN(total_capacity-20), -1, 1, 0, 1)) "<=20",
COUNT(DECODE(SIGN(total_capacity-21), 0, 1, -1, NULL,
DECODE(SIGN(total_capacity-30), -1, 1)))"21-30",
COUNT(DECODE(SIGN(total_capacity-30), 1, 1)) "31+"
FROM
(SELECT SUM(capacity) total_capacity, course_no
FROM section
GROUP BY course_no)

6. Determine the top three zip codes where most of the students live. Use an analytical function. The query will product 10 rows. (10 points)

Part 2
Analyze the file from Doc Share called utlpwdmg.sql and analyze the code in this file. Write a paragraph that describes what the function performs. What are the inputs parameters, the output parameter and what does the function do? (30 points)

CMIS 420 Homework 1 Online Vehicle Sales Database

You are part of a development team with Ace Software, Inc. who has recently been contracted to develop various database capabilities for Online Vehicle Sales, Inc. (OVS). OVS is a startup "dotcom" with about 10 dealership locations in Maryland, Virginia and Washington, D.C. They sell new and used cars (compacts, midsizes and full-sizes), sport utility vehicles (SUVs) and light trucks. Currently their business is just based on customers visiting one of the 10 dealership locations in person, but soon they plan to move the bulk of their business the Internet. Initially they have expressed a desire to have a custom OLTP database, and a custom DSS database, designed and built by your company. Each dealership has a staff of salespersons who assist customers in the purchase of different types of vehicles for which various financing plans are available. New and used vehicles are provided to each dealership based on sales and inventory needs.
An ERD for a 3NF normalized online transaction processing (OLTP) relational database for this application is provided.
Using an SQL script file create Oracle tables for the entities shown in the ERD. Use the plural form of the entity name for your table names.
You can create your database on Nova or any other Oracle system you wish.
Populate the VEHICLES and CUSTOMERS tables in your OLTP database with at least five rows each. Your DEALERSHIPS, SALESPERSONS and FINANCING_PLANS tables should have at least three rows each. Your SALES table should have at least 10 rows, using a variety of different customers, vehicles, salespersons, dealerships and financing plans. All other tables should have at least one row each. Run SELECT * statements on all your tables after they are populated to show all contents.
Submit the following in either an SQL*Plus SPOOL file or screen snapshots of the output if using iSQL*Plus:
1) The names of all your tables from the output of the SQL statement “SELECT table_name FROM user_tables”.
2) A DESC (i.e. DESCRIBE) of all tables to show the column names.
3) The contents of all tables from SELECT * statements
4) Do NOT submit your SQL script files. Only submit the output specified in Steps #1 though #3 above.

CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

Homework Submission Requirements:
Homework should be submitted as an attached file in your assignment folder. Text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
You must name your file to include your first initial of your first name and last name.
Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.

Part I Data Modeling
You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.
The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privileges or golf member which includes all of the social member privileges plus access to the golf course.
Develop, document and design an EER for this situation.
Submit your word document no later than the due date.

Part II Lab 3
In lab 2, you will learn how to edit and run a SQL script in your schema in NOVA Oracle database. You also continue to learn simple SQL commands, such as select and insert ...

Build and execute SQL script
In this lab, we learn how to write a SQL script that can be run on NOVA Oracle database to execute the SQL commands included in the file. You are provided with SQL Statements and sqlplus commands to add into the script. What you are requested to do is to reorganize the commands in proper order to avoid runtime errors.
How to write a script: The script file is a text file which contains executable commands. For SQL script, it contains SQL statements which can be executed against a proper database. For our SQL script that runs against Oracle database. Therefore, we include some of Oracle specific SQLPlus commands that help us make output clear and understandable and allow us to save the output file.
Get the txt file below that contains SQL and SQLPlus commands. Spool commands are for getting the execution results. Set echo on or off are for getting executed commands printed on the screen before the execution result. Others are all SQLcommands. These commands should work as they are without modification.
Download lab3.txt
There are a few simple suggested rules that can make your script robust.
Use remark, rem, to add comment or reminder in the script anything followed rem is not executed.
Spool on command should be the first command and followed
Set echo on should be before any execution commands begin.
Set echo off and spool off at the end of the file.
For SQL DDL commands. Drop the table before create it. this gives you clean database to begin with. The table created last should be dropped first.
For SQL DML, delete the records first before insert them into the table. This allow you begin with empty table. delete the records from child table first if primary key and foreign key constraints exist between the two tables. However, insert into parent table first then insert into child table. Don't forget the child record needs a refernce key value from the parent table. Always end with commit command to save the data.
you can use vi text editor to edit the script on NOVA or use a text editor at local to create and edit the script and upload to Nova using sFTP. The script MUST be saved with .sql extension in order for it to be run in SQLPLus
After you successfully create and executed your script. Transfer your output file back and Submit it through assignment for grading.

CMIS 320 Lab 2 Homework 2 Data modeling and Sample database

Homework Submission Requirements:
Homework should be submitted as an attached file in your assignment folder. text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
You must name your file to include your first initial of your first name and last name.
Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.

Part I Data Modeling
For each of the following descriptions:
a. A piano manufacturer wants to track all pianos it makes. Each piano has a unique serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and model. The company produces thousands of pianos of a certain model, and the design is specified before any single piano exists.
b. A vendor builds multiple types of tablet computers. Each has a type identification number and a name. The key specifications for each type include amount of storage and display type. The company uses multiple processor types, exactly one of which is used for a specific tablet type. The same processor can be used in multiple types of tablets. Each processor has a manufacturer and a manufacturer's unique code that identifies it.
perform the following tasks:
1. Identify the degree and cardinalities of the relationship.
2. Express the relationships graphically with an E-R diagram.
Document your work into a single, well-organized, well-written word document and submit no later than due date.

Part II Lab 2
In lab 2, you will learn how to run a SQL script to create a sample database in your schema in NOVA Oracle database. You also start to learn simple SQL commands, such as select and insert

Click here to open Lab 2 instruction page

This part of lab is to learn to run a SQL script on NOVA Oracle database server, and spool screen output to a text file. These techniques will be used when you work on your project 1 and 2. Download script file. This is sql script file containing completed sql commands to create tables and constraints and populate data to each table. The database you create is called JustLee Book Store. The code should be bug free.
Download lab2.sql script
Start spool command and run the script:
Log on NOVA, make a dir named scripts using command mkdir scripts
Then cd to newly created script directory
Upload your script to scripts directory using FTP
(I downloaded a free FTP client CORE FTP LE) and use it to upload and download file)
Now you have a newly create dir and your script is uploaded. You are ready to run your script.
Using SQL*Plus, log on NOVA Oracle database with your account
At SQL prompt,Type the following command to turn on spool function: SQL>spool spool01.lst
spool01.lst is the spool file name
Then use this command to run the script: SQL>@lab2.sql
The command will execute all the SQL commands inside the script and output will be spooled to spool01.lst
In the script, there are drop table statement as well. When first run, these commands will fail because the tables are not created yet.
You can run the script multiple times without any problems.
When you finish the execution, issue command SQL>spool off
This command will end spool function and save output to the file you defined
When you have done all of these. Use FTP get spool01.lst to your local computer and submit it as part of homework 2
You should know how to perform the following tasks using sqlplus on NOVA Oracle database after you complete this lab:
Using FTP to upload or download a file (scripts...)
Using sqlplus to log on to NOVA Oracle database
Using sqlplus command, spool to output screen output to a text file and transfer the file back to your local PC.
Understand the steps taken place when you perform FTP file transfer and Spooling your output.
Test the database you create and learn SQL queries.
You need to spool the execution results of the following commands.
Do a database query: select table_name from user_tables; USER_TABLES is a database system view that allow users to see the tables they created in their own schema.
You should be able to list the tables you just created
use this query: select * from "table_name from the above query"; you can retrieve the data in these tables one a time
use this query: select count(*) from "table_name from the above query"; you can retrieve the record counts in these tables one a time
Use SQL*Plus command "desc books" to check columns and their datatypes. And insert one record into the table. Use SQL command "commit" to save the record into your database.
When you have done all of these. Use FTP get spooled file to your local computer and submit it as part of homework 2

Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution

To answer the following questions, refer to the tables in the JustLee Books database.
Questions 1–7 are based on successful execution of the following statement:
CREATE VIEW changeaddress
AS SELECT customer#, lastname, firstname, order#,
shipstreet, shipcity, shipstate, shipzip
FROM customers JOIN orders USING (customer#)
WHERE shipdate IS NULL
WITH CHECK OPTION;

1. Which of the following statements is correct?
a. No DML operations can be performed on the CHANGEADDRESS view.
b. The CHANGEADDRESS view is a simple view.
c. The CHANGEADDRESS view is a complex view.
d. The CHANGEADDRESS view is an inline view.

2. Assuming there’s only a primary key, and FOREIGN KEY constraints exist on the underlying tables, which of the following commands returns an error message?
a. UPDATE changeaddress SET shipstreet = '958 ELM ROAD' WHERE customer# = 1020;
b. INSERT INTO changeaddress VALUES (9999, 'LAST', 'FIRST', 9999, '123 HERE AVE', 'MYTOWN', 'AA', 99999);
c. DELETE FROM changeaddress WHERE customer# = 1020;
d. all of the above
e. only a and b
f. only a and c
g. none of the above

3. Which of the following is the key-preserved table for the CHANGEADDRESS view?
a. CUSTOMERS table
b. ORDERS table
c. Both tables together serve as a composite key-preserved table.
d. none of the above

4. Which of the following columns serves as the primary key for the CHANGEADDRESS view?
a. Customer#
b. Lastname
c. Firstname
d. Order#
e. Shipstreet

5. If a record is deleted from the CHANGEADDRESS view based on the Customer# column, the customer information is then deleted from which underlying table?
a. CUSTOMERS
b. ORDERS
c. CUSTOMERS and ORDERS
d. Neither—the DELETE command can’t be used on the CHANGEADDRESS view.

6. Which of the following is correct?
a. ROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.
b. The view is a simple view because it doesn’t include a group function or a GROUP BY clause.
c. The data in the view can’t be displayed in descending order by customer number because an ORDER BY clause isn’t allowed when working with views.
d. all of the above
e. none of the above

7. Assuming one of the orders has shipped, which of the following is true?
a. The CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.
b. The CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.
c. The CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.
d. The CHANGEADDRESS view can’t be used to update an order’s ship date because the UPDATE command can’t be used on data in the view.

Questions 8–12 are based on successful execution of the following command:
CREATE VIEW changename
AS SELECT customer#, lastname, firstname
FROM customers
WITH CHECK OPTION;
Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.

8. Which of the following is a correct statement?
a. No DML operations can be performed on the CHANGENAME view.
b. The CHANGENAME view is a simple view.
c. The CHANGENAME view is a complex view.
d. The CHANGENAME view is an inline view.

9. Which of the following columns serves as the primary key for the CHANGENAME view?
a. Customer#
b. Lastname
c. Firstname
d. The view doesn’t have or need a primary key.

10. Which of the following DML operations could never be used on the CHANGENAME view?
a. INSERT
b. UPDATE
c. DELETE
d. All of the above are valid DML operations for the CHANGENAME view.

11. The INSERT command can’t be used with the CHANGENAME view because:
a. A key-preserved table isn’t included in the view.
b. The view was created with the WITH CHECK OPTION constraint.
c. The inserted record couldn’t be accessed by the view.
d. None of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.

12. If the CHANGENAME view needs to include the customer’s zip code as a means of verifying the change (that is, to authenticate the user), which of the following is true?
a. The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.
b. The ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.
c. The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.
d. All of the above can be performed to include the customer’s zip code in the view.
e. Only a and b include the customer’s zip code in the view.
f. Only a and c include the customer’s zip code in the view.
g. None of the above includes the customer’s zip code in the view.

13. Which of the following DML operations can’t be performed on a view containing a group function?
a. INSERT
b. UPDATE
c. DELETE
d. 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.

14. You can’t perform any DML operations on which of the following?
a. views created with the WITH READ ONLY option
b. views that include the DISTINCT keyword
c. views that include a GROUP BY clause
d. All of the above allow DML operations.
e. None of the above allow DML operations.

15. A TOP-N analysis is performed by determining the rows with:
a. the highest ROWNUM values
b. a ROWNUM value greater than or equal to N
c. the lowest ROWNUM values
d. a ROWNUM value less than or equal to N

16. 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 listed in the AS clause.
d. None of the above—columns can’t be assigned names for a view; they must keep their original names.

17. 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.

18. 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 here
b. data manipulation operation not legal on this view
c. cannot map to a column in a non-key-preserved table
d. None of the above—no error message is returned.

19. A simple view can contain which of the following?
a. data from one or more tables
b. an expression
c. a GROUP BY clause for data retrieved from one table
d. five columns from one table
e. all of the above
f. none of the above

20. A complex view can contain which of the following?
a. data from one or more tables
b. an expression
c. a GROUP BY clause for data retrieved from one table
d. five columns from one table
e. all of the above
f. none of the above

Joan Casteel Oracle 11g SQL Chapters 12 Multiple Choice Questions Solution

To answer these questions, refer to the tables in the JustLee Books database.
1. Which query identifies customers living in the same state as the customer named Leila Smith?
a. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH');
b. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' OR firstname = 'LEILA');
c. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA' ORDER BY customer);
d. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');

2. Which of the following is a valid SELECT statement?
a. SELECT order# FROM orders WHERE shipdate = SELECT shipdate FROM orders WHERE order# = 1010;
b. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders) AND order# = 1010;
c. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
d. SELECT order# FROM orders HAVING shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);

3. Which of the following operators is considered a single-row operator?
a. IN
b. ALL
c. <>
d. <>ALL

4. Which of the following queries determines which customers have ordered the same books
as customer 1017?
a. SELECT order# FROM orders WHERE customer# = 1017;
b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn = (SELECT isbn FROM orderitems WHERE customer# = 1017);
c. SELECT customer# FROM orders WHERE order# = (SELECT order# FROM orderitems WHERE customer# = 1017);
d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);

5. Which of the following statements is valid?
a. SELECT title FROM books WHERE retail <(SELECT cost FROM books WHERE isbn = '9959789321');
b. SELECT title FROM books WHERE retail = (SELECT cost FROM books WHERE isbn = '9959789321' ORDER BY cost);
c. SELECT title FROM books WHERE category IN (SELECT cost FROM orderitems WHERE isbn = '9959789321');
d. none of the above statements

6. 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 alias, or it can't be joined with another table by using the JOIN keyword.
c. If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query.
d. none of the above

7. 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 customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
b. SELECT referred FROM customers WHERE (customer#, referred) = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
c. SELECT referred FROM customers WHERE (customer#, referred) IN (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
d. SELECT customer# FROM customers WHERE customer# = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');

8. 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 country
b. when you need to find all publishers who have toll-free telephone numbers
c. when you need to find the titles of all books shipped on the same date as an order placed by a particular customer
d. when you need to find all books published by Publisher 4

9. Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005?
a. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));
b. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn <ANY (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));
c. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1001 OR 1005));
d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));

10. Which of the following operators is used to find all values greater than the highest value returned by a subquery?
a. >ALL
b. <ALL
c. >ANY
d. <ANY
e. IN

11. Which query determines the customers who have ordered the most books from JustLee Books?
a. SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#;
b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);
c. SELECT customer# FROM orders WHERE MAX(SUM(quantity)) = (SELECT MAX(SUM(quantity) FROM orderitems GROUP BY order#);
d. SELECT customer# FROM orders HAVING quantity = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);

12. Which of the following statements is correct?
a. The IN comparison operator can't be used with 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 in the outer query are executed first, and then statements in the subquery are executed.
d. A subquery can be nested only in the outer query's SELECT clause.

13. What is the purpose of the following query?
SELECT isbn, title FROM books
WHERE (pubid, category) IN (SELECT pubid, category
FROM 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 publisher.
b. It lists all publishers and categories containing the value ORACLE.
c. It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its title.
d. None of the above. The query contains a multiple-row operator, and because the inner query returns only one value, the SELECT statement will fail and return an error message.

14. 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 query.
d. None of the above. Subqueries can't be used in the outer query's HAVING clause.

15. Which of the following SQL statements lists all books written by the author of The Wok Way to Cook?
a. SELECT title FROM books WHERE isbn IN (SELECT isbn FROM bookauthor HAVING authorid IN 'THE WOK WAY TO COOK);
b. SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK');
c. SELECT title FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK);
d. SELECT isbn FROM bookauthor HAVING authorid = SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK';

16. 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 condition in its WHERE clause.
d. The order of columns listed in the SELECT clause of a multiple-column subquery must be in the same order as the corresponding columns listed in the outer query's WHERE clause.

17. In a MERGE statement, an INSERT is placed in which conditional clause?
a. USING
b. WHEN MATCHED
c. WHEN NOT MATCHED
d. INSERTs aren't allowed in a MERGE statement.

18. Given the following query, which statement is correct?
SELECT order# FROM orders
WHERE order# IN (SELECT order# FROM orderitems
WHERE 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 the subquery's Order# list.
c. The query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operator.
d. No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.

19. Given the following SQL statement, which statement is most accurate?
SELECT customer# FROM customers
JOIN orders USING(customer#)
WHERE shipdate-orderdate IN
(SELECT MAX(shipdate-orderdate) FROM orders
WHERE shipdate IS NULL);
a. The SELECT statement fails and returns an Oracle error message.
b. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query.
c. The customer number is displayed for customers whose orders haven't yet shipped.
d. The customer number of all customers who haven't placed an order are displayed.

20. Which operator is used to process a correlated subquery?
a. EXISTS
b. IN
c. LINK
d. MERGE

Joan Casteel Oracle 11g SQL Chapters 11 Multiple Choice Questions Solution

To answer these questions, refer to the tables in the JustLee Books database.
1. Which of the following statements is true?
a. The MIN function can be used only with numeric data.
b. The MAX function can be used only with date values.
c. The AVG function can be used only with numeric data.
d. The SUM function can’t be part of a nested function.

2. Which of the following is a valid SELECT statement?
a. SELECT AVG(retail-cost) FROM books GROUP BY category;
b. SELECT category, AVG(retail-cost) FROM books;
c. SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category;
d. SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;

3. Which of the following statements is correct?
a. The WHERE clause can contain a group function only if the function isn’t also listed in the SELECT clause.
b. Group functions can’t be used in the SELECT, FROM, or WHERE clauses.
c. The HAVING clause is always processed before the WHERE clause.
d. The GROUP BY clause is always processed before the HAVING clause.

4. Which of the following is not a valid SQL statement?
a. SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;
b. SELECT MIN(pubdate) FROM books WHERE category = 'COOKING';
c. SELECT COUNT(*) FROM orders WHERE customer# = 1005;
d. SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;

5. Which of the following statements is correct?
a. The COUNT function can be used to determine how many rows contain a NULL value.
b. Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.
c. The HAVING clause restricts which rows are processed.
d. The WHERE clause determines which groups are displayed in the query results.
e. none of the above

6. Which of the following is a valid SQL statement?
a. SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001;
b. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;
c. SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#);
d. SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;

7. Which of the following SELECT statements lists only the book with the largest profit?
a. SELECT title, MAX(retail-cost) FROM books GROUP BY title;
b. SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost);
c. SELECT title, MAX(retail-cost) FROM books;
d. none of the above

8. Which of the following is correct?
a. A group function can be nested inside a group function.
b. A group function can be nested inside a single-row function.
c. A single-row function can be nested inside a group function.
d. a and b
e. a, b, and c

9. Which of the following functions is used to calculate the total value stored in a specified column?
a. COUNT
b. MIN
c. TOTAL
d. SUM
e. ADD

10. Which of the following SELECT statements lists the highest retail price of all books in the Family category?
a. SELECT MAX(retail) FROM books WHERE category = 'FAMILY';
b. SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
c. SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);
d. none of the above

11. Which of the following functions can be used to include NULL values in calculations?
a. SUM
b. NVL
c. MAX
d. MIN

12. Which of the following is not a valid statement?
a. You must enter the ALL keyword in a group function to include all duplicate values.
b. The AVG function can be used to find the average calculated difference between two dates.
c. The MIN and MAX functions can be used on any type of data.
d. all of the above
e. none of the above

13. Which of the following SQL statements determines how many total customers were referred by other customers?
a. SELECT customer#, SUM(referred) FROM customers GROUP BY customer#;
b. SELECT COUNT(referred) FROM customers;
c. SELECT COUNT(*) FROM customers;
d. SELECT COUNT(*) FROM customers WHERE referred IS NULL;

Use the following SELECT statement to answer questions 14–18:
1 SELECT customer#, COUNT(*)
2 FROM customers JOIN orders USING (customer#)
3 WHERE orderdate > '02-APR-09'
4 GROUP BY customer#
5 HAVING COUNT(*) > 2;

14. Which line of the SELECT statement is used to restrict the number of records the query processes?
a. 1
b. 3
c. 4
d. 5

15. Which line of the SELECT statement is used to restrict groups displayed in the query results?
a. 1
b. 3
c. 4
d. 5

16. Which line of the SELECT statement is used to group data stored in the database?
a. 1
b. 3
c. 4
d. 5

17. Because the SELECT clause contains the Customer# column, which clause must be included for the query to execute successfully?
a. 1
b. 3
c. 4
d. 5

18. The COUNT(*) function in the SELECT clause is used to return:
a. the number of records in the specified tables
b. the number of orders placed by each customer
c. the number of NULL values in the specified tables
d. the number of customers who have placed an order

19. Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?
a. COUNT function
b. MAX function
c. MIN function
d. STDDEV function
e. VARIANCE function

20. Which of the following is not a valid SELECT statement?
a. SELECT STDDEV(retail) FROM books;
b. SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#;
c. SELECT order#, TO_CHAR(SUM(retail),'999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#;
d. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

Joan Casteel Oracle 11g SQL Chapters 10 Multiple Choice Questions Solution

To answer the following questions, refer to the tables in the JustLee Books database.
1. Which of the following is a valid SQL statement?
a. SELECT SYSDATE;
b. SELECT UPPER(Hello) FROM dual;
c. SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;
d. all of the above
e. none of the above

2. Which of the following functions can be used to extract a portion of a character string?
a. EXTRACT
b. TRUNC
c. SUBSTR
d. INITCAP

3. Which of the following determines how long ago orders that haven’t shipped were received?
a. SELECT order#, shipdate-orderdate delay FROM orders;
b. SELECT order#, SYSDATE – orderdate FROM orders WHERE shipdate IS NULL;
c. SELECT order#, NVL(shipdate, 0) FROM orders WHERE orderdate is NULL;
d. SELECT order#, NULL(shipdate) FROM orders;

4. Which of the following SQL statements produces “Hello World” as the output?
a. SELECT "Hello World" FROM dual;
b. SELECT INITCAP('HELLO WORLD') FROM dual;
c. SELECT LOWER('HELLO WORLD') FROM dual;
d. both a and b
e. none of the above

5. Which of the following functions can be used to substitute a value for a NULL value?
a. NVL
b. TRUNC
c. NVL2
d. SUBSTR
e. both a and d
f. both a and c

6. Which of the following is not a valid format argument for displaying the current time?
a. 'HH:MM:SS'
b. 'HH24:SS'
c. 'HH12:MI:SS'
d. All of the above are valid.

7. Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?
a. SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING';
b. SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name = 'AMERICAN PUBLISHING';
c. SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';
d. SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';

8. Which of the following functions can be used to determine how many months a book has been available?
a. MONTH
b. MON
c. MONTH_BETWEEN
d. none of the above

9. Which of the following displays the order date for order 1000 as 03/31?
a. SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# = 1000;
b. SELECT TO_CHAR(orderdate, 'Mth/DD') FROM orders WHERE order# = 1000;
c. SELECT TO_CHAR(orderdate, 'MONTH/YY') FROM orders WHERE order# = 1000;
d. both a and b
e. none of the above

10. Which of the following functions can produce different results, depending on the value of a specified column?
a. NVL
b. DECODE
c. UPPER
d. SUBSTR

11. Which of the following SQL statements is not valid?
a. SELECT TO_CHAR(orderdate, '99/9999') FROM orders;
b. SELECT INITCAP(firstname), UPPER(lastname) FROM customers;
c. SELECT cost, retail, TO_CHAR(retail-cost, '$999.99') profit FROM books;
d. all of the above

12. Which function can be used to add spaces to a column until it’s a specific width?
a. TRIML
b. PADL
c. LWIDTH
d. none of the above

13. Which of the following SELECT statements returns 30 as the result?
a. SELECT ROUND(24.37, 2) FROM dual;
b. SELECT TRUNC(29.99, 2) FROM dual;
c. SELECT ROUND(29.01, -1) FROM dual;
d. SELECT TRUNC(29.99, -1) FROM dual;

14. Which of the following is a valid SQL statement?
a. SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;
b. SELECT ROUND(TRUNC(125.38, 0) FROM dual;
c. SELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*") FROM dual;
d. SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM dual;

15. Which of the following functions can’t be used to convert the letter case of a character string?
a. UPPER
b. LOWER
c. INITIALCAP
d. All of the above can be used for case conversion.

16. Which of the following format elements causes months to be displayed as a three-letter abbreviation?
a. MMM
b. MONTH
c. MON
d. none of the above

17. Which of the following SQL statements displays a customer’s name in all uppercase
characters?
a. SELECT UPPER('firstname', 'lastname') FROM customers;
b. SELECT UPPER(firstname, lastname) FROM customers;
c. SELECT UPPER(lastname, ',' firstname) FROM customers;
d. none of the above

18. Which of the following functions can be used to display the character string FLORIDA in the query results whenever FL is entered in the State field?
a. SUBSTR
b. NVL2
c. REPLACE
d. TRUNC
e. none of the above

19. 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

20. If an integer is multiplied by a NULL value, the result is:
a. an integer
b. a whole number
c. a NULL value
d. None of the above—a syntax error message is returned.