Run a query or multiple queries if needed to report on final exam average based on the student’s age. Give an average for all students 40 and below and an average for all students over 40. This report will be used continuously so you cannot just figure out what 40 years ago today was. You need to use the date to calculate their age somewhere in your solution

Run a query or multiple queries if needed to report on final exam average based on the student’s age. Give an average for all students 40 and below and an average for all students over 40. This report will be used continuously so you cannot just figure out what 40 years ago today was. You need to use the date to calculate their age somewhere in your solution

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 59P

See similar textbooks

Similar questions

Use Common Table Expressions (CTE) to write a query that displays the sum of all purchase orders and sum of all sales orders per each shipping method using the TotalDue column. All shipping methods must be displayed regardless of whether they have matching sales or purchases. Show the ShipMethodID, name, purchase orders total and sales orders total. Round all values to 2 decimal places. Hint: Use mutliple CTEs. Below is a sample of expected results:
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.
Write a query to display the patron ID, first and last name of all patrons that have never checked out any book. Sort the result by patron last name and then first name. (See the figure below for the output) Write a query to display the patron ID and the average number of days that patron keeps books during a checkout. Limit the results to only patrons who have at least three checkouts. Sort the results in descending order by the average days the book is kept. (See the figure below for the output)
Write a query that Balance of each CUSTOMER who have made purchase(s) before AND subtotal that each customer purchased. So that we can find the ENDING balance. Output: CUS_CODE SUBTOTAL CUS_BALANCE ENDING_BALANCE 10010 0 0 0 10011 444.15 0 444.15 10012 154 346 500 10013 0 537 537 10014 422.87 0 422.87 10015 34.99 0 34.99 10016 0 221 221 10017 0 769 769 10018 70.44 217 287.44 10019 0 0 0
Based on the stage 2, write queries for the following questions: Sample output are provided for some queries. Write a query to display all vendors sorted by vendor name. Write a query to display the product id, description, type, base, category, and price for all products that have a product base of water and a product category of sealer. Write a query to display the first name, last name, and email address of employees who were not hired from January 1, 2015, to December 31, 2019. Sort the output by last name and then by first name. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 123 and have the title “Assistant.” Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 99675, 53845, and 44035. Sort the output by employee number and salary “from” date. Write a query to display the first name, last…
Based on the stage 2, write queries for the following questions: Sample output are provided for some queries. Write a query to display all vendors sorted by vendor name. Write a query to display the product id, description, type, base, category, and price for all products that have a product base of water and a product category of sealer. Write a query to display the first name, last name, and email address of employees who were not hired from January 1, 2015, to December 31, 2019. Sort the output by last name and then by first name. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 123 and have the title “Assistant.” Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 99675, 53845, and 44035. Sort the output by employee number and salary “from” date. Write a query to display the first name, last name, street…
How do we avoid getting duplicate entries in a query?
Create a query that displays each instructor's schedule. Display the instructor name, course title, time slot information, for each course taught by the instructor. Also, display the building and room number where the course is taught. Sort the results in a meaningful timetable way, so that each instructor can easily see their schedules.
Based on stage 2, write queries for the following questions: Write a query to display all vendors sorted by vendor name. Write a query to display the product id, description, type, base, category, and price for all products that have a product base of water and a product category of sealer. Write a query to display the first name, last name, and email address of employees who were not hired from January 1, 2015, to December 31, 2019. Sort the output by the last name and then by the first name. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 123 and have the title “Assistant.” Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 99675, 53845, and 44035. Sort the output by employee number and salary “from” date. Write a query to display the first name, last name, street no, street name, city,…
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.
Create a query that displays each instructor schedule. Display the instructor name, course title, time slot information, for each course taught by the instructor. Also display the building and room number where the course is taught. Sort the results in a meaningful timetable way, so that each instructor can easily see their schedules.
ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear