sql fundamentals.txt

Oracle 
sql:-structure query langauage.

DATA TYPES:-
-------------------------------
1)number :-size 38 digits   => number(5),number(9,2) to enter float  9999999.99
2)character:-size 2000 bytes =>char(20)
3)varchar2:-4000 bytes =>varchar2(15)
4)date:-7 bytes  =>date  =>'21-jan-2019'
5)long:-2gb
6)raw:-store binary data ,size is 2000 bytes
7)long raw:-2gb
8)lob:-4 gb
----------------------------------------------------------------------------------
oracle database:-
-------------------------- 

operator :-
------------------
1)arithmatic operator :-  +,-,*,/                                          


select idno,name,dob,salary+100 from student;

select idno,name,dob,salary*12 from student;

select idno,name,dob,salary/30 from student;


select empid,name,salary+100 as Incrementsalary from legatost1;

select empid,name,salary*12 as Annualsalary from legatost1;




insert into student(name,address,idno,DOB) values(&name,&address,&idno,&DOB)

insert into student(name,address,idno,DOB) values('&name','&address',&idno,'&DOB')
----------------------------------------------------------------------------------

2)comparison operator :- =,>=,<=,>,<,!=,<> (not equal to)  

 =>select * from student where salary > 40000;
----------------------------------------------------------------------------------

 between and,not between ,in,not in,like,is null  			          

=>select * from emp where sal between 2000 and 4000;
					      
=>select * from emp where deptno in(10,20);
				                      
=>select ename from emp where sal>=1000 and job like '%m';

kiran
=>select name from student where name like '_i%';

=>select name from student where name like '__r%';					     
 =>select * from emp where sal>=1500 and sal<=2500;
					     
 =>select * from emp where sal between 1500 and 2500;

=>select empname,empcode,dob from employee1 where phoneno is null;
----------------------------------------------------------------------------------
4)logical operator :-and,or,not                                         

 =>select * from emp where ename='smith' and sal=8000;

==>select empcode,empname ,dob from employee1 where salary >25000 and empname not like '%d';

5)concatenate operator :-  ||

select name||' the rollno is '||rollno from student;
---------------------------------------------------------------------------
rename a column in oracle:-
alter table sales rename column order_date to date_of_order;


ALTER TABLE table_name   
RENAME TO new_table_name;  




Operations
-------------
1)TL (transaction language) :-select

2)DML(data manipulation langauge) :-insert,update,delete
they are temporary .if we rollback it will come to previous state.

3)DCL (data control language) :-grant,revoke

4)DDL (data defination language) :-create,alter,drop

5)TCL (Transaction control langauge) :- commit,rollback,savepoint.




DATE
--------
it is stored as a number in the database and hence one can add number to date ,subtract number to date.
DATE FUNCTION:-
---------------------------
1)select months_between('28-mar-2019','28-feb-2019') from dual;//dummy table 
2)select last_day(sysdate)from dual;
or
2)select last_day('02-feb-2019')from dual;

3)select to_char(sysdate,'DD Month yyyy HH:MI:SS AM AD') from dual;

to convert date data type to char data type.
4)select * from emp where to_char(hiredate,'D') in (1,2,6); 
(employee who have joined either on sunday,monday,friday)

5)select to_char(sysdate,'Day') from dual;



-------------------------------------------------
ALTER TABLE emp2 MODIFY name char(150) unique;
------------------------------------------------------

ALTER TABLE emp2
MODIFY name DEFAULT 'Sandip';
-------------------------------------------------------------
The ANY operator returns TRUE if any of the subquery values meet the condition.

Example

SELECT empno,name,hiredate
FROM emp2
WHERE empno = ANY (SELECT empno FROM emp2 WHERE empno = 101);

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

SQL FUNCTION
------------------------
select * from emp order by sal asc;

select * from emp order by sal desc;

Aggregate Functions :-(apply only for column)

1) select sum(sal),avg(sal),max(sal),min(sal),count(sal) from emp;

create table student6(rollno number,name varchar2(15),day number,per number(5,2));

insert into student6(rollno,name,day) values(1,'david',1) ;


select * from student6;


update student6 set per=25/25 *100;
--------------------------------------------------------------------------------------




2)select vsize(empno),vsize(ename) from emp; (memory occupied by column value)

3)select comm,nvl(comm,0) from emp; 
 //null in oracle is not zero.the value is yet to be defined..

4)select * from emp where to_char(hiredate,'D') in (1,2,6); 
(employee who have joined either on sunday,monday,friday)
-------------------------------------------------------------

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) .

SELECT COUNT(empno),name
FROM emp2
GROUP BY name;


