Database

Microsoft Access 2010 Chapter 8 Advanced Form Techniques Camashaly Design database AC 466 - AC 520

Save the Client View and Update Form. AC 499 - AC 500.
Save the Multipage Form. AC 500 - AC 518

Project - Advanced Form Techniques
Camashaly Design wants two additional form to use with its Clients and Business Analyst tables. The first form Client View and Update Form (Figure 8-1a), contains the fields in the Client table. The form has five command buttons: Next Record, Previous Record, Add Record, Delete Record, and Close Form. Clicking nay of these buttons causes the action indicated on the button to take place.
The form also contains a combo box for the Business Analyst Number field that assists users in selecting the correct analyst (Figure 8-1b).
To assist users in finding a client when they know the clients name, the form also includes a combo box they can use for this purpose (Figure 8-1c). After clicking the arrow, the user can simply click the client they want to find; Access then will locate the client and display that clients data in the form (Figure 8-1d).

For the second new form, Camashaly Design needs a multipage form that lists the numbers and names of the analyst. Selecting the first tab, the one labelled Datasheet, displays a subform listing about the course offering for clients of the selected analyst (Figure 8-2a).
Selecting the other tab, the one labelled Charts, displays two charts that illustrate the total hours spent and hours remaining by the analyst for the various courses (Figure 8-2b). In both charts, the slices of the pie represent the various course. They are color coded and legend at the bottom indicates the meaning of various colors. The size of pie slice gives a visual representation of the portion of the hours spent or hours remaining by the analyst for the particular course. The chats also includes specific percentages. If you look at the bottom purple slice in the Hours Spent by Course Offering chart, for example, you see that the color represent course C04. It signifies 42% of the total. Thus, for all the hours already spent on the various course offering by analyst 11, 42% have been spent on course C04.

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.

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.

Microsoft Access 2010 Chapter 3 Lab 2 Maintaining the Walburg Energy Alternatives Database

Problem: The management of the Walburg Energy Alternatives recently acquired some items from a store that is going out of business. You now need to append these new items to the current item table. You also need to change the database structure and add some validation rules to the database.
Use the database modified in the In the Lab 2 of Chapter 2 on page AC 134 for this assignment. You also will use the More Items database from the Data Files for Students. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the required files.

Perform the following tasks:
1. Open the More Items database from the Data Files for Students.
2. Create a new query for the Item table and add all fields to the query.
3. Using an append query, append all records in the More Items database to the Item table in the Walburg Energy Alternatives database, as shown in Figure 3 – 87.
4. Save the append query as Walburg Append Query and close the More Items database.
5. Open the Walburg Energy Alternatives database and then open the Item table in Datasheet view. There should be 20 records in the table.
6. The items added from the More Items database do not have a vendor assigned to them. Assign items 1234 and 2234 to vendor JM. Assign item 2216 to vendor AS. Assign items 2310 and 2789 to vendor SD.
7. Create an advanced filter for the Item table. The filter should display records with fewer than 10 items on hand and be sorted in ascending order by Description. Save the filter settings as a query and name the filter Reorder Filter.
8. Make the following changes to the Item table:
a. Change the field size for the On Hand field to Integer. The Format should be fixed and the decimal places should be 0.
b. Make Description a required field.
c. Specify that the number on hand must be between 0 and 50. Include validation text.
d. Add a calculated field Inventory Value (On Hand*Cost) following the Cost field. Format the field as currency.
9. Save the changes to the table design. If a dialog box appears indicating that some data may be lost, click the Yes button.
10. Add the Inventory Value field to the Inventory Status Report. Place the field after the Cost field. Save the changes to the report.
11. Specify referential integrity between the Vendor table (the one table) and the Item table (the many table). Cascade the update but not the delete.
12. Submit the revised More Items database and the Walburg Energy Alternatives database in the format specified by your instructor.

Student Database Oracle DDL and Queries

Create the following tables.
STUDENT
Student Number (PK)
Student Last Name
Student Major
Department ID (FK)
Student GPA
Student Hours
Student Class
Advisor ID (FK)

ADVISOR
Advisor ID (PK)
Advisor Last Name
Advisor Office
Advisor Building
Advisor Phone

DEPARTMENT
Department ID (PK)
Department Code
Department Name
Department Phone

