ZuoTi.Pro
Question

A student wants to enroll in Math 170 Discrete Mathematics. If the student has already taken...

A student wants to enroll in Math 170 Discrete Mathematics. If the student has already taken the course and passed (grade of 2.0 or better), or taken and failed the course 2 times, they cannot enroll again. Use your query to determine if student 3729 can enroll in the course. Can student 5430 enroll in the course? Include the sql statement you used in your answer here USING CASE FUNCTIONS SUCH THAT A=4, B=3, C=2, D=1

create table student(
studentid int primary key,
lastname char(25),
firstname char(25),
gpa double,
major char(25),
totalcredits int);

create table course(
courseid int primary key,
name char(25),
section char(4),
department char(25),
instructor char(25));

create table enrollment(
studentid int,
courseid int,
semester char(25),
grade char(5),
units int);
  

INSERT INTO STUDENT VALUES (5430,'Last0','First0',2.17,'MATH',29);
INSERT INTO STUDENT VALUES (1287,'Last1','First1',3.1,'MATH',29);
INSERT INTO STUDENT VALUES (1970,'Last2','First2',3.62,'MATH',29);
INSERT INTO STUDENT VALUES (2928,'Last3','First3',2.21,'MATH',29);
INSERT INTO STUDENT VALUES (5209,'Last4','First4',2.14,'MATH',29);
INSERT INTO STUDENT VALUES (104,'Last5','First5',2.59,'MATH',29);
INSERT INTO STUDENT VALUES (7460,'Last6','First6',3.66,'MATH',29);
INSERT INTO STUDENT VALUES (6848,'Last7','First7',3.03,'MATH',29);
INSERT INTO STUDENT VALUES (4456,'Last8','First8',2.66,'MATH',29);
INSERT INTO STUDENT VALUES (3729,'Last9','First9',2.28,'MATH',29);



INSERT INTO COURSE VALUES (40123,'Calculus 1','1','MATH','Smith');
INSERT INTO COURSE VALUES (40125,'Calculus 1','2','MATH','Lupin');

