Showing posts with label Practical sql queries with example. Show all posts
Showing posts with label Practical sql queries with example. Show all posts
Saturday, July 11, 2015
Thursday, May 1, 2014
Practical DBMS learning with example Queries
Practical Answer 1.
1)
SQL> create
table dept1(deptno number primary key,dname varchar(6) not null,loc
varchar(8));
Table created.
SQL> describe
dept1;
Name
Null? Type
----------------------------------------- --------
----------------------------
DEPTNO NOT NULL
NUMBER
DNAME NOT NULL
VARCHAR2(6)
LOC
VARCHAR2(8)
SQL> create table emp1(empno number primary
key,ename varchar(8) not null,deptno number,
2 job
varchar(8) check(
job=UPPER(job)),hiredate date default sysdate,foreign key(deptno)
3
references dept1 (deptno));
Table created.
SQL> describe
emp1
Name Null? Type
-----------------------------------------
-------- ----------------------------
EMPNO NOT NULL
NUMBER
ENAME NOT NULL
VARCHAR2(8)
DEPTNO NUMBER
JOB
VARCHAR2(8)
HIREDATE DATE
2)
SQL> alter
table emp1 add(sal number(8,2) check (sal>0),mgr number,comm number(5,2));
Table altered.
SQL> describe
emp1;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EMPNO NOT NULL
NUMBER
ENAME NOT NULL VARCHAR2(8)
DEPTNO
NUMBER
JOB
VARCHAR2(8)
HIREDATE DATE
SAL NUMBER(8,2)
MGR
NUMBER
COMM
NUMBER(5,2)
SQL> alter
table dept1 modify(dname varchar(14),loc varchar(13));
Table altered.
SQL> insert
into dept1 select * from dept;
4 rows created.
SQL> select *
from dept1;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW
YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
3)
SQL> insert into emp1
values(7499,'ALLEN',30,'SALESMAN','20-feb-81',1600,7608,300);
1 row created.
Expriment no 2
1)
SQL> select *
from tab
2 ;
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
BONUS TABLE
CLIENTMAST TABLE
CLIENTMASTER TABLE
DEPT TABLE
DEPT1 TABLE
DEPTIT TABLE
EMP TABLE
EMP1 TABLE
EMPIT TABLE
EMPLOYEE TABLE
LOGSTUD TABLE
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
ORDERS TABLE
SALGRADE TABLE
STUD TABLE
SUPPLYMASTER TABLE
15 rows selected.
2)
SQL> select
dname from dept1;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
3)
SQL> select
dname,loc from dept;
DNAME LOC
--------------
-------------
ACCOUNTING NEW
YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
4)
SQL> select job
from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
JOB
---------
CLERK
ANALYST
CLERK
14 rows selected.
5)
SQL> select *
from emp where ename='smith';
no rows selected
SQL> select * from emp where ename='SMITH';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
6)
SQL> SELECT
ename,job from emp where deptno=20;
ENAME JOB
----------
---------
SMITH CLERK
JONES MANAGER
SCOTT ANALYST
FORD ANALYST
7)
SQL> select *
from emp where deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 20-FEB-81
1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81
1250 500
30
7654 MARTIN SALESMAN 7698 28-SEP-81
1250 1400
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81
2850
30
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0
30
7900 JAMES CLERK 7698 03-DEC-81 950
30
6 rows selected.
8)
SQL> select
empno,ename,job from emp where job='MANAGER';
EMPNO ENAME JOB
----------
---------- ---------
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
9)
SQL> select
ename from emp where comm>sal;
ENAME
----------
MARTIN
10)
SQL> select
ename,job,sal from emp where sal>2000;
ENAME JOB SAL
----------
--------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 rows selected.
11)
SQL> select
ename,sal as monthly,sal*12 as annual from emp;
ENAME MONTHLY ANNUAL
----------
---------- ----------
SMITH 800 9600
ALLEN 1600 19200
WARD 1250 15000
JONES 2975 35700
MARTIN 1250 15000
BLAKE 2850 34200
SCOTT 3000
36000
KING 5000 60000
TURNER 1500 18000
ENAME MONTHLY ANNUAL
----------
---------- ----------
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
14 rows selected.
12)
SQL> select
ename,sal from emp where deptno=10;
ENAME SAL
----------
----------
KING 5000
MILLER 1300
13)
SQL> select
dname as department,deptno as department_no from dept;
DEPARTMENT DEPARTMENT_NO
--------------
-------------
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40
14)
SQL> select
ename,mgr from emp where job='MANAGER';
ENAME MGR
----------
----------
JONES 7839
BLAKE 7839
15)
SQL> select empno,ename FROM EMP where
job='ANALYST';
EMPNO ENAME
----------
----------
7788 SCOTT
7902 FORD
Experiment No 3
1)
SQL> select
ename from emp where empno>mgr;
ENAME
----------
SCOTT
TURNER
JAMES
FORD
MILLER
6 rows selected.
2)
SQL> select
ename,job from emp where job='MANAGER' and deptno !=10;
ENAME JOB
----------
---------
JONES MANAGER
BLAKE MANAGER
3)
SQL> select
ename,job from emp where job not in('CLEARK','SALESMAN','ANALYST');
ENAME JOB
----------
---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
KING PRESIDENT
JAMES CLERK
MILLER CLERK
8 rows selected.
SQL> select
ename from emp where deptno=20 and job!='clerk' and job!='salesman' and
job!='analyst';
ENAME
----------
SMITH
JONES
SCOTT
FORD
6)
SQL> select
ename,sal from emp where sal between 1000 and 2000;
ENAME SAL
----------
----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
MILLER 1300
6 rows selected.
13)
SQL> select
ename,job,comm from emp where comm is null;
ENAME JOB COMM
----------
--------- ----------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
SCOTT ANALYST
KING PRESIDENT
JAMES CLERK
FORD ANALYST
MILLER CLERK
10 rows selected.
14)
SQL> select
ename,job,comm from emp where comm is not null
2 ;
ENAME JOB COMM
----------
--------- ----------
ALLEN SALESMAN 300
WARD SALESMAN 500
MARTIN SALESMAN 1400
TURNER SALESMAN 0
15)
SQL> select
ename,deptno from emp where deptno in(10,20);
ENAME DEPTNO
----------
----------
SMITH 20
JONES 20
SCOTT 20
KING 10
FORD 20
MILLER 10
8 rows selected.
16)
SQL> select
ename,hiredate from emp where hiredate>'01-jan-81' and deptno=10;
ENAME HIREDATE
----------
---------
KING 17-NOV-81
MILLER 23-JAN-82
17)
SQL> select
ename,deptno from emp where deptno !=10;
ENAME DEPTNO
----------
----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
SCOTT 20
TURNER 30
JAMES 30
FORD 20
11 rows selected.
Expriment no 4
1)
SQL> select
count(ename) from emp;
COUNT(ENAME)
------------
14
2)
SQL> select count(job) from emp where job='CLERK'
and hiredate>'13-jan-81';
COUNT(JOB)
----------
3
3)
SQL> select
sum(sal)*12 as yearly_comp from emp where job='SALESMAN';
YEARLY_COMP
-----------
67200
5)
SQL> select
avg(sal) from emp;
AVG(SAL)
----------
2073.21429
6)
SQL> select
avg(sal)*12 as avg_yr_comp from emp where job like 'SALESMAN';
AVG_YR_COMP
-----------
16800
7)
SQL> select
min(sal) from emp;
MIN(SAL)
----------
800
8)
SQL> select
ename from emp order by ename;
ENAME
----------
ALLEN
BLAKE
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
ENAME
----------
SMITH
TURNER
WARD
14 rows selected.
9)
SQL> select
max(sal) from emp;
MAX(SAL)
----------
5000
Experiment No 6
1)
SQL> create table clientmast(client_no varchar(4)
primary key check(client_no like 'c%')
2
,name varchar(4) not null,address1 varchar(8),address2 varchar(8),city
varchar(8)
3
,state varchar(8),pin_code number,remark varchar(5),bal_due number(7,2)
4
,check(name=upper(name)));
Table created.
SQL> describe
clientmast;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
CLIENT_NO NOT NULL
VARCHAR2(4)
NAME NOT NULL VARCHAR2(4)
ADDRESS1
VARCHAR2(8)
ADDRESS2
VARCHAR2(8)
CITY
VARCHAR2(8)
STATE VARCHAR2(8)
PIN_CODE
NUMBER
REMARK
VARCHAR2(5)
BAL_DUE
NUMBER(7,2)
4)
SQL> insert
into clientmast values('c01','ABC1','nagpada1','byculla1','mumbai','maharas',01,'good1',
1000);
1 row created.
1* insert into clientmast
values('c02','ABC2','nagpada2','byculla2','mumbai','maharas',02,'good2',
5)
SQL> SELECT *
FROM CLIENTMAST;
CLIE NAME ADDRESS1
ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE
---- ---- --------
-------- -------- -------- ---------- ----- ----------
c01 ABC1 nagpada1 byculla1 mumbai maharas 1 good1 1000
c02 ABC2 nagpada2 byculla2 mumbai maharas 2 good2 2000
c03 ABC3 nagpada3 byculla3 mumbai maharas 3 good3 3000
c04 ABC4 nagpada4 byculla4 mumbai maharas 4 good4 4000
c05 ABC5 nagpada5 byculla5 mumbai maharas 5 good5 5000
6)
SQL> ALTER
TABLE CLIENTMAST ADD(AGE NUMBER(2,2));
Table altered.
7)
SQL> UPDATE CLIENTMAST SET AGE=35;
5 rows updated.
SQL> UPDATE CLIENTMAST SET AGE=65 WHERE
NAME='ABC5';
1 row updated.
SQL> SELECT *
FROM CLIENTMAST;
CLIE NAME ADDRESS1
ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE
---- ---- --------
-------- -------- -------- ---------- ----- ----------
AGE
----------
c01 ABC1 nagpada1 byculla1 mumbai maharas 1 good1 1000
35
c02 ABC2 nagpada2 byculla2 mumbai maharas 2 good2 2000
35
c03 ABC3 nagpada3 byculla3 mumbai maharas 3 good3 3000
35
CLIE NAME ADDRESS1
ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE
---- ---- --------
-------- -------- -------- ---------- ----- ----------
AGE
----------
c04 ABC4 nagpada4 byculla4 mumbai maharas 4 good4 4000
35
c05 ABC5 nagpada5 byculla5 mumbai maharas 5 good5 5000
65
SQL> DELETE
FROM CLIENTMAST WHERE AGE>60;
1 row deleted.
Subscribe to:
Posts (Atom)