SQL Queries

SQL Queries
 
SQL Expressions:

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. SQL EXPRESSIONs are like formulas and they are written in query language. You can also use them to query the database for specific set of data.

Syntax:

Consider the basic syntax of the SELECT statement as follows:

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];

There are different types of SQL expressions, they are

SQL - Boolean Expressions: SQL Boolean Expressions fetch the data on the basis of matching single value.

 Syntax:

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

SQL - Numeric Expressions: This expression is used to perform any mathematical operation in any query.

Syntax:

SELECT numerical_expression as OPERATION_NAME
[FROM table_name
WHERE CONDITION];

Here numerical_expression is used for mathematical expression or any formula. There are several built-in functions like avg (), sum (), count () etc. to perform what is known as aggregate data calculations against a table or a specific table column.

SQL - Date Expressions: Date Expressions return current system date and time values

SQL> SELECT CURRENT_TIMESTAMP;

SQL> SELECT GETDATE ();

SQL Clauses:
A SQL clause is the word or phrase that begins each SQL command statement and the single most important component to any SQL command. Looking at the clause of any given SQL command usually tells the SQL programmer what that particular command intends to do to the database. Different clauses are as follows

LIKE Clause:

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:

The percent sign (%)

The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Syntax:

The basic syntax of % and _ is

SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

You can combine N number of conditions using AND or OR operators. Here XXXX could be any numeric or string value.

Examples:

WHERE SALARY LIKE '200%' - Finds any values that start with 200.

WHERE SALARY LIKE '%200%' - Finds any values that have 200 in any position.

WHERE SALARY LIKE '_00%' - Finds any values that have 00 in the second and third positions.   

TOP Clause:

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.

Syntax:

The basic syntax of TOP clause with SELECT statement would be as follows:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]

Example:

SQL> SELECT TOP 3 * FROM CUSTOMERS;
ORDER BY Clause:

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.

Syntax:

The basic syntax of ORDER BY clause is as follows:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. If you want to sort the records in a descending order, you can use the DESC keyword.

Examples:

Following is an example which would sort the result in ascending order by NAME and SALARY:

SQL> SELECT * FROM CUSTOMERS
     ORDER BY NAME, SALARY;

Following is an example which would sort the result in descending order by NAME:

SQL> SELECT * FROM CUSTOMERS
     ORDER BY NAME DESC;

GROUP BY & HAVING Clause:

In Sql Server, we have group by clause for grouping the records of the database table according to our need. We use having clause to filter data that we get from group by clause. Having clause operates only on group by clause means to use having clause we need to use group by clause first.

GROUP BY Clause: Group By clause is used for grouping the records of the database table(s).This clause creates a single row for each group and this process is called aggregation. To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause.

Syntax:

The basic syntax of GROUP BY clause. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2

FROM table_name
WHERE [conditions]
GROUP BY column1, column2
ORDER BY column1, column2

Example:

SELECT department, SUM (sales) as "Total sales"
FROM order_details
GROUP BY department;

HAVING Clause: Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

Syntax:

SELECT <column_list> FROM < table name >
WHERE <condition>
GROUP BY <columns>
[HAVING] <condition>;

Example:

If you want to select the department that has total salary paid for its employees more than 25000, the sql query would be like;

SELECT dept, SUM (salary)
FROM employee
GROUP BY dept
HAVING SUM (salary) > 25000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped based on the GROUP BY clause.
Finally, any conditions on the group functions in the HAVING clause is applied to the grouped rows before the final output is displayed.

0 comments:

Post a Comment