Oracle SQL, PL/SQL Lab Practice

-

Oracle lab practising for PGDICT4D 11 batch-2021

/*

SELECT * FROM TAB;


SELECT SYSDATE FROM DUAL;


SELECT 8+4 FROM DUAL;


SELECT 100*50 FROM DUAL;


SELECT UPPER('rashid') from dual;


cl scr;


SELECT SUBSTR('Bangladesh',1,4) FROM DUAL;


SELECT ROUND (120.5897,2) FROM DUAL;


--SELECT RPAD(roll,10,'*') FROM student;


--SELECT LPAD(100,10,'*') FROM DUAL;


-- Creating table


CREATE TABLE school (sc_code number(3), sc_name varchar2(30), sc_location char(20), 

sc_union varchar2(20), sc_upazila varchar2(20));


-- Inserting data in a table


INSERT INTO school 

VALUES (100, 'Basbari High School','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (101, 'Jahir High Scholl','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (102, 'Basbari High School','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (500, 'Basbari Dakhil Madrasa','Sabaihat','Tentulia','Manda');



DROP TABLE school;


CREATE TABLE school (sc_code number(3) PRIMARY KEY, sc_reg_date date(), sc_name varchar2(30), sc_location char(20), 

sc_union varchar2(20), sc_upazila varchar2(20));


DESC school;

INSERT INTO school 

VALUES (100, 'Basbari High School','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (101, 'Jahir High Scholl','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (102, 'Basbari High School','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (103, 'Proshadpur','Sabaihat','Tentulia','Manda');


INSERT INTO school 

VALUES (500, 'Cantonment Dakhil Madrasa','Sabaihat','Tentulia','Manda');

INSERT INTO school 

VALUES (501, 'Basbari Dakhil Madrasa','Sabaihat','Tentulia','Manda');

INSERT INTO school 

VALUES (500, 'Manda Dakhil Madrasa','Sabaihat','Tentulia','Manda');

INSERT INTO school 

VALUES (502, 'Sabai Hat Dakhil Madrasa','Sabaihat','Tentulia','Manda');


CL SCR;

UPDATE school

SET sc_name='Mirpur Fazil Madrasa'

WHERE sc_code=500;



--SELECT * FROM school;


DELETE FROM school

WHERE sc_code=500;



SELECT sc_code,sc_name,sc_location FROM batch11.school

WHERE sc_code=500;

*/


Drop table school;


-- Creating a table with a date field

CREATE TABLE school (sc_code number(3) PRIMARY KEY, sc_reg_date date, sc_name varchar2(30), sc_location char(20), 

sc_union varchar2(20), sc_upazila varchar2(20));

DESC school;


-- Inserting current date

INSERT INTO school 

VALUES (100,sysdate, 'Basbari High School','Sabaihat','Tentulia','Manda');

-- Inserting customized date

INSERT INTO school 

