JOINS

SELECT * FROM "PUBLIC"."CUSTOMER"

CustomerId	CustomerName	ContactName	Country
1	Aditya Sharma	adi	India
2	Priyanka Sharma	priya	India

SELECT * FROM "PUBLIC"."ORDERS"

ORDERID	ORDERDATE	PNAME	CUSTOMER
1	2228-12-27 20:34:31.23	CARD	1
2	2228-12-27 20:34:31.23	PHONE

Simple join that you know and meaningfull too …

SELECT CUSTOMER.CUSTOMERNAME , ORDERS.PNAME

FROM CUSTOMER , ORDERS

WHERE

CUSTOMER.CUSTOMERID = ORDERS.CUSTOMERID

Replace , between table name with JOIN and where with ON

,	,	,	,	,	,	j	join	Join	JOIN
WHERE	where	Is	Transformed	To	ON	ON	ON	ON	ON

SELECT Customer.CUSTOMERNAME , Orders.PNAME

FROM CUSTOMER , JOIN ORDERS

WHERE  ON

Customer.CUSTOMERID=Orders.CUSTOMERID

SELECT Customer.CUSTOMERNAME , Orders.PNAME

FROM CUSTOMER INNER JOIN ORDERS

ON  Customer.CUSTOMERID=Orders.CUSTOMERID

CUSTOMERNAME	PNAME
Aditya Sharma	CARD

Order of tables are not important ie we can swap . Result will be same.

SELECT Customer.CUSTOMERNAME , Orders.PNAME

FROM ORDERS INNER JOIN CUSTOMER

ON  Customer.CUSTOMERID=Orders.CUSTOMERID

Left Join and Right Join and full join

LEFT TABLE		RIGHT TABLE
CUSTOMER		ORDERS
CustomerId	CUSTOMERNAME	ORDERID	PNAME	CUSTOMER
1	Aditya  Sharma	1	CARD	1
2	Priyanka Sharma	2	PHONE

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match

SELECT Customer.CUSTOMERNAME , Orders.PNAME

FROM CUSTOMER  RIGHT JOIN ORDERS

ON   Customer.CUSTOMERID=Orders.CUSTOMERID

CUSTOMERNAME	PNAME
Aditya Sharma	CARD
[null]	PHONE

SELECT Customer.CUSTOMERNAME , Orders.PNAME

FROM CUSTOMER LEFT JOIN ORDERS

ON  Customer.CUSTOMERID=Orders.CUSTOMERID

CUSTOMERNAME	PNAME
Aditya Sharma	CARD
Priyanka Sharma	[null]

SELECT Customer.CUSTOMERNAME , Orders.PNAME

FROM CUSTOMER FULL JOIN ORDERS

ON  Customer.CUSTOMERID=Orders.CUSTOMERID

CUSTOMERNAME	PNAME
Aditya Sharma	CARD
Priyanka Sharma	[null]
[null]	PHONE

The following hibernate code inserts in the table:

      public static void main(String[] args) {

            AnnotationConfiguration configuration = new AnnotationConfiguration();

            configuration.addAnnotatedClass(beans.Customer.class);

            configuration.addAnnotatedClass(beans.Orders.class);

            configuration.configure();

            new SchemaExport(configuration).create(true, true);

            SessionFactory factory = configuration.buildSessionFactory();

            Session session = factory.openSession();

            session.beginTransaction();

            Customer customer = new Customer("Parikshit Sharma", "parik", "India");

            Customer customer1 = new Customer("Leena Sharma", "leenah", "India");

            Customer customer2 = new Customer("Shivam Sharma", "shiv", "India");

            Customer customer3 = new Customer("Aditya Sharma", "adi", "India");

            Customer customer4 = new Customer("Priyanka Sharma", "priya", "India");

           

            Orders order1 = new Orders(new Date(),"TV",customer);

            Orders order2 = new Orders(new Date(817289471230L),"FRIGDE",customer);

            Orders order3 = new Orders(new Date(8172894471230L),"AC",customer);

            Orders order4 = new Orders(new Date(8172389471230L),"PUMP",customer1);

            Orders order5 = new Orders(new Date(8172689471230L),"JUG",customer1);

            Orders order6 = new Orders(new Date(8172894771230L),"TRAY",customer2);

            Orders order7 = new Orders(new Date(8137289471230L),"CYCLE",customer2);

            Orders order8 = new Orders(new Date(8172894717230L),"TOY",customer3);

            Orders order9 = new Orders(new Date(8172889471230L),"CARD",customer3);

            Orders order10 = new Orders(new Date(8172889471230L),"PHONE");

            Orders order11 = new Orders(new Date(8172889471230L),"TEA");

           

//          session.save(order1);

//          session.save(order2);

//          session.save(order3);

//          session.save(order4);

//          session.save(order5);

//          session.save(order6);

//          session.save(order7);

//          session.save(order8);

            session.save(order9);

            session.save(order10);

            //session.save(order11);

            session.save(customer4);

                 

            session.getTransaction().commit();

            session.close();

      }

