Sql Training

July 13, 2022

Sql Training - 7

Date Function DATEDIFF

The date function DATEDIFF accepts a date part, start date and end date as date datetime, or valid date string and returns the difference between the dates in units bast on the date part specified.

Syntax: DATEDIFF (date part, start date, end date)

SELECT DATEDIFF(year, '2017/08/15', '2017/08/25') AS DateDiff;

SELECT DATEDIFF(dd, '2017/08/15', '2017/08/25') AS DateDiff;

SELECT DATEDIFF(mm, '2017/08/15', '2017/08/25') AS DateDiff;

Date Function CONVERT

The Convert function is used to convert data to different data types and can format the output string. In this example we used CONVERT to format the output datetime value as a character string.

Syntax: CONVERT( data_type [ ( length ) ] , expression [ , style ] )

--Example of CONVERT():
SELECT CONVERT(CHAR(19), GETDATE(), 100) as 'Mon dd YYYY hh:mmAM';
 
SELECT CONVERT(CHAR(8), GETDATE(), 112) as 'YYYYMMDD';
 
SELECT CONVERT(CHAR(20), GETDATE(), 22) as 'mm/dd/yy hh:mi:ss AM';
 
SELECT CONVERT(CHAR(10),GETDATE(),120) as 'MyDate_w_Dash',
   CONVERT(CHAR(10),GETDATE(),111) as 'MyDateTime_w_Slash',
   CONVERT(CHAR(10),GETDATE(),102) as 'MyDateTime_w_Dot';
GO

Results:

Invoke Conversion Functions and Conditional Expressions

Describe implicit and explicit data type conversion

SQL Server `CAST()` function

Let’s see the following query:

SELECT 1 + '1' AS result;
Code language: PHP (php)

It returns 2 as a number:

result
-----------
2

(1 row affected)

In this statement, SQL Server implicitly converts the character string '1' to the number 1.

When you use two values with different data types, SQL Server will try to convert the lower data type to the higher one before it can process the calculation. This is known as an implicit conversion in SQL Server.

In contrast to implicit conversions, we have explicit conversions where you call the CAST() function to explicitly convert a value of one type to another:

SELECT 1 + CAST(1 AS INT) result;
Code language: PHP (php)

The syntax of the CAST() function is as follows:

CAST ( expression AS target_type [ ( length ) ] )  
Code language: CSS (css)

In this syntax:

expression can be a literal value or a valid expression of any type that will be converted.
target_type is the target data type to which you want to convert the expression. It includes INT, BIT, SQL_VARIANT, etc. Note that it cannot be an alias data type.
length is an optional integer that specifies the length of the target type. The length defaults to 30.

The CAST() function returns the expression converted to the target data type.

SQL Server `CAST()` function examples

Let’s take some examples of using the CAST() function.

A) Using the `CAST()` function to convert a decimal to an integer example

This example uses the CAST() function to convert the decimal number 5.95 to an integer:

SELECT CAST(5.95 AS INT) result;
Code language: CSS (css)

Here is the output:

result
-----------
5

(1 row affected)

B) Using the `CAST()` function to convert a decimal to another decimal with different length

The following example uses the CAST() function to convert the decimal number 5.95 to another decimal number with the zero scale:

SELECT CAST(5.95 AS DEC(3,0)) result;
Code language: CSS (css)

The output is as follows:

result
-------
6

When you convert a value of the data types in different places, SQL Server will return a truncated result or a rounded value based on the following rules:

C) Using the `CAST()` function to convert a string to a datetime value example

This example uses the CAST() function to convert the string '2019-03-14' to a datetime:

SELECT 
    CAST('2019-03-14' AS DATETIME) result;
Code language: PHP (php)

The output is:

result
-----------------------
2019-03-14 00:00:00.000

(1 row affected)

Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions Nest multiple functions

Apply the NVL, NULLIF, and COALESCE functions to data

The COALESCE() function returns the first non-null value in a list.

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

Introduction :
The SQL Server COALESCE() function is useful to handle NULL values. The NULL values are replaced with the user-given value during the expression value evaluation process. The SQL Server Coalesce function evaluates the expression in a definite order and always results first not null value from the defined expression list.

SELECT COALESCE (NULL, 'X', 'Y') 
AS RESULT ;

Use conditional IF THEN ELSE logic in a SELECT statement

IF… Else statement in SQL Server

In MS SQL, IF…ELSE is a type of Conditional statement.