VALUES (102,TO_DATE('05 Aug, 2021', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');


SELECT sc_code, sc_reg_date,sc_name,sc_upazila 

FROM school;


-- Trigger Practice

 

What is Trigger?

Triggers are stored programs that are fired automatically when some events occur. The code to be fired can be defined as per the requirement.

Oracle has also provided the facility to mention the event upon which the trigger needs to be fire and the timing of the execution.

What are the Trigger benefits?

•        Generating some derived column values automatically

•        Enforcing referential integrity

•        Event logging and storing information on table access

•        Auditing

•        Imposing security authorizations

•        Preventing invalid transactions

What is SCHEMA?

In Oracle, USER is the account name; SCHEMA is the set of objects owned by that user.

Even though, Oracle creates the SCHEMA object as part of the CREATE USER statement and the SCHEMA has the same name as the USER but they are note the same thing.

 

--Trigger Practice

-- Access tracking

CREATE TABLE log_trig_table

     (user_id varchar2(8), log_date date, action varchar2(20));

 

CREATE OR REPLACE TRIGGER logon_trig

      AFTER LOGON ON SCHEMA

     BEGIN

            INSERT INTO  log_trig_table

            (user_id,log_date, action)

            VALUES (user, sysdate, 'LOGGING ON ');

  END;

/

-- Please logout and login several  times

SELECT *  FROM log_trig_table;

-- DML tracking

-- Create Table

CREATE TABLE dml_log_trig_table

    (user_name varchar2(8), dml_date date, dml_type varchar2(8));

-- Insert

CREATE OR REPLACE TRIGGER insert_trig

AFTER INSERT ON school

BEGIN

            INSERT INTO  dml_log_trig_table

            (USER_NAME,DML_DATE, DML_TYPE)

            VALUES (USER,SYSDATE,'INSERT');

END;

/


-- Update

CREATE OR REPLACE TRIGGER update_trig

AFTER UPDATE ON school

BEGIN

            INSERT INTO  dml_log_trig_table

            (USER_NAME,DML_DATE, DML_TYPE)

            VALUES (USER,SYSDATE,'UPDATE');

END;

/

-- Delete

CREATE OR REPLACE TRIGGER delete_trig

AFTER INSERT ON school

BEGIN

            INSERT INTO  dml_log_trig_table

            (USER_NAME,DML_DATE, DML_TYPE)

            VALUES (USER,SYSDATE,'DELETE');

END;

/

 

-- Query to test result

SELECT * FROM dml_log_trig_table;

 

-- View  your trigger information

SELECT *  FROM USER_TRIGGERS

WHERE TABLE_NAME='LOG_TRIG_TABLE';


-- Enabling or Disabling Trigger

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE]; 



-- Cursor Practice

What is PL/SQL Cursor?

The purpose of the cursor is to make a quarry in a table of a database and change (DML) the table column of a database.


-- Lab Practice

DROP TABLE student;

CREATE TABLE student (roll NUMBER(6) PRIMARY KEY, name VARCHAR(20), sex CHAR(1), sub1 NUMBER(2), sub2 NUMBER(2), total NUMBER(3), result CHAR(2));


-- Data insert into student table

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100000,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100001,'Md. Abdul Khalek','0',65,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100002,'Most. Dilruba Aktar','1',95,56);

 INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100003,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100004,'Md. Abdul Khalek','0',65,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100005,'Shahina Sultana','1',95,66);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100006,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100007,'Md. Abdul Khalek','0',65,36);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100008,'Most. Dilruba Aktar','1',85,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100009,'RASHID','0',45,56);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100010,'Md. Abdul Khalek','0',45,76);

INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100011,'Most. Aklima Khatun','1',95,56);

cl scr;

SELECT roll, sub1, sub2, total FROM student;

 -- Asignment for making total marks in table student

SET SERVEROUTPUT ON

DECLARE

               v_total number(3):=0;

               v_result varchar2(2);

               CURSOR student_cursor  IS

                              SELECT roll,sub1,sub2, total

                              FROM student;  

               student_record student_cursor%ROWTYPE;          

BEGIN

               OPEN student_cursor;

               LOOP

               FETCH student_cursor INTO student_record;

               EXIT WHEN student_cursor%ROWCOUNT>70 OR student_cursor%NOTFOUND;

               UPDATE student

               SET student.total=student_record.sub1+student_record.sub2

               WHERE student.roll=student_record.roll;

               END LOOP;

               COMMIT;

               CLOSE student_cursor;

END;

/

cl scr;

SELECT student.roll, student.name, student.total FROM student;



-- Asignment for making grading grading in table student

SET SERVEROUTPUT ON

DECLARE

               v_total number(3):=0;

               v_result varchar2(2);

               CURSOR grade_cursor IS

                              SELECT roll,total

                              FROM student;  

               student_grade grade_cursor%ROWTYPE;              

BEGIN

               OPEN grade_cursor;

               LOOP

               FETCH grade_cursor INTO student_grade;

               EXIT WHEN grade_cursor%ROWCOUNT>70 OR grade_cursor%NOTFOUND;

               v_total:=student_grade.total;

               IF v_total>=140 THEN

                                             v_result:='A+';

               ELSIF v_total>=100 THEN

                                             v_result:='A-';

               ELSE

                                             v_result:='F';

               END IF;

               update student

               set student.result=v_result

               WHERE student.roll=student_grade.roll;

               END LOOP;

               COMMIT;

               CLOSE grade_cursor;

