oracle

 

https://selfstudycp.blogspot.com/2021/09/sql-interview-questions.html


                               -: Summary :-

SQL> select sysdate,systimestamp from dual;

SYSDATE   SYSTIMESTAMP
--------- ---------------------------------------------------------------------------
24-NOV-21 24-NOV-21 03.11.45.205000 PM +05:30
convert Month no to Month Name:
SQL> select TO_CHAR(TO_DATE(2, 'MM'), 'MONTH')  from dual;

TO_CHAR(TO_DATE(2,'MM'),'MONTH')
------------------------------------
FEBRUARY
DDL(Data Defintion Language): create,Alter,Truncate,Rename,Drop,FlashBack,Purge,Comment
  • Create: 
CREATE TABLE emp( empNo Number(5),Ename varchar2(20),Date_Of_Birth Date,Sal Number(7,2) );

           create table using existing tables (with data):
     create table emp1 as select * from emp;
          create table using existing tables  (with data): but selected columns
create table emp2 as select empNo,Ename,Sal from emp;
or
create table emp2 as select empNo,Ename,Sal from emp
where 1=1;        (1=1 means any True condtion)
  create table using existing tables (only structure):
create table emp3 as select empNo,Ename,Sal from emp
where 1=2;

  (1=2 means any False condtion)

  • Truncate:  All rows deleted no RollBack
truncate table emp; jknjn===

  • Rename:  old name to new name
                                                              Rename table
rename emp to Employees;                    
                                                              Rename column(from version 9.2)
Alter table emp Rename column empNo to ENo;  

 remember:column keyword is required here
                                                               Rename constraint(from vesion 9.2)
Alter table emp rename constraint emp_Eno_pk to emp_pk;        

  • Drop:  
                                                               drop table
drop table emp;                         m
                                                               drop single column
Alter table emp2 drop column empNo;            
or
Alter table emp2 drop(empNo);                  
                                                               drop multiple column but not all
Alter table emp2 drop(Ename,Sal);              
                                                               change data type of column
 alter table lms_repaysch_dtl modify  BALANCE_PRINCIPAL_AMOUNT number(10,2);



DML(Data Manipulation Language): insert,select,update,delete,Merge
  • insert:
                                            INSERT ALL

  • Update:
                                     Update all rows(records) of the single/multiple column 
Update emp set Ename='Ankit';
Update emp set Ename="Apurv" , salary=35000;

                                     Update single row(record) of the single/multiple column 
Update emp set Ename='Ankit' where salary=25000;
                              also can update multiple rows if more than one rows having salary 25000
or using several conditions

  • Delete:
                                                                delete all records
delete from emp3;                        
or
delete emp3; means <from> is optional here
                
                                                            delete records based on condtion
delete emp3 where Sal=72435; means <from> is optional here


DCL



TCL
--------------------

Oracle NVL() function:


-- Display the name and salary  for all the employees

    SQL>select ename,sal from emp;


--  Display the employee no and totalsalary  for all the employees

    SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total  salary" from

    emp;

-- Display the employee name and annual salary for all employees.

    SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp;

-- Display the employee number and name  who are earning comm.

   SQL>select empno,ename from emp where comm is not null;

-- Display the employee number and name  who do not earn any comm.

SQL>select empno,ename from emp where comm is null;

-- Display the names of the employees who are working in the company for

-- the past 5 years;

SQL>select ename  from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;

-- Display the list of all users in your database(use catalog table).

SQL>select username from all_users;

--  Display the name of the current user.

SQL>show user

IN
--  Display the names of employees working in depart number 10 or 20 or 40

-- or employees working as

-- CLERKS, SALESMAN or ANALYST.

SQL>select ename from emp where deptno in (10, 20, 40) or job

in ('CLERKS','SALESMAN','ANALYST');

--  Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

SQL>select distinct(job) from emp where deptno=10 or deptno=20

           (or)

SQL>select distinct(job) from emp where deptno in(10,20);

LIKE
--  Display the names of employees whose name starts with alphabet S.

SQL>select ename from emp where ename like 'S%';

--  Display the Employee names for employees whose name ends with alphabet S.

SQL>select ename from emp where ename like '%S';

--  Display the names of employees whose names have second alphabet A in

-- their names.

