Question

For each employee (EMP_NUM) working in stores with STORE_ID values of 101 and 102, display the number of invoices (aka invoice count) generated by them. The query should display EMP_NUM, STORE_ID and the invoie count in the ascending order of STORE_ID and descending order of invoice count.

For each Store (Store_ID) display the manager ID (EMP_MGR) and the number of Employees (aka employee count) working in the store excluding the manager in the descending order of employee count. Provide suitable aliases for aggregated columns.
SQL SCRIPT:
/* HomeToolsInc.sql - Script file for MS SQL Server DBMS */
/* This script file creates the following tables: */
/* REGION, STORE, CATEGORY, VENDOR, PRODUCT, CUSTOMER, CUSTOMER_2, INVOICE, LINE, EMP */
/* and loads some data into each table */
USE MASTER;
CREATE DATABASE HomeToolsInc;
GO
USE HomeToolsInc;______

Answer 1

To display the invoice count for each employee working in specific stores, use the provided SQL query. To show the manager ID and employee count for each store, use a different SQL query.

Step-by-step explanation:

Query to Display Invoice Count for Each Employee in Specific Stores

To display the number of invoices generated by each employee working in stores with STORE_ID values of 101 and 102, you can use the following SQL query:

SELECT EMP_NUM, STORE_ID, COUNT(*) AS invoice_count FROM INVOICE WHERE STORE_ID IN (101, 102) GROUP BY EMP_NUM, STORE_ID ORDER BY STORE_ID ASC, invoice_count DESC;

Query to Display Manager ID and Employee Count for Each Store

To display the manager ID and the number of employees working in each store (excluding the manager) in descending order of employee count, you can use the following SQL query:

SELECT STORE.STORE_ID, STORE.EMP_MGR, COUNT(EMP.EMP_NUM) AS employee_count FROM STORE JOIN EMP ON STORE.STORE_ID = EMP.STORE_ID WHERE EMP.EMP_NUM != STORE.EMP_MGR GROUP BY STORE.STORE_ID, STORE.EMP_MGR ORDER BY employee_count DESC;