1. Create the Premiere Product Database with the first 2 tables using the following specifications: a....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
1. Create the Premiere Product Database with the first 2 tables using the following specifications: a. Double click the sqlite3.exe file Note that you are 'Connected to a transient in-memory database'. You'll see this in red once the command line/ SQLite shell opens Also note that you can use the dot command .open in order to create a new database or open an existing one C:\SQLite\sqlite3.exe SQLite version 3.8.8.3 2015-02-25 13:29:11 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> b. At the sqlite> prompt, type .open premiere.db this will create an SQLite database named premiere.db. If you already have that database in the directory, this command will open it c. Now you can create the CUSTOMER table and INSERT all its records (Note: You can just copy and paste these lines to the sqlite shell) Create the 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); REP_NUM 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 to avoid possible errors at this point. Note the extra single quotes around customer names that use apostrophes. CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, '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, '524', 'Kline"s', '838 Ridgeland', 'Fillmore','FL', '33336', 12762, '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' 10000, '65' 15000, '20' d. 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); 2. Display the rep number, first name, last name, commission and rate for all the records in the REP table REP NUM 20 35 65 sqlite> 687 408 148 524 292 282 35 sqlite> 462 462 FIRST NAME Valerie Richard Juan REP_NUM 3. 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 REP_NUM FIRST NAME LAST_NAME Richard the REP table where rate is equal to 0.05 20 65 sqlite> 942 842 356 608 725 sqlite> CUSTOMER_NUM CUSTOMER_NAME LAST_NAME COMMISSION RATE Kaiser Hull Perez 3. Display the rep number, first name, last name, commission and rate from all the records in Hull 20542.5 39216.0 23487.0 FIRST NAME LAST_NAME COMMISSION RATE Valerie Juan Kaiser Perez 408 356 148 842 524 sqlite> Ferguson's Johnson's Department Stor Deerfield's Four Seasons COMMISSION RATE 0.07 39216.0 4. Display the customer number, customer name, city, and balance for all the records in the Customer table listed in ascending order by city 148 524 842 sqlite> Lee's Sport and Appliance Altonville The Everything Shop Crystal Al's Appliance and Sport Fillmore Kline's Fillmore Brookings Direct Bargains Galore All Season 0.05 0.07 0.05 20542.5 23487.0 CUSTOMER_NUM CUSTOMER_NAME 0 CITY X CUSTOMER_NUM CUSTOMER_NAME 0.05 0.05 BALANCE 2851.0 5285.25 6550.0 12762.0 421 5 431.5 3412.0 Growe Grove Grove Grove 8221.0 Northfield 5785.0 Sheldon 2106.0 Sheldon 248.0 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 BALANCE The Everything Shop 5285.25 Ferguson's 5785.0 Al's Appliance and All Season Kline's 6550.0 8221.0 12762.0 6. Display the customer number, customer name and rep number for all the records in the Customer table for rep number equal to 20 REP_NUM Al's Appliance and Sport 20 20 Kline's All Season 20 1. Create the Premiere Product Database with the first 2 tables using the following specifications: a. Double click the sqlite3.exe file Note that you are 'Connected to a transient in-memory database'. You'll see this in red once the command line/ SQLite shell opens Also note that you can use the dot command .open in order to create a new database or open an existing one C:\SQLite\sqlite3.exe SQLite version 3.8.8.3 2015-02-25 13:29:11 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> b. At the sqlite> prompt, type .open premiere.db this will create an SQLite database named premiere.db. If you already have that database in the directory, this command will open it c. Now you can create the CUSTOMER table and INSERT all its records (Note: You can just copy and paste these lines to the sqlite shell) Create the 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); REP_NUM 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 to avoid possible errors at this point. Note the extra single quotes around customer names that use apostrophes. CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, '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, '524', 'Kline"s', '838 Ridgeland', 'Fillmore','FL', '33336', 12762, '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' 10000, '65' 15000, '20' d. 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); 2. Display the rep number, first name, last name, commission and rate for all the records in the REP table REP NUM 20 35 65 sqlite> 687 408 148 524 292 282 35 sqlite> 462 462 FIRST NAME Valerie Richard Juan REP_NUM 3. 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 REP_NUM FIRST NAME LAST_NAME Richard the REP table where rate is equal to 0.05 20 65 sqlite> 942 842 356 608 725 sqlite> CUSTOMER_NUM CUSTOMER_NAME LAST_NAME COMMISSION RATE Kaiser Hull Perez 3. Display the rep number, first name, last name, commission and rate from all the records in Hull 20542.5 39216.0 23487.0 FIRST NAME LAST_NAME COMMISSION RATE Valerie Juan Kaiser Perez 408 356 148 842 524 sqlite> Ferguson's Johnson's Department Stor Deerfield's Four Seasons COMMISSION RATE 0.07 39216.0 4. Display the customer number, customer name, city, and balance for all the records in the Customer table listed in ascending order by city 148 524 842 sqlite> Lee's Sport and Appliance Altonville The Everything Shop Crystal Al's Appliance and Sport Fillmore Kline's Fillmore Brookings Direct Bargains Galore All Season 0.05 0.07 0.05 20542.5 23487.0 CUSTOMER_NUM CUSTOMER_NAME 0 CITY X CUSTOMER_NUM CUSTOMER_NAME 0.05 0.05 BALANCE 2851.0 5285.25 6550.0 12762.0 421 5 431.5 3412.0 Growe Grove Grove Grove 8221.0 Northfield 5785.0 Sheldon 2106.0 Sheldon 248.0 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 BALANCE The Everything Shop 5285.25 Ferguson's 5785.0 Al's Appliance and All Season Kline's 6550.0 8221.0 12762.0 6. Display the customer number, customer name and rep number for all the records in the Customer table for rep number equal to 20 REP_NUM Al's Appliance and Sport 20 20 Kline's All Season 20
Expert Answer:
Answer rating: 100% (QA)
It looks like youve shared a set of tasks related to creating and querying a database using SQLite B... View the full answer
Related Book For
Systems analysis and design
ISBN: 978-0136089162
8th Edition
Authors: kenneth e. kendall, julie e. kendall
Posted Date:
Students also viewed these databases questions
-
pepsi versus coca cola Quick Ratio Liquid Ratio Coca cola Pepsi Current ratio Accounts receivable Inventory Solvency Solvency Ratio Coca-Cola Pepsi Debt to Total assets Times interest earned Free...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
On 28 April 2020, Mr Guna, CEO of Econ Engineering Malaysia, proposed to complete an abandoned boiler project that no one had dared to revive. He knew that the project was 60% complete before it was...
-
What do Alderfer consider to be the primary human needs?
-
Glenn Otis is to retire from the partnership of Otis and Associates as of March 31, the end of the current fiscal year. After closing the accounts, the capital balances of the partners are as...
-
Plot the graphs of \(\left(\Omega_{1} / \omega_{2} ight)\) against \(\left(m_{2} / m_{1} ight)\) and \(\left(\Omega_{2} / \omega_{2} ight)\) against \(\left(m_{2} / m_{1} ight)\) as \(\left(m_{2} /...
-
Change in Estimate Depreciation Frederick Industries changed from the double-declining balance to the straight-line method in 2010 on all its plant assets. There was no change in the assets salvage...
-
Describe about Organizational Behavior and Management ? What are the three goals of organizational behavior? What are the 4 primary areas of organizational behavior?
-
1. Do you believe brand personality plays a major part in decision making? Explain. 2. After evaluating Table A, which alcohol brand will Greg be most likely to purchase? 3. Using Table B and taking...
-
Let W = {f(x) = P | f(x) = ax + br2, where a, b = R}. Is W a subspace of P4? If so, find its basis, and determine dim W. Show all your work.
-
Given a Broadway show, which information is best stored using annotations? (Choose two.) A. The number of people attending each day B. The maximum number of tickets a person can purchase C. The total...
-
What is the result of running the following program? A. 6 B. X C. The code does not compile. D. The code compiles but throws a NullPointerException at runtime. E. The code compiles but throws a...
-
What is the minimum number of requires directives that need to be removed to break the cyclic dependency? A. None, there is no cyclic dependency B. 1 C. 2 D. 3 E. 4 module com.animal { exports...
-
How many lines of the following application contain compilation errors? A. The code compiles and runs without issue. B. One. C. Two. D. Three. E. Four. F. None of the above. 1: package percussion; 2:...
-
Suppose we have a peacocks table with two columns: name and rating. What does the following code output if the table is empty? A. false B. true C. The code does not compile due to lines 1213. D. The...
-
A firm with an AA rating likes to issue one million units of a 10 year-1.40% bond with face value $100. The yearly coupon payment is $1.40. The yield curve is flat and the yield is 1%. The default...
-
Let (x) = x 2 - 9, g(x) = 2x, and h(x) = x - 3. Find each of the following. (((--) 2
-
Create a context-level diagram for billing in a dental office. External entities include the patients and insurance companies.
-
List six objectives the analyst pursues in designing system output.
-
Here are several situations calling for decisions about output content, output methodology, distribution, and so on. For each situation, note the appropriate output decision. a. A large,...
-
Using the online code for the example in this chapter as a starting point and the solution from a previous exercise. 1. Implement (or modify your previous) Student: :DropCourse (). When a student...
-
What other examples can you imagine that might easily incorporate the Factory Method pattern?
-
Using the solution from a previous exercise, augment your code as follows: 1. Implement the Factory Method pattern to create various shapes. You will have already created an abstract base class of...
Study smarter with the SolutionInn App