SQL Queries Examples

-

SQL Queries

Create the following Tables:

LOCATION
Location_ID
Regional_Group
200
NEW YORK
300
DALLAS
400
CHICAGO
500
BOSTON

DEPARTMENT
Department_ID
Name
Location_ID
10
ACCOUNTS
200
20
RESEARCH
400
30
SALES
300
40
OPERATIONS
500


JOB
Job_ID
Function
101
CLERK
102
STAFF
103
ANALYST
104
SALESPERSON
105
MANAGER
106
PRESIDENT



EMPLOYEE
EMPLOYEE_ID
LAST_NAME
FIRST_NAME
MIDDLE_NAME
JOB_ID
MANAGER_ID
HIREDATE
SALARY
COMM
DEPARTMENT_ID
1111
SMITH
JOHN
A
101
5001
15-APR-2012
15000
NULL
20
1112
ALLEN
KEVIN
B
104
5002
20-MAR-2011
18000
300
30
1113
DOYLE
JEAN
C
105
5003
13-JAN-2012
28000
0
30
1114
DENNIS
LYNN
D
105
5004
16-MAR-2012
35000
NULL
30
1115
BAKER
LESLIE
E
105
5005
8-JUN-2012
17500
NULL
40
1116
WARK
CYNTHIA
F
104
5006
14-FEB-2011
52750
500
30


Queries based on the above tables

Simple Queries:

  1. List all the employee details
A: SELECT * FROM Employee;

  1. List all the department details
A: SELECT * FROM Department;

  1. List all job details
A: SELECT * FROM Job;

  1. List all the locations
A: SELECT * FROM Location;

  1. List first name, last name, salary, commission for all employees
A: SELECT first_name, last_name, salary, commission
         FROM Employee;

  1. List employee_id, last name, department id of all employees and with column names employee_id as “Emp ID”, last name as “Emp Name”, department id as  “Dept  ID”
A: SELECT employee_id as “Emp ID”,
                    last_name as “Emp Name",
                   department_id as “Dept ID”
         FROM Employee;

  1. List employees annual salary with their names only.
A: SELECT last_name, (salary*12) AS “Annual salary” FROM employee
 
Where Conditions:

  1. List all the employee of name “SMITH”
A: SELECT * from employee where last_name=’SMITH’

  1. List all the employees who are working in department 20
A: SELECT * FROM employee
                   WHERE department_id=20

  1. List out the employees who are earning salary between 3000 and 4500
A: SELECT * FROM employee
                   WHERE salary BETWEEN 3000 AND 4500

  1. List out the employees who are working in department 10 or 20
A: i) SELECT * FROM employee
                   WHERE department_id IN (20,30)
    ii) SELECT * FROM employee
                   WHERE department_id=20 OR department_id=30

  1. List the employees who are not working in department 10 or 30
A: SELECT last_name, salary, commission, department_id
    FROM Employee
    WHERE department_id NOT IN (10,30)

  1. List all the employees whose name starts with “S”
A: SELECT * FROM employee
                   WHERE last_name LIKE ‘S%’

  1. List all the employees whose name start with “S” and end with “H”
A: SELECT * FROM employee
              WHERE last_name LIKE ‘S%H’

  1. List all the employees whose name length is 4 and start with “S”
A: SELECT * FROM employee
                    WHERE last_name like ‘S___’

  1. List all the employees who are working in department 10 and draw the salaries more than 3500
A: SELECT * FROM employee
                   WHERE department_id=10 AND salary>3500

  1. List all the employees who are not receiving commission.
A: SELECT * FROM employee
                   WHERE commission IS NULL OR commission = 0

Order By Clause:

  1. List all the employee id, last name in ascending order based on the employee id.
A: i) SELECT employee_id, last_name
                FROM employee
                ORDER BY employee_id
    ii) SELECT employee_id, last_name
                FROM employee
                ORDER BY employee_id ASC
Note: SQL performs sorting in ascending order by default. So, it is optional to mention “ASC” if you need records in ascending order

  1. List all employee id, name in descending order based on salary column
