Background

Wanted to see how hard it will be to play around with the sakila MySQL Database.

Objective

Our objective will be to add a couple of records to the sakila.customer table.

Because the table has a couple of foreign keys, we need to get a handle to valid entries in those tables, as well.

SQL

Code

start transaction;

    set @tsCurrent = NOW();
    set @customerID_01 := 0;
    set @firstname_01  := 'John';
    set @lastname_01   := 'Williams';

    set @customerID_02 := 0;
    set @firstname_02  := 'Julia';
    set @lastname_02  := 'Johnson';

    set @iNumberofRecordsAffected := 0;

    /*
     * Get Address ID
     * Get first entry in table
     */
    set @addressID_01 :=
                (
                    select address_id
                    from   sakila.address a
                    order by address_id
                    limit  1
                )
    ;  

    set @addressID_02 :=
                (
                    select address_id
                    from   sakila.address a
                    order by address_id desc
                    limit  1
                )
    ;  

     /*
     * Get Store ID
     * Get first entry in table
     */
    set @storeID_01 :=
            (
                select store_id
                from   sakila.store s
                order by address_id asc
                limit  1
            )
    ;

     /*
     * Get Store ID
     * Get last entry in table
     */

    set @storeID_02 :=
            (
                select store_id
                from   sakila.store s
                order by store_id desc
                limit  1
            )
    ;

    /*
     * Add a new record into the customer table
    */
    insert into sakila.customer
    (
          `store_id`
        , `first_name`
        , `last_name`
        , `address_id`
    )
    values
    (
          @storeID_01
        , @firstname_01
        , @lastname_01
        , @addressID_01
    )
    ;

   /*
    * Get Number of records affected by last statement
   */
   set @iNumberofRecordsAffected_01 := ROW_COUNT();

   /*
   * Get ID for last inserted row
   */
   SET @customerID_01 := LAST_INSERT_ID();

     /*
     * Add a new record into the customer table
    */
    insert into sakila.customer
    (
          `store_id`
        , `first_name`
        , `last_name`
        , `address_id`
    )
    values
    (
          @storeID_02
        , @firstname_02
        , @lastname_02
        , @addressID_02
    )
    ;

   /*
    * Get Number of records affected by last statement
   */
   set @iNumberofRecordsAffected_02 := ROW_COUNT();

   /*
   * Get ID for last inserted row
   */
   SET @customerID_02 := LAST_INSERT_ID();

   select
              tblSC.`customer_id`
            , tblSC.first_name
            , tblSC.last_name
            , tblSC.create_date as 'customerAddedOn'
            , tblSA.address  as 'customerAddress'
            , tblSA2.address as 'storeAddress'

   from   sakila.customer tblSC

   inner join sakila.address tblSA

            on tblSC.address_id = tblSA.address_id

   inner join sakila.store tblSS

            on tblSC.store_id = tblSS.store_id

   inner join sakila.address tblSA2

            on tblSS.address_id = tblSA2.address_id

   where  tblSC.`create_date` >= @tsCurrent

   order by
         tblSC.`customer_id` asc

  ;

  select
      @iNumberofRecordsAffected_01 as '@iNumberofRecordsAffected_01'
    , @iNumberofRecordsAffected_02 as '@iNumberofRecordsAffected_02'
  ;

rollback;

Output

Source Code Control

Gist

Sakila.Customer
Link