DBMS.txt

HELLO WORLD
//WRITE COMMIT;  OR INSTEAD WRITE  autocommit on; AFTER INSERTION OF DATA

//creating table having foreign key with name
create table project(num int primary key,std1 int,std2 int,std3 int,constraint fk1 foreign key(std1)  references student101(stdnum),constraint fk2 foreign key(std2) references student101(stdnum),constraint fk3 foreign key(std3) references student101(stdnum));


//ALTERING FOREIGN KEY...........

create table project (groupno int,proj_title varchar2(100), rollno1 int, rollno2 int, rollno3 int);

update project set proj_title='HRMS' where groupno=1;



create table student101(stdno int primary key,classcode varchar2(20),stdname varchar2(30));

alter table project add constraint fk1 foreign key(rollno1) references student101(stdno);



alter table project add constraint fk2 foreign key(rollno2) references student101(stdno);


alter table project add constraint fk3 foreign key(rollno3) references student101(stdno);


alter table student add s1_marks int add s2_marks int add s3_marks int;


alter table student101 add s1_marks int add s2_marks int add s3_marks int;


create table result101(stdno int,total int,perc float,grade varchar2(2),status varchar2(10));

//////////////////////////////////////////////////////////////////////////////////////////////////////////////


FOREIGN KEY MAY BE NULL

<>      -------> NOT EQUALS TO SIGN



To start the DBMS(SQL 11g) on your PC. Go to start Oracle11g-->run SQL command line.
First we have to connect our database. Type     connect SYSTEM          then write password.

create table Patient(patId int,
                     patName char(50),
                     BloodGrp char(5)
                    );
Type --> desc Patient; <-- to see whole table.
We can see that by default the int will have space of 38 bytes.

There can be constraints on our table which can be at:
a) Table level
  1)Primary Key
  2)Foreign Key
b) Field level
  1)Unique
  2)Not null (It is also considered as Check)
  3)Check (User defined Constraint)
  4)Default

To delete table from DB we write --> drop table Patient; <--

Let we add constraints....
create table Patient(patId int primary key,
                     patName char(50) not null,
                     BloodGrp char(5) default 'A +ve',
                     contact int unique,
                     email char(50) check(email=lower(email))
                    );
 
 
Type -->desc user_constraints<-- this to see how much user constraints fields are there at the total.


Remember that when we make table it is named as uppercase in the oracle.
Following code will display constraints applied by the user.
  select constraint_name,constraint_type from user_constraints where table_name='PATIENT';
  select constraint_name,constraint_type,search_condition from user_constraints where table_name='PATIENT';



///**INSERTING data in tablename**
    insert into Patient values(123,'Hey1',default,9999,'pooj@gmail.com');

//Printing data of the table.
    select * from Patient;  //This will take more lines to display
    select PATID||PATNAME||BLOODGRP||CONTACT||EMAIL from Patient;
    
  The char(50) will take exactly 50 characters while writing thus to avoid this we initiallize fields as:
  varchar(50) or varchar2(50) 
  or we can simply trim(patName) while printing.
    select PATID||' '||trim(PATNAME)||' '||trim(BLOODGRP)||' '||CONTACT||' '||EMAIL from Patient;
    
//DELETING DATA FROM TABLE
    delete from Patient;      //Deletes every record of table
    delete from Patient where patId=104;

//UPDATING
    update Patient set patId=109 where patId=5123;
    
FOREIGN KEY CAN BE DUPLICATED & can accept null values......!

EXAMPLE FOR MAKING FOREIGN KEY AND TIME DATA TYPE:
      create table doctor(docId int primary key,docName char(50) not null);
      
      //timestamp is DT which includes both D&T
      create table appnt(appDate timestamp,docId int references doctor(docId), patId int references Patient(patId));  
      insert into doctor values(101,'xyz');
      
      //systimestamp gives the current time of ur computer.
      insert into appnt values(systimestamp,101,'xyz');   
      insert into appnt values(systimestamp,101,1097);
      
//**MAKING COMPOSITE KEY**
       create table appnt1(appDate timestamp,docId int references doctor(docId), patId int references Patient(patId), primary key(appDate,docId,patId));
//**MAKING FOREING KEY OF COMPOSITE KEY**
      create table visit(appDate timestamp,docId int , patId int , foreign key(appDate,docId,patId) references appnt1(appDate,docId,patId));
      
      dereferencing composite key as a foreign key :
         create table takes
            2  (ID int references student(ID),course_id varchar(10),sec_id varchar(10),semester int,year int,
              grade varchar(5) ,foreign key(course_id,sec_id,semester,year) references section(course_id,sec_id,semester,year),
              primary key(ID,course_id,sec_id,semester,year));



