SQL Cheat Sheet
SQL: Functions and Operators
INTRODUCTION:
SQL is a powerful programming language used for managing and manipulating relational databases. One of the key features of SQL is its functions and operators, which can be used to perform various calculations, comparisons, and transformations on data. In this article, we will discuss the various functions and operators in SQL, their usage, and examples.
Functions in SQL:
Functions are pre-built routines that accept one or more arguments and return a value. SQL offers several built-in functions, such as mathematical, string, date/time, and aggregate functions. Mathematical functions allow you to perform arithmetic operations, while string functions manipulate character strings. Date/time functions manipulate dates and times, while aggregate functions perform calculations on groups of rows in a table.
Here are four examples:
Let’s say you have a table named “sales” with columns “id,” “product,” “quantity,” and “price.” You want to calculate the total revenue generated by each product. You can use the SUM function, which is an aggregate function, to achieve this. And then this query will return the total revenue generated by each product.
SELECT product, SUM(quantity * price) as total_revenue
FROM sales
GROUP BY product;
// example of using mathematical functions //
SELECT ABS(-10) as absolute_value, ROUND(3.14159, 2) as rounded_value;
// example of using date/time functions //
SELECT DATEADD(year, 1, '2022-01-01') as future_date, DATEDIFF(day, '2022-01-01', '2023-04-20') as days_diff;
// example of using string functions //
SELECT CONCAT('Hello', ' World!') as concatenated_string,
SUBSTRING('SQL is great', 1, 3) as substring;
Operators in SQL:
Operators are symbols used to perform logical and mathematical operations. SQL offers several operators, including comparison, logical, arithmetic, and bitwise operators. Comparison operators are used to compare two values, logical operators are used to combine multiple conditions, arithmetic operators are used to perform mathematical operations, and bitwise operators are used to perform bitwise operations on binary data.
Here are four examples:
Let’s say you have a table named “customers” with columns “id,” “name,” and “age.” You want to select all the customers who are older than 25 years and whose name starts with “J.” You can use the AND and LIKE operators to achieve this:
SELECT *
FROM customers
WHERE age > 25 AND name LIKE 'J%';
// example of using logical operators //
SELECT *
FROM orders
WHERE status = 'completed'
AND (payment_method = 'credit_card'
OR payment_method = 'paypal');
// example of using arithmetic operators //
SELECT price * quantity as total_price
FROM sales
WHERE age > 25 AND name LIKE product = 'iPhone';
// example of using bitwise operators //
SELECT bit_column & 4
as result FROM my_table;
IN CONCLUSION:
Functions and operators are essential tools in SQL for managing and manipulating data in relational databases. By understanding and using these functions and operators effectively, you can perform complex calculations, comparisons, and transformations on your data. Whether you are a beginner or an experienced SQL developer, mastering these functions and operators will undoubtedly help you become a more effective data analyst.