Docstoc

rdbms codings

Document Sample
rdbms codings Powered By Docstoc
					                             1(A) Creating the Tables
CREATE TABLE DEMO_STUDENT
(
ST_NO CHAR (5),
ST_NAME VARCHAR (15),
ST_MARK1 NUMERIC (5, 2) ,
ST_MARK2 NUMERIC (5, 2),
MAX_MARK NUMERIC (5 ,2),
);

CREATE TABLE PASSED_OUT_STUDENT
(
ST_NO CHAR (5),
ST_NAME VARCHAR (15),
ST_MARK1 NUMERIC (5, 2) ,
ST_MARK2 NUMERIC (5, 2),
MAX_MARK NUMERIC (5, 2),
);

CREATE TABLE COURSE_DETAILS
(
COURSE_CODE VARCHAR (10),
COURSE_DESC VARCHAR (25),
COURSE_DURATION NUMERIC (8, 2),
COURSE_FEE NUMERIC (8, 2)
);


CREATE TABLE EMPLOYEES
(
COMPANY_NAME VARCHAR (25),
EMPLOYEE_ID CHAR (5),
EMPLOYEE_NAME VARCHAR (15),
TITLE_OF_COURTESY VARCHAR (15),
BASIC_SAL NUMERIC (8, 2),
HRA NUMERIC (8, 2),
DA NUMERIC (8, 2),
LIC NUMERIC (8, 2),
DF NUMERIC (8, 2),
GROSS NUMERIC (8, 2),
DED NUMERIC (8, 2),
NET NUMERIC (8, 2)
);



                                         Output
The command(s) completed successfully.
                      1(B) Alter a Table to add a column
ALTER TABLE DEMO_STUDENT ADD SEMESTER VARCHAR (10);
ALTER TABLE DEMO_STUDENT ADD COURSE_CODE VARCHAR (10);




                                         Output
The command(s) completed successfully.
              1(B) INSERTING DATA'S INTO THE TABLE
INSERT INTO DEMO_STUDENT VALUES('001','A ANTO',45,55,200,NULL,NULL)
INSERT INTO DEMO_STUDENT VALUES('002','ASLAM',99,98,200,NULL,NULL)
INSERT INTO DEMO_STUDENT VALUES('003','KARTHI',98,99,200,NULL,NULL)
INSERT INTO DEMO_STUDENT VALUES('004','MAGANDRAN',100,100,200,NULL,NULL)
INSERT INTO DEMO_STUDENT VALUES('005','BALAJI',98,90,200,NULL,NULL)
INSERT INTO DEMO_STUDENT VALUES('006','DINESH',99,92,200,NULL,NULL)
INSERT INTO DEMO_STUDENT VALUES('007','VIMAL',30,34,200,'THIRD','C04')

INSERT INTO PASSED_OUT_STUDENT VALUES('P01','JOHNSON',45,55,200)
INSERT INTO PASSED_OUT_STUDENT VALUES('P02','KANNAN',99,98,200)
INSERT INTO PASSED_OUT_STUDENT VALUES('P03','JOHNNIRMAR',98,99,200)
INSERT INTO PASSED_OUT_STUDENT VALUES('P04','MANOJ',100,100,200)


INSERT INTO COURSE_DETAILS VALUES('C01','BSC',3,45000);
INSERT INTO COURSE_DETAILS VALUES('C02','BCA',3,95000);
INSERT INTO COURSE_DETAILS VALUES('C03','MSC',2,30000);
INSERT INTO COURSE_DETAILS VALUES('C04','MCA',3,145000);
INSERT INTO COURSE_DETAILS VALUES('C05','BE',4,445000);

INSERT INTO EMPLOYEES VALUES ('HTC','E001','A ANTO','Mr', 10000, 1000, 1000, 1000,
1200, 12000, 2200, 19800)
INSERT INTO EMPLOYEES VALUES ('HTC','E002', 'ASLAM','Mr', 20000, 2000, 2000, 2000,
2200, 22000, 4400, 19600)
INSERT INTO EMPLOYEES VALUES ('HTC','E003','KARTHIK','Mr', 15000,1500,1500,1500,
1800, 18000, 2800, 15200)
INSERT INTO EMPLOYEES VALUES ('HTC','E004','MAGAHNDARAN','Mr', 20000, 2000, 2000,
2000, 2200, 22000, 4400,19600)
INSERT INTO EMPLOYEES VALUES('HTC','E005','PRIYA','Ms',15000,1500,1500,1500,
1800,18000,2800,15200)




                                    Output
