Data Base + Hibernate

Clear screen : \! cls 

HIBERNATE

1.      What is serialization for save() and persistence() in Hibernate ?

2.      How to lazy initialization in Load() of Hibernate ?

3.      How to use of scopes of session , global session in spring?

4.      Example of "Composition and Aggregation " in inheritance ?

5.      coding example for foreign key in DB ?

6.      Can  we create an instance for Interface ?

7.      How many Interfaces can extends in a class ?

8.      what problem occurs at multiple Inheritance ?

9.      Synchronized ?

10.  Comparable and comparator interface practical implementation ?

11.  ConcurentHashMap , what is bucket lock ?

12.  when using stringbuffer and stringbuilder ?

13.  Difference between Iterator and Enumeration ?

14.  Behavioral Design pattern in java ?

15.  @Configuration, @      all annotation details in SPRING framework ?

16.  How to use exception in JSP ?

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

1.      Employee  property  for  equality ? Comparator and comparable ?

2.      HashMap  key  how to recognize ?

3.      Synchronized  key  explanation ?

4.      Inheritance concept ?

5.      Lazy loading in hibernate ?

6.      procedures and function in mysql and hibernate ?

How second level cache works

1.      Whenever hibernate session try to load an entity, the very first place it look for cached copy of entity in first level cache (associated with particular hibernate session).

2.      If cached copy of entity is present in first level cache, it is returned as result of load method.

3.      If there is no cached entity in first level cache, then second level cache is looked up for cached entity.

4.      If second level cache has cached entity, it is returned as result of load method. But, before returning the entity, it is stored in first level cache also so that next invocation to load method for entity will return the entity from first level cache itself, and there will not be need to go to second level cache again.

5.      If entity is not found in first level cache and second level cache also, then database query is executed and entity is stored in both cache levels, before returning as response of load() method.

6.      Second level cache validate itself for modified entities, if modification has been done through hibernate session APIs.

7.      If some user or process make changes directly in database, the there is no way that second level cache update itself until “timeToLiveSeconds” duration has passed for that cache region. In this case, it is good idea to invalidate whole cache and let hibernate build its cache once again. You can use below code snippet to invalidate whole hibernate second level cache.

 Behaviour when not closing hibernate session

When you don't close your Hibernate sessions and therefore do not release JDBC connections, you have what is typically called Connection leak. So, after a number of requests (depending on the size of your connection pool) the server will not be able to acquire a connection to respond your request. Actually, server will be waiting for connections to be released and be available on pool again and it will seem to be hanging.

Tutorial Point

Caching is all about application performance optimization and it sits between your application and the database to avoid the number of database hits as many as possible to give a better performance for performance critical applications.

Caching is important to Hibernate as well which utilizes a multilevel caching schemes as explained below:

First-level cache:

The first-level cache is the Session cache and is a mandatory cache through which all requests must pass. The Session object keeps an object under its own power before committing it to the database.

If you issue multiple updates to an object, Hibernate tries to delay doing the update as long as possible to reduce the number of update SQL statements issued. If you close the session, all the objects being cached are lost and either persisted or updated in the database.

Second-level cache:

Second level cache is an optional cache and first-level cache will always be consulted before any attempt is made to locate an object in the second-level cache. The second-level cache can be configured on a per-class and per-collection basis and mainly responsible for caching objects across sessions.

Any third-party cache can be used with Hibernate. An org.hibernate.cache.CacheProvider interface is provided, which must be implemented to provide Hibernate with a handle to the cache implementation.

Query-level cache:

Hibernate also implements a cache for query resultsets that integrates closely with the second-level cache.

This is an optional feature and requires two additional physical cache regions that hold the cached query results and the timestamps when a table was last updated. This is only useful for queries that are run frequently with the same parameters.

The Second Level Cache:

Hibernate uses first-level cache by default and you have nothing to do to use first-level cache. Let's go straight to the optional second-level cache. Not all classes benefit from caching, so it's important to be able to disable the second-level cache

The Hibernate second-level cache is set up in two steps. First, you have to decide which concurrency strategy to use. After that, you configure cache expiration and physical cache attributes using the cache provider.

Concurrency strategies:

A concurrency strategy is a mediator which responsible for storing items of data in the cache and retrieving them from the cache. If you are going to enable a second-level cache, you will have to decide, for each persistent class and collection, which cache concurrency strategy to use.

·         Transactional: Use this strategy for read-mostly data where it is critical to prevent stale data in concurrent transactions, in the rare case of an update.

·         Read-write: Again use this strategy for read-mostly data where it is critical to prevent stale data in concurrent transactions, in the rare case of an update.

·         Nonstrict-read-write: This strategy makes no guarantee of consistency between the cache and the database. Use this strategy if data hardly ever changes and a small likelihood of stale data is not of critical concern.

·         Read-only: A concurrency strategy suitable for data which never changes. Use it for reference data only.

If we are going to use second-level caching for our Employee class, let us add the mapping element required to tell Hibernate to cache Employee instances using read-write strategy.

1.      Read Only: This caching strategy should be used for persistent objects that will always read but never updated. It’s good for reading and caching application configuration and other static data that are never updated. This is the simplest strategy with best performance because there is no overload to check if the object is updated in database or not.

2.      Read Write: It’s good for persistent objects that can be updated by the hibernate application. However if the data is updated either through backend or other applications, then there is no way hibernate will know about it and data might be stale. So while using this strategy, make sure you are using Hibernate API for updating the data.

3.      Nonrestricted Read Write: If the application only occasionally needs to update data and strict transaction isolation is not required, a nonstrict-read-write cache might be appropriate.

