SQL Queries – Basic to Advanced

0
13

In this article on SQL Queries – Basic to Advanced, I will discuss how to formulate SQL queries.

The following section specifies the database tables that we use for the queries. Also, we create the tables on Oracle 11g Database

Create the following tables.

To begin with, we need to create two tables – EmpDetails, and EmpDesignation. The following tables provide the schema of these two tables respectively.

Field Name Type
EmpId (Primary Key) Integer
EmpName Varchar(20)
Department Varchar(10)
Location Varchar(20)
DOB Date
Gender Varchar(1)
Date of Joining Date
Salary Integer
Schema of the EmpDetails Table
Field Name Type
D_id (Primary Key) Integer
EmpId (Foreign Key) Integer
Designation Varchar(20)
Project Varchar(2)
Schema of the Emp Designation Table

After that Insert the following rows.

EmpId EmpName Department Location DOB Gen der DateOfJoin ng Salary
10 Arush Aggarwal HR Hyderabad (HYD) 01-12-1976 M 01-05-2022 75000
20 Sumit Chaudhary Admin Delhi (DEL) 01-01-1968 M 05-15-2022 25000
30 Riya Khanna Account Mumbai (MUM) 05-23-1970 F 05-15-2022 45000
40 Priya HR Bengaluru (BNG) 15-12-1990 F 01-05-2022 28000
50 Anmol Singh HR Mumbai (MUM) 01-18-2001 M 02-05-2022 34000
60 Avinash Admin Delhi (DEL) 05-05-2001 M 01-05-2022 30000
70 Mrinal Pandey Technical Delhi (DEL) 12-01-1996 F 05-15-2022 35000
80 Anurag HR Bengaluru (BNG) 05-15-1992 M 01-05-2020 20000
90 Richa HR Bengaluru (BNG) 10-01-1998 F 01-05-2020 20000
100 Sapna Gupta Technical Delhi (DEL) 12-09-1998 F 01-05-2022 40000
110 Pranjal Technical Delhi (DEL) 06-20-1998 F 01-05-2022 42000
120 Amit Kumar Account Delhi (DEL) 07-12-2005 M 05-15-2022 15000
130 Raghav Account Delhi (DEL) 09-30-2006 M 05-15-2022 15000
140 Vinay Admin Mumbai (MUM) 01-08-2001 M 01-05-2020 20000
150 Ashish Kumar Admin Mumbai (MUM) 02-17-1997 M 01-05-2020 20000
Sample Data for the EmpDetails Table

Similarly, add some records in the Emp Designation Table.

D_id EmpId Designation Project
1 10 Manager P1
2 20 Executive P2
3 30 Lead P2
4 20 Manager P3
5 10 Executive P3
6 10 Manager P4
7 40 Executive P4
8 50 Executive P4
9 60 Executive P5
10 70 Executive P5
11 10 Manager P5
12 100 Executive P4
13 110 Executive P4
14 10 Lead P4
15 20 Lead P4
Sample Data for the Emp Designation Table

Further, perform the following SQL Queries – Basic to Advanced on the above tables.

  1. Find the average salary of employees.
  2. Also, Show the department name and the average salary for each department
  3. Similarly, get distinct projects from the EmpDesignation table without using a distinct keyword.
  4. Find the second highest salary along with Employee’s name.
  5. Also, Fetch the list of employees with the same salary.
  6. Show all departments along with the number of people in there.
  7. Fetch the last five records from the EmpDetails table.
  8. Similarly, fetch three max salaries from the EmpDetails table.
  9. Fetch the first 50% of records from the EmpDesignation table.
  10. Also, fetch the 5th highest salary without using the TOP or limit method.
  11. Display the complete details of the managers who are also executives.
  12. Show complete details of employees whose DOB is between 01/01/1990 to 31/12/1999 and are grouped according to gender
  13. Fetch details of all employees excluding the employees whose name starts with ‘A’.
  14. Retrieve employee details from the EmpDetails table who have not been assigned any project.
  15. Also, retrieve employee details from the EmpDetails table who is working on at least one project.
  16. Retrieve employee details from the EmpDetails table who are working on maximum projects.
  17. Display complete details of employees group by the project.
  18. Display the departments where employees have joined earliest but are not working on any project.
  19. Show projects which have the highest executives working on them.
  20. Create a Sequence DesignationSequence with minimum value=1000, maximum value=10000, and increment=100.

At first, we need to log in with Oracle username and password as shown below.

Oracle 11g for Running SQL Queries – Basic to Advanced

Further Reading

SQL Practice Exercise

Princites

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here