[PDF] Advanced SQL Advanced SQL. Practice Exercises. 5.

View - Download Advanced SQL

Previous PDF

Next PDF

CHAPTER5

Advanced SQL

Practice Exercises

5.1Describe the circumstances in which you would choose to use embed-

dedSQLrather thanSQLalone or only a general-purpose programming language. Answer:Writing queries inSQLis typically much easier than coding such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface cannot be done from withinSQL. Under circumstances in which we want the best of both worlds,we can choose embeddedSQLor dynamicSQL, rather than usingSQLalone or using only a general-purpose programming language. EmbeddedSQLhas the advantage of programs being less complicated since it avoids the clutter of theODBCorJDBCfunction calls, but requires a specialized preprocessor.

5.2Write a Java function usingJDBCmetadata features that takes aResult-

appropriate names as column headings.

Answer:

public classResultSetTableimplementsTabelModel{

ResultSet result;

ResultSetMetaData metadata;

int num cols; ResultSetTable(ResultSet result)throwsSQLException{ this.result = result; metadata = result.getMetaData(); num cols = metadata.getColumnCount(); for(inti = 1; i<=num cols; i++){ System.out.print(metadata.getColumnName(i) + `` ``); 1

2Chapter 5Advanced SQL

System.out.println();

while(result.next()){ for(inti = 1; i<=num cols; i++){

System.out.print(result.getString(

metadata.getColumnName(i) + `` ``));

System.out.println();

5.3Write a Java function usingJDBCmetadata features that prints a list of

all relations in the database, displaying for each relationthe names and types of its attributes.

Answer:

DatabaseMetaData dbmd = conn.getMetaData();

ResultSet rs = dbmd.getTables();

while(rs.next()){

System.out.println(rs.getString(``TABLE

NAME'');

ResultSet rs1 = dbmd.getColumns(null, ``schema-name'', rs.getString(``TABLE

NAME''), ``%'');

while(rs1.next()){

NAME''),

rs.getString(``TYPE

NAME'');

5.4Show how to enforce the constraint"an instructor cannot teach in two

different classrooms in a semester in the same time slot."using a trigger (remember that the constraint can be violated by changes to theteaches relation as well as to thesectionrelation).

Answer:FILL

5.5Write triggers to enforce the referential integrity constraint fromsection

totime slot, on updates tosection, andtimeslot. Note that the ones we wrote in Figure 5.8 do not cover theupdateoperation.

Answer:FILL

5.6To maintain thetot

credattribute of thestudentrelation, carry out the following: a. Modify the trigger on updates oftakes, to handle all updates that can affect the value oftot cred. b. Write a trigger to handle inserts to thetakesrelation.

Exercises3

c. Under what assumptions is it reasonable not to create triggers on thecourserelation?

Answer:FILL

cust as follows: create viewbranch custas selectbranch name, customername fromdepositor, account wheredepositor.account number=account.accountnumber Suppose that the view ismaterialized; that is, the view is computed and stored. Write triggers tomaintainthe view, that is, to keep it up-to-date on insertions to and deletions fromdepositororaccount. Do not bother about updates. Answer:For inserting into the materialized viewbranchcustwe must set a database trigger on an insert intodepositorandaccount. We assume that the database system usesimmediatebinding for rule execution. Fur- ther, assume that the current version of a relation is denoted by the relation name itself, while the set of newly inserted tuplesis denoted by qualifying the relation name with the prefix -inserted. The active rules for this insertion are given below - define triggerinsert intobranchcustviadepositor after insert ondepositor referencing new table asinsertedfor each statement insert intobranch cust selectbranch name, customername frominserted, account whereinserted.account number=account.accountnumber define triggerinsert intobranchcustviaaccount after insert onaccount referencing new table asinsertedfor each statement insert intobranch cust selectbranch name, customername fromdepositor,inserted wheredepositor.account number=inserted.accountnumber Note that if the execution binding wasdeferred(instead of immediate), then the result of the join of the set of new tuples ofaccountwith the set of new tuples ofdepositorwould have been inserted bybothactive rules, leading to duplication of the corresponding tuples inbranch cust.

The deletion of a tuple frombranch

custis similar to insertion, exce pt that a deletion from eitherdepositororaccountwill cause the natural join of theserelationstohave alessernumber of tuples.Wedenotethe newly

4Chapter 5Advanced SQL

deleted set of tuples by qualifying the relation name with the keyword deleted. define triggerdelete frombranchcustviadepositor after delete ondepositor referencing old table asdeletedfor each statement delete frombranch cust selectbranch name, customername fromdeleted,account wheredeleted.account number=account.accountnumber define triggerdelete frombranchcustviaaccount after delete onaccount referencing old table asdeletedfor each statement delete frombranch cust selectbranch name, customername fromdepositor,deleted wheredepositor.account number=deleted.accountnumber

5.8Consider the bank database of Figure 5.25. Write anSQLtrigger to carry

out the following action: Ondeleteof an account, for each owner of the account, check if the owner has any remaining accounts, and if she does not, delete her from thedepositorrelation.

Answer:

create triggercheck-delete-triggerafter delete onaccount referencing old row asorow for each row delete fromdepositor wheredepositor.customer namenot in (selectcustomer namefromdepositor whereaccount number<>orow.accountnumber) end should have only onegroup byclause.

Answer:

groupby rollup(a),rollup(b),rollup(c),rollup(d)

5.10Given a relationS(student,subject,marks), write a query to find the top

nstudents by total marks, by using ranking. Answer:Weassumethatmultiplestudentsdonot havethesamemarks since otherwise the question is not deterministic; the query below deter- ministically returns all students with the same marks as thenstudent, so it may return more thannstudents.

Exercises5

selectstudent,sum(marks)astotal, rank()over(order by(total)desc)astrank fromS groupbystudent

5.11Consider thesalesrelation from Section 5.6. Write anSQLquery to com-

Do not use thecubeconstruct.

Answer:

(selectcolor,size,sum(number) fromsales groupbycolor,size union (selectcolor, "all",sum(number) fromsales groupbycolor union (select"all",size,sum(number) fromsales groupbysize union (select"all",size,sum(number) fromsales groupbysize union (select"all", "all",sum(number) fromsalesquotesdbs_dbs14.pdfusesText_20

[PDF] advanced sql books free download

[PDF] advanced sql commands with examples pdf

[PDF] advanced sql injection cheat sheet

[PDF] advanced sql join examples

[PDF] advanced sql notes pdf

[PDF] advanced sql pdf

[PDF] advanced sql queries

[PDF] advanced sql queries book

[PDF] advanced sql queries examples in mysql

[PDF] advanced sql queries examples in sql server

[PDF] advanced sql queries interview questions

[PDF] advanced sql queries interview questions and answers

[PDF] advanced sql queries practice online

[PDF] advanced sql queries questions

[PDF] advanced sql queries tutorial pdf