Select department_id,location_id from departments;
SELECT last_name,salary
FROm employees
WHERE salary BETWEEN 12000 AND 15000;
SELECT Last_name,salary ,HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '01-JAN-1999' AND '01-JAN-2000';
SELECT LAst_name,Salary,COmmission_PCT
FROm employees
WHERE last_name BETWEEN'King' AND 'Smith'
AND COmmission_PCT IS NOT NUll;
-- USE Of IN ,LIKE and IS
SELECT Last_name,salary ,Hire_date,COmmission_pct
FROm employees
WHERE Salary IN(13500,9000,7000,8000,8400)
AND COMMISSION_PCT IS NOT NUll;
SELECT Last_name,salary ,Hire_date,COmmission_pct,HIRE_DATE
FROm employees
WHERE Salary NOT IN(13500,9000,7000,8000,8400)
AND COMMISSION_PCT IS NOT NUll
AND Hire_DATE BETWEEN '01-JAN-1990' AND '1-JAN-2001';
-- Like Operator\
SELECT FIrst_name,salary
FROM employees
WHERE LOWER(first_name) LIKE 'st%';
SELECT LAst_name,salary
FROm employees
WHERE last_name LIKE '__t%';
SELECT LAst_name,salary
FROm employees
WHERE last_name LIKE '%t_'
AND LoWER(last_name) LiKE '_m%';
--Using Of IS operator
SELECt Last_name ,salary ,Commission_pCT,Manager_id
FROm Employees
WHERE /*Manager_id*/ COMMISSION_PCT Is NOT Null;
-- Using of AND ,OR And NOT Logical Operator
SELECT Last_Name,salary ,Commission_pct ,manager_id,Job_id
FROm employees
WHERE salary Between 1000 ANd 15000
ANd Lower(Job_id) LIKE '%man'
AND Commission_pct Is NoT Null
OR manager_id =101;
SELECT Last_name,Job_id
FROm employees
WHERE Job_id NOT IN ('IT_PROG','ST_CLERK','SA_REP','SH_CLERK','ST_MAN','SA_MAN');
SELECT Last_name,JOB_id
FROm employees
WHERE Job_Id='SA_REP'
OR JOB_ID='AD_PRES'
AND salary >15000;
SELECT Last_name,JOB_id
FROm employees
WHERE (Job_Id='SA_REP'
OR JOB_ID='AD_PRES')
AND salary >15000;
SELECT Last_name,JOB_id,salary
FROm employees
WHERE (Job_Id='SA_REP'
OR JOB_ID='AD_PRES');
SELECT Last_Name ,salary ,HIRE_DATE
FROm employees
ORDER By salary DESC;
SELECT Last_Name ,salary ,HIRE_DATE
FROm employees
ORDER By salary ,HIRE_DaTE;
SELECt FIRST_NAME|| ' ' || LAsT_NAME NAME ,SALARY tk
FROM employees
ORDER By tk;
SELECT SYSDATE
FROm DUAL;
SELECT Employee_id,Last_name,salary ,Job_id
FRom employees
WHere job_id LiKE '%ST%';
SELECT employee_id,Last_Name,job_id,department_id,Hire_date,salary
From employees
Order By department_id,salary DESC;
-- Using Of Ampersand Substitution Variable
SELECT Employee_id,last_name,department_id ,salary
FROm Employees
Where employee_id = &employee_num;
SELECT Last_name,salary ,salary*12 Anual_salary
FROM &Tab_name_here
Where &Condition_here = &Condition_Value_here;
SELECT &first_cul_name,&second_CUL_name,&third_cul_name
FROm &Table_name_here
Where &condition_here = &condition_value_here
Order By &order_by_value;
SELECT last_name,department_id,job_id
FROM employees
Where lower(job_id)= '&job_id_Value_here';
/*SELECT employee_id,last_name,job_id,&&colum_name
From employees
Where &colum_name = &value
Order By &colum_name;*/
SELECT UPPER(last_name|| ' ' ||First_name)"Full Name",salary,LOWER(job_id)Job,INITCAp(email)Mail_id
FROM Employees;
DESC Employees;
SELECT Last_name,salary,job_id
FROm employees
WHERE LOWER(last_name)='smith';
--Using Of Character Manipulation Function
SELECT CONCAT(First_name,Last_name),salary
FROm employees;
SELECT last_name,SUBSTR(last_name ,1,3),salary
From employees;
SELECT LOWER(INSTR(last_name,'s'))fName,salary
FROM employees
WHERE LAST_NAME iS NOt NUll;
SELECt Last_name, LENGTH(Last_name)A,LENGTH(First_name)B,salary
FROm employees;
-- Using of LPAD and RPAD
SELECT last_name,LPAD(salary,10,'.')
FROm employees;
SELECT Rpad(last_name,10,'.'),salary
FROm employees;
-- Using Of Number Function ROUND,TRUNCT and MOD
SELECT last_name,salary,commission_pct,ROUND(salary/Commission_pct,2),(salary/Commission_pct)
FROm employees
WHERE CoMMISSIOn_pct IS NOT NUll;
SELECT last_name,salary,COMMISSION_PCT,TRUNC(salary/Commission_pct,2),(salary/Commission_pct)
FROm employees
WHERE Commission_PCT IS NOT NULL;
SELECT LAst_name,salary,COMMiSSION_PCT,MOD(salary,5000)
from employees
WHERE COMMISSION_pct IS NOT NULL;
--27-10-2018
-- Working with date
SELECT last_name,Hire_date
FROm employees
Where Hire_date <'01-FEB-88';
SELECT LAST_NAME,TRUNC((sysdate-Hire_date)/365) As Year,hire_date
FROm employees
Where department_id =50;
-- MONTHS BETWEEN
SELECT last_name,hire_date,TRUNC(MONTHS_BETWEEN(sysdate,hire_date))
from employees;
-- ADD MONTHS
SELECT last_name,Hire_date,ADD_MONTHS(hire_date,6)
FROm employees;
SELECT LAst_name, Hire_date,Next_day(Hire_date,'FRIDAY'),Last_day(Hire_date)
FROM Employees;
SELECT last_name,TO_CHAR(HIRE_date,'day MONTH year')
FROm Employees;
SELECT last_name,TO_CHAR(HIRE_date,'dd MM yyyy')
FROm Employees;
SELECT last_name,HIRE_date,TO_CHAR(Hire_date,'fmdd mm yyyysp ')
FROm Employees;
SELECT last_name,Hire_date,To_CHAR(Hire_date,'Day Month YEAR')
FROm employees;
SELECT last_name,salary,TO_char(salary,'$99,999.00')
FROm employees;
-- 02/10/2018
SELECT LAst_name,salary,NVL(COmmission_pct,0)
FROm employees;
SELECT last_name,salary,(commission_pct*salary)
FROm employees;
SELECT last_name,salary,nvl(commission_pct,0),(salary*12)+(salary
*12*NVL(commission_pct,0)) Anual_Salary
FROm employees;
SELECT last_name,salary,(salary*12*NVL(commission_pct,0 )) Anual_commission,COmmission_pct
from employees;
SELECT last_name,salary,commission_pct,nvl2(commission_pct,'sal+comm','sal') income
from employees;
-- Using CASE EXPRESSION
SELECT Last_name,job_id,salary,
( CASE job_id WHEN 'IT_PROG' THEN 1.10*SALARY
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.25*salary
ELSE salary
END) "Revised Salary"
FROm employees;
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'LOW SALARY'
WHEN salary<10000 THEN 'MEDIUM SALARY'
WHEN salary <20000 THEN 'GOOD SALARY'
ELSE 'EXCELENT'
END ) "SALARY_STATUS"
FROM EMployees;
--06/10/2018
--GROUP Function
SELECT ROUND(AVG(salary)),MAX(SALARY),MIN(SALARY),SUM(SALARY)
FROm EMPLOYEES
WHERE job_id LIKE '%REP%';
SELECT SQRT(32) FROm dual;
SELECT COUNT(last_name),COUNT(salary),COUNT(employee_id),count(commission_pct)
FROm employees
where department_id =60;
SELECT count(DISTINCT(department_id))
from employees;
SELECT department_id,ROUND(avg(salary))
from employees
group by department_id;
SELECT department_id,job_id,manager_id,SUM(salary)
FROm employees
WHERE department_id>40
GROUP by department_id,job_id,manager_id
order by department_id;
--10-10-2018
--USE OF HAVING
select DEPARTMENT_ID,MAX(SALARY)
from EMPLOYEES
gROUP BY DEPARTMENT_ID
HAVING MAX(salary)>10000;
SELECT DEPARTMENT_ID,ROUND(AVG(SALARY))
FROM employees
GROUP BY DEPARTMENT_id
HAVING MAX(salary)>10000
ORDER BY 2 DESC;
SELECT JOB_ID ,SUM(SALARY)
FROM EMPLOYEES
WHERE JOB_ID NOT LIKE '%REP%'
GROUP By JOB_ID
HAVING SUM(salary)>13000
ORDER BY 2;
-- Using Join
-- Natural Join
SELECT distinct(department_id),department_name
FROm Employees Natural Join departments
order by 1;
SELECT DISTINCT(department_id),(department_name),city
FROm employees Natural JOIN departments Natural Join locations;
-- Using Natural Join(Using)
SELECT *--last_name,department_name
FROm employees JOIN departments
USING (DEPARTMENT_ID );
SELECT table_name FROM ALL_TABLES ;
DESC JOBS;
--COMMING On Clause
-------------------------------------------
SELECT * FROM user_tables;
SELECT * FROM all_users;
SELECT * from dba_users;
SELECT * FROM INFORMATION_SCHEMA.TABLES;
select * from sys.tables;
--------------------------------------------
--i am going to added my git account..
SELECT *
FROM employees JOIN JOBS
USING (job_id);
--10-11-2018
SELECT l.city ,d.department_name
FROM locations l JOIN departments d
USING (location_id)
where location_id =1400;
SELECT first_name,d.department_name,d.manager_id
FROM employees e JOIN departments d
USING (department_id)
WHERE department_id=110;
-- Using Joi by ON clause
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROm employees e JOIN departments d
ON (e.department_id= d.department_id);
-- Join tree table using ON clause
SELECT employee_id ,last_name,city ,department_name
FROM employees e JOIN departments d
ON (e.department_id =d.department_id)
JOIN locations l
ON (d.location_id= l.location_id);
-- Join tree table using "Using" clause
SELECT employee_id,city ,department_name
FROM EMPLOYEES e JOIN DEPARTMENTS d
USING (department_id)
JOIN LOCATIONS l
USING(location_id);
-- Applying Additional Condition to a Join
-- Use the AND clause or the WHERE clause to Apply additiona Conditions
SELECT employee_id ,last_name ,e.department_id,d.department_id ,d.location_id
FROm employees e JOIN departments d
ON (e.department_id=d.department_id )
WHERE e.manager_id =149;
-- OR
SELECT employee_id ,last_name ,e.department_id,d.department_id ,d.location_id
FROm employees e JOIN departments d
ON (e.department_id=d.department_id )
AND e.manager_id =149;
DESC JOBS;
SELECT * FROM ALL_TABLES;
--Using Cross JOIN
SELECT last_name,department_name
FROM Employees CROSS JOIN DEPARTMENTS;
-- USING SUBQUERY
SELECT last_name,salary
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE LOWER(last_name)='abel');
--9:45 PM 10-11-2018
-- SUbQUERY
-- QUESTION: Display the employee whose Job id is tje same as tha pf employees 141.
SELECT last_name,job_id
FROm employees
WHERE job_id =
(SELECT job_id
FROm employees
WHERE employee_id=141);
SELECT last_name ,job_id,salary
FROm employees
WHERE JOB_ID =
(SELECT job_id --NOTE:: NOT WORKING
FROM employees
WHERE last_name='Taylor')
AND salary >
(SELECT salary
FROM Employees
WHERE last_name='Taylor');
---using Group Function in a Query
SELECT last_name,job_id,salary
FROm employees
WHERE salary=
(SELECT MIN(salary)
FROM employees);
SELECT last_name,job_id
FROM EMPLOYEES
WHERE job_id=
(SELECT JOB_ID
FROM employees
WHERE last_name='Nizam');
-- Using the "ANY" Operator in MUltiple ROW subQUERY
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ANY
(SELECT salary
FROM employees
WHERE job_id ='IT_PFROG')
AND job_id <> 'IT_PROG';
-- Using the "ALL" Operator in MUltiple ROW subQUERY
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <All
(SELECT salary
FROM employees
WHERE job_id ='IT_PFROG')
AND job_id <> 'IT_PROG';
-- Using the "EXISTS" Operator
SELECT *
FROM departments
WHERE NOT EXISTS
(SELECT *
FROM employees
WHERE employees.department_id=departments.department_id);