[sql] 3/21

mysql> select name,birthdata from employees where departmentid=10;
ERROR 1054: Unknown column 'birthdata' in 'field list'
mysql> select name,birthday from employees where departmentid=10;
ERROR 1054: Unknown column 'birthday' in 'field list'
mysql> select name,birthdate,left(birthdate,4),mid(birthdate,6,2),right from employees where departmentid=10;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ' from employees where departmentid=10' at line 1
mysql> select name,birthdate,left(birthdate,4),mid(birthdate,6,2),right as day from employees where departmentid=10;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ' as day from employees where departmentid=10' at li
mysql> select name,birthdate,left(birthdate,4),mid(birthdate,6,2),right as day from employees where departmentid=10;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ' as day from employees where departmentid=10' at l
mysql> select name,birthdate,left(birthdate,4) as year , mid (birthdate,6,2) as month,right(birtthdate,2) as day from employees where departemntid=10;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(birthdate,6,2) as month,right(birtthdate,2) as day from employ
mysql> select name,birthdate,left(birthdate,4) as year , mid(birthdate,6,2) as month,right(birtthdate,2) as day from employees where departemntid=10;
ERROR 1054: Unknown column 'birtthdate' in 'field list'
mysql> select name,birthdate,left(birthdate,4) as year , mid(birthdate,6,2) as month,right(birthdate,2) as day from employees where departemntid=10;
ERROR 1054: Unknown column 'departemntid' in 'where clause'
mysql> select name,birthdate,left(birthdate,4) as year , mid(birthdate,6,2) as month,right(birthdate,2) as day from employees where departementid=10;
ERROR 1054: Unknown column 'departementid' in 'where clause'
mysql> select name,birthdate,left(birthdate,4) as year , mid(birthdate,6,2) as month,right(birthdate,2) as day from employees where departmentid=10;
+--------+------------+------+-------+------+
| name   | birthdate  | year | month | day  |
+--------+------------+------+-------+------+
| 何力宇 | 1961-12-06 | 1961 | 12    | 06   |
| 王明亮 | 1968-12-14 | 1968 | 12    | 14   |
| 顏子福 | 1965-12-09 | 1965 | 12    | 09   |
| 張子強 | 1970-01-12 | 1970 | 01    | 12   |
+--------+------------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select EmployeeID,name,engname,concat(name+'-'+engname) as name_eng from employees where departmentid=10;
+------------+--------+---------+----------+
| EmployeeID | name   | engname | name_eng |
+------------+--------+---------+----------+
|       1013 | 何力宇 | David   | 0        |
|       1014 | 王明亮 | John    | 0        |
|       1015 | 顏子福 | Luke    | 0        |
|       1016 | 張子強 | John    | 0        |
+------------+--------+---------+----------+
4 rows in set (0.02 sec)

mysql> select EmployeeID,name,engname,concat(name,'-',engname) as name_eng from employees where departmentid=10;
+------------+--------+---------+--------------+
| EmployeeID | name   | engname | name_eng     |
+------------+--------+---------+--------------+
|       1013 | 何力宇 | David   | 何力宇-David |
|       1014 | 王明亮 | John    | 王明亮-John  |
|       1015 | 顏子福 | Luke    | 顏子福-Luke  |
|       1016 | 張子強 | John    | 張子強-John  |
+------------+--------+---------+--------------+
4 rows in set (0.00 sec)

mysql> select count(name),min(salary),max(salary),avg(salary),stddev(salary) from employees where departmentid=10;
+-------------+-------------+-------------+-------------+-----------------+
| count(name) | min(salary) | max(salary) | avg(salary) | stddev(salary)  |
+-------------+-------------+-------------+-------------+-----------------+
|           4 |      400000 |      670000 |      495000 | 103561.57588604 |
+-------------+-------------+-------------+-------------+-----------------+
1 row in set (0.00 sec)

mysql> select count(name),min(salary),max(salary),count(salary),avg(salary),stddev(salary) from employees where departmentid=10;
+-------------+-------------+-------------+---------------+-------------+-----------------+
| count(name) | min(salary) | max(salary) | count(salary) | avg(salary) | stddev(salary)  |
+-------------+-------------+-------------+---------------+-------------+-----------------+
|           4 |      400000 |      670000 |             4 |      495000 | 103561.57588604 |
+-------------+-------------+-------------+---------------+-------------+-----------------+
1 row in set (0.00 sec)

