SQL/MySQL/Select Clause/Select

Choose specific columns

/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.01 sec)
mysql> SELECT firstname,lastName from employee where title="Programmer";
+-----------+----------+
| firstname | lastName |
+-----------+----------+
| Fred      | King     |
| John      | Mac      |
| Arthur    | Sam      |
| Alok      | Nanda    |
+-----------+----------+
4 rows in set (0.01 sec)

*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee;
SELECT firstname,lastName from employee where title="Programmer";

Define and use variable in select clause

/*
mysql> SELECT * FROM report;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  4.45 |
|    0001 | B      |  5.45 |
|    0002 | A      | 16.67 |
|    0003 | B      |  6.12 |
|    0003 | C      |  2.78 |
|    0003 | D      |  2.34 |
|    0004 | D      | 21.29 |
+---------+--------+-------+
7 rows in set (0.02 sec)
mysql> /* Using User Variables */
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   2.34 |                  21.29 |
+------------------------+------------------------+
1 row in set (0.00 sec)

*/
Drop table report;  

CREATE TABLE report (
       article INT(4) UNSIGNED ZEROFILL DEFAULT "0000" NOT NULL,
       dealer  CHAR(20)                 DEFAULT ""     NOT NULL,
       price   DOUBLE(16,2)             DEFAULT "0.00" NOT NULL,
       PRIMARY KEY(article, dealer));

INSERT INTO report VALUES (1,"A",4.45),
                        (1,"B",5.45),
                        (2,"A",16.67),
                        (3,"B",6.12),
                        (3,"C",2.78),
                        (3,"D",2.34),
                        (4,"D",21.29);
    
SELECT * FROM report;    
/* Using User Variables */
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;

Performing a Single Row INSERT

/*
mysql> Select * from Professor;
+-------------+---------------+
| ProfessorID | Name          |
+-------------+---------------+
|           0 | Snail at work |
+-------------+---------------+
1 row in set (0.00 sec)

*/
/* Create the table */
Drop TABLE Professor;
CREATE TABLE Professor (
   ProfessorID INT NOT NULL PRIMARY KEY,
   Name        VARCHAR(50) NOT NULL)
TYPE = InnoDB;
/* Real command */
INSERT INTO Professor (ProfessorID, Name) 
VALUES (0, "Snail at work");

/* Check the result */
Select * from Professor;

Performing Mathematics

/* Prepare the data */  
DROP TABLE  Exam;
CREATE TABLE Exam (
   StudentID  INT NOT NULL,
   ExamID     INT NOT NULL,
   Mark       INT,
   IfPassed   SMALLINT
)TYPE = InnoDB;
/* Insert data for testing */
INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (1,1,55,1);
INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (1,2,73,0);
INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (2,3,44,1);
/* Real command */
SELECT StudentID, ExamID, Mark * IfPassed AS MarkIfPassed FROM Exam;

Reference column in select command

/*
mysql> Drop table Bird;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Bird (
    ->     name VARCHAR(20),
    ->     owner VARCHAR(20),
    ->     species VARCHAR(20),
    ->     sex CHAR(1),
    ->     birth DATE,
    ->     death DATE
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
| RedBird  | Yin   | Bus     | m    | 1979-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT name, birth FROM Bird;
+----------+------------+
| name     | birth      |
+----------+------------+
| BlueBird | 1999-03-30 |
| RedBird  | 1979-03-30 |
+----------+------------+
2 rows in set (0.01 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL);
  
select * from Bird;
  
SELECT name, birth FROM Bird;

Retrieve all columns in a table

/*
mysql> select * from student ORDER BY first_name, last_name ASC;
+-----------+------------+-----------+
| StudentID | first_name | last_name |
+-----------+------------+-----------+
|         5 | Anna       | Wolff     |
|         4 | Bruce      | Lee       |
|         3 | Emily      | Scarlett  |
|         2 | Gary       | Burton    |
|         1 | John       | Jones     |
|         7 | Steve      | Alaska    |
|         6 | Vic        | Andrews   |
+-----------+------------+-----------+
7 rows in set (0.02 sec)

*/
Drop table Student;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   first_name      VARCHAR(50) NOT NULL,
   last_name      VARCHAR(50) NOT NULL
   
)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (2,"Gary", "Burton");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews");
INSERT INTO Student (StudentID,first_name, last_name) VALUES (7,"Steve", "Alaska");
  
