Writing SQL Query

This material guides how to make the right query inorder to retrieve particular records. It has been observed that often students are not sure that what can be applied to retrieve the required records

This article will use the following schema

order(orderid, orderdate, customerid)
customer(cutomerid, customername)

1- Whenever an objective is given first determine the resources/tables that are required. This can be done either by looking at the columns that are required to be displayed or the columns on the basis of which filteration of rows is done.

for example given the following objective
Find the dates of all orders

The above objective asks for the date of all orders which clearly indicates that orderdate column is required which is present in the order table. Thus the query will be

Select orderDate from orders

However if the objective is to display the orderDates as well as customerNames then the query will be modified. Here the objective indicates that orderDate is required which is in the order table and customerName is required which is in the customer table. Thus the query will need to join the contents of two tables which is always on the basis of common column. The query in this case will be

Select customerName, orderDate from order, customer where order.orderno=customer.custno

2- Secondly determine whether all records need to be displayed or some filteration needs to be applied. For example, if the objective is to display the orderdate and name of customer who have placed order on '10/10/2010' then we need to appy filteration on the basis of orderdate. This is in addition to join-condition which is there due to the reason that orderDate and customerName are in different table. The query in this case will be

Select customerName, orderDate from order, customer where order.orderno=customer.custno and orderDate='10/10/2010'

3- Thirdly, see if there is a need of aggregates or summarized columns. If the query asks for a single aggregate then use appropriate aggregate function, however, if it requires multiple aggregates based upon values of particular column then apply group by. For example if the objective is to determine total number of orders then the query will be

Select count(*) from orders

Do note that the objective was to calculate a single aggregate for the entire table therefore group by has not been applied. In addition, since the resources required for the query were present in one table therefore no join was applied and finally there was no need of filteration as a single total for entire table was required. However if the objective is modified such that only total number of orders placed on '10/10/2010' is required then a where clause will be appended in the above query as filteration is now needed

Select count(*) from orders where orderDate='10/10/2010'

As mentioned that in cases where a number of summaries are needed to be displayed then a group by clause is appended. For example if we need to display summaries on the basis of customer, i.e. total number of orders placed by each customer then group by clause containing name of customer related column will be appended

Select count(*) from orders group by customerId

4- The final step is to see whether all summaries (outputs of aggregates) need to be displayed or only particular ones. For example if the above objective is modified such that only those customerids are required to be displayed that have more than 5 orders, then we need to apply the having clause.

Select count(*) from orders group by customerId having count(*)> 5

To summarize, having is applied when output of group by needs to be filtered

Please note that the list is not exhaustive and some more points may be added in future. However for most of the scenarios the above point will be a very great help