Automobile consumption loans audit
1 data validation:
Please check the loan certificate table exists in the loan amount is not equal to the certificate, the certificate has to recover the loan amount, loan balance verification and
Record. If there is, please will loan certificate number written on the answer sheet.

Select *
From loan document table
Where < > this document to recover a total loan amount loan + this document loans + verification amount

Loan certificate number: 321200300171893

2 data cleaning:
Please send the 1 found in the records of the receipt loans is amended as: the amount of borrowing - this document has to recover the loan amount - verification.

Update loan certificate list loans = set this document borrowing amount - this document has to recover the loan amount - Verification
Where loan certificate No. ='3212003001171893'

The 3 generation car loan scale
Please according to the type of loan (loan category beginning with F for car loans) to generate a new table: auto loans, and loans to 0 records removed.
Select *
Into car loan scale
From loan document table
Where (like'f%'loan categories) and (the document loans <>0)

4 bad car loan examination:
Description: five level loan classification is 3, 4, 5 of loans were non-performing.
4-1 please balance statistics of non-performing loan totals, and write the results on your answer sheet.

Select loan category name, sum (the document loans)
From car loan scale
Where (the five category loan classification ='3'or
    The five category loan classification ='4'or
     The five category loan classification ='5')
Group by loan category name

Loan category name         Total balances
The other car loans        13965515.08
Commercial hand car loans      3497931.61
Use a hand car loans      1988837.29

4-2 in order to determine the extension mechanism, please proceed to the bad loans auto loans made by institutional code grouping, retain the packet amount is greater than
1000000 yuan of the mechanism as the key extension of the next, and please on the balance of total descending order, write it on your answer sheet.

SELECT code, SUM (the document loans)
FROM car loan scale
WHERE (the five category loan classification ='3'OR
      The five category loan classification ='4'OR
      The five category loan classification ='5')
GRIUP BY HAVING sUM (encoding the documents loans) >1000000
ORDER BY 2 DESC

Organization code    Total balances
32010677705  2098472.07
32010811103  1823779.62
32040120301  1755909.32
32090811800  1403039.29
32060710508  1382588.05
32010710503  1279674.68
32040711208  1052201.47
32090910604  1007352.14

5 illegal car loan examination
"Auto loan management approach" provides auto loan period not longer than 5 years (including 5 years), please according to the above provisions, the term
Automobile loans statistics, and write the results on the answer sheet

SELECT sUM (the document loans)
FROM car loan scale
WHERE DATEDIFF (YEAR, borrowing date, expiration date) >5

Super total term auto loans826846.42