//QUERY
  **//SELECT KEYWORD**
      select distinct fieldname/*/function/expression
      from tablename/subquery
      where condition on record level (can't specify condition on aggregate fn)
      group by fieldname/fn/expression
      having cond on grp/aggregate fn
      order by fieldname/expression/function
      
      /DISTINCT
        select distinct patname,bloodgrp from patient;        /////Display unique record

      //STRING FNs  Strings have 1 based indexing
        select upper(patname),lower(patname),
        trim(patname),ltrim(),rtrim(),
        initcap(patname)              //Capitalize initial letter.
        substr(patname,2,5) from patient;   //Substr of patname strting from 2nd letter to 6th letter.
        
      /// IN OPERATOR
        select * from patient where length(patid)>2;
        select * from patient where patid in (101,103);
        select * from patient where patid in (select patid from appnt);   //The subquery will be executed first.
        
        select customer_name from customer where customer_city in (select branch_city from branch where branch_name 
        in (select branch_name from bank_account where account_number         in (select account_number from depositer where customer_name in (select customer_name from                 customer))));
        
        WE CANNOT WRITE LIKE..... 
          select * from patient where patid in (select * from appnt); // as * returns everything in the subquery.
          select * from patient where patid in (select patname from appnt);  // as datatype of patid and patname are not same.
      
      /// LIKE OP
         % is used for many characters
         _ is used for single character 
         
         select patname from patient where patname like '_ya%';
         select patname from patient where trim(patname) like '_ya';
         
         
     /// BETWEEN OP
         select patid from patient where patid between 1 and 10;    //HERE 1 AND 10 BOTH ARE INCLUDED IN THE RANGE.
         
         OR WE CAN WRITE EQUIVALENT AS:   select patid from patient where patid>=1 and patid<=10;   
         
    ///   IS NULL OP
          
          select stdname from student where stdhobby is null;
          select stdname from student where stdhobby is not null;
          
   ///    EXISTS OP
          
          select patid from patient where exists(select * from appnt);    // IF INNER QUERIES HAVE DATA THEN IT WILL RETURN TRUE.
          
  //// AGGREGATE FNs
        select avg(stdid),count(*),count(stdhobby), max(stdid),min(stdid),sum(stdid) from student;    
                          //count(*) will count null values and will count all the total record
        select count(distinct grpnum) from student;   //To count only distinct values.
        
 ////// DIFFERENT TYPES OF JOINT
    a)Cross
        select * from patient,student;
    
    b) Simple/equi/natural(=)
        select docname||' '||patname from doctor,patient where doctor.docid=patient.patid;
        
    c) Multiple join
        select docname||' '||patname from doctor,patient,appnt where doctor.docid=appnt.docid and patient.patid=appnt.patid;
    d) Inner join
        select * from doctor inner join appnt on doctor.docid=appnt.docid;
                //Same as simple join
                
    // INERSECTION,UNION,DIFFERENCE
        select docid from doctor intersect select docid from appnt;
        select docid from doctor minus select docid from appnt;
        select docid from doctor union select docid from appnt;
        
     e) OUTER JOIN
        
        1)Left join,Right join, full join:
            It will display the whole left table and checks wheter the condition is existing or not if it exist then right table will also be shown else it will show null in every part of right table if condition is false.
            And if frequency is more in any one of them then the other's table will increase its frequency by itself.
            select doctor.docid||' ' ||dotor.contactno||' '||appnt.docid||' '||appnt.appdate||appnt.patid from doctor left join appnt on doctor.docid=appnt.docid;
            
            It will display the whole right table and checks wheter the condition is existing or not if it exist then left table will also be shown else it will show null in every part of left table if condition is false.
            select doctor.docid||' ' ||dotor.contactno||' '||appnt.docid||' '||appnt.appdate||appnt.patid from doctor left join appnt on doctor.docid=appnt.docid;
            
            It is combination of left join and right join
            select doctor.docid||' ' ||dotor.contactno||' '||appnt.docid||' '||appnt.appdate||appnt.patid from doctor full join appnt on doctor.docid=appnt.docid;
        
        
        ///////////ALIAS
            select d.docid,d.docname from doctor d;   //here d is alias
    
    f) Self join //Joining table with itself
                -------->eg. Display name of doctor whose name are same but gender are different.
          select distinct d1.docname from doctor d1,doctor d2 where d1.docname=d2.docname and d1.gender<>d2.gender;
    
   
   
    /////////GROUP BY and HAVING
    
              select gender,count(*) from docor group by gender;
              select gender,count(*) from docor group by gender having count(*)>1;  // The fields used in group by and having can only be selected in select
              select b.customer_name||'     '||sum(l.amount) from borrower b  , loan l  where b.loan_id=l.loan_number group by b.customer_name;
              
   //////// ORDER BY 
         select * from doctor order by docname desc,gender ;  // this will perform first descending order of docname then ascending of gender then prints.
         
         
    select * from sale where length(saletext)=(select max(length(saletext)) from sale);
         
              
    DROPPING UNIQUE CONSTRAINTS..................
    alter table student drop unique (dept_name);
    
    7.	Enroll every student in the Comp. Sci. department in section 1.
      insert into takes (id, course_id, sec_id, semester, year,grade) select id,'CS-001',1,3,2009,'A+' from student where dept_name='CSE';



-----------------------date formats

select stdno,to_char(birthdate,'dd'),to_char(birthdate,'mm'),
      to_char(birthdate,'dd-mm'),to_char(birthdate,'month'),
      to_char(birthdate,'dd month yyyy'),
  to_char(birthdate,'ddth month yyyy'),
  to_char(birthdate,'ddsp'),
  to_char(birthdate,'ddthsp') from student2021;