Informatics practices

practical file
STRUCTURED QUERY LANGUAGE(SQL):

mysql> create database Xavier;

Query OK, 1 row affected (0.04 sec)
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| run |
| sakila |
| sys |
| world |
| xavier |
+--------------------+
8 rows in set (0.08 sec)
mysql> use xavier;
Database changed
mysql> show tables;
Empty set (0.03 sec)
mysql> create table employee(
-> empno int primary key,
-> ename varchar(20) not null,
-> job varchar (20),
-> mgr int,
-> hiredate date,
-> sal int,
-> comm decimal (5,2),
-> deptno int
-> );
Query OK, 0 rows affected (0.21 sec)
mysql> describe employee;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| job | varchar(20) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | int | YES | | NULL | |
| comm | decimal(5,2) | YES | | NULL | |
| deptno | int | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql> insert into employee values(7369,'SMITH','Clerk',7902,'1980-12-
17',800,null,null);
Query OK, 1 row affected (0.02 sec)
mysql> insert into employee values(7499,'ALLEN','Salesman',7698,'1981-02-
20',1600,500,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7521,'WARD','Salesman',7698,'1981-02-
22',1250,500,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7566,'JONES','Manager',7839,'1981-04-
02',2975,null,20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7654,'MARTIN','Salesman',7698,'1981-09-
28',1250,400,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7698,'BLAKE','Manager',7839,'1981-05-
01',2850,null,30);
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee values(7782,'CLARK','Manager',7339,'1981-06-

09',2850,null,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7788,'SCOTT','Analyst',7566,'1982-12-
09',3000,null,20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7839,'KING','President',null,'1981-11-
17',5000,null,10);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7844,'TURNER','Salesman',7689,'1981-09-
08',1500,null,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7876,'ADAM','Clerk',7788,'1983-01-
12',1100,null,20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7900,'JAMES','Clerk',7698,'1981-12-
03',950,null,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7902,'FORD','Analyst',7566,'1981-12-
03',3000,null,null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(7934,'MILLER','Clerk',7782,'1982-01-
23',1300,null,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+------+--------+-----------+------+------------+--------+--------+--------+
| EMPO | ENAME | JOB | MGR | HIREDATE | SALARY | COMM | DEPTNO |
+------+--------+-----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+------+--------+-----------+------+------------+--------+--------+--------+
14 rows in set (0.01 sec)
mysql> select ename,salary from employee;
+--------+--------+
| ename | salary |
+--------+--------+
| SMITH | 800 |
| ALLEN | 1600 |
| WARD | 1250 |
| JONES | 2975 |
| MARTIN | 1250 |
| BLAKE | 2850 |
| CLARK | 2850 |
| SCOTT | 3000 |
| KING | 5000 |
| TURNER | 1500 |
| ADAM | 1100 |
| JAMES | 950 |
| FORD | 3000 |
| MILLER | 1300 |
+--------+--------+
14 rows in set (0.01 sec)

mysql> select empo,ename,job,mgr,hiredate,salary,comm,deptno from employee;

+------+--------+-----------+------+------------+--------+--------+--------+
| empo | ename | job | mgr | hiredate | salary | comm | deptno |
+------+--------+-----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 140.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+------+--------+-----------+------+------------+--------+--------+--------+
14 rows in set (0.00 sec)

mysql> select * from employee;

+------+--------+-----------+------+------------+--------+--------+--------+
| EMPO | ENAME | JOB | MGR | HIREDATE | SALARY | COMM | DEPTNO |
+------+--------+-----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 140.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+------+--------+-----------+------+------------+--------+--------+--------+
14 rows in set (0.00 sec)
mysql> select empo,ename,hiredate,salary,job,mgr,deptno,comm from employee;
+------+--------+------------+--------+-----------+------+--------+--------+
| empo | ename | hiredate | salary | job | mgr | deptno | comm |
+------+--------+------------+--------+-----------+------+--------+--------+
| 7369 | SMITH | 1980-12-17 | 800 | CLERK | 7902 | NULL | NULL |
| 7499 | ALLEN | 1981-02-20 | 1600 | SALESMAN | 7698 | 30 | 300.00 |
| 7521 | WARD | 1981-02-22 | 1250 | SALESMAN | 7698 | 30 | 500.00 |
| 7566 | JONES | 1981-04-02 | 2975 | MANAGER | 7839 | 20 | NULL |
| 7654 | MARTIN | 1981-09-28 | 1250 | SALESMAN | 7698 | 30 | 140.00 |
| 7698 | BLAKE | 1981-05-01 | 2850 | MANAGER | 7839 | 30 | NULL |
| 7782 | CLARK | 1981-06-09 | 2850 | MANAGER | 7339 | 30 | NULL |
| 7788 | SCOTT | 1982-12-09 | 3000 | ANALYST | 7566 | 20 | NULL |
| 7839 | KING | 1981-11-17 | 5000 | PRESIDENT | NULL | 10 | NULL |
| 7844 | TURNER | 1981-09-08 | 1500 | SALESMAN | 7689 | 30 | NULL |
| 7876 | ADAM | 1983-01-12 | 1100 | CLERK | 7788 | 20 | NULL |
| 7900 | JAMES | 1981-12-03 | 950 | CLERK | 7698 | 30 | NULL |
| 7902 | FORD | 1981-12-03 | 3000 | ANALYST | 7566 | NULL | NULL |
| 7934 | MILLER | 1982-01-23 | 1300 | CLERK | 7782 | NULL | NULL |
+------+--------+------------+--------+-----------+------+--------+--------+
14 rows in set (0.00 sec)

mysql> select empo,ename,salary from employee;

+------+--------+--------+
| empo | ename | salary |
+------+--------+--------+
| 7369 | SMITH | 800 |
| 7499 | ALLEN | 1600 |
| 7521 | WARD | 1250 |
| 7566 | JONES | 2975 |
| 7654 | MARTIN | 1250 |
| 7698 | BLAKE | 2850 |
| 7782 | CLARK | 2850 |
| 7788 | SCOTT | 3000 |
| 7839 | KING | 5000 |
| 7844 | TURNER | 1500 |
| 7876 | ADAM | 1100 |
| 7900 | JAMES | 950 |
| 7902 | FORD | 3000 |
| 7934 | MILLER | 1300 |
+------+--------+--------+
14 rows in set (0.00 sec)
mysql> select empo,ename,salary*12 from employee;
+------+--------+-----------+
| empo | ename | salary*12 |
+------+--------+-----------+
| 7369 | SMITH | 9600 |
| 7499 | ALLEN | 19200 |
| 7521 | WARD | 15000 |
| 7566 | JONES | 35700 |
| 7654 | MARTIN | 15000 |
| 7698 | BLAKE | 34200 |
| 7782 | CLARK | 34200 |
| 7788 | SCOTT | 36000 |
| 7839 | KING | 60000 |
| 7844 | TURNER | 18000 |
| 7876 | ADAM | 13200 |
| 7900 | JAMES | 11400 |
| 7902 | FORD | 36000 |
| 7934 | MILLER | 15600 |
+------+--------+-----------+
14 rows in set (0.01 sec)
mysql> select empo,ename,salary*12'annual salary'
-> from employee;
+------+--------+---------------+
| empo | ename | annual salary |
+------+--------+---------------+
| 7369 | SMITH | 9600 |
| 7499 | ALLEN | 19200 |
| 7521 | WARD | 15000 |
| 7566 | JONES | 35700 |
| 7654 | MARTIN | 15000 |
| 7698 | BLAKE | 34200 |
| 7782 | CLARK | 34200 |
| 7788 | SCOTT | 36000 |
| 7839 | KING | 60000 |
| 7844 | TURNER | 18000 |
| 7876 | ADAM | 13200 |
| 7900 | JAMES | 11400 |
| 7902 | FORD | 36000 |
| 7934 | MILLER | 15600 |
+------+--------+---------------+
14 rows in set (0.00 sec)

mysql> select empo,ename,salary*12 as'annual salary'

-> from employee;
+------+--------+---------------+
| empo | ename | annual salary |
+------+--------+---------------+
| 7369 | SMITH | 9600 |
| 7499 | ALLEN | 19200 |
| 7521 | WARD | 15000 |
| 7566 | JONES | 35700 |
| 7654 | MARTIN | 15000 |
| 7698 | BLAKE | 34200 |
| 7782 | CLARK | 34200 |
| 7788 | SCOTT | 36000 |
| 7839 | KING | 60000 |
| 7844 | TURNER | 18000 |
| 7876 | ADAM | 13200 |
| 7900 | JAMES | 11400 |
| 7902 | FORD | 36000 |
| 7934 | MILLER | 15600 |
+------+--------+---------------+
14 rows in set (0.00 sec)
mysql>
mysql> select 2 + 3;
+------+
| 2+3 |
+------+
|5|
+------+
1 row in set (0.01 sec)

mysql> select 2+3 from employee;

+------+
| 2+3 |
+------+
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
|5|
+------+
14 rows in set (0.00 sec)

mysql> select ename,'working as',job from employee;

+--------+------------+-----------+
| ename | working as | job |
+--------+------------+-----------+
| Smith | working as | Clerk |
| Allen | working as | Salesman |
| Ward | working as | Salesman |
| Jones | working as | Manager |
| Martin | working as | Salesman |
| Blake | working as | Manager |
| Clark | working as | Manager |
| Scott | working as | Analyst |
| King | working as | President |
| Turner | working as | Salesman |
| Adam | working as | Clerk |
| James | working as | Clerk |
| Ford | working as | Analyst |
| Miller | working as | Clerk |
+--------+------------+-----------+
14 rows in set (0.01 sec)

mysql> select * from employee

-> where job='clerk';
+-------+--------+-------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+-------+------+------------+------+------+--------+
4 rows in set (0.01 sec)
mysql> select empno,ename,sal from employee
-> where job<>'clerk';
+-------+--------+------+
| empno | ename | sal |
+-------+--------+------+
| 7499 | ALLEN | 1600 |
| 7521 | WARD | 1250 |
| 7566 | JONES | 2975 |
| 7654 | MARTIN | 1250 |
| 7698 | BLAKE | 2850 |
| 7782 | CLARK | 2850 |
| 7788 | SCOTT | 3000 |
| 7839 | KING | 5000 |
| 7844 | TURNER | 1500 |
| 7902 | FORD | 3000 |
+-------+--------+------+
10 rows in set (0.00 sec)

mysql> select empno,ename,job,sal from employee where deptno=10 and sal>1000;

+-------+-------+-----------+------+
| empno | ename | job | sal |
+-------+-------+-----------+------+
| 7839 | KING | PRESIDENT | 5000 |
+-------+-------+-----------+------+

mysql> select * from employee where deptno=10 or deptno=20;

+-------+-------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
+-------+-------+-----------+------+------------+------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from employee where job='clerk' or job='manager' or job='analyst';

+-------+--------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+---------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+---------+------+------------+------+------+--------+
9 rows in set (0.00 sec)

mysql> select * from employee where job in ('clerk','manager','analyst');

+-------+--------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+---------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+---------+------+------------+------+------+--------+
9 rows in set (0.01 sec)
mysql> select * from employee where job not in ('clerk', 'manager', 'analyst');
+-------+--------+-----------+------+------------+------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 500.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 400.00 | 30 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7689 | 1981-09-08 | 1500 | NULL | 30 |
+-------+--------+-----------+------+------------+------+--------+--------+
5 rows in set (0.01 sec)
mysql> select empno, ename, sal from employee where sal >=1000 and sal <=2000;
+-------+--------+------+
| empno | ename | sal |
+-------+--------+------+
| 7499 | ALLEN | 1600 |
| 7521 | WARD | 1250 |
| 7654 | MARTIN | 1250 |
| 7844 | TURNER | 1500 |
| 7876 | ADAM | 1100 |
| 7934 | MILLER | 1300 |
+-------+--------+------+
6 rows in set (0.00 sec)

mysql> select empno, ename, sal from employee where sal not between 1000 and 2000;
+-------+-------+------+
| empno | ename | sal |
+-------+-------+------+
| 7369 | SMITH | 800 |
| 7566 | JONES | 2975 |
| 7698 | BLAKE | 2850 |
| 7782 | CLARK | 2850 |
| 7788 | SCOTT | 3000 |
| 7839 | KING | 5000 |
| 7900 | JAMES | 950 |
| 7902 | FORD | 3000 |
+-------+-------+------+
8 rows in set (0.00 sec)
mysql> select * from employee
-> where comm is not null;
+-------+--------+----------+------+------------+------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 500.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 400.00 | 30 |
+-------+--------+----------+------+------------+------+--------+--------+
3 rows in set (0.01 sec)

mysql> select empno,ename,sal,comm from employee

-> where comm is null;
+-------+--------+------+------+
| empno | ename | sal | comm |
+-------+--------+------+------+
| 7369 | SMITH | 800 | NULL |
| 7566 | JONES | 2975 | NULL |
| 7698 | BLAKE | 2850 | NULL |
| 7782 | CLARK | 2850 | NULL |
| 7788 | SCOTT | 3000 | NULL |
| 7839 | KING | 5000 | NULL |
| 7844 | TURNER | 1500 | NULL |
| 7876 | ADAM | 1100 | NULL |
| 7900 | JAMES | 950 | NULL |
| 7902 | FORD | 3000 | NULL |
| 7934 | MILLER | 1300 | NULL |
+-------+--------+------+------+
11 rows in set (0.00 sec)
mysql> select * from employee
-> where ename like 'a%';
+-------+-------+----------+------+------------+------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 500.00 | 30 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
+-------+-------+----------+------+------------+------+--------+--------+
2 rows in set (0.01 sec)

mysql> select empno,ename,sal from employee

-> where ename like '____';
+-------+-------+------+
| empno | ename | sal |
+-------+-------+------+
| 7521 | WARD | 1250 |
| 7839 | KING | 5000 |
| 7876 | ADAM | 1100 |
| 7902 | FORD | 3000 |
+-------+-------+------+
4 rows in set (0.00 sec)

mysql> select distinct deptno , job from employee;

+--------+-----------+
| deptno | job |
+--------+-----------+
| NULL | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 20 | CLERK |
| 30 | CLERK |
| NULL | ANALYST |
+--------+-----------+
9 rows in set (0.02 sec)

mysql> select distinct job,ename from employee;

+-----------+--------+
| job | ename |
+-----------+--------+
| CLERK | SMITH |
| SALESMAN | ALLEN |
| SALESMAN | WARD |
| MANAGER | JONES |
| SALESMAN | MARTIN |
| MANAGER | BLAKE |
| MANAGER | CLARK |
| ANALYST | SCOTT |
| PRESIDENT | KING |
| SALESMAN | TURNER |
| CLERK | ADAM |
| CLERK | JAMES |
| ANALYST | FORD |
| CLERK | MILLER |
+-----------+--------+
14 rows in set (0.00 sec)

mysql> select distinct empno, job from employee;

+-------+-----------+
| empno | job |
+-------+-----------+
| 7369 | CLERK |
| 7499 | SALESMAN |
| 7521 | SALESMAN |
| 7566 | MANAGER |
| 7654 | SALESMAN |
| 7698 | MANAGER |
| 7782 | MANAGER |
| 7788 | ANALYST |
| 7839 | PRESIDENT |
| 7844 | SALESMAN |
| 7876 | CLERK |
| 7900 | CLERK |
| 7902 | ANALYST |
| 7934 | CLERK |
+-------+-----------+
14 rows in set (0.00 sec)

mysql> alter table employee add tax decimal(6,2);

Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+-------+--------+-----------+------+------------+------+------+--------+------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | tax |
+-------+--------+-----------+------+------------+------+------+--------+------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | NULL | NULL |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 500 | 30 | NULL |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | NULL |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 | NULL |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 400 | 30 | NULL |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | NULL |
| 7782 | Clark | Manager | 7339 | 1981-06-09 | 2850 | NULL | 30 | NULL |
| 7788 | Scott | Analyst | 7566 | 1982-12-09 | 3000 | NULL | 20 | NULL |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 | NULL |
| 7844 | Turner | Salesman | 7689 | 1981-09-08 | 1500 | NULL | 30 | NULL |
| 7876 | Adam | Clerk | 7788 | 1983-01-12 | 1100 | NULL | 20 | NULL |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 | NULL |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | NULL | NULL |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+------+
14 rows in set (0.00 sec)

mysql> describe employee;

+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(20) | NO | | NULL | |
| job | varchar(20) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | int | YES | | NULL | |
| comm | int | YES | | NULL | |
| deptno | int | YES | | NULL | |
| tax | decimal(6,2) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
9 rows in set (0.04 sec)
mysql> alter table employee modify ename varchar(25);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table employee drop tax;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 500 | 30 |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 400 | 30 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | Manager | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | Scott | Analyst | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | Salesman | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | Adam | Clerk | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

mysql> alter table employee drop primary key;

Query OK, 14 rows affected (0.12 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> describe employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno | int | NO | | NULL | |
| ename | varchar(25) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | int | YES | | NULL | |
| comm | int | YES | | NULL | |
| deptno | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql> alter table employee add primary key(empno);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(25) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | int | YES | | NULL | |
| comm | int | YES | | NULL | |
| deptno | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> insert into employee values(0000,'Jonny','Clerk',7098,'1990-11-

30',300,400,20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 0 | Jonny | Clerk | 7098 | 1990-11-30 | 300 | 400 | 20 |
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 500 | 30 |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 400 | 30 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | Manager | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | Scott | Analyst | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | Salesman | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | Adam | Clerk | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.00 sec)
mysql> update employee set deptno=10
-> where empno=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 0 | Jonny | Clerk | 7098 | 1990-11-30 | 300 | 400 | 10 |
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 500 | 30 |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 400 | 30 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | Manager | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | Scott | Analyst | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | Salesman | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | Adam | Clerk | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.00 sec)
mysql> delete from employee
-> where empno=0;
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 500 | 30 |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 400 | 30 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | Manager | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | Scott | Analyst | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | Salesman | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | Adam | Clerk | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql&gt; use xavier;
Database changed
mysql&gt; select * from employee;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | NULL | NULL |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 500 | 30 |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 400 | 30 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | Manager | 7339 | 1981-06-09 | 2850 | NULL | 30 |
| 7788 | Scott | Analyst | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | Salesman | 7689 | 1981-09-08 | 1500 | NULL | 30 |
| 7876 | Adam | Clerk | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | NULL |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.03 sec)
mysql&gt; select count(*) from employee;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.03 sec)
mysql&gt; select count(distinct job) from employee;
+---------------------+
| count(distinct job) |
+---------------------+
|5|
+---------------------+
1 row in set (0.02 sec)
mysql&gt; select count(comm) from employee;
+-------------+
| count(comm) |
+-------------+
|3|
+-------------+
1 row in set (0.00 sec)
mysql&gt; select count(distinct comm) from employee;
+----------------------+
| count(distinct comm) |
+----------------------+
|2|
+----------------------+
1 row in set (0.00 sec)
mysql&gt; select count(*) from employee where sal&gt;2000;
+----------+
| count(*) |
+----------+
|6|
+----------+
1 row in set (0.01 sec)

mysql&gt; select sum(sal),avg(sal) from employee where job=&#39;clerk&#39;and

sal&gt;2000;
+----------+----------+
| sum(sal) | avg(sal) |
+----------+----------+
| NULL | NULL |
+----------+----------+
1 row in set (0.00 sec)
mysql&gt; select sum(sal),avg(sal) from employee where job=&#39;clerk&#39;and
sal&gt;1000;
+----------+-----------+
| sum(sal) | avg(sal) |
+----------+-----------+
| 2400 | 1200.0000 |
+----------+-----------+
1 row in set (0.00 sec)
mysql&gt; select count(*) from employee where comm is null;
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.00 sec)
mysql&gt;
mysql&gt; select deptno, count(*) from employee group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| NULL | 3 |
| 30 | 7 |
| 20 | 3 |
| 10 | 1 |
+--------+----------+
4 rows in set (0.00 sec)
mysql&gt; select job,sum(sal) from employee group by job;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| Clerk | 4150 |
| Salesman | 5600 |
| Manager | 8675 |
| Analyst | 6000 |
| President | 5000 |
+-----------+----------+
5 rows in set (0.01 sec)

