Are there any alternatives for my SQL queries?

[kordellas]

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

[urtrivedi]

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.

[smantscheff]

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.

[kordellas]

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 ?

[smantscheff]

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.