Till now we have created tables, entered data, now is the time to get the data in desired form from table. The data that is retrieved from table is called record set. The data can be pulled from table using “SELECT” statement.
SQL Select
The basic syntax:
SELECT [column names or * for all columns] from table_name;
|
Example 1:
Display all the data of the table.
To retrieve all columns you can mention all the column names or simply mention * for column name as below:
When you retrieve records using * then the sequence of the columns would be same as defined in table.
mysql> select * from departments; +------------------+----------------------------+ | DEPT_ID | DEPT_NAME | +------------------+----------------------------+ | 1 | Accounts | | 2 | HR | | 3 | Production | +------------------+----------------------------+ 3 rows in set (0.00 sec)
Example 2:
Display Department Name from the departments table.
There are just 2 columns in the DEPARTMENTS Table, hence in the example, will select only one column. The data in the result set would be in the column as per sequence mentioned in the SELECT statement.
mysql> select dept_name from departments; +-------------------+ | dept_name | +-------------------+ | Accounts | | HR | | Production | +-------------------+ 3 rows in set (0.03 sec)
Example 3:
Consider table with many columns, then you can mention desired column names like for example get Employee ID, Name and department id from employee table.
The table is defined as below:
+-----------------------------+ | Field | +-----------------------------+ | EMP_ID | | EMMP_NAME | | AGEG | | PHONE_NUM | | DEPT_ID | +-----------------------------+
mysql> SELECT EMP_ID, DEPT_ID, EMMP_NAME FROM EMPLOYEE; +-------------------+------------------+---------------------------+ | EMP_ID | DEPT_ID | EMMP_NAME | +-------------------+------------------+---------------------------+ | 1 | 2 | john | | 2 | 1 | Linda | | 3 | 3 | Max | | 4 | 3 | Will | | 5 | 3 | Michal | +-------------------+------------------+---------------------------+ 5 rows in set (0.02 sec)
While if you give * as option then below result set would be displayed.
mysql> SELECT * FROM EMPLOYEE; +------------+------------------------+----------+-------------------+---------------+ | EMP_ID | EMMP_NAME| AGEG | PHONE_NUM | DEPT_ID | +------------+------------------------+----------+-------------------+---------------+ | 1 | john | 35 | 100233023 | 2 | | 2 | Linda | 30 | 100234565 | 1 | | 3 | Max | 40 | 122222344 | 3 | | 4 | Will | 40 | 12323424 | 3 | | 5 | Michal | 45 | 12323434 | 3 | +------------+------------------------+----------+-------------------+---------------+ 5 rows in set (0.00 sec)
DISTINCT/UNIQUE command
As we have seen above that using SELECT command we can retrieve data from table. Now there can be duplicate data well in the result set. What If we need only unique or DISTNCT data? We can accomplish that by using DISTINCT command in mysql and sql server or UNIQUE in ORACLE.
Syntax:
SELECT DISTINCT column_name |
Example:
Consider transaction table as below. Get the distinct Vendor Names.
Transaction ID | Vendor_Id | Vendor_Name |
|
TOPCO | 01-01-2009 | ||
| FLYER | 02-03-2008 | ||
| TOPCO | 02-01-2009 | ||
| LENOVO | 03-04-2009 |
|
Output would be:
Vendor_Name |
TOPCO |
FLYER |
|