1)

SQL >

show pdbs

alter pluggable database ORCLPDB open read write;

alter session set container=ORCLPDB;

Create an schema .

Create user test identified by test;

grant dba to test;
GRANT CREATE SESSION TO TEST;

select * from TEST.STUDENT_INFO;

CREATE TABLE TEST.STUDENT_INFO(

id int,
name varchar(50),
address varchar(100),
email varchar(50),
phone number(10)
);

select * from TEST.STUDENT_INFO;

insert into test.student_info ( id , name , address , email , phone ) values (100,

'SANJAY','PUNE','[email protected]',12345678);
insert into test.student_info ( id , name , address , email , phone ) values (101,
'VIKAS','PUNE','[email protected]',12345678);
insert into test.student_info ( id , name , address , email , phone ) values (102,
'VIJAY','PUNE','[email protected]',12345678);
insert into test.student_info ( id , name , address , email , phone ) values (103,
'RAHUL','PUNE','[email protected]',12345678);

update test.student_info set name='SHASHNK' where id=103;

delete from test.student_info where id=103;

_-------

PK need.

insert into test.student_info ( id , name , address , email , phone ) values (103,

'RAHUL','PUNE','[email protected]',12345678);
insert into test.student_info ( id , name , address , email , phone ) values (103,
'RAHUL','PUNE','[email protected]',12345678);
insert into test.student_info ( id , name , address , email , phone ) values (103,
'RAHUL','PUNE','[email protected]',12345678);

select * from test.student_info;

delete from test.student_info where id=103;
alter table test.student_info add primary key (ID);
insert into test.student_info ( id , name , address , email , phone ) values (103,
'RAHUL','PUNE','[email protected]',12345678);
insert into test.student_info ( id , name , address , email , phone ) values (103,
'RAHUL','PUNE','[email protected]',12345678);

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

2) Constraints

Check:

select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from dba_constraints where

TABLE_NAME='STUDENT_INFO' and owner='TEST';
alter table test.student_info drop primary key;
select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from dba_constraints where
TABLE_NAME='STUDENT_INFO' and owner='TEST';
alter table test.student_info add primary key(id);

select * from test.student_info ;

insert into test.student_info ( id , name , address , email , phone ) values (104,

'AKASH','PUNE','[email protected]','');

select * from test.student_info ;

alter table test.student_info ADD CONSTRAINT Check_id check (phone is not null);
alter table test.student_info drop constraint check_id;

delete from test.student_info where id=104;

alter table test.student_info ADD CONSTRAINT Check_id check (phone is not null);
alter table test.student_info drop constraint check_id;

insert into test.student_info ( id , name , address , email , phone ) values (104,

'AKASH','PUNE','[email protected]','');

Foreign:

CREATE TABLE TEST.DEPT_INFO(

dept_id int,
deptname varchar(50),
PRIMARY KEY (dept_id )
);

alter table TEST.DEPT_INFO ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id )

REFERENCES TEST.STUDENT_INFO(id);

select * from TEST.DEPT_INFO;

insert into TEST.DEPT_INFO (dept_id , deptname ) values ( 100 , 'MECH');

insert into TEST.DEPT_INFO (dept_id , deptname ) values ( 101 , 'CIVIL');
insert into TEST.DEPT_INFO (dept_id , deptname ) values ( 102 , 'CSE');
insert into TEST.DEPT_INFO (dept_id , deptname ) values ( 103 , 'IT');
select * from TEST.DEPT_INFO;
select * from test.student_info;

delete from test.student_info where id=100;

delete from test.dept_info where dept_id=100;
delete from test.student_info where id=100;

Clauses and impact:

explain plan for select * from test.student_info;

select * from TABLE(DBMS_XPLAN.DISPLAY);

############################################################################

Operators:

operator operand

CREATE TABLE TEST.EMP(

emp_id number,
emp_name varchar(50),
emp_salary number,
hire_date date,
PRIMARY KEY (emp_id)
);

alter session set nls_date_format='YYYY-MM-DD';

insert into TEST.EMP (emp_id , emp_name , emp_salary , hire_date ) values
( 1000 , 'SANJAY',10000 ,'2018-07-17');
insert into TEST.EMP (emp_id , emp_name , emp_salary , hire_date ) values ( 1001,
'SHASHANK',20000 , '2019-05-14');
insert into TEST.EMP (emp_id , emp_name , emp_salary , hire_date ) values ( 1002,
'ROHAN',30000 , '2020-05-14');
insert into TEST.EMP (emp_id , emp_name , emp_salary , hire_date ) values ( 1003,
'AKSHAY',40000 , '2022-05-14');

