Homework answers / question archive / SQL -Data Analytics The Median screenshot will help you with calculating a question in Activity 1

SQL -Data Analytics The Median screenshot will help you with calculating a question in Activity 1

Computer Science

Share With

---

SQL -Data Analytics

The Median screenshot will help you with calculating a question in Activity 1.

                                                                Activity 1          

Exercise 1 What are the main components of a database? Answer it by a few words

or one sentence.

Exercise 2 Provide the major advantages of DBMS over file processing system.

Answer it by 3~4 sentences maximum.

Exercise 3 Given an array of numbers as follows: x = [1 0 9 10 5 8 10 0 5 4 7 7 3 7],

compute the following: 1) mode(x); 2) mean(x); 3) median(x).

Exercise 4 For the same array x from Exercise 3, compute the 1) range (x); 2)

standard deviation SD(x).

 

 

Instructions

USE WAMP OR MAMP AS A PLATFORM FOR SQL OR ANY ANOTHER AS LONG AS IT CAN OPEN ON WAMP WITH THE OUTCOME.

Import the attached database world.sql into the platform you are using and answer the questions below. Present the syntax and screen shot of output.

 

The world.sql  file has been uploaded separately .

 

                                                               Activity 2

Exercise 1 Import the “world.sql” database to phpMyAdmin, you should be able to

view it’s three tables.

1) From the “city” table, retrieve the list of city names, in which the city name starts

by “S”, and the population over 9000000. Include the SQL code and screenshot.

Hint: You may be needing a SQL function LIKE.

2) From the “country” table, retrieve a new table with two columns: `Name`,

`LifeExpectancy`, in which only contains the country Independent in 1971, for

the data within this restriction, then order the life expectancy from low to high.

Include the SQL code and screenshot.

Exercise 2 Create new table using the existing table coutrylanguage from the

database world.sql.

1) It is required the new table be named as “english_speaking”, which will be

containing only the information of English-speaking rows, i.e., the “Language”

column of the new table must display only “English”. Include the SQL code and

screenshot (Number of rows to display: 25).

2) Update the newly created “english_speaking” table, remove the rows that do

not consider English as the official language, also remove the population

percentage that is 0.0. Include the SQL code and updated screenshot of the

table “english_speaking” (Number of rows to display: 25).

                                                                    Activity 3

Exercise 1 Import the “world.sql” database to phpMyAdmin, you should be able to

view it’s four tables.

1) From the countrylanguage table, extract/generate two columns: Language

and COUNT(*), rank the frequency of languages in decreasing order, the

COUNT(*) column should indicate the number of appearance for each

different language. Provide the code and the first screenshot for the first 25

rows of the generated table.

2) Implement the Multiple Column GROUP BY toward “Language” and

“IsOfficial” columns of table countrylanguage. Provide the code and the first

screenshot for the first 25 rows of the generated table.

Exercise 2 Import the “world.sql” database to phpMyAdmin. Find and count the unique

languages, then group them based on whether they are used as official language.

                                                      

 

                                                    Activity 4

Exercise 1

For data analysis using the world.sql. In the world.sql database,

select the country table. We want to explore the relationship between the country’s

surface area and population for each continent using Pearson's Correlation. Recall in

the 1st lecture, we introduced the mathematical definition of this correlation:

1) Create 6 new tables from the country table, each of them caters to a continent,

i.e., Asia, Europe, North America etc. Each table should contain the following

two attributes: SurfaceArea and Population.

2) For each continental table, using the math functions introduced to

translate the above formula via MySQL. You should be getting one Pearson's

Correlation coefficient for each table. Compare and see the correlation strength

between SurfaceArea and Population for each continental table.