SQL Queries? (mySQL) 1

[HarryBarton]

Hi guys - we have just started with mySQL and are having a bit of trouble grasping exactly what a query can be used for.

this is the layout of our database..

3NF

Venue Table
*Venue ID, Name of Venue, Venue Address 1, Venue Address 2, Venue Postcode, Risk Accessed? , Venue Capacity, (Owner ID)

Owner Table
*Owner ID, Owner Forename, Owner Surname, Owner Address 1, Owner Address 2, Owner Postcode, Owner Telephone Number, Owner Email Address

Carer table
*Carer Employee Number, Carer Forename, Carer Surname, Carer Address 1, Carer Address 2, Carer Postcode, Carer Telephone Number, Carer Email Address, Image Link, External Qualifications, Internal Qualifications

Job table
*Job Number, Job date, (Venue ID), (Organiser ID), (Carer Employee Number(s))

Organiser table
*Organiser ID, Organiser Forename, Organiser Surname, Organiser address 1, Organiser Address 2, Organiser Postcode, organiser telephone number, organiser email address

* = primary key
in brackets = foreign key

anyway, it says to make some queries that would show relevant data and then sketch some forms and reports to make use of the queries

we are all a bit confused as to what kind of queries we would need? It's a database for a mobile creche company who get a call from a customer - check out the venue they want to hold it in and then send carers down to look after kids on location.

any ideas? sometimes it's hard to think when you've looked at something so much! just even 1 simple example is more than likely enough for us to understand.

thanks all

 

[Golom]

it says to make some queries that would show relevant data and then sketch some forms and reports to make use of the queries

That has it a bit backward. Build some mock ups of the reports that you want from this data and, from that, generate the queries to provide the data in that form. You create queries to provide what a report or form needs. You don't throw some query together and then hope that it is suitable for some report.

Just to get you started, here's a query that would give you the owners for each venue where the risk has not been assessed.

Select V.[Venue ID], V.[Name of Venue], O.[Owner Forename], O.[Owner Surname]

From [Venue Table] As V INNER JOIN [Owner Table] As O
     ON V.[Owner ID] = O.[Owner ID]

Where V.[Risk Accessed?] = FALSE

Incidentally, it is usually not a good idea to have column names that include spaces.

 

[HarryBarton]

thanks, so I suppose it's best to use an underscore if I want to have column names like the ones I listed.


I remember creating reports in old access courses I did a few years ago and they were basically sets of information presented in a nicer way than the normal database view

but what does a form do? or atleast, how will I be able to make use of them?

I apologise if these questions are really really simple but I want the guys I'm working with to fully understand so we're all on the same wavelength.

 

[r937]

"anyway, it says to make some queries that would show relevant data"

harry, may i ask, what says? where did you get this instruction from?

r937.com | rudy.ca

 

[Golom]

... where did you get this instruction from?
I hope it wasn't r937.com!

... but what does a form do?
That's a question that indicates that you need to do a bit of experimentation with Access; possibly opening up some of the sample applications to see how others have used forms. This may sound like a smart-a** comment but you can do anything you want with forms ... it's not intended that way though. It's just that forms are one of Access's basic building blocks and what you can do with them is limited only by your imagination and skill level. You do however have to acquire some skills to understand the potential.

 

[lespaul]

The idea behind forms comes from not letting the end user have direct access to the tables. The developer needs to present the records in the tables to the user in such a way that new records can be added to all tables, records can be edited and the fact that an edit occurred if necessary, and the ability to delete records if appropriate.

Congratulations on developing a normalized table structure, most first time database developers don't even know about normalization and don't have a proper table structure. As you learn more you will find that doing this will make development and support easy in the long run.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ceh4702]

You might be able to make some forms with MS Access if you have that on a PC. Another option might be building some local web forms with javascript.

A form is just like a webpage where you fill in the blanks or use some slection criteria and then in a window or a separate window you see the report.

You could set this all up in Access and just make a model, but that takes a while. Might be easier to just make the forms up.

Querry may be "SELECT FIELD, FIELD
From FILE, FILE
Where condition 1, condition 2
GROUP BY FIELD
ORDER BY FIELD

Typically a condition is like AGE = "" OR Foreign key = Primary key. Or list oreders by date and Agent, list employees by supervisor, etc.

It helps to have a standard SQL book with some examples. Typically the examples are about a Video Store.

If you do not like my post feel free to point out your opinion or my errors.