4.      Transactional: The transactional cache strategy provides support for fully transactional cache providers such as JBoss TreeCache. Such a cache can only be used in a JTA environment and you must specify hibernate.transaction.manager_lookup_class.

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-mapping PUBLIC

 "-//Hibernate/Hibernate Mapping DTD//EN"

 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<classname="Employee"table="EMPLOYEE">

<metaattribute="class-description">

         This class contains the employee detail.

</meta>

<cacheusage="read-write"/>

<idname="id"type="int"column="id">

<generatorclass="native"/>

</id>

<propertyname="firstName"column="first_name"type="string"/>

<propertyname="lastName"column="last_name"type="string"/>

<propertyname="salary"column="salary"type="int"/>

</class>

</hibernate-mapping>

The usage="read-write" attribute tells Hibernate to use a read-write concurrency strategy for the defined cache.

Cache provider:

Your next step after considering the concurrency strategies you will use for your cache candidate classes is to pick a cache provider. Hibernate forces you to choose a single cache provider for the whole application.

S.N.	Cache Name	Description
1	EHCache	It can cache in memory or on disk and clustered caching and it supports the optional Hibernate query result cache.
2	OSCache	Supports caching to memory and disk in a single JVM, with a rich set of expiration policies and query cache support.
3	warmCache	A cluster cache based on JGroups. It uses clustered invalidation but doesn't support the Hibernate query cache
4	JBoss Cache	A fully transactional replicated clustered cache also based on the JGroups multicast library. It supports replication or invalidation, synchronous or asynchronous communication, and optimistic and pessimistic locking. The Hibernate query cache is supported

Every cache provider is not compatible with every concurrency strategy. The following compatibility matrix will help you choose an appropriate combination.

Strategy/Provider	Read-only	Nonstrictread-write	Read-write	Transactional
EHCache	X	X	X
OSCache	X	X	X
SwarmCache	X	X
JBoss Cache	X			X

You will specify a cache provider in hibernate.cfg.xml configuration file. We choose EHCache as our second-level cache provider:

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-configuration SYSTEM

"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<propertyname="hibernate.dialect">

org.hibernate.dialect.MySQLDialect

</property>

<propertyname="hibernate.connection.driver_class">

com.mysql.jdbc.Driver

</property>

<!-- Assume students is the database name -->

<propertyname="hibernate.connection.url">

jdbc:mysql://localhost/test

</property>

<propertyname="hibernate.connection.username">

root

</property>

<propertyname="hibernate.connection.password">

      root123

</property>

<propertyname="hibernate.cache.provider_class">

org.hibernate.cache.EhCacheProvider

</property>

<!-- List of XML mapping files -->

<mappingresource="Employee.hbm.xml"/>

</session-factory>

</hibernate-configuration>

Now, you need to specify the properties of the cache regions. EHCache has its own configuration file,ehcache.xml, which should be in the CLASSPATH of the application. A cache configuration in ehcache.xml for the Employee class may look like this:

<diskStorepath="java.io.tmpdir"/>

<defaultCache

maxElementsInMemory="1000"

eternal="false"

timeToIdleSeconds="120"

timeToLiveSeconds="120"

overflowToDisk="true"

/>

<cachename="Employee"

maxElementsInMemory="500"

eternal="true"

timeToIdleSeconds="0"

timeToLiveSeconds="0"

overflowToDisk="false"

/>

That's it, now we have second-level caching enabled for the Employee class and Hibernate now hits the second-level cache whenever you navigate to a Employee or when you load a Employee by identifier.

You should analyze your all the classes and choose appropriate caching strategy for each of the classes. Sometime, second-level caching may downgrade the performance of the application. So it is recommended to benchmark your application first without enabling caching and later on enable your well suited caching and check the performance. If caching is not improving system performance then there is no point in enabling any type of caching.

The Query-level Cache:

To use the query cache, you must first activate it using the hibernate.cache.use_query_cache="true"property in the configuration file. By setting this property to true, you make Hibernate create the necessary caches in memory to hold the query and identifier sets.

Next, to use the query cache, you use the setCacheable(Boolean) method of the Query class. For example:

Sessionsession=SessionFactory.openSession();

Queryquery=session.createQuery("FROM EMPLOYEE");

query.setCacheable(true);

List users =query.list();

SessionFactory.closeSession();

Hibernate also supports very fine-grained cache support through the concept of a cache region. A cache region is part of the cache that's given a name.

Sessionsession=SessionFactory.openSession();

Queryquery=session.createQuery("FROM EMPLOYEE");

query.setCacheable(true);

query.setCacheRegion("employee");

List users =query.list();

SessionFactory.closeSession();

This code uses the method to tell Hibernate to store and look for the query in the employee area of the cache.

20) What is the difference between first level cache and second level cache?

No.	First Level Cache	Second Level Cache
1)	First Level Cache is associated with Session.	Second Level Cache is associated with SessionFactory.
2)	It is enabled by default.	It is not enabled by default.

1.1) First-level cache

First-level cache always Associates with the Session object. Hibernate uses this cache by default. Here, it processes one transaction after another one, means wont process one transaction many times. Mainly it reduces the number of SQL queries it needs to generate within a given transaction. That is instead of updating after every modification done in the transaction, it updates the transaction only at the end of the transaction.

1.2) Second-level cache

Second-level cache always associates with the Session Factory object. While running the transactions, in between it loads the objects at the Session Factory level, so that those objects will be available to the entire application, not bound to single user. Since the objects are already loaded in the cache, whenever an object is returned by the query, at that time no need to go for a database transaction. In this way the second level cache works. Here we can use query level cache also. Later we will discuss about it.

