Tables & SQL

[jfielding2000]

Hi Guys,

I have an exam coming up and I have a few past paper questions that I need answers to, to see if my ones are correct - to enable to further revise this topic.

I'm really looking for model answers - so I can make some good progress! I dont just want to pass - I want to at least get a half decent mark!

Here are the questions:

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. [5 marks]
b) A list of all part descriptions and their quantities appearing on order 39. [5 marks]
c) Delete all orders placed by customers in Wrexham. [5 marks]
d) Archive all orders placed by customer Clarke into a new table called tblArchive.
[5 marks]
e) Increase the price of all parts whose description includes the word “washer” by 4%. [5 marks]

 

[neileg]

I admire your honesty, or is it naivety? Most people try to sneak their assignments into these forums!

If someone one here does this for you, will you give them your qualification?

 

[jfielding2000]

haha I don't think so. I'm studying multimedia technologies and I was never expecting a database module! Pretty much all of us on this course are great at all modules but this one. The people who are good at this are the people on the programming course - cos their is a piece of piss for them - and unfortunately our lecturer teaches as if we all know about it already - bizzarre!

The good thing (for most of us), is that due to many complaints - databases is being scrapped from the course as of next year. But for now - I need to get as much info as possible - cos the lectures go straight over my (very simple) mind!

 

[georgedwilkinson]

It should take about 30 minutes to get Access to answer all of these questions for you (except the DDL on the archive table).

Build a blank database, create the 4 tables. Go to the relationships tool and drag:
tblOrders OrderNo to tblItems OrderNo
tblCustomers Custo to tblOrders CustomerNo
tblParts PartNo to tblItems PartNo

Go into the query designer and build the queries then change to sql view and copy the sql to your assignment. Experiment double clicking and dragging field on the tables to the "grid" and deleting them from the "grid".

(a) requires no joins and just a simple "> 1000" in the criteria on the OrderTotal field and sort ascending on the CustomerNo field.

(b) requires a join on 2 tables (just select them from the select table dialog, you already set the relationships up). Put "= 39" in the criteria for OrderNo.

Working those 2 problems should get you a long way. If you have specific questions about the others (hint, look in Access help for Delete query and append query), post your specific questions here.

You should be a much better multimedia technologist and world citizen once you figure out how to do this.

Unfortunate they are removing this requirement in the future. I believe every technology person should have some of these problem solving skills.

By the way, I could have written all the SQL in less time than it has taken me to write this explanation. But what would that accomplish?

HTH.

 

[jfielding2000]

Thanks for the reply. Will be really useful - am gonna do now.

If someone could write what they think is correct - I'd be v appreciative!

 

[jfielding2000]

By doing this, for part 1 I got:

SELECT tblCustomers.CutomerNo, tblOrders.OrderTotal
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CutomerNo=tblOrders.CustomerNo
WHERE (((tblOrders.OrderTotal)=">1000"))
ORDER BY tblCustomers.CutomerNo;

and for part 2 I got:

SELECT tblOrders.OrderNo, tblParts.Description, tblOrders.OrderTotal
FROM tblParts INNER JOIN (tblItems INNER JOIN tblOrders ON tblItems.OrderNo = tblOrders.OrderNo) ON tblParts.PartNo = tblItems.PartNo
WHERE (((tblOrders.OrderNo)=39));



I've attach what you reccommend to do. Is this correct? Sorry I'm such a noob!

Many thanks!

 

[georgedwilkinson]

SELECT tblCustomers.CutomerNo, tblOrders.OrderTotal
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CutomerNo=tblOrders.CustomerNo
WHERE (((tblOrders.OrderTotal)=">1000"))
ORDER BY tblCustomers.CutomerNo;

Looks accurate enough. Not sure why you needed to join to tblCustomers since the Customer number is in tblOrders.CustomerNo. It also looks like CutomerNo is mis-spelled...not sure if that could cause you an issue or not, but probably not, if you used the graphic designer to build your query.

SELECT tblOrders.OrderNo, tblParts.Description, tblOrders.OrderTotal
FROM tblParts INNER JOIN (tblItems INNER JOIN tblOrders ON tblItems.OrderNo = tblOrders.OrderNo) ON tblParts.PartNo = tblItems.PartNo
WHERE (((tblOrders.OrderNo)=39));

Again, it looks accurate. You didn't need to join tblOrders since you already have the order number in tblItems.OrderNo.

For accuracy sake, you might want to remove the superfluous tables from the 2 queries.

I've attach what you reccommend to do. Is this correct? Sorry I'm such a noob!

I can't open your file since my company is stuck using an old version.

You're going the right general direction. Just keep the queries simple and do exactly what the question says.

Don't apologize for being a noob, you only need to apologize if you choose not to learn.

For the next 3 problems, you will need to start out with a regular query using the parameters mentioned. Then look up Delete Query, Update Query, Append Query, and Make Table Query in Help. Make sure to check out the SQL in each case.

 

[jfielding2000]

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

how do I put in a new tables using SQL?


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

Do I have to * by 1.04 ?

 

[namliam]

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

how do I put in a new tables using SQL?

"create table" option in the query screen

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

Do I have to * by 1.04 ?

100 * 1.04 = 104 = +4%, so yes...

 

[gemma-the-husky]

i think jf should be congratulated on coming up with intelligent answers to his questions with just a bit of encouragement. hope you enjoyed doing it, and stick around a bit

 

[georgedwilkinson]

i think jf should be congratulated on coming up with intelligent answers to his questions with just a bit of encouragement. hope you enjoyed doing it, and stick around a bit

I agree totally!