select *

from tab;

select *
from employees;

select *
from departments;

select employee_id, last_name, salary, department_id

from employees;

-- concat columns
select employee_id, first_name ||' is son of '|| last_name || ' take salary = ' || salary as emp_report
from employees;

-- select employee_id, first_name ||' '|| last_name, salary

-- alias

select employee_id, first_name ||' is son of '|| last_name || ' take salary = ' || salary as emp_report
from employees;

select employee_id, first_name ||' is son of '|| last_name || ' take salary = ' || salary as "Emp Report"
-- only alias uses the double qoutation " " …. Other wise we use a single qout
from employees;

-- arithmatic operations + - * /
select employee_id, last_name, salary, salary * 12 as annual_salary
from employees;

select employee_id, last_name, salary, salary * 12 as annual_salary,

(salary + 100 ) * 12 as bonus
from employees;

-- the arithmetic operations execution order is like math >> brackets then * and / then + and - >> * and / what
comes first is excuted first

select employee_id, last_name, salary, salary * 12 as annual_salary,

(salary + 100 ) * 12 as bonus
from employees
order by salary asc;
-- asc is the default

select employee_id, last_name, salary, salary * 12 as annual_salary,

(salary + 100 ) * 12 as bonus
from employees
order by bonus asc;

-- where condition , comparison operators = > < >= <= !=

select *
from employees
where department_id = 30;

select *
from employees
where salary >= 8000;

-- Moulti conditions and or

-- example, show all employees work in dept 30, salary >= 8000

select *
from employees
where department_id = 30
and salary >= 8000;

-- example, show all employees work in dept 30, 60 salary >= 8000

select *
from employees
where (department_id = 30
or department_id = 60)
and salary >= 8000; -- ??????????????????????

-- Where operators in like between is null

-- example, show employees work in dept 30, 60 , 90
-- (in) is a variable of (or)
select *
from employees
where department_id in (30, 60, 90);

-- example, show employees with salary in range 5000 to 9000 included

select *
from employees
where salary between 5000 and 9000;

-- example, show employees with last name = king

select *
from employees
where last_name = 'King'; -- here it is a case sinsitive

-- to solve case sensitivity

select *
from employees
where lower( last_name) = lower ( 'king'); -- here we made all the column small and even the search word is lowered

-- example, show all employees with start with K

-- Like % zero or more letters _only 1 letter
select *
from employees
where last_name like 'K%';

select *
from employees
where lower ( last_name) like lower ('K%');

-- example, show all employees with last name contains s

select *
from employees
where last_name like '%s%';

-- example, show all employees with last name has a character before the last r
select *
from employees
where last_name like '%r_';

-- is null
select *
from employees
where department_id = null; -- XXXXXXX = null de bnst5dmha m3a el update fqt

select *
from employees
where department_id is null;

-- Not in Not between Not like is Not null

-- functions
-- Character functions: upper , lower, length, instr, substr

select employee_id, last_name, upper(last_name), lower(last_name), length (last_name),

instr(last_name, 'i' , 4), substr (last_name, 2,4)
from employees;

-- you can use the upper and lower in select statement and in where condition
-- instr ====> searches for the charecter or a word in the column ==> (colimn_name, letter, no. of letter to begin
with)
-- substr ==> substring === > btqt3 el klma

select employee_id, last_name, email,

substr(email, 1, instr(email, '@') - 1) as user_name,
substr(email, instr(email, '@') + 1) as domain_name
from employees
where employee_id in (104 , 105);

-- ==========================================
-- +++++ day 2 +++++++

-- replace >>>>> replace (column_name, what you want to be replaced , to be replaced by ...) 3 parameters
--- we didn`t update the table we use it in the display only

select *
from employees
where employee_id in (104,105);

select employee_id, first_name, replace (first_name, '_', ' ') as test_replace

from employees
where employee_id in (104,105);

-- update (using replace)

update employees
set first_name = replace (first_name, '_', ' ')
where employee_id in (104, 105);

-- trim function: to remove spaces from begin and end

select *
from employees
where trim(first_name) = 'Bruce Austin';

select *
from employees
where trim(first_name) = trim (lower( 'Bruce Austin'));

select *
from employees
where trim(first_name) = trim ( 'Bruce Austin');

-- displayed in the original table : with the spaces

--- you can use it like this aslo ===> where trim(first_name) = trim('Bruce Austin');
-- note ==> we used it in the display only we didn`t update it in the table yet

-- to display it without the spaces

select first_name, trim(first_name)
from employees
where trim(first_name) = 'Bruce Austin';

-- update using trim

update employees
set first_name = trim(first_name)
where employee_id in (104, 105);

-- Lpad , Rpad functions

-- padding :????????
-- left and right
-- (column_name, total number of digits , what to fill with)
select employee_id, last_name, salary, lpad (salary, 10, '_'), rpad (salary,5 , '*')
from employees;

-- ++++++ 2 Number Functions

-- round , trunc , Mod

-- ## round , trunc functions

-- round: to round the dicemals 15342.785 ==> 15342.79
-- trunc : to remove the decimal degits you want 15342.785 ==> 15342

select employee_id, last_name, salary, round (salary, 2), round (salary, 0), round (salary),
trunc (salary, 2), trunc (salary, 0), trunc (salary)
from employees
where employee_id in (104, 105);

-- Mod function : the remaining of the division operation

-- any thing does not related to tables use : from dual

select 4/2, mod (4, 2)

from dual;
select 11/3, mod (11, 3)
from dual;

select mod (452, 3)

from dual;

-- example: 140 seconds .... how many minutes ? how many remaing seconds?
select 140/60 as minutes, mod (140, 60)as remaining_seconds
from dual;

