Notes

****************************************************************DataDefinitionLanguage***************************************************************************

create table Employee(empid number(10),empname varchar2(40)); // CREATE TABLE
desc employee;
alter table employee add salary number(8,2); // ADDING
desc employee;
alter table employee rename column empnamee to empname1; //RENAME
desc employee;
alter table employee modify empname1 varchar(20); // TO CHANGE THE STRUCTURE OF A TABLE
desc employee;
alter table employee rename to emp; //TO RENAME A TABLE
desc emp;
alter table employee rename COLUMN CLOUMN NAME TO NEW NAME; // TO RENAME THE COLUMN NAME.

drop table emp; // to delete the table

truncate table employee; // to delete the table data and retain the structure of table.

------------------------------------------------------- Data Manipulation Language-------------------------------------------------------------------------------

Insert values

insert into emp values(10,'name',1000);

partial insertion:

insert into emp(empid, empname) values(10,'name');

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

UPDATE:

update emp set salary=90000 where empid=1000; // to update the data of certain row using primary key.

update emp set salary =90000; // to update whole column of a table with given value.

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

DELETE:

delete table emp where empid=1000; // to delete the data of certain row using primary key.

delete table emp; // to delete all the rows in the table.

------------------------------------------------------------keys/constraint--------------------------------------------------------------------------------------
primary key:

to add primary key------

alter table emp modify /*empid number(10) constraint pk_empid primary key*/; -----Column level primary key------
desc emp;
alter table emp modify empid number(12);
desc emp;

create table client(empid number(10), empname varchar2(20),/*constraint pk primary key(empid)*/); -----table level primary key------

alter table emp add constraint pk_empid primary key(empid); --------nor table level nor column level----

alter table table name drop constraint constrain name; --------to drop constraint------(pk_empid) constraint name........
desc client;
---------------------------------------------------------------------------------------------------------------------------

foreign key:

alter table emp add did number(10) constraint fk_did references dept(did);

----------------------------------------------------------------------------------------------------------------------------
self referencing key:

TO ADD self referencing key----------------

create table employe (empid number(10), empname varchar2(40) constraint nnl_empname not null,email varchar2(30) constraint ukk_email unique,mangerempid number(10) /*constraint mid references employe(empid)*/,constraint kk_empid primary key(empid));
desc employe;

insert into employe values(1001, 'ram','[email protected]',null );
insert into employe values(1002, 'ramu','[email protected]',1001 );
------------------------------------------------------------------------------------------------------------------------------
composite key: // composite key is used to find the elements that are attached to two tables using the individual primary keys of individual table.

employee table-----
create table employe (empid number(10), empname varchar2(40) constraint nnl_empname not null,email varchar2(30) constraint ukk_email unique,mangerempid number(10) constraint mid references employe(empid),constraint kk_empid primary key(empid));
desc employe;

course table-------
create table course(courseid number(10) constraint pk_courseid primary key, coursename varchar2(30) constraint nnl_cname not null, durationinhours number(2)constraint ck_duration check(durationinhours>0));

courseregistration table-------
create table courseregistration(courseid number(10),empid number(10),startdate date,enddate date, assesmentscore number(3) constraint ck_score check(assesmentscore>20), /*constraint prk_key primary key(courseid,empid)*/);


---------------------------------------------------Data Retreival Commands--------------------------------------------------------------------------------------
SELECT: it is used to retreive the data without reflecting the table/ database.

select * from employee; // to show all the rows in a table.

select eid, ename from employee; // to retreive the all the column data.

select * from employee where empid=1000; // to retrieve the data of certain row using primary key.

select DISTINCT salary from emp; // to get distinct values from the data.

----------------------------------------operators used in select statements to retreive the data---------------------------------------------------------------------

->BETWEEN // to retrieve data between two values.
select empid, empname, salary from employee where salary between 10000 and 20000;
----------------

->IN: //to retreive data which has the value given.
select empid, empname from emp where salary in(10000,20000);

----------------
->AND :

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

select empid, empname from emp where salary >10000 and salary< 20000;
----------------
->OR:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

select empid, empname from emp where salary >10000 or salary< 20000;


-----------------------
->LIKE: pattern matching- it has two wild card ((%) for multiple values or single value,( _ ) for a single value)

