Sql Query Example

Sql Query Example

05:01:00
1.      Find the manager name from same employee table?
Emp_no
name
Mg_empno
1
Kou
3
2
Kartik
104
3
Manoj
Ans:
Select b.name from employee a, employee b where a.mgr_empno=b.emp_no group by b.name;

1:Gets the number of employees for each department?
Emp_id
Emp_name  Hire_date   Sal
   Manager_id  Dept_id
1
Kartik                                   
                           Soft1
2
Koushik
                           Soft2
3
Santosh
                            ------

Department_id
Department_name
Soft1
Java
Soft2
Networking
--------
--------

SELECT
    e.department_id,
    department_name,
    COUNT(*)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id;

2:Gets the number of employees for each department and sorts the result set based on the number of employees in descending order

SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
ORDER BY
COUNT(*) DESC;

3:Gets the number of employees for each department and sorts the result set based on the number of employees in descending order and whose the number of employees is greater than 5

 SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
COUNT(*) > 5
ORDER BY

COUNT(*) DESC;
  1. Find out the maximum list of 5 salary from emp table?
    Ans:
    Select ename, salary from (select ename, salary from emp  order by salary desc) where rownum<6 order by salary;
  1. Find out all employee name and dept name from emp and dept table?
Emp_id
Emp_name
Dept_id
1
Kartik
Soft1
2
Koushik
Soft2
3
Santosh
------
Dept_id
Dept_name
Soft1
Java
Soft2
Networking
--------
--------
Ans:
Select a.emp_name,b.dept_name from(emp a left outer join dept b on a.dept_id=b.dept_id) where b.dept_id is NULL;
  1. Find out the third position of max salary?
    Select distinct (a.salary) from emp a where 3 = (select count (distinct) b.salary from emp b where (a.salary <=b.salary));
create table Emp(salary int, name String);

 insert into Emp values(10, "Kartik");
insert into Emp values(12, "cha");
insert into Emp values(11, "man");
insert into Emp values(17, "saha");
insert into Emp values(144, "kali");
insert into Emp values(18, "pada");
insert into Emp values(20, "vidya");
insert into Emp values(30, "pith");
insert into Emp values(90, "skv");

 SELECT salary FROM Emp order by salary DESC limit 3,1;    //here 3 is you position 
    

 2. Remove  the duplicate element and display which is not duplicate?

 For example, if a table has the following fields; PKID, ClientID, Name, AcctNo, OrderDate, Charge, I'd like to use the AcctNo, OrderDate and Charge fields to find unique records.

Table

PKID-----ClientID-----Name-----AcctNo-----OrderDate-----Charge
1        JX100        John     12345      9/9/2010      $100.00
2        JX220        Mark     55567      9/9/2010       $23.00
3        JX690        Matt     89899      9/9/2010      $218.00
4        JX100        John     12345      9/9/2010      $100.00
The result of the query would need to be:

PKID-----ClientID-----Name-----AcctNo-----OrderDate-----Charge
2        JX220        Mark     55567      9/9/2010       $23.00
3        JX690        Matt     89899      9/9/2010      $218.00



SELECT PKID, ClientID, Name, AcctNo, OrderDate, Charge FROM tbl
GROUP BY ClientID, Name, AcctNo, OrderDate, Charge
HAVING MAX(ClientID)=1
AND MIN(ClientID)=1
  1. How to select duplicate record from a table?
    Select id from Employee group by id having count (*) >1;

    How to find out duplicate records (duplicate data) in a SQL Server table

     A>
    This technique is good while fatching single column, 

     Example:
    SELECT studentName FROM tblStudentDtl
    GROUP BY studentName
    HAVING count(studentName) > 1

     B>

     if you want more column to fetch you need to modify it like

     Example:
    SELECT studentName, studentAddress FROM tblStudentDtl
    GROUP BY studentName, studentAddress
    HAVING count(*) > 1


    6. Difference between group by and order by?
    Groupby: It is a way to sub-total your results,or perform
    some other 'aggregate' functions on them.
    Orderby: It is a simply a way to sort your results. It
    doesn't affect what shows up in your result set,only what
    order it is displayed. 




GROUP BY vs ORDER BY
Lately, when I was designing a query for a website, I was wondering about the exact difference between group by and order by in a query. The easiest explanation is that order by is doing the sorting of a table and the group by clause is used for aggregation of a field. Ok, this is for the theory, let's see an example:
The following table contains the items bought by several people last week:
cust_id
item
total price
1
balloon
1
2
apple
3
1
apple
4
1
pillow
25
3
plastic bag
1
Order By
Let's see the following query:
SELECT * FROM shopping ORDER BY total_price
The output will be:
cust_id
item
total price
1
balloon
1
3
plastic bag
1
2
apple
3
1
apple
4
1
pillow
25
as you can see the fields have been ordered by the price. The default order is ascending. If you want to specify how the data is ordered, write either ASC or DESC at the end of your query.
Example:
SELECT * FROM shopping ORDER BY total_price DESC
will give the same table, but starting with the pillow.
Group By
Now we are going to use the group by statement. The group by statement, as said before, is especially useful for aggregating, meaning to apply some function. Let's see an example:
SELECT cust_id, SUM(total_price) FROM shopping GROUP BY cust_id
This query returns the total amount of money spent by each customer during all their shoppings. The table returned looks like this:
cust_id
SUM(total_price)
1
30
2
3
3
1
The way you have to understand the query is that we compute the sum of all amounts for each customer. This is expressed by the GROUP BY cust_id. Now, if we would try to do this for each product. This would correspond to the total money gained per product. The query looks like this:
SELECT item, SUM(total_price) FROM shopping GROUP BY item
This query returns the following table:
item
SUM(total_price)
apple
7
balloon
1
pillow
25
plastic bag
1