1) Arithmetic Operators

+ , _ , * , /
select * from test.emp;

select * from test.emp where sysdate - hire_date < 365;

select emp_salary + 1000 from test.emp where emp_id=1002;

update test.emp set emp_salary = emp_salary *2 where emp_id=1003;

commit;

select

select * from test.emp where emp_salary > 20000;

select * from test.emp where emp_salary >= 20000;

select * from test.emp where emp_salary < 20000;

select * from test.emp where emp_salary <= 20000;

2) Concatenation Operator

SELECT 'Employee Name is ' || emp_name

FROM test.emp
ORDER BY emp_name;

############################################################################

Set operators:

The different set operators are:

UNION
UNION ALL
MINUS
INTERSECT
EXCEPT

1) UNION

CREATE TABLE TEST.EMPLOYEE(

First_name varchar(50),
last_name varchar(50),
PRIMARY KEY (First_name )
);

CREATE TABLE TEST.CUSTOMER(

First_name varchar(50),
last_name varchar(50),
PRIMARY KEY (First_name )
);

insert into TEST.EMPLOYEE (first_name , last_name ) values ('SANJAY','PAWAR');

insert into TEST.EMPLOYEE (first_name , last_name ) values ('AKASH','SAWANT');
insert into TEST.EMPLOYEE (first_name , last_name ) values ('RAHUL','PATIL');
insert into TEST.EMPLOYEE (first_name , last_name ) values ('VISHAL','DESAI');
commit;

insert into TEST.CUSTOMER (first_name , last_name ) values ('SANJAY','PAWAR');

insert into TEST.CUSTOMER (first_name , last_name ) values ('AKASH','SAWANT');
insert into TEST.CUSTOMER (first_name , last_name ) values ('RAHUL','PATIL');
insert into TEST.CUSTOMER (first_name , last_name ) values ('Mark','Smith');
insert into TEST.CUSTOMER (first_name , last_name ) values ('Richard','Richard');
commit;

SELECT first_name, last_name

FROM test.customer
UNION
SELECT first_name, last_name
FROM test.employee;

But looking at this, we can’t tell which record comes from each table. Often we
don’t need to know, but sometimes we do.

SELECT 'ustomer' AS record_type, first_name, last_name

FROM test.customer
UNION
SELECT 'Employee', first_name, last_name
FROM test.employee;

You don’t need to specify the column aliases on the second table. Oracle will know
that the first columns match and use the alias already provided in the first query.

To order the results, the ORDER BY needs to go at the end.

SELECT 'Customer' AS record_type, first_name, last_name

FROM test.customer
UNION
SELECT 'Employee', first_name, last_name
FROM test.employee
ORDER BY record_type, first_name, last_name;

Que -What’s the Difference Between a UNION and a JOIN?

The difference is how they are combined.

UNION combines data into separate rows, and JOIN combines data into separate
columns.

alter session set current_schema=TEST;

SELECT e.first_name,
e.last_name,
c.first_name,
c.last_name
FROM employee e
INNER JOIN customer c
ON e.first_name = c.first_name AND e.last_name = c.last_name;

SELECT first_name, last_name

FROM employee
UNION
SELECT first_name, last_name
FROM customer ;

2) UNION ALL

alter session set current_schema=TEST;

Que- What is the difference between union and union all

The major difference between UNION ALL and UNION in SQL is that UNION removes any
duplicate results from the final result set, and UNION ALL does not.

SELECT first_name, last_name

FROM customer
UNION
SELECT first_name, last_name
FROM employee;

SELECT first_name, last_name

FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee;

explain plan for SELECT first_name, last_name

FROM customer
UNION
SELECT first_name, last_name
FROM employee;

explain plan for SELECT first_name, last_name

FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee;

select * from TABLE(DBMS_XPLAN.DISPLAY);

3) MINUS

The MINUS set operator will return results that are found in the first query
specified that don’t exist in the second query.

Find all employee who are not customer.

SELECT first_name, last_name

FROM employee
MINUS
SELECT first_name, last_name
FROM customer;

find all names in the customer table that don’t exist in the employee table.