INSERT INTO ENROLLMENT VALUES (1287,40501,'Fall2016','C',1);
INSERT INTO ENROLLMENT VALUES (1970,40126,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (1970,40130,'Spring2016','A',4);
INSERT INTO ENROLLMENT VALUES (1970,40132,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (1970,50510,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (1970,50520,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (1970,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (1970,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (1970,40500,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (1970,40501,'Fall2016','A',1);
INSERT INTO ENROLLMENT VALUES (2928,40123,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (2928,40130,'Spring2016','D',4);
INSERT INTO ENROLLMENT VALUES (2928,40132,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (2928,50510,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (2928,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (2928,50530,'Spring2017','D',3);
INSERT INTO ENROLLMENT VALUES (2928,60200,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (2928,40500,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (2928,40501,'Fall2016','B',1);
INSERT INTO ENROLLMENT VALUES (5209,40125,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (5209,40130,'Spring2016','D',4);

INSERT INTO ENROLLMENT VALUES (6848,40501,'Fall2016','A',1);
INSERT INTO ENROLLMENT VALUES (4456,40123,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (4456,40130,'Spring2016','C',4);
INSERT INTO ENROLLMENT VALUES (4456,40132,'Spring2017','C',4);
INSERT INTO ENROLLMENT VALUES (4456,50510,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (4456,50520,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (4456,50530,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (4456,60200,'Spring2017','B',3);
INSERT INTO ENROLLMENT VALUES (4456,40500,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (4456,40501,'Fall2016','A',1);
INSERT INTO ENROLLMENT VALUES (3729,40123,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (3729,40130,'Spring2016','D',4);
INSERT INTO ENROLLMENT VALUES (3729,40132,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (3729,50510,'Fall2016','D',3);
INSERT INTO ENROLLMENT VALUES (3729,50520,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (3729,50530,'Spring2017','D',3);

INSERT INTO ENROLLMENT VALUES (535,40132,'Spring2017','D',4);
INSERT INTO ENROLLMENT VALUES (535,50510,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (535,50520,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (535,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (535,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (7830,40123,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (7830,40140,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (7830,40240,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (7830,40132,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (7830,50510,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (7830,50520,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (7830,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (7830,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (9179,40126,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (9179,40140,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (9179,40240,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (9179,40132,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (9179,50510,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (9179,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (9179,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (9179,60200,'Spring2017','B',3);
INSERT INTO ENROLLMENT VALUES (8265,40125,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (8265,40140,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (8265,40240,'Spring2017','C',4);
INSERT INTO ENROLLMENT VALUES (8265,40132,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (8265,50510,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (8265,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (8265,50530,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (8265,60200,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (9563,40123,'Fall2016','D',4);
INSERT INTO ENROLLMENT VALUES (9563,40140,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (9563,40240,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (9563,40132,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (9563,50510,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (9563,50520,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (9563,50530,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (9563,60200,'Spring2017','B',3);
INSERT INTO ENROLLMENT VALUES (8013,40126,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (8013,40140,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (8013,40240,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (8013,40132,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (8013,50510,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (8013,50520,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (8013,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (8013,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (565,40126,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (565,40140,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (565,40240,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (565,40132,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (565,50510,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (565,50520,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (565,50530,'Spring2017','B',3);
INSERT INTO ENROLLMENT VALUES (565,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (6240,40126,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (6240,40130,'Spring2017','A',4);

INSERT INTO ENROLLMENT VALUES (557,40125,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (557,40130,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (557,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (557,50530,'Spring2017','B',3);
INSERT INTO ENROLLMENT VALUES (557,60200,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (557,40500,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (557,40501,'Fall2016','B',1);
INSERT INTO ENROLLMENT VALUES (557,50100,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (557,50110,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (4263,40123,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (4263,40130,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (4263,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (4263,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (4263,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (4263,40500,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (4263,40501,'Fall2016','A',1);
INSERT INTO ENROLLMENT VALUES (4263,50100,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (4263,50110,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (9524,40126,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (9524,40130,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (9524,50520,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (9524,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (9524,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (9524,40500,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (9524,40501,'Fall2016','A',1);
INSERT INTO ENROLLMENT VALUES (9524,50100,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (9524,50110,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (8843,40123,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (8843,40130,'Spring2017','D',4);
INSERT INTO ENROLLMENT VALUES (8843,50520,'Fall2016','B',3);
INSERT INTO ENROLLMENT VALUES (8843,50530,'Spring2017','D',3);
INSERT INTO ENROLLMENT VALUES (8843,60200,'Spring2017','D',3);
INSERT INTO ENROLLMENT VALUES (8843,40500,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (8843,40501,'Fall2016','D',1);
INSERT INTO ENROLLMENT VALUES (8843,50100,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (8843,50110,'Spring2017','B',4);
INSERT INTO ENROLLMENT VALUES (4914,40123,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (4914,40130,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (4914,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (4914,50530,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (4914,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (4914,40500,'Fall2016','B',4);
INSERT INTO ENROLLMENT VALUES (4914,40501,'Fall2016','D',1);
INSERT INTO ENROLLMENT VALUES (4914,50100,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (4914,50110,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (7262,40125,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (7262,40130,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (7262,50520,'Fall2016','C',3);
INSERT INTO ENROLLMENT VALUES (7262,50530,'Spring2017','C',3);
INSERT INTO ENROLLMENT VALUES (7262,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (7262,40500,'Fall2016','C',4);
INSERT INTO ENROLLMENT VALUES (7262,40501,'Fall2016','A',1);
INSERT INTO ENROLLMENT VALUES (7262,50100,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (7262,50110,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (8113,40123,'Fall2016','A',4);
INSERT INTO ENROLLMENT VALUES (8113,40130,'Spring2017','A',4);
INSERT INTO ENROLLMENT VALUES (8113,50520,'Fall2016','A',3);
INSERT INTO ENROLLMENT VALUES (8113,50530,'Spring2017','B',3);
INSERT INTO ENROLLMENT VALUES (8113,60200,'Spring2017','A',3);
INSERT INTO ENROLLMENT VALUES (8113,40500,'Fall2016','A',4);

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Below is the query to find the result as expected.

SELECT s.studentid, s.lastname, s.firstname ,
CASE WHEN e.grade ='A' and e.units='4' then 'STUDENT GRAD A'
WHEN e.grade ='B' and e.units='4' then 'STUDENT GRAD B'
WHEN e.grade ='C' and e.units='4' then 'STUDENT GRAD C'
WHEN e.grade ='D' and e.units='4' then 'STUDENT GRAD D'
END AS RESULTS
FROM student s, enrollment e, course c WHERE e.studentid=s.studentid AND c.courseid=e.courseid;

Output:

2928 Last3 First3 STUDENT GRAD B
5209 Last4 First4 STUDENT GRAD C
4456 Last8 First8 STUDENT GRAD C
3729 Last9 First9 STUDENT GRAD A

Student 3729 cannot enroll as he/she is already enrolled in the course

Student  5430 can get enroll.

0 0
Add a comment
Know the answer?
Add Answer to:
A student wants to enroll in Math 170 Discrete Mathematics. If the student has already taken...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • II. Fill in the blanks: (30 The relational database schema of Course Management System are defined...

    II. Fill in the blanks: (30 The relational database schema of Course Management System are defined as follows: Department (ID, Name, Head) Teacher (ID, Name, Position, DeptID) Student (ID, Name, Gender, Birthday, DeptID) Course (ID, Name, Room, TeacherID) Enroll (StudentID, CourseID, Grade) Please fill the blanks in the following SQL statements. 1. Create the Enroll table. CREATE TABLE Enroll ( char (8) NOT NULL StudentID [1] char (4) NOT NULL CourseID [2] int Grade CHECK (Grade IS NULL OR (Grade>-0...

  • SQL Homework Create a single script to do all of the following. Be sure to create...

    SQL Homework Create a single script to do all of the following. Be sure to create in such a way that it can be re-run multiple times. Create a database named homework. In this database, create three tables: Student, Course, StudentCourse - Create the Student table with the following fields in this order: studentID as an integer; do not allow empty values firstName as a variable number of characters, max 30, with a null default value lastName as a variable...

  • Hi! I need help answering these 3 questions on SQLQuery. Please help! The database is below....

    Hi! I need help answering these 3 questions on SQLQuery. Please help! The database is below. 1. Count the number of courses taught by all adjunct and full time faculty members during the semester and their total enrollments for all classes they teach. 2. List first and last names only of all adjunct faculty members who are teaching more than 1 course , the total enrollment for those courses, and the number of courses the faculty members teach. 3. Display...

  • Here is the P4 university database ---Drop tables --Drop Table FACULTY Cascade Constraints; --Drop Table OFFERING...

    Here is the P4 university database ---Drop tables --Drop Table FACULTY Cascade Constraints; --Drop Table OFFERING Cascade Constraints; --Drop Table STUDENT Cascade Constraints; --Drop Table COURSE Cascade Constraints; --Drop Table ENROLLMENT Cascade Constraints; Create Table STUDENT( Std_ID NUMBER(3) Constraint Student_STD_ID_FK PRIMARY KEY, Std_FN Varchar2 (9), Std_LN Varchar2 (8) , Std_City Varchar2 (10), Std_State Varchar2 (4), Std_Zip Number (5), Std_Major Varchar2 (6), Std_Class Varchar2 (3), Std_GPA NUMBER (3,2)); ---Insert records into Student Insert Into STUDENT Values( 101, 'Joe', 'Smith','Eau Clare','WI',18121,'IS','FR',3.8); Insert...

  • Name Use complete sentences if possible The following StudentGrade relation (table) stores the Student, Major, and Course information Each student has unique StudentID. Assume that a student has...

    Name Use complete sentences if possible The following StudentGrade relation (table) stores the Student, Major, and Course information Each student has unique StudentID. Assume that a student has only one major. One student can take multiple courses in one SemesterYear. One course can be taken by many students in each SemesterYear. Given values for StudentID, Course Number, and SemesterYear, one can determine all the rest of values for a record. However only Grade is functionally dependent on StudentlD, CourseNumber and...

  • A. Create a CollegeCourse class. The class contains fields for the course ID (for example, CIS 21...

    A. Create a CollegeCourse class. The class contains fields for the course ID (for example, CIS 210), credit hours (for example, 3), and a letter grade (for example, A). Include get and set methods for each field. Create a Student class containing an ID number and an array of five CollegeCourse objects. Create a get and set method for the Student ID number. Also create a get method that returns one of the Student’s CollegeCourses; the method takes an integer...

  • A. Create a CollegeCourse class. The class contains fields for the course ID (for example, CIS...

    A. Create a CollegeCourse class. The class contains fields for the course ID (for example, CIS 210), credit hours (for example, 3), and a letter grade (for example, A). Include get and set methods for each field. Create a Student class containing an ID number and an array of five CollegeCourse objects. Create a get and set method for the Student ID number. Also create a get method that returns one of the Student’s CollegeCourses; the method takes an integer...

  • A. Create a CollegeCourse class. The class contains fields for the course ID (for example, CIS...

    A. Create a CollegeCourse class. The class contains fields for the course ID (for example, CIS 210), credit hours (for example, 3), and a letter grade (for example, A). Include get and set methods for each field. Create a Student class containing an ID number and an array of five CollegeCourse objects. Create a get and set method for the Student ID number. Also create a get method that returns one of the Student’s CollegeCourses; the method takes an integer...

  • Suppose a student is taking IS 620 section 1 and HCC 629 section 1 for spring...

    Suppose a student is taking IS 620 section 1 and HCC 629 section 1 for spring 2019. Use an explicit cursor to print out the titles and prices of textbooks for these two courses. Sample code to create the tables: drop table textbook_schedule cascade constraints; drop table textbook_author cascade constraints; drop table schedule cascade constraints; drop table course cascade constraints; drop table textbook cascade constraints; drop table author cascade constraints; drop table teacher cascade constraints; drop table program cascade constraints;...

  • Webdaxlpid-12771371-dt-content-rid-89561 4 Delete a student S. Register a counse 7. Check student...

    have to make an SQL Java Database. webdaxlpid-12771371-dt-content-rid-89561 4 Delete a student S. Register a counse 7. Check student egstration 8 Uplond grades 9.Check gale prop table Registered; op table student; reate table Student address varchar (100), mafor char (10) eate table course code char (10) primary key title varchar C reate table Registered code char(a0). senester char (10), preign Key ssn) reférences student (ssa) foreign key (code) references prinary key (ssn,code.year,senester) tnsert into Student values (555550001, "Ton Harks" 100...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT