1 2 3 4 Previous Next 47 Replies Latest reply: Jan 21, 2013 3:40 AM by BluShadow Go to original post RSS
      • 15. Re: plzzzzzzzzzzzz help
        985803
        thanks for ur advice,, my ques is same CAN U HELP OUT??????????
        • 16. Re: plzzzzzzzzzzzz help
          JustinCave
          We can, sure.

          But, realistically, we're going to want you to show that you've put some effort into doing the work yourself before we're going to jump in. If you are completely unfamiliar with SQL, it makes no sense to go to an interview for a SQL developer position. If you are familiar with SQL, you should be able to make a good faith attempt at solving these problems yourself.

          Justin
          • 17. Re: plzzzzzzzzzzzz help
            6363
            982800 wrote:

            sample data is for first query is ...

            1)-
            create table emp ( empno num(5) ,ename char(20), deptno num(5));
            This bit doesn't work
            SQL> create table emp ( empno num(5) ,ename char(20), deptno num(5));
            create table emp ( empno num(5) ,ename char(20), deptno num(5))
                                        *
            ERROR at line 1:
            ORA-00907: missing right parenthesis
            Can you check.
            • 18. Re: plzzzzzzzzzzzz help
              sb92075
              982800 wrote:
              create table emp ( empno num(5) ,ename char(20), deptno num(5));
              you would be wise to avoid CHAR datatype & only ever use VARCHAR2; instead.
              • 19. Re: plzzzzzzzzzzzz help
                985803
                create table emp ( empno num(5) ,ename char(20), deptno num(5));
                insert into emp values(101 , 'ram' , 10)
                values(102, 'pawan' , 10)
                                    values(103, 'vikas' , 20)
                                    values(104, 'prabha' , 20)
                                    values(105, 'rohit' , 30)
                                    values(106, 'vishal' , 30);
                • 20. Re: plzzzzzzzzzzzz help
                  985803
                  AS I ALREADY MENTIONED I HAV NO IDEA IN THIS QUERY.............. I M NULL ABT IT THATS Y I M LOOKING FOR HELP
                  • 21. Re: plzzzzzzzzzzzz help
                    sb92075
                    982800 wrote:
                    AS I ALREADY MENTIONED I HAV NO IDEA IN THIS QUERY.............. I M NULL ABT IT THATS Y I M LOOKING FOR HELP
                    Seems the CapsLk key is stuck ON

                    consider to Read The Fine Manual for answers

                    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm#SQLRF30030
                    • 22. Re: plzzzzzzzzzzzz help
                      JustinCave
                      If you're completely unfamiliar with SQL and have no interest in learning SQL yourself, why are you interviewing for a SQL developer position? If we answer these questions and you somehow get hired at your next interview, are you going to be posting here every day hoping that we can do your job for you?

                      Justin
                      • 23. Re: plzzzzzzzzzzzz help
                        985803
                        THANKS, I THNK U ALSO DONT KNOW THE ANS. TK CR
                        • 24. Re: plzzzzzzzzzzzz help
                          985803
                          FOR UR BETTERMENT NOTEDOWN ANS FOR 3RD QUERY IS


                          create table emp ( empno num(5) ,ename char(20), deptno num(5));
                          insert into emp values(101 , 'ram' , 10)
                          values(102, 'pawan' , 10)
                                              values(103, 'vikas' , 20)
                                              values(104, 'prabha' , 20)
                                              values(105, 'rohit' , 30)
                                              values(106, 'vishal' , 30);
                          select * from emp WHERE MONOTONIC() IN (1,3,6);
                          • 25. Re: plzzzzzzzzzzzz help
                            Frank Kulash
                            Hi,
                            982800 wrote:
                            Thanks for your response...

                            * i m looking for seperate ans for each query...

                            sample data is for first query is ...

                            1)-
                            create table emp ( empno num(5) ,ename char(20), deptno num(5));
                            num is not a valid datatype in Oracle. I think you meant NUMBER.
                            CHAR is a valid datatype, but you'd be better off if it wasn't. Never use CHAR in Oracle; use VARCHAR2 instead.
                            insert into emp values(101 , 'ram' , 10)
                            values(102, 'pawan' , 10)
                                                values(103, 'vikas' , 20)
                                                values(104, 'prabha' , 20)
                                                values(105, 'rohit' , 30)
                                                values(106, 'vishal' , 30);
                            I get the error "ORA-00933: SQL command not properly ended" when I try that. The whole point of posting sample data is to let the people who want to help you re-create the problem and test their ideas. Posting INSERT statements that don't work isn't much help.

                            If you're applying for a SQL job, being able to write an INSERT statement is much more important than any of the questions you posted. Practice that first.
                            require output:

                            deptid employees
                            10 ram,pawan
                            20 vikas,prabha
                            30 rohit,vishal
                            Okay, it still looks like a job for string aggregation. Have you read the Oracle-Base page yet? What have you tried? LISTAGG (one of the many technique on that page) is very useful, but it only works in Oracle 11.2.

                            You're asking these questions because you think you'll be asked questions like these on an interview, right? Just getting the answers won't help you much. If employers do ask you this question, you're not going to tell them to look at this page. No, you want to know the principles involved in solving questions like this, and to be able to solve them yourself. If you hope to get a job where where you work on problems like this all day long, spend some time working on problems like this now.
                            2)

                            create table emp(empno num(3), ename char(20), sal num(8));
                            insert into emp values(101,xxx,3400)
                            values(102,yyy,5500)
                            values(101,zzz,1400)
                            values(101,aaa,3700)
                            values(101,bbb,3900)
                            values(101,ccc,4400);
                            You can probably post one set of sample data that will serve for all these questions. Just make sure you post something we can actually use.
                            It still looks like scott.emp is good enough for all these questions (except question 4, below: the dual table is all you need for that question).
                            The output will be as such:
                            Name Salary Running Total Salary
                            xxx 3400 3400
                            yyy 5500 8900
                            zzz 1400 10300
                            ......... so on
                            That still looks like a job for tha abnalytic SUM funtion. What have you tried?
                            How are the rows ordered? Why do you want that output, and not
                            NAME     SALARY     RUNNING_TOTAL_SALARY
                            ====     ======     ====================
                            aaa     3700      3700
                            bbb     3900      7600
                            ccc     4400     12000 
                            ...
                            or
                            NAME     SALARY     RUNNING_TOTAL_SALARY
                            ====     ======     ====================
                            yyy     5500      5500
                            ccc     4400      9900
                            bbb     3900     13800
                            ...
                            ? There is a very important concept behind my questions.
                            3) looking 1st 3rd and 6th employee from list
                            This still looks like a job for the ROW_NUMBER fucntion. What have you tried?
                            Before you said you wanted to "3) Display name of first employee, third employee, and so forth." How does "so foth" mean or include 6th? Explain what you want, and post the exact results you want from the given sample data.

                            if you have an arbitrary set of tqarget numbers, then MOD (which I suggested in my last message) probably won't help any. You're more likely to need a WHERE clause like
                            WHERE   r_num   IN (1, 3, 6)
                            4) looking for all dates of every friday in current year
                            You can do that using a Counter Table , which is usually not a table at all, but rather a result set that acts like a table.
                            SELECT  LEVEL  AS n
                            FROM    dual
                            CONNECT BY  LEVEL < 10
                            ;
                            generates the numbers 1, 2, 3, ..., 10.
                            You can use the same approach to get the numbers 1 through 52, or 1 through 365.
                            Do you know how to find the first Friday in the year? Check out the TRUNC and NEXT_DAY functions.
                            Look up Date Arithmetic in the <a href+"http://docs.oracle.com/cd/E11882_01/server.112/e26088/index.htm">SQL Language manual</a> for a neat way of manipulating dates in Oracle.
                            If dt is a DATE, then
                            dt + 7
                            is the DATE exactly one week later than dt, and
                            dt + (7 * n)
                            is that DATE exactly n weeks later. Once you find the first Friday in this year (or, alternatively, the last Friday of last year), then getting all the Fridays in this year is just a matter of generating all the needed consecutive values of n, and adding them to the original Friday DATE.
                            • 26. Re: plzzzzzzzzzzzz help
                              985803
                              THANKS FOR HINT... I M WORKING ON IT...
                              • 27. Re: plzzzzzzzzzzzz help
                                985803
                                for first que i m tring listagg
                                for second i hav no idea , looking for ur help
                                for 3rd ans is select * from emp WHERE MONOTONIC() IN (1,3,6);

                                for 4th i m tring with some date funtion

                                so try to provide me ans of 2nd first.

                                thanks
                                • 28. Re: plzzzzzzzzzzzz help
                                  JustinCave
                                  982800 wrote:
                                  for first que i m tring listagg
                                  OK. Did you get the results you wanted? If not, what, exactly, did you try? What results did you get? How did you try to refine your results?
                                  for second i hav no idea , looking for ur help
                                  Frank already suggested using the analytic SUM function. And asked a number of questions about how to determine what results you want (i.e. what order do you want to sum the salaries)? Have you tried using the analytic SUM function?
                                  for 3rd ans is select * from emp WHERE MONOTONIC() IN (1,3,6);
                                  MONOTONIC is not a valid function name in Oracle. So this is not a valid query. As has been suggested, you probably want to use one of the analytic functions RANK, DENSE_RANK, or ROW_NUMBER depending on how you want to define something like the "first row" or the "sixth row" if ties are possible.
                                  for 4th i m tring with some date funtion
                                  What date function? What are you trying? Are you getting the answer you want?

                                  Justin
                                  • 29. Re: plzzzzzzzzzzzz help
                                    985803
                                    1)- SELECT deptno, string_agg(ename) AS employees
                                    FROM emp
                                    GROUP BY deptno;
                                    quit;
                                    ERROR: Function STRING_AGG could not be located.
                                    getting error

                                    2) i m nt getting ur mean abt analytic sum fuction about... plz clear it
                                    i have only 2 colum month and sales
                                    i m looking for total sale colum also in output
                                    imonth sales total sale
                                    JAN 800 800
                                    FEB 300 1100
                                    MAR 900 2000
                                    APR 400 2400
                                    MAY 700 3100
                                    JUN 1300 4400

                                    3) i m getting my required output with the help of monotonic() , for row_number i m getting same error
                                    ERROR: Function row_number() could not be located.