SQL_SELECT_Statement

SELECT Statement

The most commonly used SQL command is SELECT statement. It is used to query the database and retrieve selected data that follow the conditions we want.
In simple words, we can say that the select statement used to query or retrieve data from a table in the database.

Let's see the syntax of select statement.

SELECT
       */list_of_cols [col-alias]/mathematical expr(s)/literals/psuedocols/functions
FROM
       <table 1>[table1_alias][table2 table2_alias
     , .......                        ]
WHERE
       <condition(s)>
GROUPBY col1[
     , col2
     , col3
     , ...........]]
HAVING
       <condition(s)>
ORDERBY col1[
     , col2
     , col3
     , ......][ASC/DESC]
;

POINTS TO BE REMEMBERED TO WORK WITH

SELECT STATEMENT:

  • Select statement does not allow selection and projection simultaneously.
  • Select statement should be terminated by semicolon (;)
  • A query can be written at a single line or at multiple lines, if it is written at multiple lines, readability of query is enhanced.
  • It is a standard rule to be followed by writing keywords, cmds, pseudo cols, functions in capital letters and user related terms in small letters.
  • Select statement always gets executed from left to right.
  • Select statement always displays the data in read only format.

Different  styles of writing a Select Statement

01
SELECT Statement
02
SELECT UNIQUE
03
SELECT DISTINCT
04
SELECT COUNT
05
SELECT TOP
06
SELECT FIRST
07
SELECT LAST
08
SELECT RANDOM
09
SELECT AS
10
SELECT IN
11
SELECT Multiple
12
SELECT DATE
13
SELECT SUM
14
SELECT NULL


 

SELECT Statement


select *
from emp;





















select   EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
DEPTNO
from emp; 


SELECT UNIQUE


select unique deptno
from emp;



 

 

SELECT DISTINCT


select distinct deptno
from emp;


SELECT COUNT


select count(ename) Employee_count
from emp;





SELECT TOP


select *
from (
      SELECT *
      FROM emp
      order by sal desc
      )
where rownum <= 2



SELECT FIRST


SELECT *
FROM emp
where rownum <= 1




 

SELECT LAST


select *
from (
                SELECT *
                FROM emp
                order by sal
      )
where rownum = 1;

SELECT RANDOM


SELECT *
 FROM  (
    SELECT *
    FROM emp 
                    ORDER BY dbms_random.value
)
WHERE rownum =1


SELECT AS


select   EMPNO as "EMPLOYEE NUMBER",
               ENAME as "EMPLOYEE_NAME",
               JOB as "JOB",
               MGR as MANAGER,
               HIREDATE HIRE_DATE,
               SAL SALARY,
               COMM COMMISSION,
               DEPTNO "DEPARTMENT NUMBER"
from emp;

SELECT IN


SELECT *
FROM emp
Where JOB in ('PRESIDENT','MANAGER','ANALYST');

SELECT Multiple Tables


Select e.*
From emp e,DEPT d
Where e.deptno = d.deptno;


SELECT DATE


Select *
From emp
where HIREDATE < '17-11-81';

SELECT SUM


Select sum(SAL)
From emp
where HIREDATE < '17-11-81' ;


SELECT NULL


Select *
From emp
where MGR is null;









Comments

Post a Comment