Write the DDL statements for the tables above. They will look like this: CREATE
ID: 3537661 • Letter: W
Question
Write the DDL statements for the tables above. They will look like this:
CREATE TABLE student (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
lName VARCHAR(20) NOT NULL,
fName VARCHAR(20) NOT NULL,
etc...
);
ALTER TABLE student
ADD PRIMARY KEY (id);
Note that you can use an AUTO_INCREMENT column to create the values for a surrogate key, or you can enter your own values. It%u2019s very common to use AUTO_INCREMENT for assigning surrogate keys, although there are other ways to create them.
Run your DDL statements in MySQL to build the tables.
Remember, after creating your tables, you can use the statement SHOW COLUMNS IN student; to see the structure of your table.
After you build a table, you can use the statement SHOW CREATE TABLE student; to show the CREATE statement that will build the same table again.
For this step, write the DLL statements on your homework. (You can cut-and-paste from MySQL if you wish. You can use the output from SHOW CREATE TABLE if you would like.)
CREATE TABLE Student (
id int unsigned NOT NULL AUTO_INCREMENT,
lName varchar(20) NOT NULL,
fName varchar(20) NOT NULL,
yearStart int NOT NULL,
Major varchar(20) NOT NULL
);
ALTER TABLE Student
ADD PRIMARY KEY (id);
CREATE TABLE Course (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
CreditHours int NOT NULL,
);
ALTER TABLE Student
ADD PRIMARY KEY (id);
CREATE TABLE Transcript (
StudentID VARCHAR(20) NOT NULL,
CourseId VARCHAR(20) NOT NULL,
date int NOT NULL,
grade VARCHAR(20) NOT NULL
);
ALTER TABLE Student
ADD PRIMARY KEY (id);
Add some data to your tables, including the association table:
INSERT INTO student (lName, fName, %u2026 )
VALUES (%u2018Smith%u2019, %u2018John%u2019, %u2026 )
Show a representative DML statement for each table.
Write a SQL statement to print the students' names, the courses they took, and the date they
completed them. Use all the tables. Use the explicit style of join syntax.
Run the statement to get some output. Cut-and-paste the query and the output here.
THE one is green is what i did and im beyond stuck. im typing that in mysql and its not working at all
Explanation / Answer
Select Student.Name, Course.Name, Transcript.date
from Student Join Transcript ON Student.ID=Transcript.ID JOIN Course ON Transcript.CourseID=Course.CourseID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.