(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)
             1(C) UPDATEING DATA'S INTO THE TABLE
UPDATE DEMO_STUDENT SET ST_NAME = 'MAGANDHARAN' WHERE ST_NO = '004';

UPDATE DEMO_STUDENT SET SEMESTER = 'FIRST' WHERE ST_NO IN ('001','002')
UPDATE DEMO_STUDENT SET SEMESTER = 'SECOND' WHERE ST_NO IN ('003','004')
UPDATE DEMO_STUDENT SET SEMESTER = 'THIRD' WHERE ST_NO IN ('005','006')

UPDATE DEMO_STUDENT SET COURSE_CODE = 'C01' WHERE ST_NO IN ('001','006')
UPDATE DEMO_STUDENT SET COURSE_CODE = 'C02' WHERE ST_NO IN ('005')
UPDATE DEMO_STUDENT SET COURSE_CODE = 'C03' WHERE ST_NO IN ('004')
UPDATE DEMO_STUDENT SET COURSE_CODE = 'C04' WHERE ST_NO IN ('003')
UPDATE DEMO_STUDENT SET COURSE_CODE = 'C05' WHERE ST_NO IN ('002')




                                  Output
(1 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)
      2 (A) SELECT STATEMENT USING AND,OR,NOT
               OPERATORS, WHERE CLAUSE

SELECT * FROM DEMO_STUDENT



                                Output




SELECT * FROM DEMO_STUDENT WHERE ST_NO='001' AND ST_MARK1 = 45

                                Output



SELECT * FROM DEMO_STUDENT WHERE ST_MARK1 = 99 OR ST_MARK2 = 99


                                Output




SELECT * FROM DEMO_STUDENT WHERE NOT ST_MARK1 = 99

                                Output
         2 (B) SELECT STATEMENT USING UNION,
                  INTERSECTION,MINUS
SELECT * FROM DEMO_STUDENT
UNION
SELECT ST_NO,ST_NAME,NULL,NULL,MAX_MARK,NULL,NULL FROM
PASSED_OUT_STUDENT

                              Output




SELECT * FROM DEMO_STUDENT
INTERSECTION
SELECT * FROM DEMO_STUDENT_TEMP

                              Output
SELECT * FROM DEMO_STUDENT
MINUS
SELECT * FROM DEMO_STUDENT_TEMP




                              Output
     2 (C) SELECT STATEMENT USING SORTING AND
                      GROUPING
SELECT * FROM DEMO_STUDENT ORDER BY ST_NAME
SELECT * FROM DEMO_STUDENT ORDER BY ST_NAME DESC




                              Output
SELECT
     ST_MARK1,
     COUNT (ST_NAME) AS COUNT
FROM
     DEMO_STUDENT
GROUP BY
     ST_MARK1



SELECT
     SEMESTER,
     SUM (ST_MARK1) AS TOTAL_MARK1,
     SUM (ST_MARK2) AS TOTAL_MARKS2
FROM
     DEMO_STUDENT
GROUP BY
     SEMESTER



                                Output
     3 (A) NESTED QUERIES USING SQL SUB QUERIES


SELECT * FROM DEMO_STUDENT
WHERE
     COURSE_CODE IN (
            SELECT
                 COURSE_CODE
            FROM
                 COURSE_DETAILS
            WHERE
                 COURSE_FEE <=45000
                     )



                               Output
            3 (B) NESTED QUERIES USING JOINS

SELECT
     DS.ST_NAME,
     DS.SEMESTER,
     CD.COURSE_DESC,
     CD.COURSE_FEE
FROM
     DEMO_STUDENT AS DS,
     COURSE_DETAILS CD
WHERE
     DS.COURSE_CODE = CD.COURSE_CODE AND
     CD.COURSE_FEE >=50000


                              Output
                4 BUILT IN FUNCTIONS OF SQL
