Hi, ive got some work to do on SQL queries. Ive attempted every question at the bottom, but could do with someone to tell me whether there correct or not and inform me on what im doing wrong.

The scenario is:

A local company that produces machine parts has decided to develop an in-house database system.

They have identified the following tables: -

tblOrders OrderNo, CustomerNo, Date, OrderTotal
tblCustomers CustomerNo, Name, Street, Town, County, Postcode
tblParts PartNo, Description, UnitCost
tblItems OrderNo, PartNo, Quantity, ItemTotal

Create SQL queries to produce the following: -

a) Details of all orders over £1000 sorted by customer
number.

b) A list of all part descriptions and their quantities
appearing on order 39

c) Delete all orders placed by
customers in Wrexham.

d) Archive all orders placed by customer Clarke into a new table called
tblArchive.

e) Increase the price of all parts whose description includes the
word “washer” by 4%.

These are my answer, which im not too sure if they are correct. If any1 could tell me if there correct or not that would be great, thanks.

a)
SELECT *
FROM tblOrders
ORDERBY CustomerNO
WHERE OrderTotal > 1000

b)
SELECT tblParts.PartNo, tblParts.Description, tblItems.Quantity
FROM tblItems INNER JOIN tblParts ON tblItems.PartNo = tblParts.PartNo;
WHERE OrderNo = 39

c)
DELETE tblOrders.*
FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID
WHERE Town = “Wrexham”

d)
INSERT INTO tblArchive
SELECT *
FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID
WHERE Name = “Clarke”

e)
UPDATE tblParts
SET UnitCost = [UnitCost]*1.04
WHERE Description LIKE “*washer” or Description LIKE “washer*” or
Description LIKE “*washer*”

Please help

You should NEVER use * in a select query, it slows the query AND can lead to problems should the database be restructured at a later stage.

a) it's ORDER BY (two words) and ORDER has to be AFTER a WHERE clause.

b) looks ok

c) should be "DELETE FROM table" and there is no CustomerID column

d) You are assuming tblArchive already exists, whereas the question calls for a new table to be created.
CREATE TABLE FROM AS ( SELECT fieldlist FROM table WHERE ... );

e) % is the wildcard symbol in SQL. * is only used by MS Access

That's from a look over not real testing done.

thanks

You might want to think about not putting tbl as the beginning of each table name.