A: SELECT employee_id, last_name, salary
                 FROM employee
                 ORDER BY salary DESC

  1. List all the employee details according to their last_name in ascending order and salaries in descending order
A: SELECT employee_id, last_name, salary
                 FROM employee
                 ORDER BY last_name, salary DESC

  1. List all the employee details according to their last_name in ascending order and then on department_id in descending order.
A: SELECT employee_id, last_name, salary
                      FROM employee
                      ORDER BY last_name, deparment_id DESC


Group By & Having Clause:

  1. Find how many employees are working in organization based on departments wise
A:  SELECT department_id, count(*) AS Total
                 FROM employee
                 GROUP BY department_id

  1. Find the department wise maximum salary, minimum salary, average salary of the employees
A: SELECT department_id, MAX(salary), MIN(salary), AVG(salary)
                 FROM employee
                 GROUP BY department_id

  1. Find the job wise maximum salary, minimum salary, average salaries of the employees.
A: SELECT job_id, MAX(salary), MIN(salary), AVG(salary)
           FROM employee
           GROUP BY job_id

  1. Find the number of employees joined in every month in ascending order.
A: SELECT to_char(hire_date,’yyyy’) Year,
                 to_char(hire_date,’mon’) Month,
                 COUNT(*) AS Total
    FROM employee
    GROUP BY to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
 
  1. Find the departments having minimum of four employees
A: SELECT department_id, COUNT(*)
     FROM employee
     GROUP BY department_id
     HAVING COUNT(*) >= 4

  1. Find how many employees are joined in month of January
A: SELECT to_char(hire_date,’mon’) month,
                 COUNT(*)
     FROM employee
     GROUP BY to_char(hire_date,’mon’)
      HAVING to_char(hire_date,’mon’)=’jan’

  1. Find how many employees who are joined in January or September month
A: SELECT to_char(hire_date,’mon’) month,
     COUNT(*)
     FROM employee
     GROUP BY to_char(hire_date,’mon’)
     HAVING to_char(hire_date,’mon’) IN (‘jan’,’sep’)

  1. Find how many employees are joined in year 2012.
A: SELECT to_char(hire_date,’yyyy’) Year,
     COUNT(*)
     FROM employee
     GROUP BY to_char(hire_date,’yyyy’)
     HAVING to_char(hire_date,’yyyy’)=2012

  1. Find how many employees are joined in each month of 2012
A: SELECT to_char(hire_date,’yyyy’) Year,
                 to_char(hire_date,’mon’) Month,
     COUNT(*) “Total”
     FROM employee
     WHERE to_char(hire_date,’yyyy’)=2012
     GROUP BY to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)

  1. Find how many employees are joined in month of June 2012
A: SELECT to_char(hire_date,’yyyy’) Year,
                 to_char(hire_date,’mon’) Month,
     COUNT(*) “Total”
     FROM employee
     WHERE to_char(hire_date,’yyyy’)=2012 AND
                 to_char(hire_date,’mon’)=’jun’
      GROUP BY to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)

  1. Find department id, having greater than or equal to 3 employees joined in month of April 2012
A: SELECT department_id,
                 COUNT(*) “Total”
     FROM employee
     WHERE to_char(hire_date,’yyyy’)=2012 AND
                 to_char(hire_date,’mon’)=’apr’
     GROUP BY to_char(hire_date,’yyyy’),
                     to_char(hire_date,’mon’),
                     department_id
     HAVING COUNT(*) >= 3


Sub-Queries

  1. Find the employee who got the maximum salary
A: SELECT * FROM employee
                    WHERE salary = (SELECT MAX(salary) FROM employee)

  1. Find the employees who are working in Sales department
A: SELECT * FROM employee
    WHERE department_id IN (SELECT department_id
                                            FROM department WHERE name=’SALES’)

  1. Find the employees who are all working as “Clerk”.
A: SELECT * FROM employee
 WHERE job_id IN (SELECT job_id FROM job WHERE function=’CLERK’)

  1. Update the employee salaries, who are working as Clerk on the basis of 10%