//create table employeex(empno number,name varchar2(30),deptno number,salary number);
insert into employeex values(101,'sunil',10,7500);
insert into employeex values(105,'anil',20,6500);
select * from employeex

SELECT COUNT(empno),deptno FROM employeex GROUP BY deptno;
SELECT sum(salary),deptno FROM employeex GROUP BY deptno;
SELECT avg(salary),deptno FROM employeex GROUP BY deptno;
SELECT max(salary),deptno FROM employeex GROUP BY deptno;
SELECT min(salary),deptno FROM employeex GROUP BY deptno;

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


The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

select deptno from employeex group by deptno having min(salary) >= 2500;



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


6)select ename from emp where rownum <11 minus select ename from emp where rownum <10;

7)select empno,ename from emp union select deptno,dname from dept;
(filter duplicate records)

8)select empno,ename from emp where sal>2000 union all select empno,ename from emp where deptno=10;

(will select duplicate records also)

9)alter table sales modify order varchar2(25);

------------------------------------------------------------------------------------------------------
create table student (rollno number,name varchar2(15),address varchar2(15),dob date);

insert into student values(1,'sandip','jayanagar','01-jul-1980');

select * from student;


update student set address='jp nagar' where rollno=1;


alter table student add phoneno number;   

update student set phoneno=123445 where rollno in(1,5,4);

select empno,sal,sal*12 as annual_salary,comm commission from emp;


there are 5 constraints in oracle
----------------------------------------
1)primary key :- if we declare the field as primary key then we cannot enter duplicate value and also we cannot leave the field empty.

2)not null:- if we declare the field as not null we cannot leave the field empty.

3)unique :-if we declare the field as unique then we cannot enter duplicate value.

4)check :-we can give limit to the field.

5)foreign key:-it is a reference key which refers to other table data.




------------------------------------------------------------------------------------------------------------------
create table studentxy(regno number primary key,name varchar2(30) not null,address varchar2(30) unique,marks number check(marks > 20));

create table studentxy(regno number primary key,name varchar2(30) not null,address varchar2(30) unique,marks number check(marks > 20));

insert into studentxy values(102,'kIRAN','CHENNEI',21)

SELECT * FROM STUDENTXY;

insert into studentxy(REGNO,ADDRESS,MARKS) values(103,'HYD',23)

create table library(regno number references studentxy,DOE date,DOR date)


6)Composite key is a key which is the combination of more than one  column of a given table if we want to make primary key.

Create table student2  
(rollno number,regno varchar(30),address varchar(50),  
PRIMARY KEY (rollno, regno));  
----------------------------------------------------------------------------------


7)Alternate key is also called secondary key .

Example :-student table contain ROLL NO,NAME,ADDRESS and MARKS

Here ROLL NO is primary key and rest of all columns are alternate keys.






----------------------
create table employee (rollno int primary key,name varchar(15) not null,address varchar(15) unique,marks int check (marks >3))


insert into employee values(2,'peter','yelhnaka old',4); 


create table salary(rollno int references employee,salary int);


insert into salary values(2,3500);

select a.rollno,a.name,a.address,a.marks,b.salary from employee a ,salary b where a.rollno=b.rollno;

----------------------
select * from employee2

create table employee2 as select * from employee1

insert into employee2 values(3,'dilip','1-jun-1999','officer',56000)

select * from employee2 minus select * from employee1;

select * from employee2 intersect select * from employee1;

select * from employee2 union select * from employee1;

select * from employee2 union all select * from employee1;
-----------------------------------------------
create table employee(empno number,name varchar2(30),address varchar2(30),doj date);

insert into employee values(101,'sandip','bangalore','2-may-2018');

insert into employee(doj,address,empno,name) values('4-may-2018','bangalore',102,'kiran');

select * from employee;

select empno,name,address from employee;

add some more columns;
------------------------------
alter table employee add salary number(9,2);

select * from employee;

update employee set salary = 45000 where empno=102;

alter table employee add (Hr number(9,2),itax number(9,2),gross number(9,2));

update employee set hr=.2*salary where empno in(101,102);

update employee set itax=.3*salary where empno in(101,102);

update employee set gross=(salary+hr)-itax where empno in(101,102);

insert into employee(empno,name,address,salary) values(103,'sunil','hyderabad',43000);

delete employee where empno=103;

alter table employee drop column address;

alter table employee drop (doj,empno);

select * from employee;

----------------------------------------------------------------------------------------------------
delete employee where name='sandip';

truncate table employee;(only data will be deleted ,table will be there)

desc employee;
drop table employee;(data+table will be deleted)

desc employee;






