Category: MySQL

i have already the assighment solved i just want to fix one errors please if you see there is no error donot accept
i mention the error with red color

This is a problem statement. i have the statement that you have to make an SQL query. Please don’t use Google or ChatGpt, as I already did. Make sure to run you’re SQL query and match it to the description. and all the query are 100$%

Task 1:
Drivers are essential for InstantRide, and the Driver Relationship team is responsible for their integration and success. The team requires all the driver detail in the system for creating a new dashboard. You need to SELECT all available data for the drivers and return back to the team.
Query all the driver data from the DRIVERS table
Task 2:
The Driver Relationship team also requests the joining dates of the drivers to create a timeline. In the table, you only need to return the joining date of the drivers. You need to only return the DRIVER_START_DATE column inside a SELECT statement for the DRIVERS table.
Task
Query the start date for each driver.
Task 3:
The Driver Relationship team requires the following details about the drivers:
All drivers with their rating in descending order
All drivers currently having a rating higher than 4
You need to return the DRIVER_ID and DRIVER_RATING couples in two separate tables.
Task 4:
The InstantRide User Satisfaction team is a core team for InstantRide, and they focus on increasing the customer satisfaction. They want to learn the travel time for each ride in the system. You need to return the USER_ID, and the TRAVEL_TIME column which is calculated using the TIMEDIFF function on the TRAVEL_END_TIME and the TRAVEL_START_TIME.
Task
Query the travel time for each ride.
Task 5:
User Satisfaction team wants to send monthly summaries for each user. They need the following details with the user ID:
The last day of the month when the users traveled most recently
One week after the last day of the month when the users traveled most recently
You need to return a three-column output with USER_ID, LAST_TRAVEL_MONTH and NOTIFICATION. LAST_TRAVEL_MONTH should be calculated using the MAX of the LAST_DAY of the TRAVEL_END_TIME field. Similarly, NOTIFICATION should be calculated with DATE_ADD function to add one week.
Task
Query monthly summaries for each user.
Task 6:
The Marketing team of InstantRide wants to know that how many discounts have been offered for each ride. You need to calculate this information for each travel where a discount is applied and return two columns: TRAVEL_ID and DISCOUNT_AMOUNT. In addition, you need to return the calculation as a money value using the ROUND function to 2 decimals.
Calculate the total amount of discounts given.
#2
Task 1:
The InstantRide received some traffic violation tickets from the government. The Legal team of InstantRide requires the travel information of the respective drivers along with corresponding Driving License IDs to proceed further. In addition, the team wants to include the drivers without travel information in the system yet for the completion of driver list. Therefore, you need to return DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID, TRAVEL_START_TIME, TRAVEL_END_TIME information from the DRIVERS and TRAVELS data connected by LEFT JOIN.
Task
Query all drivers with and without travel data.
Task 2:
The InstantRide Management team considers setting up a Lost & Found inventory. In order to start the setup, the team requires the detail of users with their travel start and end times. The team wants to track potential list of users who may have forgotten their items on the cars. Therefore, you need to return USER_FIRST_NAME, USER_LAST_NAME, TRAVEL_START_TIME, TRAVEL_END_TIME information from the USERS and TRAVELS tables connected inside a JOIN statement by the USING function and USER_ID field.
Task
Query user travel times.
Task 3:
The InstantRide Finance team wants to collect the price and discount information with the driver names for each travel in the system. You need to return the TRAVEL_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, TRAVEL_PRICE, and TRAVEL_DISCOUNT information from the TRAVELS and DRIVERS tables combined over DRIVER_ID field with the ON keyword.
Task
Calculate each user’s price and discount information
Task 4:
The InstantRide Driver Relationship team wants to create groups for drivers according to their ratings such as 3+ or 4+. For instance, a driver with the rating 3.8 will be 3+; whereas a driver with the rating 4.2 will be 4+. You need to return a two column output with DRIVER_ID and DRIVER_RATING which has first FLOOR applied and then CONCAT with + sign for all drivers with a rating greater than 0.
Task
Create groups of drivers according to their ratings.
Task 5:
The InstantRide User Satisfaction team are looking forward to creating discounts for the users. However, the team suspects that there could be duplicate users in the system with different emails. Check for the users with their names and surnames for potential duplicates. Therefore, you need to JOIN the USERS table with USERS table and compare for equality of USER_FIRST_NAME and USER_LAST_NAME and difference in USER_ID fields.
Task
Check for duplicate driver accounts.
#3
Task 1:
The InstantRide Driver Relationship team wants to analyze the travel information of the low rated drivers. You will need to provide them with all the travel information of the drivers with the average rating lower than 4. The team wants to get in touch with the travelers and analyze their feedback. You need to run SELECT query and return all travel data from TRAVELS table filtered by the drivers who has lower rating than 4 in the DRIVERS table.
Task
Analyze travel data for the low rated drivers.
Task 2:
The InstantRide Driver Relationship team wants to check if there are any drivers with zero rides. You need to extract the DRIVER_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME of the drivers with zero rides. You can use a subquery with DRIVER_ID compared to ALL rows in TRAVELS.
Task
Check if any drivers have given zero rides.
Task 3:
The InstantRide Finance team wants to know the average discount amounts for each car in the InstantRide. Calculate the average discount amount as monetary value for the travels where a discount is applied. You need to create a subquery over the TRAVELS table to retrieve CAR_ID and DISCOUNT_AMOUNT, calculated with 2 decimals using the ROUND function.
To calculate the DISCOUNT_AMOUNT, multiply the TRAVEL_PRICE by the TRAVEL_DISCOUNT where the TRAVEL_DISCOUNT value is not NULL. Round the result to 2 decimals.
Then you can use this subquery to get the CAR_ID and AVG of DISCOUNT_AMOUNT values, once again using the ROUND function on the average results. Group the results by the CAR_ID. Use CAR_ID and DISCOUNT_AMOUNT as column aliases and return it back to the Finance team.
Task
Calculate the average discount for each car.
Task 4:
The InstantRide Finance team also wants to analyze travels where more than the average discount rate is applied. They want to look for any correlation between higher discount amounts against other travel characteristics. You need to create a SELECT statement which is filtered with a subquery to calculate the AVG of the TRAVEL_DISCOUNT column.
Task
Find all travels where an above average discount was applied.
Task 5:
The InstantRide Management team considers creating a new team for Car Maintenance. The new team needs to find/list the cars that are used more than average with the usage count. Collect the information of all rides and consolidate over the Car IDs. You need to create a three level SQL statement. Firstly, you need to COUNT the number of rows in TRAVELS and GROUP_BY the CAR_ID field. Then you need to calculate the AVG of the data to find the average usage of the cars. Finally, you need to return CAR_ID and the TRAVELS count (as the Usages column) filtered to only values greater than the calculated average.
Task
Query all cars used more than the average.
Task 6:
The InstantRide Marketing team wants to organize an InstantRide party. The team requires first name and last name of all the users and drivers in order to create a gate-pass for their entry. You need to join USERS and DRIVERS tables using UNION and return FIRST_NAME and LAST_NAME columns.
Task
Query all drivers and users.

