Class -1 Introduction to SQL Querying

Here we will discuss about the following topics:

What is SQL?
Why SQL
Table basics
Table Constraints
Selecting data
Creating tables
Data Types
Predicate operations (Where Clause)
Order By

What is SQL?
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data
stored in relational database.
SQL is the standard language for Relation Database System. All relational database management systems like
MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.
Also, they are using different dialects, such as:

MS SQL Server using T-SQL,

Oracle using PL/SQL,
MS Access version of SQL is called JET SQL native format etc.

Why SQL?

Allows users to access data in relational database management systems.

Allows users to describe the data.
Allows users to define the data in database and manipulate that data.
Allows embedding within other languages using SQL modules, libraries & pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a database.
Allows users to set permissions on tables, procedures, and views

SQL Process:
When you are executing an SQL command for any RDBMS, the system determines the best way to carry out
your request and SQL engine figures out how to interpret the task.
There are various components included in the process. These components are Query Dispatcher, Optimization
Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries but
SQL query engine won't handle logical files.

Following is a simple diagram showing SQL Architecture:

SQL Commands:
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE
and DROP. These commands can be classified into groups based on their nature:
DDL - Data Definition Language:
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP

Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language:

SELECT Retrieves certain records from one or more tables
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
DCL - Data Control Language:
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user
What is table?
The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries
and it consists of columns and rows.
Remember, a table is the most common and simplest form of data storage in a relational database.

Following is the example of a CUSTOMERS table:

+----+----------+-----+-----------+----------+
| ID | NAME

| AGE | ADDRESS | SALARY |

+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi
| 3 | kaushik | 23 | Kota

| 1500.00 |
| 2000.00 |

+----+----------+-----+-----------+----------+
What is field?
Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID,
NAME, AGE, ADDRESS and SALARY.
A field is a column in a table that is designed to maintain specific information about every record in the table.
What is record or row?
A record, also called a row of data, is each individual entry that exists in a table. For example there are 7 records
in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
A record is a horizontal entity in a table.
What is column?
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would
consist of the following:
+-----------+| ADDRESS |+-----------+
| Ahmedabad |
| Delhi

| Kota

+----+------+

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a
field with no value.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
A field with a NULL value is one that has been left blank during record creation.
SQL Constraints:
Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go
into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column
whereas table level constraints are applied to the whole table.
Following are commonly used constraints available in SQL:

NOT NULL Constraint: Ensures that a column cannot have NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX: Use to create and retrieve data from the database very quickly.

Data Integrity:
The following categories of the data integrity exist with each RDBMS:
Entity Integrity: There are no duplicate rows in a table.
Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the
range of values.
Referential integrity: Rows cannot be deleted, which are used by other records.
User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or
referential integrity.
Selecting data:
The select statement is used to query the database and retrieve selected data that match the criteria that you
specify. Here is the format of a simple select statement:
select "column1"
[,"column2",etc]
from "tablename"
[where "condition"];
[] = optional
A SQL SELECT statement can be broken down into numerous elements, each beginning with a keyword.
Although it is not necessary, common convention is to write these keywords in all capital letters. In this article,
we will focus on the most fundamental and common elements of a SELECT statement, namely

SELECT
FROM
WHERE
ORDER BY

The SELECT ... FROM Clause

The most basic SELECT statement has only 2 parts: (1) what columns you want to return and (2) what table(s)
those columns come from.
If we want to retrieve all of the information about all of the customers in the Employees table, we could use the
asterisk (*) as a shortcut for all of the columns, and our query looks like
SELECT * FROM Employees

If we want only specific columns (as is usually the case), we can/should explicitly specify them in a commaseparated list, as in which results in the specified fields of data for all of the rows in the table:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees

Explicitly specifying the desired fields also allows us to control the order in which the fields are returned, so that
if we wanted the last name to appear before the first name, we could write
SELECT EmployeeID, LastName, FirstName, HireDate, City FROM Employees

The WHERE Clause

The next thing we want to do is to start limiting, or filtering, the data we fetch from the database. By adding
a WHERE clause to the SELECT statement, we add one (or more) conditions that must be met by the selected
data. This will limit the number of rows that answer the query and are fetched. In many cases, this is where
most of the "action" of a query takes place.
We can continue with our previous query, and limit it to only those employees living in London:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees WHERE City = 'London'

Resulting in