SELECT GETDATE () AS TODAYS_DATE_AND_TIME

SELECT AVG (COURSE_FEE) AS COURSE_AVERAGE FROM COURSE_DETAILS

SELECT COUNT (*) AS COUNT_OF_COURSE FROM COURSE_DETAILS

SELECT
     'COURSE FEE DETAILS',
     MAX (COURSE_FEE) MAX_FEE,
     MIN (COURSE_FEE) MIN_FEE,
     SUM (COURSE_FEE) FEE_SUM,
     AVG (COURSE_FEE) AVG_FEE
FROM
     COURSE_DETAILS




                                 Output
                             5(A) USE OF INDEXES


CREATE INDEX DEMO_AVG ON DEMO_STUDENT (ST_NAME)

SELECT * FROM DEMO_STUDENT

DROP INDEX DEMO_STUDENT.DEMO_AVG



                                         Output
The command(s) completed successfully.




The command(s) completed successfully.
                               5(B) USE OF VIEWS
CREATE VIEW STUDENT_COURSE_DETAIL
AS
SELECT
     DEMO_STUDENT.ST_NAME,
     DEMO_STUDENT.SEMESTER,
     COURSE_DETAILS.COURSE_DESC,
     COURSE_DETAILS.COURSE_DURATION,
     COURSE_DETAILS.COURSE_FEE
FROM
     DEMO_STUDENT,
     COURSE_DETAILS
WHERE
     DEMO_STUDENT.COURSE_CODE = COURSE_DETAILS.COURSE_CODE



SELECT * FROM STUDENT_COURSE_DETAIL

SELECT ST_NAME, COURSE_FEE FROM STUDENT_COURSE_DETAIL



                                         Output
The command(s) completed successfully.
                        6(A) USING CURSORS
DECLARE
     CURSOR STUDENT_DETAILS IS SELECT ST_NAME,ST_MARK1,ST_MARK2 FROM
     DEMO_STUDENT;

     V_NAME DEMO_STUDENT.ST_NAME%TYPE;
     V_MARK1 DEMO_STUDENT.ST_MARKS1%TYPE;
     V_MARK2 DEMO_STUDENT.ST_MARKS2%TYPE;
     BEGIN
           OPEN STUDENT_DETAILS
           LOOP
           FETCH STUDENT_DETAILS INTO V_NAME,V_MARKS1,V_MARK2;
           EXIT WHEN STUDENT_DETAILS%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE (V_NAME || ' ' || V_MARK1 || ' ' || V_MARK2);
           END LOOP;

            CLOSE STUDENT_DETAILS;
     END;

                                   Output
                             6(B) USING TRIGGERS

CREATE OR REPLACE TRIGGER AUDIT_TRIGGER_BILLS
     BEFORE INSERT OR DELETE OR UPDATE
     ON BILLS FOR EACH ROW

BEGIN
IF INSERTING THEN
       INSERT INTO AUDIT_LOG VALUES (USER, SYSDATE,'I',’BILLS’, NEW.BILL_NO);

ELSIF UPDATING THEN
       INSERT INTO AUDIT_LOG VALUES (USER, SYSDATE,'U',’BILLS’, NEW.BILL_NO);

ELSIF DELETING THEN
       INSERT INTO AUDIT_LOG VALUES (USER, SYSDATE,'D',’BILLS’, OLD.BILL_NO);
END IF;

END;




                                         Output
The command(s) completed successfully.
                          6(C) USING PROCEDURES

CREATE OR REPLACE PROCEDURE PR_INSERT_CLERK
      (P_CLERK_NO VARCHAR2, P_NAME VARCHAR2, PIO_CNO IN OUT NUMBER)
AS
BEGIN
       INSERT INTO CLERKS (CLERK_NO, NAME) VALUES (P_CLERK_NO, P_NAME);
       IF SQL%FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO OF ROW(S) INSERTED IS'|| ' '||SQL%ROWCOUNT);
       PIO_CNO:=PIO_CNO+1;
       END IF;
END;


SELECT * FROM CLERKS