Task 1:
Drivers are essential for InstantRide, and the Driver Relationship team is responsible for their integration and success. The team requires all the driver detail in the system for creating a new dashboard. You need to SELECT all available data for the drivers and return back to the team.
Query all the driver data from the DRIVERS table
Task 2:
The Driver Relationship team also requests the joining dates of the drivers to create a timeline. In the table, you only need to return the joining date of the drivers. You need to only return the DRIVER_START_DATE column inside a SELECT statement for the DRIVERS table.
Task
Query the start date for each driver.
Task 3:
The Driver Relationship team requires the following details about the drivers:
All drivers with their rating in descending order
All drivers currently having a rating higher than 4
You need to return the DRIVER_ID and DRIVER_RATING couples in two separate tables.
Task 4:
The InstantRide User Satisfaction team is a core team for InstantRide, and they focus on increasing the customer satisfaction. They want to learn the travel time for each ride in the system. You need to return the USER_ID, and the TRAVEL_TIME column which is calculated using the TIMEDIFF function on the TRAVEL_END_TIME and the TRAVEL_START_TIME.
Task
Query the travel time for each ride.
Task 5:
User Satisfaction team wants to send monthly summaries for each user. They need the following details with the user ID:
The last day of the month when the users traveled most recently
One week after the last day of the month when the users traveled most recently
You need to return a three-column output with USER_ID, LAST_TRAVEL_MONTH and NOTIFICATION. LAST_TRAVEL_MONTH should be calculated using the MAX of the LAST_DAY of the TRAVEL_END_TIME field. Similarly, NOTIFICATION should be calculated with DATE_ADD function to add one week.
Task
Query monthly summaries for each user.
Task 6:
The Marketing team of InstantRide wants to know that how many discounts have been offered for each ride. You need to calculate this information for each travel where a discount is applied and return two columns: TRAVEL_ID and DISCOUNT_AMOUNT. In addition, you need to return the calculation as a money value using the ROUND function to 2 decimals.
Calculate the total amount of discounts given.
#2
Task 1:
The InstantRide received some traffic violation tickets from the government. The Legal team of InstantRide requires the travel information of the respective drivers along with corresponding Driving License IDs to proceed further. In addition, the team wants to include the drivers without travel information in the system yet for the completion of driver list. Therefore, you need to return DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID, TRAVEL_START_TIME, TRAVEL_END_TIME information from the DRIVERS and TRAVELS data connected by LEFT JOIN.
Task
Query all drivers with and without travel data.
Task 2:
The InstantRide Management team considers setting up a Lost & Found inventory. In order to start the setup, the team requires the detail of users with their travel start and end times. The team wants to track potential list of users who may have forgotten their items on the cars. Therefore, you need to return USER_FIRST_NAME, USER_LAST_NAME, TRAVEL_START_TIME, TRAVEL_END_TIME information from the USERS and TRAVELS tables connected inside a JOIN statement by the USING function and USER_ID field.
Task
Query user travel times.
Task 3:
The InstantRide Finance team wants to collect the price and discount information with the driver names for each travel in the system. You need to return the TRAVEL_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, TRAVEL_PRICE, and TRAVEL_DISCOUNT information from the TRAVELS and DRIVERS tables combined over DRIVER_ID field with the ON keyword.
Task
Calculate each user’s price and discount information
Task 4:
The InstantRide Driver Relationship team wants to create groups for drivers according to their ratings such as 3+ or 4+. For instance, a driver with the rating 3.8 will be 3+; whereas a driver with the rating 4.2 will be 4+. You need to return a two column output with DRIVER_ID and DRIVER_RATING which has first FLOOR applied and then CONCAT with + sign for all drivers with a rating greater than 0.
Task
Create groups of drivers according to their ratings.
Task 5:
The InstantRide User Satisfaction team are looking forward to creating discounts for the users. However, the team suspects that there could be duplicate users in the system with different emails. Check for the users with their names and surnames for potential duplicates. Therefore, you need to JOIN the USERS table with USERS table and compare for equality of USER_FIRST_NAME and USER_LAST_NAME and difference in USER_ID fields.
Task
Check for duplicate driver accounts.
#3
Task 1:
The InstantRide Driver Relationship team wants to analyze the travel information of the low rated drivers. You will need to provide them with all the travel information of the drivers with the average rating lower than 4. The team wants to get in touch with the travelers and analyze their feedback. You need to run SELECT query and return all travel data from TRAVELS table filtered by the drivers who has lower rating than 4 in the DRIVERS table.
Task
Analyze travel data for the low rated drivers.
Task 2:
The InstantRide Driver Relationship team wants to check if there are any drivers with zero rides. You need to extract the DRIVER_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME of the drivers with zero rides. You can use a subquery with DRIVER_ID compared to ALL rows in TRAVELS.
Task
Check if any drivers have given zero rides.
Task 3:
The InstantRide Finance team wants to know the average discount amounts for each car in the InstantRide. Calculate the average discount amount as monetary value for the travels where a discount is applied. You need to create a subquery over the TRAVELS table to retrieve CAR_ID and DISCOUNT_AMOUNT, calculated with 2 decimals using the ROUND function.
To calculate the DISCOUNT_AMOUNT, multiply the TRAVEL_PRICE by the TRAVEL_DISCOUNT where the TRAVEL_DISCOUNT value is not NULL. Round the result to 2 decimals.
Then you can use this subquery to get the CAR_ID and AVG of DISCOUNT_AMOUNT values, once again using the ROUND function on the average results. Group the results by the CAR_ID. Use CAR_ID and DISCOUNT_AMOUNT as column aliases and return it back to the Finance team.
Task
Calculate the average discount for each car.
Task 4:
The InstantRide Finance team also wants to analyze travels where more than the average discount rate is applied. They want to look for any correlation between higher discount amounts against other travel characteristics. You need to create a SELECT statement which is filtered with a subquery to calculate the AVG of the TRAVEL_DISCOUNT column.
Task
Find all travels where an above average discount was applied.
Task 5:
The InstantRide Management team considers creating a new team for Car Maintenance. The new team needs to find/list the cars that are used more than average with the usage count. Collect the information of all rides and consolidate over the Car IDs. You need to create a three level SQL statement. Firstly, you need to COUNT the number of rows in TRAVELS and GROUP_BY the CAR_ID field. Then you need to calculate the AVG of the data to find the average usage of the cars. Finally, you need to return CAR_ID and the TRAVELS count (as the Usages column) filtered to only values greater than the calculated average.
Task
Query all cars used more than the average.
Task 6:
The InstantRide Marketing team wants to organize an InstantRide party. The team requires first name and last name of all the users and drivers in order to create a gate-pass for their entry. You need to join USERS and DRIVERS tables using UNION and return FIRST_NAME and LAST_NAME columns.
Task
Query all drivers and users.

