|
Author
|
Topic: HELP NEEDED - SQL gurus, please help.
|
Burkey
Pilot
|
posted 04-20- 09:04 AM
   
I know this is off topic, I'm really sorry if it annoys anyone, but please could someone in the know help me? I've an Access97 database assignment due in tommorrow and as I've said previously my mind goes blank when it comes to maths + logic. I've struggled through everything else, but I can't get this one bit:For the given relations:
Appointments: AppointmentNo(Primary key),AppointmentDate, AppointmentTime, *OutpatientNo, *ConsultantNo, *ClinicNo Clinic: ClinicNo(Primary Key),ClinicName Consultant: ConsultantNo(primary Key), ConsultantName, RoomNo, *ClinicNo Outpatients: OutpatientNo(primary Key), OutpatientName, Address, TelNo, *ConsultantNo,*ClinicNo QUES: 3 QUERIES IN STANDARD SQL
*One using a join and an aggregate function
*One using a GroupBy and Having clauses
*One complex query of your choice
(Plus brief explaination of each query) I hope you don't think its cheap me doing this, but I've been trying for hours trying to get my head around this and I just can't get the Queries to run.I have put the effort in, but i just can't get a result  . I hate handing in somwthing unfinished! IP: Logged |
Burkey
Pilot
|
posted 04-20- 10:56 AM
just posting to top in the hope that someone to help...IP: Logged |
Sv
Pilot
|
posted 04-20- 11:23 AM
Learn it, it is fun!SELECT a.appointmentdate, a.appointmenttime, o.outpatientname
FROM appointments a, outpatients o
WHERE o.outpatientNo = a.outpatientno
AND a.appointmentDate < '4/1/00'
ORDER BY a.appointmentdate, a.appointmenttime Fun. I do this all freekin' day long...  ------------------
-Sv =FC=
WWI in SDOE!
IP: Logged |
Burkey
Pilot
|
posted 04-20- 12:10 PM
Thank you Sv!, thats a join and aggregate,right?. I kind of know it but I'm an absolute beginner when it comes to any kind of computing language, so my progress is slow I type in queries but I can't get them to run! This is great help though.BTW, if you do this stuff all day, how THE HELL do you get time to do what you've done here? How many hours in an American day? IP: Logged |
Elric
Pilot
|
posted 04-20- 06:12 PM
Burkey, sv's query is just a join.SELECT ConsultantName, Count(*) AS [Number of Appointments]
FROM Consultant, Appointments
WHERE Appointments.ConsultantNo = Consultant.ConsultantNo AND AppointmentDate > '04/20/00'
GROUP BY ConsultantName This is a join and an aggregate function ( SUM, COUNT, MIN, MAX ) etc. I think the questions are backwards, when you work with a database, you have to write the SQL to get the desired info. I would have asked questions like "How many apointments does each consultant have on or after the 20th April?" write the sql... Sv, Fun? I remember doing SQL at uni and hating it, it seemed so pointless, I thought I'd never use it. Now I'm using it everyday  Elric IP: Logged |
Burkey
Pilot
|
posted 04-20- 07:18 PM
Thanks for your help Elric! I think I'm slowly beginning to get the hang of it... IP: Logged |
Sv
Pilot
|
posted 04-21- 08:17 AM
Burkey,It turns out that an American day is 32 hours... 24 hours to work and 8 "virtual" hours when you pretend to sleep I agree too, it is very stange to come up with querries out of the blue! Oh, and they are only fun when they are for web applications -Sv IP: Logged |
Burkey
Pilot
|
posted 04-21- 11:24 AM
Well, project is handed in and now I'm taking the weekend off! Sv + Elric, Thanks for your help, it enabled me to enter something resembling SQL queries and I think I now understand the basics. I still inevitably have to look at the syntax for 30mins to find where it went wrong, though Sv, that American day is similar to mine in a way: 24 hrs of sleep (sometimes with eyes open but still dead to the world ) IP: Logged | |
FSIC Messageboard
UBBFriend: Email This Page to Someone! 