mysql> select count(name),min(salary),max(salary),sun(salary),avg(salary),stddev(salary) from employees where departmentid=10;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(salary),avg(salary),stddev(salary) from employees where depart
mysql> select count(name),min(salary),max(salary),sum(salary),avg(salary),stddev(salary) from employees where departmentid=10;
+-------------+-------------+-------------+-------------+-------------+-----------------+
| count(name) | min(salary) | max(salary) | sum(salary) | avg(salary) | stddev(salary)  |
+-------------+-------------+-------------+-------------+-------------+-----------------+
|           4 |      400000 |      670000 |     1980000 |      495000 | 103561.57588604 |
+-------------+-------------+-------------+-------------+-------------+-----------------+
1 row in set (0.00 sec)

mysql> select count(*) 員工數,min(salary),max(salary),sum(salary),avg(salary),stddev(salary) from employees where departmentid=10;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '員工數,min(salary),max(salary),sum(salary),avg(salary),stddev(s
mysql> select count(*) as'員工數',min(salary) as '最低薪資',max(salary) as '最高薪資',sum(salary) as '總計',avg(salary) as '平均薪資',stddev(salary) as '標準差' from employees where departmentid=10;
+--------+----------+----------+---------+----------+-----------------+
| 員工數 | 最低薪資 | 最高薪資 | 總計    | 平均薪資 | 標準差          |
+--------+----------+----------+---------+----------+-----------------+
|      4 |   400000 |   670000 | 1980000 |   495000 | 103561.57588604 |
+--------+----------+----------+---------+----------+-----------------+
1 row in set (0.00 sec)

mysql> select * from department;
ERROR 1146: Table 'my_database.department' doesn't exist
mysql> select * from departments;
+--------------+------------+----------+
| DepartmentID | Name       | Location |
+--------------+------------+----------+
|           10 | 市場行銷部 | 台北     |
|           20 | 業務部     | 台北     |
|           30 | 管理部     | 台北     |
|           40 | 研發部     | 台中     |
|           50 | 生產製造部 | 台中縣   |
+--------------+------------+----------+
5 rows in set (0.00 sec)

mysql> select Commission from employees
    -> ;
+------------+
| Commission |
+------------+
|     120000 |
|       NULL |
|     140000 |
|     130000 |
|     180000 |
|     120000 |
|     180000 |
|       NULL |
|     130000 |
|       NULL |
|       NULL |
|       NULL |
|       NULL |
|       NULL |
|       NULL |
|       NULL |
+------------+
16 rows in set (0.00 sec)

mysql> select SSN * from employees;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'from employees' at line 1
mysql> select SSN from employees;
+------------+
| SSN        |
+------------+
| A221112333 |
| B121112233 |
| B221132237 |
| C221112389 |
| D121342354 |
| D121443301 |
| E121142302 |
| E221432357 |
| P221114387 |
| P121322345 |
| F221432312 |
| F120056332 |
| G121232312 |
| H121002230 |
| K121652456 |
|            |
+------------+
16 rows in set (0.00 sec)

mysql> select TitleOfCourtesy from employees;
+-----------------+
| TitleOfCourtesy |
+-----------------+
| 小姐            |
| 先生            |
| 小姐            |
| 小姐            |
| 先生            |
| 先生            |
| 先生            |
| 小姐            |
| 小姐            |
| 先生            |
| 小姐            |
| 先生            |
| 先生            |
| 先生            |
| 先生            |
|                 |
+-----------------+
16 rows in set (0.00 sec)

