Manipulate numbers with the ROUND, TRUNC, and MOD functions

Equal to operator(=)

The equal to operator compares the equality of two expressions:

expression1 = expression2
Code language: SQL (Structured Query Language) (sql)

It returns true if the value of the left expression is equal to the value of the right expression; otherwise, it returns false.

SELECT 

    employee_id, first_name, last_name

FROM

    employees

WHERE

    last_name = 'King';

Not equal to operator (<>)

The not equal to (<>) operator compares two non-null expressions and returns true if the value of the left expression is not equal to the right one; otherwise, it returns false.

expression1 <> expression2
Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns all employees whose department id is not 8.

SELECT 
    employee_id, first_name, last_name, department_id
FROM
    employees
WHERE
    department_id <> 8

Greater than operator (>)

The greater than operator (>) compares two non-null expressions and returns true if the left operand is greater than the right operand; otherwise, the result is false.

expression1 > expression2
Code language: SQL (Structured Query Language) (sql)

For example, to find the employees whose salary is greater than 10,000, you use the greater than operator in the WHERE clause as follows:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > 10000

Less than operator (<)

The less than operator compares two non-null expressions. The result is true if the left operand evaluates to a value that is lower than the value of the right operand; otherwise, the result is false.

The following shows the syntax of the less than operator:

expression1 < expression2
Code language: SQL (Structured Query Language) (sql)

For example, the statement below returns all employees whose salaries are less than 10,000:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary < 10000



Greater than or equal operator (>=)
The greater than or equal operator (>=) compares two non-null expressions. The result is true if the left expression evaluates to a value that is greater than the value of the right expression.
The following illustrates the syntax of the greater than or equal operator:
expression1 >= expression2
Code language: SQL (Structured Query Language) (sql)
For example, the following statement finds employees whose salaries are greater than or equal 9,000:
SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= 9000

Usage of Single-Row Functions to Customize Output 

Describe the differences between single row and multiple row functions

Question- What is difference between single row function and multi row function?

Single row function returns single result for every row whereas multi row function return single result based on groups of rows.

Example of single row- any non aggregate functions 

ex - SELECT UPPER('Allah-hus-samad');

SELECT REVERSE('abcd');

Multi row function takes single or multiple row as an input group them and gives a single record as output

Eg: sum() group multiple rows and returns single value

Manipulate strings with character function in the SELECT and WHERE clauses

SQL string functions are used primarily for string manipulation

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping.

SQL> SELECT UPPER('Allah-hus-samad');

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping.

SQL> SELECT UPPER('Allah-hus-samad');
+---------------------------------------------------------+
| UPPER('Allah-hus-samad')                                

REVERSE(str)

Returns the string str with the order of the characters reversed.

SQL> SELECT REVERSE('abcd');
+---------------------------------------------------------+
| REVERSE('abcd')                                         

LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LEN() returns 10, whereas CHAR_LENGTH() returns 5.

SELECT LEN('text');

Manipulate numbers with the ROUND, TRUNC, and MOD functions 

Number Functions - (ROUND, TRUNC, MOD)

1) ROUND

This command is used to round the decimal values. If the decimal value is greater than or equal to .5 then it is rounded to next value else rounded to the current value.

Example    SELECT  ROUND(157.96,0) 

select CAST(ROUND(10.0055,2,0) AS NUMERIC(10,2))

select CAST(ROUND(10.0055,2,0) AS int)

Perform arithmetic with date data 

Manipulate dates with the DATE functions 

