Azure SQL Code: Summary - AGRONOMY4FUTURE

Let’s practice Azure SQL codes. The data you can practice can be downloaded below.

https://github.com/agronomy4future/raw_data_practice/blob/main/micronutrients.csv

If you download the data, first you can upload data to Azure SQL server. The method to upload data to Azure SQL server is explained in the below post.

1) Create database

First, let’s create Database to upload external data in Azure Data Studio.

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">create database</mark> practice1

and upload the data in the link I provided. The method to upload external data is explained in the below post.

□ Creating an Azure SQL Database: A step-by-step guide

and let’s see the data is well uploaded.

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select * from</mark> dbo.micronutrients;

1.1) Modify database

Now I want to delete the data; “low” in plant_leave, and want to make a new database without “low” in plant_leave

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> *
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">INTO</mark> dbo.micronutrients_corrected
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHERE</mark> plant_leave <> 'low';

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> dbo.micronutrients_corrected;

Then, I want to delete micronutrients data table.

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">DROP</mark> <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">TABLE</mark> dbo.micronutrients

2) Select specific columns

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> hybrid, season, plant_leave, Boron_ppm <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients;

3) Select specific variables within columns

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">where</mark> season=2021 <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> hybrid='cv_1' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> plant_leave='upper' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> spot='A';

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">where</mark> season=2021 <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> hybrid='cv_1' 
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> plant_leave='upper' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> spot='A' 
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">and</mark> (sub_spot=1 <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">or</mark> sub_spot=2);

4) Calculation

□ Count

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> count(*) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">where</mark> season=2021;

□ min, max, sum, mean, variance, stddev

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> min(Zinc_ppm) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> min, 
max(Zinc_ppm) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> max, 
sum(Zinc_ppm) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> sum,
avg(Zinc_ppm) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> mean, 
var(Zinc_ppm) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> variance, 
stdev(Zinc_ppm) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> stddev
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">where</mark> season=2021;

### rounds

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> round(min(Zinc_ppm),2) as min, 
round(max(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> max, 
round(sum(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> sum,
round(avg(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> mean, 
round(var(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> variance, 
round(stdev(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> stddev
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">where</mark> season=2021;

### calculate per group

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">select</mark> season, hybrid, plant_leave,
round(min(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> min, 
round(max(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> max, 
round(sum(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> sum,
round(avg(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> mean, 
round(var(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> variance, 
round(stdev(Zinc_ppm),2) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">as</mark> stddev
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">from</mark> dbo.micronutrients
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">group</mark> by season, hybrid, plant_leave;

alter table dbo.micronutrients
drop column Boron_correct;

alter table dbo.micronutrients
add Boron_correct1 decimal (10, 2);

UPDATE dbo.micronutrients
SET Boron_correct1 = CASE
    WHEN season = 2020 THEN Boron_ppm * 1.5
    WHEN season = 2021 THEN Boron_ppm * 2.0
    WHEN season = 2022 THEN Boron_ppm * 0.7
    ELSE Boron_ppm
END;

select hybrid, season,  plant_leave, Boron_ppm from dbo.micronutrients