Question

Question 1:

The following questions contains real numbers for COVID-19 daily report made on 2020-09-07

Use the CREATE TABLE command and Insert command to create the following table. For each field, pick the most appropriate data type for it.

Give the table name COVID_REPORT and make “Country_name” as Primary Key.

Question 1:

The following questions contains real numbers for COVID-19 daily report made on 2020-09-07

Use the CREATE TABLE command and Insert command to create the following table. For each field, pick the most appropriate data type for it.

Give the table name COVID_REPORT and make “Country_name” as Primary Key.

Country_name	Continent	new_cases	new_deaths	total_cases	total_deaths
Saudi Arabia	Asia	895	32	320827	4081
Bahrain	Asia	676	2	54771	198
United Kingdom	Europe	2988	2	347152	41551
France	Europe	7071	3	324777	30701
Lebanon	Asia	415	4	20426	191

Country_name	Continent	new_cases	new_deaths	total_cases	total_deaths
Saudi Arabia	Asia	895	32	320827	4081
Bahrain	Asia	676	2	54771	198
United Kingdom	Europe	2988	2	347152	41551
France	Europe	7071	3	324777	30701
Lebanon	Asia	415	4	20426	191

Answer 1

Explanation:

The CREATE TABLE statement is used to create a new table.

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype
);

The unique name or identifier for the table follows the CREATE TABLE statement. Then in brackets comes the list defining each column in the table and what sort of data type it is.

Syntax for creating primary key is as follows:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(one or more columns)
);

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Syntax for creating foreign key is as follows:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(one or more columns),
FOREIGN KEY (column_name) REFERENCES tbl_name(column_name)
);

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.

Now let us create COVID_REPORT table as :

CREATE TABLE COVID_REPORT(Country_name VARCHAR(50), Continent VARCHAR(50) NOT NULL, new_cases INT NOT NULL, new_deaths INT NOT NULL, total_cases INT NOT NULL, total_deaths INT NOT NULL, PRIMARY KEY(Country_name));

From the above create statement we can see that COVID_REPORT table is created with 6 columns:

Column name	Datatype	Constraint
Country_name	VARCHAR(50)	PRIMARY KEY
Continent	VARCHAR(50)	NOT NULL
new_cases	INT	NOT NULL
new_deaths	INT	NOT NULL
total_cases	INT	NOT NULL
total_deaths	INT	NOT NULL

Now in order to insert values in the table.

The INSERT INTO statement is used to insert new records in a table.

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names. The order of the values should be in the same order as the columns in the table.

The INSERT INTO syntax for second way would be as follows:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

Now let us insert values to the COVID_REPORT Table :

INSERT INTO COVID_REPORT VALUES('Saudi Arabia','Asia',895,32,320827,4081);

INSERT INTO COVID_REPORT VALUES('Bahrain','Asia',676,2,54771,198);

INSERT INTO COVID_REPORT VALUES('United Kingdom','Europe',2988,2,347152,41551);

INSERT INTO COVID_REPORT VALUES('France','Europe',7071,3,324777,30701);

INSERT INTO COVID_REPORT VALUES('Lebanon','Asia',415,4,20426,191);

Thus, we have created COVID_REPORT Table with Country_name as Primary key and inserted values into it.