Oracle

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.

MSCD610 Week 7 Course Project Details Hotel Database

The course project is to develop a Data Model and Database Design for a set of business case requirements. Students should informally review their ER model with the facilitator in week 4 or 5.

This project should follow a top-down database design process and produce the following outputs:
1. A set of business information requirements in the form of a business case abstract.

2. A complete ERD that models those requirements.
This model should include the following:
a. A definition of each entity
b. The unique identifier for each entity
c. The attributes associated with each entity
d. The relationships between the entities including their cardinality, optionally, and names

3. A physical implementation of a database from the logical design (ERD).
The tables should be normalized to 3NF. For this design include:
a. The name for each table
b. The primary key for the table, and any secondary keys
c. Any foreign keys
d. A data dictionary
e. Any sample data available

4. The DDL scripts for implementing the physical database design for this database. Include the referential integrity constraints for these tables. Also include the DDL for any needed indexes and/or views. If necessary, indicate any database tuning which is anticipated on this database.

5. Create the designed Oracle database, and load it with sample data. Then print the definition of the tables, indexes, and any views. Print sample report(s) showing the query definitions in the business requirements have been met.

CMIS 420 Homework 2 Online Vehicle Sales Database

1) Complete the creation and population of your Homework #1 tables and include all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. All tables must have a primary key and you must have a foreign key on the many side of each one-to-many relationship in the instructor-supplied ERD. Note that you must use Oracle for your database and and you must use actual SQL statements vs. a graphical approach. You may use Nova or any other Oracle instance you wish. You may use SQL*Plus, iSQL*Plus, or other command line tool (e.g. SQL Developer).
2) Add 20 more rows to both your VEHICLES and CUSTOMERS tables to have 25 rows total in each table and display the row counts of these tables.
3) Add 40 more rows to your SALES table to have 50 rows total and display the row count.
4) Create the SALE_FINANCINGS table if necessary, populate it as necessary, and show the row count.
5) Execute a multi-table join SELECT statement to display the number of sales by make of car, sorted by make.
6) Execute a multi-table join SELECT statement to display the number of sales by customer zip code, sorted by zip code.
7) Execute a multi-table join SELECT statement to display the number of sales by make of car and customer zip code, sorted by make and then by zip code.
8) Using a set operator execute a single SELECT statement to display together all the customer last names and all the salesperson last names, with no duplicates.
9) Using a set operator, subquery, and/or join query execute a SELECT statement to display the customers by customer ID, last name, and zip code who have not been involved in a sale, sorted by customer ID.
10) Using a correlated subquery execute a SELECT statement to display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID.

The SQL for Steps #5 through #10 above must all be a single SELECT statement.
You must show ALL your SQL statements and ALL your results, together, for ALL SQL statements in Steps #1 through #10 above.
If using SQL*Plus then submit a single SPOOL file with everything. If using iSQL*Plus or another interface tool then include a single Word or PDF file of screen snapshots for everything.
In your single file label your SQL statements (#1 through #10) and results and put them in order.
You must use an SQL script file vs. typing ad hoc SQL, but do NOT submit your SQL script file.

CMIS 420 Homework 3 Online Vehicle Sales OVS Database

1) Complete your OVS database through the Homework #2 requirements.
2) Add 25 customers so that you now have 50 total.
3) Add 25 vehicles so that you now have 50 total.
4) Add 200 sales so that you now have 250 total.
5) Via a single SELECT query display the total count of sales, and the lowest, average, and highest sale price.
6) Via a single SELECT query display all customer IDs, names, and zip codes of customers having purchased 5 or more vehicles.
7) Via a single SELECT query display the institution and loan type of the financing plan used for the most sales.
8) Via a single SELECT query display the vehicle make involved in the most sales and all customers having purchased a vehicle of that make.
9) Produce a detailed and formatted report showing the customer IDs, names, and zipcodes along with makes and models sold with summarized customer counts and make and model counts.