+1443 776-2705 panelessays@gmail.com
  

Summary: SQL Queries Using MySQL DBMS. Reference the attached video (Course Project Ph1 VIDEO.mp4), for a full introduction and demonstration for the MySQL DBMS, and general background for your Course Project Ph1. 

Deliverables:

SQL File as follows: YourLastName_Project_Ph1_Query.sql. File must contain SELECT statement and required clauses to address the required tasks note below (Query 1-10).

MS Word as follows: YourLastName_Project_Ph1_Output. This file contains the queries and outputs.

Zipped file, students are required to upload a ZIP file containing these two documents.

Required Software:

MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.

Lab Steps:

Step 1: Download and install the MySQL DBMS System by Oracle. Follow the step-by-step instructions attached to install the MySQL DBMS in your computer. Also, reference the supplemental text: Available for Free download as part of our UC Library.  MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2.  Link to UC Library: http://search.ebscohost.com/login.aspx?direct=true&AuthType=shib&db=nlebk&AN=291311&site=eds-live&ebv=EB&ppid=pp_ii

Follow the Step-by-Step instructions attached, to install MySQL in your computer attached as follows: “Instructions to Install MySQL.pdf”. 

Step 2: Create a MySQL Database. Watch the attached video, Course Project Ph1 VIDEO.mp4, with instructions/demonstrations to create a database using MySQL. Also, reference the supplemental text: Available for Free download as part of our UC Library.  MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2.  Link to UC Library: http://search.ebscohost.com/login.aspx?  This textbook provides supplemental information to install MySQL and how to create a database. Download the file Project_ph1_ScriptMySQL. File attached. 

Step 3: Run the Script File in MySQL.

Step 4: Create SQL SELECT Queries

Using the data provided for you in the Student table in the database, formulate a SQL script file named YourLastName_Project_Ph1_Query.sql. This file would contain queries to execute each of the tasks required below.

Be sure to read and reference the dialect presented by MySQL, as it is unique and special in our space of SQL programming. Be sure to read/reference the supplemental text: Available for Free download as part of our UC Library.  MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2. 

Once you run each query, save and past the output in a MS Word document. Name your file YourLastName_Project_Ph1_Output.

 Query # 1 –  Write a SQL statement to produce output as follows: Student’s First and Last Name. Query # 2 – Write a SQL statement to produce output as follows: the Major of the STUDENT with no duplications. Do not display student names.  Query # 3 – Write a SQL statement to produce output as follows: First and Last Name of students who live in the Zip code 32828.  Query # 4 – Write a SQL statement to produce output as follows: First and Last Name of students who live in the Zip code 97912 and have the major of CS.  Query # 5 – Write a SQL statement to produce output as follows: First and Last Name of students who live in the Zip code 32826 or 33186. Do not use IN.  Query # 6 – Write a SQL statement to produce output as follows: the First and Last Name of students who have the major of Business or Math. Use IN. Query # 7 – Write a SQL statement to produce output as follows: the First and Last Name of students who have the Class greater than 1 and less than 5. Use BETWEEN.  Query # 8 – Write a SQL statement to produce output as follows: First and Last Name of students who have the Last name that starts with an M.  Query # 9 – Write a SQL statement to produce output as follows: First and Last name of students having an o in the second position in their First Name.  Query # 10 – Write a SQL expression to produce output as follows: display the Status for, and the number of occurrences that apply to each status. You must use the Count(*) function. You must show the result of the Count(*) function as CountStatus. Group by Status and output the results in ascending order of CountStatus.  

Step 5: Submit your work. Once you complete ALL tasks noted above, zip the files YourLastName_Project_Ph1_Query.sql AND YourLastName_Project_Ph1_Output and submit them.

Rubric:

Queries that are correct will be awarded the number of points shown below:

20 points: each query 1-10. Total of 200 Points.

The below rubric specifications applied to this assignment. Point deductions will apply as follows: 