-------------------------------------------------------------------------------------------------------------------------------
First level cache is associated with “session” object. The scope of cache objects is of session. Once session is closed, cached objects are gone forever. First level cache is enabled by default and you can not disable it. When we query an entity first time, it is retrieved from database and stored in first level cache associated with hibernate session. If we query same object again with same session object, it will be loaded from cache and no sql query will be executed. The loaded entity can be removed from session using evict() method. The next loading of this entity will again make a database call if it has been removed using evict() method. The whole session cache can be removed using clear() method. It will remove all the entities stored in cache.

Second level cache is apart from first level cache which is available to be used globally in session factory scope. second level cache is created in session factory scope and is available to be used in all sessions which are created using that particular session factory. It also means that once session factory is closed, all cache associated with it die and cache manager also closed down. Whenever hibernate session try to load an entity, the very first place it look for cached copy of entity in first level cache (associated with particular hibernate session). If cached copy of entity is present in first level cache, it is returned as result of load method. If there is no cached entity in first level cache, then second level cache is looked up for cached entity. If second level cache has cached entity, it is returned as result of load method. But, before returning the entity, it is stored in first level cache also so that next invocation to load method for entity will return the entity from first level cache itself, and there will not be need to go to second level cache again. If entity is not found in first level cache and second level cache also, then database query is executed and entity is stored in both cache levels, before returning as response of load() method.

Second level of Query Cache with springboot

 Java Brains

Locking Mechanism in Hibernate

Difference between get and load in Hibernate

get vs load in Hibernate

Difference between get and load method in Hibernate is a one of the most popular question asked in Hibernate and spring interviews. Hibernate Session class provides two method to access object e.g. session.get() and session.load() both looked quite similar to each other but there are subtle difference between load and get method which can affect performance of application. Main difference between get() vs load method is that get() involves database hit if object doesn't exists in Session Cache and returns a fully initialized object which may involve several database call while load method can return proxy in place and only initialize the object or hit the database if any method other than getId() is called on persistent or entity object. This lazy initialization can save couple of database round-trip which result in better performance. By the way there are many articles on interview questions in Java, you can use search button on top left to find them. Some of them like 20 design pattern interview questions and 10 Singleton pattern questions are my favorites, you may also like. Coming back to article, you can find more difference between load and get in rest of this article in point format but this is the one which really makes difference while comparing both of them. If you look at how get and load gets called its pretty identical.

Difference between get and load method

Here are few differences between get and load method in Hibernate.

1. Behavior when Object is not found in Session Cache

Apart from performance this is another difference between get and load which is worth remembering. get method of Hibernate Session class returns null if object is not found in cache as well as on database while load() method throwsObjectNotFoundException if object is not found on cache as well as on database but never return null.

2. Database hit

Get method always hit database while load() method may not always hit the database, depending upon which method is called.

3. Proxy

Get method never returns a proxy, it either returns null or fully initialized Object, while load() method may return proxy, which is the object with ID but without initializing other properties, which is lazily initialized. If you are just using returned object for creating relationship and only need Id then load() is the way to go.

4. Performance

By far most important difference between get and load in my opinion. get method will return a completely initialized object if  Object is not on the cache but exists on Database, which may involve multiple round-trips to database based upon object relational mappings while load() method of Hibernate can return a proxy which can be initialized on demand (lazy initialization) when a non identifier method is accessed. Due to above reason use of load method will result in slightly better performance, but there is a caveat that proxy object will throw ObjectNotFoundException later if corresponding row doesn’t exists in database, instead of failing immediately so not a fail fast behavior.

5. load method exists prior to get method which is added on user request.

When to use Session get() and load() in Hibernate

So far we have discussed how get and load are different to each other and how they can affect performance of your web application, after having this information in our kitty we can see some best practices to get most of load and get together. This section suggest some scenario which help you when to use get and load in Hibernate.

1. Use get method to determine if an instance exists or not because it can return null if instance doesn’t exists in cache and database and use load method to retrieve instance only if you think that instance should exists and non availability is an error condition.

2.  As stated in difference number 2 between get and load in Hibernate. get() method could suffer performance penalty if only identifier method like getId()  is accessed. So consider using load method  if  your code doesn't access any method other than identifier or you are OK with lazy initialization of object, if persistent object is not in Session Cache because load() can return proxy.

How to call get records in Hibernate using get and load method

If you look at below code , there is not much difference on calling get() and load() method, though both are overloaded now and can accept few more parameters but the primary methods looks exactly identical. It’s there behavior which makes them different.

//Example of calling get method of Hiberante Session class
Sessionsession = SessionFactory.getCurrentSession();
EmployeeEmployee = (Employee)session.get(Employee.class, EmployeeID);

//Example of calling load method of Hiberante Session
Sessionsession = SessionFactory.getCurrentSession();
EmployeeEmployee = (Employee)session.load(Employee.class, EmployeeID);

10) What is the difference between get and load method?

The differences between get() and load() methods are given below.

No.	get()	load()
1)	Returns null if object is not found.	Throws ObjectNotFoundException if object is not found.
2)	get() method always hit the database.	load() method doesn't hit the database.
3)	It returns real object not proxy.	It returns proxy object.
4)	It should be used if you are not sure about the existence of instance.	It should be used if you are sure  that instance exists.

9) What is the difference between session.save() and session.persist() method?

No.	save()	persist()
1)	returns the identifier (Serializable) of the instance.	return nothing because its return type is void.
2)	Syn: public Serializable save(Object o)	Syn: public void persist(Object o)

                                                        SQL  

INTERVIWE QUESTION FOR QUERIES

