Home » SQL & PL/SQL » SQL & PL/SQL » I need a command to issue a command in sql (oracle 11g on linux RH4)
I need a command to issue a command in sql [message #431307] Mon, 16 November 2009 10:56 Go to next message
zonastrutting
Messages: 2
Registered: November 2009
Location: NIGERIA
Junior Member

to issue a select statement to extract the first 5(five) maximum salary of employees.using hr schema.......
Re: I need a command to issue a command in sql [message #431312 is a reply to message #431307] Mon, 16 November 2009 13:02 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is an SQL FAQ; check the "top N" section.
Re: I need a command to issue a command in sql [message #431336 is a reply to message #431312] Mon, 16 November 2009 22:39 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
I hope this is not your homework...anyways good luck in learning SQL...Here you go for the top 5 salaries!
SQL>
SQL> -- create employee table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 2234.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 2324.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 3334.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 4334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 5334.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 6334.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 7334.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY                 DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- -------------------- --------------------------------------------------------------------------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto              Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    2234.78 Vancouver            Tester
03   James                Smith                12-DEC-78 15-MAR-90    2324.78 Vancouver            Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    3334.78 Vancouver            Manager
05   Robert               Black                15-JAN-84 08-AUG-98    4334.78 Vancouver            Tester
06   Linda                Green                30-JUL-87 04-JAN-96    5334.78 New York             Tester
07   David                Larry                31-DEC-90 12-FEB-98    6334.78 New York             Manager
08   James                Cat                  17-SEP-96 15-APR-02    7334.78 Vancouver            Tester

8 rows selected.

SQL>
SQL>
SQL> -- the top five salaries
SQL>
SQL> SELECT * FROM
  2  (SELECT id, first_name, salary, DENSE_RANK() OVER(ORDER BY salary desc) toprank
  3  FROM employee)
  4  WHERE toprank <= 5;

ID   FIRST_NAME               SALARY    TOPRANK
---- -------------------- ---------- ----------
08   James                   7334.78          1
07   David                   6334.78          2
06   Linda                   5334.78          3
05   Robert                  4334.78          4
04   Celia                   3334.78          5

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>

Re: I need a command to issue a command in sql [message #431345 is a reply to message #431336] Mon, 16 November 2009 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I hope this is not your homework...

It obviously is. What do you think HR schema and EMPLOYEE table is? A real project? What do you think top 5 query is?

In short, you are insincere and just want to post your query despite the forum guide.
Next time follow it and spare us your lame excuse.

And keep your lines in 80 characters, use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 16 November 2009 23:24]

Report message to a moderator

Re: I need a command to issue a command in sql [message #431348 is a reply to message #431345] Mon, 16 November 2009 23:38 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
Sorry for the same, Michel.
Re: I need a command to issue a command in sql [message #431357 is a reply to message #431307] Tue, 17 November 2009 01:45 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
as alternative
SELECT *
  FROM (SELECT   ID, first_name, salary
            FROM employee
        ORDER BY salary DESC)
 WHERE ROWNUM <= 5
Re: I need a command to issue a command in sql [message #431359 is a reply to message #431357] Tue, 17 November 2009 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide.

And what does this add to the very first answer?

Regards
Michel
Re: I need a command to issue a command in sql [message #431378 is a reply to message #431345] Tue, 17 November 2009 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you're being a wee bit harsh there Michel.

I don't think there are many posters here who can honestly say that they've never posted the answer to something that was homework.

Re: I need a command to issue a command in sql [message #431390 is a reply to message #431378] Tue, 17 November 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Tue, 17 November 2009 10:43
I think you're being a wee bit harsh there Michel.

I don't think there are many posters here who can honestly say that they've never posted the answer to something that was homework.

You are correct but starting with "I hope this is not a homework" for such a question on a demo schema got on my nerves; I would surely not post this remark (as I did not in several of his previous posts that were solutions on obvious homework questions) if there was not this introduction.

Regards
Michel
Re: I need a command to issue a command in sql [message #431405 is a reply to message #431390] Tue, 17 November 2009 04:28 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
Quote:

You are correct but starting with "I hope this is not a homework" for such a question on a demo schema got on my nerves; I would surely not post this remark (as I did not in several of his previous posts that were solutions on obvious homework questions) if there was not this introduction.


Michel, Kindly post on all my previous posts which were solutions on obvious homework questions - I did apologize for this post, and if I feel the same on the other posts would not hesitate but a blanket statement covering all posts is frankly uncalled for.
Re: I need a command to issue a command in sql [message #431481 is a reply to message #431378] Tue, 17 November 2009 10:40 Go to previous messageGo to next message
zonastrutting
Messages: 2
Registered: November 2009
Location: NIGERIA
Junior Member

sorry dude but it is no home work?
Re: I need a command to issue a command in sql [message #431484 is a reply to message #431481] Tue, 17 November 2009 10:44 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
zonastrutting wrote on Tue, 17 November 2009 16:40
sorry dude but it is no home work?
So are you saying that you are trying to solve a production issue? (using the emp table in hr?)
Re: I need a command to issue a command in sql [message #431486 is a reply to message #431484] Tue, 17 November 2009 10:48 Go to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Tue, 17 November 2009 17:44
zonastrutting wrote on Tue, 17 November 2009 16:40
sorry dude but it is no home work?
So are you saying that you are trying to solve a production issue? (using the emp table in hr?)

And when you say in your profile you are a student?

Regards
Michel

Previous Topic: Run Shell Script
Next Topic: Constraints
Goto Forum:
  


Current Time: Sat Oct 01 02:30:07 CDT 2016

Total time taken to generate the page: 0.16067 seconds