Question

MySQL

11. Final challenges

Reset challenge database

If you make a mistake while editing the database in this section or just want to reset the database back to its original state, return to this page and click the “Reset Section Database” button below.

RESET CHALLENGE DATABASE

If I have seen further, it is by standing on the shoulders of giants.
(Bernard of Chartres).

Databases are fun.
Let’s honour some of the most popular scientists of all times by creating a famous_scientists schema with this 3 sections challenge.

The database or schema is going to have a scientists table with 5 columns and its datatypes:

id	name	discovery	year_of_birth	year_of_death
INT(4) auto increment from 1	VARCHAR(255) can’t be NULL	TEXT can’t be NULL	INT(4) can’t be NULL	INT(4) defaults to NULL if the scientist is still alive

Creating the database

A mysql prompt has been opened for you.

Complete the following tasks:

1 - Create a famous_scientists database

Answer 1

Screenshots of the Query: 1) Create a famous_scientists database The SQL command used to create a database is CREATE DATABASE The SQL statement to create a famous_scientists database is as follows: CREATE DATABASE famous_scientists The screenshot of the above SQL statements database to create famous_scientists is as follows: 1 CREATE DATABASE famous_scientists; The sample output of the above SQL statements to create a database famous_scientists is as follows: MySQL returned an empty result set (i.e. zero rows). (Query took 0.0100 seconds.) CREATE DATABASE famous_scientists

2) Create a scientists table in the famous_scientists database The SQL statement to create a scientists table in the famous_scientists database is as follows: USE famous_scientists; CREATE TABLE scientists id INT(1) NOT NULL PRIMARY KEY AUTO_INCREMENT, VARCHAR(255) NOT NULL, name discovery TEXT NOT NULL, year_of_birth INT(4) NOT NULL, year_of_death INT(4) DEFAULT NULL The screenshot of the above SQL statements to create a table scientists is as follows User accounts Import Databases SQL Status Export Run SQL query/queries on server "MySQL": 1 USE famous_scientists; 2 CREATE TABLE scientists id INT (1) NOT NULL PRIMARY KEY AUTO INCREMENT name VARCHAR (255) NOT NULL, discovery TEXT NOT NULL, year_of_birth INT(4) NOT NULL, year_of_death INT(4) DE FAULT NULL 4 5 6

The sample output of the above SQL statements to create a table scientists is as follows MySQL returned an empty result set (i.e. zero rows). (Query took 0.0005 seconds.) USE famous_scientists MySQL returned an empty result set (ie. zero rows). (Query took 0.0265 seconds.) CREATE TABLE scientists (id INT(1) NOT NULL PRIMARY KEY AUTO INCREMENT, name VARCHAR(255) NOT NULL, discovery TEXT NOT NULL, year of birth INT(4) NOT NULL, year of death INT(4) DEFAULT NULL Edit inline]

SQL statements to copy:

1) The SQL statement to create a famous_scientists database is as follows:

CREATE DATABASE famous_scientists;

2) The SQL statement to create a scientists table in the famous_scientists database is as follows:

USE famous_scientists;

CREATE TABLE scientists (

id INT(1) NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(255) NOT NULL,

discovery TEXT NOT NULL,

year_of_birth INT(4) NOT NULL,

year_of_death INT(4) DEFAULT NULL

);

NOTE:

• Only create a famous_scientists database is asked in the question. In the second part, the SQL statements for creating a scientists table in the given database is also shown as the schema of the table is given in the question.
• To create a database, you can also write the sql statements as CREATE DATABASE IF NOT EXISTS famous_scientists;
• These statements might not work on online sql compiler, but you can run this on MySql, PhpMyAdmin, and other softwares.