SQL as Alias usage

SQL as Alias usage

Mysql tutorial> create table Topic (
-> TopicID smallint not null AUTO_INCREMENT

Primary key,
-> Name VARCHAR (50) 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 (20) 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,

10, 'popular ', 'jazz '),
-> ('Asp. net', 12, 15,

13, 'popular ', 'country '),
-> ('Vb. net', 5, 20,

10, 'popular ', 'new age '),
-> ('Vc. net', 24, 11,

14, 'popular ', 'new age '),
-> ('Uml ', 42, 17,

17, 'classical ', 'General '),
-> ('Www .java2s.com ', 25, 44,

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

12, 'classical ', 'General '),
-> ('Pl/SQL ', 20, 10,

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

8, 'classical ', 'General ');
Query OK, 16 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 | 19:09:48 |
| 2 | JavaScript | 10 | 5 | 3

| Classical | Opera | 19:09:48 |
| 3 | C Sharp | 17 | 4 | 1

| Popular | Jazz | 19:09:48 |
| 4 | C | 9 | 4 | 2

| Classical | Dance | 19:09:48 |
| 5 | C ++ | 24 | 2 | 5

| Classical | General | 19:09:48 |
| 6 | Perl | 16 | 6 | 8

| Classical | Vocal | 19:09:48 |
| 7 | Python | 2 | 25 | 6

| Popular | Blues | 19:09:48 |
| 8 | Php | 32 | 3 | 10

| Popular | Jazz | 19:09:48 |
| 9 | ASP.net | 12 | 15 | 13

| Popular | Country | 19:09:48 |
| 10 | VB.net | 5 | 20 | 10

| Popular | New Age | 19:09:48 |
| 11 | VC.net | 24 | 11 | 14

| Popular | New Age | 19:09:48 |
| 12 | UML | 42 | 17 | 17

| Classical | General | 19:09:48 |
| 13 | www.java2s.com | 25 | 44 | 28

| Classical | Dance | 19:09:48 |
| 14 | Oracle | 32 | 15 | 12

| Classical | General | 19:09:48 |
| 15 | Pl/SQL | 20 | 10 | 5

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

| Classical | General | 19:09:48 |
+ --------- + ---------------- + ---------

-+ ------------ + ---------- + --------------------- +
16 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 long statements into short statements

Mysql> SELECT first_name, start_date, CURDATE (),
-> (YEAR (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 |
+ ------------ +

For join queries, the alias as is often used to view instances.

Mysql> select o. ID from Employee as o, job AS C
-> Where c. ID = O. ID;