Q-to fetch the count of employees working in project 'P1'.

SELECT COUNT(*) FROM EmployeeSalary WHERE Project = 'P1';

Q-to find second highest salary of Employee?

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from
Employee

Q-to find Max Salary from each department?

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.

Q-to find duplicate rows in a database? and then write SQL query to delete them?
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE
a.empno=b.empno)
to Delete:
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE
a.empno=b.empno);

Q-find all employees which are also manager? .

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

Q-to fetch employee names having salary greater than or equal to 5000 and less than
or equal 10000?
SELECT FullName
FROM EmployeeDetails
WHERE EmpId IN
(SELECT EmpId FROM EmpolyeeSalary
WHERE Salary BETWEEN 5000 AND 10000);

Q-to fetch project-wise count of employees sorted by project's count in descending

order. ?
SELECT Project, count(EmpId) EmpProjectCount
FROM EmployeeSalary
GROUP BY Project
ORDER BY EmpProjectCount DESC;

Q-Return employee details even if the salary record is not present for the
employee?
SELECT E.FullName, S.Salary
FROM EmployeeDetails E LEFT JOIN EmployeeSalary S
ON E.EmpId = S.EmpId;

Q-to fetch all the Employees who are also managers from EmployeeDetails table.
SELECT DISTINCT E.FullName
FROM EmpDetails E
INNER JOIN EmpDetails M
ON E.EmpID = M.ManagerID;

Q-to fetch all employee records from EmployeeDetails table who have a salary record
in EmployeeSalary table.
SELECT * FROM EmployeeDetails E
WHERE EXISTS
(SELECT * FROM EmployeeSalary S WHERE E.EmpId = S.EmpId);

Q-to fetch duplicate records from a table.?

SELECT EmpId, Project, Salary, COUNT(*)
FROM EmployeeSalary
GROUP BY EmpId, Project, Salary
HAVING COUNT(*) > 1;

Q-to remove duplicates from a table without using temporary table.?

ex:
DELETE FROM EmployeeSalary
WHERE rowid NOT IN
(SELECT MAX(rowid) FROM EmployeeSalary GROUP BY EmpId);

Q-to fetch common records between two tables.

Ans. Using INTERSECT-

SELECT * FROM EmployeeSalary

INTERSECT
SELECT * FROM ManagerSalary

Q-to fetch records that are present in one table but not in another table.
Ans. Using MINUS-

SELECT * FROM EmployeeSalary

MINUS
SELECT * FROM ManagerSalary

Q-to fetch all the Employees details from EmployeeDetails table who joined in Year
2016 ?
SELECT * FROM EmployeeDetails
WHERE DateOfJoining BETWEEN '01-01-2016' AND date '31-12-2016';

Q-to fetch top n records?

SELECT * FROM (SELECT * FROM EmployeeSalary ORDER BY Salary DESC)
WHERE ROWNUM <= 3;

or
sql-
SELECT TOP N * FROM EmployeeSalary ORDER BY Salary DESC

Q-nth highest salary from table.

Ans. Using Top keyword (SQL Server)-

SELECT TOP 1 Salary

FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
)
ORDER BY Salary ASC

Q- to find the 3rd highest salary from table without using TOP/limit keyword.?
SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
SELECT COUNT( DISTINCT ( Emp2.Salary ) )
 FROM EmployeeSalary Emp2
WHERE Emp2.Salary > Emp1.Salary
)

or

nth highest salary-

SELECT Salary
FROM EmployeeSalary Emp1
WHERE N-1 = (
SELECT COUNT( DISTINCT ( Emp2.Salary ) )
FROM EmployeeSalary Emp2
WHERE Emp2.Salary > Emp1.Salary
)

Q SQL query to get the third maximum salary ?

ex
SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employee_table ORDER BY
salary DESC ) AS emp ORDER BY salary ASC;

Q Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE & N = (SELECT COUNT (DISTINCT (b.sal))
FROM EMP B WHERE a.sal < = b.sal);

Q Duplicate row deletion query ?

1

DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP
BY column1, column2, column3);

or

DELETE FROM table_name A WHERE a.rowid > ANY (SELECT B.rowid FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2 );

Q- finding the 2nd highest salary in SQL

Now, here is what the SQL will look like:

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM
Employee )

Q How to find third or nth maximum salary from salary table?

SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employees ORDER BY salary DESC)
AS emp ORDER BY salary ASC

or

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
SELECT EmpID, EmpName, EmpSalary,
RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

or

SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
FROM EMPLOYEE
) As A
WHERE A.RowNum IN (2,3)

Q
DELETE TRUNCATE
1) DELETE statement is used to delete a row from a table TRUNCATE statement is
used to remove all the rows from a table.
2) DELETE is slower than TRUNCATE statement. TRUNCATE statement is
faster than DELETE statement.
3) You can rollback data after using DELETE statement. it is not possible to
rollback after using TRUNCATE statement.
4) DELETE is a DML command. TRUNCATE is a DDL command.
5) We can use WHERE clause in DELETE command. We cannot use WHERE
clause with TRUNCATE
--

DROP:
Removes all rows and also the table definition, including indexes, triggers,
grants, storage parameters
The DROP command removes a table from the database.
All the tables' rows, indexes, and privileges will also be removed.
No DML triggers will be fired.
The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations
cannot be rolled back

Q ACID Properties ?
Atomicity, Consistency, Isolation, and Durability
Atomicity:
This property states that a transaction must be treated as an atomic unit, that is,
either all of its operations are executed or none. There must be no state in a
database where a transaction is left partially completed.
Consistency:
The database must remain in a consistent state after any transaction.
Durability :
The database should be durable enough to hold all its latest updates even if the
system fails or restarts.
Isolation, :
In a database system where more than one transaction are being executed
simultaneously and in parallel,
No transaction will affect the existence of any other transaction.

1 :-SQL INJECTION :
PREVENT IN sql-JAVA:-
You need to use PreparedStatement. e.g.

String insert = "INSERT INTO customer(name,address,email) VALUES(?, ?, ?);";

PreparedStatement ps = connection.prepareStatement(insert);
ps.setString(1, name);
ps.setString(2, addre);
ps.setString(3, email);

ResultSet rs = ps.executeQuery();
or
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE
userid=? AND password=?");
stmt.setString(1, userid);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

W3SCHOOL:
=========
SELECT DISTINCT:-used to return only distinct (different) values.
fetching only unique records.
ex-selects only the DISTINCT values from the "Country" column
INDIA
PAK
INDAI
INDIA
PAK

ex:-SELECT DISTINCT Country FROM Customers;

OP: INDIA
PAK
----------

WHERE Clause :-
===============
WHERE clause is used to filter records.
extract only those records that fulfill a specified condition.
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
ex:
SELECT * FROM Customers WHERE Country='Mexico';
SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000;

SQL ORDER BY:-used to sort the result-set in ascending or descending order.

==========
sorts the records in ascending order by default. To sort the records in descending
order,use the DESC keyword.
ex:
Badri
Asish
Monika
Nagendra

SELECT * FROM Customers ORDER BY Name; [default ascending]

Asish
Badri
Nagendra
Monika

to descending

SELECT * FROM Customers ORDER BY Name desc;

Monika
Nagendra
Badri
Asish

SQL SELECT TOP:

===============
used to specify the number of records to return.
Note:-MySQL supports the LIMIT clause to select a limited number of records, while
Oracle uses ROWNUM.

SELECT TOP number column_name(s)

FROM table_name
WHERE condition;

SELECT TOP 3 * FROM Customers;

oracle-ex:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
ex:
SELECT * FROM Customers WHERE ROWNUM <= 3;
SELECT TOP 3 * FROM Customers WHERE Country='Germany';
SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;

MIN() and MAX() Functions

--------------------------
SELECT MIN(Price) AS SmallestPrice FROM Products;
SELECT MAX(Price) AS LargestPrice FROM Products;

The SQL COUNT(), AVG() and SUM() Functions

------------------------------------------

SELECT COUNT(ProductID) FROM Products;

SELECT AVG(Price) FROM Products;
SELECT SUM(Quantity) FROM OrderDetails;

SQL LIKE Operator

------------------
% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character

WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%_%' Finds any values that start with "a" and are at
least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with
"o"

SQL IN
------
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

BETWEEN Operator
----------------
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN
(1,2,3);

SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni' ORDER BY ProductName;

SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND
'Mozzarella di Giovanni' ORDER BY ProductName;

SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

SQL Aliases : SQL aliases are used to give a table, or a column in a table, a
temporary name.
-------------

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

SQL JOIN :-
------------

A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
ex:
Orders:
------
OrderID CustomerID OrderDate

Customers:
----------
CustomerID CustomerName ContactName Country

Q-> selects records that have matching values in both tables? [only match data in
both table]

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Types of SQL JOIN:

------------------
(INNER) JOIN: Returns records that have matching values in both tables
ex:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Q-JOIN Three Tables ?

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

