Getting dates between period

SELECT   (TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1) "DT",
         TO_CHAR ((TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1),
                  'DD'
                 ) "DD",
         TO_CHAR ((TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1),
                  'MM'
                 ) "MM",
         TO_CHAR ((TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1),
                  'RRRR'
                 ) "RRRR",
         TO_CHAR (TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1,
                  'DY') "DD"
    FROM all_objects
   WHERE ROWNUM <=
              TO_DATE (:end_date, 'dd-mon-yyyy')
            - TO_DATE (:start_date, 'dd-mon-yyyy')
            + 1
ORDER BY TO_DATE (:start_date, 'dd-mon-yyyy') + ROWNUM - 1

Oracle SQL query to select only all words in capitals

select * from
(select REGEXP_SUBSTR (ename, '[^ ]+', 1, level) word
from ename
connect by level <= length(regexp_replace(ename,'[^ ]*'))+1 ) where regexp_instr(word,'[A-Z]') > 0;

Oracle Sql query to display half employee name in upper case & other half in lower case

SELECT ename,
LOWER (SUBSTR (ename, 1, ROUND (LENGTH (ename) / 2)))
|| UPPER (SUBSTR (ename, ROUND (LENGTH (ename) / 2) + 1, LENGTH (ename)))
a
FROM emp;

SQL Views

SQL Modifying View

SQL Join View

SQL Inline View

An SQL View is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

The maximum number of columns that can be defined in a SQL View are 1000 as in tables.

The reasons for using views (benefits / advantages of views) in applications can be many like;

• Reducing complexity.
• Improving security.
• Renaming the table columns.

Use Create View statement to create a view:

SQL> create view emp_info as
2 select first_name ' ' last_name Name, employee_id
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)

SQL> select * from emp_info;

Name	Employee_ID
Michael Hartstein	201
Pat Fay	202

You can also define altogether a new column in a view, as column commission below:

SQL> create view emp_info(Name, employee_id, commission) as
2 select first_name ' ' last_name ,employee_id, salary * .01
3 from employees where department_id = 20;
View created.

SQL> desc emp_info;

Name	Null?	Type
NAME		VARCHAR2(46)
Employee_ID	NOT NULL	NUMBER(6)
COMMISSION	NOT NULL	NUMBER

SQL> select * from emp_info;

Name	Employee_ID	COMMISSION
Michael Hartstein	201	130
Pat Fay	202	60

You can also create Read-Only views. On the read-only views no DML operations can be performed.

Create view emp_info(Name, employee_id, commission) as
select first_name ' ' last_name ,employee_id, salary * .01
from employees where department_id = 20
With Read only;

SQL Having

SQL Group By

Examples of SQL Group By and SQL Having

Null in SQL Group By

The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.

Group functions cannot be used in the WHERE clause. SQL statement can have both a WHERE clause and an HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.

A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.

According to Wikipedia (http://en.wikipedia.org) HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions.

An SQL statement with the HAVING clause may or may not include the GROUP BY clause.

HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.

Example 1 of SQL Having

Find the average salary of for each department that has either more than 1 employee or starts with a “To”:

SELECT Dept, AvgSal=(AVG(Salary))
FROM Employee
GROUP BY Dept
HAVING COUNT(Name) > 1 OR Dept LIKE “To”

Example 2 of SQL Having

Workforce (workforceno, name, position, salary, email, dcenterno)

For each distribution center with more than one member of workforce, find the number of workforce working in each of the centers and the sum of their salaries.

SELECT dCenterNo, COUNT(workforceNo) AS totalworkforce,
SUM(salary) AS totalSalary
FROM workforce
GROUP BY dCenterNo
HAVING COUNT(workforceNo) > 1
ORDER BY dCenterNo;

Important points about SQL Having:

Aggregates cannot be used in a WHERE clause; they are used only inside HAVING.

Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.

Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.

SQL Sub Queries

SQL inline View subquery

SQL Correlated subquery

SQL Scalar subquery

Subquery is a query inside a main query.

SQL subquery can be embedded:

• As a column expression in the main SQL statement
  
• As a filter inside the WHERE (or HAVING) clause in the main SQL statement
  
• As a datasource inside the FROM clause in the main SQL statement

While working with SQL Subqueries you must:

• Enclose the subquery in parentheses
  
• Do not use a semicolon at the end of the subquery statement

Example 1 of subquery

Find the names of all of the managers in the Employees table.

Employees (emp_id, Emp_name, ManagerID)

SELECT Emp_name AS Employee
FROM Employees
WHERE emp_id IN (SELECT DISTINCT ManagerID FROM Employees)

Example 2 of SQL Subquery

Store_Info (store_name, sales, date)
Location (region_names, store_name)

Find the sales of all stores in the East region.

SELECT SUM(Sales) FROM Store_Info
WHERE Store_name IN
(SELECT store_name FROM Location
WHERE region_name = 'East')

You can nest any number of such queries; Oracle does not have a limit.

There is also another term used NESTED SUBQUERIES. when you use subqueries in the WHERE clause of the SELECT statement it is called nested subquery. There are only 255 levels of subqueries.

SQL Groups

SQL Having

Examples of SQL Group By and SQL Having

Null in SQL Group By

It is a clause in SQL, which specifies how to report the output of the query. Allows one to define a subset of the values of a particular field and to apply an aggregate function to the subsets.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc).

