Thread: SQL Query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    SQL Query


    Q1. SELECT TOP 1 salary
    FROM (SELECT DISTINCT TOP 6 salary FROM employee
    ORDER BY salary DESC) a
    ORDER BY salary



    what does this ' a ' means here. ????



    Q2. How to auto number the rows in a table in Oracle SQL*9 ?



    Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?



    Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?

    Hint : Employee(empid,empname,salary) Table

    [IMG]file:///C:/Users/DELL/Desktop/image.png[/IMG]
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    858
    Rep Power
    388

    Thumbs down Are we lazy or what?


    Originally Posted by vijeshjain
    Q1. SELECT TOP 1 salary
    FROM (SELECT DISTINCT TOP 6 salary FROM employee
    ORDER BY salary DESC) a
    ORDER BY salary

    what does this ' a ' means here. ????

    Q2. How to auto number the rows in a table in Oracle SQL*9 ?

    Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?

    Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?

    Hint : Employee(empid,empname,salary) Table

    [IMG]file:///C:/Users/DELL/Desktop/image.png[/IMG]
    Perhaps you should benefit more if you did your own homework.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    Perhaps you should benefit more if you did your own homework.

    Im a beginner buddy....
    learning..... nevertheless plz answer the 3rd question
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Following LKBrwn_DBA advice; but offering some hints:

    Q1. Research table aliases.
    Q2. Research Oracle sequences.
    Q3. It sort of depends. You could write a complicated query to return one value
    or write a simple query and just fetch the first three records.
    Q4. (listed as Q3). Research the case statement.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by clivew
    Following LKBrwn_DBA advice; but offering some hints:

    Q1. Research table aliases.
    Q2. Research Oracle sequences.
    Q3. It sort of depends. You could write a complicated query to return one value
    or write a simple query and just fetch the first three records.
    Q4. (listed as Q3). Research the case statement.

    Thanks guys..!!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than 5000 than the grade = GOLD otherwise SILVER ?


    Guys.... check dis out, i have fired these QUERIES , but getting an error
    used both (case & if-then-else) , why is it so???

    N.B : I am using Oracle SQL*Plus Version : 9.2.0.1.0



    SQL> select * from empdb;

    EMPID EMPNAME SAL DESGN DEPTID CITYID
    ---------- -------------------- ---------- -------------------- ---------- ----------
    1 abc 5000 manager 1 1
    2 xyz 2000 emp 2 2
    3 john 3000 manager 2 2
    4 avi 2000 emp 1 1
    5 roy 4000 manager 5 2
    6 micheal 7000 emp 3 1

    6 rows selected.

    SQL>select empid,sal,
    case sal
    when sal >=5000 then 'gold'
    else 'silver'
    end
    "grade"
    from empdb;

    SQL> /
    when sal >=5000 then 'gold'
    *
    ERROR at line 3:
    ORA-00905: missing keyword


    SQL> select empid,empname,sal
    2 if sal >=5000 then
    3 'gold'
    4 else
    5 'silver'
    6 end if
    7 "grade"
    8 from empdb;
    if sal >=5000 then
    *
    ERROR at line 2:
    ORA-00923: FROM keyword not found where expected
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by vijeshjain
    Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than 5000 than the grade = GOLD otherwise SILVER ?


    Guys.... check dis out, i have fired these QUERIES , but getting an error
    used both (case & if-then-else) , why is it so???

    N.B : I am using Oracle SQL*Plus Version : 9.2.0.1.0



    SQL> select * from empdb;

    EMPID EMPNAME SAL DESGN DEPTID CITYID
    ---------- -------------------- ---------- -------------------- ---------- ----------
    1 abc 5000 manager 1 1
    2 xyz 2000 emp 2 2
    3 john 3000 manager 2 2
    4 avi 2000 emp 1 1
    5 roy 4000 manager 5 2
    6 micheal 7000 emp 3 1

    6 rows selected.

    SQL>select empid,sal,
    case sal
    when sal >=5000 then 'gold'
    else 'silver'
    end
    "grade"
    from empdb;

    SQL> /
    when sal >=5000 then 'gold'
    *
    ERROR at line 3:
    ORA-00905: missing keyword


    SQL> select empid,empname,sal
    2 if sal >=5000 then
    3 'gold'
    4 else
    5 'silver'
    6 end if
    7 "grade"
    8 from empdb;
    if sal >=5000 then
    *
    ERROR at line 2:
    ORA-00923: FROM keyword not found where expected
    whereas in CASE , its working if the condition is a string
    e.g. case desgn
    when 'manager' then 'gold'
    else 'silver'
    end
    "grade"
  14. #8
  15. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    858
    Rep Power
    388

    Thumbs down learn by doing


    Originally Posted by vijeshjain
    Im a beginner buddy....
    learning..... nevertheless plz answer the 3rd question
    In order to learn you need to do your own homework.


    According to this proverb, we will help if you do your part:

    "Give a man a fish and he won't starve for a day. Teach a man how to fish and he won't starve for his entire life."
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by vijeshjain
    whereas in CASE , its working if the condition is a string
    e.g. case desgn
    when 'manager' then 'gold'
    else 'silver'
    end
    "grade"
    try
    SQL Code:
    SELECT empid,sal,
    (CASE sal
    WHEN sal >=5000 THEN 'gold'
    ELSE 'silver'
    END) grade
    FROM empdb
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by clivew
    try
    SQL Code:
    SELECT empid,sal,
    (CASE sal
    WHEN sal >=5000 THEN 'gold'
    ELSE 'silver'
    END) grade
    FROM empdb

    Thanks clivew!!
    I tried ur suggestion....
    but got ERROR!!!

    SQL> select empid,sal,
    2 (case sal
    3 when sal>=5000 then 'gold'
    4 else 'silver'
    5 end)
    6 "grade"
    7 from empdb;
    when sal>=5000 then 'gold'
    *
    ERROR at line 3:
    ORA-00905: missing keyword


    error is @ line 3.. but why not getting it..
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I have not checked this as I do not have my Oracle test server running right now;
    but I think the problem is using sal twice.
    Try this variation.

    SQL Code:
        SELECT empid,sal,
        (CASE
        WHEN sal >=5000 THEN 'gold'
        ELSE 'silver'
        END) grade
        FROM empdb

    or
    SQL Code:
        SELECT empid,sal,
        (CASE sal
        WHEN >=5000 THEN 'gold'
        ELSE 'silver'
        END) grade
        FROM empdb

    and I strongly recommend against surrounding grade with quotes.
    This makes it case sensitive. In a simple SELECT like this it is probably immaterial
    but if you start using this to create table or view field names you are inviting trouble
    down the road.

    Clive
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by clivew
    I have not checked this as I do not have my Oracle test server running right now;
    but I think the problem is using sal twice.
    Try this variation.

    SQL Code:
        SELECT empid,sal,
        (CASE
        WHEN sal >=5000 THEN 'gold'
        ELSE 'silver'
        END) grade
        FROM empdb

    or
    SQL Code:
        SELECT empid,sal,
        (CASE sal
        WHEN >=5000 THEN 'gold'
        ELSE 'silver'
        END) grade
        FROM empdb

    and I strongly recommend against surrounding grade with quotes.
    This makes it case sensitive. In a simple SELECT like this it is probably immaterial
    but if you start using this to create table or view field names you are inviting trouble
    down the road.

    Clive
    Hi Clive ,
    Tried ur code :
    GOT THIS :
    [U]

    SQL> ed
    Wrote file afiedt.buf

    1 SELECT empid,sal,
    2 (CASE sal
    3 WHEN >=5000 THEN 'gold'
    4 ELSE 'silver'
    5 END) "grade"
    6* FROM empdb

    SQL> /
    WHEN >=5000 THEN 'gold'
    *
    ERROR at line 3:
    ORA-00936: missing expression
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    What about my first option?
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by vijeshjain
    Hi Clive ,
    Tried ur code :
    GOT THIS :
    [U]

    SQL> ed
    Wrote file afiedt.buf

    1 SELECT empid,sal,
    2 (CASE
    3 WHEN >=5000 THEN 'gold'
    4 ELSE 'silver'
    5 END) "grade"
    6* FROM empdb


    Hey Clive ,
    ur 1st option worked!!!!

    double-checked it, its working....

    Thanks a TON!!!

    the prob. was using the 'sal' twice ....

IMN logo majestic logo threadwatch logo seochat tools logo