Question

Assume that the following relationships were created in a database.

CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone)

COURSE (CourseNumber, CourseTitle, TeachingMode, CourseCreationDate, Fee)

ENROLLMENT (EnrollmentID, CustomerNumber, CourseNumber, EnrollmentDate, AmountPaid)

Legend:
Primary Key

Foreign Key

Possible values

TeachingMode: PRE - Presencial, ONL - Online


Write the SQL (ORACLE) statements required to complete what is required below. Provide ONE instruction per request / question. Write your answers in the space provided. Identify each answer with the corresponding request / question number.

1. Create the CUSTOMER table include the constraints 
2. Create the COURSE table include the constraints 
3. Create the ENROLLMENT table include the constraints 
4. Create the sequence seqEnroll 
5. Insert a row in the CUSTOMER table. 
6. Insert a row in the COURSE table. 
7. Insert a row in the ENROLLMENT table. The primary key field is a substitute (Surrogate / Artificial) so it will use the sequence you created in statement 4. The transaction must belong to the CUSTOMER you created in # 5 and the COURSE you created in # 6. 
8. List customers in ascending alphabetical order by last name. Include the following information in this order: last name, first name and telephone number 
9. List all the courses that have the word Pastels in the title. Include all the data in the COURSE table. 
10. List all the courses the clients are enrolled in. Include the following data in this order: CustomerNumber, CourseNumber, and AmountPaid. 
11. List all courses that started on or before October 1, 2018. Include the following data in this order: CourseDate, CourseNumber, CourseTitle, Fee. 
12. Show for each client the total paid for classroom courses and total paid for online courses TeachingMode the total paid by each client. 
Show courses that are cheaper than the average price Use subquery and functions. 
13. List all course information if you have had at least ten clients enrolled in the past twelve months. It must work at any time, the twelve-month period will depend on the date the consultation is run. DO NOT USE SPECIFIC DATES. Use subquery and functions.

Answer 1

Assuming the schema name as CD. If schema name is different then replace CD with that.

1. CREATE TABLE CD.CUSTOMER (
    CustomerNumber VARCHAR2(50) NOT NULL,
    CustomerLastName VARCHAR2(50) NOT NULL,
    CustomerFirstName VARCHAR2(50) NOT NULL,
    Phone VARCHAR2(50) NOT NULL,
PRIMARY KEY (CustomerNumber)
);

2. CREATE TABLE CD.COURSE(
    CourseNumber VARCHAR2(50) NOT NULL,
    CourseTitle VARCHAR2(50) NOT NULL,
    TeachingMode VARCHAR2(50) NOT NULL,
    CourseCreationDate DATE,
    Fee NUMBER(7,2) NOT NULL,
PRIMARY KEY (CourseNumber)
);

3. CREATE TABLE CD.COURSE(
    EnrollmentID NUMBER(5) NOT NULL,
    CustomerNumber VARCHAR2(50) NOT NULL,
    CourseNumber VARCHAR2(50) NOT NULL,
    EnrollmentDate DATE,
    AmountPaid NUMBER(7,2),
PRIMARY KEY (EnrollmentID)
FOREIGN KEY (CustomerNumber) REFERENCES CUSTOMER(CustomerNumber),
FOREIGN KEY (CourseNumber) REFERENCES COURSE(CourseNumber)
);

4. CREATE SEQUENCE seqEnroll
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

5. INTO CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone) VALUES ('CUST001','Joseph', 'Alex','+110023883')
INTO CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone) VALUES ('CUST002','Newton', 'Issac','+110023865')
INTO CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone) VALUES ('CUST003','Einstein', 'Albert','+110023844')
SELECT * FROM dual;

6. INSERT ALL
INTO COURSE(CourseNumber, CourseTitle, TeachingMode, CourseCreationDate, Fee) VALUES ('COUR001','CSE', 'ONL','01-APR-2017')
INTO CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone) VALUES ('CUST002','ECE', 'ONL','01-AUG-2019')
INTO CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone) VALUES ('CUST003','MECH', 'PRE','01-AUG-2018')
SELECT * FROM dual;

7. INSERT INTO ENROLLMENT(EnrollmentID, CustomerNumber, CourseNumber, EnrollmentDate, AmountPaid) VALUES (seqEnroll.nextval,'CUST001', 'COUR001','01-AUG-2018',500);

8. SELECT
CustomerLastName, CustomerFirstName, Phone
FROM
CUSTOMER ORDER BY CustomerLastName ASC;

9. SELECT *
FROM
COURSE WHERE CourseTitle LIKE '%Pastels%';

10. SELECT
CustomerNumber, CourseNumber, AmountPaid
FROM ENROLLMENT
INNER JOIN CUSTOMER USING(CustomerNumber)
INNER JOIN COURSE USING(CourseNumber);

11. SELECT
CourseDate, CourseNumber, CourseTitle, Fee
FROM COURSE
WHERE CourseCreationDate<='October 1, 2018';

12. Function Created

CREATE OR REPLACE FUNCTION get_CheaperCource (avg_fee)
RETURN VARCHAR
IS
courseName VARCHAR(50) := NULL;
BEGIN
FOR courceNme IN (SELECT CourseTitle FROM COURSE WHERE FEE< avg_fee) LOOP
courseName := courseName || ',' || courceNme.CourseTitle;
END LOOP;
RETURN LTRIM(courseName, ',');
END;

Select Query

SELECT
CustomerLastName, CustomerFirstName , CourseTitle, TeachingMode, AmountPaid,
get_CheaperCource(AVG(Fee)) AS CourseTitle
FROM ENROLLMENT
INNER JOIN CUSTOMER USING(CustomerNumber)
INNER JOIN COURSE USING(CourseNumber);

13. SELECT (SELECT CourseTitle FROM COURSE WHERE CourseNumber = ENROLLMENT.CustomerNumber) AS CourseTitle,
(SELECT TeachingMode FROM COURSE WHERE CourseNumber = ENROLLMENT.CustomerNumber) AS TeachingMode,
(SELECT Fee FROM COURSE WHERE CourseNumber = ENROLLMENT.CustomerNumber) AS Fee
FROM ENROLLMENT WHERE CourseTitle IN
(SELECT CourseTitle from ENROLLMENT GROUP BY CourseTitle HAVING COUNT(CourseTitle) >=10 AND EnrollmentDate BETWEEN TO_DATE (TO_CHAR(SYSDATE), 'yyyy/mm/dd'))
BETWEEN ADD_MONTHS(TRUNC(sysdate, 'month'), -12) and TRUNC(sysdate, 'month')