Example 1 of SQL Group BY

Calculate the total sales for each store in the following table

store_name	Sales	Date
London	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
London	$300	Jan-08-1999
Boston	$700	Jan-08-1999

First, we need to make sure we select the store name as well as total sales.

SELECT store_name, SUM (Sales)
FROM Store_Information

Second, we need to make sure that all the sales figures are grouped by stores.

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

So the final query is:

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

The result is:

store_name	SUM(Sales)
London	$1800
San Diego	$250
Boston	$700

Example 2 of SQL Group BY

SELECT COUNT (*) FROM t_state

The above statement returns the total number of rows in the table. We can use GROUP BY to count the number of offices in each state.

With GROUP BY, the table is split into groups by state, and COUNT (*) is applied to each group in turn.

SELECT state, COUNT (*)
FROM t_state
GROUP BY state;

Important points to remember:

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department, count (*) as cnt
From emp
Group by department

Correct way is:

Select deptno as department, count (*) as cnt
From emp
Group by deptno


What is the difference between the outputs of the following two queries?

Statement 1:

SELECT COUNT (*), SUM (comm)
FROM hr.employees;

Statement 2:

SELECT COUNT (comm), SUM (comm)
FROM hr.employees;

The COUNT (*) will count all rows in the table.

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

Restriction on SELECT Lists with Aggregation

If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.

SQL Joins

You will get vast amount of data/ information on this page regarding Oracle SQL JOINS like

1. Example of Simple SQL Join


2. SQL Outer Join


3. Example of SQL Outer Join


4. SQL Inner Join


5. Example of SQL inner Join


6. SQL Self Join


7. Example of SQL Self Join


8. SQL Cross Join


9. SQL Equijoin


10. Join using MULTIPLE TABLES

You will get good information on Joins from the book Effective Oracle by Design by Thomas Kyte

http://www.amazon.com/Effective-Oracle-Design-Osborne-ORACLE/dp/0072230657

If you are interested in DBMS Normal forms Please go to

http://www.bkent.net/Doc/simple5.htm

Here you will find an article on DBMS Normal forms named "A Simple Guide to Five Normal Forms in Relational Database Theory" which you will surely find useful.

SQL Group BY Examples

Example 1 of SQL Group By

Let us say we have a table name Orders.

Orders (O_Id, OrderDate, OrderPrice, Customer)

we want to find the total sum (total order) of each customer.

SELECT Customer,SUM(OrderPrice)
FROM Orders
GROUP BY Customer

Source: http://www.w3schools.com/sql/sql_groupby.asp


Example 2 of SQL Group By

Let us say we have a table name Sales.

Sales(OrderID, OrderDate, OrderPrice, OrderQuantity, CustomerName)

We want to retrieve a list with unique customers from our Sales table, and at the same time to get the total amount each customer has spent in our store.

SELECT CustomerName, SUM(OrderPrice)
FROM Sales
GROUP BY CustomerName

Source: http://www.sql-tutorial.com/sql-group-by-sql-tutorial/


Example 3 of SQL Group By

Returns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.

SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID

Source: http://en.wikipedia.org/wiki/Group_by_(SQL)


Example 4 of SQL Group By

From Sells(bar, beer, price) find the average price for each beer

SELECT beer, AVG(price)
FROM Sells
GROUP BY beer;

Source: infolab.stanford.edu/~ullman/fcdb/aut07/slides/ra-sql2.ppt


