Structure Query Language (SQL)
Practical Record
[Link] the Department table: CREATE TABLE statement is used to create table in a
database. If you want to create a table, you should name the table and define its column and each
column's data type.
SQL> create table dept(deptno number(2,0), dname varchar2(14),
loc varchar2(13),constraint pk_dept primary key (deptno));
table created
Inserting records in to DEPT table
SQL> insert into DEPT values(&DEPTNO, ‘&DNAME’, ‘&LOC’);
Enter the value for DEPTNO:10
Enter the value for DNAME: ACCOUNTING
Enter the value for loc: NEW YORK
To insert more rows use / symbol and insert records
Displaying records from the table
SQL>select * from DEPT;
2. Creating emp table
SQL> create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno));
table created
Inserting records in to emp table
SQL> insert into emp
values(&EMPNO,’&ENAME’,’&JOB’,’&HIREDATE’,&MGR,&SAL,&COMM,&DEPTNO);
ENTER VALUE FOR EMPNO: 7369
ENTER VALUE FOR Ename: SMITH
ENTER VALUE FOR JOb:CLERK
ENTER VALUE FOR HIREDATE: 17-DEC-80
ENTER VALUE FOR MGR: 7902
ENTER VALUE FOR SAL: 800
ENTER VALUE FOR COMM: NULL
ENTER VALUE FOR DEPTNO:20
To insert more rows use / symbol and insert records
Displaying records from EMP table
SQL>SELECT * FROM EMP;
)
QUERIES
1. Display the details of employee who have 2 A’s in their name
SQL> Select * from emp where lower(ename) like %a%a%
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ----------- ------- ------ -------------- ------ ---------
7876 ADAMS CLERK 7788 12-MAY-87 1100
2. List the details of employee who earn greater salary than the average
SQL> select * from emp where sal > ( select avg(sal) from emp)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----------- --------------- ------ -------------- ------ --------- -----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
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
7902 FORD ANALYST 7566 03-DEC-81 3000 20
3. Display the details of dept where employee salary is greater than 2000?
SQL> select * from dept where dept in ( select deptno from emp group by deptno having
avg(sal)>2000);
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
4. Display the details of employees who joined in February and year 1991
SQL> select * from emp where hiredate=’01-FEB-91’;
No records selected
5. List the names of all employees who name has more than 5 characters and less than 8 characters
SQL> select ename from emp where length(ename) > 5 and length(ename) < 8;
ENAME
----------
MARTIN
TURNER
MILLER
6. List the employee details along with their manager
SQL> select ename employee, ename manager from emp e1, emp e2 where
[Link]=[Link];
EMPLOYEE MANAGER
SCOTT JONES
FORD JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
MILLER BLAKE
ADAMS SCOTT
JONES KING
CLARK KING
BLAKE KING
SMITH FORD
7. Display the list of employees whose name each with ‘R’?
SQL> select ename from emp where lower(ename) like %R%;
ENAME
-----------
TURNER
MILLER
8. Display empno, ename, sal prefixed with ampersand sign and in decreasing order with the job or
manager.
SQL> select empno, ename, concat(‘&’,’sal’), job from emp order by job desc;
EMPNO ENAME CONCAT(&SAL) JOB
7499 ALLEN &1600 SALESMAN
7521 WARD &1250 SALESMAN
7654 MARTIN &1250 SALESMAN
7844 TURNER &1500 SALESMAN
7839 KING &5000 PRESIDENT
7566 JONES &2975 MANAGER
7782 CLARK &2450 MANAGER
7698 BLAKE &2850 MANAGER
7369 SMITH &800 CLERK
7900 JAMES &950 CLERK
7876 ADAMS &1100 CLERK
7934 MILLER &1300 ANALYST
7902 FORD &3000 ANALYST
9. Display department number and total number of employees working in each department
SQL> Select deptno, count(*) from emp group by deptno
DEPTNO COUNT(*)
---------- ---------------
10 3
20 5
30 6
10. Display the various jobs and total number of employees in each job.
SQL> select job, count(*) from emp group by job;
JOB COUNT(*)
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
11. Display the names of employees in upper case?
SQL> select upper(ename) from emp;
ENAME
---------
SMITH
ALLEN
WARD
JONES
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
12. Display the name of emplees in lowercase?
SQL> select lower(ename) from emp;
smith
allen
ward
jones
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller
13. Use appropriate function and extract 3 characters starting from 2 character from the following
string ‘oracle’ i.e the output should be ‘rac’.
SQL> select substr(‘oracle’,2,3) from dual ;
SUB
------
rac
14. Replace every occurrence of alphabet with in the string allens ( use translate function)
SQL> select translate(‘allen’ , ‘A’, ‘B’) from dual;
15. Display the employee name, job and his manager develop who employee who is without manager
SQL> select ename employee, el job e2 ename, manager from emp e1, emp e2 where
[Link]=[Link](+);
EMPLOYEE JOB MANAGER
SCOTT ANALYST JONES
FORD ANALYST JONES
ALLEN SALESMAN BLAKE
WARD SALESMAN BLAKE
JAMES CLERK BLAKE
TURNER SALESMAN BLAKE
MARTIN SALESMAN BLAKE
MILLER CLERK CLARK
ADAMS CLERK SCOTT
JONES MANAGER KING
BLAKE MANAGER KING
SMITH CLERK FORD
KING PRESIDENT
16. Display the grade of jones?
SQL> Select job from emp where ename= ’JONES’;
JOB
---------------
MANAGER
17. Create a copy of emp table
SQL> create table emp1 as select * from emp;
SQL> select * from emp1;
Note: This displays same records as that of emp table
18. Display the employee name as follows
A allen
B blake
SQL> select substr(ename,1,1), ename from emp where order by ename;
S ENAME
A ADAMS
A ALLEN
B BLAKE
C CLARK
F FORD
J JAMES
K KING
M MARTIN
M MILLER
S SCOTT
S SMITH
T TURNER
W WARD
19. Display the list of employees whose name starts with R and for only 4 characters in it
SQL> select * from emp where ename like ‘R…’;
No rows selected
20. Display enames in ascending order
SQL>select ename from emp order by ename;