Having 3 tables with the following sturucture (see image). Write MYSQL code for the following: (1) Which courses (course name, course credits) have been taken by the student with student ID 861103-2438? (2) For students who have registered courses, how many credits has each student taken? In the query result, list student ID, student first name, student last name, and his/her credits. (3) First define a view that gives the student ID, student last name, student first name, and the grade average for each student who has registered courses. Then use the view to find which students have the highest grade average. 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);

Answered: aving 3 tables with the following…

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

See similar textbooks

Related questions

Concept explainers

Question

Having 3 tables with the following sturucture (see image). Write MYSQL code for the following:

(1) Which courses (course name, course credits) have been taken by the student with student ID
861103-2438?
(2) For students who have registered courses, how many credits has each student taken? In the
query result, list student ID, student first name, student last name, and his/her credits.
(3) First define a view that gives the student ID, student last name, student first name, and the
grade average for each student who has registered courses. Then use the view to find which
students have the highest grade average.

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

Q 1.

Question:-

Which courses (course name, course credits) have been taken by the student with student ID 861103-2438?

SQL Query:-

SELECT Courses.CourseName, Courses.Credits
FROM Registration
INNER JOIN Courses ON Registration.CourseCode = Courses.CourseCode
WHERE Registration.StudentId = '861103-2438';

Explanation:-

This query joins the Registration and Courses tables on the CourseCode column and selects the course name and course credits from the Courses table where the StudentId in the Registration table matches '861103-2438'.

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

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