Discussion.
Consider the following description of a university system where professors teach
courses in which students can enroll:
Each professor has a name, address, phone number, email address, and salary. A
student also has a name, address, phone number, and email address. A student also has
an average mark (of the final marks of his or her course). Each person in the university
has a unique ID.
A Course has a name, a number, and fees. When a student is enrolled in a course, the
marks for this enrolment are recorded, and the current average, as well as the final mark
(if there is one), can be obtained from the enrolment. Each student can obtain a list of
courses he or she is enrolled in. Professors teach courses. Each course has at least one
and at most three teachers.
a) Draw a class diagram for this university.
b) Add attributes and methods (operations) when necessary.

Discussion.
Consider the following description of a university system where professors teach
courses in which students can enroll:
Each professor has a name, address, phone number, email address, and salary. A
student also has a name, address, phone number, and email address. A student also has
an average mark (of the final marks of his or her course). Each person in the university
has a unique ID.
A Course has a name, a number, and fees. When a student is enrolled in a course, the
marks for this enrolment are recorded, and the current average, as well as the final mark
(if there is one), can be obtained from the enrolment. Each student can obtain a list of
courses he or she is enrolled in. Professors teach courses. Each course has at least one
and at most three teachers.
a) Draw a class diagram for this university.
b) Add attributes and methods (operations) when necessary.

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.

