Write a query that returns the student name (Name), student major (Major), and student gpa (GPA) of all students enrolled in the Compiler...
This question has been answered
Question

2.all students enrolled in3.course. databse included below: CREATETABLEvarchar25NOTNULLKEYdecimal30DEFAULT0INSERT

Write a query that returns the student name (Name), student major (Major), and student gpa (GPA) of

the Compiler Construction course.

Implement a view called student_course_view that will generalize your solution to problem #2 for any

  dept (
deptName ()   PRIMARY ,
numPhDs (,)  
);

 INTO dept VALUES
    ('Chemical Engineering',32),
    ('Civil Engineering',88),
    ('Computer Science',47),
    ('Industrial Engineering',41),
    ('Mathematics',129),
    ('Sanitary Engineering',3);

CREATE TABLE course (
cno decimal(9,0) NOT NULL,
courseName varchar(50) DEFAULT NULL,
deptName varchar(25) NOT NULL,
PRIMARY KEY (cno, deptName),
FOREIGN KEY (deptName) REFERENCES dept(deptName)
);

INSERT INTO course VALUES
   (302,'Intro to Programming','Computer Science'),
   (310,'Thermodynamics','Chemical Engineering'),
   (365,'City Planning','Civil Engineering'),
   (366,'Advanced City Planning','Civil Engineering'),
   (375,'Highway Engineering','Civil Engineering'),
   (461,'College Geometry 1','Mathematics'),
   (462,'College Geometry 2','Mathematics'),
   (467,'Intro to Data Structures','Computer Science'),
   (514,'Manpower Utilization','Industrial Engineering'),
   (560,'Intro to Garbage','Sanitary Engineering'),
   (561,'Advanced Garbage Collection','Sanitary Engineering'),
   (701,'Compiler Construction','Computer Science'),
   (726,'Nonlinear Programming','Computer Science');

CREATE TABLE prof (
profName varchar(30) NOT NULL PRIMARY KEY,
deptName varchar(25) DEFAULT NULL,
FOREIGN KEY (deptName) REFERENCES dept(deptName)
);

INSERT INTO prof VALUES
   ('Edison, L.','Chemical Engineering'),
   ('Brown, S.','Civil Engineering'),
   ('Clark, E.','Civil Engineering'),
   ('Randolph, B.','Civil Engineering'),
   ('Brian, C.','Computer Science'),
   ('Jones, J.','Computer Science'),
   ('Smith, S.','Industrial Engineering'),
   ('Walter, A.','Industrial Engineering'),
   ('Robinson, T.','Mathematics'),
   ('Bucket, T.','Sanitary Engineering');

CREATE TABLE section (
deptName varchar(25) NOT NULL,
cno decimal(9,0) NOT NULL,
sectno decimal(3,0) NOT NULL,
profName varchar(30) DEFAULT NULL,
FOREIGN KEY (deptName) REFERENCES dept(deptName),
FOREIGN KEY (cno) REFERENCES course(cno),
FOREIGN KEY (profName) REFERENCES prof(profName),
PRIMARY KEY(deptName, cno, sectno));

INSERT INTO section VALUES
   ('Chemical Engineering',310,1,'Edison, L.'),
   ('Civil Engineering',365,1,'Randolph, B.'),
   ('Civil Engineering',375,1,'Brown, S.'),
   ('Computer Science',302,1,'Jones, J.'),
   ('Computer Science',302,2,'Smith, S.'),
   ('Computer Science',467,1,'Jones, J.'),
   ('Computer Science',701,1,'Clark, E.'),
   ('Computer Science',726,1,'Brian, C.'),
   ('Industrial Engineering',514,1,'Walter, A.'),
   ('Mathematics',461,1,'Robinson, T.'),
   ('Mathematics',462,1,'Robinson, T.'),
   ('Sanitary Engineering',561,1,'Bucket, T.');