select trunc (140/60) as minutes, mod (140, 60)as remaining_seconds

from dual;

-- example: 14 months ... how many years? how many remaining months?
select 14/12 as years, mod (14, 12) as remaining_months
from dual;

select trunc(14/12) as years, mod (14, 12) as remaining_months

from dual;

-- Note: it is important to have a refrence to divide by

-- exmple: even numbers - odd numbers

-- use number 2 as reference
select mod (4, 2), mod (11,2)
from dual;

--------------------------------------------------

-- +++++++++++++ Date Functions ++++++ conversion functions ++++++++

-- 1, sysdate: [Date Functions]to get date and time

select sysdate
from dual;
-- ######## VIP#########
-- to modify the date format
-- 2- to_char function [conversion function: convert from date to character ]
-- to control date format [to show the date in a specific format]
-- By that we converted it to a character and know we can not make date functions on it

select to_char (sysdate, 'dd-mm-yyyy'),

to_char (sysdate, 'd dd ddd, w ww, mm Mon Month yy yyyy Year'),
to_char(sysdate, 'dy day , Dy Day'),
to_char (sysdate, 'ddth ddsp ddspth "of" Month, Year')
from dual;

-- Formatting Time
-- he will modify the am to pm if its pm
select to_char (sysdate, 'hh12:mi:ss am'),
to_char (sysdate, 'hh24:mi:ss')
from dual;

-- using to_char on employees table

select employee_id, last_name, to_char(hire_date, 'dd Mon yyyy')
from employees;
-- example: find employee data who is hired on 21 05 1991
-- find means where condition
--- 21-05-1991 : means character \ while sysdate: means date \'
-- and employees hire_date: means date as its data type is date
select *
from employees
where hire_date = '21-05-1991'; -- ERROR : the left side is a date type while the right side is character
-- the solution is to convert the characyter side to date BY USING : to_date function [convert from character to date]

select *
from employees
where hire_date = to_date ('21-09-2005', 'dd-mm-yyyy');

-- example: Khaled Monday : 8-9-1997 ==> char type

-- combination between to_date and to_char function ==> to know the day of that date
select to_char( to_date ('8-9-1997', 'dd-mm-yyyy'), 'Day')
from dual;

select to_char( to_date ('1-1-2000', 'dd-mm-yyyy'), 'Day')

from dual;

-- 8-9-1997 is a character type ==> to convert from character type to date type use:
select to_date ('8-9-1997', 'dd-mm-yyyy')
from dual;

--+++++++++++++ Date Functions

-- 1. sysdate function
-- 2. Months_between Function: a date function to get No. of months between 2 dates

select employee_id, last_name, hire_date,

months_between (sysdate, hire_date),
trunc ( months_between (sysdate, hire_date) / 12) as Years, -- alias is out of the brackets
trunc (mod (months_between (sysdate, hire_date), 12)) as remaining_mounths
from employees;

-- task : show the remaining days [Accurate] ; Note: Do not use 29,30,31, 365 366
to_number( to_char( ,'dd'),'99')

-- 3. Add_months date function: To add months to a date

select sysdate + 3 as add_3_days,
sysdate - 3 as add_3_days,
add_months(sysdate, 1) as add1_month, -- to add months
add_months(sysdate, -3) as less3_month
from dual;

-- 4. last_day ==> date function : To get the last day in current month or for a specific month

select last_day (sysdate)

from dual;

-- to get the first day of the next month add 1

select last_day (sysdate) + 1
from dual;

-- example: 5-2-2024 ===> to get the last day for a specific month you have to add any date in that month

select last_day (to_date ('5-2-2024', 'dd-mm-yyyy'))

from dual;
-- last day of the next month ==> add 1 to the current month and then get the last day
select last_day (add_months(sysdate, 1))
from dual;

-- 5. next_day function: to get the nearst sunday for example from today

select next_day (sysdate, 'SUN')

from dual;

-- to get the nearst Mon from the begin of the next month
select next_day (last_day (sysdate), 'Mon') -- we didn`t add 1 to the last_day in case the beg of the next month
would be Mon
from dual;

---------------------------------------- General Functions : Nvl = coalesce -------------------------------------------

-- Nvl general function : convert from null to a value : ex ==> zero
-- null values does not be included in mathematical operations

select employee_id, last_name, salary, salary + 100 -- if their is any null value it would not be included
from employees;

select employee_id, last_name, salary, nvl(salary, 0) + 100 -- here we converted the null value into zero so it would
be included in the equation
from employees;

--------------------- if conditions ------------- their is functions similar ti if condion in sql :

/* case expression - decode function */

-- 1. case expression:

select employee_id, last_name, salary, job_id,

case job_id when 'AD_VP' then salary + salary *0.1
when 'IT_PROG' then salary + salary *0.2
when 'FI_MGR' then salary + salary *0.3
else salary
end as salary_review

from employees;

-- 2. decode:

select employee_id, last_name, salary, job_id,

decode (job_id, 'AD_VP', salary + salary *0.1,
'IT_PROG', salary + salary * 0.2,
'FI_MGR', salary + salary * 0.3,
salary)

from employees;

------------------------ if Conditions -----------------------------

/* case expression - decode function */
-- 1. case expression
select employee_id, last_name, salary, job_id,
case upper(job_id) when upper('AD_VP') then salary + salary * 0.1
when 'IT_PROG' then salary + salary *0.2
when 'FI_MGR' then salary + salary * 0.3
else salary
end as salary_review
from employees;

-- 2. decode function
select employee_id, last_name, salary, job_id,
decode(job_id, 'AD_VP', salary + salary * 0.1,
'IT_PROG', salary + salary * 0.2,
'FI_MGR', salary + salary * 0.3,
salary) as salary_review
from employees;

-- +++++++++++++++++ day 3-----------------------

-- 6, 7: round \ trunc data functions

select round (sysdate, 'Month'), round(sysdate, 'Year'), -- to round to the nearest big. (Up or down)of the month\
year \\\ up if after june
trunc (sysdate, 'Month'), trunc(sysdate, 'Year') -- to back to the big. of the month\ year
from dual;
-------------------------
--- conversion functions ..... cont.
--- 1. to_char - {convert date to char < to print date in a specific format > \
-- convert Number to char < > to print number in a specific format > 7500 : 7,500.00$

-- 2- to_dATE - { CONVERT From

-- 3- to number { to convert char to number < for any number value ex. 7,500.00$ > 7500

-- to_char [with numbers]

select employee_id, last_name, salary, to_char(salary, '999,999,999,99L'), -- use L if you have only one currency in
the DB if not you can assign a specific currency or don`t write it or concat with the currency column if existed
trim( to_char(salary, '999,999,999,99L') ) -- the number you write means the
format and size
from employees;

