Header Ads Widget

Responsive Advertisement

Sql Query Example

Kartik Chandra Mandal November 10, 2016


Your SQL queries and explanations cover a range of tasks related to employee and department management. Here are a few clarifications and corrections:

  1. Finding the manager's name:

Table raw data

Emp_no

name

Mgr_empno

1

Kou

3

2

Kartik

104

3

Manoj



 

Sql

SELECT b.name

FROM employee a

JOIN employee b ON a.mgr_empno = b.emp_no

GROUP BY b.name;

 

  1. Getting the number of employees for each department:

Table raw data

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

--------

--------

 

Sql

SELECT

    e.dept_id,

    d.dept_name,

    COUNT(*) AS employee_count

FROM

    employees e

INNER JOIN

    departments d ON d.dept_id = e.dept_id

GROUP BY

    e.dept_id, d.dept_name;

 

  1. Sorting by the number of employees in descending order:

Sql

SELECT

    e.dept_id,

    d.dept_name,

    COUNT(*) AS employee_count

FROM

    employees e

INNER JOIN

    departments d ON d.dept_id = e.dept_id

GROUP BY

    e.dept_id, d.dept_name

ORDER BY

    employee_count DESC;

 

  1. Filtering departments with more than 5 employees and sorting in descending order:

Sql

SELECT

    e.dept_id,

    d.dept_name,

    COUNT(*) AS employee_count

FROM

    employees e

INNER JOIN

    departments d ON d.dept_id = e.dept_id

GROUP BY

    e.dept_id, d.dept_name

HAVING

    COUNT(*) > 5

ORDER BY

    employee_count DESC;

 

  1. Finding the top 5 salaries:

Sql

SELECT salary

FROM (SELECT salary FROM emp ORDER BY salary DESC LIMIT 5)

ORDER BY salary;

 

  1. Finding employee names and department names:

Sql

SELECT

    e.emp_name,

    d.dept_name

FROM

    employees e

LEFT JOIN

    departments d ON e.dept_id = d.dept_id

WHERE

    d.dept_id IS NOT NULL;

 

  1. Finding the third highest salary:

Sql

SELECT salary

FROM Emp

ORDER BY salary DESC

LIMIT 2, 1;

 

  1. Removing duplicate records:

Sql

SELECT

    PKID, ClientID, Name, AcctNo, OrderDate, Charge

FROM

    tbl

GROUP BY

    ClientID, Name, AcctNo, OrderDate, Charge

HAVING

    COUNT(*) = 1;

 

  1. Finding duplicate records:

sql

SELECT

    id

FROM

    Employee

GROUP BY

    id

HAVING

    COUNT(*) > 1;

 

For multiple columns:

Sql

SELECT

    studentName, studentAddress

FROM

    tblStudentDtl

GROUP BY

    studentName, studentAddress

HAVING

    COUNT(*) > 1;

 

  1. Difference between GROUP BY and ORDER BY:

Ø  GROUP BY: Used to aggregate data into groups and apply aggregate functions like COUNT, SUM, AVG, etc. It's used when you need to perform operations on groups of data.

Ø  ORDER BY: Used to sort the results in ascending or descending order. It affects only the order of the result set and does not change the data itself.

 

  1. GROUP BY vs ORDER BY

Ø  ORDER BY:

ü  Purpose: Sorts the result set of a query based on one or more columns.

ü  Usage: You can specify ASC (ascending) or DESC (descending) to control the sort order.

ü  Example:

Table raw data

cust_id

item

total price

1

balloon

1

2

apple

3

1

apple

4

1

pillow

25

3

plastic bag

1

 

Sql

SELECT * FROM shopping ORDER BY total_price;

 

Table out put

cust_id

item

total price

1

balloon

1

3

plastic bag

1

2

apple

3

1

apple

4

1

pillow

25

 

Ø  GROUP BY:

ü  Purpose: Aggregates rows that have the same values in specified columns into summary rows. Often used with aggregate functions like SUM(), COUNT(), etc.

ü  Usage: Groups the results based on one or more columns and applies aggregate functions.

ü  Example:

sql

SELECT cust_id, SUM(total_price) FROM shopping GROUP BY cust_id;

 

Table out put

cust_id

SUM(total_price)

1

30

2

3

3

1

 

 

sql

SELECT item, SUM(total_price) FROM shopping GROUP BY item

 

Table out put

item

SUM(total_price)

apple

7

balloon

1

pillow

