Having the following table structure (see image). Write MYSQL for: (4) Which students (student first name, student last name) have taken the course “Database  Systems”?   (5) List all students (student first name, student last name) who have taken both “Database  Systems” and “C++”.   (6) List all students (student ID, student first Name, student last Name) and the courses (course  name, course grade) that they have taken. If there are students who have not taken any  course yet, the query result should reflect that (i.e. null for courseName, null for grade).   (7) Find all students who have taken computer science courses (i.e. course codes CS***). For the  result, list student first name, student last name, and the computer science course names  that they have taken.

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

Having the following table structure (see image). Write MYSQL for:

(4) Which students (student first name, student last name) have taken the course “Database 
Systems”?  
(5) List all students (student first name, student last name) who have taken both “Database 
Systems” and “C++”.  
(6) List all students (student ID, student first Name, student last Name) and the courses (course 
name, course grade) that they have taken. If there are students who have not taken any 
course yet, the query result should reflect that (i.e. null for courseName, null for grade).  
(7) Find all students who have taken computer science courses (i.e. course codes CS***). For the 
result, list student first name, student last name, and the computer science course names 
that they have taken.  

CREATE TABLE STUDENTS (
StudentId CHAR(11),
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Gender CHAR(1) NOT NULL,
DateOfBirth DATE NOT NULL,
PRIMARY KEY (StudentId)
);
CREATE TABLE COURSES (
CourseCode VARCHAR(6),
CourseName VARCHAR(70) NOT NULL,
Level CHAR(2),
Credits INT NOT NULL,
PRIMARY KEY (CourseCode)
);
CREATE TABLE Registration (
StudentId CHAR(11),
CourseCode VARCHAR(6),
Grade DECIMAL (2, 1) NOT NULL,
PRIMARY KEY (StudentId, CourseCode),
FOREIGN KEY (StudentId) REFERENCES STUDENTS (StudentId) ON UPDATE CASCADE,
FOREIGN KEY (CourseCode) REFERENCES COURSES (CourseCode) ON UPDATE CASCADE
);
Transcribed Image Text:CREATE TABLE STUDENTS ( StudentId CHAR(11), FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Gender CHAR(1) NOT NULL, DateOfBirth DATE NOT NULL, PRIMARY KEY (StudentId) ); CREATE TABLE COURSES ( CourseCode VARCHAR(6), CourseName VARCHAR(70) NOT NULL, Level CHAR(2), Credits INT NOT NULL, PRIMARY KEY (CourseCode) ); CREATE TABLE Registration ( StudentId CHAR(11), CourseCode VARCHAR(6), Grade DECIMAL (2, 1) NOT NULL, PRIMARY KEY (StudentId, CourseCode), FOREIGN KEY (StudentId) REFERENCES STUDENTS (StudentId) ON UPDATE CASCADE, FOREIGN KEY (CourseCode) REFERENCES COURSES (CourseCode) ON UPDATE CASCADE );
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Database Environment
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education