mysql> select departmentid,count(*) as'員工數',min(salary) as '最低薪資',max(salary) as '最高薪資',sum(salary) as '總計',avg(salary) as '平均薪資',stddev(salary) as '標準差' from employees where group by departmentid;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by departmentid' at line 1
mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低薪資',max(salary) as '最高薪資',sum(salary) as '總計',avg(salary) as '平均薪資',stddev(salary) as '標準差' from employees where group by departmentid;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by departmentid' at line 1
mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低薪資',max(salary) as '最高薪資',sum(salary) as '總計',avg(salary) as '平均薪資',stddev(sa
    -> ;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低薪資',max(salary) as '最高薪資',sum(salary) as '總計',avg(salary) as '平均薪資',stddev(salary) as '標準差' from employees group by departmentid;
+------+--------+----------+----------+---------+-----------------+-----------------+
| 編號 | 員工數 | 最低薪資 | 最高薪資 | 總計    | 平均薪資        | 標準差          |
+------+--------+----------+----------+---------+-----------------+-----------------+
|   10 |      4 |   400000 |   670000 | 1980000 |          495000 | 103561.57588604 |
|   20 |      9 |   420000 |  1400000 | 5860000 | 651111.11111111 | 272944.61971634 |
|   30 |      3 |   380000 |   680000 | 1510000 | 503333.33333333 | 128149.21857827 |
+------+--------+----------+----------+---------+-----------------+-----------------+
3 rows in set (0.00 sec)

mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低' max(salary) as '最高'sum(salary) as '總計',avg(salary) as '平均',stddev(salary) as '標準差' from employees group by departmentid;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'max(salary) as '最高'sum(salary) as '總計',avg(salary) as '平均
mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低' max(salary) as '最高' sum(salary) as '總計',avg(salary) as '平均',stddev(salary) as '標準差' from employees group by departmentid;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'max(salary) as '最高' sum(salary) as '總計',avg(salary) as '平?
mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低', max(salary) as '最高', sum(salary) as '總計', avg(salary) as '平均',stddev(salary) as '標準差' from employees group by departmentid;
+------+--------+--------+---------+---------+-----------------+-----------------+
| 編號 | 員工數 | 最低   | 最高    | 總計    | 平均            | 標準差          |
+------+--------+--------+---------+---------+-----------------+-----------------+
|   10 |      4 | 400000 |  670000 | 1980000 |          495000 | 103561.57588604 |
|   20 |      9 | 420000 | 1400000 | 5860000 | 651111.11111111 | 272944.61971634 |
|   30 |      3 | 380000 |  680000 | 1510000 | 503333.33333333 | 128149.21857827 |
+------+--------+--------+---------+---------+-----------------+-----------------+
3 rows in set (0.00 sec)

mysql> select departmentid as '編號',count(*) as'員工數',min(salary) as '最低', max(salary) as '最高', sum(salary) as '總計', avg(salary) as '平均',stddev(salary) as '標準差' from employees group by departmentid having count(*)>5 ;
+------+--------+--------+---------+---------+-----------------+-----------------+
| 編號 | 員工數 | 最低   | 最高    | 總計    | 平均            | 標準差          |
+------+--------+--------+---------+---------+-----------------+-----------------+
|   20 |      9 | 420000 | 1400000 | 5860000 | 651111.11111111 | 272944.61971634 |
+------+--------+--------+---------+---------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select Salary
    -> Commission;
ERROR 1054: Unknown column 'Salary' in 'field list'
mysql> select sum(salary),sum(Commission) from employees
    -> ;
+-------------+-----------------+
| sum(salary) | sum(Commission) |
+-------------+-----------------+
|     9350000 |         1000000 |
+-------------+-----------------+
1 row in set (0.00 sec)

mysql> select sum(salary,Commission) from employees;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Commission) from employees' at line 1
mysql> select name, salary+commission as '薪資總和' from employees;
+--------+----------+
| name   | 薪資總和 |
+--------+----------+
| 張瑾雯 |   660000 |
| 陳廣明 |     NULL |
| 趙飛燕 |   700000 |
| 林美麗 |   690000 |
| 劉天明 |   880000 |
| 黎國明 |   680000 |
| 郭國城 |   740000 |
| 蘇韻涵 |     NULL |
| 孟玉婷 |   690000 |
| 張文德 |     NULL |
| 文師宣 |     NULL |
| 賴俊良 |     NULL |
| 何力宇 |     NULL |
| 王明亮 |     NULL |
| 顏子福 |     NULL |
| 張子強 |     NULL |
+--------+----------+
16 rows in set (0.00 sec)

