Display only the products (name and ID) that have a quantity greater than the average quantity of all products. ## Northwinds Data ModelThe Northwinds Data Model is presented in the image, showcasing the relationships between various tables in a relational database. This data model is often used to illustrate the structure and relationships typical of a company's business data. Below is a detailed description of each table and the relationships between them.### Suppliers Table- **SupplierID** (Primary Key)- CompanyName- ContactName- ContactTitle- Address- City- Region- PostalCode- Country- Phone- Fax### Products Table- **ProductID** (Primary Key)- ProductName- **SupplierID** (Foreign Key to Suppliers)- **CategoryID** (Foreign Key to Categories)- QuantityPerUnit- UnitPrice- UnitCost- UnitsInStock- UnitsOnOrder- ReorderLevel- Discontinued### Order Details Table- **OrderID** (Primary Key)- **ProductID** (Primary Key)- UnitPrice- Quantity### Orders Table- **OrderID** (Primary Key)- CustomerID- EmployeeID- OrderDate- RequiredDate- ShippedDate- ShipVia- Freight- ShipName- ShipAddress- ShipCity- ShipRegion- ShipPostalCode- ShipCountry### Customers Table- **CustomerID** (Primary Key)- CompanyName- ContactName- ContactTitle- Address- City- Region- PostalCode- Country- Phone- Fax### Categories Table- **CategoryID** (Primary Key)- CategoryName- Description### Employees Table- **EmployeeID** (Primary Key)- LastName- FirstName- Title- TitleOfCourtesy- BirthDate- HireDate- Address- City- Region- PostalCode- Country- HomePhone- Extension- Notes- ReportsTo### Shippers Table- **ShipperID** (Primary Key)- CompanyName- Phone### Relationships ExplainedThe visual representation outlines the one-to-many (1:M) relationships between various tables:- The **Suppliers** table is linked to the **Products** table through SupplierID, indicating that each supplier can supply multiple products.- The **Products** table is connected to the **Order Details** table through ProductID, showing

Answered: Display only the products (name and ID)…

Engineering

Computer Science

Display only the products (name and ID) that have a quantity greater than the average quantity of all products.

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

Using SQL determine salary is above 4000 and job_id starts with a letter “S” In addition, when grouping the above selected employees by department, we only show the department which has more than 5 employees or the minimal salary greater than 5000, and order the result by the average salary.
C) Which employees earn less salary than the average salary for the consultants who perform the same job? Show the employees first and last names, job id, salary, department name, and manager's last name. Sort the result by the employees’ employee id. D) For each department, find the manager who pays out the most in salaries. Show the department number, the manager's last name, and the total she/he pays each month. Sort by the department number. Here are the tables and the columns within the table Here are the tables and the columns within the table CONSULTANTS - CONSULTANT_ID- FIRST_NAME- LAST_NAME- EMAIL- PHONE_NUMBER- HIRE_DATE- JOB_ID- SALARY- COMMISSION_PCT- MANAGER_ID- DEPARTMENT_ID . COUNTRIES - COUNTRY_ID- COUNTRY_NAME-REGION_ID . CUSTOMERS - CUST_IDCUST_EMAILCUST_FNAMECUST_LNAMECUST_ADDRESSCUST_CITYCUST_STATE_PROVINCECUST_POSTAL_CODECUST_COUNTRYCUST_PHONECUST_CREDIT_LIMIT . DEPARTMENTS - DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID . EMPLOYEES -…
5. Display the current day of the week, hour, minutes, and seconds of the current date settingon the computer you’re using. SQL commands
Query: List the IDs of the tutors who are currently tutoring more than one student
Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered and label this result column NumOrders.
Below are some rows of the PROVIDERS table for a factory: PCODE NAME PHONE 10192 Hans, Inc. 231 - 3092 98411 Tools and More 231 - 1029 0489035 Motors Inc. 993 - 1821 All providers from the factory zone of the city start with 231. You want to get the providers that are in the factory zone of the city. Which of the following is more appropriate? a. SELECT * FROM PROVIDERS WHERE PHONE LIKE ‘%231%’ b. SELECT * FROM PROVIDERS WHERE PHONE LIKE ‘% 231’ c. SELECT * FROM PROVIDERS WHERE PHONE LIKE ‘___ 231 ___’ d. SELECT * FROM PROVIDERS WHERE PHONE LIKE ‘231%’
5. In the OrderItems table, display a count of the number of products where item_price is greater than or equal to $6, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BRO3 1 rows returned in 0.03 seconds PROD ID Download 4 NUM PROD ID
Classify students according to the total number of hours they have completed. (Less than30=freshman, 30 to 59 is sophomore, 60 to 89 is junior, 90+ senior)
Write SQL queries for the following. Display details of those patients whose age is either 25 or 36 or 29 without using comparison operator. Display first name and last name of those doctors whose salary is less than or equal to 77,000 and greater than or equal to 50,000 using between operator. Display details of those doctors who neither belong to Isb nor Rwp nor Multan without using in and like operator. Display full name of doctors whose first name is other than Tania. Display first name of doctors whose first name contains any of vowels (a,e,i,o,u). Display names of those patients whose name’s second letter is neither ‘a’ nor ‘d’ Display details of those male patients whose age is 25 or 36. Display details of those patients whose name contains less than 5 characters. Display first 4 doctors who are earning least salary. Display details of all patients in such a way that female patients are shown before male patients. Display the unique cities from doctor table. Display all…
5. Determine the profit of each book sold to Jake Lucas, using the actual price the customerpaid (not the book’s regular retail price). Sort the results by order date. If more than onebook was ordered, sort the results by profit amount in descending order. Perform thesearch using the customer name, not the customer number.
For each product, show the product ID and the number of orders placed.Show the results in decreasing order based on the number of times the product has been ordered, with NumOrders as the result column.
In the Summary sheet, calculate average and median sales and commissions. Use the named ranges shown in cells A23:B26. In cell B5, enter a formula to calculate the average annual sales for all sales associates. Use the named range AnnualSales.