↑ Return to SQL

Print this Page

Advance

SQL Advance Commands:

Permanent link to this article: http://www.techxpress.in/sql/advance/

Having

Another thing people may want to do is to limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which …

View page »

Group By

Now we return to the aggregate functions. Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total …

View page »

Order By

So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can …

View page »

Functions

Since we have started dealing with numbers, the next natural question to ask is if it is possible to do math on those numbers, such as summing them up or taking their average. The answer is yes! SQL has several arithmetic functions, and they are: – AVG – COUNT – MAX – MIN – SUM …

View page »

Alias

We next focus on the use of aliases. There are two types of aliases that are used most frequently: column alias and table alias. In short, column aliases exist to help organizing output. In the previous example, whenever we see total sales, it is listed as SUM(sales). While this is comprehensible, we can envision cases …

View page »

Join

Now we want to look at joins. To do joins correctly in SQL requires many of the elements we have introduced so far. Let’s assume that we have the following two tables, Table Store_Information store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999   Table Geography …

View page »

Outer Join

Previously, we had looked at left join, or inner join, where we select rows common to the participating tables to a join. What about the cases where we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER …

View page »

Concate

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this: MySQL: CONCAT() Oracle: CONCAT(), || SQL Server: + The syntax for CONCAT() is as follows: CONCAT(str1, str2, str3, …): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() …

View page »

Substr

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases: MySQL: SUBSTR(), SUBSTRING() Oracle: SUBSTR() SQL Server: SUBSTRING() The most frequent uses are as follows (we will use SUBSTR() here): SUBSTR(str,pos): Select all characters from <str> starting with position <pos>. Note …

View page »

Leave a Reply

Bookmark this page