1. Write SQL query to get a third highest salary from Employee table?
2. 
   
3. How do you find the 4th highest salary in MySQL without using the LIMIT keyword?
4. 
   
5. Write SQL query to find second highest salary in Oracle database using ROWNUM?
6. 
   
7. How to find Nth highest salary in SQL Server without using TOP keyword?
8. 
   
9. Find the second highest salary in Oracle using rank?
10. 
    
11. How to find the top 3 salaries in Oracle without using ROW_NUMBER or RANK()?

Q:- What is the difference between drop and truncate ?

The DROP command is used to remove the whole database or table indexes, data, and more. Whereas the TRUNCATE command is used to remove all the rows from the table

SECOND HIGHEST SALARY

select *from employees_1 

group by salary 

order by salary desc limit 1,1;

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

mysql> select first_name, last_name, salary from employees_1 where salary=(select MAX(salary) from employees_1 where salary<>(select MAX(salary) from employees_1));

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

| first_name | last_name | salary |

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

| Paritosh   | Singh     |  25000 |

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

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

GENERIC LIMIT OF QUERY

select * from employees_1 where salary = (select salary from employees_1 ORDER BY salary DESC limit n-1,1);

mysql> select * from employees_1 where salary = (select salary from employees_1 ORDER BY salary DESC limit 2,1);

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1005 | 1994-05-21 | Abhijit    | Singh     | M      | 2016-09-16 | 4       |  35000 |

|   1006 | 1994-04-27 | Pawan      | kumar     | M      | 2010-12-17 | 4       |  35000 |

|   1007 | 1995-05-29 | Praveen    | kumar     | M      | 2010-12-17 | 4       |  35000 |

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

THIRD HIGHEST SALARY

mysql> select * from employees_1 where salary = (select salary from employees_1 ORDER BY salary DESC limit 4,1);

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1003 | 1992-03-19 | Kunal      | Sinha     | M      | 2013-12-20 | 3       |  15000 |

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

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

CREATE TABLE employees_1 (

    emp_no      INT             NOT NULL,  

    birth_date  DATE            NOT NULL,

    first_name  VARCHAR(14)     NOT NULL,

    last_name   VARCHAR(16)     NOT NULL,

    gender      ENUM ('M','F')  NOT NULL,  

    hire_date   DATE            NOT NULL,

    PRIMARY KEY (emp_no)                   

);

ALTER TABLE employees_1

ADD COLUMN dept_no CHAR(4) AFTER hire_date;

UPDATE employees_1 

SET 

    dept_no = 1

WHERE

    emp_no = 1001;

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

UPDATE employees_1 

SET 

    dept_no = 2

WHERE

    emp_no = 1002;

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

UPDATE employees_1 

SET 

    dept_no = 3

WHERE

    emp_no = 1003;

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

UPDATE employees_1 

SET 

    dept_no = 4

WHERE

    emp_no = 1004;

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

UPDATE employees_1 

SET 

    dept_no = 4

WHERE

    emp_no = 1005;

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

ALTER TABLE employees_1

ADD COLUMN salary INT AFTER dept_no;

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

UPDATE employees_1 

SET 

    salary = 4000

WHERE

    emp_no = 1001;

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

UPDATE employees_1 

SET 

    salary = 5000

WHERE

    emp_no = 1002;

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

UPDATE employees_1 

SET 

    salary = 15000

WHERE

    emp_no = 1003;

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

UPDATE employees_1 

SET 

    salary = 25000

WHERE

    emp_no = 1004;

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

UPDATE employees_1 

SET 

    salary = 35000

WHERE

    emp_no = 1005;

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

CREATE TABLE departments (

    dept_no     CHAR(4)         NOT NULL, 

    dept_name   VARCHAR(40)     NOT NULL,

    PRIMARY KEY (dept_no),                 

    UNIQUE  KEY (dept_name)                

);

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

employees_1

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

insert into employees_1 values(1001,  '1990-01-17', 'Perwaiz','Ali','M', '2010-12-17');

insert into employees_1 values(1002,  '1991-02-18', 'Vijay','Tiwari','M', '2012-10-21');

insert into employees_1 values(1003,  '1992-03-19', 'Kunal','Sinha','M', '2013-12-20');

insert into employees_1 values(1004,  '1993-04-20', 'Paritosh','Singh','M', '2015-11-07');

insert into employees_1 values(1005,  '1994-05-21', 'Abhijit','Singh','M', '2016-09-16');

departments

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

insert into departments values (1,'IT');

insert into departments values (2,'HR');

insert into departments values (3,'Account');

insert into departments values (4,'Admin');

insert into departments values (5,'Security');

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

SELECT e.first_name,e.last_name, d.dept_name

FROM   employees_1 e, departments d

WHERE  e.dept_no = d.dept_no

AND    e.salary > 15000;

SELECT e.first_name,e.last_name ,d.dept_name FROM   employees_1 e, departments d WHERE  e.dept_no = d.dept_no AND    e.salary > 15000;

select e.first_name, e.last_name, d.dept_no from employees_1 e, departments d where d.dept_no=e.dept_no and d.dept_no=4;

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

| first_name | last_name | dept_no |

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

| Paritosh   | Singh     | 4       |

| Abhijit    | Singh     | 4       |

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

2 rows in set (0.00 sec)

select e.first_name, e.last_name, d.dept_no from employees_1 e, departments d where d.dept_no=e.dept_no;

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

| first_name | last_name | dept_no |

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

| Perwaiz    | Ali       | 1       |

| Vijay      | Tiwari    | 2       |

| Kunal      | Sinha     | 3       |