EXEC PR_INSERT_CLERK 12,'ASLAM'

SELECT * FROM CLERKS
                                         Output
The command(s) completed successfully.
                            6(D) USING FUNCTION

CREATE OR REPLACE FUNCTION FN_GET_COURSE_FEE (PI_COURSE_DESC VARCHAR)
                 RETURN NUMBER
AS
      V_FEE NUMBER (5);
BEGIN
      SELECT COURSE_FEE INTO V_FEE FROM COURSE_DETAILS WHERE
      COURSE_DESC=PI_COURSE_DESC;
      RETURN V_FEE;
END;



SELECT FN_GET_COURSE_FEE ('BSC') AS FEE FROM DUAL;




                                         Output
The command(s) completed successfully.
CASE STUDIES:
               7(A) STUDENT EVALUATION SYSTEM.

CREATE OR REPLACE FUNCTION FN_CHECK_STUDENT_PASS (PI_ST_NUMBER
                                                   VARCHAR (5))
     RETURN VARCHAR (35)
AS
     V_TOTAL BILLS.BILL_VALUE%TYPE;
     TOTAL NUMBER;
     RESULT VARCHAR (35);
     ST_NAME VARCHAR (25);

BEGIN
        SELECT ((ST_MARK1 + ST_MARK2), ST_NAME) INTO (@TOTAL, ST_NAME) FROM
        DEMO_STUDENT WHERE ST_NO = PI_ST_NUMBER

        IF (TOTAL/2 > 35) THEN
               RESULT = 'THE STUDENT "' || ST_NAME || '" HAVE PASSED';
        ELSE
               RESULT = 'THE STUDENT "' || ST_NAME || '" HAVE FAILED';
        END IF;
        RETURN RESULT;
END;

SELECT * FROM DEMO_STUDENT;
SELECT FN_CHECK_STUDENT_PASS ('001') FROM DUAL;
SELECT FN_CHECK_STUDENT_PASS ('007') FROM DUAL;



                                         Output
The command(s) completed successfully.
                             7(B) PAY ROLL SYSTEM
CREATE OR REPLACE PROCEDURE [PR_GET_PAY_ROLL]
AS
CURSOR EMP_DETAIL IS
     SELECT
           COMPANY_NAME,
           EMPLOYEE_ID,
           EMPLOYEE_NAME,
           TITLE_OF_COURTESY,
           BASIC_SAL, HRA, DA, LIC, DF, GROSS, DED, NET FROM EMPLOYEES;

