SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM (LINE_UNITS) > (SELECT MAX(LINE_UNITS) FROM LINE); Should you create an index on P_CODE? If so, write the SQL command to create that index. If not, explain your reasoning.
> 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?
> SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = ‘TN’ ORDER BY V_NAME; Assume that 10,000 vendors are distributed as shown in Table P11.18. What percentage of rows will be returned by the query? Table P11.18 Distribution of Vendors
> Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The results of running that query are shown in Figure P7.22. Figure P7.
> SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = ‘TN’ ORDER BY V_NAME; What indexes should you create and why? Write the SQL command to create the indexes.
> SELECT P_CODE, P_QOH*P_PRICE FROM PRODUCT WHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT) Should you create an index? If so, what would the index column(s) be, and why should you create that index?
> SELECT P_CODE, P_QOH*P_PRICE FROM PRODUCT WHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT) What is the likely data sparsity of the P_QOH and P_PRICE columns?
> SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM (LINE_UNITS) > (SELECT MAX(LINE_UNITS) FROM LINE); Write the command to create statistics for this table.
> SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM (LINE_UNITS) > (SELECT MAX(LINE_UNITS) FROM LINE); Should you create an index? If so, what would the index column(s) be, and why would you create that index? If not, explain your reas
> SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM (LINE_UNITS) > (SELECT MAX(LINE_UNITS) FROM LINE); What is the likely data sparsity of the LINE_UNITS column?
> Given the following query SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); Should you create an index? Why or why not? Figure P11.7 The Ch11_SaleCo ER Model CUSTOMER INVOICE LINE INV NUMBER %3 FK1 Cu
> 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 is the likely data sparsity of the EMP_SEX column?
> Assuming that pessimistic locking with the two-phase locking protocol is being used, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in P
> Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS. Figure
> Assuming that pessimistic locking is being used, but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction describ
> Create a simple transaction log (using the format shown in Table 10.13) to represent the actions of the two previous transactions.
> ABC Markets sell products to customers. The relational diagram shown in Figure P10.6 represents the main entities for ABC’s database. Note the following important characteristics: •A customer may make many purchases, e
> Suppose that your database system has failed. Describe the database recovery process and the use of deferred-write and write-through techniques.
> Using a simple example, explain the use of binary and shared/exclusive locks in a DBMS.
> What DBMS component is responsible for concurrency control? How is this feature used to resolve conflicts?
> Describe the three most common problems with concurrent transaction execution. Explain how concurrency control can be used to avoid those problems.
> Assuming that pessimistic locking with the two-phase locking protocol is being used, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in P
> Assuming that pessimistic locking is being used, but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction describ
> Suppose you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, t
> Write the SQL code that will list only the distinct project numbers found in the EMP_2 table. Figure P7.9 The contents of the EMP_2 table EMP NUM EMP LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_PCT PROJ_NUM 101 News John 08-Nov-00 502 5.0
> Starting with a consistent database state, trace the activities that are required to execute a set of transactions to produce an updated consistent database state.
> You have been assigned to design the database for a new soccer club. Indicate the most appropriate sequence of activities by labeling each of the following steps in the correct order. (For example, if you think that “Load the database” is the appropriate
> In a construction company, a new system has been in place for a few months and now there is a list of possible changes/updates that need to be done. For each of the changes/updates, specify what type of maintenance needs to be done: (a) Corrective, (b)
> Write the proper sequence of activities in the design of a video rental database. (The initial ERD was shown in Figure 9.9.) The design must support all rental activities, customer payment tracking, and employee work schedules, as well as track which emp
> Using the same procedures and concepts employed in Problem 1, how would you create an information system for the Tiny College example in Chapter 4?
> Suppose you perform the same functions Noted in Problem 2 for a larger warehousing operation. How are the two sets of procedures similar? How and why are they different?
> Suppose you have been asked to create an information system for a manufacturing plant that produces nuts and bolts of many shapes, sizes, and functions. What questions would you ask, and how would the answers to those questions affect the database design
> The ABC Car Service & Repair Centers are owned by the SILENT car dealer; ABC services and repairs only SILENT cars. Three ABC Car Service & Repair Centers provide service and repair for the entire state. Each of the three centers is independently managed
> Write the query that will write Oracle sequences to produce automatic customer number and invoice number values. Start the customer numbers at 1000 and the invoice numbers at 5000.
> Write the query that will show the invoice number, the invoice amount, the average invoice amount, and the difference between the average invoice amount and the actual invoice amount. (Figure P8.8) FIGURE P8.8 Invoice amounts compared to the average invo
> Write the query to show the invoice number, the customer number, the customer name, the invoice date, and the invoice amount for all customers with a customer balance of $1,000 or more. (Figure P8.7) FIGURE P8.7 Invoice of customers with a balance over $
> Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus percentage. Figure P7.9 the contents of the EMP_2 table EMP NUM EMP LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_PCT PROJ_NUM 10
> Write a query to display the lowest average cost of books within a subject and the highest average cost of books within a subject. (Figure P8.67) Figure P8.67 Lowest and highest average subject costs Lowest Avg Cost Highest Avg Cost 66.62 89.95
> Write a query to display the book number, title, subject, author last name, and the number of books written by that author. Limit the results to books in the Cloud subject. Sort the results by book title and then author last name. (Figure P8.66) Figur
> Write a query to display the book number, title, subject, average cost of books within that subject, and the difference between each book’s cost and the average cost of books in that subject. Sort the results by book title. (Figure P8.
> Write a query to display the author ID, first and last name for all authors that have never written a book with the subject Programming. Sort the results by author last name. (Figure P8.64) Figure P8.64 Authors that have never written on programming
> Write a query to display the book number, title, and cost of books that have the lowest cost of any books in the system. Sort the results by book number. (Figure P8.63) Figure P8.63 Least expensive books
> Write a query to display the patron ID and the average number of days that patron keeps books during a checkout. Limit the results to only patrons that have at least 3 checkouts. Sort the results in descending order by the average days the book is kept
> Write a query to display the average number of days a book is kept during a checkout. (Figure P8.61) Figure P8.61 Average days kept Average Days Kept 4.44
> Write a query to display the patron ID, last name, number of times that patron has ever checked out a book, and the number of different books the patron has ever checked out. For example, if a given patron has checked out the same book twice, that would
> Write the query that will generate only the records that are unique to the CUSTOMER_2 table. (Figure P8.6) Figure P8.6 Customers unique to the CUSTOMER_2 table CUST_LNAME CUST_FNAME McPherson Anne Kowalski Jan Chen George
> Write a query to display the patron ID, first and last name of all patrons that have never checked out any book. Sort the result by patron last name then first name. (Figure P8.59) Figure P8.59 Patrons that never checked out a book PAT ID PAT FNAM
> Write the SQL code to find the average bonus percentage in the EMP_2 table you created in Problem 8. Details from Problem 8: CREATE TABLE EMP_2 ( EMP_NUM CHAR(3) NOT NULL UNIQUE, EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_I
> Write a query to display the author ID, author last name, book title, checkout date, and patron last name for all the books written by authors with the last name “Bruer” that have ever been checked out by patrons with
> Write a query to display the book number, title, and number of times each book has been checked out. Limit the results to books that have been checked out more than 5 times. Sort the results in descending order by the number of times checked out, and t
> Write a query to display the book number, title, and number of times each book has been checked out. Include books that have never been checked out. Sort the results in descending order by the number times checked out, then by title. (Figure P8.56) Figu
> Write a query to display the book number, title, author last name, author first name, patron ID, last name, and patron type for all books currently checked out to a patron. Sort the results by book title. (Figure P8.55) Figure P8.55 Currently checked ou
> Write a query to display the author ID, first and last name, book number, and book title of all books in the subject “Cloud”. Sort the results by book title and then by author last name. (Figure P8.54) Figure P8.54 Bo
> Write a query to display the book number and the number of times each book has been checked out. Do not include books that have never been checked out. (Figure P8.53) Figure P8.53 Times checked out BOOK NUM Times Checked Out 5236 12 5235 9 5240 52
> Write a query to display the patron ID, full name (first and last), and patron type for all patrons. Sort the results by patron type, then by last name and first name. Ensure that all sorting is case insensitive. (Figure P8.52) Figure P8.52 Sorted patr
> Write a query to display the patron ID, book number, patron first name and last name, and book title for all currently checked out books. (Remember to use the redundant relationship described in the assignment instructions for current checkouts.) Sort t
> Write a query to display the author last name, first name, book title, and year for each book. (Figure P8.50) Figure P8.50 Author name and book title AU LNAME AU FNAME BOOK TITLE BOOK YEAR Durante Reba Beginner's Guide to JAVA 2012 Walsh Neal Datab
> Write the query that will show only the duplicate customer records. (Figure P8.5) Figure P8.5 Duplicate customer records CUST LNAME CUST FNAME Ortega Juan
> Write the SQL code that will produce a virtual table named REP_1. The virtual table should contain the same information that was shown in Problem 16. Details from Problem 16: Figure P7.16 The query results for Problem 16 PROJ_NAME PROJ_VALUE PROJ_BA
