✅ SQL Date & Time Functions ✅

 ✅ SQL Date & Time Functions ✅

1. Why Date Functions Matter?  

Almost every real-world database contains dates 👇  

• ✔️ Orders  
• ✔️ Employee joining dates  
• ✔️ Transactions  
• ✔️ Login activity  

SQL date functions help analyze time-based data 💯  

⚡ 2. Common Date Functions  

Function : Purpose  

NOW() : Current date & time  

CURDATE() : Current date  

CURTIME() : Current time  

YEAR() : Extract year  

MONTH() : Extract month  

DAY() : Extract day  

DATEDIFF() : Difference between dates  

DATE_FORMAT() : Format dates    

🔥 3. NOW(), CURDATE(), CURTIME()

SELECT NOW();

Output : ✔️ Current date + time

SELECT CURDATE();

Output : ✔️ Current date only

SELECT CURTIME();

Output : ✔️ Current time only  

🔥 4. YEAR(), MONTH(), DAY()

SELECT YEAR(joining_date) FROM employees;

SELECT MONTH(joining_date) FROM employees;

SELECT DAY(joining_date) FROM employees;

🔥 5. DATEDIFF()  

👉 Find difference between dates

SELECT DATEDIFF('2026-06-10', '2026-06-01');

✔️ Result → 9 days  

🔥 6. DATE_FORMAT()  

👉 Format dates professionally

SELECT DATE_FORMAT(NOW(), '%d-%m-%Y');

✔️ Example Output → 06-06-2026  

🎯 7. Real Example  

👉 Employees joined in 2025

SELECT * FROM employees WHERE YEAR(joining_date) = 2025;

🎯 8. Practice Tasks  

1. Show current date  

2. Show current time  

3. Extract joining year from employee table  

4. Find employees joined in specific month  

5. Calculate days between two dates  

⚡ Mini Challenge 🔥  

👉 Find employees who joined in the last 30 days    

✅ Solution Using DATEDIFF()

SELECT * FROM employees WHERE DATEDIFF(CURDATE(), joining_date) <= 30;

✅ Alternative Solution Using DATE_SUB()

SELECT * FROM employees WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

🔥 Pro Tip

Date functions are heavily used in:  

👉 Sales reports  

👉 Monthly dashboards  

👉 Retention analysis  

👉 Time-series analytics 💯

✅ SQL String Functions

 

✅ SQL String Functions

  

🧠 1. What are String Functions?  

String functions are used to  

• 👉 manipulate text data  
• 👉 clean messy data  
• 👉 format outputs  

Used heavily in:  

✔️ Data Analytics  

✔️ Reporting  

✔️ ETL processes  

⚡ 2. Common String Functions  

Function  : Purpose  

UPPER() : Convert to uppercase  

LOWER() : Convert to lowercase  

LENGTH() : Count characters  

CONCAT() : Join strings  

SUBSTRING() : Extract part of string  

TRIM() : Remove spaces  

REPLACE() : Replace text  

🔥 3. UPPER() & LOWER()

SELECT UPPER(name) AS upper_name FROM employees;

SELECT LOWER(name) AS lower_name FROM employees;

🔥 4. LENGTH()  

👉 Count number of characters

SELECT name, LENGTH(name) AS total_chars FROM employees;

🔥 5. CONCAT()  

👉 Combine strings

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

🔥 6. SUBSTRING()  

👉 Extract part of string

SELECT SUBSTRING(name, 1, 3) FROM employees;

✔️ Extracts first 3 characters

🔥 7. TRIM()  

👉 Remove extra spaces

SELECT TRIM('   SQL   ');

✔️ Result → SQL

🔥 8. REPLACE()  

👉 Replace text inside string

SELECT REPLACE('I love Java', 'Java', 'SQL');

✔️ Result → I love SQL

🎯 9. Practice Tasks  

1. Convert names to uppercase  

2. Convert emails to lowercase  

3. Combine first & last names  

4. Extract first 4 letters of names  

5. Remove extra spaces from city names  

⚡ Mini Challenge 🔥  

👉 Create employee usernames using:  

first 3 letters of name + employee ID  