Any T-SQL statement can be executed conditionally using IF… ELSE.

Below figure explains IF ELSE in SQL server:

If the condition evaluates to True, then T-SQL statements followed by IF condition in SQL server will be executed.
If the condition evaluates to False, then T-SQL statements followed by ELSE keyword will be executed.
Once, either IF T-SQL statements or ELSE T-SQL statement is executed, then other unconditional T-SQL statements continues execution.

IF… Else Syntax and rules in SQL

Syntax:

IF <Condition>
     {Statement | Block_of_statement}   
[ ELSE   
     {Statement | Block_of_statement}]

Rules:

The condition should be Boolean Expression, i.e., condition results in Boolean value when evaluated.
IF ELSE statement in SQL can conditionally handle a single T-SQL statement or block of T-SQL statements.
Block of statement should start with keyword BEGIN and close with keyword END.
Using BEGIN and END helps SQL server to identify statement block that needs to be executed and separate it from rest of the T-SQL statements which are not part of IF…ELSE T-SQL block.
ELSE is optional.

IF…ELSE with the only numeric value in Boolean Expression.

Condition: TRUE

IF (1=1)
PRINT 'IF STATEMENT: CONDITION IS TRUE'
ELSE
PRINT 'ELSE STATEMENT: CONDITION IS FALSE'

Aggregate Data Using the Group Functions

use the aggregation functions in SELECT statements to produce meaningful reports Divide the data into groups by using the GROUP BY clause

Introduction to SQL GROUP BY clause

The GROUP BY is an optional clause of the SELECT statement. The GROUP BY clause allows you to group rows based on values of one or more columns. It returns one row for each group.

The following shows the basic syntax of the GROUP BY clause:

SELECT
	column1,
	column2,
	aggregate_function(column3)
FROM
	table_name
GROUP BY
	column1,
	column2;
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates shows how the GROUP BY clause works:

The table on the left side has two columns id and fruit. When you apply the GROUP BY clause to the fruit column, it returns the result set that includes unique values from the fruit column:

SELECT 
    fruit
FROM 
    sample_table
GROUP BY 
    fruit;
SQL GROUP BY examples
We will use the employees and departments tables in the sample database to demonstrate how the GROUP BY clause works.
The following example uses the GROUP BY clause to group the values in department_id column of the employees table:
SELECT 
    department_id
FROM 
    employees
GROUP BY 
    department_id;

In this example:

First, the SELECT clause returns all values from the department_id column of employees table.
Second, the GROUP BY clause groups all values into groups.

The department_id column of the employees table has 40 rows, including duplicate department_id values. However, the GROUP BY groups these values into groups.

Without an aggregate function, the GROUP BY behaves like the DISTINCT keyword:

SELECT 
   DISTINCT department_id
FROM 
   employees
ORDER BY 
   department_id;
Code language: SQL (Structured Query Language) (sql)

Try It

The GROUP BY clause will be more useful when you use it with an aggregate function.

For example, the following statement uses the GROUP BY clause with the COUNT function to count the number of employees by department:

SELECT
	department_id,
	COUNT(employee_id) headcount
FROM
	employees
GROUP BY
	department_id;
Code language: SQL (Structured Query Language) (sql)

Try It

Output:

+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         1 |
|             2 |         2 |
|             3 |         6 |
|             4 |         1 |
|             5 |         7 |
|             6 |         5 |
|             7 |         1 |
|             8 |         6 |
|             9 |         3 |
|            10 |         6 |
|            11 |         2 |
+---------------+-----------+
11 rows in set (0.00 sec)
Code language: JavaScript (javascript)

How it works.

First, the GROUP BY clause groups the rows in the employees table by department id.
Second, the COUNT(employee_id) returns the number of employee id values in each group.

Code language: JavaScript (javascript)

SQL GROUP BY with MIN, MAX, and AVG example

The following query returns the minimum, maximum, and average salary of employees in each department.

SELECT 
    department_name,
    MIN(salary) min_salary,
    MAX(salary) max_salary,
    ROUND(AVG(salary), 2) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;
Code language: SQL (Structured Query Language) (sql)

Try It

Output:

