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.
Following is the example of a CUSTOMERS table: +----+---+---+---+---+
|ID |NAME |AGE |ADDRESS |SALARY | +----+---+---+---+---+ | 1|Ramesh | 32|Ahmedabad| 2000.00| | 2|Khilan | 25|Delhi | 1500.00| | 3|kaushik | 23|Kota | 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 comma-separated 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
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 appear—sorting 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 by—the 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
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 0 255 1 bytes Exact
Bit 0 1 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^38–1 Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes
Exact 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.
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
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 precision 3-4 = 9 bytes precision 5-7 = 10 bytes
no no 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 no String Variable width
Text 0 chars 2,147,483,647 chars 4 bytes + number of
chars String Variable width
Nchar 0 chars 4000 chars Defined width x 2 Unicode Fixed width
Nvarchar 0 chars 4000 chars Unicode Variable width
Nvarchar(max) 0 chars 2^30 chars no 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 no 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.
Sql_variant Other Stores values of various SQL
Server-supported data types, except text, ntext, and timestamp.
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).
Xml no Other Stores XML data. You can store xml
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.
1. Display ENAME, JOB from table EMP 2. Display ENAME, SAL from table EMP
3. Display all columns and rows from table EMP 4. Display all columns and rows from table DEPT
5. Display ENAME and ANNUAL SALARY from table EMP (Annual Salary is 12times SAL) 6. Display current user name
Where clause / operators
7. Display ENAME for employees working in department 10 8. Display EMPNO, ENAME for employees who earn commission 9. Display ENAME for employees who do not earn any commission 10. Display the names of employees who are not working as MANAGER
11. Display those employees whose salary is more than 3000 after giving 20% increment 12. Display names of all employees working as CLERK and drawing a salary more than 3000
13. Display names of employees who are working as CLERK, SALESMAN or ANALYST and drawing salary more than 3000
14. Display the names of employees working in department number 10 or 20 or 40 or employees working as CLERK, SALESMAN or ANALYST
15. Display the names of employees who are not working as SALESMAN or CLERK or ANALYST 16. Display the names of employees who are working in the company for the past 5 years
17. Display the list of employees who have joined the company before 30th June 90 or after 31st Dec 90 18. Display names of employees from table EMP whose name starts with letter S
19. Display names of employees from table EMP whose name ends with letter S 20. Display names of employees from table EMP having second letter A in their JOB 21. Display names of employees whose name is exactly five characters in length
22. Display names of employees whose job start with letter A, ends with letter S and is 4 characters in length 23. Display those department whose name start with"S" while location name ends with "K"
Exercise:
Table: EMP Table: DEPT
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7369 SMITH CLERK 7902 17-Dec-80 800 20 10 ACCOUNTING NEW YORK 7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 2-Apr-81 2975 20 40 OPERATIONS BOSTON 7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2850 30 7782 CLARK MANAGER 7839 9-Jun-81 2450 10
7788 SCOTT ANALYST 7566 9-Dec-82 3000 20 Table: SALGRADE
7839 KING PRESIDENT 17-Nov-81 5000 10 GRADE LOSAL HISAL
7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30 1 700 1200
7876 ADAMS CLERK 7788 12-Jan-83 1100 20 2 1201 1400
7900 JAMES CLERK 7698 3-Dec-81 950 30 3 1401 2000
7902 FORD ANALYST 7566 3-Dec-81 3000 20 4 2001 3000
Order by clause
24. Display details of all the employees and output should have highest comm row displayed at end 25. Display details of all the employees and output should have highest comm row displayed on top 26. Display the names of employees in order of commission(comm) i.e. the name of the employee earning
lowest commission(comm) should appear first
27. Display the names of employees in order of commission(comm) i.e. the name of the employee earning highest commission(comm) should appear last
28. 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
29. 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
30. Display the details from EMP table in order of employee name
31. Display EMPNO, ENAME, DEPTNO and SAL. Sort the output based on DEPTNO and then by SAL
32. Display employee details from EMP table. Sort the output based on DEPTNO (highest to lowest) and then by SAL (highest to lowest)
33. Display the name of employees along with their annual salary (SAL*12). The name of the employee earning highest annual salary should appear first
34. 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 35. 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
36. 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. Display all employees with their department names 2. Display ename who are working in sales department
3. Display ENAME, DNAME, SAL and COMM for employees with salary between 2000 to 5000 and location is Chicago
4. Display those employees whose salary is greater than his managers salary
5. Display those employees who are working in the same dept where his manager is work 6. Display those employees who are not working under any Manager
7. Display ENAME, GRADE (deptno 10 or 30) (grade is not 4) (joined company before 31-DEC-82) 8. Delete employees joined company before 31-Dec-82 while their Location is New York or Chicago? 9. Display employee name ,job,deptname,loc for all who are working as manager?
10. Display those employees whose manager name is JONES and also display their manager name? 11. Display employee names who are working in ACCOUNTING department
12. Display the employee names who are working in CHICAGO
13. Display name and salary of FORD if his salary is equal to hisal of his grade?
14. Display employees whose salary is less than his manager but more than salary of other managers? 15. Display those employees whose manager name is Jones
16. Display the details of those employees who do not have any person working under him 17. Display the details of those employees who are in sales department and grade is 3
18. Display those department where no employee working?
19. Display ename, job and his manager. Display also employees who are without managers? 20. Find out the number of employees whose salary is greater than their managers salary?
21. Display ename, job, dname, his manager name, his grade and display output department number wise? 22. List ename, job, sal, grade and dname for everyone in a company except 'CLERK'. Sort on salary display
the highest salary first?
23. Display ENAME, Manager Name for employees who has EMPNO as odd number; Display ENAME,
Manager’s Manager Name for all the employees who has EMPNO as even number. If Manager’s 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