Example:  

Amit + 101 → Ami101  

🔥 Mini Challenge Solution 💯

👉 Requirement:  

Create username using:  

- First 3 letters of name  

- Employee ID  

Example:  

Amit + 101 → Ami101  

✅ SQL Solution

SELECT name,

       emp_id,

       CONCAT(SUBSTRING(name, 1, 3), emp_id) AS username

FROM employees;

✅ Example Output  

name : emp_id : username  

Amit : 101 : Ami101  

Neha : 102 : Neh102  

Ravi : 103 : Rav103  

🧠 How It Works  

👉 SUBSTRING(name, 1, 3)  

Extracts first 3 letters  

👉 CONCAT()  

Combines extracted text with employee ID  

🔥 Real-World Usage:  

String functions are commonly used for:  

👉 Username generation  

👉 Email formatting  

👉 Data cleaning  

👉 Customer IDs 💯

COALESCE & NULL Handling in SQL

 

🧠 1. What is NULL in SQL?  

NULL means  : 

• 👉 missing value  
• 👉 unknown value
• 👉 no data available  

⚠️ NULL is NOT:  

❌ 0  

❌ Empty string  

⚡ 2. Problems with NULL Values  

NULL can affect:  

• ❌ Calculations  
• ❌ Comparisons
• ❌ Reports  

Example 👇

SELECT salary + bonus

FROM employees;

If bonus is NULL → result becomes NULL ❌  

========================================================

🔥 3. COALESCE Function  

👉 Replaces NULL with another value  

✅ Syntax

COALESCE(column, value)

⚡ 4. Basic Example

SELECT name,

       COALESCE(bonus, 0) AS bonus

FROM employees;

✔️ If bonus is NULL → shows 0 instead  

⚡ 5. Multiple Values with COALESCE

SELECT name,

       COALESCE(phone, email, 'No Contact') AS contact

FROM employees;

✔️ Returns first non-NULL value  

🔥 6. IS NULL & IS NOT NULL  

👉 Find employees without bonus

SELECT * FROM employees

WHERE bonus IS NULL;

👉 Find employees with bonus

SELECT * FROM employees

WHERE bonus IS NOT NULL;

⚡ 7. NULL Handling in Aggregation

SELECT AVG(COALESCE(bonus, 0))

FROM employees;

✔️ Prevents NULL issues in calculations  

🎯 8. Practice Tasks  

1. Replace NULL bonus with 0  

2. Find rows with NULL values  

3. Find rows without NULL values  

4. Use COALESCE with multiple columns  

5. Calculate total salary safely using COALESCE  

⚡ Mini Challenge 🔥  

👉 Show employee total income:  

salary + bonus  

(If bonus is NULL → treat as 0)  

🔥 Mini Challenge Solution 💯

👉 Requirement:  

total_income = salary + bonus  

If bonus is NULL → treat it as 0  

✅ SQL Solution Using COALESCE

SELECT name,

       salary,

       COALESCE(bonus, 0) AS bonus,

       salary + COALESCE(bonus, 0) AS total_income

FROM employees;

✅ Example Output  

name : Amit, salary : 70000, bonus : 5000, total_income : 75000  

name : Neha, salary : 50000, bonus : 0, total_income : 50000  

name : Ravi, salary : 60000, bonus : 3000, total_income : 63000  

🧠 How It Works  

👉 COALESCE(bonus, 0)  

means:  

- If bonus exists → use bonus  

- If bonus is NULL → use 0  

  

✅ CASE Statement in SQL

 

✅ CASE Statement in SQL  

🧠 1. What is CASE Statement?

• CASE is used for conditional logic in SQL
• It's like if-else in programming
• Helps categorize data
• Create custom labels
• Build smart reports  

⚡ 2. Basic Syntax

SELECT column_name,

       CASE

           WHEN condition THEN result

           ELSE result

       END AS alias_name

FROM table_name;

📊 Example Table  

name : Ajit, Neha, Sujit  

salary : 70000, 40000, 55000  

🔥 3. Basic CASE Example  

👉 Categorize employees by salary