SQL>select ename from emp where ename like '_A%';

-- select the names of the employee whose names is exactly five characters

-- in length.

SQL>select ename from emp where length (ename) =5;

-- Display the names of the employee who are not working as SALESMAN OR

-- CLERK OR ANALYST.

SQL>select ename from EMP where job not

In ('SALESMAN','CLERK','ANALYST');

Aggreagate Function:
-- Display the total number of employee working in the company.

SQL>select count (*) from emp;

-- Display the total salary beiging paid to all employees.

SQL>select sum (Sal) from emp;
.
--  Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job='CLERK';

--  Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;
-- Display the jobs which are unique to department 10.

SQL>select distinct(job) from emp where deptno=10

order by:
-- Display the names of the employee in order of salary i.e the name of

-- the employee earning lowest salary    should salary appear first.

SQL>select ename from emp order by sal;

group by.

-- Display depart numbers and total number of employees working in each

-- department.

SQL>select deptno,count(deptno)from emp group by deptno;

-- Display the various jobs and total number of employees within each job

-- group.

SQL>select job,count(job)from emp group by job;

-- Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

-- Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

--  Display the various jobs and total salary for each job

SQL>select job,sum(sal) from emp group by job;

group by + having clause

-- Display the depart numbers with more than three employees in each dept.

SQL>select deptno,count(deptno) from emp group by deptno having

count(*)>3;

-- Display the various jobs along with total salary for each of the jobs

-- where total salary is greater than 40000.

SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;

-- Display the various jobs along with total number of employees in each

-- job.The output should contain only those  jobs with more than three employees.

SQL>select job,count(empno) from emp group by job having count(job)>3

--  Display the details of those who do not have any person working under them.

SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by

e.ename having count(*)=1;

subqueries:

-- Display the name of the empployee who earns highest salary.

SQL>select ename from emp where sal=(select max(sal) from emp);

--  Display the employee number and name for employee working as clerk and

-- earning highest salary among clerks.

SQL>select empno,ename from emp where where job='CLERK'

           and sal=(select max(sal) from emp  where job='CLERK');

-- Display the names of salesman who earns a salary more than the highest

-- salary of any clerk.

SQL>select ename,sal from emp where job='SALESMAN' and sal>(select

max(sal) from emp

 where job='CLERK');

-- Display the names of clerks who earn a salary more than the lowest

-- salary of any salesman.

SQL>select ename from emp where job='CLERK' and sal>(select min(sal)

from emp

             where job='SALESMAN');

-- Display the names of employees who earn a salary more than that of

-- Jones or that of salary grether than   that of scott.

SQL>select ename,sal from emp where sal>

(select sal from emp where ename='JONES')and sal> (select sal from emp

where ename='SCOTT');

-- Display the names of the employees who earn highest salary in their

-- respective departments.

SQL>select ename,sal,deptno from emp where sal in(select max(sal) from

emp group by deptno);

-- Display the employee names who are working in accounting department.

SQL>select ename from emp where deptno=(select deptno from dept where

dname='ACCOUNTING')

-- Display the employee names who are working in Chicago.

SQL>select ename from emp where deptno=(select deptno from dept where

LOC='CHICAGO')

--  Display the Job groups having total salary greater than the maximum

-- salary for managers.

SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT

MAX(SAL) FROM EMP WHERE JOB='MANAGER');

-- Display the names of employees from department number 10 with salary

-- greater than that of any employee working in other department.

SQL>select ename from emp where deptno=10 and sal>any(select sal from

emp where deptno not in 10).

-- Display the names of the employees from department number 10 with

-- salary greater than that of all employee working in other departments.

SQL>select ename from emp where deptno=10 and sal>all(select sal from

emp where deptno not in 10).

--  Display the common jobs from department number 10 and 20.

SQL>select job from emp where deptno=10 and job in(select job from emp

where deptno=20);

-- Display the details of those employees who are in sales department and

-- grade is 3.

 

SQL>select * from emp where deptno=(select deptno from dept where

dname='SALES')and sal between(select losal from salgrade where grade=3)and

             (select hisal from salgrade where grade=3);

-- display the managers names

SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;

-- display the who are not managers

SQL>select ename from emp where ename not in(select distinct(m.ename)

         from emp e,emp m where m.empno=e.mgr);