Example 5 of SQL Group By

You could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Source: http://www.techonthenet.com/sql/group_by.php

SQL Delete

How to delete all records from a table?

Delete from dept;

How to delete specific records from a table?

Delete from emp where empno=20;

How to delete duplicate records from the table?

Suppose we have a table t1(id integer, name varchar(10))

select * from t1;

id	name
1	aaa
2	bbb
3	bbb
4	ccc
5	ccc
6	ddd

delete from t1

where id not in ( select min(id)

from t1

group by name )

Few Examples on SQL Delete:

BOTH THE BELOW EXAMPLES OF UPDATE AND DELETE USE CORRELATED SUBQUERIES:

We need to update sal of all the employees in the emp table to the maximum salary in the corresponding dept.

UPDATE emp e1

SET sal = (SELECT MAX(sal)FROM emp e2

            WHERE e1.deptno = e2.deptno);

We need To delete the records of all the employees in the emp table whose sal is below the average sal in the department

DELETE FROM emp e

WHERE sal < (SELECT AVG(sal) FROM emp

                     WHERE deptno = e.deptno);

SQL Interview Question

What Is SQL?
SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.

What is the difference between database & relational database.

Database or Database Management System(DBMS) and Relational Database Management System(DBMS) are both used by SQL to store data and structures. However, each type of Database Management System is preferred with respect to different uses.

The main difference between the two is that DBMS saves your information as files whereas RDMS saves your information in tabular form. Also, as the keyword Relational implies, RDMS allows different tables to have relationships with one another using Primary Keys, Foreign Keys etc. This creates a dynamic chain of hierarchy between tables which also offers helpful restriction on the tables.

DBMS sorts out its tables through a hierarchal manner or navigational manner. This is useful when it comes to storing data in tables that are independent of one another and you don’t wish to change other tables while a table is being filled or edited.

What are different categories of SQL commands?

SQL command falls into following four categories:

• DML (Data Manipulation Language) which provides data manipulation features
• DDL (Data Definition Language) which is used to manipulate database structures
• TCL (Transaction Control Language) that takes in charge data transaction verification and error handling
• DCL (Data Control Language) are security statements that feature user restrictions and data access permissions to promote security of your data.

What is the Basic Structure of an SQL?

SQL is framed upon the structure of relational operations. It is based on certain modifications and enhancements.

A very basic SQL query form is: select A1, A2, ..., An from R1, R2, ..., Rm where P;

Here An are attributes, Rm is the relations within the database and P is the predicate or filter.

Define SELECT, INSERT, CREATE, DELETE, UPDATE, DROP keywords

SELECT keyword is used to highlight and get entries in rows from tables or views. It can also be accompanied by AS keyword to provide an alias. To filter the SELECT statement, WHERE clause may be included to provide filter conditions and select only the wished entries that satisfy the condition.

 

INSERT allows to add or insert a row or multiple rows in a database table. Accompanied by VALUES keyword lets you add a row with specific values. INSERT may also be accompanied with SELECT to insert the preselected row.

CREATE is a keyword used to create elements in SQL. It is usually accompanied with the keyword to be created such as CREATE DATABASE, CREATE TABLE, CREATE VIEW, etc.

DELETE keyword is used to deletes record(s) in a database. You should always use it carefully to avoid unwanted data loss. You may delete records you didn’t want to delete. Use WHERE clause to specify the range of the records you wish to delete.

UPDATE keyword updates or changes the existing data within an existing record. Be sure to note that the record must be existent.

DROP keyword drops or deletes a table within the database.

Which command displays the SQL command in the SQL buffer, and then executes it?

RUN

What are the key differences between SQL and P/L SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early ’90s. It adds procedural features of programming languages in SQL.

What is data definition language?

DDL or Data Definition Language pertains to the SQL commands directly affecting the database structure. DDL is a category of SQL command classifications that also include DML (Data Manipulation Language), Transactions, and Security. A particular attribute of DDL commands is statements that can manipulate indexes, objects, tables, views, triggers, etc. Three popular DDL keywords in SQL are:

CREATE – which is used to create a table

CREATE TABLE tableName (name data_type);

ALTER – used to modify entries or existing columns within a table.

ALTER TABLE tableName [additional syntax such as ADD, DROP, MODIFY]

DROP – used to Delete or Drop an existing table along with its entries, constraints, triggers, indexes, and permissions. Essentially deletes the table.

