SQL Queries – Basic to Advanced

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.

• EmpDetails

• EmpDesignation

After that Insert the following rows.

• EmpDetails

Similarly, add some records in the EmpDesignation Table.

• EmpDesignation

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 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.

Further Reading

SQL Practice Exercise

• AI
• Android
• Angular
• ASP.NET
• Augmented Reality
• AWS
• Bioinformatics
• Biometrics
• Blockchain
• Bootstrap
• C
• C#
• C++
• Cloud Computing
• Competitions
• Courses
• CSS
• Cyber Security
• Data Science
• Data Structures and Algorithms
• Data Visualization
• Datafication
• Deep Learning
• DevOps
• Digital Forensic
• Digital Trust
• Digital Twins
• Django
• Docker
• Dot Net Framework
• Drones
• Elasticsearch
• ES6
• Extended Reality
• Flutter and Dart
• Full Stack Development
• Git
• Go
• HTML
• Image Processing
• IoT
• IT
• Java
• JavaScript
• Kotlin
• Latex
• Machine Learning
• MEAN Stack
• MERN Stack
• Microservices
• MongoDB
• NodeJS
• PHP
• Power Bi
• Projects
• Python
• Quantum Computing
• React
• Robotics
• Rust
• Scratch 3.0
• Shell Script
• Smart City
• Software
• Solidity
• SQL
• SQLite
• Tecgnology
• Tkinter
• TypeScript
• VB.NET
• Virtual Reality
• Web Designing
• WebAssembly
• XML

Princites