SQL Query Basic & Root Concept at a Glance 

Topic Introduction: In this tutorial, we will give a basic & root concept of oracle SQL query in a sample query and structure. If you understand this tutorial clearly it will help you to make any kind of complex query structure at first thinking. 

Structure:    

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

WHERE condition/filter/join

AND multy condition/filter/join

GROUP BY column|expression

HAVING condition with group function

AND multy condition with group function

ORDER BY column|expression|column position|[alias] [ASC|DESC];

SELECT: Introduce the column name or expression which we want to see.

FROM: Table name where we get column specified on select clause or in where clause.

WHERE: Write condition or table joining.

AND: Write multiple conditions or multiple tables joining.

GROUP BY: Give the column name by which we want to make a group if we use the group function on the SELECT clause.

HAVING: Write condition with group function.

AND: If we want to write multiple conditions with group function this and write after the HAVING clause.

ORDER BY: Used for sorting rows.

Let's see examples and descriptions of every clause by extending one by one.

SELECT & FROM Clause 

"SELECT" & "FROM" Clause is the most common and mandatory clause of SQL statement. In the "SELECT" clause we declare columns or expressions which we want to show or display. Alias is an alternative name or heading of column values. it is not mandatory. In the "FROM" we will declare the table name means the source of the column or expression on the select statement. In this query, we want to show (*) all columns & All from the employees' table.

SELECT * FROM employees;

SELECT & FROM Clause  

Like the previous in 'SELECT' declare the column name which we want to show. and 'FROM' contains the table name. In this part, we specified columns and used a 'DISTINCT' function to suppress the duplicate values, and we used an alias named Grosssalary which means giving another name to the column instant of expression.

SELECT DISTINCT

       department_id,

       salary + salary * NVL (commission_pct, 0) / 100 Grosssalary

  FROM employees;

SELECT, FROM  & WHERE Clause   

In this part, we have extended the 'WHERE' clause. Here we used the where clause to restrict data that is which employees are not assigned department. Where clause we use for other purposes also.

SELECT last_name,

       department_id,

       salary + salary * NVL (commission_pct, 0) / 100 Grosssalary

  FROM employees

 WHERE department_id IS NOT NULL;

SELECT, FROM  & WHERE Clause   

In this part, we again used the 'WHERE' clause, here we use another purpose. In a relational database, we can retrieve data from multiple tables, to retrieve data from multiple tables we need to make join one table to another table by primary key & foreign key. In this query, we want to retrieve the employee's last name, department name, and gross salary, by employee name and department name are not in the same table. Employee's name and salary details are on the Employees table. The department name is on the departments' table. To get multiple table data on one select clause we need to use join by Primary key of department table department_id and foreign key of the Employees table department_id. Here we have used table alias on from clause to identify the column of the table such as FROM employees e, departments d  WHERE e.department_id = d.department_id

SELECT e.last_name,

       d.department_name,

       salary + salary * NVL (commission_pct, 0) / 100 Grosssalary

  FROM employees e, departments d

 WHERE e.department_id = d.department_id;

SELECT, FROM, WHERE & AND Clause   

In this part, we use the 'AND' clause which is used for the same tasks as the 'WHERE' clause. If we want to make multiple join or multiple conditions in a select query we need to use the 'AND' clause after the 'WHERE' clause because where can be used only once then the 'AND' clause can be used multiple times as we need.

SELECT e.last_name,

       d.department_name,

       l.city

       salary + salary * NVL (commission_pct, 0) / 100 Grosssalary

  FROM employees e, departments d,locations l

 WHERE e.department_id = d.department_id 

 AND d.location_id = l.location_id

 AND e.department_id IS NOT NULL;

SELECT, FROM, WHERE,  AND & GROUP BY  Clause

Here we have added another clause 'GROUP BY'. If we see group-wise results such as department-wise total salary, average salary, minimum salary, maximum salary, total employee, etc. when we use group functions ('SUM', 'COUNT', 'AVG', 'MAX', 'MIN', etc) on select clause to get this kind of result need to use group by close after 'WHERE' clause. Here we use the column by which we want to make a group. If we use any column in the select clause without a group function column this column is a must on the 'GROUP BY' clause.

  SELECT d.department_name,

         SUM (salary + salary * NVL (commission_pct, 0) / 100) Totalsalary

    FROM employees e, departments d

   WHERE e.department_id = d.department_id AND e.department_id IS NOT NULL

GROUP BY d.department_name;

SELECT, FROM, WHERE, AND, GROUP BY & HAVING Clause

We use the 'HAVING' clause to give conditions with the group function. Here we have used a 'HAVING' clause which says that if the department-wise Total salary is greater than or equal to 20000 then show the result.

  SELECT d.department_name,

         SUM (salary + salary * NVL (commission_pct, 0) / 100) Totalsalary

    FROM employees e, departments d

   WHERE e.department_id = d.department_id AND e.department_id IS NOT NULL

GROUP BY d.department_name

HAVING SUM (salary + salary * NVL (commission_pct, 0) / 100)>=20000;

SELECT, FROM, WHERE, AND, GROUP BY, HAVING & ORDER BY Clause

In this section, we add the 'ORDER BY' clause. It is the last clause on a SQL statement. It's used for sorting data by column or expression. If we use the  'ORDER BY' clause by default it will sort in ascending order or we can use ASC for ascending order and DESC for descending order. For sorting we can use column name, column position, expression, and alias.

  SELECT d.department_name,

         SUM (salary + salary * NVL (commission_pct, 0) / 100) Totalsalary

    FROM employees e, departments d

   WHERE e.department_id = d.department_id AND e.department_id IS NOT NULL

GROUP BY d.department_name

  HAVING SUM (salary + salary * NVL (commission_pct, 0) / 100) >= 20000;

ORDER BY Totalsalary DESC;

This example gives you a vast idea of SQL queries for beginning the SQL Course. Now to enrich your Query and for solving new work requirements you need to acquire knowledge of various SQL functions and some advanced-level theory. This can be considered as ornaments of the basic structure.

***Thanks for visiting my blog. My Youtube channel is Oracle School BD. You can visit here to see video tutorials.***