Scribd
Upload
68 views2 pages

Tutorial Week 5 - SQL Revision

This document provides 9 SQL queries to practice on the Scott database. The queries include: 1) listing employees who joined on the same date, 2) producing a concatenated output of employee name and job, 3) producing a formatted output of employee name and job, 4) counting managers, 5) listing salaries in different categories, 6) counting character lengths of names without spaces, 7) listing managers earning less than their employees, 8) printing details of employees subordinate to Blake, and 9) listing managers using a correlated subquery.

Uploaded by

Bijay Giri
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
68 views2 pages

Tutorial Week 5 - SQL Revision

This document provides 9 SQL queries to practice on the Scott database. The queries include: 1) listing employees who joined on the same date, 2) producing a concatenated output of employee name and job, 3) producing a formatted output of employee name and job, 4) counting managers, 5) listing salaries in different categories, 6) counting character lengths of names without spaces, 7) listing managers earning less than their employees, 8) printing details of employees subordinate to Blake, and 9) listing managers using a correlated subquery.

Uploaded by

Bijay Giri
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/ 2

SQL-QUERIES – Phase Test Practice

These are based on the Scott database, and require you to run scripts before to
create emp and Dept tables, if you haven’t done so already.

We’ll use a similar approach to last week, and write all come with SQL commands.
Save each query week5_number

1. List the emps who joined in the company on the same date.
select ename,hiredate from emp e where hiredate in (Select hiredate from emp
where e.hiredate=hiredate and e.empno<>empno);

2. Produce the output of EMP table ‘EMP_AND_JOB’ for Ename and Job.
SELECT ename||' '||job FROM emp;

3. Produce the following output from EMP.


EMPLOYEE
SMITH (clerk)
ALLEN (Salesman)
SELECT ename||' ('|| job ||')' employee FROM emp;

4. Count the No.of emps who are working as ‘Managers’(using set option).
select Count(*) from (
select * from emp
minus
select * from emp where job<>'MANAGER');

5) Print a list of emp’s Listing ‘just salary’ if Salary is more than 1500, on target if
Salary is 1500 and ‘Below 1500’ if Salary is less than 1500.
SELECT ename||' Just Salary' from emp where sal>1500
UNION
SELECT ename||' On Target' from emp where sal=1500
UNION
SELECT ename||' Below 1500' from emp where sal<1500;

6) Count the no. of characters with out considering spaces for each name.
SELECT ename, length(replace(ename,' ','')) length from emp;

7) List those Managers who are getting less than his emps Salary.

SELECT DISTINCT m.ename from emp e, emp m


where e.mgr=m.empno
and e.sal>m.sal;

SELECT * from emp m where empno in (SELECT mgr from where m.sal<sal);

8) Print the details of all the emps who are sub-ordinates to Blake.
SELECT * from emp e, emp m where e.mgr=m.empno and m.ename='BLAKE';

1
9) List the emps who are working as Managers using co-related sub-query.
SELECT * from emp where job='MANAGER';

You might also like