WHERE WarehouseID IN (SELECT WarehouseID FROM WAREHOUSE WHERE Manager = 'Lucille Smith'); Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use JOIN ON syntax. SQL Solutions to Project Questions 2.17 – 2.52 are contained in the Microsoft Access database DBP-e13-IM-CH02-Cape-Codd-RQ.accdb which is available on the text’s Web site (). SELECT SKU, SKU_Description, WAREHOUSE.WarehouseID FROM INVENTORY, WAREHOUSE WHERE INVENTORY.WarehouseID=WAREHOUSE.WarehouseID AND Manager = 'Lucille Smith'; Write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON syntax. SQL Solutions to Project Questions 2.17 – 2.52 are contained in the Microsoft Access database which is available on the text’s Web site (). For Microsoft SQL Server, Oracle Database and SELECT SKU, SKU_Description, WAREHOUSE.WarehouseID FROM INVENTORY JOIN WAREHOUSE ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID WHERE Manager = 'Lucille Smith'; For Microsoft Access: Microsoft Access requires the SQL JOIN ON syntax INNER JOIN instead of just JOIN: For Microsoft SQL Server, Oracle Database and MySQL: SELECT SKU, SKU_Description, WAREHOUSE.WarehouseID FROM INVENTORY INNER JOIN WAREHOUSE ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID WHERE Manager = 'Lucille Smith'; Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery. SQL Solutions to Project Questions 2.17 – 2.52 are contained in the Microsoft Access database DBP-e13-IM-CH02-Cape-Codd-RQ.accdb which is available on the text’s Web site ().
SELECT PROJECT.ProjectID, PROJECT.Name AS [Project Name], PROJECT.Department, DEPARTMENT.Phone AS DepartmentPhone, EMPLOYEE.EmployeeNumber, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Phone AS EmployeePhone FROM ((DEPARTMENT JOIN PROJECT ON DEPARTMENT.DepartmentName = PROJECT.Department) INNER JOIN EMPLOYEE ON DEPARTMENT.DepartmentName = EMPLOYEE.Department) INNER JOIN ASSIGNMENT ON (PROJECT.ProjectID = ASSIGNMENT.ProjectID) AND (EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber) ORDER BY PROJECT.ProjectID; What can we do? There are two work arounds. First, create the query without Department Phone. This is the only column needed from the DEPARTMENT table, which can thus be eliminated from the query. The QBE query is (note the use of the aliases ProjectName, ProjectDepartment and EmployeePhone): The results will be correct, but without the DepartmentPhone column. The results are: Alternatively, as devised by Professor John Schauf of Edgewood College, Madison, WI, you can illustrate building a set of queries, where each one uses the previous query and adds one additional table. This is possible because Microsoft Access allows saved queries to be used as the equivalent of a table in a query. By adding in one table at a time, you can control the JOIN...ON statement sequence, and obtain the correct answer. This is a much better solution, because the end result is exactly what we want, rather than a truncated version of it. You should use this solution in class to illustrate how to use Microsoft Access query objects as pseudo tables in queries, and point out that they can also be used in forms and reports. The steps below show how to create the needed sequence of QBE queries: (1) Create a query that joins PROJECT and ASSIGNMENT, and name it Note that you must include ASSIGNMENT.EmployeeNumber in this query. Also note the use of the two aliases ProjectName and ProjectDepartment: (2) Create a query that joins QBE-Query-02-G-PA and DEPARTMENT, and name it Note that you will have to manually link the DEPARTMENT primary key to the foreign key in QBE-Query-02-G-PA. Also note the use of the alias DepartmentPhone:
Compare these results with those shown for SQL-Query-02-H above, and you will see the difference. For reference, here is the SQL code that Microsoft Access created from the QBE query: SELECT PROJECT.ProjectID, PROJECT.Name AS [Project Name], PROJECT.Department AS ProjectDepartment, DEPARTMENT.Phone AS DepartmentPhone, EMPLOYEE.EmployeeNumber, EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.Phone AS EmployeePhone FROM ((DEPARTMENT INNER JOIN PROJECT ON DEPARTMENT.DepartmentName = PROJECT.Department) INNER JOIN EMPLOYEE ON DEPARTMENT.DepartmentName = EMPLOYEE.Department) JOIN ASSIGNMENT ON (PROJECT.ProjectID = ASSIGNMENT.ProjectID) AND (EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber) WHERE (((PROJECT.Department)="Marketing")) ORDER BY PROJECT.ProjectID; The problem we are encountering here is the same as described in 2.64 G. Again, there are two work arounds. First, create the query without Department Phone. This is the only column needed from the DEPARTMENT table, which can thus be eliminated from the query. The QBE Query is (note the use of the aliases ProjectName, ProjectDepartment and EmployeePhone): The results will be correct, but without the DepartmentPhone column: Alternatively, as devised by Professor John Schauf of Edgewood College, Madison, WI, you can illustrate building a set of queries, where each one uses the previous query and adds one additional table. This is possible because Microsoft Access allows saved queries to be used as the equivalent of a table in a query. By adding in one table at a time, you can control the JOIN...ON statement sequence, and obtain the correct answer. This is a much better solution, because the end result is exactly what we want, rather than a truncated version of it. You should use this solution in class to illustrate how to use Microsoft Access query objects as pseudo tables in queries, and point out that they can also be used in forms and reports. The steps below show how to create the needed sequence of QBE queries:
/* *** *** */ SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate FROM SHIPMENT DepartureDate LIKE '12%'; However, Microsoft Access uses the wildcard *, gives the following SQL statement: /* *** SQL-Query-MI-E-Access *** */ SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate FROM SHIPMENT WHERE DepartureDate LIKE '12*'; Assume DepartureDate and ArrivalDate are in the format MM/DD/YY. List the ShipmentID, ShipperName, and ShipperInvoiceNumber and ArrivalDate of all shipments that departed on the tenth day of any month. Solutions to Morgan Importing questions are contained in the Microsoft Access database DBP-e13-IM-CH02-MI.accdb which is available in the Instructor’s Resource Center on the text’s Web site (). The correct SQL-92 statement, which uses the wildcards % and _, is: /* *** SQL-Query-MI-F *** */ SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate FROM SHIPMENT WHERE DepartureDate LIKE '___10%'; However, Microsoft Access uses the wildcards * and ?, give the following SQL statement: /* *** SQL-Query-MI-F-Access-A *** */ SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate FROM SHIPMENT WHERE DepartureDate LIKE '???10*'; Further, Microsoft Access does NOT show the leading zero in MM, so we must add a compound WHERE clause to get months without the leading zeros: /* *** SQL-Query-MI-F-Access-B *** */ SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate FROM SHIPMENT WHERE DepartureDate LIKE '???10*' OR DepartureDate LIKE '??10*';