CREATE TABLE depart123(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) UNIQUE NOT NULL
);
insert into depart123 values(3,'CIVIL');
select * from depart123;
insert into depart123 values(null,'IT');
CREATE TABLE jul18 (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
hiredate DATE,
location varchar(20) default 'Nolocation',
age INT CHECK (age >= 18 AND age <= 60),dept_id INT,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES depart123(dept_id));
insert into jul18 values(123,'arun','[email protected]','12-jan-20','chennai',27,1);
select * from jul18;
insert into jul18 values
(124,'arun','
[email protected]','12-jan-20','chennai',27,1);
insert into jul18 values
(125,'raja','
[email protected]','21-feb-23','trichy',28,7);
insert into jul18(id,name,email,hiredate,age,dept_id) values
(126,'ram','
[email protected]','20-feb-23',28,1);
select * from jul18;
==================
Joins:
select * from emp_j1;
select * from cours_1;
INNER JOIN/JOIN
OUTER ===>RIGHT,LEFT ,FULL
SELF
CROSS
SELECT * FROM EMP_J1 JOIN COURS_1 ON EMP_J1.ID=COURS_1.ID;
SELECT * FROM EMP_J1 RIGHT JOIN COURS_1 ON EMP_J1.ID=COURS_1.ID;
SELECT * FROM EMP_J1 LEFT JOIN COURS_1 ON EMP_J1.ID=COURS_1.ID;
SELECT * FROM EMP_J1 FULL JOIN COURS_1 ON EMP_J1.ID=COURS_1.ID;
SELECT EMP_J1.NAME,COURS_1.CNAME FROM EMP_J1 JOIN COURS_1 ON EMP_J1.ID=COURS_1.ID;
SELECT EMP_J1.NAME,COURS_1.CNAME FROM EMP_J1 CROSS JOIN COURS_1;
SELECT * FROM EMPLOYEES;
EMP MANAGER
SELECT E1.FIRST_NAME AS EMPNAME, E2.FIRST_NAME AS MANAGERNAME
FROM EMPLOYEES E1 JOIN EMPLOYEES E2
ON E1.MANAGER_ID=E2.EMPLOYEE_ID;
===============================
GROUP Functions:
select * from employees;
select count(first_name) from employees;
select max(salary) from employees;
select min(salary) from employees;
select avg(salary) from employees;
select sum(salary) from employees;
select * from employees;
select department_id,max(salary) from employees
group by department_id;
ANALYTIC FUNCTIONS/WINDOW FUNCTIONS:
select first_name,salary,
row_number() over(order by salary desc)from employees;
select first_name,salary,
rank() over(order by salary desc)from employees;
select first_name,salary,
dense_rank() over(order by salary desc)from employees;
select first_name,salary,department_id,
dense_rank() over(partition by department_id
order by salary desc)from employees;
with e as(select first_name,salary,
dense_rank() over(order by salary desc) as ranking from employees)
select * from e where ranking =2 ;
select * from (select first_name,salary,
dense_rank() over(order by salary desc) as ranking from employees)
where ranking =2 ;
select first_name,salary,
lead(salary,3,0) over(order by salary) from employees;
select first_name,salary,
lag(salary,3,0) over(order by salary) from employees;
select first_name,salary,
lag(salary) over(order by salary) from employees;
================================
VIEW:
Virtual table
select * from emp_j1;
create view vw1 as select * from emp_j1 with read only;
select * from vw1;
insert into emp_j1 values(11,'harish');
select * from emp_j1;
insert into vw1 values(100,'abc');
drop view vw1;
create view vw2 as select id from emp_j1;
select * from vw2;
insert into vw2 values(100);
select * from emp_j1;
insert into emp_j1 values(200,'mani');
=====================================================
SET OPERATORS:
UNION
UNION ALL
INTERSECT
MINUS
SELECT * FROM EMP_J1;
1
2
3
4
11
100
200
10
SELECT * FROM COURS_1;
1
2
3
6
8
10
SELECT ID FROM EMP_J1 UNION SELECT ID FROM COURS_1;
SELECT ID FROM EMP_J1 UNION ALL SELECT ID FROM COURS_1;
SELECT ID FROM COURS_1 UNION ALL SELECT ID FROM EMP_J1;
SELECT ID FROM COURS_1 INTERSECT SELECT ID FROM EMP_J1;
SELECT ID FROM COURS_1 MINUS SELECT ID FROM EMP_J1;
SELECT ID FROM EMP_J1 MINUS SELECT ID FROM COURS_1;
==============
