The following questions refer to the Art Course database. The database contains
ID: 3854857 • Letter: T
Question
The following questions refer to the Art Course database.
The database contains data shown in Figure 1-10.
CUSTOMER (CustomerNumber, CustomerName, Phone)
COURSE (CourseNumber, Course, CourseDate, Fee)
ENROLLMENT (CustomerNumber, CourseNumber, AmountPaid)
where
CustomerNumber in ENROLLMENT must exist in CustomerNumber in CUSTOMER
CourseNumber in ENROLLMENT must exist in CourseNumber in COURSE
Write SQL queries to produce the following results:
1) List all students and courses they are registered for. Include, in this order, CustomerNumber, CustomerName, Phone, CourseNumber, and AmountPaid.
2) List the total amount paid for each course starting after July 4, 2009. Include, in this order, CourseNumber, CourseName, and total amount paid for that course.
3) List the average amount paid for each customer. Include, in this order, CustomerName and average amount paid by that customer.
4) List all students registered in the Adv. Pastels starting on October 1, 2009. Include, in this order, CourseName, CourseDate, Fee, CustomerName, and Phone.
Explanation / Answer
1. The query for the first question is as follows:
SELECT CustomerName FROM CUSTOMER
GROUP BY CustomerName , Course
2. The query for the second question is as follows:
SELECT CustomerName,AmountPaid FROM CUSTOMER,ENROLLMENT
WHERE CourseDate=> July 4,2009.
3. The query for the third question is as follows:
SELECT CustomerName, AmountPaid FROM CUSTOMER, ENROLLMENT WHERE AverageAmountPaid.
4. The query for the fourth question is as follows:
SELECT Order,CourseName,CourseDate,Fee,CustomerName,Phone FROM CUSTOMER,ENROLLMENT,COURSE WHERE ADV => October 1,2009.
Note: You have not listed Order Table and hence, I have assumed it in the last question to answer the order part.
Hence , these are all the query realted to the problem statement above. Hope you understand it.
Please rate the answer if it helped.....Thankyou
Hope it helps....
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.