If you wanted to get the opposite, the employees who do not live in London, you would write

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees WHERE City <> 'London'

It is not necessary to test for equality; you can also use the standard equality/inequality operators that you
would expect. For example, to get a list of employees who were hired on or after a given date, you would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees WHERE HireDate >= '1-july-1993'

and get the resulting rows

Of course, we can write more complex conditions. The obvious way to do this is by having multiple conditions in
the WHERE clause. If we want to know which employees were hired between two given dates, we could write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM
Employees
WHERE (HireDate >= '1-june-1992') AND (HireDate <= '15-december-1993')

resulting in

Note that SQL also has a special BETWEEN operators that checks to see if a value is between two values
(including equality on both ends). This allows us to rewrite the previous query as
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate BETWEEN '1-june-1992' AND '15-december-1993'

We could also use the NOT operator, to fetch those rows that are not between the specified dates:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate NOT BETWEEN '1-june-1992' AND '15-december-1993'

Let us finish this section on the WHERE clause by looking at two additional, slightly more sophisticated,
comparison operators.
What if we want to check if a column value is equal to more than one value? If it is only 2 values, then it is easy
enough to test for each of those values, combining them with the OR operator and writing something like
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City = 'London' OR City = 'Seattle'

However, if there are three, four, or more values that we want to compare against, the above approach quickly
becomes messy. In such cases, we can use the IN operator to test against a set of values. If we wanted to see if
the City was either Seattle, Tacoma, or Redmond, we would write

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees

WHERE City IN ('Seattle', 'Tacoma', 'Redmond')

producing the results shown below.

As with the BETWEEN operator, here too we can reverse the results obtained and query for those rows where
City is not in the specified list:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City NOT IN ('Seattle', 'Tacoma', 'Redmond')

Finally, the LIKE operator allows us to perform basic pattern-matching using wildcard characters. For Microsoft
SQL Server, the wildcard characters are defined as follows:
Wildcard

Description

_ (underscore)

matches any single character

matches a string of one or more characters

[]

matches any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]).

[^]

matches any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]).

A few examples should help clarify these rules.

WHERE FirstName LIKE '_im' finds all three-letter first names that end with 'im' (e.g. Jim, Tim).
WHERE LastName LIKE '%stein' finds all employees whose last name ends with 'stein'
WHERE LastName LIKE '%stein%' finds all employees whose last name includes 'stein' anywhere in the
name.
WHERE FirstName LIKE '[JT]im' finds three-letter first names that end with 'im' and begin with either 'J'
or 'T' (that is, only Jim and Tim)
WHERE LastName LIKE 'm[^c]%' finds all last names beginning with 'm' where the following (second)
letter is not 'c'.

Here too, we can opt to use the NOT operator: to find all of the employees whose first name does not start with
'M' or 'A', we would write
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees WHERE (FirstName NOT LIKE 'M%') AND (FirstName NOT LIKE
'A%')

resulting in

The ORDER BY Clause

Until now, we have been discussing filtering the data: that is, defining the conditions that determine which rows
will be included in the final set of rows to be fetched and returned from the database. Once we have
determined which columns and rows will be included in the results of our SELECT query, we may want to control
the order in which the rows appearsorting the data.
To sort the data rows, we include the ORDER BY clause. The ORDER BY clause includes one or more column
names that specify the sort order. If we return to one of our firstSELECT statements, we can sort its results by
City with the following statement:
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
ORDER BY City

By default, the sort order for a column is ascending (from lowest value to highest value), as shown below for the
previous query:

If we want the sort order for a column to be descending, we can include the DESC keyword after the column
name.
The ORDER BY clause is not limited to a single column. You can include a comma-delimited list of columns to
sort bythe rows will all be sorted by the first column specified and then by the next column specified. If we
add the Country field to the SELECT clause and want to sort by Country and City, we would write:
SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM Employees
ORDER BY Country, City DESC

Note that to make it interesting, we have specified the sort order for the City column to be descending (from
highest to lowest value). The sort order for the Country column is still ascending. We could be more explicit
about this by writing
SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM Employees
ORDER BY Country ASC, City DESC

but this is not necessary and is rarely done. The results returned by this query are

It is important to note that a column does not need to be included in the list of selected (returned) columns in
order to be used in the ORDER BY clause. If we don't need to see/use the Country values, but are only interested
in them as the primary sorting field we could write the query as
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
ORDER BY Country ASC, City DESC

