The database is used by an art school to keep track of courses and enrollment fo

Question

The database is used by an art school to keep track of courses and enrollment for the courses they offer. The database consists of three tables

CUSTOMER- Keeps track of Customers for the art school

COURSE-Keeps track of the Courses offered by the art school

ENROLLMENT-Keeps track of enrollment in terms of customer, course and amount paid

Write an SQL statement that shows the CustomerLastName,CustomerFirstName and Phone for all customers at the Art school sorted by lastname in descending order. Save this query as Query1   

Write an SQL statement that shows all the fields in the COURSE table. Save this query as query 2

Write an SQL statement that shows the CustomerNumber for all customers that are enrolled in a course. It should only show the CustomerNumber once. Note that not all customers are enrolled in a course. Save this query 3.

Write an SQL statement that shows the CustomerNumber, CustomerFirstName, CustomerLastName for customers who have a CustomerNumber greater than 5. Save this as Query4.

Write an SQL statement that shows the Course, Course Date for courses that have a fee more than $400 OR the course is a Beg Oils courses. Save this as Query5

Write an SQL statement that shows the Course, CourseDate and Fees for courses Adv Pastels, Beg Oils and Int Pastels. Save this as Query 6

Write an SQL statement that shows CustomerFirstName, CustomerLastName and Phone for customers who have a customer number between 4 and 8. Save this as Query 7

Write an SQL statement that shows the Course, CourseDate and Fees for courses that start with the characters ‘Adv’. Save this as Query 8

Write an SQL statement that shows the CustomerLastName, CustomerFirstName for customers who have a missing/null value for phone. Save this as Query 9

Write an SQL statement that shows the count of how many customers are enrolled in courses as EnrollmentNumber, the minimum amount paid as MinAmtPaid, the maximum amount paid as MaxAmtPaid and the total sum of amount paid as SumAmtPaid. Save this query as Query 10

Write an SQL statement that shows the CourseNumber and the number of times the course has been offered as NumberCourseOffered. Save this query as Query 11

Write an SQL Subquery that will show CustomerFirstName, CustomerLastName and Phone for customers who have enrolled in courses but made a zero payment. Save this query as Query 12

Write an SQL statement that will show CustomerFirstName, CustomerLastName, Phone and CourseNumber for customers who have enrolled in courses but made a zero payment. Save this query as Query 13

Write an SQL statement that will show CustomerFirstName, CustomerLastName, Phone, CourseNumber, CourseName for customers who have enrolled in courses but made a zero payment. Save this query as Query 14

Write an SQL statement that will show CustomerFirstName, CustomerLastName and total amount paid for all courses as TotalPaid. Save this query as Query15

Explanation / Answer

Query1 :

Select CustomerLastName,CustomerFirstName ,Phone from CUSTOMER order by CustomerLastName desc.

Query2:

Select * from COURSE;

Query3:

Select distinct CustomerNumber from ENROLLMENT;

Query4:

Select CustomerNumber,CustomerFirstName, CustomerLastName from CUSTOMER where CustomerNumber>5;

Query5:

select Course, CourseDate from COURSE where fee > 400 or course='Beg Oils';

Query6:

Select Course,CourseDate,Fee from COURSE where course in ('Adv Pastels','Beg Oils','Int Pastels');

Query7:

Select CustomerFirstName, CustomerLastName ,Phone from CUSTOMER where CustomerNumber between 4 and 8;

Query8:

Select Course, CourseDate ,Fee from COURSE where Course like 'Adv%';

Query9 :

Select CustomerLastName, CustomerFirstName from CUSTOMER where phone='NULL';

Query10:

Select count(CustomerNumber) as EnrollmentNumber,min(AmountPaid) as MinAmtPaid,max(AmountPaid) as MaxAmtPaid ,sum(AmountPaid) as SumAmtPaid from ENROLLMENT;

Note: As per the chegg policy, it is not allowed to asnwer more than 10 question at time. please ask the remaing quetion in the next section.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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