Introduction to Database SQL & PL/SQL

SQL stands for Structured Query Language.
SQL is used to communicate with a database.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
SQL commands are divided into several different types, among them data manipulation language (DML) and data definition language (DDL) statements, transaction controls and security measures.

1. Introduction to Database www.collaborationtech.co.in Bengaluru INDIA Presentation By Ramananda M.S Rao

2. Content Content Overview Database Concepts Database Fundamentals Introduction to Database Management Systems Different Models Three Layer Architecture Data Independence, DDL, DML, DCL, Functions of DBA, DBM Introduction to ORACLE technology stack SQL Language Introduction & Using SQL*Plus SQL History and Standards, SQL Basics SQL Data Types Writing Basic SQL statements Creating and Managing Tables Insert, Update, Delete commands Alter, Drop commands Select Command Constraints www.collaborationtech.co.in

3. Content SQL Operators and Functions Single row functions Aggregating data using group functions Group By clause Set Operators Joins Creating Views Subquery Nested Queries Co-related Sub-queries Controlling user access Grant and Revoke Statements Optimization and Performance SQL Tuning www.collaborationtech.co.in

4. Content ER Model Assignments on E-R Model E-R to Relational Mapping Assignments on E-R to Relational Mapping Normalization Assignments on Normalization Using simple modeling Tool Building model for Simple Applications Simple Fund Management example Simple Inventory example Simple Security example PL/SQL Programming PL/SQL Variables and Constants Using %TYPE and %ROWTYPE Attributes PL/SQL Variable Scope Creating Anonymous PL/SQL Block Using DBMS_OUTPUT.PUT_LINE PL/SQL Control Structures and Variables Writing Interactive PL/SQL program Embedding SELECT Statement inside PL/SQL Block, Embedding DML Statements inside PL/SQL Block www.collaborationtech.co.in

5. Content Cursors and Exceptions Cursors - Definition Cursor Using Implicit and Explicit Cursors Cursor Attributes, Cursor FOR loops, Parameterized Cursors FOR UPDATE and WHERE CURRENT OF with Explicit Cursors Exception Definition Handling user defined, Oracle predefined and non-predefined exceptions. Propagating exceptions Procedures , functions and Triggers Creating Stored Procedures and functions Invoking stored procedures Parameter Modes – IN, OUT and IN OUT Calling Stored Functions Packages Definition Packages Advantages of using Packages Components of a Package Creating and using Package www.collaborationtech.co.in

6. Content Triggers - Definition Triggers Database Triggers Difference between Stored Procedure and Database Triggers Trigger Components and types Creating and using Database Triggers Objects In database Objects Types and Using Object Table Creating and Using Collection Types Dynamic SQL Native Dynamic SQL Using Packages like DBMS_OUTPUT, DBMS_PIPE, UTL_FILE, DBMS_SQL Bulk statements in PL/SQL Creating Indexes www.collaborationtech.co.in

7. Overview SQL stands for Structured Query Language . SQL is used to communicate with a database . SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. SQL commands are divided into several different types, among them data manipulation language (DML) and data definition language (DDL) statements, transaction controls and security measures . The DML vocabulary is used to retrieve and manipulate data. DDL statements are for defining and modifying database structures. www.collaborationtech.co.in

8. Overview PL/SQL is an Oracle procedural extension for SQL. They have designed this language for easy use of complex SQL statements . PL SQL basically stands for "Procedural Language extensions to SQL ". It combines the data manipulation power of SQL with the processing power of procedural language to create a super powerful SQL queries . Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object oriented concepts. www.collaborationtech.co.in

9. SQL SQL> create database student SQL>show database SQL>create table employee (id int (11),ename varchar(45),address varchar(45 ), Dob date); SQL>insert into employee values(1,’raj’,’vijayanagar bengaluru’,’02-02-2017’); SQL>desc employee SQL>select * from employee SQL > update employee set ename=‘ Sunilkumar ' where id=1; SQL>DELETE FROM employee WHERE id=1; www.collaborationtech.co.in

