Create an account on Oracle.
ULS Link : [Link]
workshop/105208
Oracle APEX :
Week 1: SQL - MCQ1
Week 2: Pl/SQL - MCQ2
Week 3: APEX Development - MCQ3
Hands On Assessment
MyCompetency Assessment - P2 +
======================================================================
SQL : Structured Query Language
Oracle :
- It is a company which has various products
Oracle 2,
Oracle 3
Oracle 4
Oracle 5.....
Oracle 9i ..i ->internet
Oracle 10g - g->grid computing
Oracle 19c -c->cloud
Oracle 21 c
Oracle 26ai
Oracle 19c
- Can be used to extensive complex large scale applications .
- It supports Transaction
- It is very secured
- It is highly scalable
- It is very flexible
- It is used as data source - which can store data, manage data .
- Instagram , WhatsApp, Amazon...
- Collection of contacts
- Group of students
- list of bank accounts
- Education industry, Healthcare industry, Banking industry , IT industry , Retail
industry, ecommerce industry
s/w Development
- Presentation Layer (User Interface) through which the end user is interacting -
>HTML, CSS...
- Logical Layer - Business Logic Layer -> Java, C, C++,Python ....
- Data Access Layer - Which manages the data -> Oracle, SqL Server, MySQL..
Data : Raw information
name : Ananya
Class : 12th standard
contact no :
email id :
product : watch
Database : collection of logically related information .
Database Management : manages the data : add a record, modify the record, delete the
record
Database Management system (DBMS) : A system require to manage the data - Oracle
Data Models :
Entity Relationship Model
Hierarchical Model
Network Model
Relational Model (RDBMS )
RDBMS :
- Relational Database Management System
- It stores the data in the form of rows and column
- rows are knows as tuples
- columns are known as attributes
- a single value (data) is known as Field
Key concepts - Primary key, Foreign key, Unique key
SQL :
- Structured Query Language
- It is a query Language
- It is not case sensitive.
- SQL is used to manage the data
- It is used to implement CRUD Operation .
C- Create - To add a new record
R- Read - To retrieve the record
U- Update - To modify the record
D- Delete - To remove the record
-It can be used for transaction, index, views, Stored procedures, functions,
permissions etc..
SQL Command
DDL - Data Definition Language - (structure) CREATE, ALTER, DROP, TRUNCATE
DML - Data Manipulation Language (manipulating the information)- INSERT, UPDATE,
DELETE
DCL - Data Control Language - GRANT, REVOKE
DQL - Data Query Language - SELECT
TCL - Transaction Control Language - COMMIT, ROLLBACK, SAVEPOINT
======================================================================
==================
DQL -
- It has only 1 command - SELECT command .
- It is used to fetch the data from database .
Arithmetic Operators : +, -, *,/
Comparison/Relational Operators : >, < ,>=,<=
Logical Operators : AND, OR, NOT
Operators precedence :
* has more precedence than +
Today's Queries :
-- select * from [Link];
-- SELECT FIRST_NAME,LAST_NAME,SALARY FROM [Link];
-- Filter the records
-- select * from [Link] where salary=10000;
-- select first_name, salary as Original_Salary, salary+1000 as Modified_Salary from
[Link];
select first_name, salary as "Original Salary",100+salary as M1_Salary,salary*2,
(100+salary)*2 as Modified_Salary from [Link];
-- select * from [Link] where salary > 10000;
--select * from [Link] where salary >= 10000;
-- select * from [Link] where salary < 10000;
-- Between and not between
--select first_name, last_name ,salary from [Link] where salary between
10000 and 14000;
-- select first_name, last_name ,salary from [Link] where salary not between
10000 and 14000;
--Logical OPerators
SELECT first_name, last_name ,salary from [Link] where DEPARTMENT_ID=10
and salary=4400;
SELECT first_name, last_name ,salary,DEPARTMENT_ID from [Link] where
DEPARTMENT_ID=10 or salary=10000;
SELECT first_name, last_name ,salary,DEPARTMENT_ID from [Link] where
DEPARTMENT_ID=10 or DEPARTMENT_ID=80 or DEPARTMENT_ID=190
select first_name, last_name ,salary,DEPARTMENT_ID from [Link] where
DEPARTMENT_ID=10 or DEPARTMENT_ID=80 or DEPARTMENT_ID=190
-- IN OPerator
select first_name, last_name ,salary,DEPARTMENT_ID from [Link] where
DEPARTMENT_ID IN(10,40,80,190);
select first_name, last_name ,salary,DEPARTMENT_ID from [Link] where
DEPARTMENT_ID NOT IN(10,40,80,190);
-- Concatenation
select first_name , last_name from [Link];
select first_name ||' '|| last_name as "Full Name" from [Link];
--Literals
select first_name ||' '|| last_name || ' is having salary '||salary as "Full Name" from
[Link];
-- Like keyword wildcard charaters : %-represents any no if characters , _ represents 1
character
select FIRST_NAME from [Link] where FIRST_NAME like 'M%';
select FIRST_NAME from [Link] where FIRST_NAME like '%l';
select FIRST_NAME from [Link] where FIRST_NAME like '_o%';
select FIRST_NAME from [Link] where FIRST_NAME like '_o%n';
-- Escape in SQL allows you to treat wildcard characters (%, _) as normal text while
searching with LIKE
select first_name,last_name,job_id from [Link] where job_id like 'MK/_%'
ESCAPE'/';
select first_name from [Link] where first_name='John';
select distinct first_name from [Link];
-- To view structure of employee table
describe [Link];
desc [Link];
----------------------------------------------------------------------------
-- order by - It sorts the data - Ascending , descending
-- ASC 10, 20, 50, 60 , DESC 60,50,20,10
-- Salary wise Empoyee records (Highest to Lowest)
select last_name,salary from [Link] order by salary;
select last_name,salary from [Link] order by salary desc;
select last_name, DEPARTMENT_ID,manager_id from [Link] order by
DEPARTMENT_ID,manager_id desc;
select last_name, DEPARTMENT_ID,manager_id from [Link] order by
2,manager_id desc;
select last_name, DEPARTMENT_ID,manager_id from [Link] order by 2 desc,3;
-- NULL is not same as zero . Data is missing or not available
-- find out employees who is not assigned with any manager
select * from [Link] where manager_id is NULL;
select * from [Link] where manager_id is NOT NULL;
-- Quote Operator
select first_name ||' '|| last_name || q'[ isn't having salary ]'||salary as "Full Name" from
[Link];
select first_name ||' '|| last_name || q'! isn't having salary !'||salary as "Full Name" from
[Link];
-- dual table - dummy table
select * from dual;
select length('Hello') from dual;
-- ROW LIMITING CLAUSE
select last_name, salary from [Link] order by salary desc fetch first rows only;
select last_name, salary from [Link] order by salary desc fetch FIRST 5 rows
only;
select last_name, salary from [Link] order by salary desc offset 3 rows fetch
next 2 rows only;
-- substitution variables - We can query with different values
select * from [Link] where DEPARTMENT_ID=30;
select * from [Link] where DEPARTMENT_ID=&dept_id;
select last_name,&col,DEPARTMENT_ID from [Link] order by &col1 desc;
-- && - It ask for prompt only once
select last_name,&&col,DEPARTMENT_ID from [Link] order by &col desc;
Define dept_id=10;
select * from [Link] where DEPARTMENT_ID=&dept_id;
------------------------------------------------------------------------------
-- Single row functions - they return one result per row.
-- Multi Row functions - they return 1 result for set of rows : avg(), sum(),max()...
-- Functions :
-- -It is a block of code that performs a specific task.
-- -Functions take some input, process as per input and return the output.
-- Character Functions, Numeric Functions, Date functions, Conversion Functions
etc..
-- character functions
select last_name,length(last_name)from [Link];
select upper(last_name)from [Link];
select lower(last_name)from [Link];
select email,Initcap(email) from [Link];
select concat(first_name, last_name)from [Link];
select last_name,substr(last_name,2,4)from [Link];
select last_name,substr(last_name,2,6)from [Link];
select last_name,substr(last_name,4)from [Link];
select phone_number,instr(phone_number,'.')from [Link];
select instr('Hello World','l')from dual;
select instr('Hello World','l',1,2)from dual;
select instr('Hello World','l',-1)from dual;
-- Adding the charcter to the left side of the string until it reaches thetotal string length
select lpad('hello',10,'*')from dual;
select rpad('Welcome',13,'*') from dual;
select ltrim(' Hello')from dual;
select ltrim(' He llo')from dual;
select rtrim('Hello ')from dual;
select trim(' testing ')from dual;
select PHONE_NUMBER,replace(PHONE_NUMBER,'.','!')from [Link];
select PHONE_NUMBER,translate(PHONE_NUMBER,'0123456789','##########')from
[Link];
-- case Insensitivity
select * from [Link] where lower(LAST_NAME)='whalen';
-- Number fucntions
select round(123.45)from dual;
select round(123.75)from dual;
select Employee_id,mod(Employee_id,2)from [Link];
-- Removes decimal values without rounding
select trunc(123.567,1) from dual;
select trunc(123.567,-1) from dual;
select trunc(123.567,-2) from dual;
select sqrt(25) from dual;
select power(10,2)from dual;
select abs(-100)from dual;
-- round off up to nearest [Link] the smallest integer greater than on equla to
number.
select ceil(10.2)from dual;
select ceil(20)from dual;
select ceil(15.8)from dual;
-- round off up to nearest [Link] the largest integer less than on equal to
number.
select floor(10.2)from dual;
select floor(15.8)from dual;
select floor(-4.5) from dual;
-- Date Functions
-- By default, In Oracle date format is DD-MON-RR
-- MON- represensts month name
-- RR represents century year
-- 08-DEC-25 -> 08-DEC-2025
-- 08-DEC-55 -> 08-DEC-1955
-- 0 to 49 - represents century as current year
-- 49 to 99 - Previous century
-- Displays current date and time of database server
select sysdate from dual;
-- Displays session's current date and time (with timezone)
-- It adjust the time according to time zone
select CURRENT_date from dual;
-------------------------------------------------------------
-- No of days of each employees in a company.
select * from [Link];
-- no of days
select hire_date,trunc(sysdate-hire_date) as no_of_days from [Link];
-- no of weeks
select hire_date,trunc(sysdate-hire_date) as no_of_days ,(trunc(sysdate-hire_date)/7)
as no_of_weeks from [Link];
select hire_date,add_months(hire_date,1)from [Link] ;
select months_between(sysdate,hire_date)from [Link];
select months_between(sysdate,'01-OCT-25')from dual;
select trunc(months_between(sysdate,To_DATE('01-OCT-2025','DD-MON-YYYY')))from
dual;
select EXTRACT(YEAR from hire_date) from [Link];
select * from dual;
select length('Hello') from dual;
select last_name, salary from [Link] order by salary desc fetch first rows only;
select last_name, salary from [Link] order by salary desc fetch FIRST 5 rows
only;
select last_name, salary from [Link] order by salary desc offset 3 rows fetch
next 2 rows only;
-- substitution variables - We can query with different values
select * from [Link] where DEPARTMENT_ID=30;
select * from [Link] where DEPARTMENT_ID=&dept_id;
select last_name,&col,DEPARTMENT_ID from [Link] order by &col1 desc;
select last_name,&&col,DEPARTMENT_ID from [Link] order by &col desc;
Define dept_id=10;
select * from [Link] where DEPARTMENT_ID=&dept_id;
select last_name,length(last_name)from [Link];
select upper(last_name)from [Link];
select lower(last_name)from [Link];
select email,Initcap(email) from [Link];
select concat(first_name, last_name)from [Link];
select last_name,substr(last_name,2,4)from [Link];
select last_name,substr(last_name,2,6)from [Link];
select last_name,substr(last_name,4)from [Link];
select phone_number,instr(phone_number,'.')from [Link];
select instr('Hello World','l')from dual;
select instr('Hello World','l',1,2)from dual;
select instr('Hello World','l',-1)from dual;
select lpad('hello',10,'*')from dual;
select rpad('Welcome',13,'*') from dual;
select ltrim(' Hello')from dual;
select ltrim(' He llo')from dual;
select rtrim('Hello ')from dual;
select trim(' testing ')from dual;
select PHONE_NUMBER,replace(PHONE_NUMBER,'.','!')from [Link];
select PHONE_NUMBER,translate(PHONE_NUMBER,'0123456789','##########')from
[Link];
select * from [Link] where lower(LAST_NAME)='whalen';
select round(123.45)from dual;
select round(123.75)from dual;
select Employee_id,mod(Employee_id,2)from [Link];
select round(123)from dual;
select round(123.567,2)from dual;
select trunc(123.567,1) from dual;
select trunc(123.567,-1) from dual;
select trunc(123.567,-2) from dual;
select trunc(123.567,-3) from dual;
select sqrt(25) from dual;
select power(10,2) from dual;
select abs(-100)from dual;
select ceil(10.2) from dual;
select ceil(10.8) from dual;
select ceil(10) from dual;
select ceil(-4.5) from dual;
select floor(10.2) from dual;
select floor(10) from dual;
select floor(-4.5) from dual;
select sysdate from dual;
select CURRENT_date from dual;
select * from [Link];
select trunc(sysdate-hire_date) from [Link];
select hire_date,trunc(sysdate-hire_date) as days ,(trunc((sysdate-hire_date)/7)) as
weeks from [Link];
select months_between(sysdate,hire_date)from [Link];
select months_between(sysdate,'01-OCT-25')from dual;
select trunc(months_between(sysdate,To_DATE('01-OCT-2025','DD-MON-YYYY')))from
dual;
select EXTRACT(YEAR from hire_date) from [Link];
-----Conversion functions------------------------------------------------------
select trunc(months_between(sysdate,To_DATE('01-OCT-2025','DD-MON-YYYY')))from
dual;
-- convert date / number to text
select To_Char(hire_date)from [Link];
describe [Link];
SELECT TO_NUMBER('10000') from dual;
select cast(salary as varchar2(100))from [Link];
--NULL Hndling Functions------------------------------------------------------------------------
select * from [Link];
-- NVL function replace NULL with value
select commission_pct,NVL(commission_pct,0)from [Link];
-- NVL2
select commission_pct,NVL2(commission_pct,'HAs commision','NOt Having
Commission') from [Link];
-- NULLIF- Returns NULL if both expresssions are same
select salary,NULLIF(salary,10000)from [Link];
-- coalesce function- Returns first non -NULL values
select coalesce(NULL,NULL,'Testing')from dual;
select first_name, coalesce(Email,phone_number,'NO contacts Available')from
[Link];
-- DEcode function
select employee_id, department_id,
decode(department_id,
10,'Marketing',
20,'Sales',
30,'HR',
'Other'
)as dept_name
from [Link];
-- Conditional functions
select first_name,
case when salary >10000 then 'High'
when salary between 8000 and 10000 then 'Mid'
when salary < 8000 then 'low'
ELSE 'Not applicable'
END as salary_level
from [Link];
select greatest(salary, NVL(commission_pct,0)*10000) from [Link];
select least(salary, NVL(commission_pct,0)*10000) from [Link];
select user from dual;
---------------------------Aggregate function-----------------------------------
select avg(salary)from [Link];
select sum(salary)from [Link];
select max(salary)from [Link];
select min(salary)from [Link];
select count(*)from [Link];
select count(commission_pct)from [Link];
SELECT REPLACE('Hel lo', ' ', '') AS no_spaces
FROM dual;
Inner Join
Left Outer join
Right Oute join
Self join
Cross Join
Natural Join
Self join
Non equi join
---------Joins-----------------------------------------------------------
select * from [Link] where first_name='Jagan';
select * from [Link] where email='john@[Link]'
-------------------------------------------------------------------
select * from [Link]; -- dept_id
select * from [Link];-- dept_id, location_id
select * from [Link];-- location_id
-- inner join - Fetches the record based on matching condition. If condition does not
match
-- it will not show those records.
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link]
INNER JOIN [Link] ON [Link].DEPARTMENT_ID =
[Link].DEPARTMENT_ID;
SELECT
e.EMPLOYEE_ID,
e.FIRST_NAME,
e.department_id,
d.DEPARTMENT_NAME
FROM
[Link] e
INNER JOIN [Link] d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
SELECT
e.EMPLOYEE_ID,
e.FIRST_NAME,
e.department_id,
d.DEPARTMENT_NAME
FROM
[Link] e
INNER JOIN [Link] d using(department_id); -- ON e.DEPARTMENT_ID =
d.DEPARTMENT_ID;
SELECT
EMPLOYEE_ID,
FIRST_NAME,
department_id,
DEPARTMENT_NAME
FROM
[Link]
INNER JOIN [Link] using(department_id);
select e1.EMPLOYEE_ID, e1.FIRST_NAME,
e2.FIRST_NAME as manager_id
from [Link] e1 LEFT JOIN [Link] e2
on e1.manager_id=e2.employee_id;
-- inner join - Fetches the record based on matching condition. If condition does not
match
-- it will not show those records.
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link]
INNER JOIN [Link] ON [Link].DEPARTMENT_ID =
[Link].DEPARTMENT_ID;
SELECT
e.EMPLOYEE_ID,
e.FIRST_NAME,
e.department_id,
d.DEPARTMENT_NAME
FROM
[Link] e
INNER JOIN [Link] d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
SELECT
e.EMPLOYEE_ID,
e.FIRST_NAME,
e.department_id,
d.DEPARTMENT_NAME
FROM
[Link] e
INNER JOIN [Link] d using(department_id); -- ON e.DEPARTMENT_ID =
d.DEPARTMENT_ID;
SELECT
EMPLOYEE_ID,
FIRST_NAME,
department_id,
DEPARTMENT_NAME
FROM
[Link]
INNER JOIN [Link] using(department_id);
-- Natural join
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link]
Natural JOIN [Link]
-- Left outer join- fetch all records from the table mentioned on left side of JOIN syntax
-- and matching from right side
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link] e LEFT OUTER JOIN [Link] d ON e.department_id
=d.department_id;
-- Right outer join
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link] e RIGHT OUTER JOIN [Link] d ON e.department_id
=d.department_id;
-- Full outer join
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link] e FULL OUTER JOIN [Link] d ON e.department_id
=d.department_id;
-- CROSS JOIN -- catesian product
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link] e CROSS JOIN [Link] d
-- SELF JOIN
select EMPLOYEE_ID,first_name,manager_id from [Link]
-- Fetch emp_id, emp_name, manager_name
SELECT
e1.EMPLOYEE_ID,
e1.FIRST_NAME,
E2.FIRST_NAME as manager_name
FROM
[Link] e1
LEFT OUTER JOIN [Link] e2 ON E1.MANAGER_ID = E2.EMPLOYEE_ID;
--Emp_id, emp_name, dept_name, city of the employee- Join 3 tables
select * from [Link];
select e.EMPLOYEE_ID,e.FIRST_NAME,d.department_name,[Link]
from
[Link] e JOIN
[Link] d ON e.department_id=d.department_id
JOIN
[Link] l ON d.location_id=l.location_id;
select * from [Link];
-- Find employees who are not working in their salary range
SELECT
E.EMPLOYEE_ID,
E.FIRST_NAME,
[Link],
J.JOB_TITLE,
J.MIN_SALARY,
J.MAX_SALARY
FROM
[Link] E
JOIN [Link] J ON E.JOB_ID = J.JOB_ID
WHERE
[Link] BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
from [Link] group by MANAGER_ID,DEPARTMENT_ID;
select * from [Link];
select * from [Link];
select * from [Link];
select * from [Link] where email='john@[Link]'
SELECT
EMPLOYEE_ID,
FIRST_NAME,
DEPARTMENT_NAME
FROM
[Link]
INNER JOIN [Link] ON
[Link].DEPARTMENT_ID = [Link].DEPARTMENT_ID;
SELECT
e.EMPLOYEE_ID,
e.FIRST_NAME,
d.department_id,
d.DEPARTMENT_NAME
FROM
[Link] e
INNER JOIN [Link] d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
SELECT
EMPLOYEE_ID,
FIRST_NAME,
department_id,
DEPARTMENT_NAME
FROM
[Link]
INNER JOIN [Link] using(department_id);
select * from [Link] where department_id is null;
-- Subqueries
-- a query within another query
-- outer Query and inner query
-- inner query executes first and the output of inner query will become input for outer
query.
-- select....(select ....)
-- Find out employees who are earning greater than the average salary
-- find avg() salary
-- then find employee details
select EMPLOYEE_ID,FIRST_NAME from [Link] where salary >
(select avg(salary)from [Link]);
-- Fetch all employee details who are working for HR department-- Single row subquery
select EMPLOYEE_ID,FIRST_NAME from [Link] where department_id=
(select DEPARTMENT_ID from [Link] where
DEPARTMENT_NAME='Marketing');
-- Multi Row subquery
select EMPLOYEE_ID,FIRST_NAME from [Link] where department_id IN
(select DEPARTMENT_ID from [Link] where
DEPARTMENT_NAME='Marketing'or department_name='IT');
select * from [Link];
-- ALL, ANY Operator
-- Find employees whose salary is greater than ANY SH_CLERK job_id
select Employee_id, salary from [Link] where job_id='SH_CLERK';
select EMPLOYEE_ID,FIRST_NAME,salary from [Link] where salary > ANY
(select salary from [Link] where job_id='SH_CLERK');-- (10,20,30)
select EMPLOYEE_ID,FIRST_NAME,salary from [Link] where salary > ALL
(select salary from [Link] where job_id='SH_CLERK');-- (10,20,30)
-- Multi column subquery
-- Fetch the employee details having min salary in each department
select EMPLOYEE_ID,first_name,DEPARTMENT_ID,salary from
[Link]
where(department_id,salary)IN
(select DEPARTMENT_ID, min(salary) from [Link] group by department_id);
-- EXISTS Keyword , corelated subquery- outer query and inner query are inter related to
each other
-- Find the employee details who earn more then avg salary of their own department
-- For every row in outer query, inner query executes
SELECT
E.EMPLOYEE_ID,
E.FIRST_NAME,
[Link]
FROM
[Link] E
WHERE
[Link] > (select avg(salary) from [Link] where
department_id=e.department_id
-- Exists keyword just check whether inner query results some results or not.
-- If inner query returns some result, outer query executes otherwise outer query
-- don't execute
-- -- List all departments that have atleast 1 employee
select DEPARTMENT_ID, DEPARTMENT_name
from [Link] d
where exists
(select EMPLOYEE_ID from [Link] e
where e.department_id=d.department_id);
select * from [Link];
-- List departments that have no employee
select DEPARTMENT_ID, DEPARTMENT_name
from [Link] d
where not exists
(select EMPLOYEE_ID from [Link] e
where e.department_id=d.department_id);
-- Sets Operator - UNION, UNION All, INTERSECT, MINUS
-- Used to combine results of 2 or more select statements
-- Each SELECT statement must have - same no of columns, same data types, same
order of columns
-- UNION- combine the results of both queries
select * from [Link];
select * from HR.job_history;
SELECT employee_id, job_id from [Link]
UNION
SELECT employee_id, job_id from HR.JOB_HISTORY;
--UNION All- Keep Duplicates
SELECT employee_id, job_id from [Link]
UNION ALL
SELECT employee_id, job_id from HR.JOB_HISTORY;
-- INTERSECT- Return common rows
SELECT employee_id from [Link]
INTERSECT
SELECT employee_id from HR.JOB_HISTORY;
-- MINUS - Return rows from first table that do not appear in second table
SELECT employee_id from [Link] -- 101,102,103
MINUS
SELECT employee_id from HR.JOB_HISTORY;-- 103
-- no of column has to be same in SET Operator
SELECT employee_id, job_id from [Link]
UNION
SELECT employee_id from HR.JOB_HISTORY;
-- data type has to be same
SELECT employee_id from [Link]
UNION
SELECT department_name from [Link];
-- order by cannot be used with first SELECT statement
SELECT employee_id, job_id from [Link] order by employee_id
UNION
SELECT employee_id, job_id from HR.JOB_HISTORY ;
SELECT employee_id, job_id from [Link]
UNION
SELECT employee_id, job_id from HR.JOB_HISTORY order by employee_id;
-- it does the order by from first SELECT Query only
SELECT employee_id, job_id from [Link]
UNION
SELECT employee_id, job_id from HR.JOB_HISTORY order by 2;
SELECT employee_id as emp_id, job_id from [Link]
UNION
SELECT employee_id, job_id from HR.JOB_HISTORY order by employee_id desc;
-- order by works with alias name if applicable
SELECT employee_id as emp_id, job_id from [Link]
UNION
SELECT employee_id, job_id from HR.JOB_HISTORY order by emp_id desc;
-- DML Statements (Data Manipulation Language)- insert, update, delete
-- create table along with constraints
-- CREATE - DDL Statement (Data DEfinition Language - it works on structure of data -
CREATE,ALTER, DROP)
-- constraints restricts the information while inserting the records
-- Primary key, Foreign Key, Unique Key, Default, CHECK , Composite key
-- Data type defines the type of information you are going to store in a column
-- age - 'a,b,c,d'->number, Hire_date - date - DD-MON-YY
-- int -> number , String -> varchar(), varchar2(10)
-- constraints can be apply on Table level, or on column level
create table dept(
dept_id number(5)primary key,
dept_name varchar2(20)unique not null,
location varchar(20) default 'Not Assigned yet'
create table emp
emp_id number(5) primary key,
emp_name varchar2(25)not null,
email_id VARCHAR2(50)unique ,
contact_no number(10)unique not null,
salary number(6,2)CHECK (salary>0),
hire_date DATE DEFAULT SYSDATE not null,
department_id number(5) references dept(dept_id)
);
-- Table level constraint
create table emp1
emp_id number(5),
emp_name varchar2(25)not null,
email_id VARCHAR2(50),
contact_no number(10)unique not null,
salary number(6,2),
hire_date DATE DEFAULT SYSDATE not null,
department_id number(5) references dept(dept_id),
constraint c1 primary key(emp_id),
constraint c2 unique (email_id),
CONSTRAINT c3 check(salary>0)
);
select * from dept;
insert into dept values(1,'HR','PUNE');
insert into dept values(2,'SALES','HYDERABAD');
insert into dept values(3,'ACCOUNTS','DELHI');
-- partial insertion
insert into dept(dept_id,dept_name)values(4,'MARKETING');
insert into dept(dept_name,dept_id)values('IT',5);
select * from dept;
desc DEPT;
desc emp;
select * from dept;
select * from emp;
insert into emp values(101,'John','John@[Link]',95.89,2456,SYSDATE,1);
insert into emp values(102,'Ram','Ram@[Link]',23,366,SYSDATE,2);
insert into emp(emp_id,emp_name,contact_no)values(104,'Saya',223)
insert into emp(emp_id,emp_name,contact_no,department_id)values(105,'Saya',123,
(select dept_id from dept where dept_name='IT'));
delete from emp where emp_id=101;
select * from emp;
delete emp;-- will remove all rows from table but it can be rollback;
rollback;
select * from emp1;
truncate table emp1;-- will also remove all rows from table and table structure remain
[Link] it cant be rollback.
-- Data Types
-- number(10), number(6,2), char(10),varchar(10),varchar2(10),Date, float,
-- 'Hello',
-- delete - remove a specific record / remove all records
-- truncate - remove all records but table structure remains same .
-- drop - remove table data as well as table structure
drop table emp1;
select * from emp;
select * from dept;
delete from dept where dept_id=2
create table courses(c_id number(3),batch number(5),course_name varchar(50)not
null,
constraint c7 primary key(c_id,batch));
insert into courses values(1,101,'Java');
insert into courses values(1,102,'SQL');
insert into courses values(2,102,'SQL');
select * from courses;
select * from emp;
create table emp_backup as select * from emp;
select * from emp_backup1;
desc emp_backUp;
create table emp_backup1 as select emp_id, emp_name,SALARY from emp ;
-- insert, update, delete , truncate - manipulating the data - DML
-- create, alter, drop - DDL - Data definition Language
-- SELECT, - DQL
-- Alter command - modifying the existing structure
--
select * from emp_backup1;
delete from emp_backUp1 where emp_id=101;
alter table emp_backUp1 add constraint pk1 primary key(emp_id);
-- disable the constraint
alter table emp_backUp1 disable constraint pk1 ;
--- enable
alter table emp_backUp1 enable constraint pk1 ;
-- rename a table
alter table emp_backUp1 rename to e5;
-- SET UNUSED In Oracle
-- mark one or more column as unused instead of dropping them
alter table e5 set UNUSED(contact_no);
select * from e5
alter table e5 drop unused columns;
--
alter table e5 read only;
delete from e5 where emp_id=109;
alter table e5 read write ;
select * from dictionary;
select * from user_objects;
select * from user_tab_columns;
select * from user_constraints;
-- Sequences
-- PK -> inserted the data manually - insert into emp values(101,.......)
-- PK column value shud be automatically generated -
-- A sequence is an object that generate unique numbers automatically.
-- NEXTVAL - initialize the sequence and will keep on increase,
-- CURRVAL - returns the current value of sequence. It can be used only after nextval is
invoked atleast
-- once in session.
-- cache , nocache
select * from emp;
-- synonyms : It gives alternate name to table,views, procedure ..
-- It provides short name to the objects .
-- It also provides security .(access the table without knowing the schema name)
-- public synonym
-- private synonym
-- index - It helps to retreive the data faster
-- SELECT- WHERE, JOINS , ORDER bY
-- Types of Index
-- B-Tree Index - High Distinct value->emp_id
-- Unique Index - contains unique values - email, contact
-- Composite Index - Index on multiple column - emp_id + Prj_id
-- Bitmap Index - Index created with less distinct values ->gender
-- Function based - Index on expressions - upper(first_name)
-- Descending Index - order by ..
-- Oracle automatically creates index on pk and unique key column
select * from emp;
create index i1 on emp(salary);
select * from emp where salary >1000;
create UNIQUE index i2 on emp(hire_date);
drop index i1;
select index_name,index_type,table_name from user_indexes;
-- Transaction
-- A transaction is single logical unit of work that contains one or more SQL statements
-- ATM-
-- It ensures data integrity, consistency ,accuracy
-- ACID Properties
-- A - Atomicity - Either All steps in transaction are performed or none of the steps are
performed.
-- C - Consistency - data should remain in valid state.
-- I - Isolation - Multiple transactions should not impact each other .
-- Bank - 2 users - trying to withdraw amount from Bank
-- D - Durability - Once the data is committed, it should reflect permanently in Db , even
aftre system crash .
-- COMMIT - save the information permanently in Db
-- ROLLBACK- undo the changes made in current transaction
-- SAVEPOINT - create a checkpoints within a transaction . YOu can easily rollback upto
that point .
--1-100 - savepoint a
-- 100-500- savepoint b
-- rollback to a;
-- It automatically auto commits the data- DDL, DML,
-- DDL / DCL command - create , alter, drop - it automatically gets commit
-- DML - insert, update, delete - dont get automatically commit
-- Locks :
2 users
1 user is trying to manipulate the row -update -- lock on that row
2 user is trying to read the row
select * from oehr_employees;
-- Views
--It is a virtual table that fetches the data from original table .
-- It does not store data physically .
-- Provides security and it ensures data abstraction.
create or replace view v1 as select employee_id, first_name,last_name job_id from
oehr_employees;
select * from v1;
insert into v1 values(190,'Ellen','AD_VP');
select * from emp;
desc emp;
-- simple view
create view empvw as select empno, sal from emp;
select * from empvw;
insert into empvw values(1,45000);
select * from emp;
update empvw set sal=20000 where empno=8023;
create or replace view empvw as select empno, sal, job from emp;
select * from empvw;
select * from emp;
-- complex view
create or replace view vw_cmplx as select deptno, avg(sal)as avg_salary from emp
group by deptno;
select * from vw_cmplx;
--Read only view
create or replace view empvw as select empno, sal, job from emp with read only;
insert into empvw values(1,45000,'AD_RE');
-- Force view - want to create a view without existence of base table
create force view vw_fr as select * from test1;
-- check option
create view vw_chk as select * from emp where sal> 5000 with check option;
select * from vw_chk;
insert into vw_chk(empno,ename,sal) values(3,'John',1000);
drop view vw_chk;
select * from user_views;
-----------------------------------------------------------------------
create table dept1(dept_id int primary key, dept_name varchar(54)not null);
insert into dept1 values(1,'HR');
insert into dept1 values(2,'Marketing');
-- on delete cascade - if try to remove parent record, child record also will be deleted
automatically
create table emp1(empid number primary key, emp_name varchar(43)not null,sal
number(10,2),dept_id int,
constraint cfk foreign key(dept_id) references dept1(dept_id) on delete cascade);
insert into emp1 values(1,'John',45000,1);
insert into emp1 values(2,'Sam',55000,2);
delete from dept1 where dept_id=1;
drop table emp1;
select * from dept1;
select * from emp2;
create table emp2(empid number primary key, emp_name varchar(43)not null,sal
number(10,2),dept_id int,
constraint cfk1 foreign key(dept_id) references dept1(dept_id) on delete set null);
insert into emp2 values(1,'John',45000,1);
insert into emp2 values(2,'Sam',55000,2);
VIEWS
CREATE TABLE employees (
employee_id NUMBER(6) NOT NULL,
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL,
phone_number VARCHAR2(20),
hire_date DATE NOT NULL,
job_id VARCHAR2(10) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
CONSTRAINT emp_pk PRIMARY KEY (employee_id)
);
create or replace view v1 as select employee_id, first_name,last_name from
employees;
select * from v1;
desc v1;
insert into v1 values(190,'Ellen','karthick');
INSERT INTO employees VALUES (1001,'John','Smith','JSMITH','9876543210',DATE '2022-
01-15','IT_PROG',75000,0.10,100,60);
create view empvw as select employee_id, salary from employees;
select * from empvw;
insert into empvw(employee_id, salary) values(1,45000);
update empvw set salary=20000 where employee_id=1001;
select * from empvw;
insert into empvw(employee_id, salary) values(1,45000);
create or replace view vw_cmplx as select department_id, avg(salary)as avg_salary
from employees group by department_id;
select * from vw_cmplx;
create or replace view empvw_read as select employee_id, salary, job_id from
employees with read only;
select * from empvw_read;
insert into empvw_read values(1,45000,'AD_RE');
create view vw_chk as select * from employees where salary> 5000 with check option;
select * from vw_chk;
insert into vw_chk(employee_id,first_name,salary) values(3,'Sree',100);
create table dept1(dept_id int primary key, dept_name varchar(54)not null);
insert into dept1 values(1,'HR');
insert into dept1 values(2,'Marketing');
create table emp1(empid number primary key, emp_name varchar(43)not null,sal
number(10,2),dept_id int,
constraint cfk foreign key(dept_id) references dept1(dept_id) on delete cascade);
insert into emp1 values(1,'John',45000,1);
insert into emp1 values(2,'Sam',55000,2);
delete from dept1 where dept_id=1;
select * from dept1;
create table emp2(empid number primary key, emp_name varchar(43)not null,sal
number(10,2),dept_id int,
constraint cfk1 foreign key(dept_id) references dept1(dept_id) on delete set null);
insert into emp2 values(1,'John',45000,1);