-- Display those employees whose manager name is JONES.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and

e.ename='JONES';

-- Display ename who are working in sales dept.

SQL>select ename from emp where deptno=(select deptno from dept where

dname='SALES');

-- Display employee name,deptname,salary and comm for those sal in between

-- 2000 to 5000 while location is chicago.

SQL>select ename,dname,sal,comm from emp,dept where sal  between 2000

and 5000

          and loc='CHICAGO' and emp.deptno=dept.deptno;

-- Display those employees whose salary greter than his manager salary.

SQL>select p.ename from EMP e, EMP p where e.empno=p.mgr and p.sal>e.sal

-- Display those employees who are working in the same dept where his

-- manager is work.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and

p.deptno=e.deptno;

-- Display those employees who are not working under any manager.

SQL>select ename from emp where mgr is null

--   DISPLAY EMPLOYEE NAME, JOB, DEPARTMENT, LOCATION FOR ALL WHO ARE WORKING

-- AS MANAGER?

SQL>select ename, JOB, DNAME, LOCATION from EMP, DEPT where mgr is not

Null;


--  DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES?
--           [AND ALSO DISPLAY THEIR MANAGER NAME]?

SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND
E.ENAME='JONES';

-- Calculate Nth Highest Salary
SELECT ename,sal from Employee e1 where
        N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e1.sal > e2.sal)


--  top 5 min rank holder a/c to their salary
SELECT *
FROM employees e1
WHERE 5>
    (SELECT count(distinct salary)
     FROM employees e2
     WHERE e1.salary >e2.salary )
order by salary;


--  top 5 max/top rank holder a/c to their salary
SELECT *
FROM employees e1
WHERE 5>
    (SELECT count(distinct salary)
     FROM employees e2
     WHERE e1.salary <e2.salary )
order by salary DESC;


--  Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP e1 WHERE 5>(SELECT COUNT(DISTINCT SAL)

FROM EMP e2 WHERE e1.SAL<e2.SAL)ORDER BY SAL DESC;

--  Display name of those employee who are getting the highest salary?

SQL>select ename from emp where sal=(select max(sal) from emp);


--  Display dname where at least 3 are working and display only department name?

SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno

and 3>any  (select count(deptno) from emp group by deptno)












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

Table Aliases:

                                    get all the columns of employees and department tables
SELECT e.*, d.*
FROM   employees e
       JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAM LOCATION
----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- ------------- -------------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20            20 RESEARCH       DALLAS

Column Aliases:

SELECT employee_id AS employee_no, employee_name AS "Name"
FROM   employees
ORDER BY employee_id;

EMPLOYEE_NO Name
----------- ----------
       7369 SMITH

Scalar Subqueries:

                        return the number of employees for each department
