Top 10 SQL Server Functions Programmer should learn

 Hey there! SQL Server functions are like magic spells in the enchanted forest of databases. They can transform your data and make it dance to your tune. In this article, I'm going to share my top 10 SQL Server functions that every programmer should have in their toolkit.

Top 10 SQL Server Functions Programmer should learn

If databases were a music festival, SQL Server functions would be the headlining acts. They're the riffs and rhythms that bring your data to life, and in this article, I'm here to share my top 10 SQL Server functions that every programmer should have in their backstage pass.

1. COUNT()

Ever wondered how many rows are in a table? COUNT() is your headcount tool. With SELECT COUNT(*) FROM your_table;, you can find out how many rows are in "your_table." It's like counting your party guests.

SELECT COUNT(*) FROM employees;

2. AVG()

Want to find the average value in a column, like the average age of your customers? AVG() is your math whiz. With SELECT AVG(age) FROM customers;, you get the average age. It's like finding the sweet spot in your playlist.

SELECT AVG(salary) FROM employees;

3. SUM()

When you need to add up values, SUM() is your buddy. Say you want to find the total sales for a specific product. With SELECT SUM(sales) FROM sales_data WHERE product_id = 123;, you get the sum of sales for that product. It's like counting your earnings from a concert.

SELECT SUM(revenue) FROM sales;

4. MIN()

MIN() helps you find the smallest value in a column. Imagine you want to know the smallest price in your products table. Use SELECT MIN(price) FROM products;. It's like discovering the quietest song in your playlist.

SELECT MIN(price) FROM products;

5. MAX()

Conversely, MAX() finds the largest value in a column. If you're curious about the most expensive product, use SELECT MAX(price) FROM products;. It's like cranking up the volume on your favorite track.

SELECT MAX(stock_quantity) FROM inventory;

6. CONCAT()

Concatenation is the art of joining strings together. CONCAT() is your string maestro. With SELECT CONCAT(first_name, ' ', last_name) FROM customers;, you combine first and last names. It's like writing the perfect lyrics for a song.

SELECT CONCAT(first_name, ' ', last_name) FROM customers;

7. LEFT, RIGHT, and LEN

Need to extract parts of a string? LEFT(), RIGHT(), and LEN() are your substring sorcerers. LEFT() grabs characters from the left, RIGHT() from the right, and LEN() gives you the string's length. It's like rearranging lyrics in a song.

SELECT LEFT(song_title, 10) FROM songs; -- Grabs the first 10 characters of song titles.
SELECT RIGHT(song_title, 5) FROM songs; -- Grabs the last 5 characters of song titles.
SELECT LEN(song_title) FROM songs; -- Gets the length of each song title.

SELECT
  product_name,
  LEFT(product_description, 50) AS short_description,
  LEN(product_description) AS description_length
FROM products;

8. DATEPART() and GETDATE()

Handling dates and times? DATEPART() and GETDATE() are your time travelers. DATEPART() extracts parts of a date, like the year or month. GETDATE() gives you the current date and time. It's like synchronizing your music playlist.

SELECT DATEPART(year, GETDATE()) - DATEPART(year, birthdate) AS age FROM employees;

SELECT DATEPART(year, birthdate) FROM employees; -- Gets the birth years of employees.
SELECT GETDATE(); -- Gets the current date and time.

9. CASE 

CASE is your decision-maker. It allows you to perform conditional logic in SQL queries. For instance, if you want to categorize customers as "Gold," "Silver," or "Bronze" based on their total purchases:

SELECT
  customer_name,
  total_purchase,
  CASE
    WHEN total_purchase >= 1000 THEN 'Gold'
    WHEN total_purchase >= 500 THEN 'Silver'
    ELSE 'Bronze'
  END AS customer_category
FROM customers;

SELECT
  order_id,
  CASE
    WHEN shipped_date IS NULL THEN 'Not Shipped'
    WHEN delivery_date IS NULL THEN 'In Transit'
    ELSE 'Delivered'
  END AS order_status
FROM orders;

CASE helps you create custom categories, just like writing personalized lyrics for your song.

10. JOIN and INNER JOIN

JOINs are like inviting friends to a party. INNER JOIN combines rows from two or more tables based on a related column between them. For instance, if you have "orders" and "customers" tables, you can combine them to see which customers placed which orders:

SELECT customers.customer_name, orders.order_id, products.product_name
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_details ON orders.order_id = order_details.order_id
INNER JOIN products ON order_details.product_id = products.product_id;

SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

INNER JOIN is like having a jam session with your favorite artists, bringing data from different tables together.

Conclusion

And there you have it, my top 10 SQL Server functions that every programmer should master. Whether you're counting rows, performing calculations, manipulating strings, handling dates, or making decisions based on conditions, these SQL functions are your allies in the world of databases. So, go ahead, incorporate them into your coding symphony, and watch your data dance to your tune!

Other SQL articles that may interest you:
Top 10 SQL Server Functions Programmer should learn
How to use rank, row_number, and dense_rank in SQL...
10 SQL commands every Programmer should learn