Pytanie akademickie o generowanie raportu z dochodem Opcje

[Mikom]

Witam, pytanie jest na końcu postu, zacznę od tego co już zrobiłem.

Z otrzymanej bazy danych utworzyłem następujące tabele:

Tabela 1 z kategoriami czekolady:

[SQL] pobierz, plaintext 
CREATE TABLE Chocolate_Category
(Chocolate_Code VarChar(3) NOT NULL,
Chocolate_Pref VarChar(45) NOT NULL, 
Constraint Choc_PK PRIMARY KEY (Chocolate_Code));
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
ALTER TABLE Chocolate_Category
ALTER COLUMN Chocolate_Pref VarChar(45) NOT NULL;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE UNIQUE INDEX Chocolate_Pref_IDX 
ON Chocolate_Category (Chocolate_Pref);
[SQL] pobierz, plaintext 

Tabela 2 z dostawą:

[SQL] pobierz, plaintext 
CREATE TABLE Shipping		
(Shipping_ID VarChar(8) NOT NULL,
Shipping_Type VarChar(25),
Days_To_Delivery Integer,
Fee Decimal (5,2),
Constraint Shipping_PK PRIMARY KEY (Shipping_ID));
[SQL] pobierz, plaintext 

Tabela 3 z danymi o produkcie:

[SQL] pobierz, plaintext 
CREATE TABLE Product
(Product_ID VarChar(8) NOT NULL,
Product_Name VarChar(45),
Cost Decimal(6,2),		      
Price Decimal(6,2),
Chocolate_Code VarChar(3),       
Oty_On_Hand Integer,
Wholesale_Item VarChar(3),
Qty_Per_Unit Integer,
Constraint ProductID_PK PRIMARY KEY (Product_ID),
Constraint ProductID_FK FOREIGN KEY (Chocolate_Code) REFERENCES Chocolate_Category (Chocolate_Code));
[SQL] pobierz, plaintext 

Tabela 4 z danymi klienta:

[SQL] pobierz, plaintext 
CREATE TABLE Customer
(Cust_ID VarChar(12) NOT NULL,
First_Name VarChar(25),
Last_Name VarChar(25),
Address VarChar(50),
City VarChar(25),
State VarChar(2),
Zip VarChar (5),
Phone VarChar(15),
Email VarChar(45),
Location_Pref VarChar(7),
Chocolate_Pref VarChar(45),
Company VarChar(45),
Wholesale VarChar(3),
StartDate Date,
Constraint CustID_PK PRIMARY KEY (Cust_ID), 
Constraint CustID_FK1 FOREIGN KEY (Location_Pref) REFERENCES Location (Location_ID),
Constraint CustID_FK2 FOREIGN KEY (Chocolate_Pref) REFERENCES Chocolate_Category (Chocolate_Pref));
[SQL] pobierz, plaintext 

Tabela 5 z tranzakcjami:

[SQL] pobierz, plaintext 
CREATE TABLE Transaction
(Transaction_ID VarChar(7) NOT NULL,
Date Date,
Cust_ID VarChar(12),
Location_ID VarChar(7),
Shipping_ID VarChar(8),
Payment_ID VarChar(12),
Transaction_Type VarChar(1),
Constraint Transaction_PK PRIMARY KEY (Transaction_ID),
Constraint Transaction_FK1 FOREIGN KEY (Cust_ID) REFERENCES Customer (Cust_ID),
Constraint Transaction_FK2 FOREIGN KEY (Location_ID) REFERENCES Location (Location_ID),
Constraint Transaction_FK3 FOREIGN KEY (Shipping_ID) REFERENCES Shipping (Shipping_ID),
Constraint Transaction_FK4 FOREIGN KEY (Payment_ID) REFERENCES Payment (Payment_ID));
[SQL] pobierz, plaintext 

I tabelę z pozycjami sprzedanymi gdzie do tranzakcji (Transaction_ID) sa przypisane ilosci sprzedane (Quantity) :

[SQL] pobierz, plaintext 
CREATE TABLE Line_Item
(Transaction_ID VarChar(7) NOT NULL,
Product_ID VarChar(8) NOT NULL,
Quantity Integer,
Constraint LineID_PK PRIMARY KEY (Transaction_ID, Product_ID), 
Constraint LineID_FK1 FOREIGN KEY (Transaction_ID) REFERENCES Transaction (Transaction_ID),
Constraint LineID_FK2 FOREIGN KEY (Product_ID) REFERENCES Product (Product_ID));
[SQL] pobierz, plaintext 