select to_number('24,028.00£', '999,999,999.99L') + 500 -- use the same format

from dual;

-- Aggregate functions [sum , max, min, count, avg]

select sum(salary), max(salary), min(salary), count(*) -- the result will be in one row \\ so don`t use a select
statement of a column thaat reults a column with the agregate functions >>> error
from employees
where department_id = 30;

-- example: get sum of salary per each department >>> here we can use a selection of a column that result in many
rows with the aggregate functions as it will result in many rows itself

select department_id, sum(salary) -- you can`t select any column that doesn`t exist in the group by >>> so firstly
think in what you want to group by it
from employees
group by department_id;

select department_id, job_id, sum(salary), count(*), max(salary)

from employees
where department_id is not null
group by department_id, job_id -- here it will group by the department id and in it grouping by job id >>> group
inside group ....... etc
having sum(salary) > 10000 -- conditions on aggregate functions can not be used
inb where condition >>>> used in having only
order by department_id; -- to organize the display
/* The order of excution:
[Link] ,
[Link]
[Link] by,
[Link],
[Link] ,
[Link] by
*/

-- AVG function

select avg(salary), sum(salary)/count(*) -->>>>> results are different >>> why??? >>> because of the null values
that didn`t include in AVG while it is included in the equation
from employees;

select avg( nvl(salary,0) ), sum(salary)/count(*) -->>>>> here we solved the problem as it included the
employees who have null values after converting it to 0
from employees;

select round (avg( nvl(salary,0) ), 2), sum(salary)/count(*) -- >>> round is to round the decimal to the nearest 2
digits
from employees;

-----------------------------#####-----------------
/*
join >>>> used to retrieve data from more than one table so you need to know the FKs
so if the tables needed to be displayed are in 2 tables >>> you have to usee the join
or if you have subquery inside subquery ... etc use the join to enhance the performance

1. inner join
2. outer join
3. self join
*/

-- 1. inner join
-- Between 2 tables

select
from >> 2 tables_to _be_ joined
where fk = pk

select employee_id, last_name, salary, emps.department_id, department_name

from employees emps, departments depts
where emps.department_id = depts.department_id;

-- 3 tables
select employee_id, last_name, salary, employees.department_id, department_name, departments.location_id, city
from employees, departments, locations
where employees.department_id = departments.department_id
and departments.location_id = locations.location_id; -- number of condions for
joining = no. of tables - 1