Date Functions	Desc	Return Value Data Type	Note
DAY (date or datetime)	Returns the day of the week for a given date	Integer like 1 - 31
MONTH (date or datetime)	Returns the month of a given date	Integer like 1 - 12
YEAR (date or datetime)	Returns the year of a given date	Integer for year like 2021
DATEPART (date part, date or datetime)	Returns the date part specified in int format	Integer like 1 – 12 for month, 1 – 31 for day, or year like 2021
DATENAME (date part, date or datetime)	Returns the date part specified in character format	Character like April, May, ‘1’, ‘2’, ‘31’, ‘2020’, ‘2021’
EOMONTH (date [,months to add)	Returns the last do of the month with an optional parameter to add months (+ or -).	01/31/2021	Introduced in SQL Server 2012.
DATEADD (date part, units, date or datetime)	Return date math results	datetime
DATEDIFF (date part, start date, end date)	Give the difference between 2 dates in units specified by date part	Integer of date part units
DATEDIFF_BIG	Give the difference between 2 dates in units specified by date part	Big Integer of date part units
CONVERT (date type, value  [ , style ]	Used to convert date output to a specified mask	Typically, a character datatype is specified when converting dates.	*To convert a valid date char string to date no function is needed! Implicit convert!
FORMAT ( value, format [, culture ] )	Used to convert date output to a specified mask	Returns a date formatted string based on the mask specified.
CAST (value as data type)	Used to convert different data types to a date or datetime data type.	Returns data in the data type specified.
ISDATE (potential date string)	Use to validate a date string	Returns 1 if the string is a valid date or 0 if not a valid date.

Date Function DAY()

The date function DAY accepts a date, datetime, or valid date string and returns the Day part as an integer value.

Syntax: DAY(date)

--Example of DAY(): 
SELECT GETDATE(), DAY(GETDATE()) , DAY('2021-05-30 15:46:19.277');
GO

Results:

Date Function MONTH()

The date function MONTH accepts a date, datetime, or valid date string and returns the Month part as an integer value.

Syntax: MONTH(date)

--Example of MONTH(): 
SELECT GETDATE(), MONTH(GETDATE()) , MONTH('20210101'), MONTH('2021-05-30 15:46:19.277');
GO

Date Function YEAR()

The date function YEAR accepts a date, datetime, or valid date string and returns the Year part as an integer value.

Syntax: YEAR(date)

--Example of YEAR(): 
SELECT GETDATE(), YEAR(GETDATE()) , YEAR('20210101'), YEAR('2021-05-30 15:46:19.277');
GO

Results:

Date Function EOMONTH()

The date function EOMONTH accepts a date, datetime, or valid date string and returns the end of month date as a datetime. It can also take an optional offset that basically adds or subtracts months from the current passed date.

Syntax: EOMONTH(start_date [, month_to_add ])

--Example of EOMONTH(): Shows different date formats being passed in.
SELECT EOMONTH(GETDATE()), EOMONTH('20210101'), EOMONTH('May 1, 2021');
 
--Example of EOMONTH(): Shows the use of the offset optional parameter with the GETDATE function which is the current date

	SELECT EOMONTH(GETDATE()) as 'End Of Current Month',
   EOMONTH(GETDATE(),-1) as 'End Of Last Month',
   EOMONTH(GETDATE(),6) as 'End Of Month after +6 months';

The date function DATEADD accepts a date part, a number to add, date, datetime, or valid date string and returns datetime result based on the units add (can be negative).

Syntax: DATEADD(date part, units, date or datetime)

Date Parts: can use the name or listed abbreviations:

• year, yy, yyyy
• quarter, qq, q
• month, mm, m
• dayofyear, dy, y*
• day, dd, d*
• weekday, dw, w*
• week, wk, ww
• hour, hh
• minute, mi, n
• second, ss, s
• millisecond, ms
• microsecond, mcs
• nanosecond, ns

*Note: dayofyear, day, and weekday return the same value.

--Example of DATEADD():
SELECT DATEADD(DAY,1,'2021-01-01') as 'Add 1 Day',
   DATEADD(WEEK,1,'2021-01-01') as 'Add 1 Week',
   DATEADD(MONTH,1,'2021-01-01') as 'Add 1 Month',
   DATEADD(YEAR,1,'2021-01-01') as 'Add 1 Year';
GO

Results:

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/25', '2011/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: