/* This set of test codes show creating tables, 
populating tables, using joins, creating views 
and procedures, and using transactions. Copy 
and paste the whole document to your Query Analyzer */

/* Important Note: If you make any mistake 
   and mess up the whole thing, just go 
   back to the top and do it all over again. */

/* Highlight and execute all of the
   following drop object commands.
   Don't worry if errors are displayed
   in the result window. This is just
   to make sure there are no existing 
   objects with the same name. */

drop procedure prUpdateAll

drop procedure prUpdateBoth

drop procedure prUpdatePosition

drop procedure prUpdateCity

drop procedure prShowOne

drop view vwShowAll

drop table testcommit3

drop table testcommit1

drop table testcommit

/* Create three tables that can be 
   joined together using foreign
   keys that references the other tables. 

   Highlight all the three create tables commands
   and execute them together.  */

create table testcommit ( cRowId char (3) constraint pkcRowId primary key, cName char (30), 
                          cEmail char (20) )

create table testcommit1 ( cRefId char (3)constraint pkcRefId primary key, cCity char (20),
                           cZip char (20), cRef char (3) constraint fkcRef foreign
                           key references testcommit (cRowId) )

create table testcommit3 ( cProfId char (3), cPosition char (20), cCompany char (30), 
                           mSalary money, cCompId char (3) constraint fkRefId foreign key
                           references testcommit1 (cRefId) )

/* Insert values to the first table. 
   Highlight all the insert commands and
   execute them together. */

insert testcommit values ( '001', 'Aureo P. Castro', 'apcastro@email.com' )
insert testcommit values ( '003', 'Juan A. Cruz', 'jacruz@email.com' )
insert testcommit values ( '006', 'Maria J. Reyes', 'mjreyes@email.com' )

/*  Now, check to see if all values
    were inputted. Highlight the next
    select command and execute it. */

select * from testcommit

/*  Put values to the second table. 
    Highlight all the following 
    insert commands and 
    execute them together. */

insert testcommit1 values ( '002', 'Pasig', '838367', '001' )
insert testcommit1 values ( '004', 'Pasay', '847463', '003' )
insert testcommit1 values ( '007', 'Manila', '838367', '006' )

/*  Check to see if all data have 
    been accepted. Highlight the following 
    select command and execute it. */

select * from testcommit1

/*  Now populate your third table. 
    Highlight all the following 
    insert commands and 
    execute them together. */

insert testcommit3 values ( '010', 'Programmer', 'ABC', 3000, '002' )
insert testcommit3 values ( '012', 'Analyst', 'DEF', 8000, '004' )
insert testcommit3 values ( '014', 'Clerk', 'XYZ', 7000, '007' )

/*  Check if all values have been
    recorded. Highlight the next 
    select command and execute it. */

select * from testcommit3

/*  Now, let's create a view that will
    show everything in our three tables
    using joins. We use the foreign 
    keys from the second and third
    tables to join the three tables
    together. Highlight the following
    create command and execute it. */

create view vwShowAll as
             select "Name" = cName, "Email Address" = cEmail, "City" = cCity, 
             "Zip Code" = cZip, "Position" = cPosition, "Company" = cCompany, 
             "Salary" = mSalary  
             from testcommit join testcommit1 on cRowId = cRef 
             join testcommit3 on cRefId = cCompId

/*  Now, test the view if it is working. 
    Highlight the following select command 
    and execute it. */

select * from vwShowAll

/*  Next, we create a procedure to output
    one row based on a value supplied 
    by the user. Highlight the following
    create prcedure command and execute it. */

create procedure prShowOne @cRowId char (3) output as
             select "Name" = cName, "Email Address" = cEmail, "City" = cCity, 
             "Zip Code" = cZip, "Position" = cPosition, "Company" = cCompany, 
             "Salary" = mSalary 
             from testcommit join testcommit1 on cRowId = cRef 
             join testcommit3 on cRefId = cCompId
             where cRowId = @cRowId

/*  Let's test the procedure if
    it is working. Highlight the following
    exec command and execute it. */

exec prShowOne '001'

/*  That was a nice procedure though I think 
    we can still improve it. Let's test first 
    if a row with the same row id exist and act 
    accordingly based on the result. If there is
    a row with that id, we show it. if there is 
    none, we inform the user of that fact. 
    Highlight the below alter procedure command 
    and execute it. */

