CUNY Hunter College Microsoft Access Data Base Project MySQL Task

I’m working on a MySQL practice test / quiz and need guidance to help me study.

Run this R Statement

gaps<-sort(sample(1:1000,900,replace=F))

values<-sample(1:1000*1000,900,replace=F)

df<-data.frame(gaps=gaps,values=values,stringsAsFactors=F)

write.table(df,”imputation-data.csv”,quote=F,row.name=F,col.name=T,sep=’,’)

This script creates file imputation-data.csv load it into a table, either directly from R or from the file..

Upload the script you used to directory called Q2 in your home directory.

the gaps column in your table, has a set of numbers from 1 to 1000. they are supposed to be a sequence. But some numbers are missing.

Write a query to find the gaps. For example

gaps values
1 463000
2 45000
4 998000
6 147000
7 410000
8 346000
9 896000
10 370000
11 870000
12 123000
13 627000
16 901000
17 476000
18 12000
19 909000
21 610000

In the first column there are gaps between these pairs (4,6), (13,16), (19,21),

Write a SQL query to find the gaps.

PT 2:

In this example, given, gaps values
1 463000
2 45000
4 998000
6 147000
7 410000
8 346000
9 896000
10 370000
11 870000
12 123000
13 627000
16 901000
17 476000
18 12000
19 909000
21 610000

(4,6) counts as 1 gap

(13,16) counts as 1 gap

(19,21) counts as 1 gap

Between 1 and 21 there are 3 gaps.

You are being asked to write a query to find the number of gaps between 1 and 1000.

pt3:

Given the data

gaps values
1 463000
2 45000
4 998000
6 147000
7 410000
8 346000
9 896000
10 370000
11 870000
12 123000
13 627000
16 901000
17 476000
18 12000
19 909000
21 610000

We found three gaps (4,6),(13,16) and (19,21)

The gap distance is (4,6,2),(13,16,3),(19,21,2) –> 2,3,2

The value gaps are (998000, 147000),(627000,901000), (909000,610000)

The difference (-851000),(274000),(-299000)

Unit difference in value (-450500),(91333.333),(-149500)

(unit difference difference/distance) You can round the unit difference

Write a query to generate output as follows:

gaps,values,difference,unit difference

Your output should look like this

4,6,998000,147000,-851000,-450500