Assuming you completed problem 10, write the query that will list the names and ages of your customers.
> 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
> Write a query to display the author ID, book number, title, and year for each book. (Figure P8.49) Figure P8.49 Authors of books BOOK YEAR AU ID BOOK NUM 273 BOOK TITLE 5235 Beginner's Guide to JAVA 2012 383 5236 Database in the Cloud 2012 5237 Mas
> Write a query to display the author last name, author first name, and book number for each book written by that author. (Figure P8.48) Figure P8.48 Books written by author AU LNAME AU FNAME BOOK NUM Reeves Benson 5237 Reeves Benson 5253 Beatney Sal
> Write a query to display the book number, title with year, and subject for each book. (Figure P8.47) Figure P8.47 Book title with year BOOK NUM BOOK BOOK SUBJECT 5235 Beginner's Guide to JAVA (2012) 5236 Database in the Cloud (2012) Programming Clo
> Write a query to display the patron ID, patron full name, and patron type for each patron. (Figure P8.46) Figure P8.46 Patron and patron type РАT ID Patron Name PAT TYPE 1160 robert carter Faculty Faculty Faculty Student 1161 Kelsey Koch 1165 Cedri
> Write a query to display the patron ID, book number, and days kept for each checkout. “Days Kept” is the difference from the date on which the book is returned to the date it was checked out. (Figure P8.45) Figure P8.
> Write a query to display all rows in the PATRON table. Figure P8.44 Ch08_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date ---H- Pat_FName Pat_LName Pat_Type BOOK AUTHOR РК Book Num P
> Create a trigger named trg_cust_balance that will automatically update the CUSTOMER table’s CUST_BALANCE when a new CHARTER row is added. Use the CHARTER table’s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the custom
> Create a trigger named trg_pic_hours that will automatically update the PILOT table when a new CREW row is added and the CREW table uses a ‘pilot’ CREW_JOB entry. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the PILOT table’s PIL_PIC_HRS only when
> Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values. Table P8.34 Attribute
> Modify the PILOT table to add the attribute shown in the following table. Table P8.40 The New Attribute for the PILOT Table Attribute name Attribute Description Attribute type PIL_PIC_HRS Pilot in command (PIC) hours. Updated by adding the CHARTER
> Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database (see Figure P7.1), write the SQL code that will produce the results shown in Figure P7.16. Figure P7.1 Structure and contents of the Ch07_ConstructCo database Figure P7.16 Th
> Write the query that will generate a combined list of customers to include the duplicate customer records. (Figure P8.4) Figure P8.4 Combined list of customers with duplicates CUST LNAME CUST FNAME Smith Jeanne Ortega Juan McPherson Anne Ortega Juan
> Write the command required to update the CHAR_TOT_CHG attribute values in the CHARTER table. Table P8.34 Attribute name Attribute Description Attribute type CHAR_WAIT_CHG Waiting charge for each model (copied from the MODEL table.) Numeric CHA
> Write the command required to update the CHAR_TAX_CHG attribute values in the CHARTER table. Table P8.34 Attribute name Attribute Description Attribute type CHAR_WAIT_CHG Waiting charge for each model (copied from the MODEL table.) Numeric CHA
> Write the command required to update the CHAR_FLT_CHG attribute values in the CHARTER table. Table P8.34 Attribute name Attribute Description Attribute type CHAR_WAIT_CHG Waiting charge for each model (copied from the MODEL table.) Numeric CHA
> Write the sequence of commands required to update the CHAR_FLT_CHG_HR attribute values in the CHARTER table. Hint: Use either an updatable view or a stored procedure. Table P8.34 Attribute name Attribute Description Attribute type CHAR_WAIT_CHG Wa
> Write the sequence of commands required to update the CHAR_WAIT_CHG attribute values in the CHARTER table. Hint: Use either an updatable view or a stored procedure. Table P8.34 Attribute name Attribute Description Attribute type CHAR_WAIT_CHG Wait
> Modify the CHARTER table to add the attributes shown in the following table. Table P8.34 The New Attributes for the CHARTER Table Attribute name Attribute Description Attribute type CHAR_WAIT_CHG Waiting charge for each model (copied from the MOD
> Write the queries to update the MOD_WAIT_CHG attribute values based on problem 32. Details from problem 32: Table P8.32 The New Attribute for the MODEL Table Attribute name Attribute Description Attribute type Attribute Values MOD_WAIT_CHG Waitin
> Modify the MODEL table to add the following attribute and insert the values shown in Table P8.23. Table P8.32 The New Attribute for the MODEL Table Attribute name Attribute Description Attribute type Attribute Values MOD_WAIT_CHG Waiting charge p
> Create a procedure named prc_cus_balance_update that will take the invoice number as a parameter and update the customer balance. (Hint: You can use the DECLARE section to define a TOTINV numeric variable that holds the computed invoice total.) Use the C
> Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Assume case sensitivity. EMP NUM EMP LNAME EMP FNAME EMP INITIAL EMP HIRED
> Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product o
> Write the query that will generate a combined list of customers (from tables CUSTOMER and CUSTOMER_2) that do not include the duplicate customer records. Only the customer named Juan Ortega shows up in both customer tables. (Figure P8.3) Figure P8.3 Comb
> Create a trigger named trg_line_prod that will automatically update the product quantity on hand for each product sold after a new LINE row is added. Use the Ch08_SaleCo2 database Database name: Ch08_SaleCo2 Table name: CUSTOMER CUS CODE CUS_LNAME C
> Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time you add a new LINE row. (The LINE_TOTAL value is the product of the LINE_UNITS and the LINE_PRICE values.) Use the Ch08_SaleCo2 database Database name:
> The purchasing manager is still concerned about the impact of price on sales. Write a query to display the brand name, brand type, product SKU, product description, and price of any products that are not a premium brand, but that cost more than the most
> One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each bra
> LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total o
> Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then firs
> The Binder Prime Company wants to recognize the employee who sold the most of their products during a specified period. Write a query to display the employee number, employee first name, employee last name, e-mail address, and total units sold for the em
> Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and top coat products of the same brand on the same invoice. (Figure P8.22) Figure P8.22 Invoices for sealer and top coat of t
> Write the SQL command that will delete the newly created TEMP_1 table from the database.
> Write a query to display the starting salary for each employee. The starting salary would be the entry in the salary history with the oldest salary start date for each employee. Sort the output by employee number. (Figure P8.21) Figure P8.21 Starting sal
> Write a query to display the current salary for each employee in department 300. Assume that only current employees are kept in the system, and therefore the most current salary for each employee is the entry in the salary history with a NULL end date. S
> Insert the data into the tables you created in Problem 1. Figure P8.1 Ch08_SimpleCo Database Tables Database name: Ch08_SimpleCo Table name: CUSTOMER Table name: INVOICE CUST_NUM CUST_LNAME CUST_FNAME CUST_BALANCE 1000 Smith 1001 Ortega |INV_NUM CUS
> Write a query to display the products that have a price greater than $50. Figure P8.19 Ch08_SaleCo2 Database Tables LGCUSTOMER LGBRAND PK Cust Code |Рк |Brand ID Cust_Fname Cust_Lname Cust_Street Cust_City Cust_State Cust_ZIP Cust Balance Brand_Name
> Write a procedure to delete an invoice given the invoice number as a parameter. Name the procedure prc_inv_delete. Test the procedure by deleting invoices 8005 and 8006.
> Write a trigger to update the customer balance when an invoice is deleted. Name the trigger trg_updatecustbalance2.
> Write a procedure to add a new invoice record to the INVOICE table. Use the following values in the new record: 8006, 1000, ’30-APR-16’, 301.72 Name the procedure prc_invoice_add. Run a query to see if the record has been added.
> Write a procedure to add a new customer to the CUSTOMER table. Use the following values in the new record: 1002, ‘Rauthor’, ‘Peter’, 0.00 Name the procedure prc_cust_add. Run a query to see if the record has been added.
> Write the trigger to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Test the trigger using the following new INVOICE record: 8005, 1001, ’27-APR-16’, 225.40 Name the trigger tr
> Write the query that will list the average age of your customers. (Assume that the CUSTOMER table has been modified to include the CUST_DOB and the derived CUST_AGE attribute.)
> Write the two SQL command sequences required to: a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT. b. Copy the matching EMP_2 values into the TEMP_1 table. EMP NUM EMP LNAME EMP FNAME E
> Assuming the CUSTOMER table contains a CUST_AGE attribute, write the query to update the values in that attribute.
> Modify the CUSTOMER table to included two new attributes: CUST_DOB and CUST_AGE. Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22, 1988.
> Create the tables. (Use the MS Access example shown in Figure P8.1 to see what table names and attributes to use.) Figure P8.1 Ch08_SimpleCo Database Tables Database name: Ch08_SimpleCo Table name: CUSTOMER Table name: INVOICE CUST_NUM CUST_LNAME CU
> Write a query to display the movie number, movie genre, average movie cost of movies in that genre, movie cost of that individual movie, and the percentage difference between the average movie cost and the individual movie cost (result shown in Figure P7
> Write a query to display the rental number, rental date, movie title, and detail fee for each movie that was returned on or before the due date (result shown in Figure P7.124). Figure P7.124 Actual rental fees charged Rent Num Rent Date Movie T
> Write a query to display the rental number, rental date, video number, movie title, due date, and return date for all videos that were returned after the due date. Sort the results by rental number and movie title (result shown in Figure P7.123). Figur
> Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental (result shown in Figure P7.122). Figure P7.122 Minimum, maximum, and average balances Minimum Balance Maximum Balance Average Bal
> Write a query to display the membership number, first name, last name, and balance of the memberships that have a rental (result shown in Figure P7.121). Figure P7.121 Balances of memberships with rentals Mem Num Mem FName Mem LName Mem Balance 10
> Write a query to display the movie title, price description, and price rental fee for all movies that are in the genres Family, Comedy, or Drama (result shown in Figure P7.120). Figure P7.120 Movies with specific genres Movie Title Price Description
> What is the relationship between a database and an information system, and how does this relationship have a bearing on database design?
> Write a query to display the movie title, movie genre, and movie cost for all movies that have a cost between $44.99 and $49.99 (result shown in Figure P7.119). Figure P7.119 Movies costs within a range Movie_Title Movie_Genre Movie_Cost Time to Bur
> Write a query to display the movie title and movie year for all movies that have a price code (result shown in Figure P7.118). P7.118 Movies with a price Movie Title The Cesar Family Christmas Smokey Mountain Wildlife Richard Goodhope Beatnik Fever
> Write a query to display the movie title and the movie cost divided by the price rental fee for each movie that has a price to determine the number of rentals it will take to break even on the purchase of the movie (result shown in Figure P7.117). Figure
> Write a query to display the movie genre and average price rental fee for movies in each genre that have a price (result shown in Figure P7.116). Figure P7.116 Average rental fee by genre Movie Genre Average Rental Fee АCTION 2 COMEDY 3.25 DRAM
> Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code (result shown in Figure P7.115). Figure P7.115 Rental fees for movies Movie Title Movie Genre Price Description Price Ren
> Write a query to display the movie genre and average cost of movies in each genre (result shown in Figure P7.114). Figure P7.114 Average movie cost by genre Movie Genre Average Cost АCTION 52.72 COMEDY 44.12 DRAMA 58.46 FAMILY 39.95
> Write a query to display the average cost of all of the movies (result shown in Figure P7.113). Figure P7.113 Average movie cost Average Movie Cost 51.1275
> Write a query to display the movie genre and the number of movies in each genre (result shown in Figure P7.112). Figure P7.112 Number of movies in genre Movie Genre Number of Movies АCTION COMEDY 2 DRAMA FAMILY 1 3.
> Write a query to display the membership number, name, street, state, and balance for all members in Tennessee (TN), with a balance less than $5, and whose street name ends in “Avenue.” (result shown in Figure P7.111).
> Write a query to display the movie number, movie title, movie cost, and movie genre for all movies that are either action or comedy movies and that have a cost that is less than $50. Sort the results in ascending order by genre. (Result shown in Figure
> Discuss the possible data request scenarios in a distributed database environment. 1. Single request accessing a single remote database. (See Figure D12.1.) 2. Multiple requests accessing a single remote database. (See Figure D12.2.) 3. Multiple requests
> Write a query to display the movie number, movie title, and movie cost for all movies with a cost greater than $40 (result shown in Figure P7.109). P7.109 Movies costing more than $40 Movie Num Movie Title Movie Cost 1235 Smokey Mountain Wildlife 1
> Write a query to display the movie title, movie year, and movie genre for all action movies (result shown in Figure P7.108). Figure P7.108 Action movies Movie Title Smokey Mountain Wildlife Time to Burn Movie Year Movie Genre 2011 ACTION 2013 ACTI
> Write a query to display the movie title, movie year, and movie cost for all movies that contain the word “hope” anywhere in the title. Sort the results in ascending order by title (result shown in figure P7.107). Fig
> Write a query to display the movie number, movie title, and price code for all movies with a title that starts with the letter “R” (result shown in Figure P7.106). Figure P7.106 Movies starting with R Movie Num Mo
> Write a query to display the movie title, movie year, and movie genre for all movies sorted by movie genre in ascending order, then sorted by movie year in descending order within genre (result shown in Figure P7.105). Figure P7.105 Movies with multicol
> Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending order (result shown in Figure P7.104). Figure P7.104 Movies by year Movie Year Movie Title Movie Cost 2015 Constant Companion 2015 Richard Goodho
> Write a query to display the movie title, movie year, and movie genre for all movies (result shown in Figure P7.103). Figure P7.103 All Movies Movie Title Movie Year Movie Genre The Cesar Family Christmas Smokey Mountain Wildlife Richard Goodhope Be
> Write the SQL command to save the changes made to the PRICE and MOVIE tables in Problems 98 – 101.
> Write a single SQL command to increase all price rental fee values in the PRICE table by $0.50.
> Write the SQL command to change the price code for all Action movies to price code 3.
> Why does a multi-user database environment give us a special interest in transaction management and concurrency control?
> Write the SQL statements that might be used in transaction management and explain how they work.
> A Partnership failed to pay employment taxes for its employees and within the proper time limitations, the I.R.S. assessed the taxes against the Partnership, but not against individual partners. Later, the IRS tried to collect the taxes from the individu
> Peters, an employee of a union as a business agent was subpoenaed to testify before a legislative committee. He claimed that he was instructed by the union's secretary-treasurer in charge of employees to give false testimony when questioned. Instead, he
> Jackson Leasing Co. acquired an apartment complex having some problems. The tenants complained of wall cracks, peeling paint, water leaks, heating and electrical fixture problems, broken or inoperable windows, rodents and cockroaches, and the lack of suf
> Zane and Linda had often discussed the sale of a Zane’s restaurant to Linda. One night Zane and Linda were in a bar and again discussed the sale of the restaurant. At this time, Zane agreed in writing to sell the restaurant to Linda for $100,000 dollars.
> Peter, age 16, and Janet, age 25, agreed as follows. Peter, who was very religious, would pray to his favorite saint for guidance in selecting winning lottery numbers. He would give these numbers to Janet who would buy a ticket. Peter could not buy a tic
> Assume that Kersch enters into an oral agreement to lease a building for eleven months. Three months into the lease, Kersch decides to buy a building. Can he cancel the lease without penalty?
> Review the case file at the beginning of the chapter. How would Kersch protect his computer chip?
> How is the right of publicity different from the right of privacy? See the Elvis Presley case in this chapter.
> State the issues presented to the court in Groucho Marx Publications, Inc. v. Day and Night Company.
> The Arkansas Supreme Court was faced with this question: Does a child, who was created as an embryo through in vitro fertilization during his parents’ marriage, but implanted into his mother’s womb after the death of his father, inherit from the father u
> MR. CHIEF JUSTICE WARREN delivered the opinion of the Court. These cases come to us from the States of Kansas, South Carolina, Virginia, and Delaware. They are premised on different facts and different local conditions, but a common legal question justif
> Locate and summarize two family law statutes that address the issue of child custody. Provide the title of the code and the section number in your summary. Use Appendix IV to guide you in proper citation format. The index to the code will enable you to g
> State the issues presented to the Court in McCord v. McCord.
> Read the case of United States v. Morrison in Appendix VII. What common law torts could plaintiff claim in this case?
> Analyze the following factual situations and discuss possible torts. a. Peters points a gun at Quentin’s back, Quentin being unaware of what is happening. Peters then shoots and hits Quentin. b. Jones consents to a blood transfusion, providing the doctor
> Harry owned an old and deteriorating farmhouse that was unoccupied, boarded up, and had NO TRESPASS signs around it. Harry was very upset with the constant burglaries and break-ins and to stop the vandalism mounted a spring-loaded shotgun in the farmhous
> A television camera crew followed a group of city firefighters and paramedics with their permission. A call came in about a man having a heart attack. The camera crew and the paramedics rushed to the home of the reported victim. Without consent from anyo
> Brian, a twelve year old boy, sitting in a classroom and fooling around, kicked James, the boy sitting next to him. The kick was intentional. James had recently suffered an injury to the same leg. As a result of the kick, which in itself was slight, the
> Refer to the case file at the beginning of the chapter. If you represented the students, what arguments would you make that the student’s constitutional rights are being violated? If you represented the school, what arguments would you make that the scho