25

 

This calculates the total price spent by each customer.

  1. HAVING vs WHERE

Ø  WHERE:

ü  Purpose: Filters rows before any grouping or aggregation is done. It applies to individual rows.

ü  Usage: Used with queries to filter rows that match a certain condition.

ü  Example:

Table of raw data

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

 

DEPT_ID

DEPT_NAME

1

Accounting

2

Marketing

3

Sales

 

Sql

SELECT * FROM Employee WHERE EMP_SALARY > 5000;

 

Ø  HAVING:

ü  Purpose: Filters groups after the GROUP BY operation has been applied. It is used with aggregate functions.

ü  Usage: Used with GROUP BY to filter groups based on conditions.

ü  Example:

      • Finding the number of employees and the average salary for each department, but only for employees with a salary greater than 5000:

Sql

SELECT d.DEPT_NAME, COUNT(e.EMP_NAME) AS NUM_EMPLOYEE, AVG(e.EMP_SALARY) AS AVG_SALARY

FROM Employee e

JOIN Department d ON e.DEPT_ID = d.DEPT_ID

WHERE e.EMP_SALARY > 5000

GROUP BY d.DEPT_NAME;

 

      • Finding the number of employees and the average salary for each department, but only for employees with a salary greater than 5000 and where the average salary is not greater than 7000:

 

sql

SELECT d.DEPT_NAME, COUNT(e.EMP_NAME) AS NUM_EMPLOYEE, AVG(e.EMP_SALARY) AS AVG_SALARY

FROM Employee e

JOIN Department d ON e.DEPT_ID = d.DEPT_ID

WHERE e.EMP_SALARY > 5000

GROUP BY d.DEPT_NAME

HAVING AVG(e.EMP_SALARY) > 7000;

 

 

Ø  SQL UPDATE Statement

ü  Valid Update Query:

sql

UPDATE DEPARTMENT

SET DEPT_NAME = "NewSales"

WHERE DEPT_ID = 1;

 

    • Explanation: This query updates the department name to "NewSales" where the department ID is 1.

ü  Invalid Update Query:

Sql

UPDATE DEPARTMENT

SET DEPT_NAME = "NewSales"

HAVING DEPT_ID = 1;

 

    • Explanation: The HAVING clause cannot be used in an UPDATE statement. The HAVING clause is meant for use with SELECT statements to filter grouped results. In UPDATE statements, you should use WHERE to specify the conditions for updating rows.

Key Points

Ø  HAVING vs WHERE:

ü  WHERE is used to filter rows before grouping.

ü  HAVING is used to filter groups after grouping.

Ø  UPDATE Syntax:

ü  The UPDATE statement should use WHERE to filter the rows that need updating. HAVING is not valid in this context.

 

 

  1. Set Operations in SQL

Ø  UNION:

ü  Purpose: Combines results from two or more queries and removes duplicates.

ü  Usage: Ensures unique records in the result set.

ü  Example:

 

The First table raw data,

ID

Name

1

Abhi

2

Adam

The Second table raw data,

ID

Name

2

adam

3

Chester

 

Sql

SELECT * FROM First

UNION

SELECT * FROM Second;

 

Ø  UNION ALL:

ü  Purpose: Combines results from two or more queries including duplicates.

ü  Usage: Use when you want to include all records from the combined queries.

ü  Example:

Sql

SELECT * FROM First

UNION ALL

SELECT * FROM Second;

 

Ø  INTERSECT:

ü  Purpose: Returns records that are common to both queries.

ü  Usage: Not supported in MySQL; used in other SQL databases.

ü  Example:

Sql

SELECT * FROM First

INTERSECT

SELECT * FROM Second;

 

Ø  MINUS:

ü  Purpose: Returns records from the first query that are not in the second query.

ü  Usage: Not supported in MySQL; used in other SQL databases.

ü  Example:

sql

SELECT * FROM First

MINUS

SELECT * FROM Second;

 

  1. Inserting into Multiple Tables in MySQL

You can't insert into multiple tables in a single INSERT statement in MySQL, but you can achieve this using transactions. Here’s a simple example:

sql

BEGIN;

 

INSERT INTO users (username, password)

VALUES ('test', 'test');

 

INSERT INTO profiles (userid, bio, homepage)

VALUES (LAST_INSERT_ID(), 'Hello world!', 'http://www.example.com');

 

COMMIT;

 

 


 

Tags:

Post a Comment

0 Comments

Post a Comment