?? Day 34: Exploring Practical SQL Implementation - Part 6??

A. Common Functions in SQL:

Common functions are essential tools in SQL for manipulating and transforming data. They offer a range of functionalities, from simple tasks like finding string lengths or positions to more complex operations like converting data types or performing mathematical rounding. Understanding these functions is fundamental for efficient and precise data handling in SQL queries.

1. LENGTH(string): Returns the length of the provided string.
2. INSTR(string, substring): Returns the position of the substring within the specified string.
3. CAST(expression AS datatype): Converts an expression into the specified data type.
4. ADDDATE(input_date, days): Adds a number of days to a specified date.
5. NOW: Returns the current date, including time.
6. CEILING(input_val): Returns the smallest integer greater than the provided number.
7. FLOOR(input_val): Returns the largest integer less than the provided number.
8. ROUND(input_val, [round_to]): Rounds a number to a specified number of decimal places.
9. TRUNCATE(input_value, num_decimals): Truncates a number to a specified number of decimals.
10. REPLACE(whole_string, string_to_replace, replacement_string): Replaces one string inside the whole string with another string.
11. SUBSTRING(string, start_position): Returns part of a value based on a position and length.

Real-Life Example:

1. LENGTH(string): Return the length of the provided string.

Example Scenario:

• Table: customers

| customer_id | customer_name   |
|-------------|-----------------|
| 1           | Jignesh Kumar   |
| 2           | Mahesh Kumar    |        

• Query: The query calculates the length of each customer's name in the "customers" table, providing insights into the character count of customer names.

SELECT customer_name, LENGTH(customer_name) AS name_length
FROM customers;        

• Output: The "name_length" column shows the length of each customer's name.

| customer_name   | name_length |
|-----------------|-------------|
| Jignesh Kumar   | 13          |
| Mahesh Kumar    | 12          |        

2. INSTR(string, substring): Returns the position of the substring within the specified string.

Example Scenario:

• Table: products

| product_id | product_name       |
|------------|--------------------|
| 1          | Laptop Dell XPS    |
| 2          | Monitor ASUS 27"   |        

• Query: The query identifies the position of the substring 'ASUS' within each product name in the "products" table, aiding in locating specific information.

SELECT product_name, INSTR(product_name, 'ASUS') AS position
FROM products;        

• Output: The "position" column reveals the position of the substring 'ASUS' in each product name.

| product_name       | position |
|--------------------|----------|
| Laptop Dell XPS    | 0        |
| Monitor ASUS 27"   | 9        |        

3. CAST(expression AS datatype): Converts an expression into the specified data type.

Example Scenario:

• Table: orders

| order_id | order_date       |
|----------|------------------|
| 1        | '2024-01-15'     |
| 2        | '2024-02-20'     |        

• Query: The query converts the order date in the "orders" table from a string to a date data type, facilitating date-related operations.

SELECT order_id, CAST(order_date AS DATE) AS formatted_date
FROM orders;        

• Output: The "formatted_date" column displays the order date in the specified date format.

| order_id | formatted_date |
|----------|-----------------|
| 1        | 2022-01-15      |
| 2        | 2022-02-20      |        

4. ADDDATE(input_date, days): Adds a number of days to a specified date.

Example Scenario:

• Table: subscriptions

| subscription_id | start_date   |
|------------------|--------------|
| 1                | '2022-01-01' |
| 2                | '2022-02-15' |        

• Query: The query adds 30 days to the start date of each subscription in the "subscriptions" table, extending the subscription periods.

SELECT subscription_id, ADDDATE(start_date, 30) AS new_end_date
FROM subscriptions;        

• Output: The "new_end_date" column exhibits the adjusted end date after adding 30 days.

| subscription_id | new_end_date |
|------------------|--------------|
| 1                | 2022-01-31   |
| 2                | 2022-03-16   |        

5. NOW: Returns the current date, including time.

Example Scenario:

• Query: The query fetches the current date and time, providing a timestamp reflecting the moment the query is executed.

SELECT NOW() AS current_datetime;        

• Output: The "current_datetime" column displays the exact date and time.

| current_datetime       |
|-------------------------|
| 2024-02-14 14:30:45     |        

6. CEILING(input_val): Returns the smallest integer greater than the provided number.

Example Scenario:

• Query: The query rounds up the decimal number 15.25 to the nearest integer, ensuring the result is greater than the original number.

SELECT CEILING(15.25) AS rounded_up;        

• Output: The "rounded_up" column shows the smallest integer greater than 15.25.

| rounded_up |
|------------|
| 16         |        

7. FLOOR(input_val): Returns the largest integer less than the provided number.

Example Scenario:

• Query: The query rounds down the decimal number 15.75 to the nearest integer, ensuring the result is smaller than the original number.

SELECT FLOOR(15.75) AS rounded_down;        

• Output: The "rounded_down" column shows the largest integer less than 15.75.

| rounded_down |
|--------------|
| 15           |        

8. ROUND(input_val, [round_to]): Rounds a number to a specified number of decimal places.

Example Scenario:

• Query: The query rounds the decimal number 123.4567 to two decimal places, providing a more concise representation.

SELECT ROUND(123.4567, 2) AS rounded_value;        

• Output: The "rounded_value" column displays the number rounded to the specified decimal places.

| rounded_value |
|---------------|
| 123.46        |        

9. TRUNCATE(input_value, num_decimals): Truncates a number to a number of decimals.

Example Scenario:

• Query: The query truncates the decimal number 123.4567 to one decimal place, removing excess decimal values.

SELECT TRUNCATE(123.4567, 1) AS truncated_value;        

• Output: The "truncated_value" column displays the truncated number.

| truncated_value |
|------------------|
| 123.4            |        

10. REPLACE(whole_string, string_to_replace, replacement_string): Replaces one string inside the whole string with another string.

Example Scenario:

• Table: addresses

| address_id | full_address              |
|------------|---------------------------|
| 1          | 123 Main Street, City A   |
| 2          | 456 Oak Avenue, City B    |        

• Query: The query replaces the word 'Street' with 'St.' in each full address in the "addresses" table, providing abbreviated addresses.

SELECT address_id, REPLACE(full_address, 'Street', 'St.') AS modified_address
FROM addresses;        

• Output: The "modified_address" column shows addresses with the specified replacement.

| address_id | modified_address              |
|------------|-------------------------------|
| 1          | 123 Main St., City A          |
| 2          | 456 Oak Avenue, City B        |        

11. SUBSTRING(string, start_position): Returns part of a value based on a position and length.

Example Scenario:

• Table: products

| product_id | product_description               |
|------------|-----------------------------------|
| 1          | High-performance Laptop           |
| 2          | Large Screen 4K Gaming Monitor    |        

• Query: The query extracts the first 20 characters of each product description in the "products" table, offering concise product summaries.

SELECT product_id, SUBSTRING(product_description, 1, 20) AS short_description
FROM products;        

• Output: The "short_description" column displays the shortened product descriptions.

| product_id | short_description                |
|------------|---------------------------------|
| 1          | High-performance La              |
| 2          | Large Screen 4K Gami             |