-- [Link] join
select employee_id, last_name, salary, emps.department_id, department_name
from employees emps left outer join departments depts
on emps.department_id = depts.department_id; -- we can`t put the join condition in the where condion as we used
the keyword outer join in the from statement
--3. self join
select
from employees emps, employees mgrs
where fk = pk;

/*
Emps Mgrs
1 >> 1
M (fk) << 1
then the relation would be one to many and the fk will be in the many side */

select emps.last_name as emp_name, [Link] as emp_salary , mgrs.last_name as mgr_name, [Link] as

mgr_salary
from employees emps, employees mgrs
where emps.manager_id = mgrs.employee_id; -- takecare what column you assign with the table as
mgrs.manager_id means the manager of the manger
fk = pk

select emps.last_name as emp_name, [Link] as emp_salary , mgrs.last_name as mgr_name, [Link] as

mgr_salary
from employees emps outer join employees mgrs
on emps.manager_id = mgrs.employee_id;

------------------------------------------------------------------
------------------------------------------------------------------
---------------Sub query ______________________________
--## single row subquery >>> = > < <= >=
-- example: get all employees data works with employee (115) in his department
-- there is a missing information we do not have and we need wnd it is not in the same table so we need to
acquire it firstly
-- the sunquery must give me one result

select *
from employees
where department_id = (select department_id from employees where employee_id = 115)
and employee_id != 115; -- to exclude the employee no. 115 as we want the employees who work with him only

-- example: get the employee data with the max salary within all employees

select *
from employees
where salary = (select max (salary) from employees); -- we used the sub query as we can not use the aggregate
functions in the where condition

-- example: get all departments which have employees

select *
from departments
where department_id = (select department_id from employees); -- ERROR because the ssub query gives me more
than one value >> multi rows

--### Multi ROWS subquery In ALL ANY not in

 select *
from departments
where department_id in (select department_id from employees); -- DONE IT will run

-- example: get all departments which have NO employees [When use not in : take care of Nulls in sub query]

select *
from departments
where department_id not in (select department_id from employees); -- NOT correct

-- >>> When use not in : take care of Nulls in sub query as it will give me the null only

select *
from departments
where department_id not in (select department_id from employees where department_id is not null); -- or use NVL

select *
from departments
where department_id not in (select Nvl (department_id, 0) from employees );

-- Any & All

-- example: get employees datawith salary > ALL\Any salaries of employees work in dept 30
-- > any means greater than the minimum
-- > all means greater than the highest

select *
from employees
where salary > All (select salary from employees where department_id = 30); -- greater than the highest

select *
from employees
where salary > Any (select salary from employees where department_id = 30); -- greater than the minimum

-- > All > Max

-- > ANY > Min

--==========================================
--=================DAy 4++++++++++++++++++++

insert into table_name (column_namse, ........)

insert into departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)

values
(320, 'Erp Dept', 109, 14); -- integrity constraint : FK of location_id is wrong

insert into departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)

values
(40 , 'Erp Dept', 109, 14); --unique constraint (HR.DEPT_ID_PK) violated *** pk is duplicated

insert into departments (DEPARTMENT_ID, MANAGER_ID, LOCATION_ID)

values
(40, 109, 14); -- ERROR : cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME") ***
department id cannot be null
-- FK can be null if the partcipation is may

insert into departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)

values
(320, 'Erp Dept', 109, 1400);

insert into employees

( EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID )
values
( 207, 'Yahia', 'Momtaz', '[Link]@[Link]', '01274077377', to_date('24-4-2022', 'dd-mm-yyyy'),
'IT_PROG', 9000, null, 103, 320 );

update employees
set salary = 11000
where employee_id = 207;

--ddl
alter table employees
modify email varchar2(100);

update employees
set email = employee_id ||'.'||first_name||'.'||last_name||'@gmai;.com',
salary = (select salary + 1000 from employees where employee_id = 112),
manager_id = (select manager_id from employees where employee_id = 107)
where employee_id = 207;

------------------ delete
-- children are rmoved firstly then the parent

delete from employees

where employee_id = 207;

delete from departments

where department_id = 320;

-- YOU CAN USE SUBQUERIES IN THE UPDATE AND INSERT STATEMENTS

-- BUT YOU CAN NOT USE JOINS ONLY USED IN SELECT STATEMENT

--========================================
---------======== DDLS: Data Definition Language >>> Auto commit -----
--1. create
-- Example:
-- 1- depts tables [department_id number 4 pk \ department_name varchar2(100) not null]
-- 2- emps table [employee_id number 4 pk \ employee_name varchar2 100 \ employee_salary number 8,2
check sal > 500 \ employee_email varchar2 100 unique department_id number 4 fk]

/*onstraints in Databases:
primary key
foreign key
noy null
check
5.
6.
*/

create table depts

 ( department_id number (4) constraint dep_id_pk primary key, -- we name the constraint so if an error
happens it will appear in the error massage
department_name varchar2(100) constraint dept_nme_nn not null); -- the name of the constraibnt must be
unique

create table emps

(employee_id number(4) constraint emp_id__pk primary key,
employee_name varchar2(100),
employee_salary number(8,2) constraint emp_sal_chk check(employee_salary > 500), -- salary (8,1) means 4
numbers and 2 decimals
employee_email varchar2(100) constraint emp_email_u unique,
department_id number(4) constraint dept_id_fk references depts(department_id)); -- fk we have to refrencr the
parent table and the pk column of it

drop table depts;

drop table emps;

--------------------------------

-- create table : using sub query : to make a backup from existing table[with data or eithout data]
-- No constraints passed ; not null
-- with data

create table employees2

as
select * from employees;

-- with no data
create table employees3
as
select * from employees
where department_id = -1; -- to give us the strucure of the table only without data >> we put a condition that
will never be met

-- fill employees 3 with insert (insert using sub query)

insert into employees3
select * from employees where department_id = 60; -- the columns in both tables must be the same
--------------------------------
-- 2. rename DDL command
rename table_name to new_name
rename employees3 to employees_backup;
------------------------------------
-- 3. Truncate DDL command : to empty the table : remove all records without removing the strucure
-- like delete without where condition
-- auto commit >> so its faster than delete and you can not get the data back

truncate table employees2;

--------------------------------

-- 4. comment : DDL command - note on a table - noteon a column

-- used to be shown to the user of the database to give him a comment
comment on table emps is 'This table is used to store all company employees daat';
comment on column emps.employee_email is 'This is the email of the employee';

----------------------------
-- 5. Alter DDL Command

-- add new column

alter table emps
add mobile varchar2(11);
 alter table emps
add retired number(1) default 0;

-- modify data type of colunm

alter table emps
modify mobile varchar2(25);

-- rename column name

alter table emps
rename column mobile to employee_mobile;

-- add constraint on column

alter table emps
add constraint emp_mob_u unique(employee_mobile); -- we used the new name of the column we renamed
-- drop constraint from column
alter table emps
drop constraint emp_mob_u;

-- consraints can not be modified we drop it and recreate it

-- drop column
alter table emps
drop column retired; -- remember it is auto commit (DDL)

------------------------------------------------
--6. Drop DDL Command: to remove all dta and structure of a table >>> auto commit
drop table employees_backup;

-- Flshback : To return back Table dropped >> can be used for a specific time (only for 4 hours)
Flashback table employees_backup to before drop;

drop table employees_backup purge; >>> it can not be flashed back now

--------------------------------------------------------------------
------------------------- Other schema Objects ---------------
-- 1. DB sequence >> to auto increment

create sequence emps_seq

start with 4
increment by 1;

insert into emps

(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_SALARY, EMPLOYEE_EMAIL, DEPARTMENT_ID,
EMPLOYEE_MOBILE)
values
(emps_seq.nextval, 'Hesham Ashraf', 5000, 'ashraf@[Link]', 20, '1000000'); -- the usage with insert

-- identity : the same (auto increment) but its in the version 12c
select emps_seq.nextval
from dual; -- here we made a equence that didn`t saved in the table but when we use it again in the table it will
continue on these number