select * from student ORDER BY first_name, last_name ASC;

Select clause with condition

/*mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> SELECT firstname, lastName from employee where age > 32;
+-----------+----------+
| firstname | lastName |
+-----------+----------+
| Mike      | Harper   |
+-----------+----------+
1 row in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
  
SELECT firstname, lastName from employee where age > 32;

Simple Retrieval: Returning All Columns

/*
mysql> CREATE TABLE Student (
    ->    StudentID INT NOT NULL PRIMARY KEY,
    ->    Name      VARCHAR(50) NOT NULL
    -> )TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> /* Insert data for testing */
mysql> INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
Query OK, 1 row affected (0.04 sec)
mysql> /* Real command */
mysql> SELECT * FROM Student;
+-----------+-------------+
| StudentID | Name        |
+-----------+-------------+
|         1 | Joe Wang     |
|         2 | Cory But    |
|         3 | JJ Harvests |
+-----------+-------------+
3 rows in set (0.03 sec)

*/
/* Prepare the data */ 
DROP TABLE Student;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;

/* Insert data for testing */ 
INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
  
/* Real command */
SELECT * FROM Student;

Simple Retrieval:Returning a Single Column

/*
mysql> /* Prepare the data */
mysql> DROP TABLE Student;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE Student (
    ->    StudentID INT NOT NULL PRIMARY KEY,
    ->    Name      VARCHAR(50) NOT NULL
    -> )TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> /* Insert data for testing */
mysql> INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
Query OK, 1 row affected (0.06 sec)
mysql> /* Real command */
mysql> SELECT Name FROM Student;
+-------------+
| Name        |
+-------------+
| Joe Wang     |
| Cory But    |
| JJ Harvests |
+-------------+
3 rows in set (0.01 sec)

*/
/* Prepare the data */
DROP TABLE Student;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;

/* Insert data for testing */
INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
  
/* Real command */
SELECT Name FROM Student;

Simple Retrieval:Returning Multiple Columns

/*
mysql> /* Prepare the data */
mysql> DROP TABLE Student;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE Student (
    ->    StudentID INT NOT NULL PRIMARY KEY,
    ->    Name      VARCHAR(50) NOT NULL
    -> )TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> /* Insert data for testing */
mysql> INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
Query OK, 1 row affected (0.05 sec)
mysql> /* Real command */
mysql> SELECT StudentID, Name FROM Student;
+-----------+-------------+
| StudentID | Name        |
+-----------+-------------+
|         1 | Joe Wang     |
|         2 | Cory But    |
|         3 | JJ Harvests |
+-----------+-------------+
3 rows in set (0.00 sec)
*/  
/* Prepare the data */
DROP TABLE Student;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;

/* Insert data for testing */
INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But");
INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");
  
/* Real command */
SELECT StudentID, Name FROM Student;

Use defined variable in new select clause

/*
mysql> SELECT * FROM report;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  4.45 |
|    0001 | B      |  5.45 |
|    0002 | A      | 16.67 |
|    0003 | B      |  6.12 |
|    0003 | C      |  2.78 |
|    0003 | D      |  2.34 |
|    0004 | D      | 21.29 |
+---------+--------+-------+
7 rows in set (0.00 sec)
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   2.34 |                  21.29 |
+------------------------+------------------------+
1 row in set (0.00 sec)
mysql> /* Using User Variables */
mysql> SELECT * FROM report WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  2.34 |
|    0004 | D      | 21.29 |
+---------+--------+-------+
2 rows in set (0.00 sec)

*/
Drop table report;  

CREATE TABLE report (
       article INT(4) UNSIGNED ZEROFILL DEFAULT "0000" NOT NULL,
       dealer  CHAR(20)                 DEFAULT ""     NOT NULL,
       price   DOUBLE(16,2)             DEFAULT "0.00" NOT NULL,
       PRIMARY KEY(article, dealer));

INSERT INTO report VALUES (1,"A",4.45),
                        (1,"B",5.45),
                        (2,"A",16.67),
                        (3,"B",6.12),
                        (3,"C",2.78),
                        (3,"D",2.34),
                        (4,"D",21.29);
    
SELECT * FROM report;    
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;
  
/* Using User Variables */
SELECT * FROM report WHERE price=@min_price OR price=@max_price;