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