Assignment Requirements
Microsoft SQL Server Express and SQL Server Management Studio (SSMS) MUST be installed to complete this assignment.
Compose your assignment in a Word document, and be sure to identify yourself, your class, and the assignment unit at the top of your paper.
Embed the screenshots of your SQL statements and confirmatory output into the Word document
First, navigate to the Academic Tools area of this course and select Library, then Required Readings to review the Unit 5 videos covering facets associated with database security. It is very important that you watch the Unit 5 videos before completing the assignment.You must have SQL Server Express and SQL Server Management Studio (SSMS) installed to perform this assignment. The sample database for this course is called BikeStores. Download the database design diagram below.BikeStores Database Design Diagram
Use the BikeStores database design diagram for your stored procedure assessment.Please copy each SQL statement into your Microsoft® Word® document.Below that, enter a screenshot of the execution of the SQL showing the code and the resulting output.
Below that, enter text explaining the SQL statement and outcome. What has occurred? Was there an adverse impact from the SQL statement execution?
Part 1: Dynamic SQL Statements Without BindingExecute the individual SQL statements contained within the following text file in a Microsoft SSMS query window:BikeStores SQL Injection Commands
After executing these statements, explain why the GetUser stored procedure is problematic. Did any unauthorized data modifications occur? Did data corruption occur? Were any tables improperly dropped from the database?Part 2: Dynamic SQL Statements With BindingBased on the Part 1 results, the GetUser stored procedure has been revised and replaced with a stored procedure called GetUserWithBind. Execute the individual SQL statements contained within the following text file in a Microsoft SSMS query window:BikeStores SQL Injection Commands With Binding
After executing these statements, explain whether the GetUserWithBind stored procedure has solved the security issues uncovered in Part 1. If the security issues have been resolved, explain how it was accomplished. Perform research and briefly describe the binding parameter concept. Below is a table contrasting the use of “execute” versus “execute sp_executesql” in Microsoft SQL Server.EXEC or EXECUTEEXEC sp_executesql
Parameterization is not possibleParameterization is possible
Risk of SQL injection is highRisk of SQL injection is lower
An EXEC call wastes a lot of space in the plan cacheIt gets cached like a stored procedure without wasting white spaces if parameterization is used
It does not force a plan to be cachedIt forces the plan to be cached at first execution

Web Project
The final individual web project is for you to create at least six web pages to showcase your
product/service of the company that you want to create for your future personal company to make
money. This web project has nothing to do with the company that you are currently working for, it is for
you to create your own company (make believe) to create income for you. Submit all the files via canvas
as a zipped file. The due date is going to be the last lecture date. The grade will be assigned based on the
following criteria:
1. Database Connectivity (45%), 2. Contents (35%), 3. User Friendly (20%)
You can use any software tool that you like to create web pages.
You’ll need to link your web page(s) to MySql database through php pages. The deliverables are the
following:
a. All web pages
b. Php files
c. Sql file with table and column names and the relationship through foreign key. That way I can
duplicate your effort on my local MySql database
Pre-recorded presentation is required. Then submit the required files via Canvas, Assignments by the due
date listed on the course agenda.

You must submit two separate copies (one Word file and one PDF file)
use text answers only no images
1- the relational algebra operations
2- SQL constraints
3- schema mapping
4- generalization constraints
refer to the attached document for further instructions