mysql&gt; select job,sum(sal) from employee group by job having job

&lt;&gt;&#39;clerk&#39;;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| Salesman | 5600 |
| Manager | 8675 |
| Analyst | 6000 |
| President | 5000 |
+-----------+----------+
4 rows in set (0.01 sec)
mysql&gt; select deptno , min(sal),max(sal) from employee group by deptno having
count(*)&gt;3;
+--------+----------+----------+
| deptno | min(sal) | max(sal) |
+--------+----------+----------+
| 30 | 950 | 2850 |
+--------+----------+----------+
1 row in set (0.01 sec)
mysql&gt; select deptno,sum(sal) from employee where job&lt;&gt;&#39;clerk&#39;
group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 30 | 11300 |
| 20 | 5975 |
| 10 | 5000 |
| NULL | 3000 |
+--------+----------+
4 rows in set (0.02 sec)
mysql&gt; select deptno,sum(sal) from employee where job&lt;&gt;&#39;clerk&#39;
group by deptno having deptno&lt;&gt;10;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 30 | 11300 |
| 20 | 5975 |
+--------+----------+
2 rows in set (0.01 sec)
mysql&gt; select deptno,sum(sal) from employee where comm is null and deptno = 10
group by job;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 10 | 5000 |
+--------+----------+
1 row in set (0.01 sec)
mysql&gt; select job,count(*) from employee where comm is null and deptno &lt;&gt;10
group by job;
+----------+----------+
| job | count(*) |
+----------+----------+
| Manager | 3 |
| Analyst | 1 |
| Salesman | 1 |
| Clerk | 2 |
+----------+----------+
4 rows in set (0.00 sec)

