SELECT
SELECT statement returns a result set of
records from one or more tables.
The select statement has optional
clauses:
• WHERE specifies
which rows to retrieve
• GROUP BY groups
rows sharing a property so that an aggregate function can be
applied to each group having
group.
• HAVING selects
among the groups defined by the GROUP BY clause.
• ORDER BY
specifies an order in which to return the rows.
Syntax:
SELECT<attribute list>
FROM<table list>
WHERE<condition>
Where
• Attribute list
is a list of attribute name whose values to be retrieved by the query.
• Table list is a
list of table name required to process query.
• Condition is a
Boolean expression that identifies the tuples to be retrieved by query.
CONCLUSION:
Implemented
various DML commands and also executed simple SQL queries
DISPLAY ALL THE EMPLOYEENUMBERS
SQL> select
empno from emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
DISPLAY ALL THE EMPLOYEENUMBERS AND THEIR
NAMES
SQL> select
empno,ename from emp;
EMPNO ENAME
----------
----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
----------
----------
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
SQL> select *
from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN
7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
DISPLAY ALL NAMES OF THE EMPLOYEE WHOSE
SALARY ABOVE 1500
SQL> select
ename from emp where sal>1500;
ENAME
----------
ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
FORD
7 rows selected.
DISPLAY ALL NAMES OF THE EMPLOYEE WHO IS
WORKING AS CLERK AND THEIR SALARY LESS THAN 3000
SQL> select
empno,ename from emp where job='CLERK' and sal<3000;
EMPNO ENAME
----------
----------
7369 SMITH
7876 ADAMS
7900 JAMES
7934 MILLER
DISPLAY ALL NAMES OF THE EMPLOYEE IN
ASCENDING ORDER
SQL> select
ename from emp order by ename;
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
ENAME
----------
SMITH
TURNER
WARD
14 rows selected.
DISPLAY ALL NAMES OF THE EMPLOYEE IN
DESCENDING ORDER
SQL> select
ename from emp order by ename desc;
ENAME
----------
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK
ENAME
----------
BLAKE
ALLEN
ADAMS
14 rows selected.
DISPLAY ALL NAMES,
SALARIES(ASCENDINGORDER) OF THE
EMPLOYEES WHO ARE WORKING AS CLERK .
SQL> select
ename,sal from emp where job='CLERK' order by sal;
ENAME SAL
----------
----------
SMITH 800
JAMES 950
ADAMS 1100
MILLER 1300
DISPLAY HOW MANY OF THEM WORKING IN EACH
DEPARTMENT.
SQL> select
deptno,count(deptno) from emp group by deptno;
DEPTNO COUNT(DEPTNO)
----------
-------------
30 6
20 5
10 3
DISPLAY JOB GROUP OF THE EMPLOYEES WHOSE SUM
OF SALARIES IS GREATER THAN 1000.
SQL> select
job from emp group by job having sum(sal) >1000;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
DISPLAY JOB GROUP OF THE EMPLOYEES WHO ARE
WORKING ANY DEPARTMENT MORE THAN 4 EMPLOYEE MEMBERS.
SQL> select
job from emp group by job having count(deptno)> 4;
no rows selected
DISPLAY JOB GROUP OF THE EMPLOYEES WHO ARE
WORKING ANY DEPARTMENT MORE THAN 3 EMPLOYEE MEMBERS.
SQL> select
job from emp group by job having count(deptno)> 3;
JOB
---------
CLERK
SALESMAN
FIND
THE EMPLOYEES WHO ARE DOING SAME JOB.
SQL> select
job,count(job) from emp group by job;
JOB COUNT(JOB)
---------
----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME START WITH ‘A’.
SQL> select
ename from emp where ename like 'A%';
ENAME
----------
ALLEN
ADAMS
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME NOT START WITH ‘A’.
SQL> select
ename from emp where ename not like 'A%';
ENAME
----------
SMITH
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
JAMES
FORD
ENAME
----------
MILLER
12 rows selected.
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME ENDS WITH ‘A’.
SQL> select
ename from emp where ename like '%N';
ENAME
----------
ALLEN
MARTIN
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME NOT ENDS WITH ‘A’.
SQL> select
ename from emp where ename not like
'%N';
ENAME
----------
SMITH
WARD
JONES
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
ENAME
----------
MILLER
12 rows selected.
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME STARS WITH ‘A’ AND ENDS
WITH ‘S’.
SQL> select
ename from emp where ename like 'A%S';
ENAME
----------
ADAMS
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME CONTAINS FOUR LETTERS.
SQL> select
ename from emp where ename like '____';
ENAME
----------
WARD
KING
FORD
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME CONTAINS FOUR LETTERS.
SQL> select
ename from emp where length(ename)=4;
ENAME
----------
WARD
KING
FORD
DISPLAY NAMES OF THE EMPLOYEES WHOSE NAME CONTAINS FIVE LETTERS WITH FIRST LETTER S AND THIRD LETTER O AND
FIFTH LETTER T .
SQL> select
ename from emp where ename like 'S_O_T';
ENAME
----------
SCOTT
DISPLAY JOB OF THE EMPLOYEES WITHOUT ALLOWING DUPLICATES.
SQL> select
distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST