Scribd
Upload
8 views4 pages

SQL Table Creation and Queries Guide

The document outlines the creation and management of three database tables: DEPT, EMP, and SALGRADE, including their structures, primary and foreign key constraints, and sample data entries. It also includes various SQL queries for data retrieval and manipulation, demonstrating the use of operators, functions, and clauses in SQL. The document serves as a comprehensive guide for managing employee and department data within a relational database.

Uploaded by

anujasbamdale1519
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views4 pages

SQL Table Creation and Queries Guide

The document outlines the creation and management of three database tables: DEPT, EMP, and SALGRADE, including their structures, primary and foreign key constraints, and sample data entries. It also includes various SQL queries for data retrieval and manipulation, demonstrating the use of operators, functions, and clauses in SQL. The document serves as a comprehensive guide for managing employee and department data within a relational database.

Uploaded by

anujasbamdale1519
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE DEPT

(DEPTNO Number(2),
DNAME VARCHAR2(10),
LOC VARCHAR2(10),
CONSTRAINT PK_DEPT_DNo PRIMARY KEY (DEPTNO));

desc dept;

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');


INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

commit;
select * from dept;

CREATE TABLE EMP (


EMPNO Number(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR Number(4) CONSTRAINT FK_Emp_MGR REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL Number(7,2),
COMM Number(7,2),
DEPTNO Number(2),
CONSTRAINT FK_EMP_DNo FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT PK_EMP_ENo PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-21',5000,NULL,10);


INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-21',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-21',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'02-APR-21',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-21',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-21',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'03-DEC-21',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-21',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'03-DEC-21',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-20',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-23',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-22',1300,NULL,10);
commit;
select * from emp;

CREATE TABLE SALGRADE (


GRADE Number(2),
LOSAL Number(4),
HISAL Number(4));

desc salgrade;

INSERT INTO SALGRADE VALUES (1,700,1200);


INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
commit;

select * from salgrade;

select * from dept;


select * from emp;
select * from salgrade;

--Operators
select * from emp where deptno=10;
select * from emp where sal>2000;
select * from emp where sal<2000;

select * from emp where sal>=3000;


select * from emp where sal<=3000;

select * from emp where job!='MANAGER';


select * from emp where job<>'CLERK';

select * from emp where sal>=2000 and sal<=5000;


select * from emp where sal>1000 and sal<2000;

--SQL Operators
--Between
select * from emp where sal between 2000 and 5000;
select * from emp where sal between 2000 and 5000 order by sal;
select * from emp where sal not between 2000 and 5000 order by sal;

select * from emp where ename between 'A' and 'K' order by ename;
select * from emp where ename not between 'A' and 'K' order by ename;

select * from emp where hiredate between '01-JAN-21' and '31-DEC-21' order by
hiredate;
select * from emp where hiredate not between '01-JAN-21' and '31-DEC-21' order by
hiredate;

--IN
select * from emp order by sal;
select * from emp where sal=800 or sal=1250 or sal=1500 or sal=3000 order by sal;
select * from emp where sal IN (800,1250,1500,3000) order by sal;
select * from emp where ename in ('ADAMS','CLARK','WARD','KING') order by ename;
select * from emp where hiredate in ('01-May-21','08-Sep-21','17-dec-20') order by
hiredate;

select * from emp where sal not IN (800,1250,1500,3000) order by sal;


select * from emp where ename not in ('ADAMS','CLARK','WARD','KING') order by
ename;
select * from emp where hiredate not in ('01-May-21','08-Sep-21','17-dec-20') order
by hiredate;

--LIKE

select * from emp where ename like 'A%';


select * from emp where ename like '%R';
select * from emp where ename like '%A%';
select * from emp where ename like '_A%';
select * from emp where ename like '__A%';
select * from emp where ename like '____';
select * from emp where ename not like 'A%';
select * from emp where ename not like '%R';
select * from emp where ename not like '____';

select * from emp where hiredate like '%SEP%';


select * from emp where hiredate like '%09%';

--IS NULL
select * from emp where comm=NULL;
select * from emp where comm!=NULL;

select * from emp where comm IS NULL;


select * from emp where comm IS NOT NULL;

select deptno from emp;


select job from emp;

select DISTINCT deptno from emp;


select DISTINCT job from emp;
select * from emp order by ename;
select ename,job,sal,deptno from emp where sal>1500 order by sal;
select ename,job,sal,deptno from emp where sal>1500 order by sal;

select * from emp order by ename;


select * from emp order by sal;

select * from emp order by ename desc;


select * from emp order by sal desc;
select * from emp order by deptno,sal;

select deptno,ename,sal from emp order by deptno,sal;


select deptno,ename,sal from emp order by deptno,sal desc;

--Clauses of Select Statement


--Select
--Distinct
--From
--Where
--Order By

--Functions
--Single Row Functions
-- Character
-- Number
-- Date
-- Conversion
-- Misc

--Group Functions
-- Sum
-- Count
-- Avg
-- Min
-- Max

--Single Row Functions


-- Character
Select EName,Upper(Ename),Lower(Ename),Initcap(Ename) from Emp;
select Upper('ram'),'is a database', Initcap('trainer developer architect') from
dual;
select lower(ename),'Works as a', Initcap(Job),'Earning a Salary',Sal from emp;
select Initcap(ename),'Works as a', Initcap(Job),'Earning a Salary',Sal from emp
where ename ='KING';;
--Literal
select ename,length(ename) from emp;
select length('hello guys, how are you doing?') from DUAL;
select ename,lpad(ename,10,'*'),rpad(ename,10,'#') from emp;
select ename,ltrim(ename,'A'),rtrim(ename,'S'),trim(ename) from emp;
select trim(' hello how are u? ') from dual;
select trim(' hello how are u? ') from dual;
select ename,substr(ename,1,2),substr(ename,3), substr(ename,1,2)||substr(ename,3)
from emp;
select ename,substr(ename,1,2),substr(ename,3),
concat(substr(ename,1,2),substr(ename,3)) from emp;
select Initcap(ename)||' Works as a '||Initcap(Job)||'--Earning a Salary--'||Sal
from emp;
select ename,translate(ename,'ASR','@$#'),translate(ename,'ASR','@$') from emp;
select job,replace(job,'CLERK','Assistant') from emp;
select ename,replace(ename,'A','@@@') from emp;
select ename,soundex(ename) from emp;
select soundex('laxmi'),soundex('laksmi'),soundex('lakmi') from dual;
select soundex('ravan'),soundex('ravana'),soundex('rabana') from dual;
select soundex('srinivas'),soundex('sreenibas'),soundex('srinuvas') from dual;

You might also like