SELECT name, salary,

       CASE

           WHEN salary >= 60000 THEN 'High Salary'

           WHEN salary >= 50000 THEN 'Medium Salary'

           ELSE 'Low Salary'

       END AS salary_category

FROM employees;

✅ Output  

name : Ajit, Sujit, Neha  

salary : 70000, 55000, 40000  

salary_category : High Salary, Medium Salary, Low Salary  

⚡ 4. CASE with Aggregation  

👉 Count employees department-wise

SELECT department,

       SUM(

           CASE

               WHEN salary > 50000 THEN 1

               ELSE 0

           END

       ) AS high_salary_count

FROM employees

GROUP BY department;

⚡ 5. CASE in ORDER BY

SELECT name, department

FROM employees

ORDER BY

CASE

    WHEN department = 'IT' THEN 1

    WHEN department = 'HR' THEN 2

    ELSE 3

END;

🎯 6. Practice Tasks

1. Categorize salary as High/Low

2. Create age groups using CASE

3. Use CASE with GROUP BY

4. Count employees based on salary condition

5. Use CASE in ORDER BY

What will this query return?

SELECT name, salary,

       CASE

           WHEN salary >= 50000 THEN 'High'

           ELSE 'Low'

       END AS category

FROM employees;

A. Deletes salaries

B. Sorts employees

C. Add employees

D. Categorizes salary as High or Low

Ans : D

✅ UNION & UNION ALL in SQL

 ✅ UNION & UNION ALL in SQL  

🧠 1. What is UNION?  

UNION is used to combine results from multiple SELECT queries  

"Merge data from two tables into one result”  

⚡ 2. Rules for UNION  

•  Same number of columns  
•  Same datatype/order of columns  

📊 Example Tables  

👨‍💼 employees_2025

Name  

- Ajit

- Neha  

👨‍💼 employees_2026  

name  

- Sujit 

- Neha  

🔥 3. UNION Example

SELECT name FROM employees_2025

UNION

SELECT name FROM employees_2025;

✔️ Removes duplicates automatically  

✅ Result  

name  

- Ajit

- Neha  

- Sujit  

⚡ 4. UNION ALL

SELECT name FROM employees_2025

UNION ALL

SELECT name FROM employees_2026;

• Keeps duplicates  
• Faster than UNION  

✅ Result  

name  

- Ajit

- Neha  

- Sujit  

- Neha  

🔥 5. UNION vs UNION ALL  

UNION  

•  Removes duplicates
•  Slower  
• Doesn't keep all rows  

UNION ALL  

• Doesn't remove duplicates  
• Faster  
• Keeps all rows  

⚡ 6. ORDER BY with UNION

SELECT name FROM employees_2025

UNION

SELECT name FROM employees_2026

ORDER BY name;

🎯 7. Practice Tasks  

1. Combine employee names using UNION  

2. Combine employee names using UNION ALL  

3. Identify duplicate removal  

4. Sort UNION result using ORDER BY  

5. Compare UNION vs UNION ALL output  

🧠 Why UNION ALL?  

👉 UNION → removes duplicates  

👉 UNION ALL → keeps duplicates + faster

#union #union_alll

What is Business Requirement Document ?

Filtering and Sorting data using SQL statements

 Filtering & Sorting Data using SQL 

 

Once you know the basics (SELECT, FROM, WHERE), filtering and sorting helps you get the exact rows from tables .

 1. Filtering with Conditions (AND, OR, NOT) 

- Use WHERE to filter rows.

- Combine with logical operators: 

  • AND: all conditions true 

  • OR: any condition true 

  • NOT: negate condition 

-- All conditions true (AND)

SELECT * FROM employees

WHERE department = 'Sales'   AND salary > 50000;

-- Any condition true (OR)

SELECT * FROM employees

WHERE department = 'Sales'    OR department = 'Marketing';

-- Negate condition (NOT)

SELECT * FROM employees

WHERE NOT department = 'HR';

 

2.Pattern Matching with LIKE 

- % = any sequence, _ = single char 

  • Names starting with 'J' 

  • Names with 4 letters starting with 'J' 

-- Names starting with 'J'

SELECT * FROM customers

WHERE name LIKE 'J%';

