2 Replies Latest reply: Dec 18, 2013 12:15 AM by Ramin Hashimzadeh RSS

    writing querties

    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: writing querties
          rp0428

          WRONG FORUM!

           

          As the forum title says this forum is for SQL Developer questions and not for SQL or PL/SQL questions.

           

          When you have a SQL or PL/SQL question you need to post it in the proper forum

          https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

           

          The forums are NOT a coding service. Volunteers there are not going to do homework for you. If you have a question or issue with code that YOU have written post your query or code, the results it gives and explain what results you want from your query.

          • 2. Re: writing querties
            Ramin Hashimzadeh

            1)

                SELECT TRUNC(trade_time ), SUM(amount)

                FROM TRANSACTIONS

                WHERE amount > 0

                GROUP BY TRUNC(trade_time )

             

                  Even if client have no transactions

            2.1)

               SELECT ID, name, 0 balance

                from clients

                where id not in (select client_id from transactions)

                 

                 For all clients

            2.2)

            with client_bal as

            (select client_id, -sum(debet)+sum(kredit) balance

            from transactions

            group by client_id

            )

            select id, name, nvl(b.balance,0)

            from clients c

             

             

            left join client_bal b

            on b.client_id = c.id

             

            Note: All queries written on forum editor and do not tested. But logic 100% correct

             

             

            ----

            Ramin Hashimzade