SQL (Structured Query Language) is the primary language for managing and manipulating databases. Whether you're analyzing data or performing database operations, understanding SQL functions and operators is crucial. In this article, we will explore some of the essential SQL functions and operators, including aggregate functions, comparison operators, logical operators, arithmetic operators, and set operators. These tools will help you manage and query databases efficiently, especially when handling large datasets.

AGGREGATE FUNCTIONS

Aggregate functions in SQL are used to perform calculations on a set of values and return a single value. They are commonly used in conjunction with the GROUP BY clause to group rows based on certain attributes.

  • 1. COUNT()-It returns the number of rows that match a specific condition. It’s often used to find the total number of entries in a table or based on specific criteria. 
--count the total customers;
select count(*) as total_customers
from luxdevteaching.customers;
  • 2. SUM()-It adds up the values in a numeric column. It’s particularly useful when calculating totals 
--total quantity of all orders
select sum (quantities) as total_orders
from luxdevteaching.orders;
  • 3. AVG()-It calculates the average (mean) value of a numeric column. It can be useful for finding average prices or other metrics. 
--average price of books
select avg(price) as avg_price
from luxdevteaching.books;
  • 4. MAX()-It finds the largest value in a column. It is often used to find the highest value in a dataset, such as the most expensive book. 
--find the most expensive book
select max(price) as most_expensive_book
from luxdevteaching.books;
  • 5. MIN()-It finds the smallest value in a column. It’s useful for identifying the least expensive item or the lowest quantity. 
--find the least expensive book
select min(price) as cheapest_book
from luxdevteaching.books;

SQL OPERATORS

SQL Comparison Operators

These operators compare values in your data and return true or false. They are mostly used inside the WHERE clause to filter results.

  • 1. =(Equals)-shows where a column matches an exact value 
--find customers in kisumu
select * from luxdevteaching.customers 
where city = 'Kisumu';
  • 2. != or <> (Not Equals)-Selects rows where a column value does not match the specified value. 
--find customer not in Nairobi
select first_name,last_name,city 
from luxdevteaching.customers 
where city <>'Nairobi';
  • 3. > (Greater Than)-Selects rows where column value is greater than the given value 
--find books priced above 2500
select * from luxdevteaching.books 
where price > 2500;
  • 4. < (Less Than)-Selects rows where column value is less than the given value. 
--find books priced below 2500
select * from luxdevteaching.books 
where price < 2500;
  • 5. >= (Greater Than or Equal To)-Selects rows where column value is greater than or equal to the given value 
--find orders with quantity >=2
select * from luxdevteaching.orders 
where quantities >= 2;
  • 6. <= (Less Than or Equal To)-Selects rows where column value is less than or equal to the given value. 
--find orders with quantity <=2
select * from luxdevteaching.orders 
where quantities <=2;
  • 7. BETWEEN (Range) -Selects rows with column values between two values (inclusive). 
--find books priced between 2000 and 3000
select * from luxdevteaching.books 
where price between 2000 and 3000;
  • 8. LIKE(Pattern Matching)-Use LIKE to search for patterns in text data 
--find customers with first name starting with'J'
select * from luxdevteaching.customers 
where first_name like 'J%';
--find books with title containing SQL 
select * from luxdevteaching.books 
where title like '%SQL%';
--show customer emails ending with 'gmail.com'
select * from luxdevteaching.customers 
where email like '%gmail.com';
  • 9. IN (Multiple Values)-Use IN to filter records by matching any value in a list 
--find customers in kisumu or nairobi
select * from luxdevteaching.customers 
where city in ('Kisumu','Nairobi');

SQL Logical Operators

Logical operators allow us to combine multiple conditions inside the WHERE clause.

  • 1. AND-All conditions must be true 
--find customers form kisumu and first name containing paul
select * from luxdevteaching.customers 
where city ='Kisumu'
and first_name ='Paul';
  • 2. OR-one condition has to be true 
--find customers from nairobi or kisumu
select * from luxdevteaching.customers 
where city ='Nairobi' or city = 'Kisumu';
  • 3. NOT-Reverses the result of a condition. If condition is true, NOT makes it false. 
--find customers not from Kisumu
select * from luxdevteaching.customers 
where not city ='Kisumu';

SQL Arithmetic Operators

  • 1. Addition (+)-Adds two numbers together 
-- Add book price with 500
select title,price, price +500
from luxdevteaching.books;
  • 2. Subtraction (-)- gets the difference between 2 values 
-- Discount each book with 200kes
select title, price, price- 200 as discountedprice_
from luxdevteaching.books;
  • 3. Multiplication (*)-finds the product 
-- Double the price of books
select title, price, price * 5 as newprice_
from luxdevteaching.books;
  • 4. Division (/) 
select title, price, price/2 as discountedprice_
from luxdevteaching.books;
  • 5.Modulus(%) 
-- Find the remainder when quantity is divided by 2
select quantities, quantities % 2
from luxdevteaching.orders;

SQL Set Operators

Set operators allow you to combine the results of two or more SELECT queries.

  • 1. UNION-Combines two result sets and removes duplicates. 
-- Combine the customers first name and authors names
Select first_name as name from luxdevteaching.customers
union
select author as name from luxdevteaching.books;
  • 2. UNION ALL-Combines two result sets and keeps duplicates. 
--combine customer frist name and authors 
select first_name as name from luxdevteaching.customers 
union all 
select author as name from luxdevteaching.books;
  • 3. INTERSECT-Returns common records from both queries 
--find common id in between customer_id and order_id from orders
select customer_id as id from luxdevteaching.customers 
intersect
select order_id as id from luxdevteaching.orders;
  • 4. EXCEPT-Returns records from the first query that are not in the second query 
--find book id  not on orders
select book_id as books from luxdevteaching.books 
except 
select book_id as books from luxdevteaching.orders;

OTHER USEFUL SQL OPERATORS

  • 1. IS NULL-Used to find records where a column has no value 
--check for customers without a city
select * from luxdevteaching.customers 
where city is null;
  • 2. IS NOT NULL-Used to find records where the column has a value. 
--check for customers with cities
select * from  luxdevteaching.customers 
where city is not null;
  • 3. DISTINCT-Removes duplicate values from the results 
--list unique authors 
select distinct author from luxdevteaching.books;