1 answer

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

Question:

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);


Answers

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.

.

Similar Solved Questions

1 answer
The following selected transactions relate to investment activities of Ornamental Insulation Corporation during 2018. The company...
The following selected transactions relate to investment activities of Ornamental Insulation Corporation during 2018. The company buys debt securities, not intending to profit from short-term differences in price and not necessarily to hold debt securities to maturity, but to have them available for...
1 answer
B. Suppose a file consists of as and bs only and the search is for the...
b. Suppose a file consists of as and bs only and the search is for the string "abba". Construct an FSM for doing this search....
1 answer
Figure shows circuit consisting of an ideal battery with emf τ·604 A. a resistance R, and...
Figure shows circuit consisting of an ideal battery with emf τ·604 A. a resistance R, and a small wire loop of area 7.4 cm2 For the time interval-24 s to f . 48 s, an external magnetic field is set up throughout the loop. The field is uniform, its direction is into the page in Figure (a),...
1 answer
Why is income-in-kind often excluded from income in implementing an income tax?
Why is income-in-kind often excluded from income in implementing an income tax?...
1 answer
4) If a mutation causes insertion of three nucleotide pairs, the following statement is true: A)Group...
4) If a mutation causes insertion of three nucleotide pairs, the following statement is true: A)Group of answer choices B) The mRNA transcript will be longer C) No transcription will occur D) No translation will occur E) The cell won’t be able to replicate its DNA...
1 answer
Explain why incumbent (established) companies are more likely to pursue incremental innovation, while new companies are...
Explain why incumbent (established) companies are more likely to pursue incremental innovation, while new companies are more likely to focus on radical innovation....
1 answer
This one is much easier than it appears. For this type of pressure vessel, the only...
This one is much easier than it appears. For this type of pressure vessel, the only pressure is exerted by the fluid on the walls. There is no sphereical end-cap. That means that if we neglect the weight of the pressure vessel (which is almost always the case) the axial stress is approximately zero....
1 answer
At the end of the second quarter of 20X1, Malta Corporation assembled the following information: The...
At the end of the second quarter of 20X1, Malta Corporation assembled the following information: The first quarter resulted in a $106,000 loss before taxes. During the second quarter, sales were $1,216,000; purchases were $666,000; and operating expenses were $336,000. Cost of goods sold is determi...
1 answer
Also... If I have a launch height of 3m and a distance of 79.45.. angle is...
Also... If I have a launch height of 3m and a distance of 79.45.. angle is 30 degrees, and velocity is 30 m/s... What would be the range of that projectile? Answers to both would help tremendously. Predict and Test Vertical Height Calculate the initial speed needed to shoot a target directly above t...
1 answer
Please do this in C++. Thank you in advance. Note that you have to carefully create...
Please do this in C++. Thank you in advance. Note that you have to carefully create your class using the names and capitalization shown below. Put comments into your program, make sure you indent your program properly, and use good naming conventions. Create a class called entry. It should have two ...
1 answer
A car accelerates at a constant rate from zero to 26.1 m/s in 10 seconds and...
A car accelerates at a constant rate from zero to 26.1 m/s in 10 seconds and then slows to 10.2 m/s in 5 seconds. What is its average acceleration to the nearest tenth of a m/s2 during the 15 seconds? What was the acceleration during the first 10 seconds?...
1 answer
You have just been hired by FAB Corporation, the manufacturer of a revolutionary new garage door...
You have just been hired by FAB Corporation, the manufacturer of a revolutionary new garage door opening device. The president has asked that you review the company's costing system and do what you can to help us get better control of our manufacturing overhead costs." You find that the comp...
1 answer
Exercise 7: Selective and Differential Growth Medium and Tests 6. What is the difference between opaque,...
Exercise 7: Selective and Differential Growth Medium and Tests 6. What is the difference between opaque, translucent and transparent? opaque - colonie are not tamporent on trosoluent, they are impermiable to light Trarolucent colonies are almost clear but with a distanted mision Troroparent colonio ...
1 answer
3. An economy has the following money demand function: (M/P 2Y a. Derive an expression for...
3. An economy has the following money demand function: (M/P 2Y a. Derive an expression for the velocity of money. What does velocity depend on? Explain why this dependency may occur. b. Calculate velocity if the nominal interest rate i is 4 e. If output Y is 1,000 units and the money supply M d. Sup...
1 answer
6 Points Let F be the vector field represented in the figure: P(-1,1) 1907/1X X Q3.1...
6 Points Let F be the vector field represented in the figure: P(-1,1) 1907/1X X Q3.1 3 Points O2d-Curl F(0,0) > 0 O2d-Curl F(0,0) = 0 O2d-Curl F(0,0) < 0 Q3.2 3 Points OV.F(0,0) > 0 OV.F(0,0) = 0 OV.F(0,0) < 0...
1 answer
Does the Okun's law describe the short run? The long run? Or both?
Does the Okun's law describe the short run? The long run? Or both?...
1 answer
Break-even analysis for a service company Sprint Corporation (S) is one of the largest digital wireless...
Break-even analysis for a service company Sprint Corporation (S) is one of the largest digital wireless service providers in the United States. In a recent year, it had approximately 60 million direct subscribers (accounts) that generated revenue of $33,347 million. Costs and expenses for the year w...
2 answers
How many bismuth nuclei will decay per second?
1 of 2The half-life of (214/83)Bi is 19.7 min. Abismuth-214 sample contains 2.8×109 nuclei.What is the decay constant for the decay?Answer in units of s-12 of 2How many bismuth nuclei will decay per second?Answer in units of Ci...