menu
bartleby
search
close search
Hit Return to see all results
close solutoin list

The Binder Prime Company wants to recognize the employee who sold the most of its products during a specified period. Write a query to display the employee number, employee first name, employee last name, email address, and total units sold for the employee who sold the most Binder Prime brand products between November 1, 2015, and December 5, 2015. If there is a tie for most units sold, sort the output by employee last name (Figure P7.51). FIGURE P7.49 STARTING SALARY FOR EACH EMPLOYEE

BuyFindarrow_forward

Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900

Solutions

Chapter
Section
BuyFindarrow_forward

Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900
Chapter 7, Problem 51P
Textbook Problem
414 views

The Binder Prime Company wants to recognize the employee who sold the most of its products during a specified period. Write a query to display the employee number, employee first name, employee last name, email address, and total units sold for the employee who sold the most Binder Prime brand products between November 1, 2015, and December 5, 2015. If there is a tie for most units sold, sort the output by employee last name (Figure P7.51).

FIGURE P7.49 STARTING SALARY FOR EACH EMPLOYEE

Chapter 7, Problem 51P, The Binder Prime Company wants to recognize the employee who sold the most of its products during a

Program Plan Intro

Aggregate Functions:

SQL has some built-in functions and they are called as aggregate functions. SQL contains five built-in functions. They are:

  • SUM – This function is used to add values from the particular column.
    • Syntax: SELECT SUM(column_Name) FROM table_Name;
  • COUNT – This is used to count the number of rows for the particular column.
    • Syntax: SELECT COUNT(column_Name) FROM table_Name;
  • MAX – This function is used to get the maximum value from the column.
    • Syntax: SELECT MAX(column_Name) FROM table_Name;
  • MIN – This function is used to get the minimum value from the column.
    • Syntax: SELECT MIN(column_Name) FROM table_Name;
  • AVG – This function is used to get the average of all the values from the column.
    • Syntax: SELECT AVG(column_Name) FROM table_Name;

“ORDER BY” Clause:

SQL contains “ORDER BY” clause in order to sort rows. The values get sorted in ascending and 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;

Join:

Join is a relational operation, which combines the data from two or more tables into single table or view, then that is called as Join.

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;

“GROUP BY” Clause:

The GROUP BY clause is used to group the result of a SELECT statement done on a table where the tuple values are similar for more than one column.

Syntax:

SELECT expression1, expression2, expression_n, aggregate_function (expression) FROM table_name WHERE conditions GROUP BY expression1, expression2, expression_n;

“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.

“BETWEEN” operator:

The “BETWEEN” operator is to selects the in between values within the range given by user.

Syntax:

SELECT column_name1, column_name2, column_namen FROM table_name WHERE column_name BETWEEN range1 AND range2;

Explanation of Solution

SQL code:

The SQL code is to display the employee first name, number, last name, email address, and total units sold for the employee who sold the most blinder prime brand products between the time limit from “November 1, 2015” and “December 5, 2015”.

SELECT emp.Emp_Num, Emp_FName, Emp_LName, Emp_Email, Total

