SELECT Statement in SQL

SQL(Structured Query Language), still it is mostly used as a language of database, handling every and each resources from a database using sql. In this episode I am here for discussion about sql in detail begining to advance level.

And this episode will continue till stored function, stored procedure as well as trigger in sql.

To day our first episode about SQL(is a part of data manipulation language)

SELECT column_list 
FROM tbl_exp

above statement in practical like as below

>> select id,name,position,manager
     from employee;

Above statement results in practical are as follows

  
ID
  
Name
  
Position
  
Managerid


  
1
  
Jhon Doe
  
Project Manager
  
0


  
2
  
Michel
  
Lead Developer
  
1


  
3
  
Jhon Arbin
  
Sr. Software Eng.
  
2


  
4
  
Scott
  
Sr. Software Eng.
  
2


  
3
  
Tiger
  
Lead Developer
  
1

If we want to find out top employee from employee table(In SQL it is called where clause restrict), that means a table data will restrict using WHERE clause, than our SQL expression will like below,

SELECT column_list
FROM tbl_exp
WHERE clause

Above statement in practical are as follows

>> select id,name,position,managerid
     from employee
     where managerid = 0;

After execute above statement our restricted output will like as below (because we are requesting for top employee)

  
ID
  
Name
  
Position
  
Managerid


  
1
  
Jhon Doe
  
Project Manager
  
0

If you want to find out how many employee in each position? (in this statement we are used aggregate function which will be describing detail in aggregate function episode) for this requirement our sql statement will be

>> select id,position,count(*) as 'No.of Emp'
     from employee
     where managerid != 0
     group by position;

Your desired output are as follows

  
ID
  
Position
  
No.Of.Emp



  
2
  
Lead Developer
  
2


  
3
  
Sr. Software Eng.
  
2

You can also find out all employees and their manager using SELF JOIN(Self join, a table will join himself with two associated column of it’s own) like below.

SELECT e.id,e.name,e.position,m.name
FROM employee e
INNER JOIN employee m
on e.id = m.managerid

Above statement will output like

  
ID
  
Name
  
Position
  
Manager



  
2
  
Michel
  
Lead Developer
  
Jhon Doe


  
3
  
Jhon Arbin
  
Sr. Software Eng.
  
Michel


  
4
  
Scott
  
Sr. Software Eng.
  
Michel


  
3
  
Tiger
  
Lead Developer
  
Jhon Doe

Now we know how to restrict data using where clause? we have seen group clause in select statement, we have seen also AGGREGATE FUNCTIONS like as count() in select statement.
Also learn how to SELF JOIN and using that get our manager as of hierarchy.