-- Names with four letters starting with 'J'

SELECT * FROM customers

WHERE name LIKE 'J___';

 

3. Filtering with IN 

- Check if value matches any in list 

  SELECT * FROM products WHERE category IN ('Electronics', 'Furniture'); 

 

4. Range Checking with BETWEEN 

- Filter values in a range (inclusive) 

  SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; 

 

5. Checking for NULL Values 

- Find NULL: 

  SELECT * FROM employees WHERE manager_id IS NULL;

- Find NOT NULL: 

  SELECT * FROM employees WHERE manager_id IS NOT NULL; 

 

6. Sorting with ORDER BY 

- Ascending (default): 

  SELECT * FROM employees ORDER BY salary; 

- Descending: 

  SELECT * FROM employees ORDER BY salary DESC; 

- Multiple columns: 

  SELECT * FROM employees ORDER BY department, salary DESC;

List Of Table Operations in SQL

 

List of Table Operations in SQL [1. CREATE, 2. INSERT, 3.UPDATE, 4. ALTER, 5. DELETE, 6. DROP, 7. TRUNCATE ]

1.     CREATE : To define a new Table.

2.    INSERT : Add rows to the table.

3.    UPDATE : Modify existing rows in the table.

4.    ALTER : Modify the structure of the table.

5.    DELETE : Remove Rows from the table.

6.    DROP : Removes the entire table.

7.    TRUNCATE : Removes the all rows of the table but it keeps the table structure.

1>     CREATE : To define a new Table.

 

CREATE TABLE jobs (

job_id INT PRIMARY KEY,

job_title VARCHAR(100),

job_description TEXT,

min_salary DECIMAL(10, 2),

max_salary DECIMAL(10, 2)

);

 

2 INSERT : Add rows to the table

 INSERT INTO jobs (job_id, job_title, job_description, min_salary, max_salary)

VALUES

