Chapter 3 Exercise The following is a set of tables for the Art Course database

Question

Chapter 3 Exercise The following is a set of tables for the Art Course database shown in Figure 1-12. For the data for these tables, use the data shown in Figure 1-12 in chapter 1 of the text. CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, 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 CustomerNumber and CourseNumber are surrogate keys. Therefore, these numbers will never be modified, and there is no need for cascading updates. No customer data are ever deleted so there is no need to cascade deletions. Courses can be deleted. If there are enrollment entries for a deleted class, they should also be deleted. These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow. Run these statements your database in Amazon Web Services to obtain results. For each SQL statement you write, show the results based on these data. Use data types consistent with the DBMS you are using. 3.1 Write and run the SQL statements necessary to create the tables and their referential integrity constraints. Assumption: CustomerNumber and CourseNumber are surrogate keys starting at 1 and incrementing by 1. 3.2 Populate the tables with the data in Figure 1-12 3.3 Write and run an SQL query to list all occurrences of Adv Pastels in the COURSE table. Include all associated data for each occurrence of the class 3.4 Write and run an SQL query to list all students and courses they are registered for. Include, in this order, CustomerNumber, CustomerLastName, CustomerFirstName, Phone, CourseNumber, and AmountPaid 3.5 Write and run an SQL query to list all students registered in Adv Pastels starting on October 1, 2017. Include in this order, Course, CourseDate, CustomerLastName CustomerFirstName, Phone, Fee, and AmountPaid. Use a join using JOIN ON syntax. Can this query be run using one or more subqueries? If not, why not? This query cannot be run using subqueries since its result requires data from all three of the tables involved in the query

Explanation / Answer

--5.1

CREATE TABLE CUSTOMER (

--ColumnName DataType CONSTRAINTs

CustomerNumber INT NOT NULL IDENTITY(1, 1),

--we need to give lenght to varchar. 20 is maximum allowable charecters

CustomerLastName VARCHAR(20),CustomerFirstName VARCHAR(20),

Phone VARCHAR(20),

PRIMARY KEY (CustomerNumber)

);

CREATE TABLE COURSE (

CourseNumber INT NOT NULL IDENTITY(1, 1),

Course VARCHAR(20),

CourseDate Date,

FEE DECIMAL(10,2),

PRIMARY KEY (CourseNumber)

);

CREATE TABLE Enrollment (

CustomerNumber INT,CourseNumber,AmountPaid,

FOREIGN KEY (CustomerNumber) REFERENCES CUSTOMER(CustomerNumber),

FOREIGN KEY (CourseNumber) REFERENCES COURSE(CourseNumber)

);

--3.2 PLease share image (Figure 1-12) shown in questions to provide insert quires

--3.3

SELECT * FROM COURSE WHERE Course='Adv Pastels';

--3.4

SELECT c.CustomerNumber,c.CustomerLastName,c.CustomerFirstName,C.Phone,Co.CourseNumber,co.AmountPaid

--INNER JOIN brings matching columns between 2 table base on ON condition

FROM CUSTOMER c INNER JOIN Enrollment co ON c.CustomerNumber=co.CustomerNumber

--3.5

SELECT c.CustomerNumber,c.CustomerLastName,c.CustomerFirstName,C.Phone,Co.CourseNumber,co.AmountPaid

FROM CUSTOMER c INNER JOIN Enrollment co ON c.CustomerNumber=co.CustomerNumber

INNER JOIN COURSE cou ON co.CourseNumber=cou.CourseNumber

WHERE cou.Course='Adv Pastels';

--Query can't run using sub query. we have to pull data from multiple tables

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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