SQL Consider the database table structure shown in the Table: invoice_line items

Question

SQL

Consider the database table structure shown in the Table: invoice_line items Column Name invoice_id invoice sequence NUMBER Data Type NUMBER Nullable Default Data Constraint No No No No PK,FK PK FK NUMBER NUMBER(9,2) line item amt line_item description VARCHAR2(100 No Table: general ledger accounts Column Name account numbernberNlable Default Data Constraint number number No PK account_description varchar2(50) No UNIQUE Writo a SELECT statoment (compatiblo with an Oraclo RDBMS) that returns one row for oach gonoral ledgor aocount number and that contains two oolumns: the account description, the count of how many entrios in the invoico lino itoms table that have that account number. Be sure and provide column aliases where needed so that both columns of the result set have names

Explanation / Answer

Answer:

select gl.ACCOUNT_DESCRIPTION,nvl(temp_table.Invoice_Count,0)
from
general_ledger_accounts gl,
(select i.account_number,g.account_description, count(*) as Invoice_Count
from
general_ledger_accounts g,
invoice_line_items i
where
g.account_number=i.account_number
group by i.account_number,g.account_description) temp_table
where gl.ACCOUNT_NUMBER=temp_table.ACCOUNT_NUMBER(+)

Explanation:

First this sub query is used to get the count of each account number in table invoice_line_items.

select i.account_number,g.account_description, count(*) as Invoice_Count
from
general_ledger_accounts g,
invoice_line_items i
where
g.account_number=i.account_number
group by i.account_number,g.account_description

Then we are using left join of general_ledger_accounts table with above sub query to get account_description and count.

For more clarity also select gl.account_number in query. Because account_description is unique key and may contain multiple null entries for multiple account. So if we will not select this column in outer query, we may get multiple null entry in output as well(which is correct). But just to see clear picture select this column also if you want.

select gl.account_number,gl.ACCOUNT_DESCRIPTION,nvl(temp_table.Invoice_Count,0) from
general_ledger_accounts gl,
(select i.account_number,g.account_description, count(*) as Invoice_Count
from
general_ledger_accounts g,
invoice_line_items i
where
g.account_number=i.account_number
group by i.account_number,g.account_description) temp_table
where gl.ACCOUNT_NUMBER=temp_table.ACCOUNT_NUMBER(+)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.