MySQL Queries: Selecting Specific Columns

by

I have created a data table. If you copy and paste the code below into your MySQL window, you can obtain the same data table.

CREATE DATABASE test_data;
CREATE TABLE test_data.crop_yield (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Reps INT,
    Variable VARCHAR(15),
    Yield DECIMAL(5, 2)
);

INSERT INTO test_data.crop_yield (Reps, Variable, Yield) VALUES
    (1, 'Control', 116.08),
    (2, 'Control', 132.85),
    (3, 'Control', 115.98);

INSERT INTO test_data.crop_yield (Reps, Variable, Yield) VALUES
    (1, 'Treatment', 131.37),
    (2, 'Treatment', 108.89),
    (3, 'Treatment', 100.35);
    
ALTER TABLE test_data.crop_yield 
ADD COLUMN Yield_moisture DECIMAL(10, 2);

UPDATE test_data.crop_yield
SET Yield_moisture= CASE
    WHEN Variable='Control' THEN Yield-Yield*0.15
    WHEN Variable='Treatment' THEN Yield-Yield*0.15
    ELSE Yield
END;

□ Select all data column

<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> test_data.crop_yield;

□ Select specific data column

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> ID, Reps, Variable, Yield 
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> test_data.crop_yield;

□ Select specific data column within specific variable

<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> ID, Reps, Variable, Yield 
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> test_data.crop_yield 
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHERE</mark> Variable="Control";
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> ID, Reps, Variable, Yield, Yield_moisture
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> test_data.crop_yield 
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHERE</mark> Variable="Control" <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">AND</mark> (Reps="1" <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">OR</mark> Reps="2");