| Paritosh   | Singh     | 4       |

| Abhijit    | Singh     | 4       |

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

select first_name, last_name from employees_1 where dept_no='4';

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

| first_name | last_name |

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

| Paritosh   | Singh     |

| Abhijit    | Singh     |

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

select * from employees_1;

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1001 | 1990-01-17 | Perwaiz    | Ali       | M      | 2010-12-17 | 1       |   4000 |

|   1002 | 1991-02-18 | Vijay      | Tiwari    | M      | 2012-10-21 | 2       |   5000 |

|   1003 | 1992-03-19 | Kunal      | Sinha     | M      | 2013-12-20 | 3       |  15000 |

|   1004 | 1993-04-20 | Paritosh   | Singh     | M      | 2015-11-07 | 4       |  25000 |

|   1005 | 1994-05-21 | Abhijit    | Singh     | M      | 2016-09-16 | 4       |  35000 |

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

 select * from departments;

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

| dept_no | dept_name |

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

| 3       | Account   |

| 4       | Admin     |

| 2       | HR        |

| 1       | IT        |

| 5       | Security  |

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

5 rows in set (0.00 sec)

//Group by "Department no" and sort by salary

mysql> select * from employees_1 order by dept_no, salary;

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1001 | 1990-01-17 | Perwaiz    | Ali       | M      | 2010-12-17 | 1       |   4000 |

|   1002 | 1991-02-18 | Vijay      | Tiwari    | M      | 2012-10-21 | 2       |   5000 |

|   1010 | 1994-05-21 | Kamal      | Singh     | M      | 2016-09-16 | 3       |  13000 |

|   1003 | 1992-03-19 | Kunal      | Sinha     | M      | 2013-12-20 | 3       |  15000 |

|   1009 | 1994-05-21 | Abhijit_2  | Singh_2   | M      | 2016-09-16 | 3       |  25000 |

|   1006 | 1994-04-27 | Pawan      | kumar     | M      | 2010-12-17 | 4       |  21000 |

|   1004 | 1993-04-20 | Paritosh   | Singh     | M      | 2015-11-07 | 4       |  25000 |

|   1007 | 1995-05-29 | Praveen    | kumar     | M      | 2010-12-17 | 4       |  35000 |

|   1008 | 1994-05-21 | Abhijit_1  | Singh_1   | M      | 2016-09-16 | 4       |  35000 |

|   1005 | 1994-05-21 | Abhijit    | Singh     | M      | 2016-09-16 | 4       |  43000 |

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

CREATE TABLE projects (

    project_id     VARCHAR(40)         NOT NULL, 

    project_name   VARCHAR(40)     NOT NULL,

    PRIMARY KEY (project_id),                 

    UNIQUE  KEY (project_name)                

);

insert into projects values (1,'Software_1');

insert into projects values (2,'Software_2');

insert into projects values (3,'Software_3');

insert into projects values (4,'Software_4');

insert into projects values (5,'Software_5');

CREATE TABLE Employe_project_Detials (

    project_id     VARCHAR(40)         NOT NULL, 

    emp_no   VARCHAR(40)     NOT NULL,          

);

insert into Employe_project_Detials  values (1,1001);

insert into Employe_project_Detials  values (2,1002);

insert into Employe_project_Detials  values (3,1003);

insert into Employe_project_Detials  values (4,1004);

insert into Employe_project_Detials  values (5,1005);

MAX SLARY Name of that person.

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1001 | 1990-01-17 | Perwaiz    | Ali       | M      | 2010-12-17 | 1       |   4000 |

|   1002 | 1991-02-18 | Vijay      | Tiwari    | M      | 2012-10-21 | 2       |   5000 |

|   1003 | 1992-03-19 | Kunal      | Sinha     | M      | 2013-12-20 | 3       |  15000 |

|   1004 | 1993-04-20 | Paritosh   | Singh     | M      | 2015-11-07 | 4       |  25000 |

|   1005 | 1994-05-21 | Abhijit    | Singh     | M      | 2016-09-16 | 4       |  35000 |

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

mysql> select first_name,last_name, salary from employees_1 where salary = (select MAX(salary) from employees_1);

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

| first_name | last_name | salary |

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

| Abhijit    | Singh     |  35000 |

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

SECOND HIGHEST SALARY

mysql> select MAX(salary) from employees_1 where salary<>(select MAX(salary) from employees_1);

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

| MAX(salary) |

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

|       25000 |

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

select first_name, last_name, salary from employees_1 where salary=(select MAX(salary) from employees_1 where salary<> (select MAX(salary) from employees_1));

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

| first_name | last_name | salary |

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

| Paritosh   | Singh     |  25000 |

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

How many numbers in that group

mysql> select dept_no, count(*) from employees_1 group by dept_no having count(*)>1;

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

| dept_no | count(*) |

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

| 4       |        2 |

mysql> select * from departments;

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

| dept_no | dept_name |

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

| 3       | Account   |

| 4       | Admin     |

| 2       | HR        |

| 1       | IT        |

| 5       | Security  |

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

5 rows in set (0.00 sec)

 select first_name, last_name from employees_1 where dept_no IN (select dept_no from employees_1 group by dept_no having count(*)<2);

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

| first_name | last_name |

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

| Perwaiz    | Ali       |

| Vijay      | Tiwari    |

| Kunal      | Sinha     |

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

mysql> select first_name, last_name , dept_no from employees_1 where dept_no IN (select dept_no from employees_1 group by dept_no having count(*)>1);

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

| first_name | last_name | dept_no |

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

| Paritosh   | Singh     | 4       |

| Abhijit    | Singh     | 4       |

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

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