@Entity

public class Customer {

      @Id

      @GeneratedValue

      private int customerID;

      private String customerName;

      private String contactName;

      private String country;

      @OneToMany(targetEntity = Orders.class, mappedBy = "customerDetails", cascade = CascadeType.ALL)

      private List<Orders> customerOrders = new ArrayList<Orders>();

      @Entity

public class Orders {

     

      @Id

      @GeneratedValue

      private int orderID;

      private Date orderDate;

     

      private String pname;

      @ManyToOne(cascade=CascadeType.ALL)

      @JoinColumn(name="customerId")

      private Customer customerDetails;

     

Self Join

EMPID	EMPNAME	MGRID
1	PARIKSHIT	5
2	SUBODH	5
5	NAVNEET	10
10	TARUN	20
20	GIRJA	45

List the names of all employee together with the names of their manager

For self join consider it as a two tables as e1 and e2

E1                                                                    E2

EMPID	EMPNAME	MGRID		EMPID	EMPNAME	MGRID
1	PARIKSHIT	5		1	PARIKSHIT	5
2	SUBODH	5		2	SUBODH	5
5	NAVNEET	10		5	NAVNEET	10
10	TARUN	20		10	TARUN	20
20	GIRJA	45		20	GIRJA	45

SELECT e1.EMPLOYEENAME  EMP_NAME , e2.EMPLOYEENAME  MGR_NAME  FROM EMPLOYEE e1 , EMPLOYEE e2 where e1.MGRID = e2.EMPID

EMP_NAME	MGR_NAME
PARIKSHIT	NAVNEET
SUBODH	NAVNEET
NAVNEET	TARUN
TARUN	GIRJA

Java code to insert the table

@Entity

public class Employee {

     

    @Id   

      private int empId;     

      private String employeeName;

      private String job;

      private int mgrId;

      private int deptNo;

     

      public static void main(String[] args) {

            AnnotationConfiguration configuration = new AnnotationConfiguration();

            configuration.addAnnotatedClass(Employee.class);

            configuration.configure();

            new SchemaExport(configuration).create(true, true);

            SessionFactory factory = configuration.buildSessionFactory();

            Session session = factory.openSession();

            session.beginTransaction();

            session.save(new Employee(1,"PARIKSHIT", "TM", 5, 911));

            session.save(new Employee(2,"SUBODH", "TM", 5, 911));

            session.save(new Employee(5,"NAVNEET", "TL", 10, 420));

            session.save(new Employee(10,"TARUN", "GL", 20, 420));

            session.save(new Employee(20,"GIRJA", "PM", 45, 1008));

            session.getTransaction().commit();

            session.close();

      }

     

