DBMS-LAB-IV-SEM

Exercise – 4

Queries using conversion functions

Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc,round, to_char, to_date).

Below are SQL queries demonstrating the use of conversion functions (TO_CHAR, TO_NUMBER, TO_DATE), string functions (concatenation, LPAD, RPAD, LTRIM, RTRIM, LOWER, UPPER, INITCAP, LENGTH, SUBSTR, INSTR), and date functions (SYSDATE, NEXT_DAY, ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, LEAST, GREATEST, TRUNC, ROUND, TO_CHAR, TO_DATE):

Conversion Functions

1. Convert a Numeric Value to a String:

SELECT TO_CHAR(12345) AS converted_value FROM dual;

1. Convert a String to a Numeric Value:

SELECT TO_NUMBER('12345') AS converted_number FROM dual;

1. Convert a String to a Date:

SELECT TO_DATE('2024-02-28', 'YYYY-MM-DD') AS converted_date FROM dual;

String Functions

1. Concatenation of Strings:

SELECT 'Hello' || ' ' || 'World' AS concatenated_string FROM dual;

1. Padding Strings with Leading Zeros:

SELECT LPAD('123', 5, '0') AS padded_string FROM dual;

1. Trimming Spaces from the Right Side of a String:

SELECT RTRIM('  Hello  ') AS trimmed_string FROM dual;

1. Converting a String to Lowercase:

SELECT LOWER('HELLO') AS lowercase_string FROM dual;

1. Extracting Substring:

SELECT SUBSTR('Hello World', 7, 5) AS extracted_substring FROM dual;

1. Finding Position of Substring:

SELECT INSTR('Hello World', 'World') AS substring_position FROM dual;

Date Functions

1. Current Date and Time:

SELECT SYSDATE FROM dual;

1. Next Day of a Given Date:

SELECT NEXT_DAY(TO_DATE('2024-02-28', 'YYYY-MM-DD'), 'SUNDAY') AS next_sunday FROM dual;

1. Add Months to a Given Date:

SELECT ADD_MONTHS(TO_DATE('2024-02-28', 'YYYY-MM-DD'), 3) AS future_date FROM dual;

1. Last Day of a Given Month:

SELECT LAST_DAY(TO_DATE('2024-02-28', 'YYYY-MM-DD')) AS last_day_of_month FROM dual;

1. Difference in Months Between Two Dates:

SELECT MONTHS_BETWEEN(TO_DATE('2024-02-28', 'YYYY-MM-DD'), TO_DATE('2024-01-01', 'YYYY-MM-DD')) AS month_difference FROM dual;

1. Truncate Date to the Specified Unit:

SELECT TRUNC(SYSDATE, 'MONTH') AS truncated_date FROM dual;

1. Round Date to the Specified Unit:

SELECT ROUND(SYSDATE, 'MONTH') AS rounded_date FROM dual;