DATA TO INSERT

use msis230;

 

delete from studentdept;
delete from coursedept;
delete from deptchair;
delete from enroll;
delete from advisor;
delete from class;
delete from course;
delete from student;
delete from professor;
delete from department;

INSERT INTO PROFESSOR
(ProfID, ProfSpecialty, ProfRank,ProfLname, ProfFname, ProfInitial, ProfEmail)
VALUES
('SJ001','Information S..s@trinity.edu'),
('NS001','Data A..h@trinity.edu'),
('RA001','Microbiology','Adjunct','Adams','Robert','R','RAdams@t..s@trinity.edu'),
('SM001','Discrete M..d@trinity.edu'),
('SW001','Choreography','Professor','Winsor','Stephen','A','SWinsor@t..y@trinity.edu'),
('JW001','Child D..r@trinity.edu'),
('TH001','Statics','Professor','Hastings','Timothy','','THastings@trinity.edu'),
('MY001','Quantum M..g@trinity.edu'),
('CM001','Software E..a@trinity.edu'),
('DW001','Voice C..r@trinity.edu'),
('HB001','Technology in E..n@trinity.edu'),
('CN001','Genetics','Professor','Noble','Charles','E','CNoble@trinity.edu'),
('FL001','Statistical M..n@trinity.edu'),
('PH001','Hydraulics and H..l@trinity.edu'),
('KY001','Aerodynamics','Professor','Yang','Kim','','KYang@t..o@trinity.edu');

