Scribd
Upload
50 views10 pages

Query & Answer

The document outlines SQL commands for creating and populating two tables: 'dept' and 'emp', including their respective fields and relationships. It also provides various SQL queries to retrieve specific data from the 'emp' and 'dept' tables, such as employee names, salaries, job titles, and department details. The queries cover a range of conditions, including filtering by salary, job titles, and department numbers.

Uploaded by

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

Query & Answer

The document outlines SQL commands for creating and populating two tables: 'dept' and 'emp', including their respective fields and relationships. It also provides various SQL queries to retrieve specific data from the 'emp' and 'dept' tables, such as employee names, salaries, job titles, and department details. The queries cover a range of conditions, including filtering by salary, job titles, and department numbers.

Uploaded by

Devabn Nirmal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 10

Practice Session

Table Creations:
create table dept (deptno number primary key, dname varchar2(20), loc varchar2(15));
insert into dept values (&deptno, ’&dname’, ’&loc’);

create table emp (empno number primary key, ename varchar2(15), job varchar2(15), mgr
number, hiredate date, sal number, comm number, deptno number, foreign key(deptno)
references dept(deptno));
insert into emp values (&empno, ’&ename’, ’&job’, &mgr, to_date(‘&hiredate’,’DD-MON-
YY’), &sal, &comm, &deptno);
Queries
 Retrieve all the enames from the emp.

select ename from emp;

 Retrieve all the emp names and salary from emp.

select ename, sal from emp;

 Retrieve all the emp names and salary who are earning more than 3000.

select ename, sal from emp where sal>3000;

 List out all the emp names and dept no who are working in dept no 10.

 Retrieve all the emp names and jobs who are working as analyst.
 List out the manager number for emp ‘smith’.

 List out the jobs in emp.

select job from emp;

 List out the department numbers in emp.

select deptno from emp;

 Select all the emp names and jobs who are getting comm.

 Retrieve all the emp names, salary and comm whose comm is greater
than their salary.
 Retrieve all the emp details in deptno 20 and who are working as
clerk’.

 Retrieve all the emp details in deptno 20 or who are working as ‘clerk’.

 List out all the emp details in deptno 30 and who are earning more than
1000.

 Retrieve all the dept names.

select *from dept;


 Find out the name of dept 10.

 List out all names, locations of dept.

select dname, loc from dept;

 List out the deptno which is located in ‘dallas’.

 List out all the dept’s whose deptno is greater than 10.

 List out all the employees who are joined on the ’28-sep-1981’.
 List out all the emp names in descending order.

 List out all the salary information in ascending order.


 List out all the information who are joined after 1980.

 List out all the information who are in deptno 10 and 20.

 List out all the information who are earning salary from 1000 to 2000.
 List out all the emps whose names are starting with letter ‘a’.

 List out all the information whose jobs ends with letter ‘T’.

 List out all the information of emp except president in ascending order of
salary.
 List out emp in below format ‘SMITH is working as CLERK’.

You might also like