0 points: If the student does not complete any of the required queries per the specifications.  -15 points: Query submitted simply will not run -10 points: Query submitted runs. BUT is partially correct as the query required a WHERE statement, but the student did not included in the query.   -8 points: Query submitted runs. BUT is partially correct as the quired required a WHERE statement, but contained ERRORS, the query outputs pop-ups requesting user input, or partially addresses the requirements presented. 

*****IMPORTANT**** The submission MUST be ONE zipped file (not an RAR file) with the two files listed below included.  The actual SQL file (not copied into any other type of document) and the Word document.****   If any other type of submission is submitted 0 points will be earned. “YourLastName” means to replace with your actual name. 

Installing MySQL
I T S 5 3 8 : D ATA B A S E S Y S T E M S

ITS 538
Database Systems

Course Project Ph1

Miguel Buleje, PhD
[email protected]

MySQL- Intro

• MySQL is a an open source DBMS by Oracle with a Community
Version free to down load. I will demonstrate in this presentation how
to install the latest version for MySQL at this time that is V.8, on a 64
bit version of WIN 10.

• Components Required to complete your Projects:

• MySQL Server

• MySQL Workbench

• I will present in this instructional document a series of screen shoots
to walk you through the installation process. We are going to use the
Installer utility to install the latest MySQL Community Server and
Workbench.

Step 1: Visit the URL below (dev.mysql.com)

Step 1: Navigate to “Downloads”

Step 3: Select “MySQL Community Server”

Step 4: MySQL Installer – “Go to Download Page”

Step 5: Select OS AND Download Smaller File

Step 6: Start Download (follow the option below)

Step 7: Open Downloaded File

Step 8: Chose Setup Type – “Custom”

Step 9 (a): Select Products and Features
(Select MySQL Server V8.0.20 x64 & Workbench V8.0.20 – x64 for bit system)

Step 9 (b): Select Products and Features
(Select MySQL Server V8.0.20 x64 & Workbench V8.0.20 – x64 for bit system)

Step 9 (c): Select Products and Features
(Select MySQL Server V8.0.20 x64 & Workbench V8.0.20 – x64 for bit system)

Step 10: Initiate Installation – “Execute”

Step 11: Complete Installation – “Next”

Step 12: Product Configuration – “Next”

Step 13: High Availability
(Standalone MySQL Server/ Classic MySQL Replication)

Step 14: Type and Networking
Config. Type: Development Computer
Port: XXXX (Must keep record for this number. Please write down)
All other settings intact.

Step 15: Authentication Method
(Use Legacy Authentication Method)

Step 16: Accounts and Roles
(Write down your Root Password – very important)

Step 17: Windows Service
(Configure MySQL Server as a Windows Service)

Step 18: Apply Configuration – “Execute”

Step 19: Apply Configuration – “Finish”

Step 20: Product Configuration – “Next”

Step 21: Apply Configuration – “Finish”

Step 22: Installation Complete – “Finish”

Step 23: Welcome to MySQL Workbench
(Use authentication / credentials you created)

Step 24: SQL Workbench – READY!

DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CAMPUS;
DROP TABLE IF EXISTS SEMESTER;
DROP TABLE IF EXISTS ROOM;
DROP TABLE IF EXISTS GRADES;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS INSTRUCTOR;
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS STUDENT_GRADE;

CREATE TABLE STUDENT(
STUDENT_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
Last_Name VARCHAR(30),
First_Name VARCHAR(30),
Address VARCHAR(40),
ZIP VARCHAR(10),
MAJOR VARCHAR(20),
CLASS int(2),
STATUS VARCHAR(20));

INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Morote’,’Italo’,’P.O. Box 619, 5808 Semper St.’,’32828′,’MATHEMATICS’,2,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Martin’,’Yacky’,’313-6948 Nunc, Ave’,’36702′,’SPANISH’,3,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Yostin’,’Duglas’,’Ap #593-8291 Id St.’,’26151′,’BUSINESS’,1,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Dasti’,’Cosimel’,’328-4572 Elit Rd.’,’95886′,’SPANISH’,2,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Dixon’,’Steve’,’Ap #547-7314 Feugiat. Street’,’42756′,’PHIL’,1,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Alfaro’,’Patricia’,’P.O. Box 977, 6922 Sed Street’,’52838′,’ENGLISH’,1,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Farruco’,’Rose’,’Ap #179-4485 Lectus Road’,’10816′,’ENGLISH’,3,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Pope’,’Vector’,’Ap #878-2252 Curabitur Av.’,’53175′,’PHYSICS’,3,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Los’,’Angeles’,’Ap #552-758 Convallis St.’,’56968′,’FINANCE’,3,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Diaz’,’Cameron’,’Ap #145-1961 Sed Street’,’63397′,’BUSINESS’,3,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Gonzalez’,’Andew’,’Ap #141-9425 In Road’,’87521′,’BUSINESS’,4,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Michael’,’Jordan’,’Ap #412-5802 Venenatis Rd.’,’77696′,’MARKETING’,2,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Chelerson’,’Scholy’,’911-8666 A Rd.’,’32829′,’CIS’,2,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Payton’,’Tanya’,’5759 Nec, Street’,’23461′,’SPANISH’,3,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Jorge’,’Michael’,’Ap #642-7393 Ut, Ave’,’33186′,’PHIL’,1,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Cretiza’,’Noitus’,’8659 Arcu. Avenue’,’91267′,’PHYSICS’,2,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Delami’,’Oscar’,’3512 Pellentesque. St.’,’33481′,’MATHEMATICS’,4,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Francisco’,’Zoeter’,’285-9157 Proin Road’,’98674′,’CS’,1,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Cohen’,’Aurora’,’P.O. Box 957, 9450 Arcu Street’,’30350′,’PHIL’,3,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Black’,’Joshua’,’3655 Sed St.’,’95684′,’FRENCH’,2,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Barber’,’Michael’,’875-9349 Magnis St.’,’66498′,’PSY’,4,’SATISFACTORY’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Preston’,’Inez’,’Ap #829-845 Nisi Av.’,’27404′,’PSY’,1,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Direz’,’Blaster’,’439-1369 Cursus Av.’,’60644′,’MARKETING’,2,’FAILING’);
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
VALUES(‘Aiton’,’Valeria’,’7513 Vitae Street’,’61245′,’MARKETING’,2,’FAILING’);

CREATE TABLE CAMPUS
(
CAMPUS_NAME VARCHAR(45) NOT NULL,
Address VARCHAR(40),
ZIP VARCHAR(10),
PHONE_NO VARCHAR(20),
UNIQUE(CAMPUS_NAME),
PRIMARY KEY(CAMPUS_NAME));

INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘New York Campus’,’405-6298 In Street’,’62840′,’1-639-674-3520′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Chicago’,’820-7808 Risus, Rd.’,’51852′,’1-824-430-8362′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘San Francisco Campus’,’505-182 Luctus, Ave’,’21345′,’1-460-439-2331′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Seattle Campus’,’P.O. Box 215, 1469 Eu Rd.’,’52681′,’1-448-789-7971′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Washington Campus’,’631-5139 Leo. St.’,’29402′,’1-282-625-5720′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Los Angeles Campus’,’6161 Nostra, Road’,’83643′,’1-547-459-9473′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Denver Campus’,’9309 Cras Road’,’23421′,’1-680-673-7298′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Austin Campus’,’P.O. Box 878, 7621 Donec Road’,’73261′,’1-755-573-0468′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Houston Campus’,’P.O. Box 406, 9600 Odio Ave’,’32802′,’1-721-423-4315′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Detroit Campus’,’7574 Semper St.’,’21211′,’1-583-233-9026′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘San Diego Campus’,’6939 Ligula. St.’,’75022′,’1-941-394-7241′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘San Antonio Campus’,’7422 Vel Avenue’,’63763′,’1-604-359-7505′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Memphis Campus’,’P.O. Box 328, 5280 Donec Rd.’,’75068′,’1-865-208-5487′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Portland Campus’,’788-7924 Tellus. Street’,’75088′,’1-260-552-0600′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Miami Campus’,’518-7627 Ac Avenue’,’89090′,’1-262-155-0231′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Phoenix Campus’,’921-4825 Nullam Rd.’,’23117′,’1-324-211-7180′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Dallas Campus’,’Ap #202-6418 Eleifend Ave’,’54674′,’1-663-374-3235′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Atlanta Campus’,’927-3569 Id Road’,’71661′,’1-315-710-9509′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘San Jose Campus’,’8637 Ullamcorper Rd.’,’10001′,’1-224-161-4524′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Las Vegas Campus’,’P.O. Box 169, 7139 Arcu Rd.’,’83588′,’1-789-125-9687′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Indianapolis Campus’,’744-3904 Vulputate, Road’,’37762′,’1-640-455-9785′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Baltimore Campus’,’2849 At Ave’,’40882′,’1-299-696-4483′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Cleveland Campus’,’P.O. Box 785, 7330 Urna. St.’,’55794′,’1-789-114-1794′);
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
VALUES(‘Oklahoma Campus’,’172-3924 At Ave’,’31619′,’1-111-206-2900′);

CREATE TABLE SEMESTER
(
SEMESTER_NAME ENUM(‘FALL’, ‘SPRING’, ‘SUMMER’));

DROP TABLE IF EXISTS ROOM CASCADE;

CREATE TABLE ROOM
(
Campus_Name VARCHAR(45),
ROOM_NO VARCHAR(10),
FACILITY VARCHAR(45),
CAPACITY int(3)
);

INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘New York Campus’,’6′,’Lloydminster’,30);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Chicago’,’19’,’Erode’,32);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘San Francisco Campus’,’40’,’Cantalupo in Sabina’,38);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Seattle’,’40’,’Assiniboia’,25);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Washington Campus’,’23’,’Kessenich’,42);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Los Angeles Campus’,’47’,’Stony Plain’,46);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Denver Campus’,’29’,’Ulm’,44);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Austin Campus’,’46’,’Bielefeld’,39);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Houston Campus’,’37’,’Georgia’,38);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Detroit Campus’,’29’,’Lummen’,30);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘San Diego Campus’,’3′,’Pak Pattan’,33);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘San Antonio Campus’,’37’,’South Perth’,27);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Memphis Campus’,’35’,’Meeuwen’,14);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Portland Campus’,’20’,’Belcarra’,23);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Miami Campus’,’5′,’Jette’,27);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Phoenix Campus’,’24’,’Milwaukee’,18);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Dallas Campus’,’23’,’Afsnee’,39);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘San Jose Campus’,’50’,’Stamford’,26);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Atlanta Campus’,’5′,’Stamford’,21);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Las Vegas Campus’,’40’,’Morgex’,11);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Indianapolis Campus’,’1′,’Montague’,21);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Baltimore Campus’,’40’,’Cumberland’,21);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Cleveland Campus’,’4′,’Harrison Hot Springs’,32);
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
VALUES(‘Oklahoma Campus’,’10’,’Sainte-Ode’,15);

CREATE TABLE GRADES
(
Grade ENUM(‘A’,’B’,’C’,’D’,’F’,’I’,’W’)
);

CREATE TABLE COURSE
(
Course_No Varchar(10) NOT NULL,
Course_Name Varchar(25),
CreditHours int(2),
UNIQUE(COURSE_NO),
PRIMARY KEY(COURSE_NO)
);

INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1043′,’TECH121’,1);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1006′,’Psy101’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1696′,’Psy101’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1291′,’HUM234’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1841′,’Psy101’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1697′,’ELE102’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1292′,’LIT405’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1698′,’ELE101’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1255′,’Math101’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1842′,’TECH121’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1010′,’Psy101’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1810′,’Psy101’,1);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1044′,’Math131’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1293′,’Math131’,1);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1528′,’CS234’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1324′,’PHY345’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1045′,’Math101’,1);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1659′,’CS211’,1);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1294′,’PHY345’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1144′,’Psy101’,2);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1843′,’ELE102’,1);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1569′,’Math101’,3);
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
VALUES(‘1811′,’ENG304’,3);

CREATE TABLE INSTRUCTOR
(
INSTRUCTOR_ID VARCHAR(10) NOT NULL,
LAST_NAME VARCHAR(30),
FIRST_NAME VARCHAR(20),
ADDRESS VARCHAR(40),
ZIP VARCHAR(10),
OFFICE_NO VARCHAR(10),
UNIQUE(INSTRUCTOR_ID),
PRIMARY KEY(INSTRUCTOR_ID)
);

INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘400′,’Thane’,’Puckett’,’P.O. Box 895, 191 Erat Rd.’,’53841′,’30’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘737′,’Jared’,’Tate’,’Ap #177-1334 Faucibus St.’,’88675′,’21’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘640′,’Yuli’,’Hurst’,’Ap #799-5088 Scelerisque St.’,’26162′,’12’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘256′,’Micah’,’Pace’,’P.O. Box 638, 2109 Adipiscing Rd.’,’40665′,’12’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘741′,’Kasper’,’Duran’,’565-193 Nunc Avenue’,’55756′,’17’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘653′,’Dolan’,’Moore’,’3869 Cursus Road’,’37236′,’10’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘688′,’Ciaran’,’Blackwell’,’Ap #754-6723 Faucibus Rd.’,’83479′,’17’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘940′,’Kennedy’,’Fowler’,’749-1142 Non Av.’,’98172′,’22’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘499′,’Devin’,’Acosta’,’7613 Vel Road’,’93686′,’19’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘280′,’Driscoll’,’Justice’,’P.O. Box 113, 3705 Ut, St.’,’69877′,’23’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘937′,’,Cairo’,’Donaldson’,’6470 Adipiscing Rd.’,’63032′,’26’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘415′,’Hammett’,’Glenn’,’262-2645 At Rd.’,’84839′,’22’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘549′,’Elliott’,’Donaldson’,’5470 Dolor. Street’,’62489′,’23’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘639′,’Lamar’,’Robles’,’5719 Consectetuer St.’,’20213′,’25’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘767′,’Colin’,’Riley’,’P.O. Box 114, 1663 Ultrices. Street’,’23254′,’16’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘344′,’Richard’,’Thornton’,’P.O. Box 931, 8187 Lacinia. St.’,’29349′,’23’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘581′,’Raymond’,’Snow’,’1032 Est St.’,’66699′,’20’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘474′,’Nero’,’Rhodes’,’6050 Ante Ave’,’37967′,’28’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘534′,’Tate’,’Carr’,’P.O. Box 213, 1879 Purus. Ave’,’19803′,’15’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘521′,’Lance’,’Waters’,’656-7361 Conubia Ave’,’6315′,’26’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘413′,’Abbot’,’Ross’,’P.O. Box 859, 9379 Sed Avenue’,’45051′,’25’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘891′,’Dylan’,’Forbes’,’225-3164 Fermentum Ave’,’65642′,’26’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘879′,’Arsenio’,’Singleton’,’505-6318 Vel, St.’,’97288′,’10’);
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
VALUES(‘350′,’Thor’,’Copeland’,’Ap #816-3443 Magna. Avenue’,’43447′,’4′);

CREATE TABLE APPROVED_INSTRUCTOR
(
Instructor_ID VARCHAR(10) NOT NULL,
Course_No VARCHAR(10) NOT NULL
);

INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘400′,’1044’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘737′,’1528’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘640′,’1324’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘256′,’1842’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘741′,’1006’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘653′,’1431’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘688′,’1291’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘940′,’1810’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘499′,’1841’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘280′,’1211’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘937′,’1144’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘415′,’1255’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘549′,’1033’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘639′,’1881’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘767′,’1526’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘344′,’1249’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘581′,’1659’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘474′,’1298’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘534′,’1569’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘521′,’1192’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘413′,’1722’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘891′,’1511’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘879′,’1696’);
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
VALUES(‘350′,’1844’);