LEFT (OUTER) JOIN: Return all records from the left table, and the matched records
from the right table
ex:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT (OUTER) JOIN: Return all records from the right table, and the matched
records from the left table
ex:
ELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

FULL (OUTER) JOIN: Return all records when there is a match in either left or right
table
The FULL OUTER JOIN keyword return all records when there is a match in either left
(table1) or right (table2) table records.
ex:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Self JOIN:
========== but the table is joined with itself.
ex:
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

or
SQL> SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

SQL UNION Operator

------------------
The UNION operator is used to combine the result-set of two or more SELECT
statements.

ex:
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

UNION ALL:- returns the cities (duplicate values also) from both the "Customers"
and the "Suppliers"
ex:
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

UNION With WHERE:

-----------------
ex:
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

GROUP BY
=========
used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set
by one or more columns.
ex:- lists the number of customers in each country:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Q-lists the number of customers in each country, sorted high to low?

ex
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

HAVING Clause :
===============
HAVING clause was added to SQL because the WHERE keyword could not be used with
aggregate functions.
ex- lists the number of customers in each country. Only include countries with more
than 5 .
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Q-lists the number of customers in each country, sorted high to low (Only include
countries with more than 5 customers):
ex:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Q-lists the employees that have registered more than 10 orders:

ex:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

SQL EXISTS Operator:

--------------------
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns true if the subquery returns one or more records.
ex:

returns TRUE and lists the suppliers with a product price less than 20:

Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId =
Suppliers.supplierId AND Price < 20);

SELECT INTO Statement:-copies data from one table into a new table.
----------------------
ex:
SELECT * INTO CustomersBackup2017 FROM Customers;

Stored Procedure:
------------------
A stored procedure is a prepared SQL code that you can save, so the code can be
reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored
procedure, and then just call it to execute it.
ex:
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Stored Procedure With One Parameter:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

EXEC SelectAllCustomers City = "London";

Stored Procedure With Multiple Parameters

ex
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";

SQL Constraints:
---------------
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row
in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quicklySQL
Constraints

SQL CREATE INDEX Statement:

---------------------------
used to create indexes in tables.Indexes are used to retrieve data from the
database very fast.
CREATE INDEX idx_lastname
ON Persons (LastName);

SQL AUTO INCREMENT Field:

-------------------------
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

Syntax for Oracle

CREATE SEQUENCE seq_person

MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

VIEW Statement:
---------------
a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are
fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the
data as if the data were coming from one single table.

CREATE VIEW [Brazil Customers] AS

SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";

SQL Injection:
--------------
SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page
input.

SQL injection usually occurs when you ask a user for input, like their
username/userid, and instead of a name/id, the user gives you an SQL statement that
you will unknowingly run on your database.
Look at the following example which creates a SELECT statement by adding a variable
(txtUserId) to a select string. The variable is fetched from user input
(getRequestString):
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""="";
OR

------------hibernate injection---------------
Hibernate Query Language (HQL) use createQuery(),(Named Parameters) Examples

EX:Query qry= session.createQuery("from Inventory where productID=:productid");

qry.setParameter("productid", userSuppliedParameter);

SQL & PL SQL

Different between Function and Procedure
Joins
Different between Where and Having clause
Duplicate row deletion query & 3rd highest salary
 Types of Constraints?
SQL Joins, Group by, Having, PL/SQL Syntax of Procedure ,View & Function
Qtn 1:- Name of employee who works in �IT� department.
Qtn. 2 :- Sum of salary of employee who works in �IT� Deptartment.
Qtn3:- Sum of employee�s salary which is gretter than 500.
Qtn4 :- Name of employee which start with initial letter �P� .

Java Database Connectivity:

Register the Driver class

Create connection
Create statement
Execute queries
Close connection

or

1 Import JDBC packages.

ex:-
import java.sql.*;

2 Load and register the JDBC driver.

ex:-
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

3 Open a connection to the database.

ex:-
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@oracle.world",
"oratest", "oratest")

4 Create a statement object to perform a query.

ex:-
Statement sql_stmt = conn.createStatement();

5 Execute the statement object and return a query resultset.

ex:-ResultSet rset = sql_stmt.executeQuery("SELECT empno, ename, sal, deptno FROM
emp ORDER BY ename");

6 Process the resultset.

ex:
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
7 Close the resultset and statement objects.
ex:-
rset.close();
sql_stmt.close();

8 Close the connection.

Qtn 1:- Name of employee who works in �IT� department.

select * from emp where department name=IT;
21) Qtn. 2 :- Sum of salary of employee who works in �IT� Deptartment.
select sum(salary) from emp where Dept name = IT