*NOTE* You will have to decide on how to handle the Department and Advisor ID foreign keys in terms of a numbering system as well as appropriate field widths and types for the fields.
The business rules which govern this database are: A student may have one advisor while an advisor may advise multiple students. A student belongs to only one department but each department can have many students.
Populate the table with data from Table P6.4 (p. 217).

There is an error in the book! Change Ortiz’s student number to be 200888. In addition, add these three students to your database.
STU_NUM 123984 995133 367181
STU_LNAME Freeman Wilder Green
STU_MAJOR CIT CIT BIS
DEPT_CODE CS CS IS
DEPT_NAME Computer Science Computer Science Business Informatics
DEPT_PHONE 5234 3951 3951
COLLEGE_NAME Informatics Informatics Informatics
ADVISOR_LNAME Strand Zhang Goh
ADVISOR_BLDG Griffin Griffin Griffin
ADVISOR_OFFICE 5132 3451 5612
ADVISOR_PHONE 1603 3512 7922
STU_GPA 2.5 3.9 2.3
STU_HOURS 97 58 63
STU_CLASS Senior Junior Junior

Part 1
Provide all DDL-related code. This includes table definition and creation, fk/pk creation, and populating the tables with data. Please include the output from Oracle that shows that everything was created correctly.

Part 2
Queries – For each query provide 1) What the output, specifically, should be based upon eyeballing the data 2) the SQL code used to generate the query, and 3) the output from Oracle. Please include any relevant fields you think the user of the query would need to interpret the output.
1. Advisors need the capability to generate a query that returns *all* student information based upon a student number. This will help them in the advisement process. Create a query that returns all information for student Freeman from each table.
2. Each year, college administrators need to know how many students are in each major. Create a query that counts the number of students in each major while displaying the major name.
3. Kroger has approached NKU with an internship opportunity! The business department chair needs to generate a mailing list to inform great students about a job opportunity. Create a query that shows all the student numbers, last names, majors, department names, and advisor’s last name for students in Business Admin. Students should have a minimum GPA of 2.5 or greater to be on this mailing list. List in ascending order of GPA.
4. To get an idea of the adequacy of admission standards, NKU needs to have an idea of the breakdown of students. By each college, show the number of students in each classification (freshmen, sophomore, junior, senior).
5. Due to a fire, Griffin Hall has burnt down. Write a SQL statement that updates all faculty who had offices in Griffin Hall to now be housed in the University Center.
6. New funding may be able to pay for an advising center. The dean would like to get an idea of how many students each faculty member currently advises. Create a query that shows the names and majors of students for each advisor along with the advisor’s name.
7. (Extra credit) Create a query that counts the number of students who are eligible for the Dean’s list (GPA >= 3.5) in each department.

Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

Problem: The management of ECO Clothesline wants to learn more about SQL and has determined a number of questions it wants SQL to answer. You must obtain answers to the questions posed by management.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Find all customers where the customer type is SAL. Include the Customer Number, Customer Name, and Sales Rep Number fields in the result. Save the query as Lab 7-1 Step 1 Query.
2. Find all customers located in Tennessee (TN) with a paid amount greater than $1,500.00. Include the Customer Number, Customer Name, and Amount Paid fields in the result. Save the query as Lab 7-1 Step 2 Query.
3. Find all customers whose names begin with the letter, C. Include the Customer Number, Customer Name, and City fields in the result. Save the query as Lab 7-1 Step 3 Query.
4. List all cities in descending order. Each city should appear only once. Save the query as Lab 7-1 Step 4 Query.
5. Display the customer number, name, sales rep number, first name, and last name for all customers. Sort the results in ascending order by sales rep number and customer number. Save the query as Lab 7-1 Step 5 Query.
6. List the average balance amount grouped by sales rep number. Name the average balance as Average Billed. Save the query as Lab 7-1 Step 6 Query.
7. Find the customer number and name for every pair of customers who are located in the same city. Save the query as Lab 7-1 Step 7 Query.
8. Find the customer numbers, names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once. Save the query as Lab 7-1 Step 8 Query.
9. Use a subquery to find all sales reps whose customers are located in Pineville. Save the query as Lab 7-1 Step 9 Query.
10. Find the average balance amount for sales rep 44. Save the query as Lab 7-1 Step 10 Query.
11. Submit the revised database in the format specified by your instructor.