Duplicate Rows in table

mysql> select * , count(emp_no) from employees_1 group by emp_no;

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary | count(emp_no) |

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

|   1001 | 1990-01-17 | Perwaiz    | Ali       | M      | 2010-12-17 | 1       |   4000 |             1 |

|   1002 | 1991-02-18 | Vijay      | Tiwari    | M      | 2012-10-21 | 2       |   5000 |             1 |

|   1003 | 1992-03-19 | Kunal      | Sinha     | M      | 2013-12-20 | 3       |  15000 |             1 |

|   1004 | 1993-04-20 | Paritosh   | Singh     | M      | 2015-11-07 | 4       |  25000 |             1 |

|   1005 | 1994-05-21 | Abhijit    | Singh     | M      | 2016-09-16 | 4       |  35000 |             1 |

|   1006 | 1994-04-27 | Pawan      | kumar     | M      | 2010-12-17 | 4       |  35000 |             1 |

|   1007 | 1995-05-29 | Praveen    | kumar     | M      | 2010-12-17 | 4       |  35000 |             1 |

|   1008 | 1994-05-21 | Abhijit_1  | Singh_1   | M      | 2016-09-16 | 4       |  35000 |             1 |

|   1009 | 1994-05-21 | Abhijit_2  | Singh_2   | M      | 2016-09-16 | 3       |  25000 |             1 |

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

EVEN Rows 

mysql> select * from employees_1 where MOD(emp_no,2)=0;

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1002 | 1991-02-18 | Vijay      | Tiwari    | M      | 2012-10-21 | 2       |   5000 |

|   1004 | 1993-04-20 | Paritosh   | Singh     | M      | 2015-11-07 | 4       |  25000 |

|   1006 | 1994-04-27 | Pawan      | kumar     | M      | 2010-12-17 | 4       |  35000 |

|   1008 | 1994-05-21 | Abhijit_1  | Singh_1   | M      | 2016-09-16 | 4       |  35000 |

ODD Rows 

mysql> select * from employees_1 where MOD(emp_no,2)=1;

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1001 | 1990-01-17 | Perwaiz    | Ali       | M      | 2010-12-17 | 1       |   4000 |

|   1003 | 1992-03-19 | Kunal      | Sinha     | M      | 2013-12-20 | 3       |  15000 |

|   1005 | 1994-05-21 | Abhijit    | Singh     | M      | 2016-09-16 | 4       |  35000 |

|   1007 | 1995-05-29 | Praveen    | kumar     | M      | 2010-12-17 | 4       |  35000 |

|   1009 | 1994-05-21 | Abhijit_2  | Singh_2   | M      | 2016-09-16 | 3       |  25000 |

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

First Row of Table

mysql> select * from employees_1 where emp_no = (select MIN(emp_no) from employees_1);

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1001 | 1990-01-17 | Perwaiz    | Ali       | M      | 2010-12-17 | 1       |   4000 |

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

Last Row of Table

mysql> select * from employees_1 where emp_no=(select MAX(emp_no) from employees_1);

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

| emp_no | birth_date | first_name | last_name | gender | hire_date  | dept_no | salary |

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

|   1009 | 1994-05-21 | Abhijit_2  | Singh_2   | M      | 2016-09-16 | 3       |  25000 |

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

DATABASE

Self_Join

select e.name, m.name from self_join e , self_join m where e.mid=m.empid;

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

| name       | name    |

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

| Ali        | Krishan |

| neeraj     | sanjeev |

| Krishan    | sanjeev |

| Neha Singh | sanjeev |

| Neha Saini | sanjeev |

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

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

| empid | name       | mid  |

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

|     1 | Ali        |    3 |

|     2 | neeraj     |    4 |

|     3 | Krishan    |    4 |

|     4 | sanjeev    | NULL |

|     5 | Neha Singh |    4 |

|     6 | Neha Saini |    4 |

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



MariaDB [abc]> select m.name, e.name from SJ e , SJ m where m.mid=e.empid;
+---------+---------+
| name    | name    |
+---------+---------+
| Ali     | Krishan |
| Krishan | Sanjeev |
| Sanjeev | DevRaj  |
| DevRaj  | DevRaj  |
+---------+---------+


MariaDB [abc]> select * from SJ;
+-------+---------+------+
| empid | name    | mid  |
+-------+---------+------+
|     1 | Ali     |    4 |
|     4 | Krishan |    3 |
|     3 | Sanjeev |    2 |
|     2 | DevRaj  |    2 |

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

create table STUDY( s_id varchar(225), c_id varchar(225),  since int);

insert into study values('S1','C1',2016);

insert into study values('S2','C2',2017);

insert into study values('S1','C2',2017);

mysql> select * from study;

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

| s_id | c_id | since |

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

| S1   | C1   |  2016 |

| S2   | C2   |  2017 |

| S1   | C2   |  2017 |

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

3 rows in set (0.00 sec)

mysql> select T1.s_id from STUDY as T1, STUDY as T2 where T1.s_id = T2.s_id and T1.c_id <> T2.c_id;

+------+

| s_id |

+------+

| S1   |

| S1   |

+------+

create table Self_Join (emp_id varchar(225), name varchar(225), m_id varchar(225));

insert into Self_Join values('1','Paritosh','');

insert into Self_Join values('2','Jeetu','1');

insert into Self_Join values('3','Ravi','1');

insert into Self_Join values('4','Avijeet','3');

insert into Self_Join values('5','Ali','3');

insert into Self_Join values('6','Kaushik','5');

mysql> select * from self_join;

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

| emp_id | name     | m_id |

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

| 1      | Paritosh |      |

