Query 1: The bookstore has decided to keep track of the vendors' information. In order to do this, one new table will be added to the database.
The schema for this table, as related to the existing tables, is the following: vendor(vendor_id, name, address1, address2, city, state , zip_code, phone, contact_last_name , contact_first_name) Create a new table containing the previous ten columns: The table name should be VENDOR.
The vendor ID column contains a numeric number. Use column sizes you consider suitable for the columns. The vendor_id column is a primary key. Select appropriate datatypes/sizes for columns. Use a SINGLE sql statement to create the table. If you use more than one statement to complete this part, you will get no points for this query.
Query 2: Add five rows to the VENDOR table. The script 151A_vender_insert_data.sql contains the sql to populate the table needed for this part of the assignment. Run this each time you work on the table. ( this is a fairly small file- you can run it via copy and paste techniques.) BE CERTAIN to run the inserts script before you run the version of the script-to-spool that you want me to grade.
Query 3: Alter the VENDOR table to add a column that will be used to store the email address of each vendor. Name this column vendor_email and use an appropriate number datatype specification. You do not need to store any data in this column.
Query 4: Display the rows in the VENDOR table.
Query 5: Display column names and datatypes for the VENDOR table.
Query 6: Delete the vendor_email column from the VENDOR table.
Query 7: Rename the VENDOR table as VENDOR_BACKUP.
Query 8: Display the names of the tables in your current database. ______ 2
Query 9: Remove the VENDOR_BACKUP table from the database.
Query 10: Alter the EMPLOYEES table to add a column that will be used to store the salary earned year-to-date. Name this column SalaryYearToDate and use an appropriate NUMBER datatype specification. You do not need to store any data in this column.
Query 11: Write an SQL statement to drop the SalaryYearToDate column you added to the EMPLOYEES table in query 10.
Data:
INSERT INTO vendor
(vendor_id,name,address1,address2,city,state,zip_code,phone,contact_last_name,contact_first_name)
VALUES (1,'US Postal Service','Attn: Supt. Window Services','PO Box 7005','Madison','WI','53707','(800) 555-1205','Alberto','Francesco');
INSERT INTO vendor
(vendor_id,name,address1,address2,city,state,zip_code,phone,contact_last_name,contact_first_name)
VALUES (2,'National Information Data Ctr','PO Box 96621','NULL','Washington','DC','20090','(301) 555-8950','Irvin','Ania');
INSERT INTO vendor
(vendor_id,name,address1,address2,city,state,zip_code,phone,contact_last_name,contact_first_name)
VALUES (3,'Register of Copyrights','Library Of Congress','NULL','Washington','DC','20559','NULL','Liana','Lukas');
INSERT INTO vendor
(vendor_id,name,address1,address2,city,state,zip_code,phone,contact_last_name,contact_first_name)
VALUES (4,'Jobtrak','1990 Westwood Blvd Ste 260','NULL','Los Angeles','CA','90025','(800) 555-8725','Quinn','Kenzie');
INSERT INTO vendor
(vendor_id,name,address1,address2,city,state,zip_code,phone,contact_last_name,contact_first_name)
VALUES (5,'Newbrige Book Clubs','3000 Cindel Drive','NULL','Washington','NJ','07882','(800) 555-9980','Marks','Michelle');