A: UPDATE employee
    SET salary = (salary*10/100)
    WHERE job_id = (SELECT job_id FROM job WHERE function=’CLERK’)

  1. Delete the employees who are working in Accounts department
A: DELETE FROM employee
    WHERE department_id = (SELECT department_id
                                           FROM department
                                           WHERE name=’ACCOUNTING’)

  1. Retrieve the second highest salary drawing employee details
A: SELECT TOP 1 FROM (SELECT DISTINCT TOP 2 salary
                                      FROM employee
                                     ORDER BY salary DESC) tempTable
     ORDER BY tempTable.salary ASC

  1. Find the Nth highest salary drawing employee details
A: SELECT TOP 1
   FROM (SELECT DISTINCT TOP n salary
         FROM employee
         ORDER BY salary DESC) a
   ORDER BY salary

where n > 1 (n is always greater than one)

Sub-Query operators: (ALL, ANY, SOME, EXISTS)

  1. Find the employees who earn more than every employee in department 30.
A: SELECT *
     FROM employee
     WHERE salary > ALL (SELECT salary
                                     FROM FROM employee
                                     WHERE department_id = 30)

  1. Find the employees who earn more than the lowest salary in department 30.
A: SELECT * FROM employee
     WHERE salary > ANY (SELECT salary
                                      FROM employee
                                      WHERE department_id=30)

  1. Find the department which has no employees.
A: SELECT employee_id, last_name, department_id
    FROM employee e
    WHERE NOT EXISTS (SELECT department_id
                                   FROM department d
                                   WHERE d.department_id = e.department_id)

Co-Related Sub Queries:

43. Find the employees who earn more than the average salary for their department
A: SELECT employee_id, last_name, salary, department_id
      FROM employee e
     WHERE e salary > (SELECT AVGg(salary)
                                 FROM employee
                                 WHERE department_id = e.department_id)

Joins

Simple join

  1. Retrieve all employees with their department names
A: SELECT employee_id, last_name, name
    FROM employee e, department d
    WHERE e.department_id = d.department_id

  1. Retrieve employees with their designations (jobs)
A:  SELECT employee_id, last_name, function
     FROM employee e, job j
     WHERE e.job_id = j.job_id

46. Retrieve the employees with their department name and regional groups
A: SELECT employee_id, last_name, name, regional_group
   FROM employee e, department d, location l
 WHERE e.department_id = d.department_id
    AND  d.location_id = l.location_id

50. Find how many employees are working in each department with their department name.
A: SELECT d.name, COUNT(*)
    FROM employee e, department d
    WHERE d.department_id = e.department_id
    GROUP BY d.name

  1. Find how many employees are working in sales department.
A: SELECT name, COUNT(*)
     FROM employee e, department d
     WHERE d.department_id = e.department_id
     GROUP BY d.name
     HAVING d.name=’SALES’

52. Find the department having greater than or equal to 10 employees and display the department names in ascending order
A: SELECT name, COUNT(*)
    FROM employee e, department d
    WHERE d.department_id = e.department_id
    GROUP BY name
    HAVING COUNT (*) >= 6
    ORDER BY name

  1. Find how many employees are working in “New York”.
A: SELECT regional_group, COUNT(*)
    FROM employee e, department d, location l
    WHERE e.department_id = d.department_id
         AND d.location_id = l.location_id
         AND regional_group = ’NEW YORK’
    GROUP BY regional_group

Non – Equi Join:

  1. List the number of employees on grade wise.
A: SELECT grade_id, COUNT(*)
  FROM employee e, salary_grade s
  WHERE salary BETWEEN lower_bound and upper_bound
  GROUP BY grade_id
  ORDER BY grade_id desc

Self Join:

  1. Display the employee details with their manager names
A: SELECT e.last_name emp_name, m.last_name, mgr_name
    FROM employee e, employee m
    WHERE e.manager_id = m.employee_id

  1. Retrieve the employee details who earn more than their manager’s salary
A: SELECT e.last_name “emp name”, e.salary “emp salary”,
                m.last_name “mgr_name”,
                m.salary “manager salary”
   FROM employee e, employee m
   WHERE e.manager_id = m.employee_id AND m.salary < e.salary

  1. Retrieve the number of employees working under every manager
