Basic Structure of SQL Queries

SQL queries are the questions or requests imposed on the set of data to retrieve the desired information. The language we use to build these queries is structured query language i.e. SQL. In this context, we will be discussing the structure of SQL queries and try to understand them with some examples.

What is the Basic Structure of SQL Queries?

The fundamental structure of SQL queries includes three clauses that are select, from, and where clause. What we want in the final result relation is specified in the select clause. Which relations we need to access to get the result is specified in from clause. How the relation must be operated to get the result is specified in the where clause.

select A₁, A₂, . . . , A_n

from r₁, r₂, . . . , r_m

where P;

• In the select clause, you have to specify the attributes that you want to see in the result relation
• In the from clause, you have to specify the list of relations that has to be accessed for evaluating the query.
• In the where clause involves a predicate that includes attributes of the relations that we have listed in the from clause.

Though the SQL query has a sequence select, from, and where. To understand how the query will operate? You must consider the query in the order, from, where and then focus on select.

So with the help of these three clauses, we can retrieve the information we want out of the huge set of data. Let us begin with the structure of queries imposed on the single relation.

Queries on Single Relation

Consider that we have a relation ‘instructor’ with the attributes instr_id, name, dept_name, and salary. Now we want the names of all the instructors along with their corresponding department names.

The SQL query we would structure to get a result relation with instructor’s names along with their department name.

select name,

from instructor;

Observe that here we have not included where clause in the query above as we want the name of all the instructors with their department name. So there is no need of imposing any condition.

Now, if we have asked only for the dept_name in the above query by default it would have listed names of all the departments retaining the duplicates. To eliminate the duplicates you can make use of the distinct keyword.

select distinct dept_name

from instructor;

Further, you can even include the arithmetic expression in the select clause using operators such as +, -, *, and /.  In case, you want the result relation to display instructor name along with their salary which reduced by 10%. Then the SQL query you will impose on the data set is:

select instr_name, salary*0.9

from instructor;

To get specific tuples in the result relation we can use the where clause to specify the particular condition. Like if we want the result relation to display names of instructors that have salaries greater than 50000.

In where clause we can make use of logical connectives and, or & not. Along with these, we can include expressions where we can use comparison operators to compare operands in the expression. This was a query imposed on a single relation now let’s move ahead and discuss queries requested on multiple relations.

Queries on Multiple Relation

The SQL queries often need to access multiple relations from the data set in order to get the required result. Let us take an example we have two relations instructor and department.

Now, if you want to retrieve the names of all the instructors along with their department names and the corresponding department building. We will get the instructor’s name and department name in the instructor relation but building name in the department relation. So the query would be:

select name, instructor.dept_name, building

from instructor, department

where instructor.dept_name= department.dept_name;

Here department name of each tuple of instructor relation will be matched with the department name of each tuple of department relation. Observe that we have used relation name as a prefix to the attribute name in where clause, as both the attributes to be compared in where clause has the same name. The result of the query above is:

The from the clause in the queries with multiple relations act as a Cartesian product of the relations present the from clause.

Consider the relation instructor and teaches and the Cartesian product between the two can be expressed as:

(instructor.ID, instructor.name, instructor.dept_name, instructor.salary, teaches.ID, teaches.course id, teaches.sec id, teaches.semester, teaches.year)

The prefix should not be added to the attributes that are only in one of the relations. So this would be like:

(instructor.ID, name, dept_name, salary

teaches.ID, course id, sec id, semester, year)

The Cartesian product combines each tuple of instructor relation to every tuple of teaches relation. This Cartesian product results in extremely large relations which are hardly of any use. So, it is the where clause that restricts the unnecessary combinations, and let’s retain the meaningful combination that will help in retrieving the desired result.

Natural Join

Like, Cartesian product the natural join operation also combines the tuples from two relations to provide the result. The natural join operation combines only those tuples that have the same value for the attribute that appears in both the relation on which natural join is applied.

The natural join of instructor and teaches relation is:

The natural join query can be written as:

select name, course id

from instructor natural join teaches;

This is how you can structure a SQL query to retrieve your desired result. There is a lot to explore further in SQL queries. To get the correct result you must be able to structure the query correctly.