(100, ‘Manager’, ‘Manage the Team', 800000, 1500000);

 

3>  UPDATE : Modify existing rows in the table.

UPDATE jobs SET min_salary = 100000

WHERE job_id = 100;

Note : It will change the min salary to 100000 from 800000 for the job_id 100.

4>  ALTER : Modify the structure of the table.

ALTER TABLE jobs ADD COLUMN dept_id INT;

 

5> DELETE : Remove Rows from the table

DELETE FROM jobs WHERE job_id = 100;

6> DROP : Removes the entire table.

DROP TABLE jobs;

7> TRUNCATE : Removes the all rows of the table but it keeps the table structure

TRUNCATE TABLE jobs;

Note : >  It removes all rows from the table.

Ø  It retains the Table Structure

Ø  It is faster then DELETE statement

 

 #sql #create #drop #delete #truncate #alter #update #sql_statement

 

Oracle Interview Questions and Answers

 Coming Soon. 

The "Dual Table" in Oracle

> The "DUAL" table is a one row and one column table. The owner of this table is "SYS" but it's accessible to all the db users. 

> It is having single column called as "DUMMY"

> It is used to test SQL expression with out querying to the main/real tables. 

Eg : 1> SELECT SYSDATE FROM DUAL;

O.P : It will print current date and time. 

    

2> SELECT 'Hello, Welcome to SQL' from DUAL;

O.P : 

3> SELECT 5*9 AS RESULT FROM DUAL;

O.P: 

#SQL #LEARNSQL #DUALTABLE

About Oracle Buffer

About Oracle Buffer :-

• All Commands of SQL are typed at the SQL prompt.
• Only one SQL statement is managed in the SQL buffer.
• The current SQL statement replaces the previous SQL statement in the buffer.
• The SQL statements can be divided into different lines within the SQL buffer.
• Only one line i.e, the current line can be active at a time in the SQL buffer.
• At SQL prompt, editing is possible only in the current SQL buffer line.
• Every statement of SQL should be terminated using semi colon ";".
• One sql statement can contain only one semi colon.
• To run the previous or current SQL statement in the bufer type "/" at SQL prompt.
• To open the SQL editor type "ED" at SQL prompt.

Introduction to SQL

INTRODUCTION : -

• SQL usually pronounced as “Sequel” stands for Structured Query Language.
• SQL is the native language of the Oracle Server. It is the language used to communicate with the database. Again SQL consists of SQL statements and SQL*Plus commands. SQL statements are used to talk to the database. When you enter a SQL statement, it is stored in a part of the memory called the SQL Buffer and remains there until a new statement is entered. SQL*Plus is an Oracle tool that recognizes and submits 
• SQL statements to the Oracle server for execution and contains its own command language.

Features of SQL:-

• It is very easy to write, it can be used by all kinds of users with little or not programming experience.
• It is a non-procedural language.
• It reduces the amount of time required for creating and maintaining systems.
• It is based on American National Standards Iinstitute (ANSI) standard SQL.
• It manipulates data and tables definition in the database.
• It is entered into SQL buffer on one or more lines.
• It does not have a continuation character.
• It cannot be abbreviated.
• It uses a termination character to execute command immediately.
• Uses functions to perform some formatting.

Features of SQL*Plus :-

• Recognizes SQL statements and sends them to the server.
• It accepts SQL input from files.
• It provides a line editor for modifying SQL statements.
• It controls environmental settings.
• Accesses local and remote databases.
• It doesn’t allow manipulation of values in the database.
• It is entered one line at a time and is not stored in the SQL buffer.
• Has a dash(-) as a continuation character if command exceed one line.
• Doesn’t require any termination character, commands are executed immediately.
• It uses commands to format data.

Advantages of SQL :-

• SQL is a high level language that provides a greater degree of abstraction than procedural languages. It is designed so that the programmer can specify what data is needed but need not specify how to retrieves it.
• Applications written in SQL can be easily ported across systems.
• SQL as a language is independent of the way it is implemented internally.
• The language even being simple and easy to learn can handle complex situations.
• It is not just a query language, it can be used to define data structures, control access to the data and delete, insert, modify occurrences of the data.
• The results to be expected are well defined i.e, there is no ambiguity about the way a query will interpret the data and produce the result.

Types of SQL :-

SQL can be classified on the basis of its various functionality, listed below.

• Querying data.
• Updating, inserting and deleting database objects.
• Controlling access to the database.
• Providing data integrity and consistency.

SQL statements are divided into following types.

1.  Data Definition Language (DDL)
2.  Data Manipulation Language (DML)
3.  Transaction Control Lauguage (TCL)
4.  Data Control Language (DCL)

    1)   Data Definition Language DDL) :-

The DDL commands enables you to perform :--

• CREATE statement.
• ALTER statement.
• DROP statement.
• RENAME statement.
• TRUNCATE statement.

The objects that can be created, altered, dropped are :-

Cluster, Database, Database link, Function, Index, Package, Package body, Procedure, Rollback segment, Role, Sequence, Synonym, Table, User, View.

    2)  Data Manipulation Language (DML) :-

A DML command is used when we want to add, update or delete data in the database. A collection of DM statements that form a logical unit of work is called a transaction.

The DML commands are :-

• INSERT statement.
• UPDATE statement.
• DELETE statement.

   3)  Transaction  Control Language (TCL) :-

As we know transaction is nothing but a set of inserts, updated and deletes (performed on the database) that form a logical unit of work. TCL is used to control these transactions i.e , whether or not a transaction should take place is controlled by TCL.

The TCL commands are :-

• COMMIT statement.
• ROLLBACK statement.
• SAVEPOINT statement.

    4)  Data Control Language (DCL) :-

The DCL commands enable you to grant or revoke user privileges and roles.

The DCL commands are :-

• GRANT statement.
• REVOKE statement.

==================================================

Oracle Object Oriented Concepts

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object.

Class:

          It is a collection of attributes and functions (method) to plan the object.

Object Table :

·   Object table are created by using the user defined data types.

·      In an object table each row or record is treated as an object.

·      Each row in an object table has an object Identified (OID), which is unique through out       the   database.

·     The rows or objects of an object table can be referenced by other objects with in the  database.

·     An object table is created using the CREATE TABLE command.

·     All object types are associated with default method applied upon the relational tables , i.e    INSERT, DELETE, UPDATE and SELECT.