+------------------+------------+------------+----------------+
| department_name  | min_salary | max_salary | average_salary |
+------------------+------------+------------+----------------+
| Accounting       |    8300.00 |   12000.00 |       10150.00 |
| Administration   |    4400.00 |    4400.00 |        4400.00 |
| Executive        |   17000.00 |   24000.00 |       19333.33 |
| Finance          |    6900.00 |   12000.00 |        8600.00 |
| Human Resources  |    6500.00 |    6500.00 |        6500.00 |
| IT               |    4200.00 |    9000.00 |        5760.00 |
| Marketing        |    6000.00 |   13000.00 |        9500.00 |
| Public Relations |   10000.00 |   10000.00 |       10000.00 |
| Purchasing       |    2500.00 |   11000.00 |        4150.00 |
| Sales            |    6200.00 |   14000.00 |        9616.67 |
| Shipping         |    2700.00 |    8200.00 |        5885.71 |
+------------------+------------+------------+----------------+
11 rows in set (0.01 sec)
Code language: JavaScript (javascript)

SQL GROUP BY with SUM function example

To get the total salary per department, you apply the SUM function to the salary column and group employees by the department_id column as follows:

SELECT 
    department_name,
    SUM(salary) total_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;
Code language: SQL (Structured Query Language) (sql)

Try It

Output:

+------------------+--------------+
| department_name  | total_salary |
+------------------+--------------+
| Accounting       |     20300.00 |
| Administration   |      4400.00 |
| Executive        |     58000.00 |
| Finance          |     51600.00 |
| Human Resources  |      6500.00 |
| IT               |     28800.00 |
| Marketing        |     19000.00 |
| Public Relations |     10000.00 |
| Purchasing       |     24900.00 |
| Sales            |     57700.00 |
| Shipping         |     41200.00 |
+------------------+--------------+
11 rows in set (0.01 sec)

Introduction to SQL HAVING clause
In the previous tutorial, you have learned how to use the GROUP BY clause to summarize rows into groups and apply the aggregate function such as MIN, MAX, SUM, COUNT, AVG to each group.
To specify a condition for groups, you use the HAVING clause.
The HAVING clause is often used with the GROUP BY clause in the SELECT statement. If you use a HAVING clause without a GROUP BY clause, the HAVING clause behaves like the WHERE clause.



HAVING vs. WHERE
The WHERE clause applies the condition to individual rows before the rows are summarized into groups by the GROUP BY clause. However, the HAVING clause applies the condition to the groups after the rows are grouped into groups.
Therefore, it is important to note that the HAVING clause is applied after whereas the WHERE clause is applied before the GROUP BY clause.
SQL HAVING clause examples
We will take the employees and departments tables in the sample database for the demonstration.
To get the managers and their direct reports, you use the GROUP BY clause to group employees by the managers and use the COUNT function to count the direct reports.
The following query illustrates the idea:
SELECT 
    manager_id,
    first_name,
    last_name,
    COUNT(employee_id) direct_reports
FROM
    employees
WHERE
    manager_id IS NOT NULL
GROUP BY manager_id;
Code language: SQL (Structured Query Language) (sql)
Try It
To find the managers who have at least five direct reports, you add a HAVING clause to the query above as the following:
SELECT 
    manager_id,
    first_name,
    last_name,
    COUNT(employee_id) direct_reports
FROM
    employees
WHERE
    manager_id IS NOT NULL
GROUP BY manager_id
HAVING direct_reports >= 5;
Code language: SQL (Structured Query Language) (sql)
Try It
SQL HAVING with SUM function example
The following statement calculates the sum of salary that the company pays for each department and selects only the departments with the sum of salary between 20000 and 30000.
SELECT 
    department_id, SUM(salary)
FROM
    employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 30000
ORDER BY SUM(salary);
Code language: SQL (Structured Query Language) (sql)
Try It

Exclude groups of date by using the HAVING clause

Display Data From Multiple Tables Using Joins

Write SELECT statements to access data from more than one table View data that generally does not meet a join condition by using outer joins Join a table by using a self-join

Use Sub-Queries to Solve Queries

Describe the types of problem that sub-queries can solve

Define sub-queries

List the types of sub-queries

Write single-row and multiple-row sub-queries

The SET Operators

Describe the SET operators

Use a SET operator to combine multiple queries into a single query Control the order of rows returned

Data Manipulation Statements

Describe each DML statement

Insert rows into a table

Change rows in a table by the UPDATE statement

Delete rows from a table with the DELETE statement

Save and discard changes with the COMMIT and ROLLBACK statements Explain read consistency

Use of DDL Statements to Create and Manage Tables

Categorize the main database objects

Review the table structure

List the data types available for columns

