Hello everyone. It has probably been over a year since I have posted something. I am somewhat new to Access and have mostly work in Excel. I have been given the following test question and I am somewhat stumped. Any help would be appreciated. Most important here is setting up the Many-Many relationship. I am sure the query is strightforward.
Deliverable
Write an SQL statement that lists the department(s) that have at least one sale of every type of item delivered to them. The department need not have sold out of the item or exhausted their inventory to qualify.
Assumptions
The tables below follow a relational schema using foreign keys in a many-to-many relationship. Assume that the data types and relationships are properly enforced by the DBMS. Please form your answer in ANSI-compliant (SQL92) structures. Please describe any other assumptions required.
Metadata
The table Item contains the inventory (SKU’s) for sale
The table Department contains the list of departments (stores) selling items
The table Delivery contains the list of deliveries of items to departments
The table Sale contains the list of sales of items by departments
Example of Tables
Table: Item
ItemID ItemName
1 Item1
2 Item2
3 Item3
4 Item4
5 Item5
Table: Department
DeptID DeptName
1 Dept1
2 Dept2
3 Dept3
4 Dept4
5 Dept5
Table: Sale
SaleID ItemID DeptID Qty
1 1 1 75
2 2 1 67
3 3 1 61
4 4 1 82
5 5 1 73
6 1 3 100
7 2 3 70
8 3 4 67
9 4 5 78
10 5 5 99
Table: Delivery
DeliveryID ItemID DeptID Qty
1 1 1 120
2 2 1 183
3 3 1 152
4 4 1 185
5 5 1 173
6 1 2 173
7 2 2 124
8 3 2 150
9 4 2 135
10 5 2 114
11 1 3 120
12 2 3 175
13 3 3 118
14 2 4 125
15 3 4 102
16 1 5 149
17 4 5 146
18 5 5 127
Bookmarks