· 9 years ago · Oct 11, 2016, 11:28 PM
1
2/*-----------------------------------------
3 Create a database to use if needed.
4 -----------------------------------------*/
5CREATE DATABASE IF NOT EXISTS hw4jackc;
6USE hw4jackc;
7
8/*----------------------------------------
9 3.52 - Create tables with refreential
10 intergrity contraints based on 1.10
11 ----------------------------------------*/
12
13CREATE TABLE CUSTOMER
14(
15 CustomerNumber int AUTO_INCREMENT,
16 CustomerLastName varchar(255) NOT NULL,
17 CustomerFirstName varchar(255) NOT NULL,
18 Phone varchar(255),
19 CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerNumber)
20);
21
22CREATE TABLE COURSE
23(
24 CourseNumber int AUTO_INCREMENT,
25 Course varchar(255) NOT NULL,
26 CourseDate varchar(255) NOT NULL,
27 Fee varchar(255) NOT NULL DEFAULT '$0.00',
28 CONSTRAINT COURSE_PK PRIMARY KEY (CourseNumber)
29);
30
31CREATE TABLE ENROLLMENT
32(
33 CustomerNumber int NOT NULL,
34 CourseNumber int NOT NULL,
35 AmountPaid varchar(255) NOT NULL DEFAULT '$0.00',
36 CONSTRAINT CUST_ENROLL_FK FOREIGN KEY(CustomerNumber)
37 REFERENCES CUSTOMER(CustomerNumber),
38 CONSTRAINT COURSE_ENROLL_FK FOREIGN KEY(CourseNumber)
39 REFERENCES COURSE(CourseNumber)
40);
41
42/*---------------------------------------
43 3.53 - Populate the tables with data
44 ---------------------------------------*/
45
46INSERT INTO CUSTOMER
47 (CustomerLastName, CustomerFirstName, Phone)
48 VALUES
49 ('Johnson', 'Ariel', '206-567-1234'),
50 ('Green', 'Robin', '425-678-8765'),
51 ('Jackson', 'Charles', '360-789-3456'),
52 ('Pearson', 'Jeffery', '206-567-2345'),
53 ('Sears', 'Miguel', '360-789-4567'),
54 ('Kyle', 'Leah', '425-678-7654'),
55 ('Myers', 'Lynda', '360-789-5678');
56
57INSERT INTO COURSE
58 (Course, CourseDate, Fee)
59 VALUES
60 ('Adv Pastels', '10/1/2013', '$500.00'),
61 ('Beg Oils', '9/15/2013', '$350.00'),
62 ('Int Pastels', '3/15/2013', '$350.00'),
63 ('Beg Oils', '10/15/2013', '$350.00'),
64 ('Adv Pastels', '11/15/2013', '$500.00');
65
66INSERT INTO ENROLLMENT
67 (CustomerNumber, CourseNumber, AmountPaid)
68 VALUES
69 (1, 1, '$250.00'),
70 (1, 3, '$350.00'),
71 (2, 2, '$350.00'),
72 (3, 1, '$500.00'),
73 (4, 1, '$500.00'),
74 (5, 2, '$350.00'),
75 (6, 5, '$250.00'),
76 (7, 4, '$0.00');
77
78/*-----------------------------------------
79 3.54 - List all occurrences & associated
80 data.
81 -----------------------------------------*/
82
83SELECT *
84FROM COURSE
85WHERE Course='Adv Pastels';
86
87/*-----------------------------------------
88 3.55 - List all students and courses they
89 are registered for.
90 -----------------------------------------*/
91
92SELECT C.CustomerNumber, CustomerLastName, CustomerFirstName, Phone, CourseNumber, AmountPaid
93FROM CUSTOMER AS C, ENROLLMENT AS E
94WHERE C.CustomerNumber = E.CustomerNumber;
95
96/*-----------------------------------------
97 3.56 - List all students registered in Adv.
98 Pastels starting on October 1, 2013.
99 -----------------------------------------*/
100
101# Way #1 - WHERE Joinage
102SELECT Course, CourseDate, Fee, CustomerLastName, CustomerFirstName, Phone
103FROM CUSTOMER AS C, COURSE AS CO, ENROLLMENT AS E
104WHERE C.CustomerNumber = E.CustomerNumber
105 AND CO.CourseNumber = E.CourseNumber
106 AND CourseDate = '10/1/2013'
107 AND Course = 'Adv Pastels';
108
109# Way #2 - Subquery Joinage
110SELECT Course, CourseDate, Fee, CustomerLastName, CustomerFirstName, Phone
111FROM CUSTOMER AS C, COURSE AS CO
112WHERE C.CustomerNumber IN
113 (SELECT E.CustomerNumber
114 FROM ENROLLMENT AS E
115 WHERE E.CourseNumber = CO.CourseNumber
116 AND Course='Adv Pastels'
117 AND CourseDate = '10/1/2013'
118 );
119
120/*-----------------------------------------
121 3.57 - List all students registered in Adv.
122 Pastels starting on October 1, 2013. (Use
123 Join)
124 -----------------------------------------*/
125
126SELECT Course, CourseDate, Fee, CustomerLastName, CustomerFirstName, Phone
127FROM CUSTOMER AS C JOIN COURSE AS CO JOIN ENROLLMENT AS E
128 ON C.CustomerNumber = E.CustomerNumber
129 AND E.CourseNumber=CO.CourseNumber
130 AND Course='Adv Pastels'
131 AND CourseDate = '10/1/2013';
132
133/*-----------------------------------------
134 Drop the database
135 -----------------------------------------*/