1. Create the Premiere Product Database with 2 tables using the following specifications:
a. At the command for the directory where you have sqlite3.exe, create the database see image.
b. Once in the SQLite shell, create the CUSTOMER table and insert all its records
(Note: You can just copy and paste these lines to the sqlite shell) CUSTOMER Table (10 records):
CREATE TABLE CUSTOMER (CUSTOMER_NUM varchar(3) primary key , CUSTOMER_NAME
varchar(35),STREET varchar(15), CITY varchar(15), STATE varchar(2), ZIP varchar (5), BALANCE real,
CREDIT_LIMIT real, REP_NUM varchar(2));
Insert into customer values ('148', 'Al''s Appliance and Sport','2837 Greenway', 'Fillmore', 'FL','33336',
6550, 7500, 20);
Here are the rest of the records for the CUSTOMERS table. Follow the same syntax used in the INSERT above to insert these records. I suggest you copy and paste them into your INSERT commands to avoid possible errors at this point.
Note the extra single quotes around customer names that use apostrophes.
'282', 'Brookings Direct' , '3827 Devon', 'Grove', 'FL', '33321', 431.5, 10000, '35'
'356', 'Ferguson''s' , '382 Wildwood', 'Northfield', 'FL', '33146', 5785, 7500, '65'
'408', 'The Everything Shop', '1828 Raven', 'Crystal', 'FL', '33503', 5285.25 , 5000, '35'
'462', 'Bargains Galore', '3829 Central', 'Grove', 'FL', '33321', 3412, 10000, '65'
'524', 'Kline''s', '838 Ridgeland' , 'Fillmore' , 'FL', '33336', 12762, 15000, '20'
'608', 'Johnson''s Department Store', '372 Oxford', 'Sheldon', 'FL', '33553', 2106, 10000, '65'
'687', 'Lee''s Sport and Appliance', '282 Evergreen', 'Altonville', 'FL', '32543', 2851, 5000, '35'
'725', 'Deerfield''s Four Seasons', '282 Columbia', 'Sheldon', 'FL', '33553', 248, 7500, '35'
'842', 'All Season', '28 Lakeview', 'Grove', 'FL', '33321' , '8221', '7500', '20'
c. Once in the sqlite shell, create the REP table and insert all its records
CREATE TABLE REP (REP_NUM varchar(2) primary key ,LAST_NAME varchar(15),FIRST_NAME
varchar(15), STREET varchar(15) , CITY varchar(15) , STATE varchar(2) ,ZIP varchar(5) , COMMISSION
real, RATE real);
insert into rep values('20' ,'Kaiser', 'Valerie', '624 Randall', 'Grove', 'FL','33321', 20542.50, 0.05); insert
into rep values('35', 'Hull' ,'Richard' ,'532 Jackson', 'Sheldon', 'FL', '33553', 39216.00, 0.07); insert into
rep values('65' ,'Perez', 'Juan' ,'1626 Taylor', 'Fillmore', 'FL', '33336', 23487.00, 0.05); To make it easy
for you to see the output you should get, I’ve pasted screenshots. You’ll submit the results of your
commands, steps 1 thru 6 listed below:
1. Display the rep number, first name, last name, commission and rate from all the records in the REP table see image.
2. Display the rep number, first name, last name, commission and rate from all the records in the REP table where the last name is Hull see image.
3. Display the rep number, first name, last name, commission and rate from all the records in the REP table where rate is equal to 0.05 see image.
4. Display the customer number, customer name, city, and balance for all the records in the Customer table listed in ascending order by city see image.
5. Display the customer number, customer name and balance for all the records in the Customer table for customers whose balance is greater than 5000 dollars listed in ascending order by balance see image.
6. Display the customer number, customer name and rep number for all the records in the Customer table for rep number equal to 20 see image.