10 - SQL Retrieve records from TABLE

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
FROM table_name;
OR
SELECT UNIQUE column_name
FROM table_name;

Example:

Consider transaction table as below. Get the distinct Vendor Names.

Transaction ID

Vendor_Id


Vendor_Name


Transaction_Date

  1.  
  1.                         

TOPCO

01-01-2009
  1.  
  1.  
FLYER02-03-2008
  1.  
  1.  
TOPCO

02-01-2009

  1.  
  1.  
LENOVO

03-04-2009


SELECT DISTINCT Vendor_Name
FROM TRANSACTION;

 

Output would be:

   

Vendor_Name

TOPCO

FLYER


LENOVO

 

 

Like us on Facebook