CREATE TABLE student (
sid decimal(9,0) NOT NULL PRIMARY KEY,
studentName varchar(30) DEFAULT NULL,
gpa decimal(3,2) DEFAULT NULL);

INSERT INTO student VALUES
   (1,'Jacobs, T.',3.60),
   (2,'Pierson, E.',3.50),
   (3,'Zeene, Ben N.',3.90),
   (4,'Sulfate, Barry M.',2.80),
   (5,'Form, Clara O.',3.30),
   (6,'Scott, Kim J.',3.80),
   (7,'Sather, Roberto B.',2.20),
   (8,'Stanley, Leotha T.',3.60),
   (9,'Smith, Joyce A.',2.00),
   (10,'Jones, David S.',3.50),
   (11,'Paul, Mary W.',3.60),
   (12,'Soong, V.',3.50),
   (13,'Kellerman, S.',2.90),
   (14,'Cheong, R.',3.00),
   (15,'Borchart, Sandra L.',3.90),
   (16,'Alsberg, David J.',3.50),
   (17,'Thorton, James Q.',2.70),
   (18,'Gooch',1.40),
   (19,'Smith, L.',0.70),
   (20,'Korpel, E.',3.50),
   (21,'Surk, K.',2.50),
   (22,'Emile, R.',2.00),
   (23,'Bomber, C.',3.20),
   (24,'Carter, Jimmy',3.50),
   (25,'Kissinger, Henry',3.40),
   (26,'Ford, Gerald',3.50),
   (27,'Anderson, P.',3.20),
   (28,'Austin, G.',3.50),
   (29,'Hamilton, S.',2.80),
   (30,'Baker, C.',3.50),
   (31,'Andrews, R.',2.80),
   (32,'Liu, Huihusan',3.90),
   (33,'Chao, Tsechih',3.60),
   (34,'Kasten, Norman L.',2.50),
   (35,'Mathews, John W.',3.60),
   (36,'Burroughs, Susan S.',3.00),
   (37,'Dunbar, D.',3.40),
   (38,'Auen, B.',2.70),
   (39,'Shoemaker, A.',3.50),
   (40,'Rosemeyer, S.',2.90),
   (41,'Fisher, C.',3.50),
   (42,'Trude, T.',2.90),
   (43,'Ksar, J.',3.40),
   (44,'Moeri, S.',3.20),
   (45,'June, Granson',3.10),
   (46,'Micheal, Zadicki T.',2.70),
   (47,'Roger, Blotter N.',1.90),
   (48,'Natividad, A.',4.00),
   (49,'Villa-lobos, M.',3.70),
   (50,'Moomchi, B.',3.50),
   (52,'Fy, Clara I.',2.00),
   (53,'Atny, Mary H.',3.80),
   (54,'Maximillian',3.00),
   (55,'Glitch, R.',2.80),
   (56,'Starry, J.',3.30),
   (57,'Hiemerschmitz, A.',2.70),
   (58,'Marshmallton',3.00),
   (59,'Ziebart, F.',1.80),
   (60,'Calcmity, J.',2.60),
   (61,'Kennedy, Ed',2.30),
   (62,'Moses, A.',3.20),
   (63,'Kool, Joseph F.',3.70),
   (64,'Fred, Edwin B.',4.00),
   (65,'Altenhaus, Stuart',3.75),
   (67,'Altenhaus, Gloria',4.00);

CREATE TABLE major (
deptName varchar(25) NOT NULL,
sid decimal(9,0) NOT NULL,
PRIMARY KEY (deptName, sid),
FOREIGN KEY (deptName) REFERENCES dept (deptName),
FOREIGN KEY (sid) REFERENCES student (sid)
) ;

