BuyFindarrow_forward

Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482

Solutions

Chapter
Section
BuyFindarrow_forward

Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482
Chapter 8, Problem 22P
Textbook Problem
1775 views

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.

Chapter 8, Problem 22P, Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and

Program Plan Intro

SELECT statement:

It is used to retrieve information from the table or database. The syntax for the “SELECT” statement is given below:

Syntax:

SELECT * FROM table_Name;

WHERE clause:

“WHERE” statement is used limit the number of rows. For example: Consider a table “FTable” that has two columns named “FruitName” and “Color”. “WHERE” clause is used when there is a need to display the entire FruitName whose color is Red.

SELECT * FROM FTable WHERE color = 'red';

When the above statement is executed, red colored fruits get displayed.

Inner join:

The “inner join” keyword is to select the matching records from two tables. The syntax of “inner join” is as follows:

Syntax:

SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

“AND” operator:

The operator used to check two or more conditions using single query. The “AND” operator returns “true” when two conditions are satisfied in the query.

ORDER BY Clause:

SQL contains “ORDER BY” clause in order to sort rows. The values get sorted in ascending as well as descending order. The keyword used to sort values in ascending order is “ASC” and for descending order is “DESC”. By default, it sorts values by ascending order.

Syntax:

SELECT column_Name1, column_Name2 FROM table_Name ORDER BY column_Name2;

Explanation of Solution

SQL code:

The SQL code to display the invoices for sealer and top coat of the same brand is given below:

SELECT L.INV_NUM, L.LINE_NUM, P.PROD_SKU, P.PROD_DESCRIPT, L2.LINE_NUM, P2.PROD_SKU, P2.PROD_DESCRIPT, P.BRAND_ID FROM (LGLINE L INNER JOIN LGPRODUCT P ON L.PROD_SKU = P.PROD_SKU) INNER JOIN (LGLINE L2 INNER JOIN LGPRODUCT P2 ON L2.PROD_SKU = P2.PROD_SKU) ON L.INV_NUM = L2.INV_NUM WHERE P.BRAND_ID = P2.BRAND_ID AND P.PROD_CATEGORY = 'SEALER' AND P2.PROD_CATEGORY = 'TOP COAT' ORDER BY L.INV_NUM, L...

Still sussing out bartleby?

Check out a sample textbook solution.

See a sample solution

The Solution to Your Study Problems

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

Get Started

Chapter 8 Solutions

Database Systems: Design, Implementation, & Management
Show all chapter solutions
add
Ch. 8 - Suppose you have two tables: EMPLOYEE and...Ch. 8 - Given the employee information in Question 11,...Ch. 8 - Given the employee information in Question 11,...Ch. 8 - Given the employee information in Question 11,...Ch. 8 - Why does the order of the operands (tables) matter...Ch. 8 - What MS Access and SQL Server function should you...Ch. 8 - What Oracle function should you use to calculate...Ch. 8 - Suppose a PRODUCT table contains two attributes,...Ch. 8 - What string function should you use to list the...Ch. 8 - What is a sequence? Write its syntax.Ch. 8 - What is a trigger, and what is its purpose? Give...Ch. 8 - What is a stored procedure, and why is it...Ch. 8 - What is embedded SQL and how is it used?Ch. 8 - What is dynamic SQL, and how does it differ from...Ch. 8 - Create the tables. (Use the MS Access example...Ch. 8 - Insert the data into the tables you created in...Ch. 8 - Write the query that will generate a combined list...Ch. 8 - Write the query that will generate a combined list...Ch. 8 - Write the query that will show only the duplicate...Ch. 8 - Write the query that will generate only the...Ch. 8 - Write the query to show the invoice number, the...Ch. 8 - Write the query for all the invoices that will...Ch. 8 - Write the query that will write Oracle sequences...Ch. 8 - Modify the CUSTOMER table to include two new...Ch. 8 - Assuming that you completed Problem 10, write the...Ch. 8 - Assuming that the CUSTOMER table contains a...Ch. 8 - Write the query that lists the average age of your...Ch. 8 - Write the trigger to update the CUST_BALANCE in...Ch. 8 - Write a procedure to add a new customer to the...Ch. 8 - Write a procedure to add a new invoice record to...Ch. 8 - Write a trigger to update the customer balance...Ch. 8 - Write a procedure to delete an invoice, giving the...Ch. 8 - Write a query to display the products that have a...Ch. 8 - Write a query to display the current salary for...Ch. 8 - Write a query to display the starting salary for...Ch. 8 - Write a query to display the invoice number, line...Ch. 8 - Write a single SQL command to increase all price...Ch. 8 - Write a query to display the customer code, first...Ch. 8 - LargeCo is planning a new promotion in Alabama...Ch. 8 - One of the purchasing managers is interested in...Ch. 8 - The purchasing manager is still concerned about...Ch. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Write the queries to update the MOD_WAIT_CHG...Ch. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Modify the PILOT table to add the attribute shown...Ch. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Write a query to display all rows in the PATRON...Ch. 8 - Write a query to display the patron ID, book...Ch. 8 - Write a query to display the patron ID, patron...Ch. 8 - Write a query to display the book number, title...Ch. 8 - Write a query to display the author last name,...Ch. 8 - Write a query to display the author ID, book...Ch. 8 - Write a query to display the author last name,...Ch. 8 - Write a query to display the patron ID, book...Ch. 8 - Write a query to display the patron ID, full name...Ch. 8 - Write a query to display the book number and the...Ch. 8 - Write a query to display the author ID, first and...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the author ID, author...Ch. 8 - Write a query to display the patron ID, first and...Ch. 8 - Write a query to display the average number of...Ch. 8 - Write a query to display the patron ID and the...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the author ID, first and...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the lowest average cost...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Update the PRICE table to place the values shown...Ch. 8 - Create a sequence named rent_num_seq to start with...

Additional Engineering Textbook Solutions

Find more solutions based on key concepts
Show solutions add
List and describe the three major steps in executing the project plan.

Principles of Information Security (MindTap Course List)

Discuss the importance of data models.

Database Systems: Design, Implementation, & Management

What is the difference between a value chain and a supply chain?

Principles of Information Systems (MindTap Course List)

What does WCS stand for?

Precision Machining Technology (MindTap Course List)

For Problem 19.22, calculate the probability distribution and plot the probability distribution curve.

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

What s the Hawthorne Effect? Have you ever experienced it? When and where?

Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)

A(n) ___ allows users to bypass security controls when accessing a program, computer, or network.

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)

What metals can be cut with the oxyfuel gas process?

Welding: Principles and Applications (MindTap Course List)