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

Recommended Answers

All 4 Replies

For learning basic queries, microsoft access is good software.
There you can drag columns and apply functions, and you can see sql query generating on its own.

Your queries look fine, and I don't know any query generator which would render them better or more compact or readable.
I only wonder why in your last query you use a left join instead of an inner join.

Your queries look fine, and I don't know any query generator which would render them better or more compact or readable.
I only wonder why in your last query you use a left join instead of an inner join.

Can you tell me of a query generator ?

Like urtrivedi says, MS Access used to have a decent query composing interface. Navicat has a good query builder, too. But they all are worthless compared to the real stuff: notepad.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.