Home » Other » Training & Certification » Query
Query [message #284424] Thu, 29 November 2007 11:23 Go to next message
ladywood
Messages: 4
Registered: November 2007
Junior Member
I am trying to figure out some homework problems I have to do in apex. i am stuck on 3 of them. any major whiz's who could help?
Re: Query [message #284429 is a reply to message #284424] Thu, 29 November 2007 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Post your questions and we give you tips and clues.

Regards
Michel
Re: Query [message #284710 is a reply to message #284424] Fri, 30 November 2007 11:26 Go to previous messageGo to next message
ladywood
Messages: 4
Registered: November 2007
Junior Member
I have one of the questions.

I have 2 tables- Departments and Locations
The following are the columns in each table.
*Departments
-DEPARTMENT_ID
-DEPARTMENT_NAME
-MANAGER_ID
-LOCATION_ID

*Locations
-LOCATION_ID
-CITY
-STATE

The question is:
List out the Department name, location city and the number of employees in the department for all departments.

And I am supposed to be using joins.
Re: Query [message #284715 is a reply to message #284424] Fri, 30 November 2007 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>And I am supposed to be using joins.
Please proceed to do so & show us what you have already tried using SQL*Plus, CUT & PASTE; plus <code tags>.

[Updated on: Fri, 30 November 2007 12:09] by Moderator

Report message to a moderator

Re: Query [message #284781 is a reply to message #284710] Sat, 01 December 2007 02:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

number of employees in the department for all departments.


You're going to need a third table i.e. the employees table, you will need a COUNT function a you will need a group by clause as well as your joins.

To help you on your way, here are a couple of links to the reading that will be relevant
Joins
Aggregate functions
Group by
Re: Query [message #285163 is a reply to message #284424] Mon, 03 December 2007 16:14 Go to previous messageGo to next message
ladywood
Messages: 4
Registered: November 2007
Junior Member
This query doesnt work yet but this is what I have so far. I know I am missing parts but I'm slightly stuck. And yes from above, I have another table -Employees.


Select d.department_name "Department", l.city, e.emp_count
FROM
(Select department_name "Department" from Departments)d,
(Select city from Locations)l,
(Select ___________)e
order by d.department_name
Re: Query [message #285174 is a reply to message #285163] Mon, 03 December 2007 16:58 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
That is a long way from how this sort of thing should be done.
Here is a sample
SELECT tab1.Col1, tab1.col2, tab2.col1, tab2.col2, tab2,col3, tab3.col1
FROM tab1
JOIN tab2
ON tab1.colx = tab2.colx
JOIN tab3
ON tab2.coly =tab3.coly

But you really need to check out the documentation which you can fins at http://tahiti.oracle.com or you might even want to have a look at the notes that I'm sure your teacher would have provided.

[Updated on: Mon, 03 December 2007 16:58]

Report message to a moderator

Re: Query [message #285185 is a reply to message #284424] Mon, 03 December 2007 19:24 Go to previous messageGo to next message
ladywood
Messages: 4
Registered: November 2007
Junior Member
I tried this but definitely didnt work

SELECT Employees.Employee_ID, Employees.Department_ID, 
Departments.Department_ID, Departments.Department_Name, 
Departments. Location_ID, Locations.Location_ID, Locations.City
FROM Employees
JOIN Departments
ON Employees.Department_ID= Departments.Department_ID
JOIN Locations
ON Departments.Location_ID= Locations.Location_ID



select  department_name, city
FROM departments
natural join Locations;



^^^That works for the first 2 requirements of the question. But I dont know how to get the count for employees per deparment

[Updated on: Tue, 04 December 2007 01:15] by Moderator

Report message to a moderator

Re: Query [message #285186 is a reply to message #284424] Mon, 03 December 2007 19:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT COUNT(Employees.Employee_ID), .........
Re: Query [message #285252 is a reply to message #285185] Tue, 04 December 2007 01:39 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

I tried this but definitely didnt work
What happened, Error, wrong results, monitor blew up, aliens landed and told you that it wouldn't work (the last one happened to me the other day) Smile
Previous Topic: Design problem
Next Topic: Certification [merged topic]
Goto Forum:
  


Current Time: Mon Dec 05 04:33:02 CST 2016

Total time taken to generate the page: 0.06287 seconds