Click Here to download DBMS Lab programs and also Web Technology - Its mainly for Master of Engineering
EX.No : SQL STATEMENTS
Basic Select Statement:
---------------------------
SYNTAX
-------
SELECT [DISTINCT] {*,column[alias]...} FROM table;
SELECT -> is a list of one or more columns.
DISTINCT -> supresses the duplicates.
* -> selects all columns.
Column -> selects the named column.
FROM table -> specifies the table containing the columns.
EMP TABLE
---------
1.a) Display all columns of the Emp table.
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
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
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
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
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1.b) Display all columns of Department table.
SQL> select * from dept;
DEPTNO DNAME LOC
------ ------ ----
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2. Display only deptno,loc columns from department table.
SQL> select deptno,loc from dept;
DEPTNO LOC
------ ----
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON
3. Display distinct values of deptno from department/employee table.
SQL> select distinct deptno from emp;
DEPTNO
------
10
20
30
USING ARITHMETIC OPERATORS:
---------------------------
4. Increment the salary of every employee by 300 and display their names and
original salary.
SQL> select ename,sal,sal+300 from emp;
ENAME SAL SAL+300
---------- ---------- ----------
SMITH 800 1100
ALLEN 1600 1900
WARD 1250 1550
JONES 2975 3275
MARTIN 1250 1550
BLAKE 2850 3150
CLARK 2450 2750
SCOTT 3000 3300
KING 5000 5300
TURNER 1500 1800
ADAMS 1100 1400
JAMES 950 1250
FORD 3000 3300
MILLER 1300 1600
14 rows selected.
Here salary increase is on arithmetic expression.
OPERATOR PRECEDENCE:
--------------------
*,/,+,- Multiplication and division followed by addition and subtraction.
Note : Parenthesis can form precedence.
5.a) Display the annual salary of all employees with additional sum of $100 added
to annual salary as bonus to them.
SQL> select ename, sal, sal*12+100 as "Annual Salary With Bonus" from emp;
ENAME SAL Annual Salary With Bonus
---------- ---------- ------------------------
SMITH 800 9700
ALLEN 1600 19300
WARD 1250 15100
JONES 2975 35800
MARTIN 1250 15100
BLAKE 2850 34300
CLARK 2450 29500
SCOTT 3000 36100
KING 5000 60100
TURNER 1500 18100
ADAMS 1100 13300
JAMES 950 11500
FORD 3000 36100
MILLER 1300 15700
14 rows selected.
5.b)Display the annual salary of all employees with additional sum of $100 added
to monthly salary as bonus to them.
SQL> select ename, sal,12*(sal+100) from emp;
ENAME SAL 12*(SAL+100)
---------- ---------- ------------
SMITH 800 10800
ALLEN 1600 20400
WARD 1250 16200
JONES 2975 36900
MARTIN 1250 16200
BLAKE 2850 35400
CLARK 2450 30600
SCOTT 3000 37200
KING 5000 61200
TURNER 1500 19200
JAMES 950 12600
FORD 3000 37200
MILLER 1300 16800
14 rows selected.
DEFINING A NULL VALUE:
----------------------
NULL is a special datatype. It is an unavailable/unassigned/unknown/inapplicable
value. A NULL value is not a blank or space or a zero.
Anything operated on NULL is NULL.
6. Display the annual salary with commission for employee named 'KING'.
SQL> select ename,12*sal+comm from emp where ename='KING';
ENAME 12*SAL+COMM
---------- -----------
KING
DEFINING COLUMN ALIAS:
----------------------
1. The heading name is replaced for the current SELECT statement.
2. AS keyword optional between the column name and actual alias name.
7. Retrieve the name and salary of all employees and label it as NAME and SALARY.
SQL> select ename as "NAME",sal as "SALARY" from emp;
NAME SALARY
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
CONCATENATION OPERATOR(||):
---------------------------
1. Concatenates the columns of any datatype.
2. Resultant columns will be a single column.
8. For all employee concatenate employee name with his/her job.
SQL> select ename||job as "EMPLOYEES" from emp;
EMPLOYEES
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK
14 rows selected.
USING DISTINCT CLAUSE:
----------------------
Eliminates duplicate row(s).
9. Display distinct department number from employee table.
SQL> select distinct deptno from emp;
DEPTNO
----------
10
20
30
10. Display distinct department and job in them.
SQL> select distinct deptno,job from emp;
DEPTNO JOB
------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
9 rows selected.
SQL Vs SQL * PLUS
-------------------
S.NO | SQL | SQL * PLUS |
1 | A language ANSI standard keyword cannot be abbreviated. | An environment Oracle proprietary can be abbreviated. |
2 | SQL statements are stored in buffer. | SQL Plus statements are not stored in buffer. |
USING WHERE AND ORDER BY CLAUSES:
---------------------------------
SYNTAX
-------
SELECT [DISTINCT] {*,column[alias],…} FROM table [WHERE condition(s)];
11. Retrieve the employee from emp table whose job is Clerk.
SQL> select ename,job,deptno from emp where job='CLERK';
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ADAMS CLERK 20
JAMES CLERK 30
MILLER CLERK 10
NOTE:
-----
Character strings and dates used in the WHERE clause are enclosed in the single
quotation marks. Character literals are case sensitive and dates are not.
12.a) Retrieve the job and deptno of the employee whose name is James.
SQL> select ename,job,deptno from emp where ename='JAMES';
ENAME JOB DEPTNO
---------- --------- ----------
JAMES CLERK 30
SQL> select ename,job,deptno from emp where ename='James';
no rows selected
12.b) Retrieve the job,deptno,ename,hiredate of the employee whose hiredate is
17-dec-80.
SQL> select ename,job,deptno,hiredate from emp where hiredate='17-dec-80';
ENAME JOB DEPTNO HIREDATE
---------- --------- ------ ---------
SMITH CLERK 20 17-DEC-80
12.c) Retrieve the job,deptno,ename,hiredate of the employee whose hiredate is
17-DEC-80.
SQL> select ename,job,deptno,hiredate from emp where hiredate='17-DEC-80';
ENAME JOB DEPTNO HIREDATE
---------- --------- ---------- ---------
SMITH CLERK 20 17-DEC-80
13. Retrieve the employee details whose salary is less than or equal to commission.
SQL> select * from emp where sal<=comm;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
Comparison Operator:
----------------------
= > >= < <= <>
All the above operators can be used in the expression specified in the WHERE
clause.
More Comparison Operators
OPERATOR | DESCRIPTION |
BETWEEN…AND | Between two values inclusive |
IN | Match any of a list values |
LIKE | Match a character pattern |
IS NULL | Is a NULL value |
USING BETWEEN...AND OPERATOR:
-----------------------------
14. Display the names of the employee whose salary between 1000 and 1500.
SQL> select ename,sal from emp where sal between 1000 and 1500;
ENAME SAL
---------- ----------
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
15. Display the names of the employee join between 01-JAN-1980 and 01-JAN-
1982. Assume different dates.
SQL> select ename from emp where hiredate between '01-jan-80' and '01-jan-82';
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
11 rows selected.
USING IN OPERATOR:
------------------
IN Operator to check a list of values.
16.List the employee whose manager ids are 7902, 7566, 7788.
SQL> select empno, ename from emp where mgr in(7902,7566,7788);
EMPNO ENAME
------- ------
7369 SMITH
7788 SCOTT
7876 ADAMS
7902 FORD
17. List the employee number, name and manager id of SUNIL, ANAND and VIMAL.
SQL> select empno, ename, mgr from emp where ename in('SUNIL','ANAND','VIMAL');
no rows selected
USING LIKE OPERATOR:
--------------------
LIKE keyword does a wild card search in valid string values.
% replaces on arbitrary number of characters.
_[underscore] replaces a single character
If underscore or percentage or literal characters in the string, they should be
preceded with an escape character, which is specified after the string.
Example:
--------
AB\_CD\%EF results in AB_CD%EF
18. List the names of the employee whose name starts with 'S'.
SQL> select ename from emp where ename like 'S%';
ENAME
------
SMITH
SCOTT
19. List the names of the employee whose name has a second character A.
SQL> select ename from emp where ename like '_A%';
ENAME
----------
WARD
MARTIN
JAMES
USING IS NULL OPERATOR:
-----------------------
20. List the employees who don't have Boss or Manager.
SQL> select ename, empno, mgr from emp where mgr is NULL;
ENAME EMPNO MGR
---------- ---------- ----------
KING 7839
21. List the employees who don't get commission.
SQL> select ename,empno,comm from emp where comm is NULL;
ENAME EMPNO COMM
---------- ---------- ----------
SMITH 7369
JONES 7566
BLAKE 7698
CLARK 7782
SCOTT 7788
KING 7839
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
10 rows selected.
LOGICAL OPERATORS:
------------------
AND OR NOT
22. List the employee whose salary is less than 1100 and their job is 'clerk'.
SQL> select empno,ename,sal,job from emp where sal<1000 and job='CLERK';
EMPNO ENAME SAL JOB
-------- -------- ------- ------
7369 SMITH 800 CLERK
7900 JAMES 950 CLERK
23. List the employee whose salary is greater than or equal to 1100 and/or their
job 'clerk'.
SQL> select empno,ename,sal,job from emp where sal>=1000 and job='CLERK';
EMPNO ENAME SAL JOB
------- ------- --- ----
7876 ADAMS 1100 CLERK
7934 MILLER 1300 CLERK
SQL> select empno,ename,sal,job from emp where sal>=1100 or job='CLERK';
EMPNO ENAME SAL JOB
----- ------ ------ --------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
14 rows selected.
24. List the names of the employee whose job is not "Clerk,Manager,Analyst".
SQL> select ename,job from emp where job not in('CLERk','MANAGER','ANALYST');
ENAME JOB
---------- ---------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
MILLER CLERK
9 rows selected.
ORDER BY CLAUSE:
----------------
Orders the rows selected either by ascending or descending order.
SYNTAX
------
SELECT <column name> FROM <table name> WHERE <condition> [ORDER
BY]{column,expression}[ASC/DESC]];
25. List the names of the employee according to their date of joining in the
increasing chronological order.
SQL> select ename,hiredate from emp order by hiredate;
ENAME HIREDATE
---------- ---------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
TURNER 08-SEP-81
MARTIN 28-SEP-81
KING 17-NOV-81
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
SCOTT 19-APR-87
ADAMS 23-MAY-87
14 rows selected.
SQL> select ename,hiredate from emp order by hiredate asc;
ENAME HIREDATE
---------- ---------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
TURNER 08-SEP-81
MARTIN 28-SEP-81
KING 17-NOV-81
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
SCOTT 19-APR-87
ADAMS 23-MAY-87
14 rows selected.
26. List the names of the employee according to their date of joining in the
descending chronological order.
SQL> select ename,hiredate from emp order by hiredate desc;
ENAME HIREDATE
---------- ---------
ADAMS 23-MAY-87
SCOTT 19-APR-87
MILLER 23-JAN-82
JAMES 03-DEC-81
FORD 03-DEC-81
KING 17-NOV-81
MARTIN 28-SEP-81
TURNER 08-SEP-81
CLARK 09-JUN-81
BLAKE 01-MAY-81
JONES 02-APR-81
WARD 22-FEB-81
ALLEN 20-FEB-81
SMITH 17-DEC-80
14 rows selected.
SORTING THE ROWS BY ALIAS:
--------------------------
27. Sort the rows based on the alias name 'ANNSAL'.
SQL> select empno,ename,sal*12 as "ANNSAL" from emp order by ANNSAL;
EMPNO ENAME ANNSAL
---------- ---------- ----------
7369 SMITH 9600
7900 JAMES 11400
7876 ADAMS 13200
7521 WARD 15000
7654 MARTIN 15000
7934 MILLER 15600
7844 TURNER 18000
7499 ALLEN 19200
7782 CLARK 29400
7698 BLAKE 34200
7566 JONES 35700
7788 SCOTT 36000
7902 FORD 36000
7839 KING 60000
14 rows selected.
SORTING BY MULTIPLE COLUMNS:
---------------------------
28. Sort the deptno in the Ascending order, sal in the each dept should be sorted
in descending order.
SQL> select ename,deptno,sal from emp order by deptno,sal desc;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
JONES 20 2975
ADAMS 20 1100
SMITH 20 800
BLAKE 30 2850
ALLEN 30 1600
TURNER 30 1500
WARD 30 1250
MARTIN 30 1250
JAMES 30 950
14 rows selected.
29. List the name and salary of employee earning more than $2850.
SQL> select ename,sal from emp where sal>2850;
ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
30. List the name and deptno of the employee whose empno is 7566.
SQL> select ename,deptno from emp where empno=7566;
ENAME DEPTNO
---------- ----------
JONES 20
0 comments:
Post a Comment