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.
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.
There are various data types in advanced SQL. Some of them are built-in data types and user-defined data types.
Dates, they contain 4 digits representing the year, month and date.
example: date ‘2009-7-17’
Time of day that is in the form of hours, minutes and seconds.
example: time ‘08:00:30’ time ‘08:00:30.75’
represents the date in addition with the time of day.
example: timestamp ‘2007-7-27 08:00:30.75’
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.
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.
Given below are some of the advanced functions defined in SQL:
☆ BIN(): It helps to convert a decimal number to a binary number.
☆ 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.
☆ 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.
☆ DATABASE(): Its output is the name of the default 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.
☆ 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.
☆ SYSTEM_USER(): Its output is the user name and hostname for the present MySQL user.
☆ USER(): Its output is the user name and hostname for the present MySQL user.
☆ VERSION(): Its output is the version of the MySQL database.