Advanced SQL

In this course, we will study What is an Advanced SQL, additional data types (BUILT-IN DT and USER DEFINED DT) and functionalities with an example.

What is an advanced SQL?

An advanced SQL is an addition in traditional SQL with some built-in data types and user-defined data types. The advanced SQL helps in increasing the performance and generates much better estimates which makes the SQL more efficient.

Advanced SQL

What are the additional data types in advanced SQL?

There are various data types in advanced SQL. Some of them are built-in data types and user-defined data types.

BUILT-IN DATA TYPES

Date

Dates, they contain 4 digits representing the year, month and date.
example: date ‘2009-7-17’

Time

Time of day that is in the form of hours, minutes and seconds.
example: time ‘08:00:30’ time ‘08:00:30.75’

Timestamp

represents the date in addition with the time of day.
example: timestamp ‘2007-7-27 08:00:30.75’

Interval

represents a specified period of time.
example: interval ‘2’ day
On subtracting the date/time/timestamp value from another we can have an interval value.

USER DEFINED DATA TYPES

create type construct in SQL helps to create a user-defined type.
create type Rupees as numeric (11,3) final

create domain construct in SQL-92 leads to the creation of user-defined domain types.
create domain student_name char(25) not null

Types and domains are the same. Domains might have constraints, like not null, specified on them.

What are the functions in advanced SQL?

Given below are some of the advanced functions defined in SQL:

BIN(): It helps to convert a decimal number to a binary number.
SELECT BIN(15);

BINARY(): It converts a value into a binary string.
SELECT BINARY "Fast food";

COALESCE(): Its output is the first non-null expression in a list.
SELECT COALESCE (NULL,NULL, 'Food', NULL,'Junk');

CONNECTION_ID(): Its output is the unique connection ID for the present connection.
SELECT CONNECTION_ID();

CURRENT_USER(): Its output is the user name and hostname for the MySQL account which is used by the server to authenticate the present client.
SELECT CURRENT_USER();

DATABASE(): Its output is the name of the default database.
SELECT DATABASE();

IF(): Its output is one value if the given condition is TRUE, or another value if the given condition is FALSE.
SELECT IF(900<500, "YES", "NO");

LAST_INSERT_ID(): Its output is the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement.
SELECT LAST_INSERT_ID();

NULLIF(): Its output is the first expression if the given two expressions are not equal. In case if the expressions are equal, NULLIF returns a null value of the type of the first expression.
SELECT NULLIF(125, 125);

SESSION_USER(): Its output is the user name and hostname for the present MySQL user.
SELECT SESSION_USER();

SYSTEM_USER(): Its output is the user name and hostname for the present MySQL user.
SELECT SYSTEM_USER();

USER(): Its output is the user name and hostname for the present MySQL user.
SELECT USER();

VERSION(): Its output is the version of the MySQL database.
SELECT VERSION();