SQL is my "weak" part, however I can handle it. I was wondering if there are alternative ways of writing my queries below. Any alternative is welcome, it's a way to learn more.
I have the expected/correct results on all of them.
**Shows which shops have the product code 1234 giving the code, name and address of them.**
SELECT k.ShopCode, Name, Address FROM shops as k INNER JOIN availability as d ON k.ShopCode=d.ShopCode WHERE d.ProductCode = '1234';
**Shows the average value of transactions in the shop with code 2, including the name, address and the average price of transactions.**
SELECT Name, Address, AVG(QUANTITY*ProductPrice) AS AveragePrice FROM shop AS k INNER JOIN chashier AS t ON k.ShopCode = t.ShopCode INNER JOIN buy AS a ON t.CashierCode = a.CashierCode INNER JOIN contains AS p ON a.BuyCode = p.BuyCode WHERE t.ShopCode = '1' GROUP BY NAME, ADDRESS
**Shwos the name, address and the amount of the top three in total purchasing customers of the chain.**
SELECT TOP 3 BuyerName, BuyerSurname, Address, SUM(QUANTITY*ProductPrice) AS AveragePrice FROM buyer AS pe INNER JOIN buy AS a ON pe.CardCode = a.BuyerCode INNER JOIN contains AS p ON a.BuyCode= p.BuyCode GROUP BY pe.CardCode, BuyerName, BuyerSurname, Address ORDER BY AveragePrice DESC;
**This one shows the new products that not yet launched in the shops and therefore
not participate in any buy. The answer includes the code and description of the product.**
SELECT BuyCode, description from products WHERE product.BuyCode not in ( select distinct store_has_products.product_id FROM store_has_products)
**It shows the manager of each shop? The answer includes the store code and the name of the manager.**
SELECT works.store_id as, employee.first_name, employee.last_name FROM manager left join employee on manager.id = employee.id lefti join works on employee.id = works. employee_id