I am having trouble with this problem on SQL and cannot seem to get the exact ou

Question

I am having trouble with this problem on SQL and cannot seem to get the exact output. Here's the question:

5. List all books and their corresponding book subject average cost. The report should look like this: SQL I All Rows Fetched: 20 in 0.004 seconds Cost Avg Cost Number Title Subject 5235 Beginner's Guide to JAVA Programming $59.95 $66.62 Cloud 5236 Database in the Cloud $79.95 $72.45 5237 Mastering the database environment Database $89.95 $84.95 Programming $59.95 $66.62 5238 Conceptual Programming Programming $49.95 $66.62 5239 J++ in Mobile Apps Programming $79.95 $66.62 5240 iOS Programming Programming $49.95 $66.62 5241 JAVA First Steps Middleware $59.95 $89.95 5242 in Middleware Deployment Database $129.95 $84.95 5243 DATABASES in Theory Cloud 10 5244 Cloud-based Mobile Applications $69.95 $72.45 Middleware $119.95 $89.95 5245 The Golden Road to Platform independence 11 Cloud 5246 Capture the Cloud 12 $69.95 $72.45 13 5247 Shining Through the Cloud: Sun Programming Programming $109.95 $66.62 $49.95 $84.95 14 5248 What You Always Wanted to Know About Database, But Were Afraid to Ask Database Cloud 15 5249 Starlight Applications $69.95 $72.45 Middleware $89.95 $89.95 16 5250 Reengineering the Middle Tier Programming $59.95 $66.62 17 5251 Thoughts on Revitalizing Ruby $69.95 $84.95 18 5252 Beyond the Database Veil Database Programming $79.95 $66.62 19 5253 Virtual Programming for Virtual Environments Programming $49.95 $66.62 20 5254 Coding Style for Maintenance

Explanation / Answer

Here we define some steps through which you can create your output as per your requirements, please follow these steps and try to understand:-

/* -------Step 1) Creating our table----------------------------------- */
CREATE TABLE BOOK (
BOOK_NUM NUMBER(10,0) PRIMARY KEY,
BOOK_TITLE VARCHAR2(120) NOT NULL ,
BOOK_YEAR NUMBER(4) ,
BOOK_COST NUMBER(8,2) ,
BOOK_SUBJECT VARCHAR2(120) ,
PAT_ID NUMBER(10)
);

/* ------Step2) Inserting data into the table-------------------------- */

INSERT INTO BOOK VALUES ('5235', 'Beginner''s Guide to JAVA', '2012', '59.95', 'Programming', NULL);
INSERT INTO BOOK VALUES ('5236', 'Database in the Cloud', '2012', '79.95', 'Cloud', NULL);
INSERT INTO BOOK VALUES ('5237', 'Mastering the database environment', '2013', '89.95', 'Database', NULL);
INSERT INTO BOOK VALUES ('5238', 'Conceptual Programming', '2013', '59.95', 'Programming', '1229');
INSERT INTO BOOK VALUES ('5239', 'J++ in Mobile Apps', '2013', '49.95', 'Programming', NULL);
INSERT INTO BOOK VALUES ('5240', 'iOS Programming', '2013', '79.95', 'Programming', '1212');
INSERT INTO BOOK VALUES ('5241', 'JAVA First Steps', '2013', '49.95', 'Programming', NULL);
INSERT INTO BOOK VALUES ('5242', 'C# in Middleware Deployment', '2013', '59.95', 'Middleware', '1228');
INSERT INTO BOOK VALUES ('5243', 'DATABASES in Theory',' 2013', '129.95', 'Database', NULL);
INSERT INTO BOOK VALUES ('5244', 'Cloud-based Mobile Applications', '2013', '69.95', 'Cloud', NULL);
INSERT INTO BOOK VALUES ('5245', 'The Golden Road to Platform independence', '2014', '119.95', 'Middleware', NULL);
INSERT INTO BOOK VALUES ('5246', 'Capture the Cloud', '2014', '69.95', 'Cloud', '1172');
INSERT INTO BOOK VALUES ('5247', 'Shining Through the Cloud: Sun Programming', '2014', '109.95', 'Programming', NULL);
INSERT INTO BOOK VALUES ('5248', 'What You Always Wanted to Know About Database, But Were Afraid to Ask', '2014', '49.95', 'Database', NULL);
INSERT INTO BOOK VALUES ('5249', 'Starlight Applications', '2014', '69.95', 'Cloud', '1207');
INSERT INTO BOOK VALUES ('5250', 'Reengineering the Middle Tier', '2014',' 89.95', 'Middleware', NULL);
INSERT INTO BOOK VALUES ('5251', 'Thoughts on Revitalizing Ruby', '2014', '59.95', 'Programming', NULL);
INSERT INTO BOOK VALUES ('5252', 'Beyond the Database Veil', '2014',' 69.95', 'Database', '1229');
INSERT INTO BOOK VALUES ('5253', 'Virtual Programming for Virtual Environments', '2014', '79.95', 'Programming', NULL);
INSERT INTO BOOK VALUES ('5254', 'Coding Style for Maintenance', '2015', '49.95', 'Programming', NULL);

/* ------Step 5) Accessing data in the table-------------------------- */

SELECT * FROM BOOK; // This command is used to view all rows and columns of table

Similarly you can do this as this command describe your table as per given requirements.

SELECT BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT, BOOK_COST, AVG(BOOK_COST)AS AVG_COST
FROM BOOK
GROUP BY BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT, BOOK_COST;

Please run this command and check out the result.

Similarly you can do this for remaining table and describe your table as per given requirements.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.