A: SELECT m.manager_id “Manager ID”,
                 m.last_name “Manager Name”,
                COUNT(*)
     FROM employee e, employee m
     WHERE e.employee_id = m.manager_id
     GROUP BY m.manager_id, m.last_name

Outer Join:

  1. Display employee details with all departments.
A: SELECT last_name, d.department_id, d.name
     FROM employee e, department d
     WHERE e.department_id(+)= d.department_id

  1. Display all employees in sales or operation departments.
A: SELECT last_name, d.department_id, d.name
     FROM employee e, department d
     WHERE e.department_id(+)=d.department_id
            AND d.department_id IN
                             (SELECT department_id
                               FROM department
                               WHERE name IN (‘SALES’,’OPERATIONS’))

UNION Operators:

  1. Find the distinct jobs in Sales and Accounts Departments.
A: SELECT function
     FROM job
     WHERE job_id IN (SELECT job_id
                                FROM employee
                                WHERE department_id = (SELECT department_id
                                                                      FROM department
                                                                      WHERE name=’SALES’)
                                )
 UNION
SELECT function
FROM job
WHERE job_id IN (SELECT job_id
                         FROM employee
                         WHERE department_id = (SELECCT department_id
                                                              FROM department
                                                              WHERE name=’ACCOUNTS’)
                           )

Comments

  1. sssa9/01/2016

    25 no. query showing invalid character error plz help me

    ReplyDelete

Post a Comment

Popular posts from this blog

Auto Scroll in Common Controls

-
This example shows, how to programatically autoscroll WinForm controls TextBox, ListBox, ListView, TreeView and DataGridView. Autoscroll to the end of box or view is useful. For example, in situations when you use one of these components as the output log window. Usually you add the items to the box or view and you want to be sure that the recently added item is visible without the necessary to do it manually. Though it is not difficult to solve this problem, .NET doesn't provide any unified way how to do it. TextBox autoscroll Code: textBox1.SelectionStart = textBox1.Text.Length; textBox1.ScrollToCaret(); ListBox autoscroll Code: listBox1.SelectedIndex = listBox1.Items.Count - 1; listBox1.SelectedIndex = -1; ListView autoscroll Code: listView1.EnsureVisible(listView1.Items.Count - 1); TreeView autoscroll Code: treeView1.Nodes[treeView1.Nodes.Count - 1].EnsureVisible(); DataGridView autoscroll Code: dataGridView1.FirstDisplayedCell = ...
Read more

Convert typed library (.tlb) to .net assembly?

-
Sometimes we have to use the legacy code(C, C++) in .Net projects which is provided by third party vendors. Most of the times these guys will provide, equivalent .net framework typed library (.tlb) of that legacy code. Then, how to refer those in our .net projects and make use of functionality provided by it?   Follow the below steps, Keep ".tlb" file which is nothing but legacy typed library Open windows command prompt Go to directory, " C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin " or     "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin". It depends on which version of visual studio you had. Enter the following and execute, TlbImp.exe "E\MyLibrary.tlb" . Here, replace "E:\MyLibrary.tlb" with path to your tlb file. It will import the .net framework type library to assembly.i.e, .dll and it can be found at path "C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin" with name ...
Read more

Disable close button on form - C#

-
In one of my projects, I had to implement a form with Close[X] button Hide, so that the user cannot leave the form until it finishes the data processing. From the form designer window in VS.Net 2005, it is possible to hide the Minimize box and Maximize box. But there is no property called Close or CloseBox. Then I had tried with by setting form property ControlBox = false . With this i am able to achieve hiding the Close/Minimize/Maximize buttons. But at the same time there is a side effect.i.e., Form icon(which is generally displayed on form title bar)  also hidden.  So after little bit investigation came up with the following hack to get,  i)  disable the close button  ii) displaying the form icon To achieve this, we have to override CreateParams property of Form base class as follows, Code: private const int CP_DISABLE_CLOSE_BUTTON = 0x200; protected override CreateParams CreateParams { get ...
Read more