Term
| List the item number, description, and price of all food items |
|
Definition
| SELECT Item_Number, Item_Desc, Item_Price FROM Item; |
|
|
Term
| List all of the info from the Item table without using the wildcard (*) |
|
Definition
| SELECT Supplier_ID, Item_Number, Item_Desc, Item_Price, Item_PriceIncrease FROM Item; |
|
|
Term
| List all of the info from the Item table using the wildcard (*) |
|
Definition
|
|
Term
| List the first and last name of all of the employees that have a department code of ‘Shp’ |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Dept_Code='Shp'; |
|
|
Term
| List the first and last name for the employee who has the phone number 2259 |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Emp_Phone='2259'; |
|
|
Term
| List the last names and hire dates of all employees hired after 1996 |
|
Definition
| SELECT Emp_LastName, Emp_HireDate FROM Employee WHERE Emp_HireDate > #12/31/1996#; |
|
|
Term
| List all items that show no price increase |
|
Definition
| SELECT Item_Number, Item_Desc FROM Item WHERE Item_PriceIncrease IS NULL; |
|
|
Term
| List all unique department codes from the Employee table |
|
Definition
| SELECT DISTINCT Dept_Code FROM Employee; |
|
|
Term
| List the first and last name of all employees that have a credit limit which is between $25 and $30, inclusive. Sort the list by department code and then by credit limit, both in ascending order. |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Emp_CreditLimit BETWEEN 25 AND 30 ORDER BY Dept_Code, Emp_CreditLimit; |
|
|
Term
| List the first name, last name, credit limit and dept code of all employees that have a credit limit which is greater than or equal to $25. Sort the list by dept code in ascending order and then by credit limit in descending order. |
|
Definition
| SELECT Emp_FirstName, Emp_LastName, Emp_CreditLimit, Dept_Code FROM Employee WHERE Emp_CreditLimit >= 25 ORDER BY Dept_Code, Emp_CreditLimit DESC; |
|
|
Term
| List all items with a price increase of less than $0.60 and a price of at least $3.00 |
|
Definition
| SELECT Item_Number, Item_Desc FROM Item WHERE Item_PriceIncrease < 0.6 AND Item_Price >= 3; |
|
|
Term
| List the description of all food items that have a price between $1.00 and $5.00 (use the between keyword) |
|
Definition
| SELECT Item_Desc FROM Item WHERE Item_Price BETWEEN 1 and 5; |
|
|
Term
| List the names of all employees who have ‘o’ as the third letter of their last name |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Emp_LastName LIKE "??o*"; |
|
|
Term
| List the names of all employees that belong to a department whose name does not end with ‘t’ |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Dept_Code NOT LIKE "*t"; |
|
|
Term
| List all employees who do not belong to the ‘Shp’ department and who were not hired in 1995 |
|
Definition
| "SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Dept_Code<>"Shp" AND Emp_HireDate NOT BETWEEN #1/1/1995# AND #12-31-1995#" |
|
|
Term
| List all employees who have been serving the company as of today for more than 10 years (use the date() function) |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE DATEDIFF("yyyy", Emp_HireDate, Date()) > 10; |
|
|
Term
| Count the number of items that cost no more than $5.00, and identify them as 'Cheap' |
|
Definition
| SELECT COUNT(Item_Number) AS Cheap FROM Item WHERE Item_Price <= 5; |
|
|
Term
| For each manager that supervises at least 1 employee, list the number of employees they supervise along with the average credit limit of these supervisees |
|
Definition
| SELECT Emp_MgrID, COUNT(Emp_ID), AVG(Emp_CreditLimit) FROM Employee WHERE Emp_MgrID IS NOT NULL GROUP BY Emp_MgrID HAVING COUNT(Emp_ID) >= 1; |
|
|
Term
| Based on the projected price increase values, list the description of each food item along with its total projected price, using the heading "Next Year’s projected price" for the latter |
|
Definition
| SELECT Item_Desc, Item_Price+Item_PriceIncrease AS [Next Year's projected price] FROM Item; |
|
|
Term
| Set the price increase equal to 0 for all foods that currently have a null value for this attribute |
|
Definition
| UPDATE Item SET Item_PriceIncrease = 0 WHERE Item_PriceIncrease IS NULL; |
|
|
Term
| Delete the department which was added in #33 from the Department table |
|
Definition
| DELETE FROM Department WHERE Dept_Code = 'Lgt'; |
|
|
Term
| Insert a new employee named Cinzia Daldini, who was hired today but has not yet been assigned to a department, and who has a credit limit of $17 |
|
Definition
| INSERT INTO Employee ( Emp_ID, Emp_HireDate, Emp_FirstName, Emp_LastName, Emp_CreditLimit ) VALUES (100, #3/12/2011#, 'Cinzia', 'Daldini', 17); |
|
|
Term
| Using the ‘in’ operator, list the first and last name of any employees that are managers |
|
Definition
| SELECT Emp_FirstName, Emp_LastName FROM Employee WHERE Emp_ID IN (sELECT Emp_MgrID FROM Employee); |
|
|