INSERT INTO STUDENT
(StudentID, StudentLName, StudentFName, S..e@trinity.edu'),
('AA001','Adams','Abigail','W','aadams@t..n@trinity.edu'),
('AB001','Bonner','Allison','W','abonner@t..y@trinity.edu'),
('KN001','Nelson','Kenneth','A','knelson@t..r@trinity.edu'),
('TN001','Nguyen','Trin','','tnguyen@t..t@trinity.edu'),
('JE001','Espanet','Jesse','',''),
('JE002','Espanet','Jordan','',''),
('EL001','Lawton','Eleanor','','elawton@t..g@trinity.edu'),
('RK002','Kershaw','Rowena','C','rkershaw@t..a@trinity.edu'),
('LC001','Chin','Lori','','lchin@t..z@trinity.edu'),
('MB002','Brown','Marcus','L','');

INSERT INTO DEPARTMENT
(DeptID,DeptNamE)
VALUES
('MSIS','MSIS'),
('BIO','Biology'),
('PHY','Physics'),
('MKT','Marketing'),
('ECO','Economics'),
('CHE','Chemistry'),
('DAN','Dance'),
('MUS','Music'),
('AER','Aerospace'),
('CE','Civil Engineering'),
('CS','Computer Science'),
('AM','Applied Mathematics'),
('EE','Elementary Education'),
('SE','Special Education');

INSERT INTO COURSE
VALUES
('MGT105','Intro to Information Systems','Lecture',3),
('MGT475','Strategic Marketing','Lecture',4),
('ENG210','Engineering Mecahnics I','Lab',3),
('ENG320','Electric Circuits','Lab',4),
('EDU117','Educational Equity and the Law','Lecture',3),
('EDU256','Education and Religion','Lecture',3),
('MATH300','Statistics','Lecture',4),
('MATH325','Calculus II','Lecture',4),
('SCI110','Physics I','Lab',3),
('SCI415','Thermodynamics','Lab',4);

INSERT INTO CLASS
(Classid,ClassSection,ClassDays,ClassTime,CourseID,ProfID)
VALUES
('CL001','1','MWF','8:00 - 9:00','MGT105','SJ001'),
('CL002','2','TTh','1:30 - 3:00','MGT105','NS001'),
('CL003','1','MWF','1:00 - 2:00','MGT475','SJ001'),
('CL004','1','MWF','11:00 - 12:00','ENG210','KY001'),
('CL005','2','TTh','11:30 - 1:00','ENG210','KY001'),
('CL006','3','W','6:00 - 9:00','ENG210','BB001'),
('CL007','1','TTh','8:30 - 11:00','MATH300','SM001'),
('CL008','1','MWF','10:00 - 11:00','MATH325','MY001'),
('CL009','1','M','6:00 - 9:00','SCI110','FL001'),
('CL010','1','Th','6:00 - 9:00','SCI415','MY001');

INSERT INTO ENROLL
(CLASSID, STUDENTID, ENROLL_DATE,GRADE, COURSEID)
VALUES
('CL001','PP001','2020-03-09','','MGT105'),
('CL002','PP001','2020-01-04','','MGT105'),
('CL003','PP001','2020-01-21','','MGT475'),
('CL009','AA001','2020-03-12','','SCI110'),
('CL010','AA001','2020-02-29','','SCI415'),
('CL004','WS001','2020-02-15','','ENG210'),
('CL005','AB001','2020-01-17','','ENG210'),
('CL006','AB001','2020-03-31','','ENG210'),
('CL008','AB001','2020-02-02','','MATH325'),
('CL009','JE001','2020-01-20','','SCI110'),
('CL010','JE002','2020-03-31','','SCI415'),
('CL001','JE002','2020-01-15','','MGT105'),
('CL004','LC001','2020-01-05','','ENG210'),
('CL008','JM001','2020-03-17','','MATH325'),
('CL009','MB002','2020-03-15','','SCI110'),
('CL006','MB001','2020-04-01','','ENG210'),
('CL005','RK002','2020-02-12','','ENG210'),
('CL006','RK002','2020-04-05','','ENG210'),
('CL003','KN001','2020-03-03','','MGT475'),
('CL010','CA001','2020-02-12','','SCI415');

INSERT INTO DEPTCHAIR
(DEPTID, PROFESSORID)
VALUES
('MSIS','SJ001'),
('PHY','JS001'),
('MKT','NS001'),
('CHE','MY001'),
('DAN','SW001'),
('MUS','DW001'),
('AER','TH001'),
('CS','CM001'),
('AM','SM001'),
('EE','JW001'),
('SE','JW001');

INSERT INTO ADVISOR
(STUDENTID, PROFESSORID)
VALUES
('PP001','NS001'),
('AA001','CN001'),
('WS001','TA001'),
('MB001','JW001'),
('KN001','SJ001'),
('CA001','NS001'),
('TN001','CM001'),
('SB001','TH001'),
('JE002','SW001'),
('EL001','MY001'),
('RK002','CM001'),
('RM001','HB001'),
('JM001','HB001');

INSERT INTO STUDENTDEPT
VALUES
('PP001','MSIS'),
('AA001','BIO'),
('WS001','ECO'),
('AB001','CS'),
('MB001','SE'),
('CA001','MSIS'),
('TN001','CS'),
('JE001','PHY'),
('JE002','DAN'),
('RK001','PHY'),
('RK002','CS'),
('RM001','EE'),
('LC001','MUS'),
('JM001','EE'),
('MB002','MKT');

INSERT INTO COURSEDEPT
VALUES
('MGT105','MSIS'),
('MGT475','MKT'),
('ENG210','AER'),
('ENG320','CE'),
('EDU117','EE'),
('EDU256','EE'),
('MATH300','AM'),
('MATH325','AM'),
('SCI110','PHY'),
('SCI415','PHY');

 

PHASE 2

TABLE

USE MSIS230;

 

DROP TABLE IF EXISTS ENROLL;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS COURSEDEPT;
DROP TABLE IF EXISTS ADVISOR;
DROP TABLE IF EXISTS STUDENTDEPT;
DROP TABLE IF EXISTS DEPTCHAIR;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS PROFESSOR;

CREATE TABLE IF NOT EXISTS PROFESSOR
(
PROFID VARCHAR(45) PRIMARY KEY,
PROFSPECIALTY VARCHAR(45),
PROFRANK VARCHAR(45),
PROFLNAME VARCHAR(45) NOT NULL,
PROFFNAME VARCHAR(45) NOT NULL,
PROFINITIAL CHAR(2),
PROFEMAIL VARCHAR(45)
);

CREATE TABLE IF NOT EXISTS DEPARTMENT
(
DEPTID VARCHAR(45) PRIMARY KEY,
DEPTNAME VARCHAR(45) NOT NULL
);

CREATE TABLE IF NOT EXISTS COURSE
(
COURSEID VARCHAR(10) PRIMARY KEY,
COURSETITLE VARCHAR(45) NOT NULL,
COURSEDESCRIPTION VARCHAR(100),
COURSECREDITS INT
);

CREATE TABLE IF NOT EXISTS STUDENT
(
STUDENTID VARCHAR(10) PRIMARY KEY,
STUDENTLNAME VARCHAR(45) NOT NULL,
STUDENTFNAME VARCHAR(45) NOT NULL,
STUDENTINITIAL CHAR(2),
STUDENTEMAIL VARCHAR(45)
);

##### TABLES W/ FOREIGN KEYS #####

CREATE TABLE IF NOT EXISTS DEPTCHAIR
(
PROFESSORID VARCHAR(45),
DEPTID VARCHAR(45),
CONSTRAINT PK_DEPTCHAIR PRIMARY KEY (PROFESSORID, DEPTID),
CONSTRAINT FK_DEPT FOREIGN KEY (PROFESSORID) REFERENCES PROFESSOR (PROFID),
CONSTRAINT FK_CHAIR FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)
);

