Write a query to display the book number, title, subject, average cost of books within that subject, and the difference between each books cost and the average cost of books in that subject. Sort the results by book title. (Figure P8.65)
FIGURE P8.65 BOOKS WITH AVERAGE COST BY SUBJECT
It is used to retrieve information from the table or database. The syntax for the “SELECT” statement is given below:
SELECT * FROM table_Name;
This function is used to round a number that specified in decimal places. The format of the “Round()” function is as follows:
INNER JOIN keyword:
“INNER JOIN” keyword is used to select all the matching records of both the table.
SELECT col_Name FROM table_Name1 INNER JOIN table_Name2 ON table_Name1.col_Name = table_Name2.col_Name;
“GROUP BY” Clause:
The GROUP BY clause is used to group the result of a SELECT statement completed on a table where the values of tuple are identical for more than one column.
SELECT expression1, expression2, expression_n, aggregate_function (expression)FROM table_name WHERE conditions GROUP BY expression1, expression2, expression_n;
ORDER BY Clause:
SQL contains “ORDER BY” clause in order to sort rows. The values get sorted in ascending as well as descending order. The keyword used to sort values in ascending order is “ASC” and for descending order is “DESC”. By default, it sorts values by ascending order.
SELECT column_Name1, column_Name2 FROM table_Name ORDER BY column_Name2;
Query to display the given details for the corresponding condition:
The query to display the given details for the corresponding condition is given below:
SELECT BOOK_NUM, BOOK_TITLE, book.BOOK_SUBJECT,
ROUND(averageCost,2) AS "AVGCOST", Round(BOOK_COST-averageCost,2) as "DIFFERENCE"
FROM BOOK INNER JOIN (SELECT AVG(BOOK_COST) AS averageCost , BOOK_SUBJECT FROM BOOK GROUP BY BOOK_SUBJECT) averageCalc ON
averageCalc.BOOK_SUBJECT = book.BOOK_SUBJECT ORDER BY book.BOOK_NUM, book.BOOK_TITLE;
The above query was executed using the tool “Access 2013”.
The above query is used to display the book number, title, subject, average cost of books within that subject and the difference between the cost of each book and the average cost of books in that subject.
Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!Get Started
Fundamentals of Information Systems
A Guide to SQL
Fundamentals of Information Systems
Database Systems: Design, Implementation, & Management
Principles of Information Security (MindTap Course List)
Fundamentals of Geotechnical Engineering (MindTap Course List)
Precision Machining Technology (MindTap Course List)
Principles of Information Systems (MindTap Course List)
Cornerstones of Financial Accounting
Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)
Mechanics of Materials (MindTap Course List)
Principles of Geotechnical Engineering (MindTap Course List)
Automotive Technology: A Systems Approach (MindTap Course List)
Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)
Management Of Information Security
Electric Motor Control
EBK ELECTRICAL WIRING RESIDENTIAL
Fundamentals of Chemical Engineering Thermodynamics (MindTap Course List)
International Edition---engineering Mechanics: Statics, 4th Edition
Solid Waste Engineering
Steel Design (Activate Learning with these NEW titles from Engineering!)
A+ Guide to Hardware (Standalone Book) (MindTap Course List)
Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Welding: Principles and Applications (MindTap Course List)