| 2      | Jeetu    | 1    |

| 3      | Ravi     | 1    |

| 4      | Avijeet  | 3    |

| 5      | Ali      | 3    |

| 6      | Kaushik  | 5    |

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

mysql> select m.name as emp_name,e.name as maneger_name from self_join e, self_join m where m.m_id = e.emp_id;

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

| emp_name | maneger_name |

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

| Jeetu    | Paritosh     |

| Ravi     | Paritosh     |

| Avijeet  | Ravi         |

| Ali      | Ravi         |

| Kaushik  | Ali          |

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

1. What is a stored procedure?

    A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.

What is a Stored Procedure:

They are one or more SQL programs stored in a database as an executable object. They can be called interactively, from within a client application or from another stored procedure and from within triggers. We can pass parameters to and return from stored procedures to increase their usefulness and flexibility. A stored procedure can return a number or result set and a status code.

MySQL stored procedures advantages

·         Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However, MySQL implements the stored procedures slightly different. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise, the stored procedure works like a query.

·         Stored procedures help reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.

·         Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures.

·         Stored procedures are secure. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permissions on the underlying database tables.

Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before using them in your databases.

MySQL stored procedures disadvantages

·         If you use a lot of stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside store procedures, the CPU usage will also increase because the database server is not well-designed for logical operations.

·         Constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic.

·         It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.

·         It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.

JOIN OPERATOR

The JOIN operator specifies how to relate tables in the query.  The JOIN operator is one of the set operations available in relational databases.

The following join types of join are available in most relational databases:

            INNER

            OUTER (LEFT. RIGHT, FULL)

            CROSS

Joins may be represented as Venn diagrams, as shown below along with other common set operations:

Result of applying these joins in a query:

INNER JOIN:  Select only those rows that have values in common in the columns specified in the ON clause.

