Home » SQL & PL/SQL » SQL & PL/SQL » rounding/ codums/ etc ahh!
icon4.gif  rounding/ codums/ etc ahh! [message #236000] Tue, 08 May 2007 11:57 Go to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

I need to query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.






should look like below but the formatting got messed up

DNAME LOC Number of People Salary
-------------------- ---------------- --------------------- ----------
ACCOUNTING NEW YORK 3 2916.67
RESEARCH DALLAS 5 2175
SALES CHICAGO 6 1566.67
Re: rounding/ codums/ etc ahh! [message #236002 is a reply to message #236000] Tue, 08 May 2007 12:01 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Maple50175 wrote on Tue, 08 May 2007 19:57
I need to query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.



Aren't the answers of Oracle course tasks at the end of the books?

Gints Plivna
http://www.gplivna.eu
Re: rounding/ codums/ etc ahh! [message #236011 is a reply to message #236000] Tue, 08 May 2007 12:13 Go to previous messageGo to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

haha they are? I don't have a book though :-/

Re: rounding/ codums/ etc ahh! [message #236013 is a reply to message #236000] Tue, 08 May 2007 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
HR> desc employees
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 EMPLOYEE_ID                      NOT NULL NUMBER(6)
 FIRST_NAME                                VARCHAR2(20 CHAR)
 LAST_NAME                        NOT NULL VARCHAR2(25 CHAR)
 EMAIL                            NOT NULL VARCHAR2(25 CHAR)
 PHONE_NUMBER                              VARCHAR2(20 CHAR)
 HIRE_DATE                        NOT NULL DATE
 JOB_ID                           NOT NULL VARCHAR2(10 CHAR)
 SALARY                                    NUMBER(8,2)
 COMMISSION_PCT                            NUMBER(2,2)
 MANAGER_ID                                NUMBER(6)
 DEPARTMENT_ID                             NUMBER(4)

HR> desc DEPARTMENTS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 DEPARTMENT_ID                    NOT NULL NUMBER(4)
 DEPARTMENT_NAME                  NOT NULL VARCHAR2(30 CHAR)
 MANAGER_ID                                NUMBER(6)
 LOCATION_ID                               NUMBER(4)

HR> desc LOCATIONS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 LOCATION_ID                      NOT NULL NUMBER(4)
 STREET_ADDRESS                            VARCHAR2(40 CHAR)
 POSTAL_CODE                               VARCHAR2(12 CHAR)
 CITY                             NOT NULL VARCHAR2(30 CHAR)
 STATE_PROVINCE                            VARCHAR2(25 CHAR)
 COUNTRY_ID                                CHAR(2 CHAR)

EMPLOYEES -> (department_id) -> DEPARTMENTS -> (location_id) -> LOCATION
Join the 3 to get all columns then group by department_id.

Regards
Michel
Re: rounding/ codums/ etc ahh! [message #236093 is a reply to message #236011] Wed, 09 May 2007 00:48 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And you think you will learn from a course if you ask the solution to this problem here, without trying yourself?
If you did try, show us what you did and where you got stuck.
This is not a this-is-my-homework-assignment-please-solve-this-for-me forum.
Previous Topic: CURSORS
Next Topic: Cursors
Goto Forum:
  


Current Time: Tue Dec 06 04:18:28 CST 2016

Total time taken to generate the page: 0.08901 seconds