-----------------------------------------------------
-------------------- 2. ddDB Views ---------------
-- stored select statement in the database as an object to be used more than once easily

create view emps_view

as
select employee_id, last_name, salary, job_id, employees.department_id, department_name
from employees, departments
where employees.department_id = departments.department_id;
--------------------------
select *
from emps_view;

select employee_id, last_name, department_name

from emps_view
where department_id = 60;
--------------------------
---- to modify the view

create or replace view emps_view

as
select employee_id, last_name, salary, job_id, employees.department_id, department_name, hire_date
from employees, departments
where employees.department_id = departments.department_id;

-----------------------\
-- if you update a view >> the table will be updated

--------------------------------
-- View Types:
-- 1. Simple view [pfrom 1 table - no functions - no group - allow DMLs]
-- 2. Complex view [from more than one table - may have functions - may have group by -no DMLs allowed]

create view simple_emps_view

as select * from emps;

select *
from simple_emps_view;

update simple_emps_view
set employee_salary = employee_salary + 1000; -- Now we udated the table itself

--------------------- With check option ----- with read only

create view simple_vu_60

as select * from employees
where department_id = 60;

select *
from simple_vu_60;

update simple_vu_60
set department_id = 30; -- here the view will retrieve no data as we damaged the condition tyhe view is based on
>> so we will use the check option

create or replace view simple_vu_60

as select * from employees
where department_id = 60
with check option;

update simple_vu_60
set department_id = 30; -- ERROR , here it will not work

--------------- Read only -------------

create or replace view simple_vu_60
as select * from employees
where department_id = 60
with read only; -- Cannot Make DDLs
update simple_vu_60
set department_id = 30; -- ERROR, you cannot perform a DML operation on a read-only view

------------------------------------------------------------------------------------------------------------------------
--======================= DAy 5=======================================
-- DB index:
-- stored object on a column
-- to speed up retrival of data
-- and to speed up join

-- when wew search >> it goes record by record >> aaaaaall recoerds >> this is a huge headache
-- so the solution is to use the indexes >> it maps to the the result of the column you indexed >> pointing to each
record in the column you indexed
-- the index is updated automatically when there is a change in the column indexed >> so you have to choose the
column that is not updated regularly
-- the index is used by the dbms in the retrieval >> you just create it

create index f_name_index

on employees(first_name);

-- automatic index: PK columns \ unique columns

Drop index f_name_index;

---------------------------------------------------------------------------------------------------------------------------------
-- 5. synonym db object : alias for a db object
-- if the table name is too large for example and you need to make it shorter for easy retrieve but without renaming it
-- here we can call the table by bothits original name and the synonym
-- you can say it is a permenant alias not for one select statement only
-- you can use them both the oorigianl and the synonym for all
create synonym locy for locations;
select * from locy;
select * from locations;

create public synonym pub_locy for [Link]; -- hr is the name of the schema
-- if you have many users and this table is not one of their tables now we gave them access to this table
-- public synonym is for retrieval onlyyyyyyyy
-- must be ran by admin >> by sys >>>> that is why we specified the schema
---------------------------------------------------------------------------------------------------------------------------------------
--
=======================================================================
===
-- we need to know data about our data

-- ## data dictionary : show all db metadata (metadata : informations about my db)

-- user_ \ All_tables \ DBA_ \ v$_
select * from user_tables; -- all tables that exist only in the user
select * from user_tab_columns; -- all columns that exist in all tables only in the user
select * from user_tab_columns where table_name = 'BRANCHES';
-- the usage >>>

select * from all_tables; -- all tables that exist in the schema in all users
select * from user_tables; -- used by the dba all tables that exist even in the sys
select * from v$_tables; -- retrieve the data related to the performance >> to detect a problem for example

select * from user_views;

select * from user_sequences;

select * from user_constraints;

select * from user_synonyms;

-- for tables
select * from user_indexes; -- for all tables
select * from user_indexes where table_name = 'EMPLOYEES'; -- name is capital

-- for columns
select * from user_ind_columns; -->>> indexes on columns
select * from user_cons_columns; -->>> constraints on columns

select * from user_objects;

select * from dictionary;

select * from dictionary where upper(comments) like upper ('%COMMENT%');

select * from user_col_COMMENTS where table_name = 'EMPS';

----------------------------------------------------------------------------------------------------------------------
--=================================================================

-- 1. system priveledges >> create - alter - drop >> (with admin option) >> DDL
-- 2. object priveledge >> select -insert - update - delete >> (with grant option) >> DML

--=====
-- Roles: Group of priveledges
-- steps:
-- 1. create role.
--2. grant priveleges to role.
-- 3. grant role to users.
--================================================================

-- plsql block

declare -- declare section

-- the size must be equal or greater than the size of in the db

-- semicolon at each line

begin
-- begin section
-- variables >> columns in the select means 4 variables

end;

set serveroutput on
declare
v_last_name varchar2(100);
v_salary varchar2(100);
v_hire_date date;
v_department_id number(4);

begin
select last_name, salary, hire_date, department_id
into v_last_name, v_salary, v_hire_date, v_department_id
from employees
where employee_id = 107;
DBMS_OUTPUT.PUT_LINE('name is '||v_last_name|| ', take salary = '||v_salary);
DBMS_OUTPUT.PUT_LINE('hiredate = '||v_hire_date|| ', work in dept = '||v_department_id);
end;

select * from employees where employee_id = 107;

set serveroutput on
declare
v_last_name varchar2(100);
v_salary number(8, 2);
v_hire_date date;
v_department_id number(4);
v_annual_salary number (10, 2);
v_upper_name varchar2(100);
v_years number(2);
v_department_name varchar2 (100);