CREATE TABLE IF NOT EXISTS STUDENTDEPT
(
STUDENTID VARCHAR(10),
DEPTID VARCHAR(45),
CONSTRAINT PK_STUDENTDEPT PRIMARY KEY (STUDENTID, DEPTID),
CONSTRAINT FK_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),
CONSTRAINT FK_DEPARTMENT FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)
);

CREATE TABLE IF NOT EXISTS ADVISOR
(
PROFESSORID VARCHAR(45),
STUDENTID VARCHAR(10),
CONSTRAINT PK_ADVISOR PRIMARY KEY (PROFESSORID, STUDENTID),
CONSTRAINT FK_PROF FOREIGN KEY (PROFESSORID) REFERENCES PROFESSOR (PROFID),
CONSTRAINT FK2_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID)
);

CREATE TABLE IF NOT EXISTS COURSEDEPT
(
COURSEID VARCHAR(10),
DEPTID VARCHAR(45),
CONSTRAINT PK_COURSEDEPT PRIMARY KEY (COURSEID, DEPTID),
CONSTRAINT FK2_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
CONSTRAINT FK2_DEPT FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)
);

CREATE TABLE IF NOT EXISTS CLASS
(
CLASSID VARCHAR(10),
COURSEID VARCHAR(10),
PROFID VARCHAR(45),
CLASSSECTION CHAR(2),
CLASSDAYS VARCHAR (25),
CLASSTIME VARCHAR (25),
CONSTRAINT PK_CLASS PRIMARY KEY (CLASSID, COURSEID),
CONSTRAINT FK3_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID)
);

CREATE TABLE IF NOT EXISTS ENROLL
(
STUDENTID VARCHAR(10),
COURSEID VARCHAR(10),
CLASSID VARCHAR(10),
ENROLL_DATE DATETIME NOT NULL,
GRADE CHAR(2) NOT NULL,
CONSTRAINT PK_ENROLL PRIMARY KEY (STUDENTID, COURSEID, CLASSID),
CONSTRAINT FK3_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),
CONSTRAINT FK4_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
CONSTRAINT FK_CLASS FOREIGN KEY (CLASSID) REFERENCES CLASS (CLASSID)
);

MSIS/IT230

PHASE 4

SELECT FROM SINGLE TABLES

 

In this phase you will demonstrate your knowledge of selecting data from single tables.  Each query must return the data in the order shown in the question and produce the correct results. Each query is worth 15 points

NOTE: THE RESULTS OF THE QUERY ARE DEPENDENT ON THE CORRECT DATA BEING IN THE TABLES AND THE NAMES OF THE COLUMNS BEING CONSISTENT.  IF YOU HAVE NOT DONE SO PLEASE RUN THE PHASE 2 (CREATE TABLES) AND PHASE 3 (INSERT DATA) SQL FILES THAT I HAVE PUT IN THE ASSIGNMENT SECTION IN THIS PHASE SO THAT ALL RESULTS WILL BE CONSISTENT.

1. Write a query that returns the professor id, last name, first name, rank and specialty for all rows sorted by the last name

 

2. Write a query that returns the full professor name (first name mddle initial and last name) of all professors.  You must use concatenation for this query

 

3. Write a query that returns the student id, last name, first name and email address for all students sorted by last name

 

4. Write a query that returns the student id and the full name of the student (first name, middle initial and last name) for all students sorted by last name from Z to A.  You must use concatenation for this query.

 

5. Write a query that returns the student id, last name and first name of all students missing an email address.  Sort on last name

 

6. Write a query that returns the course id, title, description and credits for all courses sorted by course id

 

7. Write a query that returns the department id and department name for all departments which have an ID that begins with the letter C and sort on Department name.

 

8. Write a query that returns the course id, title, description and credits for all courses sorted by course id and having 4 course credits

 

9. Write a query that returns all the columns in the table Enroll for rows where the student enrolled on or after March 1, 2020.

 

10. Write a query that returns all columns in the table Class that does not meet on a MWF schedule
 

Phase 4 tests your knowledge of single table selects.  The requirements are in the attached document.  NOTE:  It is important that you use the SQL that I provided as far as creating the tables and inserting the data.  This will insure that everyone has the same results for this phase of the project.

 

Phase2CreateTables.sql
Phase3InsertData.sql
MSIS230Phase4SingleTableSelect.docx