select distinct(empno) from employee;(only unique value will be displayed)

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

Contraints :-
--------------------
1)primary key :- if we declare a column as primary key we cannot enter duplicate records nor we can keep it empty.

2)unique key:- if we declare a column as unique we cannot enter duplicate record.


3)not null :-if we declare a column as not null we cannot leave the record empty.


4)check :- if we declare a column as check it should fulfil the condition.




constraints
---------------------
create table bank(accno number primary key,name varchar2(30) unique,address varchar2(30) not null,bal number check (bal > 500));



https://github.com/sandipmohapatra/mphasis
-----------
The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Uniquely identifies a row/record in another table

CHECK - Ensures that all values in a column satisfies a specific condition

DEFAULT - Sets a default value for a column when no value is specified

INDEX - Used to create and retrieve data from the database very quickly

------------------
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
----------------------------------------
CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

-----------------------------------------------------------------------------
composite key
--------------------------------
create table studenttable
( rollno number not null
, name varchar2(20) not null
, marks number not null
, primary key (rollno,name,marks)
, unique (rollno,name)
);
-------------------------------------------
create table details
( rollno number not null
, name varchar2(20) not null
, dob date
, foreign key (rollno,name) references studenttable (rollno,name))

---------------------------------------------------------
Normalization is a systematic approach of decomposing tables to eliminate data redundancy(repetition) and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables.

--------------------------------------------------------------------------------------
First Normal Form (1NF)
For a table to be in the First Normal Form:

It should only have single(atomic) valued attributes/columns.
As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.
---------------------------------------------------------------------------------------
Second Normal Form (2NF)
For a table to be in the Second Normal Form,

It should be in the First Normal form.
And, it should not have Partial Dependency.
------------------------------------------------------------------------------
Third Normal Form (3NF)
A table is said to be in the Third Normal Form when,

It is in the Second Normal form.
And, it doesn't have Transitive Dependency.
---------------------------------------------------------------------------



https://beginnersbook.com/2015/05/normalization-in-dbms/
-----------------------------------------------------------------------------------
stud:-
empno number primary key
name varchar2(30)
address varchar2(30)
------------------------------------------------------
result :-
empno number references stud
phy number
chem number
bio number
tot number
avg number
grade varchar2(30)

--------------------------------------------------------
inner join

insert into stud values(105,'peter','chennei');

insert into result(empno,phy,chem,bio) values(104,61,61,51);
select * from result;
update result set tot=phy+chem+bio where empno in(101,102,103,104);
update result set avg=tot/3 where empno in(101,102,103,104);
update result set avg=round(tot/3,2) where empno in(101,102,103,104);
update result set grade='first' where avg>=60  
update result set grade='second' where avg<60

(INNER) JOIN: Returns records that have matching values in both tables
desc result;
select * from stud;


select a.empno,a.name,a.address,b.phy,b.chem,b.bio,b.tot,b.avg,b.grade from stud a,result b where a.empno=b.empno;

select a.empno,name,address,phy,chem,bio,tot,avg,grade from stud a,
result b where a.empno=b.empno;

select stud.empno,stud.name,stud.address,result.phy,result.chem,result.bio,result.tot,result.avg,result.grade from stud ,result  where stud.empno=result.empno;
----------------------------------------------------------------------
WITH
-------------------------------------------------------------------------------------------------------------------------------
Let us take the example of Student table.If user wants to find out the Students from Student table whose marks is more than Average of all marks.
-----------------------------------------------------------------------------------------------------------------------------------
With Student_Temp(Average_Marks)

As

(Select Avg(Marks) From Student),

Select Roll_No,Name,Marks From Student , Student_Temp

Where Student.Marks > Student_Temp.Average_Marks);
------------------------------------------------------------------------------------------------------------------------
1)Write a query to create a student table having columns (rollno,name,address,email,addhar cardno,phy marks,chem marks,bio marks)
2)insert 10 records into it.
3)find total and average of 3 subjects marks.












-----------------------------------------------self join-------------------------------------------
self join :- The join is within a single table.
*we are displaying all customer pairs who has different customerID's

<>  ------> not equal to 
SELECT * FROM Test Where name <> 'amit'
----------------------------------------------------------------------------
create table customer (CustomerID number,CustomerName varchar2(30),ContactName varchar2(30),	
Address varchar2(30),City varchar2(30),PostalCode number,Country varchar2(30))
---------------------------------------------------------------------------------
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customer A, Customer B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
--------------------------------------------------------------------------------
inner join:- we join 2 tables having same column name.
-------------------------------------------------------------
outer join :-
left outer join
right outer join
----------------------------------------------