LEFT, RIGHT, or FULL OUTER JOIN:  Select all rows from the table on the left (or right, or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing.  (Note:  FULL OUTER JOIN not implemented in Access.)

CROSS JOIN (not illustrated - not exactly a set operation):  Select all possible combinations of  rows and columns from both tables (Cartesian product). Not available in Access but can "happen" by not specifying relationships between tables or not setting up the appropriate joins in a query.  (Not A Good Thing - the query may run for a very long time  and produce a huge, not very useful result set.)     

Access uses the ANSI  (American National Standards Institute) style, with the JOIN and ON keywords.  Access, MySQL, and Oracle all use similar syntax, with more join types and options and other set operations in MySQL and Oracle (CROSS JOIN, FULL OUTER JOIN, INTERSECT, MINUS).

        

Select a.username ,a.addressb.firstname, b.lastname, b.city  from USER a LEFT JOIN adminuser b on a.id = b.id;

Select a.username ,a.addressb.firstname, b.lastname, b.city  from USER a RIGHT  JOIN adminuser b on a.id = b.id;

create table emp (id int , name varchar(20) , salary varchar(20));

Alter table emp add primary key(id);

desc   company;

Describe   company;

`

alter table company modify column Name varchar(20);

update company set name='Apple' where id='4';

select company.name, emp.name,emp.salary from company right join emp on company.id=emp.id;

àselect company.name, emp.name,emp.salary from company left join emp on  company.id = emp.id;

Adding new column :alter table sales name varchar(20);

Dropping a column "alter table company drop column name;"

With Foreign key

DELETE customers

FROM customers

LEFT JOIN orders ON customers.customerNumber = orders.customerNumber

WHERE orderNumber IS NULL;

DELETE FROM table_name [WHERE Clause]

Add Date column in a Table

alter table register add column Date date default null;

insert date in table

update register set date=curdate() where id='1';

 update register set date='2018-09-01' where id='3';
+---------------+----------------+----------------+----------+----------------+----+------+
| name          | password       | email          | sex      | country        | id | Date |
+---------------+----------------+----------------+----------+----------------+----+------+
| ali           | pass           | a@a.com        | male     | India          |  1 | NULL |
| Gaurav Mourya | password       | G@gmail.com    | male     | India          |  2 | NULL |
| Perwaiz Ali   | ghghjg         | a@ag.cokm      | male     | India          |  5 | NULL |
| Choudhary     | ada            | a@a.com        | male     | Berma          |  6 | NULL |
| Choudhary     | aaa            | a@a.com        | male     | India          |  7 | NULL |
| Kunal         | kkkk           | kka@g.com      | male     | India          |  8 | NULL |
| NULL          | NULL           | NULL           | NULL     | Not Done       |  9 | NULL |
| NULL          | NULL           | NULL           | NULL     | Not Done       | 10 | NULL |
| NULL          | NULL           | NULL           | NULL     | NULL           | 11 | NULL |
| Kunal         | aaaa           | a@a.com        | male     | India          | 12 | NULL |
| aaa           | aaa            | aa@g.com       | Done     | Not Done       | 14 | NULL |
| NULL          | NULL           | NULL           | Done     | Done in Mosque | 15 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | Not Done       | 16 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | NULL           | 17 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | Not Done       | 18 | NULL |
| Not Done      | Not Done       | Not Done       | Not Done | Not Done       | 19 | NULL |
| Not Done      | Done in Mosque | Not Done       | Not Done | Not Done       | 20 | NULL |
| Not Done      | Done in Mosque | Not Done       | Not Done | Not Done       | 21 | NULL |
| Not Done      | Not Done       | Done in Mosque | Not Done | Not Done       | 22 | NULL |

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

We have two tables: customers and orders. Each customer has zero or more orders and each order belongs to only one customer. The relationship between customers table and orders table is one-to-many, and it is established by a foreign key in the orders table specified by the customerNumberfield. The customerNumberfield in the orders table relates to the customerNumberprimary key field in customers table.
The customers table is called parent table or referenced table, and the orders table is known as child table or referencing table.

A row in the child table must contain values that exist in the parent table e.g., each order record in the orders table must have a customerNumberthat exists in the customers table. Multiple orders can refer to the same customer therefore this relationship is called one (customer) to many (orders), or one-to-many.

In addition, you can set up a cascade on delete action for the customerNumberforeign key so that when you delete a customer in the customers table, all the orders associated with the customer are also deleted.

MariaDB [test]> select MAX(salary) from perwaiz;

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

| MAX(salary) |

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

|      175000 |

MariaDB [test]> select MAX(salary),id, name,city from perwaiz;

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

| MAX(salary) | id   | name | city  |

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

|      175000 |    1 | Ali  | Delhi |

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

MariaDB [test]> select MIN(Salary) from perwaiz;

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

| MIN(Salary) |

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

|       60000 |

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

1 row in set (0.00 sec)

MariaDB [test]> SELECT MAX(Salary) From Perwaiz WHERE Salary < ( SELECT Max(Salary) FROM Perwaiz);

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

| MAX(Salary) |

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

|       95000 |

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

1 row in set (0.07 sec)

MariaDB [test]> SELECT Min(Salary) From Perwaiz WHERE Salary > ( SELECT min(Salary) FROM Perwaiz);

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

| Min(Salary) |

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

|       75000 |

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

MariaDB [test]> select name, salary from perwaiz where salary = (select MAX(salary) from perwaiz);

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

| name   | salary |

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

| shakti | 175000 |

MariaDB [test]> select name from perwaiz where salary =(select  MAX(salary) from perwaiz where salary<(select Max(salary) from Perwaiz));

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

| name     |

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

| Paritosh |

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

MariaDB [test]> select * from perwaiz;

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

| id   | name           | city      | salary |

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

|    1 | Ali            | Delhi     |  60000 |

|    2 | Mako           | Mumbai    |  90000 |

|    2 | Paritosh       | Bangalore |  95000 |

|    4 | shakti         | gr. Noida | 175000 |

|    5 | Gaurav Shooter | bglr      |  75000 |

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

5 rows in set (0.03 sec)

MariaDB [test]> select count(*) from perwaiz;

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

| count(*) |

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

|        5 |

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


Different way
Max salary from employee table 

select * from employee emp1 where 0=(select count (distinct salary)  from employee emp2 where emp2.salary>emp1.salary);

3rd highest and many more 

select  * from employee emp1 where 2=(select count (distinct salary)  from employee emp2 where emp2.salary>emp1.salary);

        ID NAME                     SALARY
---------- -------------------- ----------
         2 sumaira_2                  1300

         3 sumaira_2                  1300

select salary from

(select salary from employee order by salary desc limit 5)

As emp

order by salary limit 1;

MySql Table

SELF_JOIN

Primary Key:

·         There can only be one primary key in a table

·         In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL

·         Primary Key is a unique key identifier of the record

Unique Key:

·         Can be more than one unique key in one table

·         Unique key can have NULL values

·         It can be a candidate key

·         Unique key can be NULL and may not be unique

Unique Key (UK): It's a column or a group of columns that can identify a uniqueness in a row.

Primary Key (PK): It's also a column or group of columns that can identify a uniqueness in a row.

So the Primary key is just another name for unique key, but the default implementation in SQL Server is different for Primary and Unique Key.

By Default:

1.      PK creates a Clustered index and UK creates a Non Clustered Index.

2.      PK is not null, but UK allows nulls (Note: By Default)

3.      There can only be one and only one PK on a table, but there can be multiple UK's

4.      You can override the default implementation depending upon your need.

It really depends what is your aim when deciding whether to create a UK or PK. It follows an analogy like "If there is a team of three people, so all of them are peers, but there will be one of them who will be a pair of peers: PK and UK has similar relation.". I would suggest reading this article: The example given by the author may not seem suitable, but try to get an overall idea.

Remove Duplicate Records in Orcale

5 ways to delete duplicate records Oracle

 In Oracle there are many ways to delete duplicate records. Note that below example are described to just explain the different possibilities.

Consider the EMP table with below rows
create table emp(
EMPNNO  integer,
EMPNAME varchar2(20),
SALARY  number);
10    Bill    2000
11    Bill    2000
12    Mark    3000
12    Mark    3000
12    Mark    3000
13    Tom    4000
14    Tom    5000
15    Susan    5000
1. Using rowid
SQL > delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
This technique can be applied to almost scenarios. Group by operation should be on the columns which identify the duplicates.
2. Using self-join
SQL > delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );
3. Using row_number()
SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
This is another efficient way to delete duplicates
4. Using dense_rank()
SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);
Here you can use both rank() and dens_rank() since both will give unique records when order by rowid.
5. Using group by
Consider the EMP table with below rows
10    Bill    2000
11    Bill    2000
12    Mark    3000
13    Mark    3000
SQL > delete from emp where
(empno,empname,salary) in
(
select max(empno),empname,salary from emp
group by empname,salary
);
This technique is only applicable in few scenarios.
Always take extra caution while deleting records. 
1. First identify the duplicates using select.
2. Double verify those are actual  ‘duplicates’ or not
3. Take backup if necessary
4. Apply commit only if you are sure.
Did you find above post useful ? Your comments are highly valuable.

Advertisements

Delete duplicate row from a table

Removing duplicate rows from table

truncate 

https://youtu.be/JeN6ijUdvsA