mysql&gt; use xavier;

Database changed
mysql&gt; select job,sum(sal) from employee group by job;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| Clerk | 4150 |
| Salesman | 5600 |
| Manager | 8675 |
| Analyst | 6000 |
| President | 5000 |
+-----------+----------+
5 rows in set (0.11 sec)
mysql&gt; select job,sum(sal) from employee group by job having
job&lt;&gt;&#39;clerk&#39;;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| Salesman | 5600 |
| Manager | 8675 |
| Analyst | 6000 |
| President | 5000 |
+-----------+----------+
4 rows in set (0.01 sec)
mysql&gt; select deptno,min(sal),max(sal) from employee group by deptno having
count(*)&gt;3;
+--------+----------+----------+
| deptno | min(sal) | max(sal) |
+--------+----------+----------+
| 30 | 950 | 2850 |
+--------+----------+----------+
1 row in set (0.01 sec)
mysql&gt; select deptno,sum(sal) from employee where job&lt;&gt;&#39;clerk&#39;
group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 30 | 11300 |
| 20 | 5975 |
| 10 | 5000 |
| NULL | 3000 |
+--------+----------+
4 rows in set (0.00 sec)
mysql&gt; select deptno,sum(sal) from employee where job&lt;&gt;&#39;clerk&#39;
group by deptno having deptno &lt;&gt; 10;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 30 | 11300 |
| 20 | 5975 |
+--------+----------+
2 rows in set (0.00 sec)
mysql&gt; select job,count(*) from employee where comm is null and deptno &lt;&gt; 10
group by job;
+----------+----------+
| job | count(*) |
+----------+----------+
| Manager | 3 |
| Analyst | 1 |
| Salesman | 1 |
| Clerk | 2 |