begin
select last_name, salary, hire_date, department_id
into v_last_name, v_salary, v_hire_date, v_department_id
from employees
where employee_id = 107;

v_annual_salary := v_salary * 12; -- plsql statement \ assign

v_upper_name :=upper(v_last_name);
v_years := months_between(sysdate, v_hire_date)/12;

DBMS_OUTPUT.PUT_LINE('name is '||v_last_name|| ', take salary = '||v_salary);

DBMS_OUTPUT.PUT_LINE('hiredate = '||v_hire_date|| ', work in dept = '||v_department_id);
DBMS_OUTPUT.PUT_LINE('upper name = '||v_upper_name|| ', annual salary = '||v_annual_salary);
DBMS_OUTPUT.PUT_LINE('Years hired = ' || v_years);

-- retrieve department name of emp 107

select department_name
into v_department_name
from departments
where department_id = v_department_id;
-- we retrieved this variable before in the previous select ..... we use this instead of join
dbms_output.put_line('Dept name = '||v_department_name);

-- update employee last name with his upper value'

update employees
set last_name = v_upper_name
where employee_id = 107;
end;

select * from employees where employee_id = 107;

--====================================
--===========if========================

declare
v_salary number(8, 2);
v_tax number(3);
v_net_salary number(10, 2);
begin
 select salary
into v_salary
from employees
where employee_id = 107;

if v_salary < 5000 then

v_tax :=0;
elsif v_salary < 10000 then
v_tax := 10;
elsif v_salary < 15000 then
v_tax := 15;
else
v_tax := 20;
end if;

v_net_salary := v_salary - v_salary * v_salary / 100;

dbms_output.put_line('Net salary = '||v_net_salary);
end;

--
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++
--
=======================================================================
======
--------------------------------------------------------------------------------------------------------------------------------------------
-- DAy 6---

--1. For LOOP

set serveroutput on
declare

begin

for i in 1..10 loop

dbms_output.put_line('Welcome'||i); -- i >> counter from 1 to 10 >>> the loop will be repeated 10 times
\\\ i >> built in decleration
end loop;

dbms_output.put_line('Cpuntinue or End the program');

end;

set serveroutput on
declare
v_last_name varchar2(100);
v_salary number(8, 2);
v_department_id number(4);
v_annual_salary number (10, 2);

begin

for i in 100..120 loop

select last_name, salary, department_id
into v_last_name, v_salary, v_department_id
from employees
where employee_id = i;
 v_annual_salary := v_salary * 12;

