SQL

🗓️ Day 2: SQL Query Execution Order, Operators & String Functions

Photo of Mathimaa Mathimaa3 weeks ago
0 1 3 minutes read

🗓️ Day 2: SQL Query Execution Order, Operators & String Functions

Welcome back to Day 2 of your SQL learning journey!
In this lesson, we’ll explore how SQL queries are executed step by step, and we’ll dive into some powerful operators and string functions used in real-world queries.

⚙️ Order of SQL Query Execution

Understanding the order in which SQL executes your commands helps you write efficient and correct queries.

Step Clause Description
1 FROM Specifies the table(s) to query data from
2 WHERE Filters rows based on conditions
3 GROUP BY Groups rows with similar values
4 HAVING Filters results from groups
5 SELECT Defines which columns to display
6 ORDER BY Sorts the final output
7 LIMIT Restricts number of rows returned

📘 Example:

SELECT department, COUNT(*)
FROM employees
WHERE salary > 5000
GROUP BY department
HAVING COUNT(*) > 3
ORDER BY department
LIMIT 10;

🧮 SQL Functions Overview

SQL provides built-in functions to process, manipulate, and transform data.

Types of Functions:

  1. Operators

  2. Case Manipulation Functions

  3. Character Manipulation Functions

  4. Number Functions

  5. Aggregate Functions

  6. Logical Functions

  7. Null Functions

  8. Date Functions

  9. Analytical Functions

🧩 SQL Clauses Recap

  1. SELECT – Columns to display

  2. FROM – Source table

  3. WHERE – Filters based on condition

⚖️ Operators in SQL

Operators help compare, filter, and retrieve specific data.

Operator Description Example
= Equal to WHERE employee_id = 100
<> / != Not equal to WHERE employee_id <> 110
> Greater than WHERE salary > 50000
< Less than WHERE salary < 40000
>= Greater than or equal to WHERE salary >= 60000
<= Less than or equal to WHERE salary <= 30000
IN / NOT IN Matches values in a list WHERE department_id IN (10,20,30)
BETWEEN / NOT BETWEEN Range comparison WHERE salary BETWEEN 30000 AND 50000
LIKE Pattern matching WHERE first_name LIKE 'A%'

🔍 Using LIKE with Wildcards

Symbol Meaning Example
_ Single character 'A_i_' → Matches “Anil”
% Multiple characters 'A%' → Matches “Adam”, “Alex”, “Amelia”

🧠 Examples:

SELECT * FROM employees WHERE first_name LIKE 'A%';
SELECT * FROM employees WHERE first_name LIKE '%s';
SELECT * FROM employees WHERE first_name LIKE 'A%t';
SELECT * FROM employees WHERE first_name LIKE 'L__';
SELECT * FROM employees WHERE first_name LIKE 'D___d';

🔡 Case Manipulation Functions

Used to change the case of text in SQL.

Function Description Example
UPPER() Converts text to uppercase SELECT UPPER(first_name) FROM employees;
LOWER() Converts text to lowercase SELECT LOWER(first_name) FROM employees;
INITCAP() Converts first letter to uppercase SELECT INITCAP(first_name) FROM employees;

Examples:

SELECT first_name, UPPER(first_name) FROM employees;
SELECT first_name, LOWER(first_name) FROM employees;
SELECT INITCAP(first_name) FROM employees;

✂️ Character Manipulation Functions

These are used to modify or analyze strings.

Function Description Example
LENGTH() Returns length of string SELECT LENGTH('India') FROM dual;
REVERSE() Reverses string SELECT REVERSE('India') FROM dual;
REPLACE() Replaces part of a string SELECT REPLACE('India','in','ABC') FROM dual;
TRANSLATE() Replaces character by character SELECT TRANSLATE('India','in','AB') FROM dual;
CONCAT() Joins two strings SELECT CONCAT(first_name,last_name) FROM employees;
** (Pipeline)**
SUBSTR() Extracts substring SELECT SUBSTR('India',1,3) FROM dual;
INSTR() Finds position of substring SELECT INSTR('India','i',1,2) FROM dual;
TRIM() Removes spaces from both ends SELECT TRIM(' India ') FROM dual;
LTRIM() / RTRIM() Removes spaces from left/right SELECT LTRIM(' India ') FROM dual;
LPAD() / RPAD() Pads a string with characters SELECT LPAD('India',10,'*') FROM dual;

💻 Example Queries

SELECT first_name, LENGTH(first_name) FROM employees;

SELECT REPLACE('India','in','abcd') FROM dual; -- Result: abcddia
SELECT TRANSLATE('India','in','abcd') FROM dual; -- Result: abdaa

SELECT first_name, last_name, CONCAT(first_name,last_name) FROM employees;
SELECT first_name || ' ' || last_name || '@' || salary FROM employees;

SELECT SUBSTR('Database',1,4) FROM dual; -- Output: Data
SELECT INSTR('Database','a',1,3) FROM dual;

SELECT TRIM(' Oracle SQL ') FROM dual;
SELECT LPAD('SQL',8,'*') FROM dual; -- Output: *****SQL
SELECT RPAD('SQL',8,'*') FROM dual; -- Output: SQL*****

✅ Summary

On Day 2, you learned:

  • The order of SQL query execution

  • Common operators and pattern matching

  • Case and character manipulation functions

Photo of Mathimaa Mathimaa3 weeks ago
0 1 3 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Check Also
Close
Back to top button