SQL is a powerful language for managing and manipulating databases. In this guide, we’ll explore essential SQL functions and different types of joins that allow you to work efficiently with your data.

SQL String Functions

SQL String Functions help in manipulating text data and offer operations like concatenating strings, extracting parts of a string, and modifying text.

  • 1. CONCAT()-Combines two or more strings into one 
--combine first and last name 
select concat(first_name ,' ',last_name) as full_name
from luxdevteaching.customers;
  • 2. SUBSTRING()-Extracts part of a string 
--get the first 3 letters of the first name 
select substring(first_name, 1 ,3) as short_name
from  luxdevteaching.customers;
  • 3. LENGTH()-Returns the number of characters in a string. 
--find the length of customer first name 
select first_name, length(first_name)as name_length
from  luxdevteaching.customers;
  • 4. UPPER(),LOWER()-Converts text to uppercase or lowercase 
--convert the first names to uppercase
select upper(first_name) as uppercase_names
from  luxdevteaching.customers;
--convert the book title to lowercase
select lower(title) as lowercase_titles
from  luxdevteaching.books;
  • 5. TRIM ()-removes extra spaces from the beginning or the end of a text string 
select trim(  title  ) as book_title
from  luxdevteaching.books;
  • 6. REPLACE()-Replaces occurrences of a substring within a string 
--relace nairobi with NRB
select replace (city,'Nairobi','NRB') as short_name
from  luxdevteaching.customers;

SQL Date Functions

SQL also allows us to perform operations on date values. Date functions help in extracting parts of a date or performing calculations involving dates.

  • 1. NOW()-Returns the current date and time 
select NOW() as CURRENT_DATE;
  • 2. YEAR()-extracts the year 
--extract the year from order date 
select order_id,extract (year from order_date) as order_year
from  luxdevteaching.orders;
  • 3. MONTH()-extracts the month 
--extract the month from the order date 
select order_id,extract(month from order_date) as order_month
from  luxdevteaching.orders;
  • 4. DAY()-extracts the day 
--extract the day from the order day
select order_id,extract(day from order_date) as order_day
from  luxdevteaching.orders;
  • 5. DATEDIFF()-Returns the difference in days between two dates. 
-- find the difference between today and published date
select date '2025-04-16'-published_date as diff
from luxdevteaching.books;

SQL Mathematical Functions

Mathematical functions are used for performing calculations and rounding operations.

  • 1. ROUND()-Rounds a numeric value to the specified number of decimal places. 
--round the price to 2 decimal places
select title ,price,round(price,1) as rounded_price
from luxdevteaching.books;
  • 2. CEIL()-returns the smallest integer greater than or equal to the number 
select title,price ,ceil(price) as cell_price
from luxdevteaching.books;
  • 3. FLOOR()-returns the largest integer less than or equal to the number 
select title,price,floor(price) as floor_price
from luxdevteaching.books;
  • 4. MOD()-Returns the remainder of division between two numbers. 
--check if the order quantity is odd or even
select order_id, quantities,mod(quantities,2)
as remainder
from luxdevteaching.orders;
  • 5. POWER()-Raises a number to the power of another number 
--raise the quantity to the power 2 
select order_id,power(quantities,2) as squared_quantity
from luxdevteaching.orders;
  • 6. ABS()-Returns the absolute (non-negative) value of a number. 
--find  the absolute value of  the price difference
select title,price,abs(price-2000) as price_diff
from luxdevteaching.books;

SQL JOINS

SQL Joins allow you to combine related data from different tables into one result set.Let’s look at different types of SQL joins:

  • 1. INNER JOIN -N returns only the rows that have matching values in both tables 
--employees and the projects they have been assigned to 
select employees.name ,projects.project_name
from employees
inner join projects on employees.employee_id= projects.employee_id;
  • 2. LEFT JOIN(LEFT OUTER JOIN)-returns all the rows from the left table, and the matched rows from the right table.If there’s no match, it fills in NULL. 
--list all employees and their departments
select employees.name,departments.department_name
from employees
left join departments  on employees. department_id =departments.department_id;
  • 3. RIGHT JOIN(RIGHT OUTER JOIN)- shows all the entries from the right side, whether there’s a match on the left or not. 
--list all projects and employees (even unassigned projects)
select projects.project_name,employees.name
from employees
right join projects on projects.employee_id =employees.employee_id;
  • 4. FULL OUTER JOIN-It shows all rows from both tables. If there’s no match, it uses NULLs 
--show all employees and all departments
select employees.name,departments.department_name
from employees 
full outer join departments on employees.department_id = departments.department_id;
  • 5. CROSS JOIN-returns every combination of rows from both table 
--cross departments and employees
select employees.name,departments.department_name
from departments
cross join employees;
  • 6.SELF JOIN-It is when a table joins with itself 
--find employee manager relationships
select A.name as employee, B.name as manager
from employees A 
join employees B on B.manager_id = A.employee_id;

These SQL functions and joins are crucial for working with relational databases, enabling you to perform a variety of operations, from simple string manipulations to complex data retrieval from multiple tables. Understanding how to use these functions and joins effectively will enhance your ability to work with data in SQL databases.