SELECT d.department_id, d.department_name,
       (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS emp_count
FROM   departments d
ORDER BY d.department_id;

DEPARTMENT_ID DEPARTMENT_NAM  EMP_COUNT
------------- -------------- ----------
           10 ACCOUNTING              3

emp table me ahr ek job_id kitni baar aayi hai ye pta lagana:

SQL> select job_id,count(*) from employees group by job_id;

JOB_ID       COUNT(*)
---------- ----------
PR_REP              1
PU_CLERK            5
PU_MAN              1
SA_MAN              5
SA_REP             30
SH_CLERK           20
ST_CLERK           20
ST_MAN              5

Group by ke sath kabhi cond lagani ho to glti se WHERE mt 

lga dena Having clause ayega:

SQL> select job_id,count(*) from employees group by job_id having job_id='SH_CLERK';

JOB_ID       COUNT(*)
---------- ----------
SH_CLERK           20

SQL JOIN Cheat Sheet:







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

Practice Problem on  Join:









  • find the salesperson and customer who belongs to same city.
Sample table: salesman
 salesman_id |    name    |   city   | commission
Sample table: customer
 customer_id |   cust_name    |    city    | grade | salesman_id
SELECT salesman.name AS "Salesman",
customer.cust_name, customer.city 
FROM salesman,customer 
WHERE salesman.city=customer.city
Salesmancust_namecity
James HoogNick RimandoNew York

  • find those orders where order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.

Sample table: orders

ord_no      purch_amt   ord_date    customer_id  salesman_id
Sample table: customer
 customer_id |   cust_name    |    city    | grade | salesman_id
SELECT  a.ord_no,a.purch_amt,
b.cust_name,b.city
FROM orders a,customer b
WHERE a.customer_id=b.customer_id
AND a.purch_amt BETWEEN 500 AND 2000;

Output of the Query:

ord_no	purch_amt	cust_name	city
70007	948.50		Graham Zusi	California
find the salesperson(s) and the customer(s) he handle. Return Customer Name, city, Salesman, commission.
SELECT a.cust_name AS "Customer Name",
a.city, b.name AS "Salesman", b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id=b.salesman_id;

find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission.

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
Sample table: salesman
 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
SELECT a.cust_name AS "Customer Name", 
a.city, b.name AS "Salesman", b.commission 
FROM customer a 
INNER JOIN salesman b 
ON a.salesman_id=b.salesman_id 
WHERE b.commission>.12;
 find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.
SELECT a.cust_name AS "Customer Name",
a.city, b.name AS "Salesman", b.city,b.commission  
FROM customer a  
INNER JOIN salesman b  
ON a.salesman_id=b.salesman_id
WHERE b.commission>.12
AND a.city<>b.city;
 find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.
SELECT a.ord_no,a.ord_date,a.purch_amt,
b.cust_name AS "Customer Name", b.grade,
c.name AS "Salesman", c.commission
FROM orders a
INNER JOIN customer b
ON a.customer_id=b.customer_id
INNER JOIN salesman c
ON a.salesman_id=c.salesman_id;
Write a SQL statement to make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.Tricky
SELECT *
FROM orders
NATURAL JOIN customer  
NATURAL JOIN salesman;
 display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.
SELECT a.cust_name,a.city,a.grade,
b.name AS "Salesman",b.city
FROM customer a
LEFT JOIN salesman b
ON a.salesman_id=b.salesman_id
order by a.customer_id;

find those customers whose grade less than 300. Return cust_name, customer city, grade, Salesman, saleman city. The result should be ordered by ascending customer_id.
SELECT a.cust_name,a.city,a.grade,
b.name AS "Salesman", b.city
FROM customer a
LEFT OUTER JOIN salesman b
ON a.salesman_id=b.salesman_id
WHERE a.grade<300
ORDER BY a.customer_id;

----

----

Practice Problem on  Aggregate Function:

Sample table: orders
ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
 calculate total purchase amount of all orders. Return total purchase amount.
SELECT SUM (purch_amt)
FROM orders;

count the number of unique salespeople. Return number of salespeople.

SELECT COUNT (DISTINCT salesman_id)
FROM orders;

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
count the number of customers. Return number of customers.
SELECT COUNT(*) 
FROM customer;
ALL,ANY,SOME keyword in oracle
 find the number of customers who got at least a gradation for his/her activity.
SELECT COUNT (ALL grade) 
FROM customer;
 find the highest grade of the customers for each of the city. Return city, maximum grade.
SELECT city,MAX(grade) 
FROM customer 
GROUP BY city;
 find the highest purchase amount on '2012-08-17' by each salesperson. Return salesperson ID, purchase amount.
using order table:
SELECT salesman_id,MAX(purch_amt) 
FROM orders 
WHERE ord_date = '2012-08-17' 
GROUP BY salesman_id;
find highest order (purchase) amount by each customer in a particular order date. Filter the result by highest order (purchase) amount above 2000.00. Return customer id, order date and maximum purchase amount.
SELECT customer_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX(purch_amt)>2000.00;
find the maximum order (purchase) amount in the range 2000, 6000 (Begin and end values are included.) by combination of each customer and order date. Return customer id, order date and maximum purchase amount.
SELECT customer_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX(purch_amt) BETWEEN 2000 AND 6000;



---

----





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

                                                            PL/SQL Practice

STRING KE ANDER ' LAANA:
SQL> set serveroutput on
SQL> BEGIN
  2  dbms_output.put_line('can''t');
  3  END;
  4  /
can't

PL/SQL procedure successfully completed

calculate the incentive of an employee whose ID is 110.

SQL> DECLARE
  2    incentive   NUMBER(8,2);
  3  BEGIN
  4    SELECT salary * 0.12 INTO incentive
  5    FROM employees
  6    WHERE employee_id = 110;
  7  DBMS_OUTPUT.PUT_LINE('Incentive  = ' || TO_CHAR(incentive));
  8  END;
  9  /
Incentive  = 984

PL/SQL procedure successfully completed.
PL/SQL Function Syntax:
CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
    RETURN return_type
IS

[declarative section]

BEGIN

[executable section]

[EXCEPTION]

[exception-handling section]

END;

Function to get FullName of Employee for given employee_id:
SQL> CREATE OR REPLACE FUNCTION gettFullName(id number)
  2
  3    RETURN varchar2
  4    IS
  5
  6     full_name varchar2(50);
  7     fname VARCHAR(20);
  8     lname varchar(20);
  9    BEGIN
 10      SELECT first_name INTO fname FROM employees
 11      WHERE employee_id =id;
 12       SELECT last_name INTO lname FROM employees
 13      WHERE employee_id =id;
 14      full_name:= fname ||' '|| lname;
 15      RETURN full_name;
 16  end;
 17  /

Function created.

SQL> declare
  2     full_name varchar2(45);
  3      begin
  4      full_name:=printFullName(102);
  5        dbms_output.put_line('Full Name is : '||full_name);
  6      end;
  7    /
Full Name is : LexDe Haan
Drop Function:
DROP FUNCTION function_name;
Recursive Function to Find Factorial of Number:
SQL> CREATE OR REPLACE FUNCTION fact(n number) RETURN number
  2  is
  3  begin
  4    IF n=0 THEN
  5      RETURN 1;
  6    ELSE
  7      RETURN n*fact(n-1);
  8    END IF;
  9  end fact;
 10  /

Function created.

SQL> DECLARE
  2       num NUMBER;
  3       resultt NUMBER;
  4  BEGIN
  5      num:=&num;
  6      resultt:=fact(num);
  7
  8      dbms_output.put_line('factorial of '||num||' is : '||resultt);
  9  END;
 10  /
Enter value for num: 5
old   5:     num:=&num;
new   5:     num:=5;
factorial of 5 is : 120

PL/SQL procedure successfully completed.

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

delete table when pk and fk exist

suppose tmp1(id[pk],name)   and  tmp2(cid[fk reference to tmp1(id) , name) are two table
if we want to drop tmp1 => error, so follow below step;

step1: find parent, constraint_name & child table name

select t1.table_name child_table, t1.constraint_name,
t2.table_name parent_table from user_constraints t1,
user_constraints t2 where t1.r_constraint_name = t2.constraint_name;

step2:
alter table tmp2
drop constraint tmp2_fk;
 
step 3:
drop table tmp1;

Excel TEXT function:

run sql using script file in sqlplus:
SQL> ed lab_01_04.sql;
SQL> set serveroutput on;
SQL> start lab_01_04.sql;
  3  /

1 row created.

create sequence:
CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
drop sequence:
 DROP SEQUENCE SYSTEM.REPAYSCH_SEQ;
display sequences:
select sequence_owner, sequence_name from dba_sequences;


DBA_SEQUENCES -- all sequences that exist 
ALL_SEQUENCES  -- all sequences that you have permission to see 
USER_SEQUENCES  -- all sequences that you own
current value of sequence:

select a_seq.nextval from dual;

convert ASCII value to Char:          select chr(65) from dual;

-----------

 

                                                          



----














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

Show tables that are accessible by the current user:


Show all tables in the Oracle Database:


The following picture illustrates the tables that can be returned from the user_tables, all_tables, and dba_tables views:



create table:
Alter table:
can not drop the column owned by SYS


Oracle - SQL - Not Null Constraint:

Oracle - SQL - Unique Constraint:

Oracle - SQL - Primary Key Constraint:

Oracle - SQL - Check Constraint:

Oracle - SQL - Managing Constraints:




DML:


Data types: char vs varchar






set column width to display:
update student:

Delete Statement:
MERGE Statement:
https://oracle-base.com/articles/9i/merge-statement

Oracle - SQL - Managing Transactions:


after perform commit:
delete ke baad rollback krna:

disc se disconnect to ho jaata hai database lekin commit bhi automatic:
savepoint:

conacate space b/w two columns using select:

distinct:

Sorting:

sorting based on 3rd column:

operators:
operators list










Oracle - SQL - Functions:    oracle sql functions





lower() function: convert in to lowercase
upper() function: convert in to uppercases
 INITCAP() function sets the first letter of each word in uppercase, all other letters in lowercase

length() function:

concat() function:

substr() method :  e.g. show staring first 3 digit phone_no:


Oracle - SQL - Number Functions:
Date Functions:



Oracle - SQL - Conversion Functions:



Delete vs Truncate



------------------------------------
Oracle - SQL - Joins:




                     ~~~~~PL/SQL~~~~~

The 'Hello World' Example
dbms_output is package

put_line is a proceedure
Till 9i :put_line accept only char or varchar data or this type of variable
10g onwards :put_line accept char,number,date,time_stamps etc.

  • SQL> BEGIN
      2     dbms_output.put_line('hello word!');
      3  END;
      4  /
    hello word!

    PL/SQL procedure successfully completed.
                     or
SQL> begin dbms_output.put_line('hello word!'); end;
  2  /
hello word!
  • SQL> DECLARE
      2     message  varchar2(20):= 'Hello, World!';
      3  BEGIN
      4     dbms_output.put_line(message);
      5  END;
      6  /
    Hello, World!

    PL/SQL procedure successfully completed.
The PL/SQL Comments
single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */.
SQL> DECLARE
  2     -- variable declaration
  3     message  varchar2(20):= 'Hello, World!';
  4  BEGIN
  5     /*
  6     *  PL/SQL executable statement(s)
  7     */
  8     dbms_output.put_line(message);
  9  END;
 10  /
Hello, World!

PL/SQL procedure successfully completed.


Initializing Variables in PL/SQL

  • The DEFAULT keyword

  • The assignment operator

  • counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';
SQL> DECLARE
  2     a integer := 10;
  3     b integer := 20;
  4     c integer;
  5     f real;
  6  BEGIN
  7     c := a + b;
  8     dbms_output.put_line('Value of c: ' || c);
  9     f := 70.0/3.0;
 10     dbms_output.put_line('Value of f: ' || f);
 11  END;
 12  /
Value of c: 30
Value of f: 23.33333333333333333333333333333333333333

PL/SQL procedure successfully completed.

Variable Scope in PL/SQL
PL/SQL allows the nesting of blocks
SQL> DECLARE
  2     -- Global variables
  3     num1 number := 95;
  4     num2 number := 85;
  5  BEGIN
  6     dbms_output.put_line('Outer Variable num1: ' || num1);
  7     dbms_output.put_line('Outer Variable num2: ' || num2);
  8     DECLARE
  9        -- Local variables
 10        num1 number := 195;
 11        num2 number := 185;
 12     BEGIN
 13        dbms_output.put_line('Inner Variable num1: ' || num1);
 14        dbms_output.put_line('Inner Variable num2: ' || num2);
 15     END;
 16  END;
 17  /
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.

Parameterized Cursor:

Write a PL/SQL block that accepts a value of a job from user and displays the names, department numbers and salaries of the employees from the “emp” table having that job. The block makes use of a parameterized cursor. The user input is passed on to the cursor as a parameter. 

DECLARE
emp_rec employees%ROWTYPE;

CURSOR emp_cur(job_name varchar) IS
SELECT * FROM employees
WHERE job_id=job_name;
BEGIN
    OPEN emp_cur(&job_name);
    LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
            dbms_output.put_line(
            'Name : '||emp_rec.first_name||' '||emp_rec.last_name||'****'||
            'department_no: '||emp_rec.department_id||'****'||
            'salary : '||emp_rec.salary
                );
    END LOOP;
END;

Pragma Exception

https://way2tutorial.com/plsql/pragma-exception-init.php

  PL/SQL Packages

steps to create package and calling it:

--------steps to create Packages--
CREATE OR REPLACE PACKAGE my_pkg IS
 FUNCTION prnt_greetings RETURN VARCHAR2;
  PROCEDURE proc_students(f_name VARCHAR2, l_name VARCHAR2);
END my_pkg;
--------------
  CREATE OR REPLACE PACKAGE Body my_pkg IS
  FUNCTION prnt_greetings RETURN VARCHAR2
     IS
     greeing VARCHAR2(50):='Welcome to PLSQL';
     BEGIN
        RETURN greeing;
     END prnt_greetings;

  PROCEDURE proc_students(f_name  VARCHAR2, l_name  VARCHAR2)
     IS
     BEGIN
       INSERT INTO students(f_name,l_name)
       VALUES(f_name,l_name);
     END ;

END my_pkg;
---
BEGIN
    my_pkg.proc_students('raza','khan');
END;


~~~~~PL/SQL MCQ~~~~~

Answer : D

Q 2 - To get the server output result and display it into the screen, you need to write −

A - set serveroutput on

Which of the following code will open a cursor named cur_employee?

A - OPEN cur_employee;

Q 7 - Which of the following is true for querying a table in the same trigger?

A - The AFTER keyword should be used, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Q 8 - Any subprogram not in the package specification but coded in the package body is called a

A - Public object.

B - Private object.

Q 1 - Which of the following is true about the PL/SQL language?

A - PL/SQL provides access to predefined SQL packages.

B - PL/SQL provides support for Object-Oriented Programming.

C - PL/SQL provides support for Developing Web Applications and Server Pages.

D - All of the above.

Which of the following code correctly create a record named book with two field title and author?

A - TYPE book IS RECORD

(title  varchar(50),
    author  varchar(50),
    );

The collection method LIMIT
C - Checks the Maximum Size of a Collection.

 A transaction ends when

A - A COMMIT or a ROLLBACK statement is issued.

B - A DDL statement, like CREATE TABLE statement, is issued; because in that case a COMMIT is automatically performed.

C - A DCL statement, such as a GRANT statement, is issued; because in that case a COMMIT is automatically performed.

D - All of the above.








-----------

-------- hr login steps---------------

C:\Users\Lenovo>sqlplus / as sysdba

SQL> select name from v$pdbs;


SQL> alter pluggable database all open;


SQL> alter pluggable database all save state;


SQL> connect sys/oracle@localhost:1521/XEPDB1 as sysdba;


SQL> alter user hr identified by hr;



SQL> alter user hr account unlock;


SQL> connect hr/hr@localhost:1521/XEPDB1;

SQL> show user;
USER is "HR"









------
 grant alter any trigger to hr;

GRANT ALL PRIVILEGES TO hr;


























































































































































-----

Comments

Post a Comment

Popular posts from this blog

c++ oops

Image
   class:   It is a user-defined data type, which holds its own data members and member functions, which can be accessed and used by creating an instance of that class. A class is like a blueprint for an object. blue-print representing a group of objects which shares some common properties and behaviours. object: An Object is an instance of a Class. When a class is defined, no memory is allocated but when it is instantiated (i.e. an object is created) memory is allocated. Member Functions in Classes There are 2 ways to define a member function: Inside class definition Outside class definition To define a member function outside the class definition we have to use the scope resolution :: operator along with class name and function name. C++ Constructor: A constructor in C++ is a special ‘MEMBER FUNCTION’ having the same name as that of its class which is used to initialize some valid values to the data members of an object. It is executed automatically whenever an object o...
Read more

Takeoff (hackerearth.3, datastructure, array 1-D)

      There are three planes  A ,  B  and  C . Plane  A  will takeoff on every  p t h  day i.e.  p ,  2 p ,  3 p  and so on. Plane  B  will takeoff on every  q t h  day and plane  C  will takeoff on every  r t h  day. There is only one runway and the takeoff timing is same for each of the three planes on each day. Your task is to find out the maximum number of flights that will successfully takeoff in the period of  N  days. Note:  If there is collision between the flights no flight will take off on that day. Input Format The first line of the input contains a single integer  T , the number of test cases. Then  T  lines follow each containing four space-separated integers  N ,  p ,  q  and  r  as denoted in the statement. Output Format For each test case print a single integer representing the maximum num...
Read more

Aptitude tricks

Image
                                                                                    Time and Work 1.      Work from Days: If A can do a piece of work in  n  days, then A's 1 day's work = 1 . n 2.      Days from Work: If A's 1 day's work = 1 , then A can finish the work in  n  days. n 3.      Ratio: If A is thrice as good a workman as B, then: Ratio of work done by A and B = 3 : 1. Ratio of times taken by A and B to fin...
Read more