5 SQL things people don't know about!

Introduction

SQL, or Structured Query Language, is the go-to tool for managing and manipulating relational databases. Whether you're fetching data, modifying tables, or performing complex analysis, SQL provides the framework to interact with your data effectively. However, even seasoned SQL users can sometimes overlook certain concepts that can greatly enhance their querying capabilities. In this article, we'll explore five SQL concepts that are often overlooked but can prove to be incredibly useful in SQL interviews and practical data manipulation tasks.

WHERE vs HAVING

The WHERE clause is commonly used to filter rows based on specific conditions, such as age greater than 30 or product name equal 'XYZ'. On the other hand, the HAVING clause is used to filter aggregated data, typically in conjunction with GROUP BY. While the distinction may seem subtle, understanding when to use WHERE versus HAVING can significantly impact the outcome of your queries.

-- Using WHERE clause to filter rows

SELECT *

FROM customers

WHERE age > 30;

-- Using HAVING clause to filter aggregated data

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department

HAVING AVG(salary) > 50000;        

Mapping Values with CASE WHEN

The CASE WHEN statement in SQL allows for conditional logic within queries, making it a powerful tool for data transformation. With CASE WHEN you can map specific column values to different output values based on defined conditions. This flexibility is invaluable when dealing with messy or inconsistent data that requires standardization.

SELECT

    customer_name,

    CASE 

        WHEN age < 18 THEN 'Minor'

        WHEN age BETWEEN 18 AND 65 THEN 'Adult'

        ELSE 'Senior'

    END AS customer_category

FROM customers;        

Combining Strings with STRING_AGG

Concatenating strings in SQL is a common task, but the STRING_AGG function takes it a step further by allowing you to aggregate strings across multiple rows into a single delimited string. This can be particularly useful when dealing with denormalized data or when you need to concatenate values from related rows into a single result.

SELECT

    order_id,

    STRING_AGG(product_name, ', ') AS products_ordered

FROM orders

GROUP BY order_id;        

Joining Stuff Using CONCAT

While the CONCAT function in SQL is well-known for concatenating strings, it's often overlooked for joining non-string values. However, CONCAT can be used to concatenate any data type, including integers and dates. This can simplify query logic and make your code more concise and readable.

SELECT CONCAT(first_name, ' ', last_name) AS full_name

FROM employees;        

Smallint vs Int vs Bigint

Understanding the differences between smallint, int, and bigint data types is essential for efficient database design. While all three are used to store integer values, they differ in terms of storage size and range. Choosing the appropriate data type based on your data requirements can optimize storage efficiency and prevent data truncation or overflow issues.

CREATE TABLE employee (

    employee_id smallint,

    employee_name varchar(50),

    salary bigint

);        

Conclusion:

By familiarizing yourself with these often-overlooked SQL concepts and their practical applications through code examples, you can expand your SQL toolkit and become a more proficient SQL user. Whether you're optimizing queries for performance, cleaning and transforming data, or designing database schemas, mastering these concepts will empower you to tackle a wide range of data challenges with confidence. So, the next time you're writing SQL queries, remember to consider these five overlooked concepts—they just might save you time and headaches in the long run.

Thank you...

If you're passionate about data and eager to explore further, I encourage you to reach out. Whether it's for a casual discussion, collaboration on a project, or seeking advice on data-related challenges, I'm here to help.

For more data role skill discussion. Let's make a connection! I'm open to working as a Data Analyst :)

Email: [email protected]

Phone: +923241839800

Linkedin: https://www.dhirubhai.net/in/izhanalisyed/

My Portfolio: https://www.datascienceportfol.io/SyedIzhanAli

Let's chat for more discussion about being a data nerd!