SQL Tutorial March 2023
SQL Programming - Sessions
select * from hr.employees;
Select first_name, last_name, email from hr.employees;
Select first_name, last_name, email,salary from hr.employees where salary > 7000;
select first_name || ‘ ‘ ||last_name “FULL NAME”, email “EMAIL ID”,salary from hr.employees where salary > 7000;
Select first_name name, last_name last,salary, DEPARTMENT_ID from hr.employees where salary > 7000 and DEPARTMENT_ID=100;
Select first_name name, last_name last,salary, DEPARTMENT_ID from hr.employees where salary > 7000 or DEPARTMENT_ID=100;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where COMMISSION_PCT IS NOT NULL;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where COMMISSION_PCT <> NULL;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 and last_name =’Tuvault’;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 and first_name like’A%’;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 and first_name like’%t%t%’;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 order by first_name;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 order by first_name desc;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 order by DEPARTMENT_ID, first_name;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100 order by DEPARTMENT_ID, first_name;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees order by COMMISSION_PCT NULLS FIRST;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees order by COMMISSION_PCT DESC
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID <> 100;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID > 100;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID < 100;
Select first_name name, last_name last,salary, DEPARTMENT_ID, COMMISSION_PCT from hr.employees where DEPARTMENT_ID = 100;
select * from hr.employees;
select * from dual;
— Functions that work on rows
select abs(-99), round(45.61) from dual;
select to_number(‘12345.12678′,’99999.99999’) from dual;
select ‘12345.12678’ from dual;
— 12-Jan-2023
select to_date(‘May 15, 2023 5:25 P.M.’,’Month dd, yyyy HH:MI P.M.’) from dual;
select to_char(to_date(’11 January, 2023 5:25 P.M.’,’HH Month, yyyy DD:MI P.M.’), ‘Mon dd yyyy hh:mi’) from dual;
select first_name, last_name, hire_date, to_char(hire_date, ‘Month dd yyyy hh:mi’), salary, to_char(salary,’$99,999.9′) from hr.employees;
— DECODE
select decode(5+3,9,’Correct’,’Incorrect’) from dual;
— AGGREAGATE Functions – that work on columns
select count(*), round(avg(salary)), sum(salary), min(salary), max(salary) ,DEPARTMENT_ID from hr.employees group by DEPARTMENT_ID having avg(salary) > 7000;
Select * from hr.employees;
select * from hr.departments;
— Cartesian
select first_name, last_name, e.DEPARTMENT_ID, DEPARTMENT_NAME from hr.employees e , hr.departments d
select first_name, last_name, e.DEPARTMENT_ID, DEPARTMENT_NAME from hr.employees e join hr.departments d on (e.DEPARTMENT_ID=d.department_ID)
— Right Outer
select first_name, last_name, e.DEPARTMENT_ID, DEPARTMENT_NAME from hr.employees e , hr.departments d where e.DEPARTMENT_ID (+)=d.department_ID
— Left Outer
select first_name, last_name, e.DEPARTMENT_ID, DEPARTMENT_NAME from hr.employees e , hr.departments d where e.DEPARTMENT_ID =d.department_ID (+)
— FULL OUTER
select first_name, last_name, e.DEPARTMENT_ID, DEPARTMENT_NAME from hr.employees e full outer join hr.departments d on (e.DEPARTMENT_ID=d.department_ID)
— Sub query
select * from HR.employees where first_name in (select first_name from hr.employees where salary>6000)
select * from HR.employees where department_ID = (select department_ID from hr.departments where department_name=’Shipping’)
select * from HR.employees e, hr.departments d where e.DEPARTMENT_ID =d.department_ID and d.department_name = ‘Shipping’