dbms_output.put_line('EMP id = '||i||', last name = '||v_last_name|| ' , salary = '||v_salary||' , dept id =

'||v_department_id);
end loop;

dbms_output.put_line('Cpuntinue or End the program');

end;

--
=======================================================================
===========================================

--2. Basic Loop

set serveroutput on >>> --could be written one time per session >> in any tap
-- if you got the errpr " the buffer overflow limit" >>use this "set serveroutput on size 1000000" >> the highest limit
1000000

declare
i number(3) := 100;
v_last_name varchar2(100);
v_salary number(8, 2);
v_department_id number(4);
begin

Loop
select last_name, salary, department_id
into v_last_name, v_salary, v_department_id
from employees
where employee_id = i;

dbms_output.put_line('EMP id = '||i||', last name = '||v_last_name|| ' , salary = '||v_salary||' , dept id =

'||v_department_id);
-- we have to assign the increment and the end of the loop mannually
-- the same result as if loop but here we assign everything mannnually
-- if you know the number of repeatings use thr if loop
-- if you don`t know the number of repeatings and you will end the loop based on a condition >> use the basic
loop
i := i +1;
--increment >>> to add 1 to the counter >> without it will print 120 for infinity
if i > 120 then
-- to end the loop
exit;
end if;
End Loop;

end;

------------------------------------------------------------------------------------------------------------------------------------------------
----------------------
-- 3. While Loop

declare
i number(3) := 100;
v_last_name varchar2(100);
v_salary number(8, 2);
v_department_id number(4);
begin

-- While condition_to_continue Loop -- as long as the condition is met >> the loop continues
While i < 121 Loop
select last_name, salary, department_id
 into v_last_name, v_salary, v_department_id
from employees
where employee_id = i;

dbms_output.put_line('EMP id = '||i||', last name = '||v_last_name|| ' , salary = '||v_salary||' , dept id =

'||v_department_id);

i := i + 1;
end Loop;

end;

-- the size of the variables >> must be based on the db tables >> the right way is to go to the schema and look for
the sizes
-- you can make the size be the same of the column and table by using this in the declaration section: by using the
percentage type >>"variable table_name.column_name %tpe;"

declare
i number(3) := 100;
v_last_nameemployees.last_name%type;
v_salary [Link]%type;
v_department_id employees.department_id&type;
begin

------------------------------------------------------------------------------------------------------------------------------------------------
---
-- 4. plsql records

declare
v_emp_record employees%rowtype;
v_annual_salary number(10, 2);
v_years number(2);

begin

select * -- we can use * here as we can store all the columns record in one variable so we don`t need to specify
the name of each column and then assign each in a specific variable
into v_emp_record -- one variable that stores a result of an entire row rather than one value
-- while the scalar variable stores a value with no internal components.
from employees
where employee_id = 107;

v_annual_salary := v_emp_record.salary * 12;

v_years := months_between(sysdate, v_emp_record.hire_date) / 12;

-- dbms_output.put_line(v_emp_record); -- ERROR \\ you have to erite the cell (column name) you need to print
dbms_output.put_line(v_emp_record.employee_id||' , last name= '||v_emp_record.last_name||' , salary
'||v_emp_record.salary);
dbms_output.put_line(' Annual salary= '||v_annual_salary||' , years hired '||v_years);

end;

-------------------------------------------------------------------------------------------------------------------------------------
-- 5. plsql record with loop
declare
v_emp_record employees%rowtype;
v_annual_salary number(10, 2);
v_years number(2);
begin
for i in 100..120 loop -- here he will print for employees from 100 to 120
select *
into v_emp_record
 from employees
where employee_id = i;

v_annual_salary := v_emp_record.salary * 12;

v_years := months_between(sysdate, v_emp_record.hire_date) / 12;
dbms_output.put_line(v_emp_record.employee_id||', last name = '||v_emp_record.last_name||', salary = '||
v_emp_record.salary);
dbms_output.put_line('Annual salary = '||v_annual_salary||', years hired = '||v_years);
end loop;

end;

------------------------------------------------------------------------------------------------------------------------------------------------
-- 6. plsql cursor

set serveroutput on
declare
cursor emp_cursor is
select * from employees
where department_id = 60
and salary is not null
order by salary desc;

begin
-- Loop over cursor >> we use the for loop >> but not the original one >> for loop designed for the cursir >>
called cursor for loop
for v_emp_record in emp_cursor loop -- the record is declared by default >> built in declaration
dbms_output.put_line(v_emp_record.employee_id||', last name = '||v_emp_record.last_name||' ,salary =
'||v_emp_record.salary||' , dept id = '||v_emp_record.department_id);

end loop; -- the loop ill end when the cursor will be empty >> built in condition

-- release from memory [curor \ record ...]

end;
-----------------------------------------------------------------------------------------------------------
--7. plsql cursor Examples

set serveroutput on
declare
cursor emp_cursor is
select *from employees
where department_id = 30;
v_sal_text varchar2(50);
v_years number(2);
v_hire_text varchar2(50);

begin
for v_emp_record in emp_cursor loop
if v_emp_record.salary >= 10000 then
v_sal_text := 'High salary';
else
v_sal_text := 'Low salary';
end if;

v_years := months_between(sysdate, v_emp_record.hire_date)/12;

if v_years >= 15 then
v_hire_text := 'Old Employee';
else
v_hire_text := 'Young Employee';
end if;
 dbms_output.put_line(v_emp_record.employee_id||', last name = '||v_emp_record.last_name||' ,salary =
'||v_emp_record.salary||' , hire date : '||v_emp_record.hire_date||' , dept id = '||v_emp_record.department_id|| ' ,
salary check : '||v_sal_text||' Employee type : '||v_hire_text);
end loop;

end;

/* ++++++++++++++++++++++ the syntax of the session +++++++++++++++++++++++++++

exercise :-
___
print data for all employees works in dept 30
- employee_id | last_name | salary | hire_date | dept id | 'High Salary' | 'old employee'

High / Low Salary

High : Salary >= 10000
Low : Salary < 10000
________
Old / Young employee
Old : no of working years >= 15 years
Young : no of working years < 15 years

Answer :

declare
cursor emp_cursor is
select * from employees
where department_id = 30;
v_sal_text varchar2(50); v_years number(2); v_hire_text varchar2(50);
begin
for v_emp_record in emp_cursor loop
if v_emp_record.salary >= 10000 then
v_sal_text := 'High Salary';
else
v_sal_text := 'Low Salary';
end if;
v_years := months_between(sysdate, v_emp_record.hire_date)/12;
if v_years >= 15 then
v_hire_text := 'Old Employee';
else
v_hire_text := 'Young Employee';
end if;

dbms_output.put_line(v_emp_record.employee_id||', last name = '||v_emp_record.last_name||', salary

= '||
v_emp_record.salary||', hire date : '||v_emp_record.hire_date||', dept id = '||v_emp_record.department_id||
', Salary Check : '||v_sal_text||' Employee type : '||v_hire_text);
end loop;
end;
*/
------------------------------------------------------------------------------------------------------------------------------------------------
--
-- 8. cursor with update

-alter table employees -- >>> This is a nSQL statement to make a new column called retired that
we will use in the example
add retired varchar2 (1) default 'N';

-- Program loop for each employee : check for his hired years >= 15 : make retired : Y
select * from employees;
set serveroutput on
declare
cursor emp_cursor is
select * from employees;
v_years number(2);

begin
for v_emp_record in emp_cursor loop
v_years := months_between (sysdate, v_emp_record.hire_date)/12;

if v_years >= 20 then

update employees
set retired = 'Y'
where employee_id = v_emp_record.employee_id; -- Don`t forget this condition >> otherwise
aaaaaaaaaaaaall records will be updated
end if;
end loop;
end;
select * from employees;

/* ====================== SESSION syntax=========================

exercise :-
___
print data for all employees works in dept 30
- employee_id | last_name | salary | hire_date | dept id | 'High Salary' | 'old employee'

High / Low Salary

High : Salary >= 10000
Low : Salary < 10000
________
Old / Young employee
Old : no of working years >= 15 years
Young : no of working years < 15 years

Yahia Momtaz to Everyone 13:22

++++++++++++++
--alter table employees
-- add retired varchar2(1) default 'N';
--- program loop for each employee : check for his hired years >= 15 : make retired : Y
select * from employees;
declare
cursor emp_cursor is
select * from employees;
v_years number(2);
begin
for v_emp_record in emp_cursor loop
v_years := months_between(sysdate, v_emp_record.hire_date) / 12;
if v_years >= 15 then
update employees
set retired = 'Y'
where employee_id = v_emp_record.employee_id;
end if;
end loop;
end;
select * from employees;
*/
----------------------------------------------------------------------------------------------------------------------------
--======================================================================
-- Day6
-- 1. predefined exceptions
-- Exceptions >> ERRORS : 3 types
-- a. Syntax Error
-- b. Logical Error >> The worst
-- c. Runtime Erro9r : Exceptions [ Handle Exceptions ]

-- 1. Predefined Exceptions >> has a code number and has a name

-- appear during the testing or during the usage of the program
set serveroutput on
declare
v_last_name varchar2(100);
v_salary number(8,2);

begin
v_salary := 5000;
v_salary := v_salary / 0; -- error 3 >> dividing by 0
v_salary := 'ahmed'; -- error 2 >> wrong value type

select last_name, salary

into v_last_name, v_salary
from employees
where employee_id = 13; -- error 1 >> no data found
dbms_output.put_line('name = '||v_last_name||' , salary = '||v_salary);

Exception -- Exception section >> to handle the exceptions || -- the first error only will appear as the code will stop
where ever it finds an error and not go further
when no_data_found then
rollback; -- very important to protect the database from any mistakes
dbms_output.put_line('Please enter a valid employy id');

when value_error then -- even if the size is not right it will give me this error
rollback;
dbms_output.put_line('Salary should be only numbers');

when others then

/*
when others then;
-- Log Error [store error in a table]
insert into err_table
(table columns, err_date)
values(sqlcode,, sqlerrm); -- built in functions
*/
rollback;
dbms_output.put_line('Please contact administrator');

/*
when others then;
-- Log Error [store error in a table]
insert into err_table
(table columns, err_date)
values(sqlcode,, sqlerrm); -- built in functions

null; -- nothing will appear to the user

*/
end;

------------------------------------------------------------------------------------------------------------------------------
--2. non-predefined exceptions

--Non-predefined exception: has a number and don`t have a name

declare
-- declaring the exception
insert_except exception; -- naming the error
 pragma exception_init(insert_except, -01400); -- connecting the name of the error and the error number

begin
insert into departments
(department_id, department_name)
values
(401, null);
exception
when insert_except then
dbms_output.put_line('dept name cannot be empty');

end;
----------------------------------------------------------------------------------------------------------------------------------------------
-- 3. user define exceptions

-- user defined exceptions

declare

begin
update employees
set salary = salary + 1000
where department_id = 3; -- this will not give me error >> in the update and delete statements

-- to chech if the last DML is executed or not

if sql%notfound then -- sql%notfound : Implicit cursor
raise_application_error(-20001, 'The last update is not executed correctly : so i will raise exception and stop
program flow'); -- the number must be greater than 20000 as the numbers below are reserved
end if;

dbms_output.put_line('program continue');

end;

---------------------------------------------------------------------------------------------------------------------------------------
-- 4.

-- procedure : stored plsql in the db >> like the view in the sql [stored select statement] >> stored and we call it to
execution multiple times without needing to write it again >>> DMLs
-- function : stord function that we can call multiple times >> only a function without DDMLs

-- Example: program to update employee salary by a bonus [ procedure >> DMLs >> anonymous block

declare -- put any value in a variable and declare it

v_emp_id number (4) := 107;
v_bonus number(8, 2) := 2000;

begin
update employees
set salary = salary + v_bonus
where employee_id = v_emp_id;

end;

---------------------------------------------------------------------------------------------------------------------------------
-- 5. Procedure update_salary

-- procedure name: update salary [ take 2 parameters number] : update salary of employee with a bonus
create or replace procedure update_salary(v_emp_id number, v_bonus number) -- 2 parameters >> -- every time
we must get 2 values from the user -- don`t declare the size
is
begin

update employees
set salary = salary + v_bonus
where employee_id = v_emp_id;

end;
-----------------------------------------------------------------------------------------------------------------------------------------

--6. call procedure

select employee_id, salary from employees;

declare

begin
update_salary(105, 2000); -- calling the procedure
update_salary(107, 4000);
update_salary(114, 5000);

end;
select employee_id, salary from employees;

select * from employees where employee_id in (105, 107, 114);

-- Example: calling procedure inside a cursor to update all employees

declare
cursor emp_cursor is
select * from employees;
begin
for v_emp_record in emp_cursor loop
update_salary(v_emp_record.employee_id, 2000);
end loop;

end;

--
=======================================================================
=
--7. Anonymous for functions
-- 8. Functions
-- 8. call for functions

-- anonymous block to calculate tax on salary based on employee id, tax %

declare
v_emp_id number(4) :=107;
v_tax_perc number(4) := 10;
v_salary number(8,2);
v_tax_value number(8, 2);

begin
select salary
into v_salary
from employees
where employee_id = v_emp_id;

v_tax_value := v_salary * v_tax_perc / 100;

 dbms_output.put_line('Tax value = '||v_tax_value);
end;

-- function calc_tax to calculate tax on salary based on employee id, tax %

create or replace function calc_tax( v_emp_id number, v_tax_perc number)

return number -- the data type of the value that will be returned -- do not type semicolon
is
v_salary number(8,2);
v_tax_value number(8, 2);

begin
select salary
into v_salary
from employees
where employee_id = v_emp_id;

v_tax_value := v_salary * v_tax_perc / 100;

-- dbms_output.put_line('Tax value = '||v_tax_value); >>> wwwwwwwwwwwRONG >> in the function we do
not print; we return the result
return v_tax_value; -- this return the result when calling the function
end;
show errors -- used with procedures and functions to show the errors

-- calling the function

declare

v_result number (10, 2

begin

v_tax_value := calc_tax(107, 10); -- the value of the function must must must be stored in a variable
dbms_output.put_line('tax value = '||v_result);

end;

-- functions return only one value

-- One of the main advantages of functions is that it could be called from sql [ almost used in reports] >> by select
statement

select employee_id, last_name, salary, calc_tax(employee_id, 10)

from employees;

--
=======================================================================
====================