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
SQL has some built-in functions and they are called as aggregate functions. SQL contains five built-in functions. They are:
“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.
SELECT column_Name1, column_Name2 FROM table_Name ORDER BY column_Name2;
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.
The “inner join” keyword is to select the matching records from two tables. The syntax of “inner join” is as follows:
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.
SELECT expression1, expression2, expression_n, aggregate_function (expression) FROM table_name WHERE conditions GROUP BY expression1, expression2, expression_n;
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.
The “BETWEEN” operator is to selects the in between values within the range given by user.
SELECT column_name1, column_name2, column_namen FROM table_name WHERE column_name BETWEEN range1 AND range2;
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...
Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!Get Started
A Guide to SQL
Principles of Information Security (MindTap Course List)
Fundamentals of Information Systems
Database Systems: Design, Implementation, & Management
Fundamentals of Information Systems
Fundamentals of Geotechnical Engineering (MindTap Course List)
Precision Machining Technology (MindTap Course List)
Principles of Information Systems (MindTap Course List)
Automotive Technology: A Systems Approach (MindTap Course List)
Cornerstones of Financial Accounting
Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)
Mechanics of Materials (MindTap Course List)
Principles of Geotechnical Engineering (MindTap Course List)
Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)
Management Of Information Security
International Edition---engineering Mechanics: Statics, 4th Edition
Fundamentals of Chemical Engineering Thermodynamics (MindTap Course List)
Solid Waste Engineering
Electric Motor Control
EBK ELECTRICAL WIRING RESIDENTIAL
Steel Design (Activate Learning with these NEW titles from Engineering!)
A+ Guide to Hardware (Standalone Book) (MindTap Course List)
Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)
Welding: Principles and Applications (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)