Search results for '''

ITS 320 Java Program 2 Custom BankAccount Class with a Modified Constructor

1) Enter the code for the two classes "BankAccount.java" and "Program2.java" shown below. The Program2 class is a driver for the BankAccount class. Note that this version of the BankAccount class accepts a monthly interest rate in decimal format which must be calculated by the user.
/**
* BankAccount class
* This class simulates a bank account.
*
* (Taken from "Starting Out with Java - Early Objects
* (Third Edition) by Tony Gaddis, 2008 by Pearson Educ.)
*
*/
public class BankAccount
{
private double balance; // Account balance
private double interestRate; // Interest rate
private double interest; // Interest earned

/**
* The constructor initializes the balance
* and interestRate fields with the values
* passed to startBalance and intRate. The
* interest field is assigned to 0.0.
*/
public BankAccount(double startBalance,
double intRate)
{
balance = startBalance;
interestRate = intRate;
interest = 0.0;
}

/**
* The deposit method adds the parameter
* amount to the balance field.
*/
public void deposit(double amount)
{
balance += amount;
}

/**
* The withdraw method subtracts the
* parameter amount from the balance
* field.
*/
public void withdraw(double amount)
{
balance -= amount;
}

/**
* The addInterest method adds the interest
* for the month to the balance field.
*/
public void addInterest()
{
interest = balance * interestRate;
balance += interest;
}

/**
* The getBalance method returns the
* value in the balance field.
*/
public double getBalance()
{
return balance;
}

/**
* The getInterest method returns the
* value in the interest field.
*/
public double getInterest()
{
return interest;
}
}

/**
*
* Colorado State University – ITS-320 – Basic Programming
*
* This program demonstrates the BankAccount class.
*
* (Taken from "Starting Out with Java - Early Objects
* (Third Edition) by Tony Gaddis, 2008 by Pearson Educ.)
*
* Programmed by: Reggie Haseltine, instructor
*
* Date: June 19, 2010
*
*/
import java.util.Scanner; // Needed for the Scanner class
import java.text.DecimalFormat; // Needed for 2 decimal place amounts
public class Program2 {
public static void main(String[] args)
{
BankAccount account; // To reference a BankAccount object
double balance, // The account's starting balance
interestRate, // The annual interest rate
pay, // The user's pay
cashNeeded; // The amount of cash to withdraw

// Create a Scanner object for keyboard input.
Scanner keyboard = new Scanner(System.in);

// Create an object for dollars and cents
DecimalFormat formatter = new DecimalFormat ("#0.00");

// Get the starting balance.
System.out.print("What is your account's " + "starting balance? ");
balance = keyboard.nextDouble();

// Get the monthly interest rate.
System.out.print("What is your monthly interest rate? ");
interestRate = keyboard.nextDouble();

// Create a BankAccount object.
account = new BankAccount(balance, interestRate);

// Get the amount of pay for the month.
System.out.print("How much were you paid this month? ");
pay = keyboard.nextDouble();

// Deposit the user's pay into the account.
System.out.println("We will deposit your pay " + "into your account.");
account.deposit(pay);
System.out.println("Your current balance is $" + formatter.format( account.getBalance() ));

// Withdraw some cash from the account.
System.out.print("How much would you like " + "to withdraw? ");
cashNeeded = keyboard.nextDouble();
account.withdraw(cashNeeded);

// Add the monthly interest to the account.
account.addInterest();

// Display the interest earned and the balance.
System.out.println("This month you have earned $" + formatter.format( account.getInterest() ) + " in interest.");
System.out.println("Now your balance is $" + formatter.format( account.getBalance() ) );
}
}

Compile the two test files (BankAccount.java first and then Program2.java second). Execute Program2 with the following inputs:
starting balance - $500 (don't enter the dollar sign)
monthly interest rate - 0.00125 (this is a 1.5% annual rate)
monthly pay - $1000 (don't enter the dollar sign)
withdrawal amount - $900 (don't enter the dollar sign)
Verify that you earn $0.75 in interest and have an ending balance at the end of the month of $600.75.

Then modify the BankAccount class's constructor method to create a BankAccount object which stores a monthly interest when the user inputs an annual interest rate of the format "nnn.nn" (i.e. 1.5). Note that the BankAccount constructor stored a monthly interest rate for the BankAccount object's instance field originally, but the user had to convert the annual rate to a monthly rate (i.e. 1.5 to 0.00125). Then modify the Program2 driver class to prompt the user for an annual interest rate. Recompile both classes and execute the modified Program2 driver class again, this time with following inputs:
starting balance - $500 (don't enter the dollar sign)
annual interest rate - 1.5
monthly pay - $1000 (don't enter the dollar sign)
withdrawal amount - $900 (don't enter the dollar sign)
Verify that you still earn $0.75 in interest and still have an ending balance at the end of the month of $600.75 as you did with the original code.

Submit only the modified source code files, final user inputs, and final output. Do not submit the original source code, inputs, and output.

Be sure that you include the course, the program number, your name, and the date in your program header. Also include this information at the top of your Microsoft Word file. Include additional comments as necessary and maintain consistent indentation for good programming style as shown and discussed in our text.

2) You may use the Windows Command Prompt command line interface or any Java IDE you choose to compile and execute your program.

3) You are to submit the following deliverables to the Dropbox:
a) A single Microsoft Word file containing a screen snapshot of your Java source code for both Program2.java and BankAccount.java (just the beginnings of the source code is OK) as it appears in your IDE (e.g. jGRASP, Net Beans, JDeveloper, etc.) or editor (e.g. a DOS "more" of the .java file's first screen).
b) A listing of your entire modified version of the Java source code for Program2.java and BankAccount.java in the same Microsoft Word file as item a), and following item a).
You can simply copy and paste the text from your IDE into Word. Be sure to maintain proper code alignment by using Courier font for this item. Do not submit the original source code files! c) A screen snapshot showing all of your program’s inputs and output in the same Microsoft Word file, and following item b) above.

4) Your instructor may compile and run your program to verify that it compiles and executes properly.

5) You will be evaluated on (in order of importance):
a) Inclusion of all deliverables in Step #3 in a single Word file.
b) Correct execution of your program. This includes getting the correct results with the modified class files!
c) Adequate commenting of your code.
d) Good programming style (as shown and discussed in the textbook's examples).
e) Neatness in packaging and labeling of your deliverables.

6) You must put all your screen snapshots and source code to a single Microsoft Word file.

MIS562 Week 4 Homework Oracle Queries Database

Part 1
Using the tables created in week 2:
Question ( 4 pts per question)
1. Show a list of all employee names and their department names and the employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join.
2. Select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join.

Using the student schema:
Question ( 3 pts per question)
3. Write a query that that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax.
4. Write a query that that performs an inner join of the grade, student, and grade_type tables using the Oracle inner join convention.
5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a set operator.
6. Write a SQL statement using a set operator to show which students enrolled in a section that are not enrolled in any classes. Exclude students with student id less than 300.

Part 2
Question ( 5 pts per question)
1. Write and execute two INSERT statements to insert rows into the ZIPCODE table for the following two cities of your choice. After your INSERT statements are successful, make the changes permanent.
2. Create a sequence called STUDENT_ID_NEW that begins with 900 and increments by 1. If the sequence already exists, drop the sequence and recreate it.
3. Make yourself a student by writing and executing an INSERT statement to insert a row into the STUDENT table with data about you. Use one of the zip codes you inserted in Exercise 1. Only insert values into the columns STUDENT_ID using the sequence you created in step 2, FIRST_NAME, LAST_NAME, ZIP, REGISTRATION_DATE (use a date that is five days after today), CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE. Issue a COMMIT command afterwards.
4. Write an UPDATE statement to update the data about you in the STUDENT table. Update the columns SALUTATION, STREET_ADDRESS, PHONE, and EMPLOYER. Be sure to also update the MODIFIED_DATE column and make the changes permanent
5. Delete the row in the STUDENT table and the two rows in the ZIPCODE table you created. Be sure to issue a COMMIT command afterwards
6. Create a table called TEMP_STUDENT with the following columns and constraints: a column STUDID for student ID that is NOT NULL and is the primary key, a column FIRST_NAME for student first name; a column LAST_NAME for student last name, a column ZIP that is a foreign key to the ZIP column in the ZIPCODE table, a column REGISTRATION_DATE that is NOT NULL and has a CHECK constraint to restrict the registration date to dates after January 1st, 2000.
7. Write an INSERT statement violating at least two of the constraints for the TEMP_STUDENT table you just created.
8. Write another INSERT statement that succeeds when executed, and commit your work.
9. Alter the TEMP_STUDENT table to add two more columns called EMPLOYER and EMPLOYER_ZIP. The EMPLOYER_ZIP column should have a foreign key constraint referencing the ZIP column of the ZIPCODE table. Update the EMPLOYER column.
10. Alter the table once again to make the EMPLOYER column NOT NULL.
11. Write a statement that drops the EMPLOYER_ZIP column.
12. Drop the TEMP_STUDENT table once you're done with the exercise.
13. Create a non unique index called crse_modified_by_i on the MODIFIED_BY column of the course table
14. Change the registration date of Paula Valentine to today’s date. Create a view called CURRENT_REGS reflecting all students that registered today. (You will need to execute this query in question 6 on the same day you change the date)
15. Create a view called roster reflecting all students taught by the instructor Marilyn Frantzen. Query the view.
16. Given the MY_EMPLOYEE view, what information is the user allowed to retrieve? Who can update the SALARY column through the view? Hint: The USER function returns the name of the currently logged in user.
CREATE OR REPLACE VIEW my_employee AS
SELECT employee_id, employee_name, salary, manager
FROM employee
WHERE manager = USER
WITH CHECK OPTION CONSTRAINT my_employee_ck_manager

ITS407 Module 8 Project Ace Software MySQL Database For Mom and Pop Johnson video store

You are a database consultant with Ace Software, Inc., and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.

You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent, therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.

The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors and at different wholesale prices.

Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop, and the information from these catalogs must be included in the database.

Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the Academy Awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.

There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and Academy Awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.

1) Identify and describe the entities and their attributes.
2) Develop relationship sentence pairs.
3) Draw an ERD with Visio.
4) Develop metadata from the ERD and document in an Excel spreadsheet.
5) Using your selected RDBMS (SQL Server, Oracle, or MySQL), develop and execute an SQL script file of DDL SQL to create the database tables in the metadata document.
6) Using your selected RDBMS, develop and execute an SQL script file of DML SQL INSERT statements to populate the tables using SQL INSERT statements for at least 5 rows of data per table.
7) Using your selected RDBMS develop and execute an SQL script file to:
 a) Show the contents of all tables
 b) Retrieve all of the customers' names, account numbers, and addresses (street and zip code only), sorted by account number
 c) Retrieve all of the DVDs rented in the last 30 days and sort in chronological rental date order
 d) Update a customer name to change their maiden names to married names. You can choose which row to update. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.
 e) Delete a specific customer from the database. You can choose which row to delete. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.

The metadata should be submitted in an Excel spreadsheet. All other outputs for the database design, SQL code, and SQL results should be submitted in a single Word file in order, by step, and clearly labeled.

VB Study Guide Questions

1. Write a statement that would fill a DataTable object named datCoins if an instance of the OleDbDataAdapter is named odaCollection.

2. Write a Try-Catch structure to test a list box, lstTours has something selected and the selected item index can be converted into an Integer variable intTourNumber. If exception is detected, a message box saying "Select a Tour" should be displayed.

3. Write a data validation to check that the variable intAge entered from an input box (and already converted to an integer) is between 0 and 120 inclusive.

4. Write a statement that displays an input box, which should have a title of "Highland Heights Pay Roll" and a message of "Enter the hourly rate of pay" along with the default buttons of "OK" and "Cancel."

5. Write a Public Function called "CylinderVolume" to convert the radius and height input to cylinder volume (height * π * radius ^ 2) and returns the result (use System.Math.PI for π).

6. List and describe types of input that can be validated.

7. Describe the different types of sequential files. Specifically, describe the characteristics of freeform files, delimited files, and fixed-field files; and what methods can be used to read those files.

8. Describe the steps performed by ADO.NET to retrieve data from a database and optionally make changes to that data.

9. Discuss the purpose of having user-defined (custom) classes.

10. What is a constructor? How are an object's instance variables initialized if a class has only a constructor which does not accept any arguments?

Multiple Choice
Identify the choice that best completes the statement or answers the question.

1. Which of the following sections are part of a UML class diagram?
a. The class name
c. The operations (methods)
b. The attributes (data)
d. All of the above

2. When does the code in the Catch block of a structured exception handler execute?
a. At run-time, when the main form loads.
b. This code typically generates an exception so it can execute at any time while the program runs.
c. The code always executes whether an exception occurs or not.
d. This code executes when an exception occurs.

3. In organizing namespaces, classes, and methods hierarchically, what character separates a namespace and class name?
a. .
c. _
b. &
d. ^

5. The ____ of a variable specifies where a variable can be referenced within a program.
a. range
c. scope
b. lifetime
d. scale


6. When an argument is passed ____, the Sub procedure code can change the value in the temporary variable and use it in any manner required, but the original value is not changed.
a. byPal
b. byRef
c. byVal
d. bySub

7. In a database table, one ____ represents one record.
a. row
b. field
c. key
d. Column

8. Which of the following statements is correct of applications with multiple modules?
a. The Form window displays all of an application's forms in a columnar list.
b. In an application with multiple forms, all forms are stored in the same physical file.
c. An application can contain multiple forms but it can only contain a single Module block.
d. An application can contain multiple forms, multiple Class blocks, and multiple Module blocks.

9. Which of the following statements is true of UML class diagrams?
a. Like flowcharts, they model the activities performed by the class.
b. They describe each of the members (properties and methods) of the class.
c. They describe the relationships between the methods of a class.
d. They describe the purpose of each property and method in the class.

10. Which of the following statements correctly calls the constructor for the class named Demo. Assume that the constructor accepts one argument having a data type of Double.
a. Dim objDemoInstnace = New Demo(123.45)
b. Dim objDemoInstance As Demo(123.45)
c. Dim objDemoInstance As New Demo(123.45)
d. Dim objDemoInstance As Demo = Create Demo(123.45)

MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

True/False (2 points each)
Indicate whether the sentence or statement is true or false.
1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
2. A table name can consist of numbers, letters, and blank spaces.
3. A constraint can only be created as part of the CREATE TABLE command.
4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
6. By default, the lowest value that can be generated by a sequence is 0.
7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
10. By default, the column headings displayed in a report are in upper-case characters.

Multiple Choice (3 points each)
Identify the letter of the choice that best completes the statement or answers the question.
11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
a. one-to-many c. one-to-all
b. many-to-many d. one-to-one

Contents of the BOOKS table
12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
a. SELECT title, cost+.20 "New Retail Price" FROM books;
b. SELECT title, cost*.20 "New Retail Price" FROM books;
c. SELECT title, cost*1.20 "New Retail Price" FROM books;
d. none of the above

Structure of the CUSTOMERS table
13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
a. ALTER TABLE customers
MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
b. ALTER TABLE customers
MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
c. ALTER TABLE customers
MODIFY (city (+8), lastname (+4));
d. ALTER TABLE customers
MODIFY (city (20), lastname (14));

14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
a. The constraint exists between two tables, called the parent table and the child table.
b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.

15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
a. ALTER USER rthomas PASSWORD EXPIRE ;
b. ALTER USER rthomas CHANGE PASSWORD;
c. ALTER USER rthomas UPDATE PASSWORD;
d. ALTER USER rthomas EXPIRE PASSWORD;

16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
a. Asc c. ascending
b. A d. either a or c

17. Which of the following is an accurate statement?
a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
d. none of the above

18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
a. NVL c. IFNVL
b. IFNL d. NVL2

Contents of the ORDERS table
19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
a. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
b. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL
GROUP BY order#;
c. SELECT COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
d. SELECT COUNT(*)
FROM orders
WHERE shipdate IS NULL;

20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
a. Z
b. 9
c. ,
d. .

Completion (4 points each)
Complete each sentence or statement.
21. A(n) ____________________ is a group of interrelated files.
22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
25. The ____________________ function is used to round numeric fields to a stated position.

SQL
26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
EMPLOYEE (employee name, street, city)
WORKS (employee name, company_name, salary)
A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation

27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
DEPTLOCATION (DNumber, DLocation)
PROJECT (PName, PNumber, PLocation, DNum)
WORKSON (ESSN, PNO, Hours)
DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)

Integrity Constraints:
Primary key = Foreign Key
EMPLOYEE.SSN = DEPENDENT.ESSN
EMPLOYEE.SSN = WORKSON.ESSN
EMPLOYEE.SSN = DEPARTMENT.MGRSSN
EMPLOYEE.SSN = EMPLOYEE.SuperSSN
DEPARTMENT.DNumber = EMPLOYEE.DNO
DEPARTMENT.DNumber = DEPTLOCATION.DNumber
DEPARTMENT.DNumber = PROJECT.DNum
PROJECT.PNumber = WORKSON.PNO

28. (18 points) Write SQL syntax to resolve the following queries.
- Find the names of all employees who are directly supervised by the employee named “John Doe”
- List the name of employees whose salary is greater than the average salary of his or her corresponding department
- For each department, retrieve the department name and the average salary of all employees working in that department.

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.

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