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.