Create TABLE CLASS
(
Class_id VARCHAR(10) NOT NULL,
Course_No VARCHAR(10) NOT NULL,
Section VARCHAR(10),
Semester Varchar(15),
Year VARCHAR(4),
Instructor_ID VARCHAR(10) NOT NULL,
Campus Varchar(15),
Room_No VARCHAR(10),
Start_Date Date,
Start_Time Time,
UNIQUE (CLass_id),
PRIMARY KEY (Class_id)
);

INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘12408′,’1044′,’Fall’,’499′,’2014-10-08′,’8:00′,’10’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘15716′,’1006′,’Other’,’639′,’2014-10-08′,’8:00′,’17’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘12525′,’1291′,’ Other’,’767′,’2014-10-08′,’8:00′,’18’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘16500′,’1696′,’Winter’,’937′,’2015-01-03′,’8:00′,’19’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘19047′,’1291′,’Winter’,’280′,’2015-01-03′,’8:00′,’14’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘12587′,’1841′,’Other’,’280′,’2014-08-10′,’8:00′,’15’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘17106′,’1696′,’Other’,’400′,’2014-08-10′,’8:00′,’10’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘14331′,’1291′,’Other’,’413′,’2014-08-10′,’8:00′,’14’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘17246′,’1696′,’Summer’,’640′,’2015-06-12′,’8:00′,’12’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘17092′,’1255′,’Summer’,’639′,’2015-06-12′,’8:00′,’10’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘14169′,’1842′,’Fall’,’344′,’2014-08-10′,’8:00′,’13’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘13062′,’1006′,’Winter’,’879′,’2015-01-03′,’8:00′,’12’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘17267′,’1810′,’Summer’,’640′,’2015-06-12′,’8:00′,’15’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘15415′,’1044′,’Summer’,’549′,’2015-06-12′,’8:00′,’15’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘11386′,’1291′,’Fall’,’767′,’2014-08-10′,’8:00′,’10’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘14972′,’1528′,’Other’,’937′,’2014-08-10′,’8:00′,’17’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘15009′,’1324′,’Fall’,’256′,’2014-08-10′,’8:00′,’11’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘18265′,’1044′,’Winter’,’415′,’2015-01-03′,’8:00′,’12’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘16415′,’1659′,’Fall’,’474′,’2014-08-10′,’8:00′,’14’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘19464′,’1291′,’Fall’,’521′,’2014-08-10′,’8:00′,’18’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘12433′,’1144′,’Summer’,’415′,’2015-06-12′,’8:00′,’10’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘19175′,’1842′,’Winter’,’534′,’2015-01-03′,’8:00′,’10’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘19864′,’1569′,’Summer’,’280′,’2015-06-12′,’8:00′,’14’,’2014′);
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
VALUES(‘17022′,’1810′,’Other’,’940′,’2014-08-10′,’8:00′,’15’,’2014′);

CREATE TABLE STUDENT_GRADE
(
Student_ID MEDIUMINT NOT NULL AUTO_INCREMENT,
Class_ID VARCHAR(10),
Student_Grade ENUM(‘A’,’B’,’C’,’D’,’F’,’I’,’W’),
PRIMARY KEY(Student_ID)
);

Commit;

SHOW TABLES;