This assessment consists of two parts: ode block and a quiz.
Part 1: Code Blocks
To complete this part of the assessment, you can use MySQL database management system available on the Coursera platform.
Instructions
The questions in this assessment relate to a sports club that needs to build a digital database to maintain data about the players joining the club.
Run each complete SQL statement you write in this part to develop the database for the sports club.
Important
Remember to end each complete SQL statement with a semicolon.
Make sure you leave a space between the SQL terms and the operators.
Use capital letters for the SQL commands and keywords.
For example, a correctly formatted SQL statement must be written as follows:
Here is an example of an incorrectly formatted SQL statement in which there is no semicolon, and no spaces are placed before or after the operator:
Write an SQL statement to create a database called "SportsClub".
CREATE DATABASE SportsClub;
In the text field below, input the missing keyword (___) from the following SQL statement to create a table called "Players".
CREATE ____ Players (playerID INT, playerName VARCHAR(50), age INT, PRIMARY KEY(playerID));
Run the complete SQL statement in MySQL to create the table in the club database.
CREATE TABLE Players (playerID INT, playerName VARCHAR(50), age INT, PRIMARY KEY(playerID));
In the text field below, input the missing keyword (___) from the following SQL statement to insert data into the "Players" table.
INSERT INTO Players (playerID, playerName, age) ____ (1, "Jack", 25);
Run the complete SQL statement in MySQL to insert the record of data in the players table.
INSERT INTO Players (playerID, playerName, age) VALUES (1, "Jack", 25);
Insert three more records into the "Players" table that contain the following data:
(2, "Karl", 20)
(3, "Mark", 21)
(4, "Andrew", 22)
Once you have executed the INSERT INTO statement to enter these three records of data, run the following SQL statement:
SELECT playerName FROM Players WHERE playerID = 2;
What is the playerName that appears on the screen?
INSERT INTO Players (playerID, playerName, age) VALUES (2, "Karl", 20);
INSERT INTO Players (playerID, playerName, age) VALUES (3, "Mark", 21);
INSERT INTO Players (playerID, playerName, age) VALUES (4, "Andrew", 22);
Write a SQL statement that outputs all players names in the "Players" table. When you run the right SQL query, you should have the following output result:
SELECT playerName FROM Players;
The following table called "Players", contains four records of data. Write a SQL statement that updates the age of the player with ID = 3. The new age value should be '22'.
UPDATE Players SET age = 22 WHERE playerID = 3;
The following table called "Players", contains four records of data. Write a SQL statement that deletes the record of the player with ID = 4.
DELETE FROM Players WHERE playerID = 4;
Write an SQL statement that evaluates if the PlayerID in the following "Players" table is odd or even.
Hint: Assume X is a number. If the remainder of X divided by 2 is 0, then X is an even number otherwise X is an odd number. Remember to use the “%” symbol to get the remainder.
| PlayerID | Name |
| --- | --- |
| 1 | Karl |
| 2 | Adam |
| 3 | Anas |
SELECT playerID,
CASE
WHEN playerID % 2 = 0 THEN 'Even'
ELSE 'Odd'
END AS PlayerID_Type
FROM Players;
Write an SQL statement that outputs all names of the players in the following "Players" table who are older than 25 years of age.
| Age | Name |
| --- | --- |
| 38 | Karl |
| 25 | Adam |
| 22 | Anas |
SELECT Name FROM Players WHERE Age > 25;
Review the following ER-Diagram. Write the missing part of the SQL statement to define a foreign key that links the course table with the department table.
CREATE TABLE Course( courseID int NOT NULL, courseName VARCHAR(50), PRIMARY KEY (courseID),
____ ____(____) ____ ____ (____)
);
Hint: write only the missing part in your answer.
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
What is a row of information about one specific staff member in a college database table referred to as?
A sports club database includes a table called "Members" with two columns:
Choose the right data type for each column. Select all correct answers.
The Player number column data type is DECIMAL.
The Full name column data type is CHAR.
The Player number column data type is INT.
The Full name column data type is VARCHAR.
In a football club the skill level of all new players must automatically be set at the default of level 1. Which SQL syntax is used to set this default level using the DEFAULT keyword?
level INT DEFAULT 1;
DEFAULT level INT 1;
Database constraints are used to limit the type of data value that can be stored in a table.
The output result of the following SQL statement is the data of all customers from Italy.
SELECT * FROM customers WHERE Country = "Italy";
The output result of the following SQL statement returns the records of all customers from India in Alphabetical order from A to Z.
SELECT * FROM students WHERE country = "India" ORDER BY FirstName DESC;
What does the following SQL statement do?
SELECT * FROM Players ORDER BY Country, PlayerName;
It orders the result by country and ignores the staff name.
It displays the results ordered by country first, then players name.
The following table of data conforms with the first normal form.
Department ID | Department Name | Head of department | Course ID | Course Name |
D1 | Computing | Dr Karl | C1 | Database |
D1 | Computing | Dr Karl | C2 | Python |
D1 | Computing | Dr Karl | C3 | Web |
D1 | Computing | Dr Karl | C4 | Java |
D2 | Math | Dr Mosa | C5 | Math |
Which of the following represents the correct diagram that links the course table with the department table?
Identify the relationship between the tables in the diagram.