DROP TABLE tableName;

What is Data Manipulation Language?

DML or Data Manipulation Language is a set of commands that are classified pertaining to its capability to give users permission to change entries within the database. This may be through Inserting, Retrieving, Deleting or Updating data within tables. Popular DML statements arise from these core functionalities and are listed below:

 

SELECT – used to highlight a row within a table and retrieve it.

SELECT [columnName] FROM [tableName]

UPDATE – used to update entries from existing tables.

UPDATE [tableName] SET [value]

INSERT – used to insert entries into an existing table.

INSERT INTO [tableName]

DELETE – used to delete entries from an existing table

DELETE FROM [tableName]

What is Transaction Control Language (TCL)?

TCL is a category of SQL commands which primarily deals with the database transaction and save points. These keywords implement the SQL functions and logic defined by the developer into the database structure and behavior. Examples of these TCL commands are:

COMMIT – used to commit a transaction

ROLLBACK – in any advent of errors, transaction rollback is invoked by this keyword.

SAVEPOINT – keyword representing the reverting point of rollback

SET TRANSACTION – sets the specifics of the transaction

What is Data Control Language (DCL)?

Data Control Language or DCL oversees the issuance of access and restrictions to users, including the rights and permissions required within the SQL statements. Example DCL keywords are:

GRANT – DCL keyword that provides access to certain databases to users.

REVOKE – opposite of the GRANT keyword. Revokes or withdraws the privileges given to the user.

Define tables and fields in a database

In terms of databases, a table is referred to as an arrangement of organized entries. It is further divided into cells which contain different fields of the table row.

A field pertains to a data structure that represents a single piece of entry. They are then further organized to records. They practically hold a single piece of data. They are the basic unit of memory allocation for data and is accessible.

What are different types of keys in SQL?

Keys are a vital feature in RDMS, they are essentially fields that link one table to another and promote fast data retrieval and logging through managing column indexes.

Different types of keys are:

Primary Key – a unique key that identifies records in database tables. By unique it means that it must not be Null and must be unique in the table.

Candidate Key – a unique field which identifies for column or group of columns independently, without any required reference to other fields.

Alternate Key – can be substituted in use for Primary Keys but are considered as a secondary. The difference is that Alternate Keys can have a Null value, provided that the columns have data within them. A type of Candidate Key which is also required to be unique.

Unique Key – Keys that offer restriction to prevent duplicate data within rows except for null entries.

The other keys available are Foreign Keys, Super Keys, and Composite Keys.

Name the different types of indexes in SQL and define them.

Unique Index: Prevents duplicate entries within uniquely indexed columns. They are automatically generated if a Primary Key is available.

Clustered Index: Used to organize or edit the arrangement within the table, with respect to the key value. Each table is only allowed to have a single clustered index only.

NonClustered Index: Conversely, NonClustered Index only manages the order of logic within entries. It does not manage the arrangement and tables can have multiple NonClustered Indexes.

How can you call a PL/SQL procedure from SQL? 

By use of the EXECUTE (short form EXEC) command.

Which is the subset of SQL commands used to manipulate Oracle Database structures?

Data Definition Language (DDL)

What is UNION and UNION ALL keyword in SQL and what are their differences?

The UNION operator in SQL combines multiple sets highlighted in the SELECT statements. The restrictions of the set are: (1) column number must be identical, (2) Data Types in the set must be the same, and (3) the order of the column highlighted in the SELECT statement must be the same. It automatically eliminates duplicate rows within the results highlighted in the SELECT statement.

UNION ALL does the same function as the UNION, but it includes all, including the duplicate rows.

SELECT C1, C2 FROM T1

UNION

SELECT Cx, Cy FROM T2;

What are the different types of joins in SQL?

The join keyword queries entries from multiple tables. It is used with different keys to find these entries and is conscious on the link between fields.

Inner Join: Returns rows which are common between the tables

Right Join: Returns rows of the right-hand side table, including the common rows.

Left Join: Returns rows of the left-hand side table, including the common rows.

Full Join: Returns all rows, regardless if common or not.

 

Write a query to select the second highest salary from a table.

SELECT max(salary) AS salary_2 FROM emp WHERE salary < (SELECT max(salary) AS salary_1 FROM emp) 

Write a query to select the 5th highest salary from a table.