      UNION

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

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Example:

Now we have another table called PREMIUMCUSTOMER which holds the data of our special customers

SELECT * FROM "PUBLIC"."PREMIUMCUSTOMER"

CustomerId	CustomerName	ContactName	Country
1	Parikshit Sharma	parik	India
2	Aditya Sharma	adi	India

We have old CUSTOMER table also

SELECT * FROM "PUBLIC"."CUSTOMER"

CustomerId	CustomerName	ContactName	Country
1	Priyanka Sharma	priya	India
2	Aditya Sharma	adi	India

SELECT * FROM "PUBLIC"."PREMIUMCUSTOMER"

UNION

SELECT * FROM "PUBLIC"."CUSTOMER"

The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SELECT * FROM "PUBLIC"."PREMIUMCUSTOMER"

UNION ALL

SELECT * FROM "PUBLIC"."CUSTOMER"

CustomerId	CustomerName	ContactName	Country
1	Parikshit Sharma	parik	India
2	Aditya Sharma	adi	India
1	Priyanka Sharma	priya	India
2	Aditya Sharma	adi	India

We can also do battamazii  unless and until each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

That is the following statement is perfectly valid

SELECT CONTACTNAME ,COUNTRY,CUSTOMERID FROM "PUBLIC"."CUSTOMER"

UNION

SELECT PNAME , PNAME, ORDERID FROM "PUBLIC"."ORDERS"

This will produce the following result

CONTACTNAME	COUNTRY	CUSTOMERID
CARD	CARD	1
PHONE	PHONE	2
adi	India	1
priya	India	2

SELECT CUSTOMERS.CUSTOMERNAME , ORDERS.ORDERID

FROM CUSTOMERS , ORDERS

WHERE

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID

Number of Records: 196

SELECT CUSTOMERS.CUSTOMERNAME , ORDERS.ORDERID

FROM CUSTOMERS , ORDERS

WHERE

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID GROUP BY CUSTOMERNAME

Number of Records: 74

SELECT COUNT(*), CUSTOMERS.CUSTOMERNAME , ORDERS.ORDERID

FROM CUSTOMERS , ORDERS

WHERE

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID GROUP BY CUSTOMERNAME

SELECT COUNT(*), CUSTOMERS.CUSTOMERNAME , ORDERS.ORDERID

FROM CUSTOMERS , ORDERS

WHERE

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID GROUP BY CUSTOMERNAME HAVING COUNT(*)>5

SELECT COUNT(*), CUSTOMERS.CUSTOMERNAME , ORDERS.ORDERID

FROM CUSTOMERS , ORDERS

WHERE

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID GROUP BY CUSTOMERNAME HAVING COUNT(*)>5 ORDER BY COUNT(*)

SELECT COUNT(*), CUSTOMERS.CUSTOMERNAME , CUSTOMERS.CUSTOMERID , ORDERS.ORDERID

FROM CUSTOMERS , ORDERS

WHERE

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID GROUP BY CUSTOMERNAME HAVING COUNT(*)>5 ORDER BY COUNT(*)

	COUNT(*)	CustomerName	CustomerID	OrderID
	6	Hungry Owl All-Night Grocers	37	10429
	6	Split Rail Beer & Ale	75	10432
	7	QUICK-Stop	63	10418
	7	Rattlesnake Canyon Grocery	65	10401
	7	Wartian Herkku	87	10437
	10	Ernst Handel	20	10442

How to find the how many orders each employee has placed ??

SELECT Employees.FirstName FROM Employees , Orders where  Employees.EmployeeID =Orders.EmployeeID

,(between Table) à Join

Where à ON

SELECT Employees.FirstName FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID

SELECT  Employees.FirstName FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID group by Employees.EmployeeID

SELECT count(*) Employees.FirstName FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID group by Employees.EmployeeID

SELECT count(*) as Orders, Employees.FirstName FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID group by Employees.EmployeeID

SELECT count(*) as Orders, Employees.FirstName FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID group by Employees.EmployeeID having Orders > 10

SELECT count(*) as Orders, Employees.FirstName FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID group by Employees.EmployeeID having Orders > 10 order by Employees.FirstName

SELECT count(*) as Orders, Employees.FirstName,Employees.EmployeeID FROM Employees join Orders on  Employees.EmployeeID =Orders.EmployeeID group by Employees.EmployeeID having Orders > 10 order by Employees.FirstName

orders	FirstName	EmployeeID
20	Andrew	2
31	Janet	3
27	Laura	8
40	Margaret	4
18	Michael	6
29	Nancy	1
14	Robert	7
11	Steven	5

Understanding SQL group and Having

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_groupby

SELECT count(*) FROM [Customers]

Number of Records: 1 

91

SELECT count(*) FROM [Customers] group by country

Number of Records: 21

SELECT count(*),country FROM [Customers] group by country

Number of Records: 21

SELECT count(*),country FROM [Customers] group by country having count(*)<5

Number of Records: 14

Finding customers who placed more then 5 orders

Another Example

SELECT * FROM [Orders]

SELECT count(*) FROM [Orders]

SELECT count(*) FROM [Orders]  group by CustomerID

SELECT count(*) , CustomerID FROM [Orders]  group by CustomerID

SELECT count(*) , CustomerID FROM [Orders]  group by CustomerID having count(*) > 5

SELECT count(*), CustomerID FROM [Orders] group by CustomerID having count(*) > 5 order by count(*)