I mam trzy zadania odnośnie powyższych Tabel:

1. Utwórz raport zawierający identyfikator produktu (Prouct_ID), nazwę produktu (Product Name), koszt (Cost), cenę (Price), ilosc na stanie w magazynie (Qty_On_Hand). Grupuj według kategorii czekolady (Chocolate_Category). Sortuj według nazwy product (Product Name).

I po wygenerowaniu poniższego raportu wszystko gra:

[SQL] pobierz, plaintext 
SELECT Chocolate_Pref AS 'Kategorie Czekolady', Product.Product_ID AS 'Produkt ID',
Product_Name AS 'Nazwa Produktu', Cost AS 'Koszt', Price AS 'Cena', Qty_On_Hand AS 'Ilosc Produktu w Magazynie'
FROM Product, Chocolate_Category
WHERE Product.Chocolate_Code = Chocolate_Category.Chocolate_Code
GROUP BY Chocolate_Pref, Product_Name, Product.Product_ID, Cost, Price, Qty_On_Hand
ORDER BY Chocolate_Pref ASC, Product_Name ASC;
[SQL] pobierz, plaintext 

2. Utwórz raport wyświetlający przychody klienta. Raport ma zawierać imię I nazwisko kienta (Last_Name i First_Name), łączny przychód z produktów, całkowity koszt produktów i całkowity przychód kienta. Grupuj według klienta. Pokaż wszystkie dochody według każdego klienta i na koniec podsumuj doshody wszystkich klientów.

[SQL] pobierz, plaintext 
SELECT IsNull(Last_Name + ', ' + First_Name, 'Łącznie:') AS 'Nazwa Kienta',
Format (Sum(Price*Quantity), 'C', 'en-us') AS 'Łączny Przychód',
Format (Sum(Cost*Quantity), 'C', 'en-us') AS ' Łączny Koszt Sprzednych Towarów',
Format (Sum((Price-Cost)*Quantity), 'C', 'en-us') AS ' Łączny Zysk Brutto'
FROM Customer, Transaction, Product, Line_Item
WHERE Customer.Cust_ID = Transaction.Cust_ID
AND Transaction.Transaction_ID = Line_Item.Transaction_ID
AND Line_Item.Product_ID = Product.Product_ID
GROUP BY ROLLUP (Last_Name + ', ' + First_Name);
[SQL] pobierz, plaintext 

Mam natomiast problem z zadaniem trzecim:

3. Utwórz raport wyświetlający przychody uzyskane z każdego produktu. Raport ma zawierać identyfikator produktu (Prouct_ID), nazwę produktu (Product Name), koszt (Cost), cenę (Price), ilość na stanie w magazynie (Qty_On_Hand) i potencjalne przychody (Potential Revenue). Grupuj według kategorii czekolady (Chocolate_Category). Sortuj według potencjalnych przychodów (Potential Revenue) i nazwy produktu (Product Name).

Cena produktu (Price) jest w tabeli Product a ilości sprzedane (Quantity) sa przyporządkowane do poszczególnej tranzakcji (Transaction_ID) w tabeli Line_Item

Czy moge prosić o jakakolwiek pomoc z pytaniem trzecim? Jak wyświetlić przychody uzyskane z każdego produktu osobno?



Ten post edytował Mikom 3.12.2017, 00:09:08

[mmmmmmm]

[SQL] pobierz, plaintext 
SELECT  Chocolate_Pref AS Kategorie_Czekolady, Product.Product_ID, Product.Product_Name, Product.Cost, Product.Price, Product.Qty_On_Hand, Sum(Line_Item.Quantity*(Product.Price-Product.Cost)) Potential_Revenue
FROM Transaction, Product, Line_Item,  Chocolate_Category
WHERE Product.Chocolate_Code = Chocolate_Category.Chocolate_Code
AND Transaction.Transaction_ID = Line_Item.Transaction_ID
AND Line_Item.Product_ID = Product.Product_ID
GROUP BY Chocolate_Pref AS Kategorie_Czekolady, Product.Product_ID, Product.Product_Name, Product.Cost, Product.Price, Product.Qty_On_Hand
ORDER BY 7, Product.Product_Name
[SQL] pobierz, plaintext