Create a simple table

Decipher how constraints can be created at table creation

Describe how schema objects work

Other Schema Objects

Create a simple and complex view

Retrieve data from views

Create, maintain, and use sequences

Create and maintain indexes

Create private and public synonyms

Control User Access

Differentiate system privileges from object privileges Create Users

Grant System Privileges

Create and Grant Privileges to a Role

Change Your Password

Grant Object Privileges

How to pass on privileges?

Revoke Object Privileges

Management of Schema Objects

Add, Modify and Drop a Column

Add, Drop and Defer a Constraint

How to enable and Disable a Constraint?

Create and Remove Indexes

Create a Function-Based Index

Perform Flashback Operations

Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP Query External Tables

Manage Objects with Data Dictionary Views

Explain the data dictionary

Use the Dictionary Views

USER_OBJECTS and ALL_OBJECTS Views

Table and Column Information

Query the dictionary views for constraint information

Query the dictionary views for view, sequence, index, and synonym information Add a comment to a table

Query the dictionary views for comment information

Manipulate Large Data Sets

Use Subqueries to Manipulate Data

Retrieve Data Using a Subquery as Source

Insert Using a Subquery as a Target

Usage of the WITH CHECK OPTION Keyword on DML Statements List the types of Multitable INSERT Statements

Use Multitable INSERT Statements

Merge rows in a table

Track Changes in Data over a period of time

Data Management in Different Time Zones

Time Zones

CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP þÿCompare Date and Time in a Session s Time Zone DBTIMEZONE and SESSIONTIMEZONE

Difference between DATE and TIMESTAMP

INTERVAL Data Types

Use EXTRACT, TZ_OFFSET, and FROM_TZ

Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

Multiple-Column Subqueries

Pairwise and Nonpairwise Comparison

Scalar Subquery Expressions

Solve problems with Correlated Subqueries

Update and Delete Rows Using Correlated Subqueries The EXISTS and NOT EXISTS operators

Invoke the WITH clause

The Recursive WITH clause

Regular Expression Support

Use the Regular Expressions Functions and Conditions in SQL Use Meta Characters with Regular Expressions

Perform a Basic Search using the REGEXP_LIKE function Find patterns using the REGEXP_INSTR function Extract Substrings using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE function Usage of Sub-Expressions with Regular Expression Support

Implement the REGEXP_COUNT function

Search This Blog

Tech Conscious

Sql Training - 7

Date Function DATEDIFF

Date Function CONVERT

SQL Server `CAST()` function

SQL Server `CAST()` function examples

A) Using the `CAST()` function to convert a decimal to an integer example

B) Using the `CAST()` function to convert a decimal to another decimal with different length

C) Using the `CAST()` function to convert a string to a datetime value example

IF… Else statement in SQL Server

IF… Else Syntax and rules in SQL

IF…ELSE with the only numeric value in Boolean Expression.

Introduction to SQL GROUP BY clause

SQL GROUP BY examples

SQL GROUP BY with MIN, MAX, and AVG example

SQL GROUP BY with SUM function example

Introduction to SQL HAVING clause

HAVING vs. WHERE

SQL HAVING clause examples

SQL HAVING with SUM function example

Comments

Post a Comment

Popular Posts

How to use PostgreSql With .Net

SQL Interview Questions

Sql Training - 7

Date Function DATEDIFF

Date Function CONVERT

SQL Server CAST() function

SQL Server CAST() function examples

A) Using the CAST() function to convert a decimal to an integer example

B) Using the CAST() function to convert a decimal to another decimal with different length

C) Using the CAST() function to convert a string to a datetime value example

IF… Else statement in SQL Server

IF… Else Syntax and rules in SQL

IF…ELSE with the only numeric value in Boolean Expression.

Introduction to SQL GROUP BY clause

SQL GROUP BY examples

SQL GROUP BY with MIN, MAX, and AVG example

SQL GROUP BY with SUM function example

Introduction to SQL HAVING clause

HAVING vs. WHERE

SQL HAVING clause examples

SQL HAVING with SUM function example

Comments

Post a Comment

Popular Posts

How to use PostgreSql With .Net

SQL Interview Questions

SQL Server `CAST()` function

SQL Server `CAST()` function examples

A) Using the `CAST()` function to convert a decimal to an integer example

B) Using the `CAST()` function to convert a decimal to another decimal with different length

C) Using the `CAST()` function to convert a string to a datetime value example