DescriptionIFSM 330 Business Intelligence and Data Analytics
Business Intelligence SQL Assignment Instructions
Student Name:
First of all, have a look at the SQL database creation and population code that accompanies this
assignment. You should be familiar with the fields and what kinds of data are represented in the
database. All questions and items in this assignment pertain to the SQL code that accompanies this
assignment.
You must include all SQL code here in the spaces below, respecting standard capitalization conventions
as presented in the tutorials. For ease of review, please use BOLD on any SQL code (see the example
below). Your SQL code must be in text format, so that the instructor can cut and paste for review and
testing purposes. Here is an example of SQL code/text that can be cut and pasted for testing:
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, weight REAL);
INSERT INTO customers VALUES (73, “Brian”, 33, 50);
For certain responses you are also asked to include a ‘snip’ / screenshot of your results. If you can, try
not to take an image of the entire screen, but only of the relevant output.
You should use sqliteonline.com to complete this assignment, both to test code and take screenshots:
https://sqliteonline.com/
Here is an example of a database schema image and a query results image from sqliteonline.com.
Due to screen resolution, it’s not always possible to capture all query results. But be sure your images
are legible, no smaller than the second image below. If an image is not legible, your assignment may
be returned to you and you’ll have to rerun the SQL scripts.
Feel free to take several screenshots as needed or make the columns narrower in the sqliteonline
output before taking the screenshot (columns can be manually made more narrow by hovering over
the lines marked in red below):
For some of these questions, you will need to perform a SUM or AVERAGE in a grouphttps://www.khanacademy.org/computing/computer-programming/sql/more-advanced-sqlqueries/pt/restricting-grouped-results-with-having
1). 10 points. As you glance through the database, you note that Houston is spelled with a lower case
‘h’ in at least one case. First of all, write the SQL code to list all values for Houston, capitalized or not
capitalized. Begin with SELECT * FROM sales to list all fields.
Then, write the SQL script to update all lower case instances of houston to Houston. NOTE: use of
WHERE id= is not permitted. This method is used in the tutorial but is not practical if you had a very
large database.
2) 10 points. Write an SQL query to list all products and the total sales in dollars for each product. List
products in order from highest total sales to lowest. Use Total_Sales as the label for the field (column)
containing the sales data.
INSERT a snip (image) of your QUERY RESULTS here:
3) 10 points. Write an SQL query to list the customer name and the dollar amount of total purchases.
List only names and dollar amounts if total purchases exceed $5000. List in order of total purchases
from highest to lowest and use the column titles Customer_Name and Total_Purchases.
INSERT a snip (image) of your QUERY RESULTS here:
4) 10 points. Write an SQL query to list the states (note Canadian provinces are also included in the
database) and the dollar amount of the average purchase. List only states (and provinces) when the
average purchase exceeds $2500. List in order of average purchase from highest to lowest and use the
column titles State_Province and Average_Purchase. Round the dollar amount of the Average_Purchase
so only whole numbers (no decimals) will appear.
INSERT a snip (image) of your QUERY RESULTS here:
5a) NOTE: For all of Q5, this must be an actual situation/scenario that can be executed with an SQL
query on the data provided in the assignment. NOT a hypothetical scenario or partial SQL query. If
this is unclear, please ask.
15 points. In 2-3 sentences, given the nature and structure of the furniturestore.sql data, describe a
situation or scenario (or question that might be answered) that would require use of a CASE statement
in SQL. (This is a very open question with many correct responses. Think about what the CASE
statement in SQL allows us to do.)
5b) Create the SQL query for the scenario presented in part a). This SQL query must be complete,
executable and produce output relevant to the scenario presented in part a).
INSERT a snip (image) of your QUERY RESULTS here:
6a) 15 points. You have been asked to produce a report that shows all chairs bought on January 12,
2019. In the box below, respond to the following question:
Why does the following SQL query not yield any results (feel free to test it)?
SELECT * FROM sales WHERE product = ‘Chair’ AND transaction_date = ‘1/12/19’;
6b) Rewrite the query so that it yields the correct results.
INSERT a snip (image) of your QUERY RESULTS here:
PART 2:
Part 2 introduces a second table to the database schema. When creating queries from two tables, in
particular when there may be a field that shares a common name, it is safer to construct your queries
with the table name AND field name. You may wish to review the “JOINing related tables” tutorial and
video on Khan Academy.
7) 5 points. Using an SQL script, add (CREATE) a suppliers table to the database schema, with an
autoincrement integer as the primary key (id), and fields for name and country (both text). The primary
key (id) will correspond to the supplier field in the sales table (e.g. supplier in sales is a foreign key).
8) 5 points. Using an SQL script, insert the following data into the suppliers table, in the order presented
in the table (Reminder: id primary key should have been set to autoincrement when the table was
created):
Name
Ikea
Rosewood
BoConcept
AllModern
Country
Sweden
Thailand
Denmark
United States
9) 10 points. Write an SQL query to list transaction date, customer name, product, price, supplier name
and country (6 columns) ordered by price from highest to lowest. List only items with a price greater
than 1500. Construct the SQL query to present field (column) titles as: Date, Customer, Product, Price,
Supplier, Country.
INSERT a snip (image) of your QUERY RESULTS here:
10) 10 points. Write an SQL query to list supplier name, country and total sales for that supplier.
INSERT a snip (image) of your QUERY RESULTS here:
Insert (copy and paste) all code from Items 7-10 within this textbox:
QVC-Jennifer Gary
Business intelligence (BI) is a set of strategies, processes, and tools used
to analyze data to inform business decisions. Companies like QVC often
use BI to gain customer insights, identify customer needs, and measure
performance. BI can be used in practice to identify opportunities and
find trends, optimize operations, and forecast future outcomes.
The data used for QVC’s BI implementation included customer data
(demographics, buying habits, etc.), product data (sales, competition,
etc.), and financial data (invoices, expenses, etc.). In order to fully access
the data and produce meaningful insights, QVC employed a
combination of on-premises and cloud-based technology from vendors
such as Microsoft, Tableau, and SAP.
The implementation of BI could be faced with various challenges, such
as ensuring data security, setting up reliable data pipelines, and tackling
data governance issues. Additionally, employees need to be able to
interpret the data and turn it into insights that can be easily assimilated
through tools like dashboards to inform decision making.
Other technology and vendors that QVC could consider using in
addition to Microsoft, Tableau, and SAP include:
IBM Cognos: This is a comprehensive business intelligence platform
that offers features such as data warehousing, reporting, analytics, and
dashboard creation.
Oracle Business Intelligence: This platform provides a range of business
intelligence capabilities, including data visualization, reporting, and
predictive analytics.
Looker: This is a cloud-based business intelligence platform that
enables data exploration and analysis, as well as the creation of
interactive dashboards and reports.
Amazon Web Services (AWS): AWS offers a range of cloud-based
services for business intelligence, including data warehousing, big data
analytics, and machine learning.
Google Cloud Platform (GCP): GCP offers a range of business
intelligence services, including data warehousing, big data analytics, and
machine learning.
The choice of technology and vendors will depend on a number of
factors, including the specific business intelligence needs of QVC, the
size and complexity of their data, and their overall IT strategy.
Autumn Fishel
What problem is business intelligence helping to solve?
Business intelligence can provide a deeper insight into trends related to human
capital within a company such as Microsoft. People-based data can then be
aggregated and with the help of data software, can be analyzed to allow Human
Resource Managers the opportunity to identify which data is the most
important and how to utilize it in determining how to best manage their
employees and reach the business’ organizational strategies (Microsoft, n.d.).
•
How is business intelligence used in practice?
Human Resources at Microsoft use analytics to develop statistical profiles of
the employees that are most likely to leave the company. The use of these
analytics provides the team with an opportunity to open further discussion
regarding growth and a total rewards compensation package within the
company. This ultimately leads to enhanced workplace communication amongst
the upper management and lower-level employees.
•
What were (are) the results (even preliminary or anticipated)?
The use of business intelligence and data analytics has given Microsoft the
ability to focus on what causes turnover within the company and how to reduce
attrition rates by improving the overall employee experience and catering to the
individuals motivation within the organization.
•
What data was used?
Both quantitative and qualitative data can be used to analyze, interpret, and
better understand the patterns and behaviors of their employees. Prior to 2020,
Microsoft would send out a lengthy survey to track employee engagement.
Although improvements would be made based on the data collected, the
employee experience bar was set low. The end goal of the Microsoft employee
survey was to ensure employees were thriving and had a sense of purpose.
Employee fulfillment would be brown down in the following five success
components (Klinghoffer & Elizabeth, 2022):
1. Pay
2. Perks
3. People
4. Pride
5. Purpose
•
Were there any specific challenges to be faced?
Human resource management can be tricky as there is no one-size-fits-all
solution. A business is only as successful as its human capital, and Microsoft has
paved the way to ensure employees are thriving and to keep listening, learning,
improving, and adapting to any changes that lie ahead.
Stitch Fix
Mary Fernando
•
What problem is business intelligence helping to solve?
Stitch Fix was established to provide customers with a unique
subscription service to shop for clothing online. It took the job of a
personal shopper to the next level. The online styling subscription
service eliminates the need for their customers to go out and shop for
clothing or even browse online because they deliver personalized
recommendations right to their door on a regular schedule (Marr,
2019). The challenge this new business venture faced was that the
company wants to reduce returns, keep repeat customers and generate
word-of-mouth business with recommendations from customers to
their friends and family. These are the problems that business
intelligence was helping to solve.
•
How is business intelligence used in practice?
Stitch Fix used BI to collect data at every point of interaction with their
customers. That feedback gets input into the company’s data vaults to
make the algorithms even better at determining the preferred style for
each person and even identifying trends (Marr, 2019).
•
What were (are) the results (even preliminary or anticipated)?
Personalized service to its customers – Through the use of BI to analyze
data, Stitch Fix was able to provide customized recommendations to
every customer. This helped minimize returns and keep repeat
customers by improving satisfaction rates. With the multitude of data,
they were able to collect Stitch Fix was also able to create its hybrid
design to better accommodate customer preferences.
Stitch Fix was founded in 2011 and went public in November 2017.
The company sold $730 million worth of clothing in 2016, $977 million
in 2017, and forecasts that it will sell $1.23 billion in 2018 (WBR, n.d.).
reported a customer base of 3.4 million in 2020 and revenues of $1.7
billion in the fiscal year 2020 (Morris, 2021).
•
What data was used?
Stitch Fix collects data within BI throughout the buying process,
meaning the more customer shops with Stitch Fix, the better the styling
team comprehends their taste in clothing. The company hired
astrophysicists to decode the different personal style components—
intricate work that would be impossible without the powerful analytics
of BI (Morris, 2021).
•
•
•
•
Style Profile: When a client signs up for Stitch Fix, we receive 90
different data points — from style to price point to size.
Feedback at checkout: 85% of our clients tell us why they are
keeping or returning an item. This is incredibly rich data, including
details on fit and style — no other retailer gets this level of
feedback.
Style Shuffle: an interactive feature within our app and on our
website where clients can “thumbs up” or “thumbs down” an
image of an item or an outfit. They can do this at any time — so
not just when they receive a Fix. So far, we’ve received an
incredible 4 billion item ratings from clients.
Personalized request notes to Stylists: Clients give their Stylists
specific requests, such as if they are looking for an outfit for an
event, or if they’ve seen an item that they like.
(Colaner, 2020)
•
What are the technical details (vendors, the platform used, etc.)?
Stitch Fix combines human expertise with Artificial Intelligence (AI)
systems. Their big data-backed approach is another strategic advantage,
especially when paired with machine-learning techniques to identify
customer insights and foster innovation. Not only does this technology
enable the company to increase the speed at which stylists can provide
recommendations, but it also enables new fashion styles to be born
entirely from data (Gregory, 2017)).
•
Were there any specific challenges to be faced?
There are many competitors online and offline now using the same
business model. Most importantly Amazon offers free service to its
prime members. Stitch Fix faces the challenge of becoming innovative
and maintaining higher customer satisfaction rates to retain its
customers.
•
Anything else of interest?
As with everything pandemic-related, Stitch Fix suffered a loss due to
most switching to work-from-home and hybrid work atmospheres. Now
as people are returning to in-person work Stitch Fix is once again trying
to capitalize and is planning a new venture. As the path to a postpandemic future becomes clearer, we wanted to share our approach at
Stitch Fix — what we’re calling, “work, restyled.” (Stitch Fix, 2021).
Name: Kehinde Badmus
Part 2 –Queries with Join
This is where you start to turn things in. To turn in your assignment, delete the preceding pages and
start with this one. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below,
and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your SQLite database. (Do not use Excel
or other tools to answer these questions; since we are trying to learn SQLite here, you should do these
exercises in SQLite.)
You will eventually submit this document as part of your week’s assignment.
After you have completed your first draft of this document, you will use the Week 5 SQL Check online
quiz to verify your answers. The Week 5 SQL Check online quiz uses the same numbering system given
here, so you can quickly find the question you need.
You are welcome to update your assignment document with results from the online quiz, and submit
the document after you’ve checked your results with the Week 5 SQL Check.
Write SQL queries to answer the following questions:
1. [SQL Week 5 Question 500-001] Print the first name, last name, ticket ID, ticket description, and
duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by
first name (starting with letter A at the top) and then ascending by Duration.
SELECT FirstName, LastName, idTickets, Description, Duration
FROM employee
JOIN tickets
ON employee.idEmployee = tickets.Employee_idEmployee
ORDER BY FirstName ASC, Duration ASC;
Document1
Page 1
2. [SQL Week 5 Question 500-002] We want to get a workload by employee printout. For every
employee, print the first name, last name, all ticket ID numbers assigned to that employee, the
ticket description, and ticket duration of our records. Include only tickets which have been
assigned to an employee. Include all employees, even if that person doesn’t have any tickets in
the current database. Sort them first alphabetically by first name (starting with letter A at the
top) and then ascending by ticket duration.
SELECT FirstName, LastName, idTickets, Description, Duration
FROM employee
LEFT JOIN tickets
ON employee.idEmployee = tickets.Employee_idEmployee
WHERE tickets.Employee_idEmployee IS NOT NULL
ORDER BY FirstName ASC, Duration ASC;
Document1
Page 2
3. [SQL Week 5 Question 500-003]
4. [SQL Week 5 Question 500-004] Management wants to pay special attention to calls which are
in progress and for which the duration is currently 20 minutes or longer (include calls of exactly
20 minutes duration). Print the ticket ID number, description, duration, priority, status, and the
employee first name for all of these calls.
SELECT idTickets, Description, Duration, Priority, Status, FirstName
FROM tickets
JOIN employee
ON tickets.Employee_idEmployee = employee.idEmployee
WHERE Status = ‘In Progress’ AND Duration >= 20
ORDER BY Duration DESC;
Document1
Page 3
5. [SQL Week 5 Question 500-005] You suspect there has been a malware breach at the Maryland
facility. They’re not sure if it’s an inside job (involving one of your employees) or an outside job
(involving an outside attack.) Make a listing of all high priority helpdesk tickets which have
either been identified as malware, or which were taken by an employee whose location is in the
Maryland facility. Print the description, duration, ticket ID number, ticket status, employee first
name, employee location, and priority of ticket. Sort it alphabetically by description, then by
duration (longest duration on top), then ascending by ticket ID number.
SELECT Description, Duration, idTickets, Status, FirstName, Location, Priority
FROM tickets
JOIN employee
ON tickets.Employee_idEmployee = employee.idEmployee
WHERE (Description = ‘Malware’ OR Location = ‘MD’) AND Priority = ‘1-High’
ORDER BY Description, Duration DESC, idTickets;
6. [SQL Week 5 Question 500-006] We want to see the total duration for completed helpdesk calls
by employee. Only include employees who have completed helpdesk tickets; if an employee
doesn’t have any completed tickets, their name should not show up in this report. Generate a
report for which the
o First column contains the employee’s first and last name (don’t use two columns for this
– put the first and last name together)
o Second column contains the total duration of all completed tickets by that employee. If
an employee had more than one ticket, add the durations together.
o Report is sorted so the largest total duration is on the top, and then alphabetically by
employee name (sort it using the “First Last” combination, so that “Mickey Mouse”
would come before “Nancy Mouse”.)
o Only include completed tickets; don’t include those in progress or not yet started.
o Hint: Be sure to review the Concatenation tutorial in the LEO classroom.
For example, assume your initial data was as follows.
Employee First Name
Mickey
Mickey
Donald
Donald
Donald
Employee Last Name
Mouse
Mouse
Duck
Duck
Duck
Ticket status
Completed
Completed
In Progress
Completed
Completed
Document1
Ticket Duration
5
10
15
20
25
Page 4
Donald
Duck
Completed
30
Your report would contain the following information:
Employee Name Duration of
completed
tickets
Donald Duck
75
Mickey Mouse
15
Note that Mickey Mouse has two calls here, both of which were completed, so their duration is
5 + 10 = 15. Donald Duck has four calls here, but only three of them are completed, so his
duration is 20 + 25 + 30 = 75 minutes; Donald’s 15 minute call is still in progress and is not
counted here. And Donald’s 75 minutes is the largest number of minutes, so it goes on top.
SELECT FirstName || ‘ ‘ || LastName AS “Employee Name”, SUM(Duration) AS “Total
Duration”
FROM tickets
JOIN employee ON tickets.Employee_idEmployee = employee.idemployee
WHERE Status = ‘Completed’
GROUP BY idEmployee
ORDER BY “Total Duration” DESC, “Employee Name”
7. [SQL Week 5 Question 500-007] Do the same as Problem 6 above, except in addition to the sum
of the ticket durations, also include an additional column to give us the number of completed
tickets as well. Now sort your database so the largest number of completed tickets is on the
top.
Here’s a sample of the output for Mr. Duck and Mr. Mouse.
Employee Name Duration of
Total number of
completed
completed
tickets
tickets
Donald Duck
75
3
Mickey Mouse
15
2
Document1
Page 5
SELECT FirstName || ‘ ‘ || LastName AS “Employee Name”, SUM(Duration) AS “Total
Duration”, COUNT(idTicket) AS “Number of Tickets”
FROM tickets
JOIN employee ON tickets.Employee_idEmployee = employee.idemployee
WHERE Status = ‘Completed’
GROUP BY idEmployee
ORDER BY “Number of Tickets” DESC, “Total Duration” DESC, “Employee Name”
8. [SQL Week 5 Question 500-008] You want to learn more about the average duration of all
tickets taken by employees in California. Include all tickets which have been completed or are in
progress by employees in California. Sort them alphabetically by priority, and then
alphabetically by ticket description. Additionally, group them first by priority (highest on top),
then alphabetically by description. So your final report will look something like this (your data
may vary):
SELECT
tickets.Priority,
tickets.Description,
AVG(tickets.Duration) AS Duration
FROM tickets
INNER JOIN employee
ON tickets.Employee_idEmployee = employee.idEmployee
WHERE employee.Location = ‘CA’
AND (tickets.Status = ‘Completed’
OR tickets.Status = ‘In Progress’)
GROUP BY tickets.Priority,
tickets.Description
ORDER BY tickets.Priority, tickets.Description
Document1
Page 6
Priority
1-High
1-High
2-Medium
2-Medium
2-Medium
Description
Malware
Virus
Internet
Malware
Virus
Average duration of tickets
99
12
5
8
3 etc.
9. [SQL Week 5 Question 500-009] Your management wants to know what the different types of
tickets are. Write a query that uniquely identifies ticket type based on the ticket description.
SELECT DISTINCT Description as “Ticket Type”
FROM tickets;
Document1
Page 7
Help Desk SQL Assignment
We are going to follow the same study plan as last week. You will:
• There are two parts to this document.
o Part 1 doesn’t have anything to turn in but contains important directions
o Part 2 contains the assignment you do need to turn in
• Go through this document and write SQL to answer all the questions listed below. As part of
your SQL, you will capture screenshots and paste them where indicated in Part 2.
• Refer to this document as you complete the Week 5 SQL Check online quiz which verifies your
homework. You may attempt the Week 5 SQL Check twice. Your highest score will be posted to
the Grades area. The questions may change from one attempt to the next.
• You are welcome to use the quiz results to update your homework file.
• When you have completed the online quiz, submit the Word document.
Part 1 – Set up your Database
We are now working with a database which notes helpdesk action.
There are two tables. First, there is the employee table, which contains the employee’s ID as the
primary key, first name, last name, and location. You can assume all employees at our company are
listed here.
Second, there is the ticket table, which contains each individual helpdesk ticket. The ticket table
contains the ticket ID as the primary key, a description of the helpdesk incident, the duration in minutes,
a priority, a status, and which employee (if any) has been assigned to the ticket. If nobody is available, a
ticket will be logged into the system in this table, but no employee will be assigned yet.
The incident status can be completed, in progress, or not started. If an incident is completed, the
duration field will contain the number of minutes the incident took and the employee ID will reference
the employee who completed the service.
If an incident is in progress, the duration field number denotes the number of minutes since the incident
started and the employee ID will reference the employee who is currently working on the service
(assume for now that all incidents will be completely serviced by the single employee who is assigned to
them.)
If an incident service is not yet started, the duration field will be blank, and there will not be an
employee assigned to that incident, either.
Let’s get started. One difference this week in preparing the database for use in SQLiteonline.com, is that
we have separated the database creation (Setup) and database population (INSERT) into two scripts,
two steps. You must create the database before inserting data.
Document1
Page 1
1. Open the “Helpdesk_setup.sql” in Notepad and run it (Forgot how? You can refer back to how
you used the SQL script and setup the database in Week 1 and 2 Mythical Creatures).
2. Open and run the “Helpdesk_insert.sql” script to insert data into the helpdesk database.
Document1
Page 2
3. Run the following counts to verify your helpdesk database is up and running properly:
a. SELECT COUNT(*) from employee; (Should be 7 records)
b. SELECT COUNT(*) from tickets; (Should be 30 records)
Document1
Page 3
Name: _________________________________
Part 2 –Queries with Join
This is where you start to turn things in. To turn in your assignment, delete the preceding pages and
start with this one. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below,
and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your SQLite database. (Do not use Excel
or other tools to answer these questions; since we are trying to learn SQLite here, you should do these
exercises in SQLite.)
You will eventually submit this document as part of your week’s assignment.
After you have completed your first draft of this document, you will use the Week 5 SQL Check online
quiz to verify your answers. The Week 5 SQL Check online quiz uses the same numbering system given
here, so you can quickly find the question you need.
You are welcome to update your assignment document with results from the online quiz, and submit
the document after you’ve checked your results with the Week 5 SQL Check.
Write SQL queries to answer the following questions:
1. [SQL Week 5 Question 500-001] Print the first name, last name, ticket ID, ticket description, and
duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by
first name (starting with letter A at the top) and then ascending by Duration.
2. [SQL Week 5 Question 500-002] We want to get a workload by employee printout. For every
employee, print the first name, last name, all ticket ID numbers assigned to that employee, the
ticket description, and ticket duration of our records. Include only tickets which have been
assigned to an employee. Include all employees, even if that person doesn’t have any tickets in
the current database. Sort them first alphabetically by first name (starting with letter A at the
top) and then ascending by ticket duration.
3. [SQL Week 5 Question 500-003]
4. [SQL Week 5 Question 500-004] Management wants to pay special attention to calls which are
in progress and for which the duration is currently 20 minutes or longer (include calls of exactly
Document1
Page 4
20 minutes duration). Print the ticket ID number, description, duration, priority, status, and the
employee first name for all of these calls.
5. [SQL Week 5 Question 500-005] You suspect there has been a malware breach at the Maryland
facility. They’re not sure if it’s an inside job (involving one of your employees) or an outside job
(involving an outside attack.) Make a listing of all high priority helpdesk tickets which have
either been identified as malware, or which were taken by an employee whose location is in the
Maryland facility. Print the description, duration, ticket ID number, ticket status, employee first
name, employee location, and priority of ticket. Sort it alphabetically by description, then by
duration (longest duration on top), then ascending by ticket ID number.
6. [SQL Week 5 Question 500-006] We want to see the total duration for completed helpdesk calls
by employee. Only include employees who have completed helpdesk tickets; if an employee
doesn’t have any completed tickets, their name should not show up in this report. Generate a
report for which the
o First column contains the employee’s first and last name (don’t use two columns for this
– put the first and last name together)
o Second column contains the total duration of all completed tickets by that employee. If
an employee had more than one ticket, add the durations together.
o Report is sorted so the largest total duration is on the top, and then alphabetically by
employee name (sort it using the “First Last” combination, so that “Mickey Mouse”
would come before “Nancy Mouse”.)
o Only include completed tickets; don’t include those in progress or not yet started.
o Hint: Be sure to review the Concatenation tutorial in the LEO classroom.
For example, assume your initial data was as follows.
Employee First Name
Mickey
Mickey
Donald
Donald
Donald
Donald
Employee Last Name
Mouse
Mouse
Duck
Duck
Duck
Duck
Ticket status
Completed
Completed
In Progress
Completed
Completed
Completed
Ticket Duration
5
10
15
20
25
30
Your report would contain the following information:
Employee Name Duration of
completed
tickets
Donald Duck
75
Mickey Mouse
15
Document1
Page 5
Note that Mickey Mouse has two calls here, both of which were completed, so their duration is
5 + 10 = 15. Donald Duck has four calls here, but only three of them are completed, so his
duration is 20 + 25 + 30 = 75 minutes; Donald’s 15 minute call is still in progress and is not
counted here. And Donald’s 75 minutes is the largest number of minutes, so it goes on top.
7. [SQL Week 5 Question 500-007] Do the same as Problem 6 above, except in addition to the sum
of the ticket durations, also include an additional column to give us the number of completed
tickets as well. Now sort your database so the largest number of completed tickets is on the
top.
Here’s a sample of the output for Mr. Duck and Mr. Mouse.
Employee Name Duration of
Total number of
completed
completed
tickets
tickets
Donald Duck
75
3
Mickey Mouse
15
2
8. [SQL Week 5 Question 500-008] You want to learn more about the average duration of all
tickets taken by employees in California. Include all tickets which have been completed or are in
progress by employees in California. Sort them alphabetically by priority, and then
alphabetically by ticket description. Additionally, group them first by priority (highest on top),
then alphabetically by description. So your final report will look something like this (your data
may vary):
Priority
1-High
1-High
2-Medium
2-Medium
2-Medium
Description
Malware
Virus
Internet
Malware
Virus
Average duration of tickets
99
12
5
8
3 etc.
9. [SQL Week 5 Question 500-009] Your management wants to know what the different types of
tickets are. Write a query that uniquely identifies ticket type based on the ticket description.
Document1
Page 6
Document1
Page 7
HELPDESK SETUP
DROP TABLE IF EXISTS employee ;
CREATE TABLE IF NOT EXISTS employee (
idEmployee INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
FirstName NULL DEFAULT NULL,
LastName TEXT NULL DEFAULT NULL,
Location TEXT NULL DEFAULT NULL);
DROP TABLE IF EXISTS tickets ;
CREATE TABLE IF NOT EXISTS tickets (
idTickets INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Description TEXT NULL DEFAULT NULL,
Duration INTEGER NULL DEFAULT NULL,
Priority TEXT NULL DEFAULT NULL,
Status TEXT NULL DEFAULT NULL,
Employee_idEmployee INTEGER NULL DEFAULT NULL);
HELP DESK INSERT
select * from employee;
INSERT INTO employee (FirstName, LastName, Location) VALUES
(‘Will’, ‘Smith’,’MD’),
(‘Ben’, ‘Johnson’,’MD’),
(‘Charlie’, ‘Jackson’,’MD’),
(‘Diamond’, ‘Lee’,’CA’),
(‘Elena’, ‘Gomez’,’CA’),
(‘Fabian’, ‘Williams’,’CA’),
(‘Giriraj’, ‘Patel’,’CA’);
select * from employee;
select * from tickets;
INSERT INTO tickets (Description, Duration, Priority, Status,
Employee_idEmployee) VALUES
(‘Malware’, ’33’,’3-Low’, ‘Completed’,’1′),
(‘Virus’, ’17’,’1-High’, ‘In Progress’,’7′),
(‘Hardware’, Null,’3-Low’, ‘Not Started’,Null),
(‘Malware’, ’28’,’1-High’, ‘Completed’,’7′),
(‘Virus’, ’14’,’2-Medium’, ‘Completed’,’5′),
(‘Virus’, ’20’,’2-Medium’, ‘Completed’,’2′),
(‘Virus’, Null,’3-Low’, ‘Not Started’,Null),
(‘Malware’, ’27’,’2-Medium’, ‘In Progress’,’7′),
(‘Malware’, ’18’,’3-Low’, ‘Completed’,’1′),
(‘Hardware’, ’63’,’2-Medium’, ‘Completed’,’7′),
(‘Malware’, ‘7’,’3-Low’, ‘Completed’,’2′),
(‘Malware’, ‘8’,’1-High’, ‘In Progress’,’2′),
(‘Virus’, ’40’,’3-Low’, ‘Completed’,’7′),
(‘Software’, ’42’,’3-Low’, ‘Completed’,’7′),
(‘Virus’, Null,’1-High’, ‘Not Started’,Null),
(‘Hardware’, ’12’,’3-Low’, ‘Completed’,’2′),
(‘Malware’, ’33’,’3-Low’, ‘Completed’,’2′),
(‘Virus’, ’53’,’2-Medium’, ‘Completed’,’4′),
(‘Malware’, ’15’,’1-High’, ‘Completed’,’2′),
(‘Hardware’, ’16’,’2-Medium’, ‘In Progress’,’7′),
(‘Malware’, ’16’,’3-Low’, ‘Completed’,’2′),
(‘Hardware’, ’11’,’3-Low’, ‘Completed’,’7′),
(‘Virus’, ’27’,’3-Low’, ‘Completed’,’7′),
(‘Malware’, Null,’1-High’, ‘Not Started’,Null),
(‘Internet’, ’36’,’2-Medium’, ‘In Progress’,’4′),
(‘Internet’, ’40’,’2-Medium’, ‘Completed’,’2′),
(‘Malware’, ’39’,’2-Medium’, ‘Completed’,’5′),
(‘Malware’, ’39’,’3-Low’, ‘Completed’,’2′),
(‘Malware’, Null,’1-High’, ‘Not Started’,Null),
(‘Hardware’, ’20’,’1-High’, ‘Completed’,’1′)
;
select * from tickets;
Week 7: Business Intelligence vs Data Analytics
Other than the fact that the title of the course contains both Business
Intelligence AND Data Analytics, these two concepts seem like separate
things, right?
Well, that’s what this week’s discussion is going to find out.
While we won’t address data visualization in depth in this course, data
visualization is never very far from any discussion of data analytics, so
I’d like everyone (ALL students, not just the initial post group) to get
acquainted with Google’s Ngram Viewer: Books Google.
Basically, the Ngram Viewer allows you to see a graph of the
occurrence of a given word or phrase over a specified period of years in
the entire corpus of books contained in the Google Books
collection. You can also enter several words or phrases to compare the
evolution of usage over time. The default (sample) ngram on the day I
visited was a comparison of Albert Einstein, Sherlock Holmes,
Frankenstein which resulted in
Your tasks and the discussion topic(s) this week: 1. All students should
use Ngram and try various combinations of
•
•
•
business intelligence,
data analytics, and
data science.
You might try allied terms such as
•
•
data analysis and
big data.
Be sure to change the years. You’ll definitely want to include the
current year, and I think you’ll quickly find that years before 1900
(sometimes later) don’t show much. Think about what the graph is
showing you regarding the usage of these various terms. Be aware that
even if you enter the current year, Ngram may change that year
depending on the data available (currently, it seems to default to 2008
as the most recent year available).
2. Initial post group: In addition to the Ngram exercise, members of the
initial post group should also conduct a web search for definitions of at
least business intelligence and data analytics. I’m sure you’ll find more
than a few sites willing to compare the two. The topic this week is as
follows:
Compare and contrast the terms business intelligence and data
analytics. That is, how are they similar? How are they different? You
should include in your initial post a brief discussion of the Ngram
findings and a brief discussion of your search for definitions.
Purchase answer to see full
attachment
![DescriptionIFSM 330 Business Intelligence and Data Analytics
Business Intelligence SQL Assignment Instructions
Student Name:
First of all, have a look at the SQL database creation and population code that accompanies this
assignment. You should be familiar with the fields and what kinds of data are represented in the
database. All questions and items in this assignment pertain to the SQL code that accompanies this
assignment.
You must include all SQL code here in the spaces below, respecting standard capitalization conventions
as presented in the tutorials. For ease of review, please use BOLD on any SQL code (see the example
below). Your SQL code must be in text format, so that the instructor can cut and paste for review and
testing purposes. Here is an example of SQL code/text that can be cut and pasted for testing:
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, weight REAL);
INSERT INTO customers VALUES (73, “Brian”, 33, 50);
For certain responses you are also asked to include a ‘snip’ / screenshot of your results. If you can, try
not to take an image of the entire screen, but only of the relevant output.
You should use sqliteonline.com to complete this assignment, both to test code and take screenshots:
https://sqliteonline.com/
Here is an example of a database schema image and a query results image from sqliteonline.com.
Due to screen resolution, it’s not always possible to capture all query results. But be sure your images
are legible, no smaller than the second image below. If an image is not legible, your assignment may
be returned to you and you’ll have to rerun the SQL scripts.
Feel free to take several screenshots as needed or make the columns narrower in the sqliteonline
output before taking the screenshot (columns can be manually made more narrow by hovering over
the lines marked in red below):
For some of these questions, you will need to perform a SUM or AVERAGE in a grouphttps://www.khanacademy.org/computing/computer-programming/sql/more-advanced-sqlqueries/pt/restricting-grouped-results-with-having
1). 10 points. As you glance through the database, you note that Houston is spelled with a lower case
‘h’ in at least one case. First of all, write the SQL code to list all values for Houston, capitalized or not
capitalized. Begin with SELECT * FROM sales to list all fields.
Then, write the SQL script to update all lower case instances of houston to Houston. NOTE: use of
WHERE id= is not permitted. This method is used in the tutorial but is not practical if you had a very
large database.
2) 10 points. Write an SQL query to list all products and the total sales in dollars for each product. List
products in order from highest total sales to lowest. Use Total_Sales as the label for the field (column)
containing the sales data.
INSERT a snip (image) of your QUERY RESULTS here:
3) 10 points. Write an SQL query to list the customer name and the dollar amount of total purchases.
List only names and dollar amounts if total purchases exceed $5000. List in order of total purchases
from highest to lowest and use the column titles Customer_Name and Total_Purchases.
INSERT a snip (image) of your QUERY RESULTS here:
4) 10 points. Write an SQL query to list the states (note Canadian provinces are also included in the
database) and the dollar amount of the average purchase. List only states (and provinces) when the
average purchase exceeds $2500. List in order of average purchase from highest to lowest and use the
column titles State_Province and Average_Purchase. Round the dollar amount of the Average_Purchase
so only whole numbers (no decimals) will appear.
INSERT a snip (image) of your QUERY RESULTS here:
5a) NOTE: For all of Q5, this must be an actual situation/scenario that can be executed with an SQL
query on the data provided in the assignment. NOT a hypothetical scenario or partial SQL query. If
this is unclear, please ask.
15 points. In 2-3 sentences, given the nature and structure of the furniturestore.sql data, describe a
situation or scenario (or question that might be answered) that would require use of a CASE statement
in SQL. (This is a very open question with many correct responses. Think about what the CASE
statement in SQL allows us to do.)
5b) Create the SQL query for the scenario presented in part a). This SQL query must be complete,
executable and produce output relevant to the scenario presented in part a).
INSERT a snip (image) of your QUERY RESULTS here:
6a) 15 points. You have been asked to produce a report that shows all chairs bought on January 12,
2019. In the box below, respond to the following question:
Why does the following SQL query not yield any results (feel free to test it)?
SELECT * FROM sales WHERE product = ‘Chair’ AND transaction_date = ‘1/12/19’;
6b) Rewrite the query so that it yields the correct results.
INSERT a snip (image) of your QUERY RESULTS here:
PART 2:
Part 2 introduces a second table to the database schema. When creating queries from two tables, in
particular when there may be a field that shares a common name, it is safer to construct your queries
with the table name AND field name. You may wish to review the “JOINing related tables” tutorial and
video on Khan Academy.
7) 5 points. Using an SQL script, add (CREATE) a suppliers table to the database schema, with an
autoincrement integer as the primary key (id), and fields for name and country (both text). The primary
key (id) will correspond to the supplier field in the sales table (e.g. supplier in sales is a foreign key).
8) 5 points. Using an SQL script, insert the following data into the suppliers table, in the order presented
in the table (Reminder: id primary key should have been set to autoincrement when the table was
created):
Name
Ikea
Rosewood
BoConcept
AllModern
Country
Sweden
Thailand
Denmark
United States
9) 10 points. Write an SQL query to list transaction date, customer name, product, price, supplier name
and country (6 columns) ordered by price from highest to lowest. List only items with a price greater
than 1500. Construct the SQL query to present field (column) titles as: Date, Customer, Product, Price,
Supplier, Country.
INSERT a snip (image) of your QUERY RESULTS here:
10) 10 points. Write an SQL query to list supplier name, country and total sales for that supplier.
INSERT a snip (image) of your QUERY RESULTS here:
Insert (copy and paste) all code from Items 7-10 within this textbox:
QVC-Jennifer Gary
Business intelligence (BI) is a set of strategies, processes, and tools used
to analyze data to inform business decisions. Companies like QVC often
use BI to gain customer insights, identify customer needs, and measure
performance. BI can be used in practice to identify opportunities and
find trends, optimize operations, and forecast future outcomes.
The data used for QVC’s BI implementation included customer data
(demographics, buying habits, etc.), product data (sales, competition,
etc.), and financial data (invoices, expenses, etc.). In order to fully access
the data and produce meaningful insights, QVC employed a
combination of on-premises and cloud-based technology from vendors
such as Microsoft, Tableau, and SAP.
The implementation of BI could be faced with various challenges, such
as ensuring data security, setting up reliable data pipelines, and tackling
data governance issues. Additionally, employees need to be able to
interpret the data and turn it into insights that can be easily assimilated
through tools like dashboards to inform decision making.
Other technology and vendors that QVC could consider using in
addition to Microsoft, Tableau, and SAP include:
IBM Cognos: This is a comprehensive business intelligence platform
that offers features such as data warehousing, reporting, analytics, and
dashboard creation.
Oracle Business Intelligence: This platform provides a range of business
intelligence capabilities, including data visualization, reporting, and
predictive analytics.
Looker: This is a cloud-based business intelligence platform that
enables data exploration and analysis, as well as the creation of
interactive dashboards and reports.
Amazon Web Services (AWS): AWS offers a range of cloud-based
services for business intelligence, including data warehousing, big data
analytics, and machine learning.
Google Cloud Platform (GCP): GCP offers a range of business
intelligence services, including data warehousing, big data analytics, and
machine learning.
The choice of technology and vendors will depend on a number of
factors, including the specific business intelligence needs of QVC, the
size and complexity of their data, and their overall IT strategy.
Autumn Fishel
What problem is business intelligence helping to solve?
Business intelligence can provide a deeper insight into trends related to human
capital within a company such as Microsoft. People-based data can then be
aggregated and with the help of data software, can be analyzed to allow Human
Resource Managers the opportunity to identify which data is the most
important and how to utilize it in determining how to best manage their
employees and reach the business’ organizational strategies (Microsoft, n.d.).
•
How is business intelligence used in practice?
Human Resources at Microsoft use analytics to develop statistical profiles of
the employees that are most likely to leave the company. The use of these
analytics provides the team with an opportunity to open further discussion
regarding growth and a total rewards compensation package within the
company. This ultimately leads to enhanced workplace communication amongst
the upper management and lower-level employees.
•
What were (are) the results (even preliminary or anticipated)?
The use of business intelligence and data analytics has given Microsoft the
ability to focus on what causes turnover within the company and how to reduce
attrition rates by improving the overall employee experience and catering to the
individuals motivation within the organization.
•
What data was used?
Both quantitative and qualitative data can be used to analyze, interpret, and
better understand the patterns and behaviors of their employees. Prior to 2020,
Microsoft would send out a lengthy survey to track employee engagement.
Although improvements would be made based on the data collected, the
employee experience bar was set low. The end goal of the Microsoft employee
survey was to ensure employees were thriving and had a sense of purpose.
Employee fulfillment would be brown down in the following five success
components (Klinghoffer & Elizabeth, 2022):
1. Pay
2. Perks
3. People
4. Pride
5. Purpose
•
Were there any specific challenges to be faced?
Human resource management can be tricky as there is no one-size-fits-all
solution. A business is only as successful as its human capital, and Microsoft has
paved the way to ensure employees are thriving and to keep listening, learning,
improving, and adapting to any changes that lie ahead.
Stitch Fix
Mary Fernando
•
What problem is business intelligence helping to solve?
Stitch Fix was established to provide customers with a unique
subscription service to shop for clothing online. It took the job of a
personal shopper to the next level. The online styling subscription
service eliminates the need for their customers to go out and shop for
clothing or even browse online because they deliver personalized
recommendations right to their door on a regular schedule (Marr,
2019). The challenge this new business venture faced was that the
company wants to reduce returns, keep repeat customers and generate
word-of-mouth business with recommendations from customers to
their friends and family. These are the problems that business
intelligence was helping to solve.
•
How is business intelligence used in practice?
Stitch Fix used BI to collect data at every point of interaction with their
customers. That feedback gets input into the company’s data vaults to
make the algorithms even better at determining the preferred style for
each person and even identifying trends (Marr, 2019).
•
What were (are) the results (even preliminary or anticipated)?
Personalized service to its customers – Through the use of BI to analyze
data, Stitch Fix was able to provide customized recommendations to
every customer. This helped minimize returns and keep repeat
customers by improving satisfaction rates. With the multitude of data,
they were able to collect Stitch Fix was also able to create its hybrid
design to better accommodate customer preferences.
Stitch Fix was founded in 2011 and went public in November 2017.
The company sold $730 million worth of clothing in 2016, $977 million
in 2017, and forecasts that it will sell $1.23 billion in 2018 (WBR, n.d.).
reported a customer base of 3.4 million in 2020 and revenues of $1.7
billion in the fiscal year 2020 (Morris, 2021).
•
What data was used?
Stitch Fix collects data within BI throughout the buying process,
meaning the more customer shops with Stitch Fix, the better the styling
team comprehends their taste in clothing. The company hired
astrophysicists to decode the different personal style components—
intricate work that would be impossible without the powerful analytics
of BI (Morris, 2021).
•
•
•
•
Style Profile: When a client signs up for Stitch Fix, we receive 90
different data points — from style to price point to size.
Feedback at checkout: 85% of our clients tell us why they are
keeping or returning an item. This is incredibly rich data, including
details on fit and style — no other retailer gets this level of
feedback.
Style Shuffle: an interactive feature within our app and on our
website where clients can “thumbs up” or “thumbs down” an
image of an item or an outfit. They can do this at any time — so
not just when they receive a Fix. So far, we’ve received an
incredible 4 billion item ratings from clients.
Personalized request notes to Stylists: Clients give their Stylists
specific requests, such as if they are looking for an outfit for an
event, or if they’ve seen an item that they like.
(Colaner, 2020)
•
What are the technical details (vendors, the platform used, etc.)?
Stitch Fix combines human expertise with Artificial Intelligence (AI)
systems. Their big data-backed approach is another strategic advantage,
especially when paired with machine-learning techniques to identify
customer insights and foster innovation. Not only does this technology
enable the company to increase the speed at which stylists can provide
recommendations, but it also enables new fashion styles to be born
entirely from data (Gregory, 2017)).
•
Were there any specific challenges to be faced?
There are many competitors online and offline now using the same
business model. Most importantly Amazon offers free service to its
prime members. Stitch Fix faces the challenge of becoming innovative
and maintaining higher customer satisfaction rates to retain its
customers.
•
Anything else of interest?
As with everything pandemic-related, Stitch Fix suffered a loss due to
most switching to work-from-home and hybrid work atmospheres. Now
as people are returning to in-person work Stitch Fix is once again trying
to capitalize and is planning a new venture. As the path to a postpandemic future becomes clearer, we wanted to share our approach at
Stitch Fix — what we’re calling, “work, restyled.” (Stitch Fix, 2021).
Name: Kehinde Badmus
Part 2 –Queries with Join
This is where you start to turn things in. To turn in your assignment, delete the preceding pages and
start with this one. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below,
and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your SQLite database. (Do not use Excel
or other tools to answer these questions; since we are trying to learn SQLite here, you should do these
exercises in SQLite.)
You will eventually submit this document as part of your week’s assignment.
After you have completed your first draft of this document, you will use the Week 5 SQL Check online
quiz to verify your answers. The Week 5 SQL Check online quiz uses the same numbering system given
here, so you can quickly find the question you need.
You are welcome to update your assignment document with results from the online quiz, and submit
the document after you’ve checked your results with the Week 5 SQL Check.
Write SQL queries to answer the following questions:
1. [SQL Week 5 Question 500-001] Print the first name, last name, ticket ID, ticket description, and
duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by
first name (starting with letter A at the top) and then ascending by Duration.
SELECT FirstName, LastName, idTickets, Description, Duration
FROM employee
JOIN tickets
ON employee.idEmployee = tickets.Employee_idEmployee
ORDER BY FirstName ASC, Duration ASC;
Document1
Page 1
2. [SQL Week 5 Question 500-002] We want to get a workload by employee printout. For every
employee, print the first name, last name, all ticket ID numbers assigned to that employee, the
ticket description, and ticket duration of our records. Include only tickets which have been
assigned to an employee. Include all employees, even if that person doesn’t have any tickets in
the current database. Sort them first alphabetically by first name (starting with letter A at the
top) and then ascending by ticket duration.
SELECT FirstName, LastName, idTickets, Description, Duration
FROM employee
LEFT JOIN tickets
ON employee.idEmployee = tickets.Employee_idEmployee
WHERE tickets.Employee_idEmployee IS NOT NULL
ORDER BY FirstName ASC, Duration ASC;
Document1
Page 2
3. [SQL Week 5 Question 500-003]
4. [SQL Week 5 Question 500-004] Management wants to pay special attention to calls which are
in progress and for which the duration is currently 20 minutes or longer (include calls of exactly
20 minutes duration). Print the ticket ID number, description, duration, priority, status, and the
employee first name for all of these calls.
SELECT idTickets, Description, Duration, Priority, Status, FirstName
FROM tickets
JOIN employee
ON tickets.Employee_idEmployee = employee.idEmployee
WHERE Status = ‘In Progress’ AND Duration >= 20
ORDER BY Duration DESC;
Document1
Page 3
5. [SQL Week 5 Question 500-005] You suspect there has been a malware breach at the Maryland
facility. They’re not sure if it’s an inside job (involving one of your employees) or an outside job
(involving an outside attack.) Make a listing of all high priority helpdesk tickets which have
either been identified as malware, or which were taken by an employee whose location is in the
Maryland facility. Print the description, duration, ticket ID number, ticket status, employee first
name, employee location, and priority of ticket. Sort it alphabetically by description, then by
duration (longest duration on top), then ascending by ticket ID number.
SELECT Description, Duration, idTickets, Status, FirstName, Location, Priority
FROM tickets
JOIN employee
ON tickets.Employee_idEmployee = employee.idEmployee
WHERE (Description = ‘Malware’ OR Location = ‘MD’) AND Priority = ‘1-High’
ORDER BY Description, Duration DESC, idTickets;
6. [SQL Week 5 Question 500-006] We want to see the total duration for completed helpdesk calls
by employee. Only include employees who have completed helpdesk tickets; if an employee
doesn’t have any completed tickets, their name should not show up in this report. Generate a
report for which the
o First column contains the employee’s first and last name (don’t use two columns for this
– put the first and last name together)
o Second column contains the total duration of all completed tickets by that employee. If
an employee had more than one ticket, add the durations together.
o Report is sorted so the largest total duration is on the top, and then alphabetically by
employee name (sort it using the “First Last” combination, so that “Mickey Mouse”
would come before “Nancy Mouse”.)
o Only include completed tickets; don’t include those in progress or not yet started.
o Hint: Be sure to review the Concatenation tutorial in the LEO classroom.
For example, assume your initial data was as follows.
Employee First Name
Mickey
Mickey
Donald
Donald
Donald
Employee Last Name
Mouse
Mouse
Duck
Duck
Duck
Ticket status
Completed
Completed
In Progress
Completed
Completed
Document1
Ticket Duration
5
10
15
20
25
Page 4
Donald
Duck
Completed
30
Your report would contain the following information:
Employee Name Duration of
completed
tickets
Donald Duck
75
Mickey Mouse
15
Note that Mickey Mouse has two calls here, both of which were completed, so their duration is
5 + 10 = 15. Donald Duck has four calls here, but only three of them are completed, so his
duration is 20 + 25 + 30 = 75 minutes; Donald’s 15 minute call is still in progress and is not
counted here. And Donald’s 75 minutes is the largest number of minutes, so it goes on top.
SELECT FirstName || ‘ ‘ || LastName AS “Employee Name”, SUM(Duration) AS “Total
Duration”
FROM tickets
JOIN employee ON tickets.Employee_idEmployee = employee.idemployee
WHERE Status = ‘Completed’
GROUP BY idEmployee
ORDER BY “Total Duration” DESC, “Employee Name”
7. [SQL Week 5 Question 500-007] Do the same as Problem 6 above, except in addition to the sum
of the ticket durations, also include an additional column to give us the number of completed
tickets as well. Now sort your database so the largest number of completed tickets is on the
top.
Here’s a sample of the output for Mr. Duck and Mr. Mouse.
Employee Name Duration of
Total number of
completed
completed
tickets
tickets
Donald Duck
75
3
Mickey Mouse
15
2
Document1
Page 5
SELECT FirstName || ‘ ‘ || LastName AS “Employee Name”, SUM(Duration) AS “Total
Duration”, COUNT(idTicket) AS “Number of Tickets”
FROM tickets
JOIN employee ON tickets.Employee_idEmployee = employee.idemployee
WHERE Status = ‘Completed’
GROUP BY idEmployee
ORDER BY “Number of Tickets” DESC, “Total Duration” DESC, “Employee Name”
8. [SQL Week 5 Question 500-008] You want to learn more about the average duration of all
tickets taken by employees in California. Include all tickets which have been completed or are in
progress by employees in California. Sort them alphabetically by priority, and then
alphabetically by ticket description. Additionally, group them first by priority (highest on top),
then alphabetically by description. So your final report will look something like this (your data
may vary):
SELECT
tickets.Priority,
tickets.Description,
AVG(tickets.Duration) AS Duration
FROM tickets
INNER JOIN employee
ON tickets.Employee_idEmployee = employee.idEmployee
WHERE employee.Location = ‘CA’
AND (tickets.Status = ‘Completed’
OR tickets.Status = ‘In Progress’)
GROUP BY tickets.Priority,
tickets.Description
ORDER BY tickets.Priority, tickets.Description
Document1
Page 6
Priority
1-High
1-High
2-Medium
2-Medium
2-Medium
Description
Malware
Virus
Internet
Malware
Virus
Average duration of tickets
99
12
5
8
3 etc.
9. [SQL Week 5 Question 500-009] Your management wants to know what the different types of
tickets are. Write a query that uniquely identifies ticket type based on the ticket description.
SELECT DISTINCT Description as “Ticket Type”
FROM tickets;
Document1
Page 7
Help Desk SQL Assignment
We are going to follow the same study plan as last week. You will:
• There are two parts to this document.
o Part 1 doesn’t have anything to turn in but contains important directions
o Part 2 contains the assignment you do need to turn in
• Go through this document and write SQL to answer all the questions listed below. As part of
your SQL, you will capture screenshots and paste them where indicated in Part 2.
• Refer to this document as you complete the Week 5 SQL Check online quiz which verifies your
homework. You may attempt the Week 5 SQL Check twice. Your highest score will be posted to
the Grades area. The questions may change from one attempt to the next.
• You are welcome to use the quiz results to update your homework file.
• When you have completed the online quiz, submit the Word document.
Part 1 – Set up your Database
We are now working with a database which notes helpdesk action.
There are two tables. First, there is the employee table, which contains the employee’s ID as the
primary key, first name, last name, and location. You can assume all employees at our company are
listed here.
Second, there is the ticket table, which contains each individual helpdesk ticket. The ticket table
contains the ticket ID as the primary key, a description of the helpdesk incident, the duration in minutes,
a priority, a status, and which employee (if any) has been assigned to the ticket. If nobody is available, a
ticket will be logged into the system in this table, but no employee will be assigned yet.
The incident status can be completed, in progress, or not started. If an incident is completed, the
duration field will contain the number of minutes the incident took and the employee ID will reference
the employee who completed the service.
If an incident is in progress, the duration field number denotes the number of minutes since the incident
started and the employee ID will reference the employee who is currently working on the service
(assume for now that all incidents will be completely serviced by the single employee who is assigned to
them.)
If an incident service is not yet started, the duration field will be blank, and there will not be an
employee assigned to that incident, either.
Let’s get started. One difference this week in preparing the database for use in SQLiteonline.com, is that
we have separated the database creation (Setup) and database population (INSERT) into two scripts,
two steps. You must create the database before inserting data.
Document1
Page 1
1. Open the “Helpdesk_setup.sql” in Notepad and run it (Forgot how? You can refer back to how
you used the SQL script and setup the database in Week 1 and 2 Mythical Creatures).
2. Open and run the “Helpdesk_insert.sql” script to insert data into the helpdesk database.
Document1
Page 2
3. Run the following counts to verify your helpdesk database is up and running properly:
a. SELECT COUNT(*) from employee; (Should be 7 records)
b. SELECT COUNT(*) from tickets; (Should be 30 records)
Document1
Page 3
Name: _________________________________
Part 2 –Queries with Join
This is where you start to turn things in. To turn in your assignment, delete the preceding pages and
start with this one. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below,
and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your SQLite database. (Do not use Excel
or other tools to answer these questions; since we are trying to learn SQLite here, you should do these
exercises in SQLite.)
You will eventually submit this document as part of your week’s assignment.
After you have completed your first draft of this document, you will use the Week 5 SQL Check online
quiz to verify your answers. The Week 5 SQL Check online quiz uses the same numbering system given
here, so you can quickly find the question you need.
You are welcome to update your assignment document with results from the online quiz, and submit
the document after you’ve checked your results with the Week 5 SQL Check.
Write SQL queries to answer the following questions:
1. [SQL Week 5 Question 500-001] Print the first name, last name, ticket ID, ticket description, and
duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by
first name (starting with letter A at the top) and then ascending by Duration.
2. [SQL Week 5 Question 500-002] We want to get a workload by employee printout. For every
employee, print the first name, last name, all ticket ID numbers assigned to that employee, the
ticket description, and ticket duration of our records. Include only tickets which have been
assigned to an employee. Include all employees, even if that person doesn’t have any tickets in
the current database. Sort them first alphabetically by first name (starting with letter A at the
top) and then ascending by ticket duration.
3. [SQL Week 5 Question 500-003]
4. [SQL Week 5 Question 500-004] Management wants to pay special attention to calls which are
in progress and for which the duration is currently 20 minutes or longer (include calls of exactly
Document1
Page 4
20 minutes duration). Print the ticket ID number, description, duration, priority, status, and the
employee first name for all of these calls.
5. [SQL Week 5 Question 500-005] You suspect there has been a malware breach at the Maryland
facility. They’re not sure if it’s an inside job (involving one of your employees) or an outside job
(involving an outside attack.) Make a listing of all high priority helpdesk tickets which have
either been identified as malware, or which were taken by an employee whose location is in the
Maryland facility. Print the description, duration, ticket ID number, ticket status, employee first
name, employee location, and priority of ticket. Sort it alphabetically by description, then by
duration (longest duration on top), then ascending by ticket ID number.
6. [SQL Week 5 Question 500-006] We want to see the total duration for completed helpdesk calls
by employee. Only include employees who have completed helpdesk tickets; if an employee
doesn’t have any completed tickets, their name should not show up in this report. Generate a
report for which the
o First column contains the employee’s first and last name (don’t use two columns for this
– put the first and last name together)
o Second column contains the total duration of all completed tickets by that employee. If
an employee had more than one ticket, add the durations together.
o Report is sorted so the largest total duration is on the top, and then alphabetically by
employee name (sort it using the “First Last” combination, so that “Mickey Mouse”
would come before “Nancy Mouse”.)
o Only include completed tickets; don’t include those in progress or not yet started.
o Hint: Be sure to review the Concatenation tutorial in the LEO classroom.
For example, assume your initial data was as follows.
Employee First Name
Mickey
Mickey
Donald
Donald
Donald
Donald
Employee Last Name
Mouse
Mouse
Duck
Duck
Duck
Duck
Ticket status
Completed
Completed
In Progress
Completed
Completed
Completed
Ticket Duration
5
10
15
20
25
30
Your report would contain the following information:
Employee Name Duration of
completed
tickets
Donald Duck
75
Mickey Mouse
15
Document1
Page 5
Note that Mickey Mouse has two calls here, both of which were completed, so their duration is
5 + 10 = 15. Donald Duck has four calls here, but only three of them are completed, so his
duration is 20 + 25 + 30 = 75 minutes; Donald’s 15 minute call is still in progress and is not
counted here. And Donald’s 75 minutes is the largest number of minutes, so it goes on top.
7. [SQL Week 5 Question 500-007] Do the same as Problem 6 above, except in addition to the sum
of the ticket durations, also include an additional column to give us the number of completed
tickets as well. Now sort your database so the largest number of completed tickets is on the
top.
Here’s a sample of the output for Mr. Duck and Mr. Mouse.
Employee Name Duration of
Total number of
completed
completed
tickets
tickets
Donald Duck
75
3
Mickey Mouse
15
2
8. [SQL Week 5 Question 500-008] You want to learn more about the average duration of all
tickets taken by employees in California. Include all tickets which have been completed or are in
progress by employees in California. Sort them alphabetically by priority, and then
alphabetically by ticket description. Additionally, group them first by priority (highest on top),
then alphabetically by description. So your final report will look something like this (your data
may vary):
Priority
1-High
1-High
2-Medium
2-Medium
2-Medium
Description
Malware
Virus
Internet
Malware
Virus
Average duration of tickets
99
12
5
8
3 etc.
9. [SQL Week 5 Question 500-009] Your management wants to know what the different types of
tickets are. Write a query that uniquely identifies ticket type based on the ticket description.
Document1
Page 6
Document1
Page 7
HELPDESK SETUP
DROP TABLE IF EXISTS employee ;
CREATE TABLE IF NOT EXISTS employee (
idEmployee INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
FirstName NULL DEFAULT NULL,
LastName TEXT NULL DEFAULT NULL,
Location TEXT NULL DEFAULT NULL);
DROP TABLE IF EXISTS tickets ;
CREATE TABLE IF NOT EXISTS tickets (
idTickets INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Description TEXT NULL DEFAULT NULL,
Duration INTEGER NULL DEFAULT NULL,
Priority TEXT NULL DEFAULT NULL,
Status TEXT NULL DEFAULT NULL,
Employee_idEmployee INTEGER NULL DEFAULT NULL);
HELP DESK INSERT
select * from employee;
INSERT INTO employee (FirstName, LastName, Location) VALUES
(‘Will’, ‘Smith’,’MD’),
(‘Ben’, ‘Johnson’,’MD’),
(‘Charlie’, ‘Jackson’,’MD’),
(‘Diamond’, ‘Lee’,’CA’),
(‘Elena’, ‘Gomez’,’CA’),
(‘Fabian’, ‘Williams’,’CA’),
(‘Giriraj’, ‘Patel’,’CA’);
select * from employee;
select * from tickets;
INSERT INTO tickets (Description, Duration, Priority, Status,
Employee_idEmployee) VALUES
(‘Malware’, ’33’,’3-Low’, ‘Completed’,’1′),
(‘Virus’, ’17’,’1-High’, ‘In Progress’,’7′),
(‘Hardware’, Null,’3-Low’, ‘Not Started’,Null),
(‘Malware’, ’28’,’1-High’, ‘Completed’,’7′),
(‘Virus’, ’14’,’2-Medium’, ‘Completed’,’5′),
(‘Virus’, ’20’,’2-Medium’, ‘Completed’,’2′),
(‘Virus’, Null,’3-Low’, ‘Not Started’,Null),
(‘Malware’, ’27’,’2-Medium’, ‘In Progress’,’7′),
(‘Malware’, ’18’,’3-Low’, ‘Completed’,’1′),
(‘Hardware’, ’63’,’2-Medium’, ‘Completed’,’7′),
(‘Malware’, ‘7’,’3-Low’, ‘Completed’,’2′),
(‘Malware’, ‘8’,’1-High’, ‘In Progress’,’2′),
(‘Virus’, ’40’,’3-Low’, ‘Completed’,’7′),
(‘Software’, ’42’,’3-Low’, ‘Completed’,’7′),
(‘Virus’, Null,’1-High’, ‘Not Started’,Null),
(‘Hardware’, ’12’,’3-Low’, ‘Completed’,’2′),
(‘Malware’, ’33’,’3-Low’, ‘Completed’,’2′),
(‘Virus’, ’53’,’2-Medium’, ‘Completed’,’4′),
(‘Malware’, ’15’,’1-High’, ‘Completed’,’2′),
(‘Hardware’, ’16’,’2-Medium’, ‘In Progress’,’7′),
(‘Malware’, ’16’,’3-Low’, ‘Completed’,’2′),
(‘Hardware’, ’11’,’3-Low’, ‘Completed’,’7′),
(‘Virus’, ’27’,’3-Low’, ‘Completed’,’7′),
(‘Malware’, Null,’1-High’, ‘Not Started’,Null),
(‘Internet’, ’36’,’2-Medium’, ‘In Progress’,’4′),
(‘Internet’, ’40’,’2-Medium’, ‘Completed’,’2′),
(‘Malware’, ’39’,’2-Medium’, ‘Completed’,’5′),
(‘Malware’, ’39’,’3-Low’, ‘Completed’,’2′),
(‘Malware’, Null,’1-High’, ‘Not Started’,Null),
(‘Hardware’, ’20’,’1-High’, ‘Completed’,’1′)
;
select * from tickets;
Week 7: Business Intelligence vs Data Analytics
Other than the fact that the title of the course contains both Business
Intelligence AND Data Analytics, these two concepts seem like separate
things, right?
Well, that’s what this week’s discussion is going to find out.
While we won’t address data visualization in depth in this course, data
visualization is never very far from any discussion of data analytics, so
I’d like everyone (ALL students, not just the initial post group) to get
acquainted with Google’s Ngram Viewer: Books Google.
Basically, the Ngram Viewer allows you to see a graph of the
occurrence of a given word or phrase over a specified period of years in
the entire corpus of books contained in the Google Books
collection. You can also enter several words or phrases to compare the
evolution of usage over time. The default (sample) ngram on the day I
visited was a comparison of Albert Einstein, Sherlock Holmes,
Frankenstein which resulted in
Your tasks and the discussion topic(s) this week: 1. All students should
use Ngram and try various combinations of
•
•
•
business intelligence,
data analytics, and
data science.
You might try allied terms such as
•
•
data analysis and
big data.
Be sure to change the years. You’ll definitely want to include the
current year, and I think you’ll quickly find that years before 1900
(sometimes later) don’t show much. Think about what the graph is
showing you regarding the usage of these various terms. Be aware that
even if you enter the current year, Ngram may change that year
depending on the data available (currently, it seems to default to 2008
as the most recent year available).
2. Initial post group: In addition to the Ngram exercise, members of the
initial post group should also conduct a web search for definitions of at
least business intelligence and data analytics. I’m sure you’ll find more
than a few sites willing to compare the two. The topic this week is as
follows:
Compare and contrast the terms business intelligence and data
analytics. That is, how are they similar? How are they different? You
should include in your initial post a brief discussion of the Ngram
findings and a brief discussion of your search for definitions.
Purchase answer to see full
attachment](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAyAAAAGQAQMAAABs65Z3AAAABlBMVEX/8P8AAADXkkSvAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAX00lEQVR4nO2d32sbWZbHT21B1ct16m25Qo70FwxcUWAnRLF2n/ZfWJiHrUIgv8iJh0Dw0B65jKD8sEr8KtPZ8cP+AavQ4E7DQJe4bOlhK/arGoeJjFnlxc2W8U5G3lY7e84tSZadpGNPx8PuTH3pdqz69dG95xb+3qNzSwCpUqVKleojKqmfAsAA0E+GQwl5a93TYwCWHDDE/03dCyZnMLUJALfcwXOuAil4CYQjBKTmv4FCUwY6n0BwF+jaBUhI+7Qe4B52JYgYnc1BQbx3+BYrjs7OD0gg52cwkATRhYJchQGis3FwsrL8qnbLfBIgZB80LxJ6xq/NzpsbhzGIVzVL19bb/73yKm+B3u8dgcyXzMDILq9y91tTx2M+CYkiOxOJPjfYriPj4T4GoCH0Y1+yu41ojgPuytQ1T85G/cKWZ8wXnoEsNJhjZJshdxu6Mcc/DelWDc0Tkhu8KiQYk5YEjHbhAZILbKHM+NLBvp3LYndpDUAIMwjCjCt01+s3/WmIxJhUhT7j1xjuou6SvKQgZ9IpBeZbgpQaVs/8gZ0piH6F7urC3GUIBn7G17ElILhqCQa+fexhS4CplrgNji2Z8RUEPt1dhUbVzniin/FZhN3iy9EQ3pVMRFWKicw0qSUQykIzYI8zzyAsN7o9hDzldkNeJSYls3Nw7A1fWfmNhn0Ck5vxca0kOh3srqG0rI26PB6syrzlmTv8CIb3NzYpJm+Ltzc25RVG10jBpw7QP3hH/L13VcCVINrgQ1tL12GkSpUqVapUqRLRH9BA/WbRP8t3xn/LazkBZIHF+Mjh+JfJliuLXH0C0emago8h0hagB+NL0q6R0Q+uDxm7+glk/DJwW2A441fTkGsz0MotHsjllVoJ9JlOLxZ8Nv8PT9pxrCDMPh4s/+bBLzaedHpo70ulbFQ4iZeLD/LetSAlcqfNSLqefstdb2J3FaLdcqYJNYKgKRWdR3PRrost6buNW1WbB6Ly6Lz9V2xJFeSMHzho3wp1RpBmdQ6tnK4gOojQ3elWMXRCOoyxOYyJxF3Xkugu9uTMGXUXQoYE2T6a04egL7XAOjkeEOT1kYKUJpBt75oQR8gZD307QWh05Tk1APRFbAm+FKvYEqYgWmMMmZpCXkWFxpGQLKSYsMJWg67K0Oc31BBWMQncnahaaCLEbUTGIx6Jitu6HqRkLh6esFXsLisS8WClWMpvRIV4gDfjtx6NrmHN3engLrT3JbOhP49XhsXrdtf7ujxy5M+83gd1+R6o3QQkVapUqVKl+msW/bFV/veapvBaEqGCCLhzkxBjBCneJCTfKHSPXwyh2PkPt3dDkLzP7LtbkYC5aOmr609vrqgQveiMj5BgaefTaeSfAfkRIfbe919/MC37uSAaQgrdpZ2bYoiw8ujuE99GyPdf3VR35evyeffUz4HYW3p+5ax7qlSpUqVKlSoVqjRKzFkA+nowqU/pTeXPIb/RSLLbUw5zfGSgfp6X4tD2i6UZeKLQkyMzHu4/hzjqiriv7amyEzpWTEGMi5DzUhzcrhvq/GkIXAHCq+9Bxr9MIOctvAShHjBnDk9iKHIw2wh5seBugt4DZ6Vog7kX6McvignkaHWYnzc7Bz1Y/X3O2q6vHy7DwoNbwUz78Fc+z5dMPcRdxdubYIW90wHkrQ35718tr76axZjo5hwPoILvxUVIVHa3PEOAE1XmgJWxJVEF8s0qaM9CUfimUbUFhI9sHmp1uwnlR5ngVtne8nmhoRuLuKtib3m6WWg4UGhGlcp+M+yrmBgGtlnWAAji6zbO6wS4viwCEMTHkdGtgs4MobWCqsE1z3G9UPOzKp8fqEkB1xo6m8NdErfoBmcOaC3O5vaZIROIHiOEg3VAkDW7FJhxArEOCfIOYJ8gZ6LU2nvzZoCQte0EsjaBlCaQbxGSGTpQSiBnIwhwBeGqJTIngKmWzAFXLcHQfncEOs5h3FbXmcPQOo5IIHICcc9bgoHPYktcBZnxRxCDRxQTrlrSyfGALVNMbOCHAl76ZTWEtX95ape/aR7ZHEKn8Dx06zaDjrtFMWnu8nJDGngNjEkz0OeysxUoq5hsPVUQK9TjFRpdVrnd0/wvmOWZLYhXigXPwknkr/0FvBn/rQdHb3P3v7YWD2JYdfI52Tk4ZPCF+5RG1/Lj4v2NTRlyNbo8rZ85KsL9zQ0Z7i+/lZ+jtoX9VNndlYrl/q9AUqVKlSpVqr84BaN/jSRpj4Y2YLSRamOc5SQxGYyrcWIH7iSWmSWWV8ef9GdW6324Nl2bhhjnEEggVBvjjMplAnTenPhNB/gUxJhAdKFM+SU5Y8ioHapU/jJkctoEAu9B4FMQa7Nz0P7Di53cfN7Tesc+lObNhgiO4FUODP0/Wz1xJz/vsYI8fgG1edPTvN4Kv73tJQX2Dyz8TRy8uFcCs544fzBfHvS03xUfzG9sHBz+alC8je+qWxXydrRv3/Xx3W754H7TiET7GfRtMIy5LnZX4VnARNCOgN9tYGiciNutICmwf8RD0B03WnQ9JhPn77FFdP79yqPZKLLtpoNOFB07WtqMv++2QqBSHAwCYcnnenlPQbCHZlpB28fu6jpmz0Xn2wJVYL+P9h6HBxpnDNTDQDl/DyoY20C6TV4lUyxdnA/udXoEWRtDSmPImmeYbwlSOoe87mmee8bdbU8V2O+72yNIidy5cv6e1TcHELxzm/k3b35gzjuXPmVyRPvY33fECKK1RhAMKlMtcadagp7e9cj/s6QlLdVdvtQ8gpDz93gFp3NBgC2BOZ05AUIKdEnN3y+0QgfazV20/FG4TDEpeAZ7TJByE6w4eOlDphU5GJOQYqIK7N+5z0NHF64/igk5f4/3deyu8iPOqnaWVcoYk9xe5/D41N/PCxlAED/G7urUn+PIeZUHw9z5brBSu98Eoxz/2gdLdAJtfYDWfTspsK+4v5WBnjn1x6MLnb9nVbQYgoUHHCeYJ6y4cDsZyx+umf+Zci69/nDN/GeGpEqVKlWqVKkmCsapL11fD5JNU3nuZIsy7R4fb5zWOG1G+fD42BsdML0mVUH00YHm+qie2ZiGBOMrtS9CknM4jFL95MWNzNYYQu78gxDWHkMm+5ypKvr2xTc3hsAURJ/5CORWZ699srxay6m8MDyWubzKww8hP286moxXdtBTzVDS/g7+0jmMe//z6pdWdjPQl1d+X7M2cFe8ogy/guQts0wHL9TAlAfxcRX9G2Sisp5thtJOILvSFpSHR3tf+KbRw+6K9qnkHt1lhAew6hx3n/aXtp5J0JvRI84l7goSw6+fIKSw1ejTwWUOrGJntnb6BfLCZcgyI0gcruZLt2VofhYPR1OM57fRJqP7E9D2OfZD1QC3IZdaXeV8HQ5S7VKGH+gsNSnALbQGuopt25E4CRJ7h/oP7Ky25uVVhluubfuUIh9CKYGc7ZfGkBJYbw5jgnyrIGcOz08gr3uQXVKQhzFuWeNgHSlIierqy7TuVXfGuXoHVB4e33EC8fap5F5BcBSj/3YG2BLl4T2Hi/OWAGTvJy1Rn1Vw4ElL0NVnuodz97aeyoKXxGQR7b1bt3EOUW5FGJMopJgwStrjAfwIY+LJpWa3LPRm6GSeEySKlOFXQ5hiQgd3MmjvVUzKOLr23MP95be1PM5i2j14fC+fr1Mefgj3Wx0HB87qviq5h1/7VHu/eHg8GMqlzabb05dXHSu3h7tWVpTh1+M/DNXoogz/FxZYZRxdO6/uw2VdYc2z5k/fKZ9U8P6mK0DG63b+dEiqVKlSpUqV6s/zFzKBXOXP+8/WDUKmc/U3BrmQq78xTefqb0oXcvU3pQu5+pvShVz9TelCrv4mdSO5+su6kVx9qlSpUqVKlepjmn5yDJXTqwqU9QCGRYc28rEdp5/O+WkctACmX05dZHyiqdLxfArCR/uT3H+g+ZX3IOICZHrC4SgIXfMcMnpUpdqV8abfyznEk87li/0EBN6DjN518sIyG+JvvvNrNTBn2rHr38slBfYEMWW7G8Or3PyGp8llGD6wwOz3BqvFEmTrQXDsw0PLetnunQ7uPMj/nfmmd/riXn5+w9Hi0xd3alZdr8e4K4ezEhYJfYlyxfqtMnf9RVsV2MMQIaxSvtuEvj0bBSCbIB5lPDZfaIQV15sNHSrXubvVfFAuNBz+qBCwwvqTaLHwLOrpmScR51xqHu2yqdyqSpAAIWyOitBdjwrswUAIHz39sckdkAwE1Z4bfIYaSY/nmdkBIbp4DHa8+9xjmjfjSw0PNmDGx5ggxGG4awKpTSBrHhXYg7GfQIYg15r5XgL5NjD7mR+9dyXgD2MFKe1F8z8kEPNg/Udflgii/YiQPELaw2mIPoEkSXsw9hIIgHTwXY1aQiVEMxQxTs8AopZAdU4nyDZ69zq2hJpt6NQS9PHr9VFLWLisV3dlxtOZzZ3dxYKXFNhXkpg0QBaaTHWX7TYDNm/PhmWKiWhvnYHg3QflTKNCJUys8OVtf7HcZD09ezvimedSq+uzFRUTs97Sq49rFlhRIY4f38urAnsY3l9To2sAMs/NQD9hkHM3PbN/cLS6QKProBdHIKzNl+14UKxRCVPv+Hv/3v2m6WjH37+oWbnOxuHxUbGWg2vrvTny+Gm0wfTGy5/OfSbIxbnzz4SkSpUqVapUf0Xiqpp8yqKTdX93vt/06KeqONe9yVY9XgnOn1OnltZOfZAeXCAYIwjjaleyjjW4cBQLJpCpv+I6j4Ly5JVaWnsOcT4AgWkIfBgClyEzfuBOXqpVr5cg1mZgHK6s1HLz+Xq7C8uvoISOXT/2h2jm9V57AEFu3tPbx4MVyDGYOThky0XXy66v9wbDIog9Kn0miPkkgNMXMoEMVnB2YMl2Txu8ylGu3jELUSTtu75RvgOiD2yWqrl9Yc9KgyrGh/ZsYJSzLAKbgWnbTJCrb9cLT/wKiG7FwXeEELbraE8imSytbUQ4O+CVsgCnr+rqwcis41GtkKnHrWN3qRIa4TZ1egmB2wQmspoP7rdgGJmmkEseTjLI3iOEiaQldNbMeGktVfJ4nC44qqsPEHJWW2uF1tsRRJXQEMSMqbsQYp2cnp6B20BXe9oU7wiycTocvsPu2ouTmCAEzbxMltZqanaw98cBQtZUrh4hnu6IEOceo5bACMIEraygltC0REEMiEWgWqIzA4dHUIUJRFctoaW1qpKHCo8Q4lCuHmPihbLQCiv2HQXpVgREu7bNdbaMMQlsDsbcPRZSTAydx6K8RjHJ3vbLkMGDkyFMMzZ09cnSWowJzg4qeGZFFihXHxgH3motL6Q86NLz6s1muQcrj3M5bnWeH9DowrnL4XeDVRpdhhY7wwUbXX37+Gi4ALf2ZA9vxjOccjaEhq4+WVqLowtnB/IghqLMj2+Qa+tjz/2pf+yEzwlJH4OZKlWqVKlSfVoi8efqSThThfFKfOp3WJk6SW0kt/uxD/In21dGEG0CmSqMV4gRhKsLKq+e2AFB61e1enIxPbnKxeuz8cHRNKSitp4Xxk8g8JMQuBpk3lxfP2RQzIn94LwwPsneo72HgxdQs/jDAIKV3/+S1sbGMSXttz3zcHkFZuetvfCErQ7J+R+cLMMC+mW1kPZVPr9dV2ch5G6jTU9O4bbYdc4L45PsPdl7NwKeaZDvjh4tNUPsUkra8xBb0oyA3W2W9S9ZKMj529kmlMnK0kLaPlpSg0ruH1FDu66frQJ3W9iySWF8kr0no+5SpXmnDjg/cJY8hOiU6vYIMuOj2+/qOrZfkF+GLBnnBoy+qaplMPrKLOp78fpgCjIujE+y92TvCVKS9IAihNDa2BOCbCvIGUL25ASi/5BARt9Ute1bNDtQkK42BRkXxifZe7L3qiW0fpQgVAqUVUl7BfEQEpQnEGPUktFnFV7IYAS523Dr9hBjkongvDA+yd7Phqq7Ms1ICuxdZ+l56FBMbFokq2EoqLvKtvYstEfOH8p2I1lI2y/jNEGdpUaXbB80geduRXBeGJ9k77P1nnbqg2U13R6OE2eJ1sbi6MrRIlnAQQUlsdc+hKPVnHL++wwWbm8kC2lf3d+uS3XWR+7Xz6HgBq/954WkSpUqVapU/4/lXHjF1cvptJlJP4pQstTGcRJukuSeznaP/jXoUZmJmPpP8z4FUadWwN1qJhfg4+dR8imIPvmBrw1VdaMezjOGXGwXv8g8hzi0MvLaEKDvCdp40zt48RuY50v9Xjws0uNZQjRXtDb2ZQDH/sIv53U9oEcFEUSL48dF5fPHS2L7Pe1FEazmQ8rV30Mj13nTix8Dt7KBiRcczmJLWFTw3KgDd5uL84Utv+J6+X4Z+4XWxj5wtGZUXvqmbjj0qKASQvRMc7eS+PzRkth5oUUV4N0yaE+iRbSk1cJ6cxfnAk8dbX3LV93Fq5rn+iGl9o3sqLrGpu9ZxU5De89W9KWWRH8+M6D1szRRwlkF+fxkSSyHOU5fsSro4ZGU4RZBVRn+moiUX1aQ/JuDcwgbquqaEx3dcQmsI6EPxRpCrFinJ2LuJJB3Y5+/hhDzLULeKYjK1Yu9Nwf1H8mmE2Q4TFoC2F3+6ghiqOoaXVdPOKL0O6xIagkoyFcJJBj7fFoSSw+09AMFUbl60XUKdW0MAUNB6BumXDzqbqNvYkzK9KQcW2uAWhsrtEbUoZgIigkNYYpJOfH5akksqz7mWlQGHh0mufq7zSOcGFBMGqBtPfFxGgfmRqd3fOrXcFRUDBwMCzS6DmEAtDYWZ1qn/hdLX5v1Ho2ukvVPAY2uhcTnqyWxZmeHay8WwNpweypXP28t9uIBjS68ejwY5s6fbClHXyj9QV3rSxJ+4uDanwOSKlWqVKlSpVKytvd68schrPn5hnnhi28YBGY/++NwYa3kWW/QbPA1EbD5k1Ee3vzh1E+KczT8ZY2uA6PyGvNkcLHolnerWs/1gFk8MkYXn0C0DTRGZQevPcfd3SLVcFcmq/mk5o+cvxtyh3dZUiIkDDCysHIBIv6xqguCZJrVixB6LudG0/WCEeSs4m55LNSaYwhEY0ifHpHCEguRQKKLEF41OEFEl1qyKksl85/dw5UhzN66CNmR7ia6XYjhVf4XGx5CVjhYm/8aTCBonO8sfJ33jCx5ymlIfi/k7gAhrxtmD0LpNtjLrwovfWxboPlddxDUKCYxQtZw/mT2HOgXdqjSPhboUrt9dJY7vCbymz2dRbz8jY8xOb4MYVMtoYUBjC/tZNZV9pogXsdZAGYGCFlFiIFzP4nO2yE/iN3VjQRBnELX8vQln5dFiHOWyy3JNMwBQUrdqoKUEPL1qTdMICsuYm1ghuP67ypUlU+Q0s7rXtJd+e5eDO5vsbu6LNCXzhTEKGtnlwIfjVuSQLQGtkTzjARyhyAFjxnC9WUZN4YEcXEOkQRe7DUw8E8VxNGXPL5AEPtSS3AITwc+XHQb0cuvMuujmNwZDWE8xpcubqwQpLwTOckQLnQNoSA0hPUhjmUROu8FHud6fe5S57zefBPD6j2cRD55fozdVbqFNyPuWqB70MicJpB5dPfy/k4HA4+3J+T2JOXzOd6MEU4vVmslIQMje+qfwWfU5anyecnaZ5T6ouspfY7n7KdKlSpVqlR/4co3F9+cnJ5BSbw+DKZ3SCb4w8NYO1tF21gPZKwWwNbb6CBXFyB/6fGQPy1jtjpHT/1jd6R6cvwkRy9zyiaDEUKmEbqHqlaGhWWBXrYMXA8+eskPQHJVQ0GKMrHlE4hNECrLAfR9tq3MPq/OZXq01pYn7+hPgpz6tZr1tzI8HkDFbSkIo9y+M5fN/O5Ofp4FAj0QcrON6+Qajezim8MBQuYQoj3xJc98WSl/yRTkIUViFiwIKtm4zwvfbHiOcHBSAbPR9SDOnN4bQXSNstf7VVuHpCXJzAk9fpjlAddaURA4qiVQvRYk05vTBULsBFJDyMMTbQDFtQTiUAk/PYYy4KVWl+ZEav3z9SAcIRwhhQSiU0t0nNMVRy0JEBLquo0Qt9Ut08dGFPhkqfaVIUfJEC7gENYauzKz9WVFfbThtlhEX9FFQ9jQDrG7yq0opC9YwCHMrvUwG3UzIgTdOA79weOaZWXLh9hd3P12YxNvRg9vxvx/xTIOavdbnc0/ng7oZjRxKpYqVapUqVL9xel/ASLaTnDt6NywAAAAAElFTkSuQmCC)
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Your Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.