Question

sql question

5. Find out who keep(s) the highest balance among all clients associated with bookkeeper 24 (Hint: you may recall one of the Lab 4 questions). Save this query as a view "Q5". List all rows in the view Q5. 6. a. Update balance value to 525.00 for client Afton Mills b. Does it change the output of query in question 5? And why? (You can use the view Q5 to double-check. But you don't have to include it in your submission. Simply typing up your answer for question 6b is fine.) c. Display balance value of client Afton Mills d. Restore original value using a single keyword command e. Display balance value of client Afton Mills again 7. Using CLIENT table, calculate total balance associated with each bookkeeper 8. Using "NATURAL JOIN" keyword, list each client's name and the corresponding bookkeeper number and name, in descending order by the bookkeeper number (you may be surprised that you don't have to specify table name for bookkeeper number attribute although it appears in both tables. Think about why. You don't have to put the reason in your submission).

Answer 1

5.

CREATE VIEW [Q5] AS
SELECT * FROM CLIENT

WHERE BKP_NUM=24 AND

CLI_BALANCE = (SELECT MAX(CLI_BALANCE) FROM CLIENT) ;

EXPLANATION:-

Here, we are creating view with name Q5.

Here we are using SELECT * statement to fetch the data with all the columns from the table CLIENT.

For filtering this data we used WHERE clause in which we checked for BKP_NUM=24 and whether the balance of client is equal to the highest balance which is fetched using MAX() aggregate function.

This query can give multiple records which are having bkp_num as 24v and maximum balance.

6.

a)UPDATE CLIENT

SET CLI_BALANCE = 525

WHERE CLI_NAME='Afton Mills' ;

EXPLANATION:-

Here we used UPDATE to update CLI_BALANCE which are having CLI_NAME as Afton Mills by using WHERE Clause.

b)No , it won't change the result of Q5 as the record with CLI_ NAME as Afton Mills is associated with BKP_NUM 22 ,while in 5 it was for BKP_NUM 24 .

c)SELECT CLI_BALANCE FROM CLIENT

WHERE CLI_NAME ='Afton Mills' ;

EXPLANATION:-

It uses select to fetch data of balance from the CLIENT table and  WHERE clause is used to fulter the data.

d) ROLLBACK

EXPLANATION:-

Any uncommitted change pf UPDATE query can be restored to earlier state by using ROLLBACK command.'

e)

SELECT CLI_BALANCE FROM CLIENT

WHERE CLI_NAME ='Afton Mills' ;

7. SELECT BKP_NUM,SUM(CLI_BALANCE) AS Total_Balance FROM

CLIENT GROUP BY BKP_NUM ;

EXPLANATION:-

Above query uses SELECT statement along with SUM() aggregate function on CLI_BALANCE to get sum for each BKP_NUM.

Here, GROUP BY is used along with aggregate function SUM() to get Sum for balance for each group divided on the basis on BKP_NUM.

8. SELECT CLI_NAME ,BKP_NUM ,CONCAT(BKP_FNAME ,' ',BKP_LNAME) AS BKP_NAME

FROM CLIENT NATURAL JOIN BOOKKEEPER

ORDER BY BKP_NUM DESC;

EXPLANATION:-

Here, again we are using SELECT to fetch the data , then for getting full name we used CONCAT() function to combine first and last name and name it as BKP_NAME using AS keyword.

Then , for natural join we used NATURAL JOIN keyword between two table .

Then for ordering with BKP_NUM we used ORDER BY clause along with DESC keyword to get ordering in descending order.