COMPANY_NAME VARCHAR (3);
EMPLOYEE_ID CHAR (5);
EMPLOYEE_NAME VARCHAR (15);
TITLE_OF_COURTESY VARCHAR (15);
BASIC_SAL NUMBER;
HRA NUMBER;
DA NUMBER;
LIC NUMBER;
DF NUMBER;
GROSS NUMBER;
DED NUMBER;
NET NUMBER;
BEGIN
      OPEN EMP_DETAIL
      LOOP
            FETCH EMP_DETAIL INTO COMPANY_NAME, EMPLOYEE_ID,
            EMPLOYEE_NAME, TITLE_OF_COURTESY
            BASIC_SAL, HRA, DA, LIC, DF, GROSS, DED, NET;

       DBMS_OUTPUT.PUT_LINE ('--------------------PAY ROLL------------------------')
       DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------')
       DBMS_OUTPUT.PUT_LINE ('               ' || COMPANY_NAME || '                   ')
       DBMS_OUTPUT.PUT_LINE ('EMPNo EMP NAME                    BASIC HRA            DA        LIC
                                DF        GROSS DED NET')
       DBMS_OUTPUT.PUT_LINE ( EMPLOYEE_ID || ' ' || TITLE_OF_COURTESY || '.' ||
                                EMPLOYEE_NAME || ' ' || BASIC_SAL ||
                                ' ' || @HRA || ' ' || @DA || ' ' || LIC || ' ' || DF || ' ' ||
                                @GROSS || ' ' || @DED || ' ' || @NET);
       EXIT WHEN EMP_DETAIL%NOTFOUND;
       END LOOP;

       CLOSE EMP_DETAIL;
END;

EXEC PR_GET_PAY_ROLL;
                                         Output
The command(s) completed successfully.
            7(C) PERSONNEL INFORMATION SYSTEMS
CREATE OR REPLACE PROCEDURE PR_GET_EMP_DETAILS
      (PI_EMP_ID VARCHAR2 (5))
AS
BEGIN
       SELECT
            TITLE_OF_COURTESY,
            EMPLOYEE_NAME,
            BASIC_SAL,
            NET
      FROM
            EMPLOYEES
      WHERE
            EMPLOYEE_ID = PI_EMP_ID;
END;

EXEC PR_GET_EMP_DETAILS 'E001'




                                         Output
The command(s) completed successfully.
                   7(D) INCOME TAX CALCULATION
CREATE OR REPLACE PROCEDURE PR_GET_INCOMETAX (PI_ANNUAL_SAL NUMBER)
RETURN
AS
BEGIN
      IF (PR_GET_INCOMETAX < 100000) THEN
             DBMS_OUTPUT.PUT_LINE (' THERE IS NOT INCOMETAX FOR U ')
      ELSE IF( (@PI_ANNUAL_SAL > 100000) && (@PI_ANNUAL_SAL < 500000)) THEN
             DBMS_OUTPUT.PUT_LINE (' THE INCOMETAX AMOUNT IS ' +
                                       (@PI_ANNUAL_SAL * 2)/100
      ELSE IF( (@PI_ANNUAL_SAL > 500000) && (@PI_ANNUAL_SAL < 750000)) THEN
             DBMS_OUTPUT.PUT_LINE (' THE INCOMETAX AMOUNT IS ' +
                                       (@PI_ANNUAL_SAL * 5)/100
      ELSE
             DBMS_OUTPUT.PUT_LINE (' THE INCOMETAX AMOUNT IS ' +
                                       (@PI_ANNUAL_SAL * 8)/100
      END IF;
END;

EXEC PR_GET_INCOMETAX 799999




                                         Output
The command(s) completed successfully.
                    7(E) MARK SHEET PREPARATION
CREATE OR REPLACE PROCEDURE [PR_PREPARE_MARK_SHEET]
AS
     CURSOR STUDENT_DETAIL IS
     SELECT
           ST_NO,
           ST_NAME,
           ST_MARK1,
           ST_MARK2,
           MAX_MARK,
           COURSE_DESC
     FROM
           DEMO_STUDENT,
           COURSE_DETAILS
     WHERE
           DEMO_STUDENT.COURSE_CODE = COURSE_DETAILS.COURSE_CODE;

ST_NO VARCHAR (4);
ST_NAME CHAR (25);
ST_PAPER1 NUMBER;
ST_PAPER2 NUMBER;
ST_MAX_MARK NUMBER;
COURSE_DESC VARCHAR (25);
RESULT VARCHAR (5);

BEGIN
        OPEN STUDENT_DETAIL
        LOOP
        FETCH STUDENT_DETAIL INTO @ST_NO , @ST_NAME , @ST_PAPER1 ,
                         @ST_PAPER2 , @ST_MAX_MARK , @COURSE_DESC;

        DBMS_OUTPUT.PUT_LINE('              MARK SHEET               ');
        DBMS_OUTPUT.PUT_LINE('              ---- -----       ');
        DBMS_OUTPUT.PUT_LINE('NAME : ' || @ST_NAME || '          ');
        DBMS_OUTPUT.PUT_LINE('ST_NUMBER PAPER1 PAPER2 MAX_MARK
                                                          COURSE PASS/FAIL ');
        DBMS_OUTPUT.PUT_LINE( @ST_NO || ' ' || STR(@ST_PAPER1) || ' ' ||
                    STR(@ST_PAPER1) || ' ' || STR(@ST_MAX_MARK) ||
                    ' ' || @COURSE_DESC || ' ' || @RESULT);
        EXIT WHEN STUDENT_DETAIL%NOTFOUND;

        END LOOP;

        CLOSE STUDENT_DETAIL;
END;



EXEC PR_PREPARE_MARK_SHEET;
                                         Output
The command(s) completed successfully.