The Difference between the HAVING and WHERE Clauses in a SQL Query

Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
  1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
  2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3.       The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
  1. Example The difference between the having and where clause is best illustrated by an example. Suppose we have a table called emp_bonus as shown below. Note that the table has multiple entries for employees A and B.
emp_bonus
Employee
Bonus
A
1000
B
2000
A
500
C
700
B
1250
If we want to calculate the total bonus that each employee received, then we would write a SQL statement like this:
select employee, sum(bonus) from emp_bonus group by employee;

WHERE vs HAVING Clause Example in SQL

In this example of WHERE and HAVING clause, we have two tables Employee and Department. Employee contains details of employees e.g. id, name, age, salary and department id, while Department contains id and department name. In order to show, which employee works for which department we need to join two tables on DEPT_ID to get the the department name. Our requirement is to find how many employees are working in each department and average salary of department. In order to use WHERE clause, we will only include employees who are earning  more than 5000. Before executing our query which contains WHERE, HAVING, and GROUP BY clause, let see data from Employee and Department table:
SELECT * FROM Employee;
EMP_ID
EMP_NAME
EMP_AGE
EMP_SALARY
DEPT_ID
1
Virat
23
10000
1
2
Rohit
24
7000
2
3
Suresh
25
8000
3
4
Shikhar
27
6000
1
5
Vijay
28
5000
2
SELECT * FROM Department;
DEPT_ID
DEPT_NAME
1
Accounting
2
Marketing
3
Sales
SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,
Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME;
DEPT_NAME
NUM_EMPLOYEE
AVG_SALARY
Accounting
1
8000
Marketing
1
7000
Sales
2
8000
From the number of employee (NUM_EMPLOYEE) column you can see that only Vijay who work for Marketing department is not included in result set because his earning 5000. This example shows that, condition in WHERE clause is used to filter rows before you aggregate them and then HAVING clause comes in picture for final filtering, which is clear from following query, now Marketing department is excluded because it doesn't pass condition in HAVING clause i..e AVG_SALARY > 7000
SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,
Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME HAVING AVG_SALARY > 7000;
DEPT_NAME
NUM_EMPLOYEE
AVG_SALARY
Accounting
1
8000
Sales
2
8000

Difference between WHERE and HAVING in SQL

Apart from this key difference we have seen in this article, here are few more differences between WHERE and HAVING clause, which is worth remembering and can be used to compare both of them :
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 :
update DEPARTMENT set DEPT_NAME="NewSales" WHERE DEPT_ID=1 ;  // works fine
update DEPARTMENT set DEPT_NAME="NewSales" HAVING DEPT_ID=1 ; // error
Incorrect syntax near the keyword 'HAVING'.: update DEPARTMENT set DEPT_NAME='NewSales' HAVING DEPT_ID=1
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.

Set Operation in SQL

SQL supports few Set operations to be performed on table data. These are used to get meaningful results from data, under different special conditions.

Union

UNION is used to combine the results of two or more Select statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and datatype must be same in both the tables.
union in sql

Example of UNION

The First table,
ID
Name
1
Abhi
2
Adam
The Second table,
ID
Name
2
adam
3
Chester
Union SQL query will be,
select * from First
UNION
select * from second
The result table will look like,
ID
NAME
1
abhi
2
adam
3
Chester

Union All

This operation is similar to Union. But it also shows the duplicate rows.
union all in sql

Example of Union All

The First table,
ID
NAME
1
abhi
2
adam
The Second table,
ID
NAME
2
adam
3
Chester
Union All query will be like,
select * from First
UNION ALL
select * from second
The result table will look like,
ID
NAME
1
abhi
2
adam
2
adam
3
Chester

Intersect

Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same. MySQL does not support INTERSECT operator.
intersect in sql

Example of Intersect

The First table,
ID
NAME
1
abhi
2
adam
The Second table,
ID
NAME
2
adam
3
Chester
Intersect query will be,
select * from First
INTERSECT
select * from second
The result table will look like
ID
NAME
2
adam

Minus

Minus operation combines result of two Select statements and return only those result which belongs to first set of result. MySQL does not support INTERSECT operator.
minus in sql

Example of Minus

The First table,
ID
NAME
1
abhi
2
adam
The Second table,
ID
NAME
2
adam
3
Chester
Minus query will be,
select * from First
MINUS
select * from second
The result table will look like,
ID
NAME
1
abhi

How to MySQL Insert into multiple tables?



No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) 
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.java2blogs.blogspot.com');
COMMIT;








About

I am Kartik Chandra Mandal from Bangalore, India. Working as software engineer in Java technology. Have good knowledge of OWASP and CSRF in security for applications and it's implementation in Java technology.

Next
« Prev Post
Previous
Next Post »
Subscribe to: Post Comments (Atom)