select eid,ename from emp where ename like 'j%'; // to retrieve all the information with likelihood of multiple character after j.
select eid,ename from emp where ename like 'j_ck'; // to retrieve all the information with likelihood of single character between j and c.
select eid,ename from emp where ename like 'j_c%'; // to retrieve all the information with likelihood of single character between jandc and multiplecharacter.


----------------------------------------------------Transmission Control Language--------------------------------------------------------------------------------
Rollback;-----to undo the actions so performed----
commit; ----to commit the action so performed----

----------------------------------------------------------------FUNCTIONS----------------------------------------------------------------------------------------
AGGREGATION:

select empid as "employee id" from emp; //to change the name of column while printing without effecting database.

select salary, salary+1k as employee salary from emp; // adding the value and assiging tht to a temporary table.

MAX:
select empname,eid ,max(salary) from emp ; // maximum value;

MIN:
select min(salary)from emp; //minimum value

AVG:
select avg(salary)from emp; // average value

COUNT:
select count(salary) from emp; //count the total no of salary records.
select count(distinct(salary)) from emp; //count the total no of distinct salary records.

DATE:
select to_char(doj,'day,mon')from emp; // display the date in day and month
select to_char(doj,'yyyy')from employee; //display the date in year.


------------------------------------------------------------------------------------------------------------------------------
GROUPBY:

select did,count(*) from emp group by did;

seselect did,count(*) from emp group by did having count(*)>1; //always use having along with group by to specify the condition.


-------------------------------------------------------------------------------------------------------------------------------
ORDERBY:

select empid, empname, salary order by salary; // by default when orderby is provides in ascending order.

select empid ,empname,salary from emp order by salary asc; // to order the values in ascending order

select empid ,empname,salary from emp order by salary desc; // to order the values in descending order
----------------------------------------------------------------------------------------------------------------------------------------------
NVL: NOT A NULL VALUE;
select nvl(range, 0) from emp;


select nvl(to_char(range),'please insert range') from emp; //TO_CHAR- FUNCTION

select nvl(to_char(range),'11') from emp where empid=101;

-------------------------------------------------------------------------------------------------------------------------------------------
DUAL: DUMMY TABLE

select * from dual;

select substr('training',3,5) from dual; //SUBSTR()--FUNCTION

select translate('substring','s','a') from dual; //TRANSLATE()--FUNCTION

select round(410.1121) from dual; //ROUND()- FUNCTION.

-----------------------------------------------------------------SUB QUERIES:--------------------------------------------------------------------------------------

SUB QUERIES
these are categorized in two type;
->Non corelated
->corelated

-----------------------------------------------------------------------------------Joins------------------------------------------------------------------------------
Joins:

->Cross join: Retreive cartesian product (M x N) from the tables.

select e.enum,e.ename,e.dob,e.salary,e.dcode,d.dname from employee e cross join department d; // no condition should be provided but key cross join must b used.
--------------------------------------------------------
->Inner Join: Retreive matching records between both the tables.

select e.enum,e.ename,e.dob,e.salary,e.dcode,d.dname from employee e Inner join department d on e.dcode=d.dcode; //condition must b performed

select e.eid,e.ename,a.assetid,a.assetname from employeee e, asset a where e.assetid=a.assetid;

select e.eid,e.ename,a.assetid,a.assetname from employeee e inner join asset a on e.assetid=a.assetid order by e.eid;

->Self join: it refers to the same table.

select e.ename ,m.ename as "Manager name" from employee e inner join employee m on e.mnum=m.enum ; //refers to same table.

----------------------------------------------------------------
->Outer Join
->left Outer Join: It retrieves the left records matched to right table and place null incase of values not matched.

select e.eid,e.ename,e.assetid,a.assetname from employeee e left outer join asset a on e.assetid=a.assetid order by e.eid;
-------------------------------------------------------------

->right Outer Join: It retrieves the Right records matched to right table and place null incase of values not matched.

select e.eid,e.ename,a.assetid,a.assetname from employeee e right outer join asset a on e.assetid=a.assetid order by e.eid;
--------------------------------------------------------------

->full Outer Join: It retrieves all the records matched to right table and place null incase of values not matched.

select e.eid,e.ename,a.assetid,a.assetname from employeee e full outer join asset a on e.assetid=a.assetid order by e.eid;
----------------------------------------------------------------------------