FROM lgemployee AS emp INNER JOIN (SELECT employee_id, Sum(line_qty) AS total FROM ((lginvoice AS i INNER JOIN lgline AS l ON i.inv_num = l.inv_num) INNER JOIN lgproduct AS p ON l.prod_sku = p.prod_sku) INNER JOIN lgbrand AS b ON b.brand_id = p.brand_id WHERE brand_name = 'BINDER PRIME' AND INV_DATE BETWEEN #01-NOV-15# AND #06-DEC-15# GROUP BY employee_id)  AS sub ON emp.emp_num = sub.employee_id

WHERE total = (SELECT Max(total)                            FROM (SELECT employee_id, Sum(line_qty) AS total                                         FROM ((lginvoice i inner join lgline l ON i.inv_num = l.inv_num) inner join lgproduct p ON l.prod_sku = p.prod_sku) inner join lgbrand b ON b.brand_id = p...

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 7 Solutions

Database Systems: Design, Implementation, & Management
Show all chapter solutions
add
Ch. 7 - In a SELECT query, what is the difference between...Ch. 7 - What is a subquery, and what are its basic...Ch. 7 - What are the three types of results that a...Ch. 7 - What is a correlated subquery? Give an example.Ch. 7 - Explain the difference between a regular subquery...Ch. 7 - What does it mean to say that SQL operators are...Ch. 7 - The relational set operators UNION, INTERSECT, and...Ch. 7 - What is the difference between UNION and UNION...Ch. 7 - Suppose you have two tables: EMPLOYEE and...Ch. 7 - Given the employee information in Question 19,...Ch. 7 - Given the employee information in Question 19,...Ch. 7 - Given the employee information in Question 19,...Ch. 7 - Suppose a PRODUCT table contains two attributes,...Ch. 7 - Why does the order of the operands (tables) matter...Ch. 7 - What MS Access and SQL Server function should you...Ch. 7 - What Oracle function should you use to calculate...Ch. 7 - What string function should you use to list the...Ch. 7 - What two things must a SQL programmer understand...Ch. 7 - Given the structure and contents of the...Ch. 7 - Using the EMPLOYEE, JOB, and PROJECT tables in the...Ch. 7 - Write the SQL code that will produce the same...Ch. 7 - Write the SQL code that will list only the...Ch. 7 - Write the SQL code to validate the ASSIGN_CHARGE...Ch. 7 - Using the data in the ASSIGNMENT table, write the...Ch. 7 - Write a query to produce the total number of hours...Ch. 7 - Write the SQL code to generate the total hours...Ch. 7 - Write a query to count the number of invoices.Ch. 7 - Write a query to count the number of customers...Ch. 7 - Generate a listing of all purchases made by the...Ch. 7 - Using the output shown in Figure P7.12 as your...Ch. 7 - Write a query to display the customer code,...Ch. 7 - Modify the query in Problem 13 to include the...Ch. 7 - Use a query to compute the total of all purchases,...Ch. 7 - Create a query to produce the total purchase per...Ch. 7 - Use a query to show the invoices and invoice...Ch. 7 - Write a query to produce the number of invoices...Ch. 7 - Write a query to generate the total number of...Ch. 7 - List the balances of customers who have made...Ch. 7 - Provide a summary of customer balance...Ch. 7 - Create a query to find the balance characteristics...Ch. 7 - Find the listing of customers who did not make...Ch. 7 - Find the customer balance summary for all...Ch. 7 - Create a query that summarizes the value of...Ch. 7 - Find the total value of the product inventory. The...Ch. 7 - Write a query to display the eight departments in...Ch. 7 - Write a query to display the SKU (stock keeping...Ch. 7 - Write a query to display the first name, last...Ch. 7 - Write a query to display the first name, last...Ch. 7 - Write a query to display the employee number, last...Ch. 7 - Write a query to display the first name, last...Ch. 7 - Write a query to display the employee number, last...Ch. 7 - Write a query to display a brand name and the...Ch. 7 - Write a query to display the number of products in...Ch. 7 - Write a query to display the number of products...Ch. 7 - Write a query to display the total inventorythat...Ch. 7 - Write a query to display the brand ID, brand name,...Ch. 7 - Write a query to display the department number and...Ch. 7 - Write a query to display the employee number,...Ch. 7 - Write a query to display the customer code, first...Ch. 7 - Write a query to display the department number,...Ch. 7 - Write a query to display the vendor ID, vendor...Ch. 7 - Write a query to display the employee number, last...Ch. 7 - Write a query to display the largest average...Ch. 7 - Write a query to display the brand ID, brand name,...Ch. 7 - Write a query to display the manager name,...Ch. 7 - Write a query to display the current salary for...Ch. 7 - Write a query to display the starting salary for...Ch. 7 - Write a query to display the invoice number, line...Ch. 7 - The Binder Prime Company wants to recognize the...Ch. 7 - Write a query to display the customer code, first...Ch. 7 - LargeCo is planning a new promotion in Alabama...Ch. 7 - One of the purchasing managers is interested in...Ch. 7 - The purchasing manager is still concerned about...Ch. 7 - Write a query that displays the book title, cost...Ch. 7 - Write a query that displays the first and last...Ch. 7 - Write a query to display the checkout number,...Ch. 7 - Write a query to display the book number, book...Ch. 7 - Write a query to display the different years in...Ch. 7 - Write a query to display the different subjects on...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the checkout number, book...Ch. 7 - Write a query to display the book title, year, and...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the checkout number, book...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the author ID, first...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the patron ID, first and...Ch. 7 - Write a query to display the patron ID, first and...Ch. 7 - Write a query to display the author ID, first and...Ch. 7 - Write a query to display the author ID, first and...Ch. 7 - Write a query to display the checkout number, book...Ch. 7 - Write a query to display the author ID, first...Ch. 7 - Write a query to display the number of books in...Ch. 7 - Write a query to display the number of different...Ch. 7 - Write a query to display the number of books that...Ch. 7 - Write a query to display the highest book cost in...Ch. 7 - Write a query to display the lowest book cost in...Ch. 7 - Write a query to display the number of different...Ch. 7 - Write a query to display the subject and the...Ch. 7 - Write a query to display the author ID and the...Ch. 7 - Write a query to display the total value of all...Ch. 7 - Write a query to display the patron ID, patron...Ch. 7 - Write a query to display the book number, title...Ch. 7 - Write a query to display the author last name,...Ch. 7 - Write a query to display the author ID, book...Ch. 7 - Write a query to display the author last name,...Ch. 7 - Write a query to display the patron ID, book...Ch. 7 - Write a query to display the patron ID, full name...Ch. 7 - Write a query to display the book number and the...Ch. 7 - Write a query to display the author ID, first and...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the author ID, author...Ch. 7 - Write a query to display the patron ID, first and...Ch. 7 - Write a query to display the patron ID, last name,...Ch. 7 - Write a query to display the average number of...Ch. 7 - Write a query to display the patron ID and the...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the author ID, first and...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the book number, title,...Ch. 7 - Write a query to display the lowest average cost...

Additional Engineering Textbook Solutions

Find more solutions based on key concepts
Show solutions add
What tool can be used as a reference plane when performing layout?

Precision Machining Technology (MindTap Course List)

Give three examples of a materials mechanical properties.

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

How has technology changed in recent years? Provide of traditional, evolving and emerging technology.

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

Convert 5 megawatts of power into BTU/hr, ft-lbs/s, and kJ/hr.

Fundamentals of Chemical Engineering Thermodynamics (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)

How does a personal firewall protect your computer? (189)

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