
INFOSYS POWER PREPARATION
SQL
Quries
SCHEMA - I
Table 1 : STUDIES
PNAME (VARCHAR), SPLACE (VARCHAR), COURSE
(VARCHAR), CCOST (NUMBER)
Table 2 : SOFTWARE
PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR),
SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)
Table 3 : PROGRAMMER
PNAME (VARCHAR), DOB (DATE), DOJ (DATE),
SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR),
SAL (NUMBER)
LEGEND :
PNAME Programmer Name, SPLACE Study Place,
CCOST Course Cost, DEVIN Developed in,
SCOST Software Cost, DCOST Development
Cost, PROF1 Proficiency 1
QUERIES :
Find out the selling cost average for packages
developed in Oracle.
Display the names, ages and experience of
all programmers.
Display the names of those who have done
the PGDCA course.
What is the highest number of copies sold
by a package?
Display the names and date of birth of all
programmers born in April.
Display the lowest course fee.
How many programmers have done the DCA course.
How much revenue has been earned through
the sale of packages developed in C.
Display the details of software developed
by Rakesh.
How many programmers studied at Pentafour.
Display the details of packages whose sales
crossed the 5000 mark.
Find out the number of copies which should
be sold in order to recover the development
cost of each package.
Display the details of packages for which
the development cost has been recovered.
What is the price of costliest software developed
in VB?
How many packages were developed in Oracle
?
How many programmers studied at PRAGATHI?
How many programmers paid 10000 to 15000
for the course?
What is the average course fee?
Display the details of programmers knowing
C.
How many programmers know either C or Pascal?
How many programmers dont know C and C++?
How old is the oldest male programmer?
What is the average age of female programmers?
Calculate the experience in years for each
programmer and display along with their names
in descending order.
Who are the programmers who celebrate their
birthdays during the current month?
How many female programmers are there?
What are the languages known by the male
programmers?
What is the average salary?
How many people draw 5000 to 7500?
Display the details of those who dont know
C, C++ or Pascal.
Display the costliest package developed by
each programmer.
Produce the following output for all the
male programmers
Programmer Mr. Arvind has 15 years of experience.
SCHEMA - II
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE
(DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO
(NUMBER(2))
MGR is the empno of the employee whom the
employee reports to. DEPTNO is a foreign key.
QUERIES
1. List all the employees who have at least
one person reporting to them.
2. List the employee details if and only
if more than 10 employees are present in department
no 10.
3. List the name of the employees with their
immediate higher authority.
4. List all the employees who do not manage
any one.
5. List the employee details whose salary
is greater than the lowest salary of an employee
belonging to deptno 20.
6. List the details of the employee earning
more than the highest paid manager.
7. List the highest salary paid for each
job.
8. Find the most recently hired employee
in each department.
9. In which year did most people join the
company? Display the year and the number of
employees.
10. Which department has the highest annual
remuneration bill?
11. Write a query to display a * against
the row of the most recently hired employee.
12. Write a correlated sub-query to list
out the employees who earn more than the average
salary of their department.
13. Find the nth maximum salary.
14. Select the duplicate records (Records,
which are inserted, that already exist) in
the EMP table.
15. Write a query to list the length of service
of the employees (of the form n years and
m months).