ER diagram to table mapping in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the SQL code to create a table for an entity named 'Student' with an ID as primary key.

SQL

CREATE TABLE Student (StudentID [1] PRIMARY KEY, Name VARCHAR(100));

Drag options to blanks, or click blank then click option'

AINT

BTEXT

CFLOAT

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT or DATE for primary key which is not suitable for IDs.

The primary key should be an integer type for IDs in this case.

2fill in blank

medium

Complete the SQL code to create a table for a weak entity 'Dependent' with a foreign key referencing 'Employee'.

SQL

CREATE TABLE Dependent (DependentID INT, EmployeeID [1], PRIMARY KEY (DependentID, EmployeeID), FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID));

Drag options to blanks, or click blank then click option'

ADATE

BINT

CTEXT

DFLOAT

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different data type than the referenced primary key for foreign key.

The foreign key EmployeeID should have the same data type as the referenced primary key, which is INT.

3fill in blank

hard

Fix the error in the SQL code to correctly create a table for a many-to-many relationship 'Enrollment' between 'Student' and 'Course'.

SQL

CREATE TABLE Enrollment (StudentID INT, CourseID [1], PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID));

Drag options to blanks, or click blank then click option'

AFLOAT

BTEXT

CINT

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using mismatched data types for foreign keys.

Both foreign keys should have the same data type as their referenced primary keys, which are INT.

4fill in blank

hard

Fill both blanks to create a table for an entity 'Book' with a composite primary key and a foreign key referencing 'Publisher'.

SQL

CREATE TABLE Book (ISBN [1], Edition [2], PRIMARY KEY (ISBN, Edition), PublisherID INT, FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID));

Drag options to blanks, or click blank then click option'

AVARCHAR(13)

BINT

CDATE

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT for ISBN or TEXT for Edition.

ISBN is a string of 13 characters, so VARCHAR(13) is used. Edition is a number, so INT is appropriate.

5fill in blank

hard

Fill all three blanks to create a table for an entity 'Employee' with an auto-increment primary key, a name, and a hire date.

SQL

CREATE TABLE Employee (EmployeeID [1] [2] PRIMARY KEY, Name [3] NOT NULL, HireDate DATE);

Drag options to blanks, or click blank then click option'

AINT

BVARCHAR(100)

CAUTO_INCREMENT

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT for Name or missing AUTO_INCREMENT for EmployeeID.

EmployeeID is an INT with AUTO_INCREMENT for automatic numbering. Name is a VARCHAR(100) to store text.