Question

7.13. Consider the following View definition and update statement: CREATE VIEW TOPPRODUCTS(PRODNR,PRODNAME,QUANTITY) AS SELECT PRODNR, PRODNAME, AVAILABLE_QUANTITY FROM PRODUCT WHERE AVAILABLE_QUANTITY>100 WITH CHECK OPTION UPDATE TOPPRODUCTS SET QUANTITY=80 WHERE PRODNR=0153 What will be the result of this? a. The update can be successfully made but only the PRODUCT table will be updated. b. The update can be successfully made and both the View and PRODUCT table will be updated. c. The update will be halted because of the WITH CHECK OPTION. d. The update can be successfully made but only the View will be updated.

Answer 1

7.13.Qns: create view TOPPRODUCTS(PRODNR,PRODNAME,QUANTITY)as select PRODNR,PRODNAME,AVAILABLE_QUANTITY from PRODUCT where AVALIABLE_QUANTITY>100 with check option update TOPPRODUCTS set QUANTITY=80 where PRODNR=0153

Result:The update will be halted because of the WITH CHECK OPTION

exp:The following Transact-SQL statement attempting to update data by changing the QUANTITYto 80 of PRODNR=0153 will fail because quantity does not meet the criteria defined in the view. If you did not have WITH CHECK OPTION defined, the UPDATE statement would succeed, and a requery of the view would return no rows. To update the view we must use the following statement

UPDATE VIEW TOPPRODUCTS SET QUANTITY=80 WHERE PRODNR=0153

7.19 Qns:SELECT R1.SUPNAME,R1.SUPNR,COUNT(*) FROM PURCHASE_ORDER PO1,SUPPLIER R1 WHERE PO1.SUPNR=R1.SUPNR GROUP BY R1.SUPNR HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM PURCHASE_ORDER PO2,SUPPLIER R2 WHERE PO2.SUPNR=R2.SUPNR GROUP BY R2.SUPNR)

Result:The name,number and total outstanding orders of all suppliers that have outstanding orders

exp:Here in this statement the subquery returns all the suppliers group by SUPNR.from that result The ALL query returns all rows whose values in the column_name ie count are greater than or equal to all the values returned by using the condition specified in the having clause

The HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.

7.21 Qns:SELECT P1.PRODNR,P1.PRODNAME,S1.SUPNR,S1.PURCHASE_PRICE FROM PRODUCT P1,SUPPLIES S1 WHERE P1.PRODNR AND NOT EXISTS(SELECT * FROM PRODUCTS P2,SUPPLIES S2 WHERE P2.PRODNR=S2.PRODNR AND P1.PRODNR=P2.PRODNR AND S1.PURCHASE_PRICE>S2.PURCHASE_PRICE)

Ans:option C(2 and 3)

exp:here the sub query returns the supplier name who supplies the product for highest price is reteieved.Then the NOT EXISTS operator returns true if the underlying subquery returns no record. However, if a single record is matched by the inner subquery, the NOT EXISTS operator will return false, and the subquery execution can be stopped.The advantage of using the NOT EXISTS operators is that the inner subquery execution can be stopped as long as a matching record is found.