Use the SQL Script in the link to create a Sample Sandbox Database. There are th

ID: 3908396 • Letter: U

Question

Use the SQL Script in the link to create a Sample Sandbox Database. There are three tables in this database. There is a student tables containing a list of students. There is a course table containing a list of course offerings. There is a registration table containing a list of student ids and course ids representing the courses that students have registered for. The Database will permit you to interactively play around with queries that will respond to the following information request.

Find the full names of the students who have taken a CIS course or a History course

Find the full names of the students who have taken both a CIS course and a History course

Find the full names of the students who have taken a CIS course but have not taken a History course

the database is below

Overview: This file will create the tables and load the data

-- that will be used in the class exercises.

/* NOTE: It is good practice to use the "drop table" statement

before the "create table" statement in case the table

already exists.

CREATE if not exists DATABASE RSYSTEM;

USE RSYSTEM;

DROP TABLE if exists registration;

DROP TABLE if exists course;

DROP TABLE if exists student;

CREATE TABLE student(

sid varchar(10),

fName VARCHAR(30) NOT NULL,

lName VARCHAR(30) NOT NULL,

gpa double,

dob date,

CONSTRAINT sailor_pk PRIMARY KEY(sid)

)ENGINE = INNODB;

-- NOT NULL auto_increment

CREATE TABLE course(

crn INT ,

cname VARCHAR(30),

cDiscription VARCHAR(10),

dept varchar(5),

cNumber int,

cSection int,

credits int,

cyear char(4),

CONSTRAINT course_pk PRIMARY KEY(crn)

)ENGINE = INNODB;

CREATE TABLE registration(

registration_num INT NOT NULL auto_increment,

sid varchar(10),

crn int,

csection int,

rdate TIMESTAMP,

CONSTRAINT registration_pk PRIMARY KEY(registration_num),

CONSTRAINT registration_fk1 FOREIGN KEY (sid) REFERENCES

student(sid),

CONSTRAINT registration_fk2 FOREIGN KEY (crn) REFERENCES course(crn)

) ENGINE = INNODB;

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J0901',

'John','Burke', 4.0, '1988-09-12');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J1902',

'Terrence', 'Williams', 1.69, '1987-12-10');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J2903',

'Jennifer', 'Thompson', 3.8, '1998-06-30');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J3904',

'Jasmine', 'Sloane', 3.8, '1979-03-9');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J4905',

'Joseph', 'Nilan', 2.0, '1982-01-14');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J59906',

'Gregory','Moody', 2.7, '1990-02-17');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J6907',

'Harper', 'Dennis', 3.0, '1991-10-10');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J7908',

'Camella','Poindexter', 3.9, '1994-10-10');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J8909',

'Harper','Dennis', 3.2, '1988-09-19');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J9910',

'Porsha', 'Maner', 3.9, '1988-05-11');

-- The following SQL statements populate the boat table