·   The relational DML operation style is accepted only when the user defined data type is a  collection of Build-in data types, and the object table doesn't contain any REF Constraints.

Creating an user defined Object type :-

Syntax

SQL> CREATE OR REPLACE TYPE

         

          Object (Element1 (size),

           (Element2 (size),

           (ElementN (size) );

             

•      All user defined data types are schema objects of the database.
•            The user defined object data type can be used as Reference in other tables.
•            All user defined data types and object are stored permanently in the Data dictionaries.
•            USER_TYPES
•            USER_OBJECTS
•            We can query for the uesr defined data types and objects using the relational SELECT.

  SQL> Select Type_Name, TypeCode,

          Attributes,Methods

          FROM USER_TYPES;

  SQL> Select Object_Name,

          Object_Type

          FROM USER_OBJECTS;

  Creating User Defined Address Type :-

  

  SQL> Create or Replace TYPE Addr_type AS OBJECT ( Hno VARCHAR2 (10),

                              Street VARCHAR2 (20), City VARCHAR2 (20), Pin NUMBER(6));

  SQL> Create or Replace TYPE PF_TYPE AS OBJECT ( PFNO NUMBER(6), AMT      NUMBER(12,2));

• The above statement create the user defined object data type called as Addr_type and      PF_TYPE in the data dictionary called USER_TYPES.
•            This data type is also called as collection in oracle, and this collection is reusable where ever   the same data type collection is expected in project development.

 Creating Table with user Defined Data Type :-

 SQL> CREATE TABLE EMPLOYEE ( ECODE NUMBER(3), NAME VARCHAR2(20),  ADDRESS ADDR_TYPE, BASIC NUMBER(12,2), PF PF_TYPE);

• Once the user defined data types are created we can instantiate them in the normal  relational tables.
•  These instances look as normal attributes with in the table, but can be operated only with  CONSTRUCTOR METHOD or OBJECT VIEWS.
•  In any of the operation we have to provide reference to all attributes with in the instance,  but partial association is not accepted.

SQL> Create table Supp_Det ( Rol number(3), Name varchar2(20),

          Saddr addr_type, COurse varchar2(20));

Inserting the data into EMPLOYEE Table :-

SQL> INSERT INTO EMPLOYEE VALUES (100, 'Ram', ADDR_TYPE ('130 A', 'CROSS LANE', 'HYBD', 500016), 8000, PF_TYPE(1200,400));

Select Data From OBJECT table:-

SQL> SELECT * FROM EMPLOYEE;

SQL> SELECT ADDRESS FROM EMPLOYEE

SQL> SELECT NAME, E.ADDRESS.CITY FROM EMPLOYEE E;

Note :- Alias is must to retrieve/manipulate Object elements.

Update data from Object Table:-

SQL> UPDATE EMPLOYEE E SET BASIC =BASIC+100 WHERE E.ADDRESS.CITY=’HYD’;

Deleting data from Object Table:-

SQL> DELETE FROM EMPLOYEE;

SQL> DELETE FROM EMPLOYEE E WHERE E.PF.PFNO=1200;

Creating User Defined Emp Det Type:

SQL> Create type info As Object (code Number(4), Name Varchar2(20), Hno Varchar2(10), Street Varchar2(20), City Varchar2(20), Pin Number(6));

Creating an Object Table:-

• Is a table which is entirely build from the abstract type.

Syntax

       Create Table (Table_Name) of (Object Name);

Inserting Rows into Object Table:-

•           To insert a record into an Object Table we may use the CONSTRUCTOR METHOD of the actual data type or directly implement the RELATIONAL INSERT statement.
•          The Normal; INSERT or RELATION INSERT is possible only when the table does not contain any nested data types.

Relation Insert:-

SQL> Insert into Emp_info Values(1001, ‘ram’, ‘12-123’, ‘AMEERPET’,’MGS’,’BLORE’,516217);

Inserting Using Construct Method:-

SQL> Insert into Emp_info values(info(1002,’suman’,’12-124’,’vijiinag’,’blore’,516217);

SQL> Create table supp_info of Info;