Create a sample XML document and DTD for the exchange of customer data.
> What is a stored procedure, and why is it particularly useful? Give an example.
> What is a trigger, and what is its purpose? Give an example.
> Modify the query in Problem 30 to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you would count three product purcha
> What is a sequence? Write its syntax.
> What string function should you use to list the first three characters of a company’s EMP_LNAME values? Give an example, using a table named EMPLOYEE.
> Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 12
> What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?
> What MS Access/SQL Server function should you use to calculate the number of days between the current date and January 25, 1999?
> Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query?
> Given the employee information in Question 11, what is the query output for the MINUS query? (List the query output.) Details from Question 11: Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three e
> Given the employee information in Question 11, what is the query output for the INTERSECT query? (List the query output.) Details from Question 11: Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for th
> What is a recursive join?
> Explain why it would be preferable to use a DATE data type to store date data instead of a character data type.
> Modify the query used in Problem 29 to produce the summary shown in Figure P7.30. FIGURE P7.30 Customer Purchase Summary CUS CODE CUS BALANCE Total Purchases 444.00 153.85 10011 0.00 10012 345.86 10014 0.00 422.77 10015 0.00 34.97 10018 216.55 70.4
> Write an example of a database request.
> What is the difference between the COUNT aggregate function and the SUM aggregate function?
> Explain why the two following commands produce different results. SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;
> Explain the difference between an ORDER BY clause and a GROUP BY clause.
> Rewrite the following WHERE clause without the use of the IN special operator. WHERE V_STATE IN (‘TN’, ‘FL’, ‘GA’)
> What are “referential constraint actions”?
> What is the difference between a column constraint and a table constraint?
> Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results, and then reset the job code to its original value. ATTRIBUTE (FIELD) NAME DATA DECLARATION
> Write the SQL code that will save the changes made to the EMP_1 table. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3)
> Assuming the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP
> Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P7.2. Figure P7.2 The contents of the EMP_1 table Figure P7.1 Structure and contents of the Ch07_ConstructCo dat
> Using the output shown in Figure P7.29 as your guide, generate the listing of customer purchases, including the subtotals for each of the invoice line numbers. (Hint: Modify the query format used to produce the listing of customer purchases in Problem 1
> Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the table below. (Note that the JOB_CODE is the FK to JOB.) ATTRIBUTE (FIELD
> Write a query to display the author ID, first name, last name, and year of birth for all authors born in the decade of the 1980s (See Figure P7.79). FIGURE P7. 79 Authors Born in the 1980s AU ID AU FNAME AU LNAME AU BIRTHYEAR 218 Rachel Beatney Wals
> Write a query to display the book number, title, year of publication, subject, and cost for all books that are on the subjects of “Middleware” or “Cloud,” and that cost more than $70
> Write a query to display the book number, title, and year of publication of all books published after 2013 and on the “Programming” subject (See Figure P7.77). FIGURE P7. 77 Newer Books on Programming BOOK NUM BOO
> Write a query to display the checkout number, book number, and checkout date of all books checked out before April 5, 2015 (See Figure P7.76). FIGURE P7. 76 Checkouts before April 5th CHECK NUM BOOK NUM CHECK OUT DATE 91001 5235 3/31/2015 91002 523
> Write a query to display the book number, title, and year of publication for all books in the “Database” subject (See Figure P7.75). FIGURE P7. 75 Database Books BOOK NUM BOOK TITLE BOOK YEAR 5237 Mastering the da
> Write a query to display the book number, title, and year of publication for all books published in 2012 (See Figure P7.74). FIGURE P7. 74 Books Published in 2012 BOOK NUM BOOK_TITLE BOOK YEAR 5235 Beginner's Guide to JAVA 5236 Database in the Cloud
> Write a query to display the book title, year, and subject for every book. Sort the results by book subject in ascending order, year in descending order, and then title in ascending order (See Figure P7.73). FIGURE P7. 73 Books by Cascading Sort BO
> Write a query to display the checkout number, book number, patron ID, checkout date, and due date for every checkout that has ever occurred in the system. Sort the results by checkout date in descending order (See Figure P7.72). (68 rows) FIGURE P7. 72
> Write a query to display the book number, title, and cost of each book (See Figure P7.71). FIGURE P7. 71 Title and Replacement Cost for Books BOOK NUM ВОOK TITLE Replacement Cost 5235 Beginner's Guide to JAVA 5236 Database in the Cloud 59.95 79.95 5
> Generate a listing of all purchases made by the customers, using the output shown in Figure P7.28 as your guide. (Hint: Use the ORDER BY clause to order the resulting rows as shown in Figure P7.28) FIGURE P7.28 List of Customer Purchases CUS_CODE
> Write a query to display the different subjects on which FACT has books. Include each subject only once (See Figure P7.70). FIGURE P7. 70 Unique Book Subjects BOOK SUBJECT Cloud Database Middleware Programming
> Write a query to display the different years that books have been published in. Include each year only once (See Figure P7.69). FIGURE P7. 69 Unique Book Years BOOK YEAR 2012 2013 2014 2015
> Write a query to display the book number, book title, and year of publication for every book (See Figure P7.68). FIGURE P7. 68 Title and Year for all Books BOOK NUM TITLE Year Published 5235 Beginner's Guide to JAVA 2012 5236 Database in the Cloud 2
> Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994 and whose job code is at least 501. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VA
> Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 502 or higher. When you finish Problems 10 and 11, the EMP_2 table will contain the data sho
> Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15)
> Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values as shown in Figure P7.9. Figure P7.9 The contents of the EMP_2 table7 E
> Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to its structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteri
> Write the SQL code that will restore the data to its original status; that is, the table should contain the data that existed before you made the changes in Problems 5 and 6. ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VAR
> Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (Hint: Use logical operators to include all of the information given in this problem. Remember, if you
> Write a query to count the number of customers with a customer balance over $500. FIGURE P7.26 THE CH07_SALECO DATABASE Relational Diagram Database name: Ch07_SaleCo CUSTOMER INVOICE LINE PRODUCT VENDOR 8 INV_NUMBER Y LINE_NUMBER 8 P_CODE P_DESCRIPT
> Create a sample XML document and DTD for the exchange of product and pricing data.
> Use MS Excel to list all classes taught in room KLR200 using the Ch02_TinyCollege System DSN.
> Create a System DSN ODBC connection called Ch02_TinyCollege using the Administrative Tools section of the Windows Control Panel.
> Use MS Excel to list all of the invoice lines for Invoice 1003 using the Ch02_SaleCo System DSN.
> Create a System DSN ODBC connection called Ch02_SaleCo using the Administrative Tools section of the Windows Control Panel.
> Use MS Excel to connect to the Ch02_InsureCo MS Access database using ODBC, and retrieve the customers whose AGENT_CODE is equal to 503.
> Use MS Excel to connect to the Ch02_InsureCo MS Access database using ODBC, and retrieve all of the CUSTOMERs.
> Create a sample XML document and DTD for the exchange of student transcript data. Use your college transcript as a sample.
> Create a sample XML document and DTD for the exchange of order data.
> Write a query to count the number of invoices. FIGURE P7.26 THE CH07_SALECO DATABASE Relational Diagram Database name: Ch07_SaleCo CUSTOMER INVOICE LINE PRODUCT VENDOR 8 INV_NUMBER Y LINE_NUMBER 8 P_CODE P_DESCRIPT Y CUS CODE 00 V INV_NUMBER CUs COD
> Use MS Excel to connect to the Ch02_InsureCo MS Access database using ODBC, and retrieve all of the AGENTs.
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. What is the SQL command to list the number of product sales (number of rows) and total sales by month, with subtotals by month and a grand total for all sales?
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. What is the SQL command to list the total sales by month and product category, with subtotals by month and a grand total for all sales?
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. What is the SQL command to list the total sales by region and customer, with subtotals by region and a grand total for all sales?
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem What is the SQL command to list the total sales by customer, month and product, with subtotals by customer and by month and a grand total for all product sales?
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. What is the SQL command to list the total sales by customer and by product, with subtotals by customer and a grand total for all product sales?
> ROBCOR, Inc., whose sample data are contained in the database named Ch13_P4.mdb, provides "on demand" aviation charters using a mix of different aircraft and aircraft types. Because ROBCOR has grown rapidly, its owner has hired you to be its first databa
> David Suker, the inventory manager for a marketing research company, wants to study the use of supplies within the different company departments. Suker has heard that his friend, Ephanor, has developed a spreadsheet-based data warehouse model that she us
> Victoria Ephanor manages a small product distribution company. Because the business is growing fast, Ephanor recognizes that it is time to manage the vast information pool to help guide the accelerating growth. Ephanor, who is familiar with spreadsheet s
> Create your own data analysis and visualization presentation. The purpose of this project is for you to search for a publicly available data set using the Internet and create your own presentation using what you have learned in this chapter. a. Search fo
> Write the SQL code to generate the total hours worked and the total charges made to all projects. The results should be the same as those shown in Figure P7.24. Figure P7.24 Total hours and charges, all employees SumOfSumOfASSIGN HOURS SumofSumOfA
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. Using the answer to Problem 10 as your base, what command would you need to generate the same output but with subtotals in all columns? (Hint: Use the CUBE command).
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. What is the SQL command to list the number of product sales (number of rows) and total sales by month, product category and product, with subtotals by month and product
> Using the data provided in the Ch13_SaleCo_DW database, solve the following problem. What is the SQL command to list the number of product sales (number of rows) and total sales by month and product category with subtotals by month and product category a
> The university computer lab's director keeps track of the lab usage, as measured by the number of students using the lab. This particular function is very important for budgeting purposes. The computer lab director assigns you the task of developing a da
> Given the scenario and the requirements in Problem 2, answer the following questions: a. What recommendations will you make regarding the type and characteristics of the required database system? b. What type of data fragmentation is needed for each tabl
> The following data structure and constraints exist for a magazine publishing company. a. The company publishes one regional magazine each in Florida (FL), South Carolina (SC), Georgia (GA), and Tennessee (TN). b. The company has 300,000 customers (subscr
> Figure P12.1 The DDBMS Scenario Specify the minimum types of operations the database must support to perform the following operations. These operations should include remote request, remote transaction, distributed transaction, and distributed request
> Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); What is the likely data sparsity of the P_PRICE column? Figure P11.7 The Ch11_SaleCo ER Model CUSTOMER INVOICE LINE INV NUMB
> Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); What type of database I/O operations will likely be used by the query? (See Table 11.3.) Figure P11.7 The Ch11_SaleCo ER Model
> Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); Assuming that there are no table statistics, what type of optimization will the DBMS use? Figure P11.7 The Ch11_SaleCo ER Model
> Write the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure P7.24. (Hint: This is a nested query. If you use Microsoft Access, you can generate the result by using the query output s
> SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR FROM EMPLOYEE WHERE YEAR (EMP_DOB) = 1966; What type of database I/O operations will likely be used by the query? (See Table 11.3.)
> SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR FROM EMPLOYEE WHERE YEAR(EMP_DOB) = 1966; Should you create an index on EMP_DOB? Why or why not?
> SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR FROM EMPLOYEE WHERE YEAR (EMP_DOB) = 1966; What is the likely data sparsity of the EMP_DOB column?
> SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = ‘615’ GROUP BY CUS_CODE; How would you rewrite the query to ensure that the index you created in Problem 31 is used?
> SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = ‘615’ GROUP BY CUS_CODE; What indexes would you recommend for the query you wrote in Problem 30, and what SQL commands would you use?
> SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = ‘615’ GROUP BY CUS_CODE; Assuming that you follow the recommendations you gave in Problem 29, how would you rewrite the query?
> Using Table 11.4 as an example, create two alternative access plans. Use the following assumptions: a. There are 8,000 employees. b. There are 4,150 female employees. c. There are 370 employees in area code 615. d. There are 190 female employees in area
> SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = ‘615’ GROUP BY CUS_CODE; Assuming that you generate 15,000 invoices per month, what recommendation would you give the designer about th
> SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH < P_MIN AND P_MIN = P_REORDER’ AND P_REORDER = 50; ORDER BY P_QOH; What indexes you would recommend? Write the commands to create those indexes.
> SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH < P_MIN AND P_MIN = P_REORDER’ AND P_REORDER = 50; ORDER BY P_QOH; Use the recommendations given in Section 11-5b to rewrite the query to produce the required results more effi
> Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table. The output is shown in Figure P7.23. Figure P7.23 Total hour and charges by project PROJ NUM SumOfASSIGN HOURS SumOfASSIGN C
> SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = ‘21344’ ORDER BY P_CODE; How should you rewrite the query to ensure that it uses the index you created in your solution to Problem 25?
> SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = ‘21344’ ORDER BY P_CODE; What index would you recommend, and what command would you use?
> SELECT P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = ‘NY’ AND V_AREACODE = ‘212’; ORDER BY P_PRICE; Write the command(s) used to generate the statistics for the PRODUCT and VENDOR
> SELECT P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = ‘NY’ AND V_AREACODE = ‘212’; ORDER BY P_PRICE; Write the commands required to create the indexes you recommended in Problem 22
> SELECT P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = ‘NY’ AND V_AREACODE = ‘212’; ORDER BY P_PRICE; What indexes would you recommend?
> Assume that you have 10,000 different products stored in the PRODUCT table and that you are writing a Web-based interface to list all products with a quantity on hand (P_QOH) that is less than or equal to the minimum quantity, P_MIN. What optimizer hint
> Using Table 11.4 as an example, create two alternative access plans.
> SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX FROM EMPLOYEE WHERE EMP_SEX = ‘F’ AND EMP_AREACODE = ‘615’ ORDER BY EMP_LNAME, EMP_FNAME; What indexes should you create? Write the required SQL commands.
> SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = ‘TN’ ORDER BY V_NAME; What type of I/O database operations would be most likely to be used to execute that query?