10. SQL SQL>create table student (id int (11 ), sname varchar(45),address varchar(45 ), Dob date,age int (11)); SQL>insert into employee values(1 ,’kumar’, ’vijayanagar mysore’,’01-02-2017’,20); SQL>insert into employee values(2,’sunil’, ’vijayanagar bangalore’,’05-02-2017’,25); SQL>desc student SQL>select * from student SQL> update student set ename =‘ Anilkumar ' where id=2; SQL>DELETE FROM student WHERE id=1 ; SQL>create table customer (id int (11 ), cname varchar(45),address varchar(45 ), mno varchar(45 ) ); SQL>insert into customer values(1,’chetan’,’btm bangalore’,’9865324175’); SQL>insert into customer values(2,guru’,’rpc layout bangalore’,’9586231475’); SQL>desc customer SQL>select * from customer SQL> update customer set ename =‘Vishal' where id=2; SQL>DELETE FROM customer WHERE id=1; Alter Command ALTER TABLE customer ADD Gender char(1); ALTER TABLE customer MODIFY Location char(100); www.collaborationtech.co.in

11. PL/SQL BEGIN d bms_output.put_line (‘Hello World..’); END; \ Declaring and usage of variables in program DECLARE text VARCHAR2(45); BEGIN t ext:= ‘Hello World’; dbms_output.put_line (text); END; \ www.collaborationtech.co.in

12. Ratio declare numerator number;denominator number; the_ratio number; lower_limit constant number:=0.72; samp_num constant number:=132; BEGIN SELECT X, Y INTO numerator, denominator from result_table where sample_id = samp_num ; the_ratio := numerator/denominator; if the_ratio > lower_limit then insert into ratio values( samp_num , the_ratio ); ELSE Insert into ratio values (samp_num,-1); END IF;commit ; exception when zero_divide then insert into ratio values(samp_num,0); commit;when others then rollback; end ; www.collaborationtech.co.in

13. Wages CREATE FUNCTION dept-sal ( dnum NUMBER) RETURN NUMBER IS CURSOR emp -cursor ISo SELECT sal , comm FROM emp WHERE deptno = dnum ; total-wages NUMBER(,(:=0; cnt NUMBER(10) :=1; BEGIN FOR emp -record IN emp -cursor LOOP emp-record.comm := NVL ( emp-record.comm , 0); total-wages := total-wages + emp.record , sal+emp-record-comm ; PUT-LINE (LOOP number = '|| cnt ||', 'wages ='|| TO-CHAR (total-wages)); cnt := cnt+1; ENDLOOP: /* Debug Line */ PUT-LINE (Total wages ='|| TO-CHAR (total-wages)); RETURN total-wages; END dept-sal : www.collaborationtech.co.in

14. Procedures and Functions in PL/SQL Procedure is a subprogram unit that consists of a group of PL/SQL statements.Procedure can have a RETURN tatement to return the control to the calling block, but it cannot return any values through the RETURN statement. CREATE OR REPLACE PROCEDURE welcome_msg ( p_name IN VARCHAR2) IS BEGIN Dbms_output.put_line (‘Welcome’||p_name); END \ EXEC welcome_msg (‘Collaboration Technologies’); www.collaborationtech.co.in

15. Follow us on Social Facebook: https://www.facebook.com/collaborationtechnologies / Twitter : https:// twitter.com/collaboration09 Google Plus : https:// plus.google.com/100704494006819853579 LinkedIn : https://www.linkedin.com/in/ramananda-rao-a2012545 Instagram : https:// instagram.com/collaborationtechnologies YouTube : https ://www.youtube.com/channel/UCm9nK56LRbWSqcYWbzs8CUg Skype : facebook:ramananda.rao.7 WhatsApp : [phone] www.collaborationtech.co.in THANK YOU

16. About Us