with the results being sorted in the same order as before:

SQL data types:

The columns named 8, 9, 10 and 11 indicates SQL Server version data type support where
8 = SQL Server 2000
9 = SQL Server 2005
10 = SQL Server 2008
11 = SQL Server 2012

DATATYPE

MIN

MAX

STORAGE

8 9 10 11 TYPE

NOTES

Bigint

-2^63

2^63-1

8 bytes

Exact

Int

-2,147,483,648

2,147,483,647

4 bytes

Exact

Smallint

-32,768

32,767

2 bytes

Exact

Tinyint

255

1 bytes

Exact

Bit

1 to 8 bit columns in
the same table requires
a total of 1 byte, 9 to
16 bits = 2 bytes, etc...

Exact

Decimal

-10^38+1

10^381

Precision 1-9 = 5 bytes,

precision 10-19 = 9
bytes, precision 20-28 =
13 bytes, precision 2938 = 17 bytes

Exact

Numeric

same as Decimal

same as Decimal

same as Decimal

Exact

Money

-2^63 / 10000

2^63-1 / 10000

8 bytes

Exact

Smallmoney

-214,748.3648

214,748.3647

4 bytes

Exact

Float

-1.79E + 308

1.79E + 308

4 bytes when precision

is less than 25 and 8
bytes when precision is
25 through 53

Approx

Precision is specified from 1 to 53.

Real

-3.40E + 38

3.40E + 38

4 bytes

Approx

Precision is fixed to 7.

Datetime

1753-01-01
00:00:00.000

9999-12-31
23:59:59.997

8 bytes

Datetime If you are running SQL Server 2008

or later and need milliseconds
precision, use datetime2(3) instead
to save 1 byte.

Smalldatetime

1900-01-01 00:00

2079-06-06 23:59

4 bytes

Datetime

Date

0001-01-01

9999-12-31

3 bytes

no no

Datetime

Time

00:00:00.0000000

23:59:59.9999999

time(0-2) = 3 bytes,
time(3-4) = 4 bytes,
time(5-7) = 5 bytes

no no

Datetime Specifying the precision is possible.

TIME(3) will have milliseconds
precision. TIME(7) is the highest
and the default precision. Casting
values to a lower precision will

The Decimal and the Numeric data

type is exactly the same. Precision
is the total number of digits. Scale
is the number of decimals. For
both the minimum is 1 and the
maximum is 38.

DATATYPE

MIN

MAX

STORAGE

8 9 10 11 TYPE

NOTES
round the value.

Datetime2

0001-01-01
00:00:00.0000000

9999-12-31
23:59:59.9999999

Presicion 1-2 = 6 bytes

precision 3-4 = 7 bytes
precision 5-7 = 8 bytes

no no

Datetime Combines the date datatype and

the time datatype into one. The
precision logic is the same as for
the time datatype.

Datetimeoffset

0001-01-01
00:00:00.0000000 14:00

9999-12-31
23:59:59.9999999
+14:00

Presicion 1-2 = 8 bytes no no

precision 3-4 = 9 bytes
precision 5-7 = 10 bytes

Datetime Is a datetime2 datatype with the

UTC offset appended.

Char

0 chars

8000 chars

Defined width

String

Fixed width

Varchar

0 chars

8000 chars

2 bytes + number of
chars

String

Variable width

Varchar(max)

0 chars

2^31 chars

2 bytes + number of
chars

String

Variable width

Text

0 chars

2,147,483,647 chars 4 bytes + number of

chars

String

Variable width

Nchar

0 chars

4000 chars

Unicode

Fixed width

Nvarchar

0 chars

4000 chars

Unicode

Variable width

Nvarchar(max)

0 chars

2^30 chars

Unicode

Variable width

Ntext

0 chars

1,073,741,823 chars

Unicode

Variable width

Binary

0 bytes

8000 bytes

Binary

Fixed width

Varbinary

0 bytes

8000 bytes

Binary

Variable width

Varbinary(max)

0 bytes

2^31 bytes

Binary

Variable width

Image

0 bytes

2,147,483,647 bytes

Binary

Variable width. Prefer to use the

varbinary(max) type as the image
type will be removed in future
versions.

Other

Stores values of various SQL

Server-supported data types,
except text, ntext, and timestamp.

