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

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

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

I need help with the SQL query for the following question: 1. Get a list of 5 reviews for shoppers in Chicago Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours
Question R Write a PL/SQL block to read and display the names of world regions, with a count of the number of countries in each region. Include only those regions having at least 10 countries. Order your output by ascending region name. Full explain this question and text typing work only We should answer our question within 2 hours takes more time then we will reduce Rating Dont ignore this line.
Write a SQL query that retrieves the names and email addresses of all students who are enrolled in at least one course in the "Computer Science" department and have a graduation year of 2022 or later. Include the course name and instructor name for each enrollment in the output. Schemas of the tables are below. "students" table: student_id: unique identifier for the student name: name of the student email: email address of the student major: field of study for the student graduation_year: expected year of graduation for the student "courses" table: course_id: unique identifier for the course course_name: name of the course instructor_name: name of the instructor teaching the course. department: department offering the course. course_description: description of the course "enrollments" table: enrollment_id: unique identifier for the enrollment student_id: identifier for the student enrolled in the course. course_id: identifier for the course the student is enrolled in…
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
Write a SQL query, which shows a list of all employees last name, their department names and their department city where that department is located from the employees table. If an employee does not belong to a department, then their department number should show as “N/A” and their department city should show up as “No City info Found”. MAKE SURE you have all the employees displayed including the employees who do not have a department.
This is in sql. For all artists who are currently in a band: Return the artist’s full name (as a single value), the band name, and how long in years the artist has been in the band. The artist should only be shown once for each band. (Hint: Remember that the data might include an artist who left a band and then rejoined. That is the challenge here!) Tables with values are in image.
Write SQL codes according to the requirements given. Tables are imaginary, column names can be made up. List the item ID, description, and category for each pair of items that are in the same category. (For example, one such pair would be item FS42 and item PF19, because the category for both items is FSH.) List the invoice number and invoice date for each invoice created for the customer James Gonzalez. Use a subquery to find the sales rep ID, first name, and last name of each sales rep who represents at least one customer with a credit limit of $500. List each sales rep only once in the results.
Databise Design Describe three SQL queries to provide information to the end users for this system. At least one must be a join. These can be real SQL statements or written in English Table Name Attribute Description Data Type Null Keys Sales Sales_ID Contains unique Sales ID INT No Primary key Product_Price Price of the Product VARCHAR(30) No Sales_Date Sales of the Date VARCHAR(30) No Quantity Quantity of product DATE No Personal_ID Personal_ID ofSales INT No Foreign key Cashier_ID Cashier_ID of Sales INT No Foreign key Table Name Attribute Description Data Type Null Keys Product Product ID ID of the Product INT No Primary key Product_name Name of the Product VARCHAR(50) No Brand_name Brand of the name VARCHAR(50) No Quantity Quantity VARCHAR(20) No Pharmaceutical_ID Pharmaceutical of the ID INT…
TAL Distributors would like to know the number of months between the current date and the order date of an order. Write an SQL statement in MySQL that displays the order number and the number of months between the current date and the order date. The number of months should display as an integer. (Hint: You can nest one function within another function.) Snip and paste the SQL command(s) and results below.
Use PL/SQL in oracle sql developer Feature :Given a start and end date, print out name, phone number, status and event ID of people who attended the same event with someone whose status is 1 (tested positive) and the event date is between the start and end date. Feature : print out names of people who have recovered. A person has recovered if the person tested positive before but the current status is 0 (not positive).
I need help with the SQL query for the following question: 1. What is the total number of reviews in every zip code. Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours
I need help with the SQL query for the following question: 1. Show the total number of customers who complained about Bluetooth issues for every product name of every manufacturer from every retailer Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours