DB2 programs assorted

CREATE

--DROPPING THE EXISTING TABLES IF ANY
 --DROP TABLE COURSE;
 --DROP TABLE STUDENT;
 --DROP TABLE ENROLMENT;
 COMMIT;

 --CREATING  COURSE TABLE
   CREATE TABLE COURSE (
   CID INTEGER NOT NULL,
   CNAME VARCHAR(25) CONSTRAINT CNAME_NNULL CHECK(CNAME IS NOT NULL),
   DURATION SMALLINT CONSTRAINT DURATION_CHK CHECK(DURATION <=10),
   WEIGHTAGE SMALLINT,
   CTYPE VARCHAR(5) DEFAULT 'FP',
   CONSTRAINT COURSE_PKEY PRIMARY KEY(CID)
   )IN DBENR.ENR;

  --ALTER TABLE ADDING A CONSTRAINT

   ALTER TABLE COURSE ADD CONSTRAINT
   COURSE_CHK CHECK(CTYPE IN('FP','RBT'));

  --CREATING AN UNIQUE INDEX ON PRIMARY KEY COLUMN

   CREATE UNIQUE INDEX COURSE_IND ON COURSE(CID);

  --ALTERING TABLE COURSE ADDING COLUMN PREREQ


   ALTER TABLE COURSE ADD PREREQ INTEGER;

  --ALTERING TABLE ADDING  A SELF REFERENTIAL FOREIGN KEY

   ALTER TABLE COURSE ADD CONSTRAINT C_FKEY FOREIGN KEY(PREREQ)
   REFERENCES COURSE(CID) ON DELETE CASCADE;
   
 -- CREATING STUDENT TABLE
   CREATE TABLE STUDENT (
   STID INTEGER NOT NULL,
   SNAME VARCHAR(25) CONSTRAINT SNAME_NNUL CHECK(SNAME IS NOT NULL),
   SEMAIL VARCHAR(25),
   BATCH VARCHAR(25) CONSTRAINT BATCH_NNULL CHECK(BATCH IS  NOT NULL),
   CONSTRAINT STUDENT_PKEY PRIMARY KEY(STID)
   )IN DBENR.ENR;



--CREATING AN UNIQUE INDEX ON STUDENT TABLE

   CREATE UNIQUE INDEX STUD_IND ON STUDENT(STID);

 -- CREATING ENROLMENT TABLE
   CREATE TABLE ENROLMENT (
   STID INTEGER NOT NULL,
   CID INTEGER NOT NULL,
   EDATE DATE WITH DEFAULT,
   MARKS SMALLINT CONSTRAINT MARKS_CHK CHECK(MARKS BETWEEN 0 AND 100),
   CONSTRAINT ENRL_FKS FOREIGN KEY(STID) REFERENCES STUDENT(STID)
   ON DELETE CASCADE,
   CONSTRAINT ENRL_FKC FOREIGN KEY(CID) REFERENCES COURSE(CID) ON DELETE
   CASCADE,
   CONSTRAINT ENRL_PKEY PRIMARY KEY(STID,CID)
   ) IN DBENR.ENR;


--CREATING AN UNIQUE INDEX ON ENROLLMENT TABLE FOR THE PRIMARY KEY

   CREATE UNIQUE INDEX ENROL_IND ON ENROLMENT(STID,CID);

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

 INSERT

--INSERTING DATA INTO TABLE COURSE

insert into course values(1,'CHSSC',4,2,'FP',NULL);
insert into course values(2,'PF',9,4,'FP',1);
insert into course values(3,'RDBMS',7,3,'FP',2);
insert into course values(4,'Introduction to JAVA',5,NULL,
'RBT',NULL);
insert into course values(5,'Sockets in JAVA',2,NULL,
'RBT',4);
insert into course values(6,'Mobile in JAVA',3,NULL,
'RBT',5);
insert into course values(7,'SDM+UID',2,1,'FP',2);
insert into course values(8,'C/S',1,1,'FP',3);
insert into course values(9,'IWT',2,1,'FP',8);
insert into course values(10,'AOA',3,2,'FP',2);


--INSERTING DATA INTO TABLE STUDENT

insert into student values(1001,'Ramesh','ramesh_cv@gmail.com',
'mys-jan03-lc1');
insert into student values(1002,'Pratap','pratap_pa@gmail.com',
'mys-dec04-lc2');
insert into student values(1003,'Rajesh','rajesh_kk@gmail.com',
'mys-jan03-lc1');
insert into student values(1004,'Rakesh','rakesh_ba@gmail.com',
'mys-dec04-lc2');
insert into student values(1005,'Ganesh','ganesh_sh@gmail.com',
'mys-dec05-lc2');
insert into student values(1006,'Mahesh','mahesh_ob@gmail.com',
'mys-jan05-lc1');
insert into student values(1007,'Suresh','suresh_k@gmail.com',
'mys-dec04-lc2');
insert into student values(1008,'Venkatesh','venkatesh_v@gmail.com',
'mys-jan03-lc1');
insert into student values(1009,'Ajeesh','ajeesh_gp@gmail.com',
'mys-jan04-lc2');
insert into student values(1010,'Shibu','shibu_kumar@gmail.com',
'mys-jan03-lc1');
--INSERTING DATA INTO TABLE ENROLMENT
-- DATES ARE INSERTED IN THE FORMAT 'mm/dd/yyyy'

insert into enrolment values(1001,1,'01/09/2004',88);
insert into enrolment values(1001,2,'02/04/2004',79);
insert into enrolment values(1001,3,'04/06/2004',98);
insert into enrolment values(1001,4,'07/25/2004',99);
insert into enrolment values(1002,8,'06/28/2003',78);
insert into enrolment values(1002,7,'09/24/2003',67);
insert into enrolment values(1002,3,'03/19/2004',56);
insert into enrolment values(1002,4,'12/03/2004',78);
insert into enrolment values(1003,2,'11/02/2003',78);
insert into enrolment values(1003,4,'03/22/2004',80);
insert into enrolment values(1003,1,'09/26/2004',79);
insert into enrolment values(1004,5,'02/23/2003',79);
insert into enrolment values(1004,7,'07/15/2004',78);
insert into enrolment values(1004,2,'08/02/2004',66);
insert into enrolment values(1005,1,'08/29/2004',86);
insert into enrolment values(1005,5,'06/28/2003',69);
insert into enrolment values(1007,8,'08/24/2004',66);
insert into enrolment values(1008,6,'07/23/2003',86);
insert into enrolment values(1009,6,'08/13/2004',69);
 

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

QUERY

--printing all the records of student,course and enrollment
select * from student;
select * from course;
select * from enrolment;
--find the count of enrolments for the course id 2.
select count(*) from enrolment group by cid having cid = 2;

--find the maximum marks of any student who are
--enrolled for course with id 1

select max(marks) from enrolment where cid=1;

--find the titles of the courses which does not have
--any enrolled students

select cname from course a where not exists
 (select * from enrolment b where a.cid = b.cid) ;


--print all the student names which start with Ra

select sname from student where sname like 'Ra%' ;

--print all the student names whose email ends with kk


select sname from student where semail like '%kk' ;


--print the course id's from both course and enrolment

select cid from course
union
select cid from enrolment;

--print the student ids who have enrolled for more than
-- two courses


select stid from enrolment group by stid having count(*) >2;

--print the student ids who have enrolled for more than
-- one course using correlated sub query

select distinct stid from enrolment a where cid in
( select cid from enrolment b where a.stid <> b.stid );

--print the course id and number of students
--enrolled for every course
--if no students enrolled the count should be zero


select a.cid, count(b.stid) from
course a left outer join enrolment b
on a.cid = b.cid group by b.cid, a.cid ;

--print all the student names who are enrolled for
--atleast one course

select distinct sname from student a, enrolment b where a.stid = b.stid;

--print the course id and product of duration* weightage for
--all courses which have duration*weightage > 25
--order by duration*weightage

select cid, duration * weightage from course
where (duration*weightage) > 25 order by duration*weightage;


--print the second maximum marks for all students,across all courses

select max(marks) from enrolment where marks
 < (select max(marks) from enrolment) ;

--print the students who have enrolled for the same course
select a.stid , b.stid from enrolment a, enrolment b
where a.cid = b.cid and a.stid < b.stid;

--print the courses which have duration more than 3 days
select cid from course where duration > 3;

--print the enrollments where students got
--marks between 55 and 65
select * from enrolment where marks between 55 and 65 ;

--print all the enrollments where marks can be any one of 66,86,99
select * from enrolment where marks in (66,86,99);

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

OTHERS

--DROP DUMMY TABLE IF IT EXISTS ALREADY
--DROP TABLE DUMMY;
--COMMIT;
--CREATING A DUMMY TABLE
CREATE TABLE DUMMY
(
   COL1 VARCHAR(1) NOT NULL,
   CONSTRAINT PK_DUMMY PRIMARY KEY(COL1)
) IN DBENR.ENR;
CREATE UNIQUE INDEX IDX_DUMMY ON DUMMY(COL1);
--INSERT A ROW IN DUMMY TABLE
--DUMMY TABLE IS USED FOR TESTING THE BUILT-IN FUNCTIONS
--IN DB2
INSERT INTO DUMMY VALUES('A');
--GETTING THE CURRENT DATE
SELECT CURRENT DATE FROM DUMMY;

--GETTING THE CURRENT TIME
SELECT CURRENT TIME FROM DUMMY;
--GETTING THE CURRENT TIME STAMP
SELECT CURRENT TIMESTAMP FROM DUMMY;
--GETTING THE CURRENT TIMEZONE
SELECT CURRENT TIMEZONE FROM DUMMY;

--DATA CONVERSION FUNCTIONS
--CONVERTING STRING TO DECIMAL
SELECT DECImal('1245.50',7,2,'.') FROM DUMMY;
--CONVERTING STRING TO INTEGER
SELECT INTEGER('100') FROM DUMMY;
--CONVERTING STRING TO DATE
SELECT DATE('2007-01-28') FROM DUMMY;
--CONVERTING STRING TO DOUBLE
SELECT DOUBLE('123.456') FROM DUMMY;
--conversion from any numeric,date,time to character

SELect char(DATE(CURRENT DATE)) from DUMMY;
select char(TIME(CURRENT TIME)) from DUMMY;
select char(CURRENT DATE) from DUMMY;
select char(TIMESTAMP(CURRENT TIMESTAMP)) from DUMMY;

--removing spaces from a string
select ltrim(CNAME) from COURSE;
select rtrim(CNAME) from COURSE;


--getting DIFFERENCE IN YEARS

SELECT YEAR(CURRENT DATE)-YEAR(DATE('1997-02-02')) FROM DUMMY;

--GETTING DIFFERENCE IN DAYS
SELECT DAYS(CURRENT DATE) - DAYS(DATE('1997-01-01')) FROM DUMMY;

--GETTING THE CURRENT USER

SELECT USER FROM DUMMY;


--PRINTING YEAR,DAY,MONTH OF A DATE

select YEAR(current date) FROM DUMMY;
select DAY(current date) from DUMMY;
SELECT MONTH(CURRENT DATE) FROM DUMMY;

--printing hour,minute and seconds
select HOUR(CURRENT TIME) from DUMMY;
select MINUTE(CURRENT TIME) from DUMMY;
select SECOND(CURRENT TIME) from DUMMY;



--PRINTING A VALUE FOR A NULL VALUE

select CNAME,VALUE(PREREQ,0) FROM COURSE;

--querying the catalog tables

SELECT * FROM SYSIBM.SYSPLAN;