SELECT first_name, last_name

FROM customer
MINUS
SELECT first_name, last_name
FROM employee;

explain plan for SELECT first_name, last_name

FROM employee
MINUS
SELECT first_name, last_name
FROM customer;

select * from TABLE(DBMS_XPLAN.DISPLAY);

4) EXCEPT

EXCEPT is the same as MINUS – they both show results from one query that don’t
exist in another query.
However, MINUS is an Oracle-specific keyword, and EXCEPT is in other databases such
as SQL Server.

5) INTERSECT: Showing Common Results

SELECT first_name, last_name

FROM employee
INTERSECT
SELECT first_name, last_name
FROM customer;

explain plan for SELECT first_name, last_name

FROM employee
INTERSECT
SELECT first_name, last_name
FROM customer;

SELECT first_name, last_name

FROM customer
UNION
SELECT first_name, last_name
FROM employee;

The difference between UNION and INTERSECT is that UNION gets results from both
queries and combines them,
while INTERSECT gets results that only exist in both queries.
############################################################################

About SQL Expressions

An expression is a combination of one or more values, operators, and SQL functions

that evaluates to a value.
An expression generally assumes the data type of its components.

select 2*2 from dual;

e.g. 2

The following expression is an example of a more complex expression that uses both
functions and operators.
The expression adds seven days to the current date, removes the time component from
the sum, and converts the result to CHAR data type:

TO_CHAR(TRUNC(SYSDATE+7))

select to_char(trunc(HIRE_DATE+7)) from test.emp;

select to_char(trunc(sysdate+7)) from dual;

WHere you can use it ?

You can use expressions in:

The select list of the SELECT statement

A condition of the WHERE clause and HAVING clause
The CONNECT BY, START WITH, and ORDER BY clauses
The VALUES clause of the INSERT statement
The SET clause of the UPDATE statement

select * from test.emp where emp_id like '1002';

select * from test.emp where emp_id ='1002';
update test.emp set emp_salary=40000 where emp_id='1002';
commit;

2) Compound Expressions

A compound expression specifies a combination of other expressions.

select SQRT(144) + 72 from dual;

select SQRT(144) + hire_date from test.emp;

3) CASE Expressions

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without
having to invoke procedures.
CREATE TABLE TEST.CUSTOMER_CREDIT(
cust_first_name varchar(50),
cust_last_name varchar(50),
credit_limit number
);

SQL>

insert into TEST.CUSTOMER_CREDIT (cust_first_name,cust_last_name,credit_limit)

values ('SANJAY','PAWAR',50);
insert into TEST.CUSTOMER_CREDIT (cust_first_name,cust_last_name,credit_limit)
values ('SHASHANK','PAWAR',500);
insert into TEST.CUSTOMER_CREDIT (cust_first_name,cust_last_name,credit_limit)
values ('VIKAS','YADAV',1500);
insert into TEST.CUSTOMER_CREDIT (cust_first_name,cust_last_name,credit_limit)
values ('DHEERAJ','SHINDE',4000);
COMMIT;

SELECT cust_first_name,
CASE credit_limit WHEN 60 THEN 'Low'
WHEN 1500 THEN 'High'
ELSE 'Medium' END AS credit
FROM TEST.CUSTOMER_CREDIT
ORDER BY cust_first_name,credit;

SELECT cust_first_name,
CASE credit_limit WHEN 50 THEN 'Low'
WHEN 1500 THEN 'High'
ELSE 'Medium' END AS credit
FROM TEST.CUSTOMER_CREDIT
ORDER BY cust_first_name,credit;

3) Column Expressions

4) CURSOR Expressions

CREATE TABLE TEST.DEPARTMENT_C(

department_id number ,
department_name varchar(50),
last_name varchar(50),
PRIMARY KEY (department_id)
);

CREATE TABLE TEST.EMPLOYEE_C(

department_id number ,
salary number ,
commission_pct number ,
PRIMARY KEY (department_id)
);

insert into TEST.DEPARTMENT_C( department_id,department_name,last_name)

values( 1000,'CSE','PAWAR');
insert into TEST.DEPARTMENT_C( department_id,department_name,last_name)
values( 2000,'SE','PATIL');
insert into TEST.DEPARTMENT_C( department_id,department_name,last_name)
values( 3000,'CIVIL','DESAI');
insert into TEST.DEPARTMENT_C( department_id,department_name,last_name)
values( 4000,',MECH','DESAI');
COMMIT;

