# SELECT

## SELECT Syntax (Other Than SELECT \*)

Create a lab environment:

```sql
mysql> CREATE DATABASE db1;
mysql> use db1;
mysql> CREATE TABLE tb (empid VARCHAR(10), sales INT, month INT);
mysql> INSERT INTO tb VALUES ('A103', 101, 4);
mysql> INSERT INTO tb VALUES ('A102', 54, 5);
mysql> INSERT INTO tb VALUES ('A104', 181, 4);
mysql> INSERT INTO tb VALUES ('A101', 184, 4);
mysql> INSERT INTO tb VALUES ('A103', 17, 5);
mysql> INSERT INTO tb VALUES ('A101', 300, 5);
mysql> INSERT INTO tb VALUES ('A102', 205, 6);
mysql> INSERT INTO tb VALUES ('A104', 93, 5);
mysql> INSERT INTO tb VALUES ('A103', 12, 6);
mysql> INSERT INTO tb VALUES ('A107', 87, 6);
```

Select columns in a specific order:

```sql
SELECT sales, empid FROM tb;
```

Alias for column names:

```sql
SELECT empid AS employee_id, sales AS sold_items FROM tb;
```

Arithmetics based on column value:

```sql
SELECT sales*10000 FROM tb;
```

## MySQL Functions

Functions for computation (names are self-explanatory):

```sql
mysql> SELECT AVG(sales) FROM tb;
mysql> SELECT SUM(sales) FROM tb;
mysql> SELECT COUNT(sales) FROM tb;
```

Functions for displaying information (names are self-explanatory):

```sql
mysql> SELECT PI();
mysql> SELECT VERSION();
mysql> SELECT DATABASE();
mysql> SELECT USER();
```

## String Manipulation

Create a lab environment:

```sql
mysql> CREATE TABLE tb1 (empid VARCHAR(10), name VARCHAR(10), age INT);
mysql> INSERT INTO tb1 VALUES('A101', 'ABC', 40);
mysql> INSERT INTO tb1 VALUES('A102', 'DEF', 28);
mysql> INSERT INTO tb1 VALUES('A103', 'GHI', 20);
mysql> INSERT INTO tb1 VALUES('A104', 'JKL', 23);
mysql> INSERT INTO tb1 VALUES('A105', 'MNO', 35);
```

String concatenation:

```sql
SELECT CONCAT(empid, 'Mr.', name) FROM tb1;
```

Slice from the right:

```sql
SELECT RIGHT(empid, 2) FROM tb1;
```

Slice from the left:

```sql
SELECT LEFT(empid, 2) FROM tb1;
```

Slice starting from the 2nd character of `empid` and get a substring of length 3:

```sql
SELECT SUBSTRING(empid, 2, 3) FROM tb1;
```

Repeat for a variable times (used for drawing graphs):

```sql
SELECT REPEAT('.', age) FROM tb1;
```

Print a string reversely:

```sql
SELECT REVERSE(name) FROM tb1;
```

## Date and Time

Get current time with `NOW()`:

```sql
mysql> CREATE TABLE t_now (a INT AUTO_INCREMENT PRIMARY KEY, b DATETIME);
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
mysql> INSERT INTO t_now (b) VALUES(NOW());
```

## SELECT with Conditions

Limit the number of records:

```sql
SELECT * FROM tb LIMIT 3;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://ret2basic.gitbook.io/ctfnote/computer-science/databases/mysql/select.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.