+----------+----------+
4 rows in set (0.00 sec)
mysql&gt; select ucase(ename),lcase(job) from employee where length(ename)&gt;5;
+--------------+------------+
| ucase(ename) | lcase(job) |
+--------------+------------+
| MARTIN | salesman |
| TURNER | salesman |
| MILLER | clerk |
+--------------+------------+
3 rows in set (0.02 sec)
mysql&gt; select month(&#39;2003-11-05&#39;),year(&#39;2003-11-
05&#39;),day(&#39;2003-11-05&#39;),monthname(&#39;2003-11-05&#39;);
+---------------------+--------------------+-------------------+-------------------------+
| month(&#39;2003-11-05&#39;) | year(&#39;2003-11-05&#39;) | day(&#39;2003-11-
05&#39;) | monthname(&#39;2003-11-05&#39;) |
+---------------------+--------------------+-------------------+-------------------------+
| 11 | 2003 | 5 | November |
+---------------------+--------------------+-------------------+-------------------------+
1 row in set (0.01 sec)
mysql&gt; select monthname(curdate()),dayname(curdate());
+----------------------+--------------------+
| monthname(curdate()) | dayname(curdate()) |
+----------------------+--------------------+
| April | Sunday |
+----------------------+--------------------+
1 row in set (0.00 sec)
mysql&gt; select curdate(),sysdate();
+------------+---------------------+
| curdate() | sysdate() |
+------------+---------------------+
| 2021-04-25 | 2021-04-25 10:17:33 |
+------------+---------------------+
1 row in set (0.00 sec)
mysql&gt; select round(sal,2) from employee where month(hiredate)=11;
+--------------+
| round(sal,2) |
+--------------+
| 5000 |
+--------------+
1 row in set (0.01 sec)
mysql&gt; select ename,length(ename) from employee where
dayname(hiredate)=&#39;tuesday&#39;;
+--------+---------------+
| ename | length(ename) |
+--------+---------------+
| Clark | 5 |
| King | 4 |
| Turner | 6 |
+--------+---------------+
3 rows in set (0.01 sec)
mysql&gt; select round(2345.7899,2),round(2345.7899,-3),round(2345.7899,0);
+--------------------+---------------------+--------------------+
| round(2345.7899,2) | round(2345.7899,-3) | round(2345.7899,0) |
+--------------------+---------------------+--------------------+
| 2345.79 | 2000 | 2346 |
+--------------------+---------------------+--------------------+
1 row in set (0.01 sec)
mysql&gt; select mod(5,4),power(5,4);
+----------+------------+
| mod(5,4) | power(5,4) |
+----------+------------+
| 1 | 625 |
+----------+------------+
1 row in set (0.00 sec)

mysql&gt; select sysdate(),sleep(10),sysdate();

+---------------------+-----------+---------------------+
| sysdate() | sleep(10) | sysdate() |
+---------------------+-----------+---------------------+
| 2021-04-26 11:46:52 | 0 | 2021-04-26 11:47:02 |
+---------------------+-----------+---------------------+
1 row in set (10.02 sec)

mysql&gt; select now(),sleep(10);

+---------------------+-----------+
| now() | sleep(10) |
+---------------------+-----------+
| 2021-04-26 11:47:25 | 0 |
+---------------------+-----------+
1 row in set (10.01 sec)
mysql&gt; use xavier;
Database changed
mysql&gt; select now(),sleep(20),now();
+---------------------+-----------+---------------------+
| now() | sleep(20) | now() |
+---------------------+-----------+---------------------+
| 2021-04-26 12:14:24 | 0 | 2021-04-26 12:14:24 |
+---------------------+-----------+---------------------+
1 row in set (20.01 sec)
mysql&gt; select truncate(75537.0001,-5),truncate(75537.0001,5),truncate(75537.0001,0);
+-------------------------+------------------------+------------------------+
| truncate(75537.0001,-5) | truncate(75537.0001,5) | truncate(75537.0001,0) |
+-------------------------+------------------------+------------------------+
| 0 | 75537.0001 | 75537 |
+-------------------------+------------------------+------------------------+
1 row in set (0.00 sec)
mysql&gt; select truncate(25537.2366,2),round(25537.2366,2);
+------------------------+---------------------+
| truncate(25537.2366,2) | round(25537.2366,2) |
+------------------------+---------------------+
| 25537.23 | 25537.24 |
+------------------------+---------------------+
1 row in set (0.00 sec)
mysql&gt; select rtrim(&#39; cl ass&#39;),ltrim(&#39; class&#39;);
+----------------------+-------------------+
| rtrim(&#39; cl ass&#39;) | ltrim(&#39; class&#39;) |
+----------------------+-------------------+
| cl ass | class |
+----------------------+-------------------+
1 row in set (0.00 sec)
mysql&gt; select substr(&#39;class xii ip&#39;,3,2),instr(&#39;class xii
ip&#39;,&#39;xii&#39;);
+----------------------------+-----------------------------+
| substr(&#39;class xii ip&#39;,3,2) | instr(&#39;class xii ip&#39;,&#39;xii&#39;) |
+----------------------------+-----------------------------+
| as | 7 |
+----------------------------+-----------------------------+
1 row in set (0.01 sec)
mysql&gt; select substr(&#39;class xii ip&#39;,3,2),instr(&#39;class xii
ip&#39;,&#39;xii&#39;);
+----------------------------+-----------------------------+
| substr(&#39;class xii ip&#39;,3,2) | instr(&#39;class xii ip&#39;,&#39;xii&#39;) |
+----------------------------+-----------------------------+
| as | 7 |
+----------------------------+-----------------------------+
1 row in set (0.01 sec)
mysql&gt; select left(&#39;class xii ip&#39;,3),right(&#39;class xii
ip&#39;,3),mid(&#39;class xii ip&#39;,3);
+------------------------+-------------------------+-----------------------+
| left(&#39;class xii ip&#39;,3) | right(&#39;class xii ip&#39;,3) | mid(&#39;class xii
ip&#39;,3) |
+------------------------+-------------------------+-----------------------+
| cla | ip | ass xii ip |
+------------------------+-------------------------+-----------------------+
1 row in set (0.00 sec)
mysql&gt;