alter procedure prShowOne @cRowId char (3) output as
begin
       if exists ( select * from testcommit where
       cRowId = @cRowId )
       begin
       print "Details for Data " + @cRowId
       print " "  
       select "Name" = cName, "Email Address" = cEmail, "City" = cCity, 
             "Zip Code" = cZip, "Position" = cPosition, "Company" = cCompany,
             "Salary" = mSalary
             from testcommit join testcommit1 on cRowId = cRef 
             join testcommit3 on cRefId = cCompId
             where cRowId = @cRowId
       end
       else
             print "No Records for Object " + @cRowId
end

/*  Now, let's test the new procedure using a 
    valid value. Highlight and execute the
    next exec procedure command. */

exec prShowOne '001'

/*  Now, let's test the procedure using 
    invalid data. Highlight and execute 
    the next command which supplies a row 
    id that is not present in the data base. */

exec prShowOne '002'

/*  Now, let's create a procedure that will 
    update the city column in our second table. 
    this procedure will return a 0 value if it 
    was successful and return a 1 value if not. 
    Take note that we did not want this procedure 
    to output any user messages since we plan to 
    use it as a nested procedure in the other procedures 
    we plan to make. Highlight the next procedure and execute it. */

create procedure prUpdateCity @cRowId char (3),
                              @cCity char (20) as
begin
       if exists ( select * from testcommit where
       cRowId = @cRowId )
       begin
            update testcommit1 set cCity = @cCity 
            from testcommit join testcommit1 on cRowId = cRef  
            where cRowId = @cRowId
            return 0
       end
       else
            return 1
end

/*  Okay, we now test if the update will work. 
    Let's check first the contents of the rows 
    corresponding to row id of '001'. Highlight 
    and execute the next command. Take careful 
    note of the value in the city column.  */

exec prShowOne '001'

/*  Now, update the row '001' and change the 
    city column in the second table with 
    another value. Highlight the next command 
    and execute it. */

exec prUpdateCity '001', 'Mandaluyong'

/*  Okay. Now let's see if the update had been
    made on the data base. Highlight and execute 
    the next command to see if the city for 
    row id of '001' had been changed. */

exec prShowOne '001'

/*   Now, we create another procedure to update 
     the position column in our third table. Just 
     like the last procedure we made, we don't want 
     this procedure to send any message to the user since 
     we will be using it as a nested procedure later on. 
     Highlight and execute the next create procedure command. */

create procedure prUpdatePosition @cRowId char (3) output,
                                 @cPosition char (20) as
begin
       if exists ( select * from testcommit where
       cRowId = @cRowId )
       begin
       update testcommit3 set cPosition = @cPosition 
       from testcommit join testcommit1 on cRowId = cRef join 
             testcommit3 on cRefId = cCompId
             where cRowId = @cRowId
       return 0
       end
       else
       return 1
end

/*   Now let's see if the procedure works. First, 
     let's take a peek at the value of the position 
     column for row id '003'. Highlight and execute 
     the following procedure. */

exec prShowOne '003'

/*   Okay, so now we change the value in the position 
     column of row id '003'. Highlight and execute 
     the next update procedure giving 'Developer' as 
     the new value of the position column. */

exec prUpdatePosition '003', 'Developer'

/*   Now, we check if the row had been updated. 
     Highlight and execute the next command. 
     Take careful note of the new value of 
     the position column. */

exec prShowOne '003'

/*   Next, we create a procedure that would update 
     both the city on the second table and the position 
     on the third table using a single procedure. We 
     will be nesting the two update procedures we 
     created earlier under this new procedure. Highlight 
     and execute the procedure next. */ 

create procedure prUpdateBoth @cRowId char (3) output,
                              @cCity char (20),
                              @cPosition char (20) as                             
begin
       declare @iReturn int
       exec @iReturn = prUpdateCity @cRowId, @cCity
       if @iReturn <> 0 
       print "Error Updating City for Object " + @cRowId
       else
       print "City Updated for Object " + @cRowId
       exec @iReturn = prUpdatePosition @cRowId, @cPosition
       if @iReturn <> 0  
       print "Error Updating Position for Object " + @cRowId
       else
       print "Position Updated for Object " + @cRowId    
end

/*   Okay, let's test if the new procedure can change the 
     contents of the two tables. First, we check what the 
     existing city and position values of row '003'. 
     Highlight and execute the following command. */ 
      
exec prShowOne '003'

