4 Replies Latest reply: Dec 19, 2013 1:03 AM by SKP RSS

    Query writing

    DBA2011

      Q1: Given the following table of transactions in a cash register:
      id| trade_time | amount
      -- ---------------- ------
      1| 06/16/2013 11:43| 52.00
      2| 06/16/2013 11:50| 142.74
      3| 06/16/2013 14:21| -43.89
      4| 06/17/2013 12:50| 36.85
      5| 06/17/2013 13:40|-523.58
      please write a query which selects net daily turnovers (arithmetical sums of all transactions for a day) for all days when these turnovers are positive.


      Q2: Given the following two tables:

      clients (id (PK), name)
      transactions (id (PK), client_id, trans_date, debit, credit, comment)
      transactions.client_id references clients.id

      please write a query which selects the balance for ALL clients, even if they have no transactions. (Assume initial balances to be zeros).
      Sample table contents:

      clients:
      id name
      -- ------
      1 John
      2 Mary
      3 Alex

      transactions:
      id client_id trans_date debit credit comment
      -- --------- ---------- ------- ------- -----------------
      1 | 1 06/16/2013 NULL 1000.00 SALARY
      2 | 1 06/18/2013 516.43 0.00 MOB PHONE PAYMENT
      3 | 2 06/16/2013 NULL 1000.00 SALARY
      4 | 2 06/18/2013 200.00 NULL INTERNET PAYMENT
      5 | 2 06/18/2013 500.00 0.00 MOB PHONE PAYMENT

        • 1. Re: Query writing
          Frank Kulash

          Hi,

           

          When you have 2 separate questions, post 2 separate threads.

           

          Q1 sounds like a job for the aggregate SUM function.

          To get a separate total for each calendar day, you can use TRUNC (trade_time) in the GROUP BY clause.  (When dt is a DATE, TRUNC r(dt) returns the same time, namely midnight, on that calendar day, regardless of what the hours, minutes and seconds of dt are.)

          To see only the days with a positive sum, use a HAVING clause.

           

          Again, start a separate thread for Q2.  (You may want to wait until you have solved Q1.)

           

          Whenver you have a question, post your best attempt at solving the problem, along with a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and the results you want from that data.

          Explain, using specific examples, how you get those results from that data.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: Query writing
            Partha Sarathy S

            As Frank said, please post CREATE and INSERT table scripts, the required output and the solution which you have tried to solve your requirement. Please do mention your Oracle version. I am posting the query for the first requirement. Try it.

             

            WITH t1 AS

              (SELECT 1 ID,

                TO_DATE('06/16/2013 11:43','MM/DD/YYYY HH24:MI') TRADE_TIME,

                52.00 AMOUNT

              FROM DUAL

              UNION ALL

              SELECT 2, TO_DATE('06/16/2013 11:50','MM/DD/YYYY HH24:MI'), 142.74 FROM DUAL

              UNION ALL

              SELECT 3, TO_DATE('06/16/2013 14:21','MM/DD/YYYY HH24:MI'), -43.89 FROM DUAL

              UNION ALL

              SELECT 4, TO_DATE('06/17/2013 12:50','MM/DD/YYYY HH24:MI'), 36.85 FROM DUAL

              UNION ALL

              SELECT 5, TO_DATE('06/17/2013 13:40','MM/DD/YYYY HH24:MI'), -523.58 FROM DUAL

              )

            SELECT TO_CHAR(TRUNC(trade_time),'DD-MON-YYYY') DAY,

              SUM(amount)

            FROM t1

            WHERE SIGN(amount)=1

            GROUP BY TO_CHAR(TRUNC(trade_time),'DD-MON-YYYY');

            Do post your required scripts for the second question. Do not forget to mention the solution you could come up with. Then we will let you know on how to improve your solution.

            • 3. Re: Query writing
              Ramin Hashimzadeh

              Do not duplicate threads

              https://forums.oracle.com/thread/2614036

               

              ----

              Ramin Hashimzade

              • 4. Re: Query writing
                SKP

                Looks Like the OP usually ask the question and don't  check the thread for few days ,

                Coming back after few days they don't find the thread  which they started .

                So they started asking the same question again with a new thread.

                 

                For those -My advice is to

                 

                1. Login to the forum with your credential .

                2. Click on your profile.

                3.Click on the Activity Tab of your profile.

                Now you will find your thread easily.

                 

                Regards

                SKP