DAT 560G: Database Design and SQL Fall 2023, Mini A
Lab #2: SQL Part 1
INSTRUCTIONS
1. This is a group assignment, to be completed during the lab.
2. Please include only your Student IDs on the submission.
3. Part of the submission will be online, and part requires a physical submission.
a. Questions 1 – 6 will be submitted on Canvas. Each member of your group needs to submit, even though you are working in a group. The online submission is automatically graded, although you will only see your grades next week.
b. The rest of the questions need to be on a PDF file and submitted online on this page. One submission per group.
c. The submissions of both parts are due by 11 pm TOMORROW on Canvas.
DAT 560G – Fall 2023 – Lab #2: SQL Part 1
SESSION OUTLINE
Introduction of the database
Lecture: Joining Tables (Part 1)
Assignment Questions #1 – #13
Solutions to some questions
ASSIGNMENT
10 minutes
40 minutes 10-15 minutes
Lyft is the well-known ride service, which allows individuals to drive for Lyft. In essence, Lyft replaces traditional taxi service. Customers that want to use the service, request a ride using the Lyft app. Using their proprietary technology, Lyft finds the closest driver to the customer and assigns the driver to the requested ride.
Understanding customer trends is essential for Lyft. The company wants to know their customers’ preferences for rides, including when customers are more likely to request a ride and when drivers are available.
Using a sample of ride requests from one week, Lyft compiled data for you to analyze. For the purpose of this analysis, assume that each ride costs $10.
Use the Lyft database to answer all questions in this assignment. The database is posted on Canvas
The E/R diagram for this database is below:
Programming Help
The first 6 questions will be submitted on Canvas and graded automatically. Each of these questions is 10 points.
1) For each driver, find the driver’s names, car color, and car’s year
2) Find all requests for rides on Friday. Identify the driver and the customer. Sort the results by customer name.
3) Find all requests for rides on evenings on the weekends (Saturday / Sunday). Identify the destination. Sort results by RequestID.
4) Calculate the number of requests and total distance driven by time of day on each day
5) Find the average year of manufacturing for each car color. Sort the results in decreasing order of average manufacturing year.
6) Create a list of destinations that customers chose this week. Include every destination only once. Do not include the NULL destinations. Sort results in decreasing order.
Each of these questions is 5 points. For each question, submit your SQL code and a screenshot of the results. If the results are too long, partial results are fine. Include relevant attributes for each result, to clarify that the result is correct. Do NOT include many unnecessary attributes. Do NOT use SELECT *.
The rest other questions will be submitted on PDF:
7) Develop one query that will help find how many customers do not have addresses.
8) Calculate the number of requests for each day of the week
9) Explain some of the previous results (in Question #8). Specifically, explain why there might be variation across days.
10) For each age level of customers between 30 – 35, find the total number of past trips and minimum number of past trip for that age group. Also, include the number of customers in that age group. Sort the results by the age of customers. In other words, find the number of trips, etc, for 30-year- olds, 31-year-olds, … 35-year-olds.
11) For each driver of a white car, find all trips. Include the Destination and 2 other attributes in your results. Sort results in decreasing order of customer name
12) Find the name, gender, and address of customers that drove to the Stadium this past week
DAT 560G – Fall 2023 – Lab #2: SQL Part 1
Code Help, Add WeChat: cstutorcs
13) List the number of requests for each customer. Sort the results in increasing order of number of requests. Note, include also customer address
14) How much time did you spend on this assignment?
DAT 560G – Fall 2023 – Lab #2: SQL Part 1
浙大学霸代写 加微信 cstutorcs