22) Qtn3:- Sum of employee�s salary which is gretter than 500.

select sum(salary) from emp where salary > 500

23) Qtn4 :- Name of employee which start with initial letter �P� .
select name from emp where empname='P%'

Q diff bw procedure and function ?

Stored Procedures

Stored Procedures are pre-compiled objects which are compiled for the first time
and its compiled format is saved, which executes (compiled code) whenever it is
called.

Functions
A function is compiled and executed every time whenever it is called. A function
must returns a value and cannot modify the data received as parameters.

Basic Differences

Function must return a value but in Stored Procedure it is optional. Even a

procedure can return zero or n values.
Functions can have only input parameters for it whereas Procedures can have input
or output parameters .
Functions can be called from Procedure whereas Procedures cannot be called from a
Function.
Advance Differences

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it

whereas Function allows only SELECT statement in it.
Procedures can not be utilized in a SELECT statement whereas Function can be
embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section whereas Function can be.
Functions that return tables can be treated as another rowset. This can be used in
JOINs with other tables.
Inline Function can be though of as views that take parameters and can be used in
JOINs and other Rowset operations.
Exception can be handled by try-catch block in a Procedure whereas try-catch block
cannot be used in a Function.
We can use Transactions in Procedure whereas we can't use Transactions in Function.

or

Procedure Function
Stored Procedures can call functions. Stored
procedures can�t be called from function.
Can have select statements as well as DML statements such as insert, update, delete
and so on. will not allow us to use DML statements.
Can use both table variables as well as temporary table in it.
 We can use only table variables, it will not allow using temporary tables.
Procedures can't be used in Join clause A UDF
can be used in join clause as a result set.
We can go for transaction management in procedure
we can't go in
function.

Procedures cannot be utilized in a select statement

function can be embedded in a select statement.
procedure can returnmultiple values(max. 1024)

Q - joins ?
There are 4 different types of Oracle joins:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records
from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched
records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right
table

or
Oracle INNER JOIN (or sometimes called simple join)
Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

Q Difference between WHERE and HAVING ?

difference between WHERE and HAVING:

WHERE is used to filter records before any groupings take place.

HAVING is used to filter values after they have been groups. Only columns or
expression in the group can be included in the HAVING clause�s conditions..

or

Difference between WHERE and HAVING clause:

1. WHERE clause can be used with - Select, Insert, and Update statements, where as
HAVING clause can only be used with the Select statement.

2. WHERE filters rows before aggregation (GROUPING), where as, HAVING filters
groups, after the aggregations are performed.

3. Aggregate functions cannot be used in the WHERE clause, unless it is in a sub

query contained in a HAVING clause, whereas, aggregate functions can be used in
Having clause.

Filtering Groups:

WHERE clause is used to filter rows before aggregation, where as HAVING clause is
used to filter groups after aggregations
or

1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE
clause but HAVING clause can only be used with SELECT query. For example following
query, which involve WHERE clause will work but other which uses HAVING clause will
not work :

2) WHERE clause is used for filtering rows and it applies on each and every row,
while HAVING clause is used to filter groups in SQL.

3) One syntax level difference between WHERE and HAVING clause is that, former is
used before GROUP BY clause, while later is used after GROUP BY clause.

4) When WHERE and HAVING clause are used together in a SELECT query with aggregate
function, WHERE clause is applied first on individual rows and only rows which
pass the condition is included for creating groups. Once group is created, HAVING
clause is used to filter groups based upon condition specified.

Q type of CONSTRAINT?
ans:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row
in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly

or
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 identifies each row/record in a database table.

FOREIGN Key - Uniquely identifies a row/record in any of the given database table.

CHECK Constraint - The CHECK constraint ensures that all the values in a column
satisfies certain conditions.

INDEX - Used to create and retrieve data from the database very quickly.'
or

A CONSTRAINT can be one of the following:

a column-level constraint
Column-level constraints refer to a single column in the table and do not specify a
column name (except check constraints). They refer to the column that they follow.
a table-level constraint
Table-level constraints refer to one or more columns in the table. Table-level
constraints specify the names of the columns to which they apply. Table-level CHECK
constraints can refer to 0 or more columns in the table.

Column constraints include:

NOT NULL
Specifies that this column cannot hold NULL values (constraints of this type are
not nameable).