END;

/

CL SCR;

SELECT student.roll, student.name, student.total, student.result FROM student;


The writer is an Oracle Certified Professional, trainer, and PhD Fellow in BUP and can be reached at ‘rashid.eee.cse@gmail.com’.

Comments

  1. Md. Nasarul HasanAugust 6, 2021 at 9:36 PM

    Respected sir, I completed this commands as a practice. It is very useful and helpful for us in this course.

    ReplyDelete
  2. Md. Solaiman GaziAugust 7, 2021 at 7:56 AM

    Dear Sir, I have completed the above test and also practiced it with a different table name. Tests will be helpful in creating tables in the future and entering value into the table. Thank you very much for giving the test in the blog.

    ReplyDelete
  3. jahirAugust 7, 2021 at 10:50 AM

    Sir, I can make a table but practice in the query showed as ' name of school used as object' earlier. I did not understand it clearly. Pls show us the query as practice in the next. Thanking you.

    ReplyDelete
  4. UnknownAugust 8, 2021 at 12:41 AM

    Sir, I've completed my lab practice. Thank you sir.

    ReplyDelete
  5. Mohammed Jowel RanaAugust 10, 2021 at 9:07 PM

    Dear Sir, It's a good Discover for creating tables and giving opinion in the blog.

    ReplyDelete
    Replies
    1. Dr. Engr. Md. Abdur RashidAugust 11, 2021 at 7:18 AM

      Thanks. I am trying to measure the effectiveness of blog to improve the training in future.

      Delete

Post a Comment

Popular posts from this blog

Differences between the terms Science, Technology, and Engineering

-
What are the differences between the terms Science, Technology, and Engineering? Dr. Engr. Md. Abdur Rashid Director (Admin & Finance) NAPD Science Focus: The systematic study of the natural world through observation, experimentation, and analysis. Goal: To understand and explain the universe, its laws, and its phenomena. Methods: Scientific inquiry involves formulating hypotheses, conducting experiments, collecting data, and drawing conclusions. Examples: Physics, chemistry, biology, astronomy, geology. Technology Focus: The application of scientific knowledge to create tools, machines, and systems that solve problems or fulfill human needs. Goal: To improve efficiency, productivity, and quality of life. Methods: Technological development often involves innovation, design, and engineering. Examples: Computers, smartphones, automobiles, medical devices, renewable energy systems. Eng...
Read more

Developing Personal Skills

-
  Developing Personal Skills Engr. Md. Abdur Rashid  PhD, NAPD Dear Officers and Staff, you must establish and ensure the long-term growth of your organization. Maintain the following managerial abilities in order to enhance personal skills on a daily basis while working. 1.                  1)       Self-assessment 2)       Creativity 3)       Innovation 4)       Listening and feedback 5)       Find self-satisfaction 6)       Leadership 7)       Team building 8)       Hunt knowledge 9)       Self-reliance and judgment 10)    Resilience 11)    Time management 12)    Stress management 13)    Control over anything 14)    Find satisfa...
Read more

PhD Tips

-
  PhD Tips Dr. Engr. Md. Abdur Rashid Director (Admin & Finance), NAPD I’d like to share my personal experiences with the PhD journey from my perspective- 1. A PhD doesn't demand extraordinary intelligence, but rather an immense amount of patience and perseverance. 2. The scope of your research will not be vast, but rather a very specific and narrow focus. 3. Your supervisor cannot do everything for you; they serve as a guiding umbrella. Ultimately, you will be your own supervisor. 4. Keeping a PhD diary is essential. Write down every thought, idea, or insight as soon as it comes to you—even after leaving the washroom. Make sure to follow through on each task and mark it off once completed. 5. From day one to the very end, adhere strictly to the guidelines provided by your university and supervisor. Review other PhD reports to enhance the quality of your work. 6. Avoid being overly ambitious about publishing in renowned journals. It takes time. Stick to your university...
Read more