SELECT min(salary) AS high5 
FROM employee 
WHERE salary IN(SELECT DISTINCT TOP 5 salary FROM employee ORDER BY salary DESC) 

How to find duplicate records with the number they are duplicated?

SELECT Id, count (*) as num_records
from table 
group by id 
having count (*) > 1

What is the difference among UNION, MINUS and INTERSECT?

The UNION keyword is used in SQL for combining multiple SELECT queries but deletes duplicates from the result set.

The INTERSECT keyword is only used for retrieving common rows using SELECT queries between multiple tables.

The MINUS keyword essentially subtracts between two SELECT queries. The result is the difference between the first query and the second query. Any row common across both the result set is removed from the final output.

What command is used to get back the privileges offered by the GRANT command? 

 REVOKE

How do you execute a host operating system command from within SQL? 

 By use of the exclamation point “!” (in UNIX and some other OS) or the HOST (HO) command. 

 What command is used to create a table by copying the structure of another table? 

 CREATE TABLE .. AS SELECT command

What is the difference between Delete and Truncate command in SQL? 

Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be. TRUNCATE is a DDL command whereas DELETE is a DML command. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate. 

How can variables be passed to a SQL routine?

By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: “select * from dba_tables where owner=&owner_name;” . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

What SQLPlus command is used to format output from a select? 

This is best done with the COLUMN command. 

Can Primary key is a Foreign Key on the same table? 

Yes 

You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done? 

Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:

 

select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);

What is the use of the DROP option in the ALTER TABLE command? 

It is used to drop constraints specified on the table. 

What is a Cartesian product? 

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

How do you prevent output from coming to the screen? 

The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.

What is the use of CASCADE CONSTRAINTS? 

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Which function is used to find the largest integer less than or equal to a specific value? 

FLOOR 

What is a PRIMARY KEY? 

The PRIMARY KEY is the column(s) used to uniquely identify each row of a table. 

What is a FOREIGN KEY?

A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

What is a UNIQUE KEY? 

A UNIQUE KEY is one or more columns that must be unique for each row of the table.

What is the difference between UNIQUE and PRIMARY KEY? 

The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <null> value is also restricted.

Consider the below DEPT and EMPLOYEE table and answer the below queries. 

DEPT DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)), LOC (VARCHAR2(13)

EMPLOYEE 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. 

List all the Employees who have at least one person reporting to them. 

SELECT ENAME FROM EMPLOYEE WHERE EMPNO IN (SELECT MGR FROM EMPLOYEE); 

List the highest salary paid for each job. 

SELECT JOB, MAX(SAL) FROM EMPLOYEE GROUP BY JOB 

In which year did most people join the company? Display the year and the number of Employees.

SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO))
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY'));

Write a correlated sub-query to list out the Employees who earn more than the average salary of their department.

SELECT ENAME,SAL
FROM EMPLOYEE E
WHERE SAL > (SELECT AVG(SAL)
FROM EMPLOYEE F
WHERE E.DEPTNO = F.DEPTNO);

Find the nth maximum salary.

SELECT ENAME, SAL
FROM EMPLOYEE A
WHERE &N = (SELECT COUNT (DISTINCT(SAL))
FROM EMPLOYEE B
WHERE A.SAL<=B.SAL);  

Select the duplicate records (Records, which are inserted, that already exist) in the EMPLOYEE table.

SELECT * FROM EMPLOYEE A
WHERE A.EMPNO IN (SELECT EMPNO                                         
FROM EMPLOYEE                                         
GROUP BY EMPNO                                          
HAVING COUNT(EMPNO)>1)
AND A.ROWID!=MIN (ROWID));

Write a query to list the length of service of the Employees (of the form n years and m months).

SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))
||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN
(SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE"
FROM EMPLOYEE;

SQL Keywords

SQL keywords are case insensitive. So the keyword SELECT and select will behave in the same way. Let’s look at some of the commonly used SQL keywords.

• AND : AND is an sql operator which is used for adding multiple condition. If the conditions are satisfied by a row. It will be part of the result set.

Syntax	SELECT column_name FROM table_name WHERE column1 = value1 AND column2 = value2;
Output	On execution of this command, if column1 is equal to value1 and column2 is equal to value2 then only the row will be qualified for result set

• AS : AS is used for aliasing a column or table.

Syntax	SELECT AVG(column_name) FROM table_name;
Output	Execution of this command will provide the average of column_name.

• BETWEEN : BETWEEN is an operator which is used for defining a set. The value for range can be numeric, text and date.

Syntax	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Output	Execution of this command will give a result set bounded by value1 and value2.

• COUNT : COUNT is a function which provides the count of the row for a column. The column should be a not null column.

Syntax	SELECT COUNT(column_name) FROM table_name;
Output	On execution of this command, the result will contain the count of the rows for column_name.

• CREATE TABLE : This command is used to create table in the database. It allows to specify the name of the table along with the name a datatype of the column.

Syntax	CREATE TABLE table_name (column1 datatype, column2 datatype);
Output	Execution of this command will result in creation of table with name as table_name along with column1 and column2 as the columns of the table.

• DELETE : DELETE is the command which is used for removing rows from the table.

Syntax	DELETE FROM table_name WHERE column_name = value;
Output	On execution of this command the rows that will be selected based on the condition in the WHERE clause will be deleted.

• GROUP BY : GROUP BY is a clause in SQL that is only used with aggregate functions. It is used along with the SELECT statement to arrange identical data into groups.

Syntax	SELECT COUNT(*) FROM table_name GROUP BY column_name;
Output	Execution of this order will result in grouping the result set based on the column_name.

• INNER JOIN : INNER JOIN will select records with matching values in two tables.

Syntax	SELECT column_name FROM table_1 INNER JOIN table_2 ON table_1.column_name = table_2.column_name;
Output	Above command will result in rows where both tables have matching values for column_name.

• INSERT : INSERT is used for adding a new row in the table.

Syntax	INSERT INTO table_name (column1, column2, column3) VALUES (value1, 'value2', value3);
Output	Execution of this command will result in addition of a new row with values corresponding to the columns.

• LIKE : LIKE is an operator which is used for specifying a pattern. This operator is used along with WHERE clause.

Syntax	SELECT column_name FROM table_name WHERE column_name LIKE pattern;
Output	Output will the rows that will satisfy the like pattern.

• LIMIT : LIMIT is a clause which allows the restricting the result set rows to maximum number specified by the limit clause.

Syntax	SELECT column_name FROM table_name LIMIT number;
Output	The resultset will be limited by the number that is provided as part of the limit clause.

• MAX : MAX is a function that will return the max value from the column specified.

Syntax	SELECT MAX(column_name) FROM table_name;
Output	The output will the maximum value specified in the column column_name.

• MIN : MIN is a function that will return the min value from the column specified.

Syntax	SELECT MIN(column_name) FROM table_name;
Output	The output will the minimum value specified in the column column_name.

• OR : OR is the operator that is used for selecting the rows based on satisfaction of either condition in WHERE clause.

Syntax	SELECT column_name FROM table_name WHERE column_name = value1 OR column_name = value2;
Output	Result set will contain rows where column_name value will be either value1 or value2.

• ORDER BY : ORDER BY is used for sorting of columns in ascending or descending order numerically or in alphabetical order.

Syntax	SELECT column_name FROM table_name ORDER BY column_name ASC;
Output	On execution of the command above we will get the result set in ascending order.

• LEFT JOIN, RIGHT JOIN : These will combine rows from different tables even if the join condition is not met. Every row in the left/right table is returned in the result set, and if the join condition is not met, then NULL values are filled in the columns from the left/right table.

Syntax	SELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;
Output	Execution of the command above will result in rows from the table_1 along with rows which satisfies the condition from table_2
Syntax	SELECT column_name(s) FROM table_1 RIGHT JOIN table_2 ON table_1.column_name = table_2.column_name;
Output	Execution of the command above will result in rows from the table_2 along with rows which satisfies the condition from table_1.

• ROUND : ROUND is a function that rounds of the number specified in the column based on the integer that is specified as part of the function.

Syntax	SELECT ROUND(column_name, integer) FROM table_name;
Output	The output of the command will result in rounding up the number based on the integer that is provided as part of the function.

• SELECT : SELECT is used to fetch the data from the data base.

Syntax	SELECT column_name FROM table_name
Output	On execution of this command the result set will contain rows for column column_name.

• SELECT DISTINCT : SELECT DISTINCT is used for retrieving distinct values from the column that is specified.

Syntax	SELECT DISTINCT column_name FROM table_name;
Output	On execution of the command above the result set will only contain the unique values from the column column_name.

• SUM : SUM is a function will provides the total value of a column which is numeric.

Syntax	SELECT SUM(column_name) FROM table_name;
Output	Execution of this command will result in the total of all the row that are part of the column column_name

• UPDATE : UPDATE is used for updating values of a row of a table.

Syntax	UPDATE table_name SET some_column = some_value WHERE some_column = some_value;
Output	Execution of this command will result in updating the row that will satisfy the condition in the where clause.

• WHERE : WHERE is used for specifying the condition that should be satisfied for selecting the row to be part of the result set.

Syntax	SELECT column_name FROM table_name WHERE column_name operator value;
Output	The output of this command will result in the rows that are satisfying the where clause.

SQL Datatypes & SQL Operator

SQL Datatypes

SQL Data Type is an attribute that specifies the type of data of any object. Every column is required to have a name and data type in the database table. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement. SQL offers six categories of data types for your use which are listed below

1.   Binary Datatypes

There are Three types of binary Datatypes which are given below:

binary	It has a maximum length of 8000 bytes. It contains fixed-length binary data.
varbinary	It has a maximum length of 8000 bytes. It contains variable-length binary data..
image	It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.

2.   Approximate Numeric Datatype

The subtypes are given below:

float	Range from -1.79E + 308 to 1.79E + 308. It is used to specify a floating-point value e.g. 6.2, 2.9 etc.
real	Range from -3.40e + 38 to 3.40e + 38. It specifies a single precision floating point number.

3.   Exact Numeric Datatype

The subtypes are given below:

int	It is used to specify an integer value.
smallint	It is used to specify small integer value.
bit	It has the number of bits to store.
decimal	It specifies a numeric value that can have a decimal number.
numeric	It is used to specify a numeric value.

4.   Character String Datatype

The subtypes are given below:

char	It has a maximum length of 8000 characters. It contains Fixed-length non-unicode characters.
varchar	It has a maximum length of 8000 characters. It contains variable-length non-unicode characters.
text	It has a maximum length of 2,147,483,647 characters. It contains variable-length non-unicode characters.

5.   Date and time Datatypes

The subtypes are given below:

date	It is used to store the year, month, and days value.
time	It is used to store the hour, minute, and second values.
timestamp	It stores the year, month, day, hour, minute, and the second value.

SQL Operators

SQL Data Type is an attribute that specifies the type of data of any object. Every column is required to have a name and data type in the database table. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement. SQL offers six categories of data types for your use which are listed below

1.   SQL Arithmetic Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

+	It adds the value of both operands. i.e. a+b will give 30
-	It is used to subtract the right-hand operand from the left-hand operand. i.e. a-b will give 10
*	It is used to multiply the value of both operands. i.e. a*b will give 200
/	It is used to divide the left-hand operand by the right-hand operand. i.e. a/b will give 2
%	It is used to divide the left-hand operand by the right-hand operand and returns reminder. i.e a%b will give 0

2.   SQL Comparison Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

=	It checks if two operands values are equal or not, if the values are queal then condition becomes true. Example: (a=b) is not true
!=	It checks if two operands values are equal or not, if values are not equal, then condition becomes true. Example: (a!=b) is true
<>	It checks if two operands values are equal or not, if values are not equal then condition becomes true. Example: (a<>b) is true
>	It checks if the left operand value is greater than right operand value, if yes then condition becomes true. Example: (a>b) is not true
<	It checks if the left operand value is less than right operand value, if yes then condition becomes true. Example: (a
>=	It checks if the left operand value is greater than or equal to the right operand value, if yes then condition becomes true. Example: (a>=b) is not true
<=	It checks if the left operand value is less than or equal to the right operand value, if yes then condition becomes true. Example: (a<=b) is true
!<	It checks if the left operand value is not less than the right operand value, if yes then condition becomes true. Example: (a!=b) is not true
!><	It checks if the left operand value is not greater than the right operand value, if yes then condition becomes true Example: (a!>b) is true

3.   SQL Logical Operators

There is the list of logical operator used in SQL:

ALL	It compares a value to all values in another value set.
AND	It allows the existence of multiple conditions in an SQL statement.
ANY	It compares the values in the list according to the condition.
BETWEEN	It is used to search for values that are within a set of values.
IN	It compares a value to that specified list value.
NOT	It reverses the meaning of any logical operator.
OR	It combines multiple conditions in SQL statements.
EXISTS	It is used to search for the presence of a row in a specified table.
LIKE	It compares a value to similar values using wildcard operator.