PRIMARY KEY
Specifies the column that uniquely identifies a row in the table. The identified
columns must be defined as NOT NULL.

Note: If you attempt to add a primary key using ALTER TABLE and any of the columns
included in the primary key contain null values, an error will be generated and the
primary key will not be added. See ALTER TABLE statement for more information.
UNIQUE
Specifies that values in the column must be unique.

FOREIGN KEY
Specifies that the values in the column must correspond to values in a referenced
primary key or unique key column or that they are NULL.

CHECK
Specifies rules for values in the column.

Table constraints include:

PRIMARY KEY
Specifies the column or columns that uniquely identify a row in the table. NULL
values are not allowed.

UNIQUE
Specifies that values in the columns must be unique.

FOREIGN KEY
Specifies that the values in the columns must correspond to values in referenced
primary key or unique columns or that they are NULL.

Note: If the foreign key consists of multiple columns, and any column is NULL, the
whole key is considered NULL. The insert is permitted no matter what is on the non-
null columns.
CHECK
Specifies a wide range of rules for values in the table.

Column constraints and table constraints have the same function; the difference is
in where you specify them. Table constraints allow you to specify more than one
column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition.
Column-level constraints (except for check constraints) refer to only one column.

A constraint operates with the privileges of the owner of the constraint. See
"Using SQL standard authorization" and "Privileges on views, triggers, and
constraints" in the Java DB Developer's Guide for details.
Q What are Triggers in PL/SQL?
Triggers are stored programs that are fired automatically when some events occur.
The code to be fired can be defined as per the requirement.
Types of Triggers in Oracle
Triggers can be classified based on the following parameters.

Classification based on the timing

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

A database definition (DDL) statement (CREATE, ALTER, or DROP).

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

or

BEFORE Trigger: It fires before the specified event has occurred.

AFTER Trigger: It fires after the specified event has occurred.
INSTEAD OF Trigger: A special type. You will learn more about the further topics.
(only for DML )
Classification based on the level
STATEMENT level Trigger: It fires one time for the specified event statement.
ROW level Trigger: It fires for each record that got affected in the specified
event. (only for DML)
Classification based on the Event
DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
DATABASE Trigger: It fires when the database event is specified
(LOGON/LOGOFF/STARTUP/SHUTDOWN)

Q The SQL GROUP BY Statement

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN,
SUM, AVG) to group the result-set by one or more columns.
What is the Group by Clause?

The GROUP BY clause is a SQL command that is used to group rows that have the same
values.

The GROUP BY clause is used in the SELECT statement

Grouping using a Single Column

In order to help understand the effect of Group By clause, let's execute a simple
query that returns all the gender entries from the members table.

SELECT `gender` FROM `members` ;

gender
Female
Female
Male

Table:- Department
Deptid Name
1 IT
2 Sales
3 Account

Table :- Emp
Emp Id Name
1 Abc
2 Pqr
3 Xyz
4 Lpr
5 pqs

Table :- Emp_dept_map
Edid Empid Deptid
1 1 3
2 2 2
3 3 1
4 4 1
5 5 3

Table :- Emp_sal
Edid Salary
1 200
2 520
3 600
4 300
5 400
2 300
5 100
3 300
4 250

======================================================

-------------------------------

2 Statement and prepared statetment:

The JDBC Statement, CallableStatement, and PreparedStatement interfaces

1 Statement :-using static SQL statements

------------------------------------------
Statement stmt = con.createStatement();
stmt.executeUpdate("delete from emp765 where id=33)");

2 PreparedStatement :- supplying dynamically input / PreparedStatement query is

compiled only once.
-----------------------------------------------------------------------------------
----------------
String SQL = "Update Employees SET age = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(SQL);

or

PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");

stmt.setInt(1,101);//1 specifies the first parameter in the query
stmt.setString(2,"Ratan");
int i=stmt.executeUpdate();

3 CallableStatement :-CallableStatement interface is used to call the stored

procedures and functions.
CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");
or
CallableStatement cstmt = con.prepareCall("{call anyProcedure(?, ?, ?)}");

----------------------

How to call stored procedure and function from java

ans:
CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");
or
CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");
or

String query = "{? = call

INTEGRATED_STUFF.TEST_PKG.Fn_Insert_TestRecord(?, ?, ?)}";
CallableStatement statement = connection.prepareCall(query);

String query = "call PACKAGE INTEGRATED_STUFF.TEST_PKG.Select_TestData(?,?)";

CallableStatement statement = connection.prepareCall(query);