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.

create database 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.

select * from 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

SELECT *
INTO dbo.micronutrients_corrected
FROM dbo.micronutrients
WHERE plant_leave <> 'low';

SELECT * FROM dbo.micronutrients_corrected;

Then, I want to delete micronutrients data table.

DROP TABLE dbo.micronutrients

---

2) Select specific columns

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

---

3) Select specific variables within columns

select * from dbo.micronutrients
where season=2021 and hybrid='cv_1' and plant_leave='upper' and spot='A';

select * from dbo.micronutrients
where season=2021 and hybrid='cv_1' 
and plant_leave='upper' and spot='A' 
and (sub_spot=1 or sub_spot=2);

---

4) Calculation

□ Count

select count(*) from dbo.micronutrients
where season=2021;

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

select min(Zinc_ppm) as min, 
max(Zinc_ppm) as max, 
sum(Zinc_ppm) as sum,
avg(Zinc_ppm) as mean, 
var(Zinc_ppm) as variance, 
stdev(Zinc_ppm) as stddev
from dbo.micronutrients
where season=2021;

### rounds

select round(min(Zinc_ppm),2) as min, 
round(max(Zinc_ppm),2) as max, 
round(sum(Zinc_ppm),2) as sum,
round(avg(Zinc_ppm),2) as mean, 
round(var(Zinc_ppm),2) as variance, 
round(stdev(Zinc_ppm),2) as stddev
from dbo.micronutrients
where season=2021;

### calculate per group

select season, hybrid, plant_leave,
round(min(Zinc_ppm),2) as min, 
round(max(Zinc_ppm),2) as max, 
round(sum(Zinc_ppm),2) as sum,
round(avg(Zinc_ppm),2) as mean, 
round(var(Zinc_ppm),2) as variance, 
round(stdev(Zinc_ppm),2) as stddev
from dbo.micronutrients
group 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

---