insert into TEST.EMPLOYEE_C( department_id,salary,commission_pct )

values( 1000,10000,10);
insert into TEST.EMPLOYEE_C( department_id,salary,commission_pct )
values(2000,20000,20);
insert into TEST.EMPLOYEE_C( department_id,salary,commission_pct )
values( 2000,30000,30);
insert into TEST.EMPLOYEE_C( department_id,salary,commission_pct )
values( 4000,40000,40);
COMMIT;

alter session set current_schema=TEST;

SELECT department_name, CURSOR(SELECT salary, commission_pct

FROM EMPLOYEE_C e
WHERE e.department_id = d.department_id)
FROM DEPARTMENT_C d
ORDER BY department_name;

5) Datetime Expressions

select DBTIMEZONE from dual;

select SESSIONTIMEZONE from dual;

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',

'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')
AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
FROM DUAL;

6) Function Expressions

You can use any built-in SQL function or user-defined function as an expression.
Some valid built-in function expressions are:

LENGTH('BLAKE')
ROUND(1234.567*43)
SYSDATE

select ROUND(1234.5673) from dual;

 select ROUND(1234.4673) from dual;

select emp_name from emp;

select length(emp_name) from emp;
select DBMS_LOB.getlength(emp_name) from emp;

Few more you can try :

circle_area(radius)
payroll.tax_rate(empno)
hr.employees.comm_pct@remote(dependents, empno)
DBMS_LOB.getlength(column_name)
my_function(a_column)

7)

Interval Expressions
An interval expression yields a value of INTERVAL YEAR TO MONTH or INTERVAL DAY TO
SECOND.

select sysdate, sysdate - numtodsinterval(3, 'HOUR') from dual;

mysql : mysql> SELECT '2020-02-01' + INTERVAL 5 DAY AS DATE;

SELECT (SYSTIMESTAMP - order_date) DAY(9) TO SECOND FROM orders

WHERE order_id = 2458;

8) JSON Object Access Expressions

CREATE TABLE test.j_purchaseorder

(id RAW (16) NOT NULL,
date_loaded TIMESTAMP(6) WITH TIME ZONE,
po_document CLOB CONSTRAINT ensure_json CHECK (po_document IS JSON));

INSERT INTO test.j_purchaseorder

VALUES (
SYS_GUID(),
SYSTIMESTAMP,
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Alexis Bull",
"Address": {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of
America"},
"Phone" : [{"type" : "Office", "number" : "909-
555-7307"},
{"type" : "Mobile", "number" : "415-
555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');

commit;

select * from test.j_purchaseorder;

SELECT po.po_document.PONumber
FROM j_purchaseorder po;

SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;

9) Placeholder Expressions
A placeholder expression provides a location in a SQL statement for which a third-
generation language bind variable will provide a value.

:employee_name INDICATOR :employee_name_indicator_var

:department_location

10 ) Expression Lists
An expression list is a combination of other expressions.

(10, 20, 40)

('SCOTT', 'BLAKE', 'TAYLOR')
( ('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA') )

select * from test.emp where emp_id in ('1002','1003');

select * from test.emp where emp_id not in ('1002','1003');
select emp_name , max(emp_salary) from test.emp group by emp_name;

select * from test.emp where emp_salary in ( select max ( emp_salary) from

test.emp);
====================================================================

About SQL Conditions

You can use a condition in the WHERE clause of these statements:

DELETE
SELECT
UPDATE

You can use a condition in any of these clauses of the SELECT statement:

WHERE
START WITH
CONNECT BY
HAVING

The following simple condition always evaluates to TRUE:

1 = 1

Logical conditions can combine multiple conditions into a single condition. For
example, you can use the AND condition to combine two conditions:
1 = 1) AND (5 < 7)

Here are some valid conditions:

name = 'SMITH'
employees.department_id = departments.department_id
hire_date > '01-JAN-08'
job_id IN ('SA_MAN', 'SA_REP')
salary BETWEEN 5000 AND 10000
commission_pct IS NULL AND salary = 2100

Try this out

name = 'SMITH'
employees.department_id = departments.department_id
hire_date > '01-JAN-08'
job_id IN ('SA_MAN', 'SA_REP')
salary BETWEEN 5000 AND 10000
commission_pct IS NULL AND salary = 2100