no

Defined width x 2

no

no

Sql_variant

Timestamp

8 bytes

Other

Stores a database-wide unique

number that gets updated every
time a row gets updated.

Uniqueidentifier

16 bytes

Other

Stores a globally unique identifier

(GUID).

Other

Stores XML data. You can store xml

instances in a column or a variable.

Xml

no

Creating Tables:
The create table statement is used to create a new table. Here is the format of a simple create table statement:
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Format of create table if you were to use optional constraints:
create table "tablename"
("column1" "data type"
[constraint],
"column2" "data type"
[constraint],
"column3" "data type"
[constraint]);
[ ] = optional
Note: You may have as many columns as you'd like, and the constraints are optional.
Example:
create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));
To create a new table, enter the keywords create table followed by the table name, followed by an open
parenthesis, followed by the first column name, followed by the data type for that column, followed by any
optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open
parenthesis before the beginning table and a closing parenthesis after the end of the last column definition.
Make sure you separate each column definition with a comma. All SQL statements should end with a ";".
The table and column names must start with a letter and can be followed by letters, numbers, or underscores not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or
column names (such as "select", "create", "insert", etc.).
Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to
be used to hold names, then that particular column should have a "varchar" (variable-length character) data
type.

Exercise:
Table: EMP
EMPNO
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

Table: DEPT
ENAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

JOB
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK

MGR
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782

HIREDATE
17-Dec-80
20-Feb-81
22-Feb-81
2-Apr-81
28-Sep-81
1-May-81
9-Jun-81
9-Dec-82
17-Nov-81
8-Sep-81
12-Jan-83
3-Dec-81
3-Dec-81
23-Jan-82

SAL
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300

COMM
300
500
1400

DEPTNO
20
30
30
20
30
30
10
20
10
30
20
30
20
10

DEPTNO
10
20
30
40

DNAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS

Table: SALGRADE
GRADE
LOSAL
1
2
3
4
5

LOC
NEW YORK
DALLAS
CHICAGO
BOSTON

HISAL
700
1201
1401
2001
3001

1200
1400
2000
3000
9999

Display ENAME, JOB from table EMP

Display ENAME, SAL from table EMP
Display all columns and rows from table EMP
Display all columns and rows from table DEPT
Display ENAME and ANNUAL SALARY from table EMP (Annual Salary is 12times SAL)
Display current user name
Where clause / operators
Display ENAME for employees working in department 10
Display EMPNO, ENAME for employees who earn commission
Display ENAME for employees who do not earn any commission
Display the names of employees who are not working as MANAGER
Display those employees whose salary is more than 3000 after giving 20% increment
Display names of all employees working as CLERK and drawing a salary more than 3000
Display names of employees who are working as CLERK, SALESMAN or ANALYST and drawing salary more
than 3000
Display the names of employees working in department number 10 or 20 or 40 or employees working as
CLERK, SALESMAN or ANALYST
Display the names of employees who are not working as SALESMAN or CLERK or ANALYST
Display the names of employees who are working in the company for the past 5 years
Display the list of employees who have joined the company before 30th June 90 or after 31st Dec 90
Display names of employees from table EMP whose name starts with letter S
Display names of employees from table EMP whose name ends with letter S
Display names of employees from table EMP having second letter A in their JOB
Display names of employees whose name is exactly five characters in length
Display names of employees whose job start with letter A, ends with letter S and is 4 characters in length
Display those department whose name start with"S" while location name ends with "K"

Order by clause
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