/*   Now, we execute the procedure. Highlight and 
     execute the next exec command. */

exec prUpdateBoth '003', 'Quezon City', 'Clerk'

/*   Okay, let's check if our tables have been updated. 
     Highlight and execute the next exec command. */

exec prShowOne '003'

/*   Now, let's see if the procedure will work if 
     we give it erroneous data. Highlight and execute 
     the next command which requests an update for a 
     non-existing row id of '010'. */

exec prUpdateBoth '010', 'Quezon City', 'Clerk'

/*   Did you see the error messages? Okay, so our 
     procedure is working. Finally, let's create 
     a procedure that will update the position, 
     company, and salary values of our third table. 
     The business rule that we need to implement is 
     no salary will ever be greater than 10,000. 
     Management will approve of any salary increase 
     until as long as no salary will ever be greater 
     than 10,000. The procedure will roll back any 
     transaction that will cause this limit to be 
     breached. Okay, highlight and execute the next command */

create procedure prUpdateAll @cRowId char (3) output,
                             @cPosition char (20),
                             @cCompany char (30),
                             @mSalary money as 
begin
        if exists ( select * from testcommit
                    where cRowId = @cRowId )
        begin
     
        begin transaction
        
        update testcommit3 set cPosition = @cPosition 
        from testcommit join testcommit1 on cRowId = cRef 
        join testcommit3 on cRefId = cCompId
        where cRowId = @cRowId

        update testcommit3 set cCompany = @cCompany 
        from testcommit join testcommit1 on cRowId = cRef 
        join testcommit3 on cRefId = cCompId
        where cRowId = @cRowId

        update testcommit3 set mSalary = mSalary + @mSalary 
        from testcommit join testcommit1 on cRowId = cRef 
        join testcommit3 on cRefId = cCompId
        where cRowId = @cRowId

        if ( select max(mSalary) from testcommit3 ) > 10000
        begin
              rollback transaction
              print "Transaction Rolled Back."
        end
        else
        begin
              commit transaction
              print "Transaction Committed."
        end
        end
        else
              print "Error Updating All for Object " + @cRowId
end

/*  Now, let's see if our procedure is working. Let's 
    check the existing salary of row '001'. Highlight 
    and execute the next command. */

exec prShowOne '001'

/*  Now, update the salary of row '001' using the next 
    procedure. Highlight and execute it. Check the  
    result pane if the transaction was rolled back 
    or committed. */

exec prUpdateAll '001', 'Lecturer', 'NIIT', 1000

/*  Check if the values have been updated. Highlight 
    and execute the next command. Was the 
    salary of '001' increased? Why? */

exec prShowOne '001'

/*  Now, let's test if our procedure will be able 
    to roll back the transaction if it violates 
    our business rule that disallows salaries 
    greater than 10,000. First, let's check what 
    the salary of row id '003' is. Highlight and 
    execute the next command. */

exec prShowOne '003'

/*  Okay, the next command will cause the salary of 
    row '003' to become greater than 10,000. It should 
    therefore roll back the transaction to restore 
    the original values found in columns of row '003'. 
    Pay close atention to any roll back or commit 
    message the procedure will send. Highlight and 
    execute it to see what will happen. */

exec prUpdateAll '003', 'Lecturer', 'NIIT', 5000

/*  Did the procedure roll back the transaction? Why? */

/*  Now let's check if the values for row '003' had 
    been changed. Highlight and execute the next command. */

exec prShowOne '003'

/*  Now, let's excute the procedure again this time using 
    values that will make the salary of row '003' just 
    exactly 10,000. Pay close attention to any messages 
    in your result pane. Execute and highlight the 
    next instruction. */

exec prUpdateAll '003', 'Secretary', 'JHGFD', 2000

/*  Was the transaction committed? Why? Okay, let's take 
    a look at what happened to our data. Highlight and 
    execute the next command. */

exec prShowOne '003'

/*  What is the salary of row '003' now? Why was 
    the transaction committed? Now, let's test if 
    the procedure will be able to catch an input 
    with invalid data. Pay close atention to any 
    error message in your result pane. Highlight 
    and execute the next command which requests an 
    update for a non-existing row '015'. */

exec prUpdateAll '015', 'Secretary', 'JHGFD', 2000

/*  Did you see the error message? Good, so the 
    procedure is working as we intended it. If you 
    still have questions or other problems, please 
    e-mail me at apcastro111@yahoo.com. Thanks. */