Create the following artifacts: Sample Test Data to test the seven information r
ID: 3825193 • Letter: C
Question
Create the following artifacts:
Sample Test Data to test the seven information requests in the document. Place your sample data in an Excel spreadsheet.
An SQL CREATE DATABASE statement to create a database container for the phone table
An SQL CREATE TABLE to Create the table in the database
SQL INSERT INTO statements to insert your Sample Test Data into the phone table
SQL SELECT queries to respond to the information requests and test the functionality of the table and test data.
1. Create SQL queries to extract the following from the phone table, whose metadata is shown below:
a. The Phone ID, Manufacturer and Model Name for phones provided by Verizon.
b. The Phone ID, Manufacturer, Model Name and Rating for phones provided by Cellular One and less
than $200.00.
c. The Phone ID, Manufacturer, Model Name, Provider, Width and Height for all Smart Phones with a
height less than 5 inches.
d. The Phone ID, Manufacturer, Model Name, Provider and Price for all Windows Phones with 16GB
or more of memory.
e. The Phone ID, Manufacturer, Model Name, Provider and Rating for all phones Manufactured by
Apple and Samsung.
f. The Phone ID, Manufacturer, Model Name, Provider, price and Operating System for all smart
phones with at least 32GB of memory, rating of at least 4 and priced over $99.00.
g. The Phone ID, Manufacturer, Model Name, Provider and Price for all phones that are not Smart
Phones.
Table: Phone
column name
data type
size
notes about the data
ID
Number
Phone ID number (primary key) Integer
Manu
Text
40
Manufacturer Eg. Samsung, Apple, etc.
ModelNum
Text
20
Model Number
ModelName
Text
2
Eg. Galaxy Note 3, IPhone 5s, etc.
Desc
Text
400
Marketing description of phone
Memory
Number
Memory in Gigabytes eg. 16
Height
Number
Height in inches eg. 4.75
Width
Number
Width in inches eg. 3.50
Provider
Text
20
Eg. Verizon, Sprint
Price
Number
Eg. 299.99
Rating
Number
Range 1-5 eg. 4.2
OperSys
Text
20
Operating System Eg. Andriod, Windows
SmartPhone
Boolean
true = Yes ; false = No
Table: Phone
column name
data type
size
notes about the data
ID
Number
Phone ID number (primary key) Integer
Manu
Text
40
Manufacturer Eg. Samsung, Apple, etc.
ModelNum
Text
20
Model Number
ModelName
Text
2
Eg. Galaxy Note 3, IPhone 5s, etc.
Desc
Text
400
Marketing description of phone
Memory
Number
Memory in Gigabytes eg. 16
Height
Number
Height in inches eg. 4.75
Width
Number
Width in inches eg. 3.50
Provider
Text
20
Eg. Verizon, Sprint
Price
Number
Eg. 299.99
Rating
Number
Range 1-5 eg. 4.2
OperSys
Text
20
Operating System Eg. Andriod, Windows
SmartPhone
Boolean
true = Yes ; false = No
Explanation / Answer
a. The Phone ID, Manufacturer and Model Name for phones provided by Verizon.
select ID,manu,ModelName from phone where provider = 'Verizon';
b. The Phone ID, Manufacturer, Model Name and Rating for phones provided by Cellular One and less
than $200.00.
SELECT ID,manu,ModelName,Rating from Phone
where provider = 'cellular' and price < 200.00;
c. The Phone ID, Manufacturer, Model Name, Provider, Width and Height for all Smart Phones with a
height less than 5 inches
select ID,manu,ModelName,Provider,Width,Height from Phone
where smartphone = 'Yes' and Height < 5;
d. The Phone ID, Manufacturer, Model Name, Provider and Price for all Windows Phones with 16GB
or more of memory.
select ID,manu,ModelName,Provider,Price from Phone
where OperSys = 'Windows' and Memory > = 16;
e. The Phone ID, Manufacturer, Model Name, Provider and Rating for all phones Manufactured by
Apple and Samsung.
select ID,manu,ModelName,Provider,Rating from Phone
where Manu In('Apple','Samsung');
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.