In SQL,Query statements scan one or more tables or expressions and return the computed result rows. With SELECT statement, you can specify the columns you want to retrieve, as well as any filtering, grouping, or ordering criteria you want to apply to the data.
The fictitious dataset called “Ivys_shop.csv” can be downloaded here.
SELECT as Value
You can assign any value to a column or multiple columns. In the example, the “apple” is the string value, the “fruit” is the alias of the column.
> SELECT "apple" AS fruit # Assign a value "apple" to fruit
/*-------+
| fruit |
+-------+
| apple |
*-------+
> SELECT "orange" AS fruit, "tomato" AS vegetable
/*--------+-----------*
| fruit | vegetable|
+--------+----------+
| orange | tomato |
*--------+----------*/SELECT multiple columns
You can select one or more columns and their records with SELECT()
# select columns from the table (Name,Quantity)
SELECT NAME,Quantity
FROM `instruction-415216.examples.ivys_shop` #address of the table| Row | NAME | Quantity |
|---|---|---|
| 1 | Kiwi | 99 |
| 2 | Kiwi | 41 |
| 3 | Kiwi | 74 |
| 4 | Kiwi | 39 |
| 5 | Kiwi | 86 |
SELECT ALL
If you want to have a quick view of the table, you can use SELECT * to retrieve all data.
SELECT *
FROM `instruction-415216.examples.ivys_shop` | Row | Name | Category | Price | Quantity | Sold | Purchase_Price |
|---|---|---|---|---|---|---|
| 1 | Kiwi | Fruit | 8.86 | 99 | 10 | 2.7 |
| 2 | Kiwi | Fruit | 4.61 | 41 | 35 | 2.34 |
| 3 | Kiwi | Fruit | 0.85 | 74 | 57 | 2.56 |
| 4 | Kiwi | Fruit | 2.7 | 39 | 22 | 0.95 |
| 5 | Kiwi | Fruit | 7.52 | 86 | 80 | 0.6 |
| … |
SELECT EXCEPT
SELECT EXCEPT is a good way to retrieve data and filter the columns that you don’t want. For example, I want to get all columns except Category. I can list each column’s name or I just use the EXCEPT .
SELECT * EXCEPT (Category)
FROM `instruction-415216.examples.ivys_shop` | Row | Name | Price | Quantity | Sold | Purchase_Price |
|---|---|---|---|---|---|
| 1 | Kiwi | 8.86 | 99 | 10 | 2.7 |
| 2 | Kiwi | 4.61 | 41 | 35 | 2.34 |
| 3 | Kiwi | 0.85 | 74 | 57 | 2.56 |
| 4 | Kiwi | 2.7 | 39 | 22 | 0.95 |
| 5 | Kiwi | 7.52 | 86 | 80 | 0.6 |
| | | … | | | |
SELECT DISTINCT
You have noticed that the name of fruit has many duplicates. If I want to get the name of each fruit and vegetable, I can use the DISTINCT to get the result. A SELECT DISTINCT statement discards duplicate rows and returns only the remaining rows.
SELECT DISTINCT NAME
FROM `instruction-415216.examples.ivys_shop` | Row | NAME |
|---|---|
| 1 | Kiwi |
| 2 | Apple |
| 3 | Mango |
| 4 | Peach |
| 5 | Banana |
| 6 | Grapes |
| 7 | Orange |
| … | … |