INSERT INTO major VALUES
   ('Chemical Engineering',25),
   ('Chemical Engineering',26),
   ('Chemical Engineering',27),
   ('Chemical Engineering',28),
   ('Chemical Engineering',29),
   ('Chemical Engineering',30),
   ('Chemical Engineering',31),
   ('Chemical Engineering',32),
   ('Chemical Engineering',33),
   ('Chemical Engineering',34),
   ('Chemical Engineering',35),
   ('Civil Engineering',36),
   ('Civil Engineering',37),
   ('Civil Engineering',38),
   ('Civil Engineering',39),
   ('Civil Engineering',40),
   ('Civil Engineering',41),
   ('Civil Engineering',42),
   ('Civil Engineering',43),
   ('Civil Engineering',44),
   ('Civil Engineering',45),
   ('Civil Engineering',46),
   ('Civil Engineering',47),
   ('Civil Engineering',48),
   ('Civil Engineering',49),
   ('Civil Engineering',50),
   ('Civil Engineering',52),
   ('Civil Engineering',53),
   ('Civil Engineering',54),
   ('Civil Engineering',55),
   ('Civil Engineering',56),
   ('Civil Engineering',57),
   ('Civil Engineering',58),
   ('Civil Engineering',59),
   ('Civil Engineering',60),
   ('Civil Engineering',61),
   ('Civil Engineering',62),
   ('Civil Engineering',63),
   ('Industrial Engineering',64),
   ('Industrial Engineering',65),
   ('Industrial Engineering',67),
   ('Mathematics',1),
   ('Mathematics',2),
   ('Mathematics',3),
   ('Sanitary Engineering',4),
   ('Chemical Engineering',5),
   ('Chemical Engineering',6),
   ('Computer Science',7),
   ('Computer Science',8),
   ('Computer Science',9),
   ('Computer Science',10),
   ('Computer Science',11),
   ('Computer Science',12),
   ('Computer Science',13),
   ('Computer Science',14),
   ('Computer Science',15),
   ('Computer Science',16),
   ('Computer Science',17),
   ('Computer Science',18),
   ('Computer Science',19),
   ('Computer Science',20),
   ('Computer Science',21),
   ('Computer Science',22),
   ('Computer Science',23),
   ('Computer Science',24);

CREATE TABLE enroll (
sid decimal(9,0) NOT NULL,
grade decimal(3,2) DEFAULT NULL,
deptName varchar(25) NOT NULL,
cno decimal(9,0) NOT NULL,
sectno decimal(3,0) NOT NULL,
PRIMARY KEY (sid, deptName, cno, sectno),
FOREIGN KEY (sid) REFERENCES student(sid),
FOREIGN KEY (deptName, cno, sectno) REFERENCES section(deptName, cno, sectno)
) ;

INSERT INTO enroll VALUES
   (1,3.00,'Chemical Engineering',310,1),
   (2,3.00,'Computer Science',302,1),
   (3,3.50,'Civil Engineering',375,1),
   (4,4.00,'Mathematics',461,1),
   (5,3.00,'Industrial Engineering',514,1),
   (6,3.50,'Computer Science',302,2),
   (7,4.00,'Computer Science',302,1),
   (8,4.00,'Computer Science',302,1),
   (9,3.00,'Civil Engineering',375,1),
   (10,2.00,'Computer Science',302,1),
   (11,3.00,'Computer Science',302,2),
   (12,2.50,'Computer Science',302,2),
   (13,2.50,'Computer Science',302,2),
   (14,2.50,'Mathematics',462,1),
   (15,3.00,'Chemical Engineering',310,1),


dont copy the wrong answer from the site 1

Answered by Expert Tutors

, consectetur adipiscing elit. Nam lacinia pulvinar tortor n

a. Fusce dui

et, consectetur adipiscing elit. Nam

ipiscing elit.


usce dui lectus, congue vel laoree

f

amet, consectetur adipiscing elit. Nam lacinia pulvinar

a. Fusce dui

et, consectetur adipiscing elit. Nam

Step-by-step explanation

a. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar t


, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a mole

The student who asked this found it Helpful
Overall rating 100%
Get unstuck

490,808 students got unstuck by Course
Hero in the last week

step by step solutions

Our Expert Tutors provide step by step solutions to help you excel in your courses