SQL: Exploring SELECT statement

 

SELECT statement is used to select data from one or more tables.

 

For example, let’s create an employee table and insert some data and get it via SELECT statement.

 

Create table

CREATE TABLE employee (

    emp_id int,

    first_name varchar(255) NOT NULL,

    last_name varchar(255),

    PRIMARY KEY (emp_id)

);

 

Insert some data into employee table

INSERT INTO employee(emp_id, first_name, last_name) VALUES (1, 'krishna', 'gurram');

INSERT INTO employee(emp_id, first_name, last_name) VALUES (2, 'gopi', 'battu');

INSERT INTO employee(emp_id, first_name, last_name) VALUES (3, 'joel', 'chelli');

mysql> CREATE TABLE employee (
    ->     emp_id int,
    ->     first_name varchar(255) NOT NULL,
    ->     last_name varchar(255),
    ->     PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO employee(emp_id, first_name, last_name) VALUES (1, 'krishna', 'gurram');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(emp_id, first_name, last_name) VALUES (2, 'gopi', 'battu');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(emp_id, first_name, last_name) VALUES (3, 'joel', 'chelli');
Query OK, 1 row affected (0.00 sec)

How to get all the records from a table?

Syntax

SELECT * FROM {table_name}

 

Example

SELECT * FROM employee;

mysql> SELECT * FROM employee;
+--------+------------+-----------+
| emp_id | first_name | last_name |
+--------+------------+-----------+
|      1 | krishna    | gurram    |
|      2 | gopi       | battu     |
|      3 | joel       | chelli    |
+--------+------------+-----------+
3 rows in set (0.00 sec)

How to get an employee with id 2?

Syntax

SELECT * FROM {table_name} WHERE {condition}

 

Example

SELECT * FROM employee WHERE emp_id=2;

mysql> SELECT * FROM employee WHERE emp_id=2;
+--------+------------+-----------+
| emp_id | first_name | last_name |
+--------+------------+-----------+
|      2 | gopi       | battu     |
+--------+------------+-----------+
1 row in set (0.00 sec)

How to get specific columns from a table?

Syntax

SELECT column_name1, columne_name2 FROM {table_name}

SELECT column_name1, columne_name2 FROM {table_name} WHERE {condition}

 

Example

SELECT emp_id, last_name FROM employee;

SELECT emp_id, last_name FROM employee WHERE emp_id=2;

mysql> SELECT emp_id, last_name FROM employee;
+--------+-----------+
| emp_id | last_name |
+--------+-----------+
|      1 | gurram    |
|      2 | battu     |
|      3 | chelli    |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> 
mysql> SELECT emp_id, last_name FROM employee WHERE emp_id=2;
+--------+-----------+
| emp_id | last_name |
+--------+-----------+
|      2 | battu     |
+--------+-----------+
1 row in set (0.00 sec)

You can even use built-in functions in SELECT clause

For example, we can use UPPER function to convert employee name to uppercase.

mysql> SELECT emp_id, first_name, UPPER(last_name) FROM employee;
+--------+------------+------------------+
| emp_id | first_name | UPPER(last_name) |
+--------+------------+------------------+
|      1 | krishna    | GURRAM           |
|      2 | gopi       | BATTU            |
|      3 | joel       | CHELLI           |
+--------+------------+------------------+
3 rows in set (0.00 sec)

 

 

 

Previous                                                    Next                                                    Home