Question

Question 1 – Inserting data to the CITY table

Using the following DML statement:

INSERT INTO CITY (id, name, countrycode, district, population) VALUES (7000, ‘Guelph’, ‘CAN’, ‘Ontario’, 131794);

Query the CITY table to ensure this row was inserted correctly.

Provide screenshot of your ‘SELECT statement’ and resultset pane here

               Insert another row into the CITY table with the following values:

                              Id                           7002

                              Name                   Montebello

                              Countrycode       CAN

                              District                 Quebec

                              Population          983

Provide screenshot of your ‘INSERT statement’ and resultset pane here

Question 2 – Insert data to COUNTRY table

               Insert a row into the COUNTRY table with the following values:

                              Code                     SRB

                              Name                   Serbia

                              Continent            Europe

                              Region                 Eastern Europe

                              Surface Area       88361

                              IndepYear            2006

                              Population          6963764

Provide screenshot of your ‘INSERT statement’ and resultset pane here

Question 3 – Incorrect SQL statement

There are syntax errors in the following SQL statement.   Correct and execute the corrected SQL statement.

INSERT INTO Country( Code, Name, Continent, Region, SurfaceArea, IndepYear, Population )

VALUES( 'MNE, 'Montenegro', 'Europe', 'Eastern Europe' 13812, 2006, 622359 );

Provide screenshot of your ‘INSERT statement’ and resultset pane here

Question 4 – Update a row in COUNTRY table

It is often necessary to query the data to determine how best to filter on the correct row (s) to update.   Using the Select * from COUNTRY will display all the data and then you see all the data.

               Update the column – governmentform changing the value from Serbia to Republic

              But only for the country named Serbia

Note: this query should only update a single row

Provide screenshot of your ‘UPDATE statement’ and resultset pane here

Question 5 – Update a row in COUNTRY table

This ‘update’ is meant to change the ‘population’ of Canada to 37971020

Provide screenshot of your ‘UPDATE statement’ and resultset pane here

Question 6 – Deleting rows in the COUNTRYLANGUAGE table

Create the SQL statement which will delete any rows in COUNTRYLANGUAGE table where country is Canada.   Hint, you should see twelve (12) rows and its is best when ‘deleting’ data, to always query the data using the filter before running the ‘delete’ statement.

Provide screenshot of your ‘UPDATE statement’ and resultset pane here

Question 7 – Deleting rows in a table

Modify the data in the COUNTRYLANGUAGE table using the script provided – CanadianLanguages2020.sql

Once you have executed this script, write a query statement that will list all rows which have English and French as languages.

Provide screenshot of your ‘SELECT statement’ and resultset pane here

Question 8 – Updating the Official Language for Canada

Modify the rows in the COUNTRYLANGUAGE table so that all rows associated to Canada has their ‘isofficial’ indicator column set to one(1).

Provide screenshot of your ‘UPDATE statement’ and resultset pane here

Question 9 – Updating more than one column using a single SQL statement

Write a single SQL statement to change the population of Algeria to 43600000 and gnp to 684689.

Provide screenshot of your ‘UPDATE statement’ and resultset pane here

Answer 1

Below are the answers

Note as Table structire was not given, created my sample table strcture and executed the queries

Question 1

INSERT INTO CITY (id, name, countrycode, district, population) VALUES (7000, ‘Guelph’, ‘CAN’, ‘Ontario’, 131794);

Below is the screenshot of select statement

INSERT INTO CITY (id, name, countrycode, district, population) VALUES (7002, 'Montebello', 'CAN', 'Quebec', 983);

Below is the screenshot after inserting city with id 7002

**********************************************************************************

Question 2

Inserting data with given values

Insert

Result of inserted row

**********************************************************************************

Question 3

Correct insert query after fixing the eerors

INSERT INTO Country( Code, Name, Continent, Region, SurfaceArea, IndepYear, Population )

VALUES( 'MNE', 'Montenegro', 'Europe', 'Eastern Europe', 13812, 2006, 622359 );

Explanation end quate was missing after MNE – It should be 'MNE' and comma was missing after 'Eastern Europe'

**********************************************************************************

Question 4 -

Ans - Update Country set governmentform = 'Republic' where Name = 'Serbia'

Explanation

As above Country table was not having column governmentfrom column so aaded governmentfrom to the Country table for updating the same.

Below is the sample records in country table before updating governmentfrom

Update the column –   changing the value from Serbia to Republic. But only for the country named Serbia

Below is the update statement

Update Country set governmentform = 'Republic' where Name = 'Serbia'

Below is the screenshot

Below is the result after updation

**********************************************************************************

Question 5 -

Answer - Update country set Population = 37971020 where Name = 'Canada'

Explanation

Before updating the population

Update statement - Update country set Population = 37971020 where Name = 'Canada'

Below is the screenshot

Below is the screenshot of updated population for Canada

**********************************************************************************

Question 6 -

Answer - DELETE FROM COUNTRYLANGUAGE WHERE country='Canada'

Explanation

As COUNTRYLANGUAGE structure is not given so created sample COUNTRYLANGUAGE table with 2 columns LanguageId and country.

Before delete query below is the COUNTRYLANGUAGE table data

After delete query

DELETE FROM COUNTRYLANGUAGE WHERE country='Canada'

Below is the screenshot after delete query

**********************************************************************************

Question 7

Answer - select * from COUNTRYLANGUAGE where Language in ('English', 'French')

As table structure is not there assumed table structure with 2 columns Language and Country.

Below is the all record

Ans - select * from COUNTRYLANGUAGE where Language in ('English', 'French')

OR - select * from COUNTRYLANGUAGE where Language = 'English' or Language = 'French'

Output

**********************************************************************************

Question 8

Answer -

Update COUNTRYLANGUAGE

SET isofficial =

CASE WHEN Country = 'Canada' THEN 1

ELSE 0

END

****************************************************************************************

Question 9

Answer -

UPDATE country

   SET Population = CASE Name

                      WHEN 'Algeria' THEN 43600000

                      WHEN 'gnp' THEN 684689

                      ELSE Population

                      END

WHERE Name IN('Algeria', 'gnp');

Note for question 8 and 9 use queries as per your table structure. Use column names as per your table structure. As required table structure i don;t know so given the generic queries.