mysql> select name,salary,ifnull(commission,0)as comm, salary+ifnull(commission,0) as '薪資總和' from employees;
+--------+---------+--------+----------+
| name   | salary  | comm   | 薪資總和 |
+--------+---------+--------+----------+
| 張瑾雯 |  540000 | 120000 |   660000 |
| 陳廣明 | 1400000 |      0 |  1400000 |
| 趙飛燕 |  560000 | 140000 |   700000 |
| 林美麗 |  560000 | 130000 |   690000 |
| 劉天明 |  700000 | 180000 |   880000 |
| 黎國明 |  560000 | 120000 |   680000 |
| 郭國城 |  560000 | 180000 |   740000 |
| 蘇韻涵 |  420000 |      0 |   420000 |
| 孟玉婷 |  560000 | 130000 |   690000 |
| 張文德 |  680000 |      0 |   680000 |
| 文師宣 |  380000 |      0 |   380000 |
| 賴俊良 |  450000 |      0 |   450000 |
| 何力宇 |  670000 |      0 |   670000 |
| 王明亮 |  460000 |      0 |   460000 |
| 顏子福 |  450000 |      0 |   450000 |
| 張子強 |  400000 |      0 |   400000 |
+--------+---------+--------+----------+
16 rows in set (0.00 sec)

mysql> select salary,ifnull(commission,0)as comm, salary+ifnull(commission,0) as '薪資總和' from employees group by departmentid;
+--------+--------+----------+
| salary | comm   | 薪資總和 |
+--------+--------+----------+
| 670000 |      0 |   670000 |
| 540000 | 120000 |   660000 |
| 680000 |      0 |   680000 |
+--------+--------+----------+
3 rows in set (0.00 sec)

mysql> select departmentid,salary,ifnull(commission,0)as comm, salary+ifnull(commission,0) as '薪資總和' from employees group by departmentid;
+--------------+--------+--------+----------+
| departmentid | salary | comm   | 薪資總和 |
+--------------+--------+--------+----------+
|           10 | 670000 |      0 |   670000 |
|           20 | 540000 | 120000 |   660000 |
|           30 | 680000 |      0 |   680000 |
+--------------+--------+--------+----------+
3 rows in set (0.00 sec)

mysql> select departmentid,salary,ifnull(commission,0)as comm, salary+ifnull(commission,0) as '薪資總和' group by departmentid;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by departmentid' at line 1
mysql> select departmentid,sum(salary),sum(ifnull(commission,0))as comm, sum(salary+ifnull(commission,0)) as '薪資總和' from employees group by departmentid;
+--------------+-------------+---------+----------+
| departmentid | sum(salary) | comm    | 薪資總和 |
+--------------+-------------+---------+----------+
|           10 |     1980000 |       0 |  1980000 |
|           20 |     5860000 | 1000000 |  6860000 |
|           30 |     1510000 |       0 |  1510000 |
+--------------+-------------+---------+----------+
3 rows in set (0.00 sec)

mysql> select orders ,orderdetails from orders nation join orderdetails;
ERROR 1054: Unknown column 'orders' in 'field list'
mysql> select orders.orderid ,customerid,employeeid,orderdate from orders natural join orderdetails where order.orderid<=10250;
ERROR 1109: Unknown table 'order' in where clause
mysql> select orders.orderid ,customerid,employeeid,orderdate from orders natural join orderdetails where orders.orderid<=10250;
+---------+------------+------------+------------+
| orderid | customerid | employeeid | orderdate  |
+---------+------------+------------+------------+
|   10248 | VINET      |       1005 | 1996-07-04 |
|   10248 | VINET      |       1005 | 1996-07-04 |
|   10249 | TOMSP      |       1006 | 1996-07-05 |
|   10249 | TOMSP      |       1006 | 1996-07-05 |
|   10250 | HANAR      |       1004 | 1996-07-08 |
|   10250 | HANAR      |       1004 | 1996-07-08 |
|   10250 | HANAR      |       1004 | 1996-07-08 |
+---------+------------+------------+------------+
7 rows in set (0.00 sec)