Display details of all the employees and output should have highest comm row displayed at end
Display details of all the employees and output should have highest comm row displayed on top
Display the names of employees in order of commission(comm) i.e. the name of the employee earning
lowest commission(comm) should appear first
Display the names of employees in order of commission(comm) i.e. the name of the employee earning
highest commission(comm) should appear last
Display the names of employees in descending order of commission(comm) i.e., the name of the
employee earning highest commission(comm) should appear first
Display the names of employees in descending order of commission(comm) i.e., the name of the
employee earning lowest commission(comm) should appear last
Display the details from EMP table in order of employee name
Display EMPNO, ENAME, DEPTNO and SAL. Sort the output based on DEPTNO and then by SAL
Display employee details from EMP table. Sort the output based on DEPTNO (highest to lowest) and then
by SAL (highest to lowest)
Display the name of employees along with their annual salary (SAL*12). The name of the employee
earning highest annual salary should appear first
Display ENAME, SAL, HRA, PF, DA, total salary for each employee. The output should be in the order of
total salary (HRA 15% of SAL, DA 10% of SAL, PF 5% of SAL and Total Salary will be (SAL + HRA + DA) PF
Display EMPNO, ENAME, SAL, and DEPTNO from EMP table. Output should be in the order of length of
ename(high to low), if length of ename is same for more employees then sort the output by salary(high to
low) and if more employees has same salary than sort the output by ename
Write a query to display ename from EMP table. MILLER to be displayed in first row and rest in ascending
order

Class 2: Querying Multiple Tables

Here we will discuss the below topics

Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins

Understanding Joins:
SQL join is instruction to a database to combine data from more than one table. There are different kinds of
joins, which have different rules for the results they create.
Let's look at the different kinds of SQL joins:
INNER JOIN:
An inner join produces a result set that is limited to the rows where there is a match in both tables for what
we're looking for. If you don't know which kind of join you need, this will usually be your best bet.
Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners INNER JOIN Plantings ON gid = gardener_id

LEFT OUTER JOIN

A left outer join, or left join, results in a set where all of the rows from the first, or left hand side, table are
preserved. The rows from the second or right hand side table only show up if they have a match with the rows
from the first table. Where there are values from the left table but not from the right, the table will read null,
which means that the value has not been set.
Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners LEFT OUTER JOIN Plantings ON gid = gardener_id

Right Outer Join:

A right outer join, or right join, is the same as a left join, except the roles are reversed. All of the rows from the
right hand side table show up in the result, but the rows from the table on the left are only there if they match
the table on the right. Empty spaces are null, just like with the the left join.
Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners RIGHT OUTER JOIN Plantings ON gid = gardener_id

FULL OUTER JOIN

A full outer join, or just outer join, produces a result set with all of the rows of both tables, regardless of
whether there are any matches. Similarly to the left and right joins, we call the empty spaces null.
Example
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners FULL OUTER JOIN Plantings ON gid = gardener_id

Cross Join:
The cross join returns a table with a potentially very large number of rows. The row count of the result is equal
to the number of rows in the first table times the number of rows in the second table. Each row is a combination
of the rows of the first and second table.
Example:
SELECT gid, first_name, last_name, pid, gardener_id, plant_name
FROM Gardners CROSS JOIN Plantings

SELF JOIN
You can join a single table to itself. In this case, you are using the same table twice.
Example:
SELECT G1.gid, G1.first_name, G1.last_name, G2.gid, G2.first_name, G2.last_name
FROM Gardners G1 INNER JOIN Gardners G2 ON G1.first_name = G2.first_name

Exercise:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

Display all employees with their department names

Display ename who are working in sales department
Display ENAME, DNAME, SAL and COMM for employees with salary between 2000 to 5000 and location is
Chicago
Display those employees whose salary is greater than his managers salary
Display those employees who are working in the same dept where his manager is work
Display those employees who are not working under any Manager
Display ENAME, GRADE (deptno 10 or 30) (grade is not 4) (joined company before 31-DEC-82)
Delete employees joined company before 31-Dec-82 while their Location is New York or Chicago?
Display employee name ,job,deptname,loc for all who are working as manager?
Display those employees whose manager name is JONES and also display their manager name?
Display employee names who are working in ACCOUNTING department
Display the employee names who are working in CHICAGO
Display name and salary of FORD if his salary is equal to hisal of his grade?
Display employees whose salary is less than his manager but more than salary of other managers?
Display those employees whose manager name is Jones
Display the details of those employees who do not have any person working under him
Display the details of those employees who are in sales department and grade is 3

18.
19.
20.
21.
22.
23.

Display those department where no employee working?

Display ename, job and his manager. Display also employees who are without managers?
Find out the number of employees whose salary is greater than their managers salary?
Display ename, job, dname, his manager name, his grade and display output department number wise?
List ename, job, sal, grade and dname for everyone in a company except 'CLERK'. Sort on salary display
the highest salary first?
Display ENAME, Manager Name for employees who has EMPNO as odd number; Display ENAME,
Managers Manager Name for all the employees who has EMPNO as even number. If Managers Manager
is not present display Manager Name and display employee name if no manager is present for that
employee. Output should consist of two columns: 1-ENAME, 2-BOSS