Term
| List the first and last names of all employees who have lunch scheduled for Dec 4, 1998 |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee, Lunch WHERE Employee.Emp_ID = Lunch.Emp_ID AND Lunch_date = #12/4/1998#; |
|
|
Term
| List the name (description) and price of the most expensive item sold by Certified Beef Company |
|
Definition
| SELECT Item_Desc, Item_Price FROM Item, Supplier WHERE Item.Supplier_ID = Supplier.Supplier_ID AND Supplier.Supplier_Name = 'Certified Beef Company' AND Item_Price = (SELECT Max(Item_Price) FROM Item, Supplier WHERE Item.Supplier_ID = Supplier.Supplier_ID AND Supplier.Supplier_Name = 'Certified Beef Company'); |
|
|
Term
| List the last names of all employees in the Sales department, along with the dates they have lunch |
|
Definition
| SELECT Emp_LastName, Lunch_Date FROM Employee, Lunch, Department WHERE Employee.Emp_ID = Lunch.Emp_ID AND Employee.Dept_Code = Department.Dept_Code AND Dept_Name = "Sales" |
|
|
Term
| What did Carol Rose have for lunch on November 16, 1998? |
|
Definition
| SELECT Item_Desc FROM Item I, Lunch_item LI, Lunch L, Employee E WHERE E.Emp_ID = L.Emp_ID AND L.Lunch_ID = LI.Lunch_ID AND LI.Item_Number = I.Item_Number AND E.Emp_FirstName = 'Carol' AND E.Emp_LastName = 'Rose' AND L.Lunch_Date = #11/25/1998# |
|
|
Term
| List the maximum credit limit for all employees in a given department, along with their department name. Include in your list departments with no employees |
|
Definition
| SELECT MAX(Emp_CreditLimit) AS [Max credit limit], Dept_Name FROM Employee E RIGHT JOIN Department D ON E.Dept_Code = D.Dept_Code GROUP BY Dept_Name |
|
|
Term
| List all employees who have managers, along with those managers. Include department names |
|
Definition
| SELECT E.Emp_FirstName, E.Emp_LastName, M.Emp_FirstName AS Mgr_FirstName, M.Emp_LastName AS Mgr_LastName, D.Dept_Name AS Mgr_Dept FROM Employee E, Employee M, Department D WHERE E.Emp_MgrID = M.Emp_ID AND M.Dept_Code = D.Dept_Code |
|
|
Term
| Display in a single table the total number of lunches eaten by each employee. Please provide the restaurant name and the employee's last name in each case. |
|
Definition
| TRANSFORM COUNT(L.Lunch_ID) SELECT E.Emp_LastName FROM Department AS D, Employee AS E, Item AS I, Lunch AS L, Lunch_item AS LI, Supplier AS S WHERE D.Dept_Code=E.Dept_Code AND E.Emp_ID=L.Emp_ID AND L.Lunch_ID=LI.Lunch_ID AND LI.Item_Number=I.Item_Number AND I.Supplier_ID=S.Supplier_ID GROUP BY E.Emp_LastName PIVOT S.Supplier_Name; |
|
|
Term
| Display in a single table the total amount spent by each manager, at each restaurant, during November 1998. Please provide each manager's first and last name and each restaurant name in each case. |
|
Definition
| TRANSFORM IIF(IsNull(SUM(LI.LI_Quantity*I.Item_Price)),'$0', FORMAT(SUM(LI.LI_Quantity*I.Item_Price),"$0.00")) SELECT E.Emp_FirstName & " " & E.Emp_LastName AS Manager FROM Department AS D, Employee AS E, Item AS I, Lunch AS L, Lunch_item AS LI, Supplier AS S WHERE D.Dept_Code=E.Dept_Code AND E.Emp_ID=L.Emp_ID AND L.Lunch_ID=LI.Lunch_ID AND LI.Item_Number=I.Item_Number AND I.Supplier_ID=S.Supplier_ID AND E.Emp_ID IN (SELECT DISTINCT Emp_MgrID FROM Employee) AND Lunch_Date BETWEEN #11/1/1998# AND #11/30/1998# GROUP BY E.Emp_FirstName & " " & E.Emp_LastName PIVOT S.Supplier_Name; |
|
|
