BTD210
Lab #5
Updating Data
In this lab, you will perform various task: -
1) Create a new table from existing data
2) Change data using the UPDATE command
3) Add new data using the INSERT command
4) Delete data using the DELETE command
5) Use nulls in an UPDATE command
1) Your account already contains the tables for the Premiere database. You can create a new table using data in an existing table. For example, if you want to create a table called Level1Customer containing the following columns from the customer table; customer_num, customer_name, balance, credit_limit, and rep_num, you would write the CREATE TABLE command as follows:
Create table level1customer (
Customer_num int primary key,
Customer_name varchar(35),
Balance dec(8,2) ,
Credit_limit dec(8,2),
Rep_num int
)
To insert the data that already exists in the Customer table, you use the SELECT INTO command as follows: In this example, we want to select only those accounts for which the credit limit is $7500)
Insert into level1customer
select customer_num, customer_name, balance, credit_limit, rep_num
from customer
where credit_limit = 7500
2) If there is any attribute that we want to change, we use the UPDATE command. For instance, If we want to change the name of customer 842 to All Season Sport, we would use the following command:
Update level1customer
Set customer_name = ‘All Season Sport’
Where customer_num = 842
You can also use the UPDATE command to update a value by using a calculation on an existing value. For instance, if you decide that credit limits should be raised by 10%, you can make this change by means of the update command:
Update level1customer
Set credit_limit = credit_limit * 1.10
3). You used the INSERT command to add the initial rows to the tables in the database. If you want to add additional rows to the table, you can also use the INSERT command. For exampleyou want to add a new customer, #895, to the level1customer table. The name is Peter and Margaret’s; the balance is 0; the credit limit is $8000 and the rep number is 20. the command is written as follows:
Insert into level1customer
Values
(895, ‘Peter and Margaret’’s’ , 0, 8000, 20)
(Note that I have highlighted Peter and Margaret’s because I have used 2 apostrophes. This is how the system knows to include it as part of the name)
4) To delete data from the database, use the DELETE command. We use this command to delete rows from a table. If we want to remove customer 895 from the level1customer table, we write the following command:
Delete from level1customer
Where customer_num = 895
5) The command for changing a column value to NULL is exactly what it would be for changing any other value. You simple use the value NULL as the replacement value. To reset the balance to null for customer 795, we would write the following command:
Update level1customer
Set balance = null
Where customer_num = 795
Lab HandIn – Complete the following queries, execute them to verify that they run, and send the script to me via e-mail.
1. Create a NONAPPLIANCE table with the structure shown below:
NONAPPLIANCE
Column / Type / Size / PrimaryKey / Foreign Key / Required
/Not Null / Unique / Validation/Check
Part_num / varchar / 4 / Y / Y
Description / Varchar / 15
On_hand / Dec / 4,0
Class / Varchar / 2
Price / Dec / 6,2
2. Insert into the Nonappliance table the part number, part description, number of units on hand, item class, and unit price from the PART table for each part that is NOT inclass AP.
3. In the Nonappliance table, change the description of part number AT94 to “Steam Iron”.
4. In the Nonapplioance table, increase the price of each item in item class SG by 3%.
5. Add the following part to the Nonappliance table: part number, TL92; description, Edge Trimmer; number of units on hand, 11; class, HW; and price of 29.95.
6. Delete every part in the Nonappliance table for which the class is SG
7. In the Nonappliance table, change the class for part FD21 to null.
8. Add a column ON-HAND-VALUE to the Nonappliance table. The on-hand-value is a seven digit number with two decimal places that represents the product of the number of units on hand and the price. Then set all the values of ON_Hand_Value to ON_HAND *PRICE.
9.Remove the Nonappliance table from the Premiere database.