Pattern

Design Pattern

1. creational pattern 2. structural Pattern 3. Behavioral Pattern

1. Creational Pattern

It have some types. They are Abstract factory, Singleton Factory ,prototype pattern etc..


a. singleton factory:- No more than one instance . It provides a single global point of access to that instance.

For Example coding: (Database Connection)

public class DatabaseConnectionFactory {

private static DatabaseConnectionFactory DBfactory = new DatabaseConnectionFactory();

public static DatabaseConnectionFactory getInstance() {
return DBfactory;
}

private DatabaseConnectionFactory() {

}

public Connection getDBConnection() {
DatabaseConnect dbase = new DatabaseConnect();
return dbase.getConnection();
}

}

b. Abstract factory:- Provides an interface to create and return one of several families of related objects.

c. Prototype Pattern :- Initialized and instantiated class and copies or clone it to new instances rather than creating new instances.

sql Query

1. only show duplicate empname

select empname from emp group by empname having count(empname)>1

2. Show duplicate empname and count

select empname, count(empname) as countemp from emp group by empname having count(empname)>1

Join

------

1. Left Join (Select all fields from firsttable and match fields from secondtable)

select * from firsttable f1 left join secondtable s1 on f1.id=s1.id

2. rightJoin (Select all fields from secondtable and match fields from firsttable)

select * from firsttable f1 right join secondtable s1 on f1.id=s1.id

3. join (match the two table )

select * from firsttable f1 join secondtable s1 on f1.id=s1.id

Difference between Store procedure and function
-----------------------------------------------------------

1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations

on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some

operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can not be invoked from SQL Statements. Execute. SELECT
operating system can be invoked from SQL Statements. Execute. SELECT

6.Functions can run an executable file from SQL SELECT or an action query.
operating system use Execute or Exec to run

difference between delete and truncate

Delete we can use where condition.

Truncate we can not use where cond.

Delete is slower than than Truncate.

Truncate is faster than delete.

Delete is a DML command but Truncate is not a DML.

Delete remove one or all the records but Truncate always delete all the records.

Delete watermark some time increase and decrease but in Truncate watermark always static.

What is the difference between groupby and orderby?

Group by is used to group set of values based on one or more values.
Order by is to sort values either in ascending or descending order

groupby is always used with having clause
whereas we do not use having clause with orderby.


What is a basic difference between having and where clause?

If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.