SQL as Alias usage method

SQL as Alias usage method

MySQL Tutorials > CREATE TABLE Topic (
-> topicid SMALLINT not NULL auto_increment

PRIMARY KEY,
-> Name VARCHAR is not NULL,
-> instock SMALLINT UNSIGNED not NULL,
-> onorder SMALLINT UNSIGNED not NULL,
-> Reserved SMALLINT UNSIGNED not NULL,
-> Department ENUM (' Classical ', ' popular ') not

Null
-> Category VARCHAR () is not NULL,
-> rowupdate TIMESTAMP not NULL
->);
Query OK, 0 rows affected (0.02 sec)

Mysql>
Mysql>
Mysql> INSERT into Topic (Name, Instock, Onorder,

Reserved, Department, Category) VALUES
-> (' Java ', 10, 5,

3, ' popular ', ' Rock '),
-> (' JavaScript ', 10, 5,

3, ' classical ', ' Opera ',
-> (' C Sharp ', 17, 4,

1, ' popular ', ' Jazz ',
-> (' C ', 9, 4,

2, ' classical ', ' dance '),
-> (' C + + ', 24, 2,

5, ' classical ', ' General ',
-> (' Perl ', 16, 6,

8, ' classical ', ' vocal '),
-> (' Python ', 2, 25,

6, ' popular ', ' Blues '),
-> (' Php ', 32, 3,

Ten, ' popular ', ' Jazz ',
-> (' asp.net ', 12, 15,

' Popular ', ' Country '),
-> (' vb.net ', 5, 20,

Ten, ' popular ', ' New age '),
-> (' Vc.net ', 24, 11,

, ' popular ', ' New age '),
-> (' UML ', 42, 17,

' Classical ', ' General ',
-> (' www.java2s.com ', 25, 44,

, ' classical ', ' dance ',
-> (' Oracle ', 32, 15,

, ' classical ', ' General ',
-> (' Pl/sql ', 20, 10,

5, ' classical ', ' Opera ',
-> (' SQL Server ', 23, 12,

8, ' classical ', ' general ');
Query OK, Rows Affected (0.00 sec)
Records:16 duplicates:0 warnings:0

Mysql>
Mysql> select * from Topic;
+---------+----------------+---------+---------+---------

-+------------+----------+---------------------+
| TopicID | Name | Instock | Onorder | Reserved

| Department | Category | Rowupdate |
+---------+----------------+---------+---------+---------

-+------------+----------+---------------------+
| 1 |      Java |       10 |        5 | 3

| Popular | Rock | 2007-07-23 19:09:48 |
| 2 |      JavaScript |       10 |        5 | 3

| Classical | Opera | 2007-07-23 19:09:48 |
| 3 |      C Sharp |       17 |        4 | 1

| Popular | Jazz | 2007-07-23 19:09:48 |
| 4 |       C |       9 |        4 | 2

| Classical | Dance | 2007-07-23 19:09:48 |
| 5 |      C + + |       24 |        2 | 5

| Classical | General | 2007-07-23 19:09:48 |
| 6 |      Perl |       16 |        6 | 8

| Classical | Vocal | 2007-07-23 19:09:48 |
| 7 |       Python |      2 |        25 | 6

| Popular | Blues | 2007-07-23 19:09:48 |
| 8 |      PHP |       32 |       3 | 10

| Popular | Jazz | 2007-07-23 19:09:48 |
| 9 |      asp.net |      12 |       15 | 13

| Popular | Country | 2007-07-23 19:09:48 |
| 10 |       vb.net |      5 |       20 | 10

| Popular | New Age | 2007-07-23 19:09:48 |
| 11 |      vc.net |      24 |       11 | 14

| Popular | New Age | 2007-07-23 19:09:48 |
| 12 |      UML |      42 |       17 | 17

| Classical | General | 2007-07-23 19:09:48 |
| 13 |      www.java2s.com |      25 |       44 | 28

| Classical | Dance | 2007-07-23 19:09:48 |
| 14 |      Oracle |      32 |       15 | 12

| Classical | General | 2007-07-23 19:09:48 |
| 15 |      Pl/sql |      20 |        10 | 5

| Classical | Opera | 2007-07-23 19:09:48 |
| 16 |      SQL Server |      23 |        12 | 8

| Classical | General | 2007-07-23 19:09:48 |
+---------+----------------+---------+---------+---------

-+------------+----------+---------------------+
Rows in Set (0.00 sec)

Mysql>
Mysql> SELECT Name, instock+onorder-reserved as Available
-> from Topic
-> WHERE Name like '%bach% '
-> ORDER by Name;
Empty Set (0.00 sec)

Convert a long statement to a short statement

Mysql> SELECT first_name, Start_date, Curdate (),
-> (Curdate ())-year (start_date))-(right

(Curdate (), 5) <right (start_date,5))
-> as Yearserved
-> from employee order by yearserved;
+------------+------------+------------+------------+
| first_name | start_date | Curdate () | yearserved |
+------------+------------+------------+------------+
| Jason | 1996-07-25 |         2007-07-22 | 10 |
| James | 1996-09-17 |         2007-07-22 | 10 |
| David | 1990-12-31 |         2007-07-22 | 16 |
| Linda | 1987-07-30 |         2007-07-22 | 19 |
| Robert | 1984-01-15 |         2007-07-22 | 23 |
| Celia | 1982-10-24 |         2007-07-22 | 24 |
| James | 1978-12-12 |         2007-07-22 | 28 |
| Alison | 1976-03-21 |         2007-07-22 | 31 |
+------------+------------+------------+------------+

In a federated join query, you often use the alias's as below to see an instance.

Mysql> SELECT o.id from Employee as O, job as C
-> WHERE c.id = o.id;