/*
we can divide total SQL concepts into the following parts.
1) DDL: Data Definition Language - Create, Alter, Drop, Rename, Truncate, Grant, Revoke, Flashback
2) DQL: Data Query Language - Select
3) DML: Data Manipulation Language - Insert, Update, Delete
4) TCL: Transaction Control Language - Commit, Rollback, Savepoint
First Statement in Oracle SQL Developer;
Select 'SQL' as Technology from dual;
Select
(4+6 )as addition
, (4-6) as subtraction
, (4*6) as multiplication
, (44/6) as Division
--, (2>3) as boolean_1
--, (2=3) as boolean_2
--, (2!=3) as boolean_3
--, (2<3) as boolean_4
, sysdate as Today_Date
from dual;
-------- general procedure of writing queries -----------
Select
list of columns
from Table(s)
Where
group by
having
order by
Distinct - to get unique records
* - to get all columns from table
When a calculation has more than one operators, it follows following the precedence(Sequence)
1) ()
2) *(Multiplication), /
3) +, -
*/
select * from table_1_sales_order;
select * from table_6_salessummary;
select Tab_1.* from table_6_salessummary Tab_1;
select Tab_1.* from table_6_salessummary as Tab_1 ;
describe table_6_salessummary;
select CUSTOMER_NAME,POSTAL_CODE,CITY from table_6_salessummary;
select CUSTOMER_NAME as CNAME,POSTAL_CODE ZIP,CITY from table_6_salessummary;
-- 1) Single line comments
-- Show the metadata of table 'table_1_sales_order'
-- we use symbol ';' to terminate the SQL query.
describe table_1_sales_order;
-- 2) Multi line comments ; we put our comments in between /* and */
/*
Show the metadata of table 'table_1_sales_order'
we use symbol ';' to terminate the SQL query.
*/
describe table_1_sales_order;
--3) Create table
/*
drop the table 'EMP_CUSTOM_TABLE' if it exists,
then create a table 'EMP_CUSTOM_TABLE' ;
*/
DROP TABLE EMP_CUSTOM_TABLE;
CREATE TABLE EMP_CUSTOM_TABLE (
EMPNO NUMBER NOT NULL CONSTRAINT EMP_CUSTOM_TABLE_PK PRIMARY KEY,
ENAME VARCHAR2(50),
JOB VARCHAR2(50),
MGR VARCHAR2(4000),
HIREDATE VARCHAR2(4000),
EXITDATE VARCHAR2(4000),
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER,
LOCATION VARCHAR2(400) )
;
create table DEPT_CUSTOM_TABLE (
deptno number(2,0) ,
dname varchar2(50 char) ,
loc varchar2(50 char) ,
--
primary key (deptno)
);
insert into DEPT_CUSTOM_TABLE(DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
insert into DEPT_CUSTOM_TABLE(DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
insert into DEPT_CUSTOM_TABLE(DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
insert into DEPT_CUSTOM_TABLE(DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
insert into DEPT_CUSTOM_TABLE(DEPTNO,DNAME,LOC) values (50,'RandD','HYD');
insert into DEPT_CUSTOM_TABLE(DEPTNO,DNAME,LOC) values (60,'TRAINING','MUMBAI');
--3) insert data into table EMP_CUSTOM_TABLE;
-- entering data for all columns;
INSERT INTO EMP_CUSTOM_TABLE VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'), 800,NULL,20, 'HYD');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1600,300,30,'MUMBAI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),TO_DATE('19-6-1983','dd-mm-yyyy'),1250,500,30, 'CHENNAI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),TO_DATE('27-11-1987','dd-mm-yyyy'),2975,NULL,20, 'HYD');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1250,1400,30, 'BANGALORE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),2850,NULL,30, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),2450,NULL,10, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('13-JUL-87','dd-mm-rr')-85,TO_DATE('7-1-1990','dd-mm-yyyy'),3500,NULL,20, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),5000,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1500,0,30, 'BANGALORE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('13-JUL-87', 'dd-mm-rr')-51,TO_DATE('7-1-1989','dd-mm-yyyy'),1100,NULL,20, 'CHENNAI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),950,NULL,30, 'HYD');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1983','dd-mm-yyyy'),3230,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1400,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (7999,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),3050,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8001 ,'KUMAR','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1600,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8002 ,'SACHIN','MARKETING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),3200,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8003,'RAHUL','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1100,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8004,'GAMBHIR','TRAINING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),3000,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8005,'SEHWAG','TRAINING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1700,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8006,'YURAJ','TRAINING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3000,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8007,'DHONI','MARKETING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1986','dd-mm-yyyy'),1300,800,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8008,'KOHLI','SALES',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3050,658,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8009,'PANDHYA','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1988','dd-mm-yyyy'),1350,800,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8010,'DHAWAN','ADVRTISING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1989','dd-mm-yyyy'),3000,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8011,'BHUVANESH','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1990','dd-mm-yyyy'),1360,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8012,'KUMBLE','TRAINING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1982','dd-mm-yyyy'),1300,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8013,'KAPIL','TRAINING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3200,300,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8014,'ROBIN','MARKETING',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1986','dd-mm-yyyy'),1700,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8015,'JOSHI','SALES',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1985','dd-mm-yyyy'),3800,NULL,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8016,'SHASHTRI','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1988','dd-mm-yyyy'),1000,NULL,10, 'DELHI');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8017,'GANGULI','ADVRTISING',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),TO_DATE('7-1-1989','dd-mm-yyyy'),3300,600,20, 'PUNE');
INSERT INTO EMP_CUSTOM_TABLE VALUES (8018,'SRINADH','SALES',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),TO_DATE('7-1-1990','dd-mm-yyyy'),1800,500,10, 'DELHI') ;
-- entering data for a few columns only but not all
INSERT INTO EMP_CUSTOM_TABLE (EMPNO, ENAME , JOB, MGR) values (8080,'VVS Laxman','SALES',7782);
INSERT INTO EMP_CUSTOM_TABLE (EMPNO, ENAME , JOB, MGR,SAL, COMM , DEPTNO, LOCATION) values (8090,'Kapil Dev','SALES',7782, 1000,50, 20,'Kolkatta') ;
-- 4) Oracle Supports the following data types;
/*
1) char(n) - we use this data type to store the alphanumeric data & size is static i.e. fixed size.
EX: Name char(5)
create table names(Name char(5));
insert into names values('A') --- it shows 5 characters
insert into names values('AB') --- it shows 5 characters
insert into names values('ABC') --- it shows 5 characters
select Name, length(Name) from names;
2) varchar2(n) - we use this data type to store the alphanumeric data & size is dynamic, depends on the data we supply.
drop table names2;
create table names2(Name varchar2(5));
insert into names2 values('A'); --- it shows 1 characters;
insert into names2 values('AB'); --- it shows 2 characters;
insert into names2 values('ABC'); --- it shows 3 characters;
insert into names2 values(''); --- it shows 0 characters since we enter empty
insert into names2 values('ASDF ASF'); --- it throws error since we are entering the data with more than 5 characters
select Name, length(Name) length from names2;
3) Number(n,m);
n: precision - it means total number of digits including decimal values
m: scale - it means total number of decimal positions
number(3,1) it means total 3 digits in that 1 for decimal remain 2 for integer;
xyz.abc - 'xyz' is integer part, 'abc' is decimal part
xyz - integer part
abc - decimal part
129.345 -> 129 is integer part and 345 is decimal part
create table names3(Name varchar2(50) ,age number(2) , height number(3,1 ) );
-- age is allowed digits but not decimals, we have to enter age in terms of exact years;
insert into names3 values('ABC' , 3, 4);
insert into names3 values('ABC1' ,7,5);
insert into names3 values('A' , 10, 4.5);
insert into names3 values('A2' , 10.1, 4.5); it throws error since age is having decimal.
insert into names3 values('A21' , 10.1, 4.5333); it throws error since height is having two decimal points.
select * from names3;
describe names3;
4) Date
it stores the date kind of data , 'yyyy-mm-dd'
EX: '2010-Apr-20'
5) TimeStamp
it stores the date kind of data , 'yyyy-mm-dd HH:MM:SS:MS'
EX: '2010-Apr-20 11:12:05'
drop table names4;
create table names4(Name varchar2(50) ,age number(2) , height number(3,1 ) , DOB Date, timestamp_data_enter TIMESTAMP NOT NULL);
-- age is allowed digits but not decimals, we have to enter age in terms of exact years;
insert into names4 values('ABC' , 3, 4, TO_DATE('2021-11-19','yyyy-mm-dd') , TO_TIMESTAMP('2021-11-21 11:10:10' , 'yyyy-mm-dd HH:MM:SS' ));
TO_DATE('2021-11-19','dd-mm-yyyy')
TO_TIMESTAMP('2021-11-21 11:10:10' , 'dd-mm-yyyy HH:MM:SS' )
('2021-11-19','dd-mm-yyyy')
*/
--5) Constraints
/*
inline constraints : define along with the column
outline constraints : define at the end of the table creation;
Unique: Does not allow duplicate;
Not Null : Does not allow null or empty values; we have to specify along with the column i.e. inline constraints DOES NOT outline constraints
Primary Key: Unique + Not Null;
check: allow the pre defined valiues only
Foreign Key: used to join with another table
*/
------------------------- Creating a table without Constraints -------------------------
drop table Product_0;
create table Product_0 (
Serial_Number number ,
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_0 values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_0 values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_0 values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_0 values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_0 values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_0 values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
select * from Product_0;
------------------------- Creating tables with Constraints -------------------------
-- Inline Constraints
drop table Product_1;
create table Product_1 (
Serial_Number number constraint PID Not Null , -- inline costraint
Product_ID varchar2(500),
Product_Name varchar2(500) Not Null, -- inline costraint
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_1 values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_1 values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_1 values (3,'OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
--Insert into Product_1 values (NULL,'ANCD','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
--Insert into Product_1 values (4,'ANCD',NULL,'Storage','Office Supplies','Home Office');
select * from Product_1;
------------------- Start - Unique_Key -------------------
drop table Product_Unique_Key;
create table Product_Unique_Key (
Serial_Number number Unique , -- inline costraint
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_Unique_Key values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Unique_Key values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Unique_Key values (3,'OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_Unique_Key values (NULL,'ANCD','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
--Insert into Product_Unique_Key values (3,'ANCD',NULL,'Storage','Office Supplies','Home Office');
select * from Product_Unique_Key;
------------------- End - Unique_Key -------------------
------------------------------------------------------------------------------------------------
------------------- Start - Check Constraint -------------------
drop table Product_Check_Values;
create table Product_Check_Values (
Serial_Number number Unique ,
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500),
constraint CHECK_Segment_Values Check (Segment in ('Home Office' , 'Consumer','Corporate'))
);
Insert into Product_Check_Values values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Check_Values values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Check_Values values (3,'OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_Check_Values values (NULL,'ANCD','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
--Insert into Product_Check_Values values (3,'ANCD',NULL,'Storage','Office Supplies','ABCD');
select * from Product_Check_Values;
------------------- End - Check Constraint -------------------
------------------------------------------------------------------------------------------------
------------------- Start - Not_Null -------------------
drop table Product_Not_Null;
create table Product_Not_Null (
Serial_Number number not null ,
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_Not_Null values (1,'OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Not_Null values (2,'OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Not_Null values (3,'OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
--Insert into Product_Not_Null values (NULL,'ANCD','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
select * from Product_Not_Null;
------------------- End - Not_Null -------------------
------------------------------------------------------------------------------------------------
------------------- Start - Primary_Key_Outline -------------------
drop table Product_Primary_Key_Outline;
create table Product_Primary_Key_Outline (
Product_ID varchar2(500) ,
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500) not null,
constraint PID_PKEY_Outline primary key (Product_ID) --- outline constraint
);
Insert into Product_Primary_Key_Outline values ('OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Primary_Key_Outline values ('OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Primary_Key_Outline values ('OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_Primary_Key_Outline values ('OFF-PA-10002005','Xerox 225','Paper','Office Supplies','Consumer');
Insert into Product_Primary_Key_Outline values ('OFF-AR-10002399','Dixon Prang Watercolor Pencils, 10-Color Set with Brush','Art','Office Supplies','Corporate');
select * from Product_Primary_Key_Outline;
--Insert into Product_Primary_Key_Outline values ('OFF-LA-10003223','zzzz','Labels','Office Supplies','Home Office'); --- error.; since trying to enter the duplicte value
--Insert into Product_Primary_Key_Outline values ('OFF-ST-10002743','xxxx','Storage','Office Supplies','Home Office');--- error ; since trying to enter the duplicte value
------------------- End - Primary_Key_Outline -------------------
------------------------------------------------------------------------------------------------
------------------- Start - Multiple_Constraints_at_a_Time -------------------
drop table Product_Multiple_Constraints_at_a_Time;
create table Product_Multiple_Constraints_at_a_Time (
Product_ID varchar2(500) ,
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500) not null,
constraint CHECK_Segment_All Check (Segment in ('Home Office' , 'Consumer','Corporate')),--- outline constraint
constraint PK_PID_PNAME_All primary key(Product_ID,Product_Name), --- outline constraint - Composite Primary Key
constraint UNIQUE_All UNIQUE (Product_ID,Product_Name,Sub_Category,Category,Segment) --- outline constraint - Composite Unique Key
);
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-BI-10004094','GBC Standard Plastic Binding Systems Combs','Binders','Office Supplies','Home Office');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-AR-10003478','Avery Hi-Liter EverBold Pen Style Fluorescent Highlighters, 4/Pack','Art','Office Supplies','Consumer');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-PA-10002005','Xerox 225','Paper','Office Supplies','Consumer');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-AR-10002399','Dixon Prang Watercolor Pencils, 10-Color Set with Brush','Art','Office Supplies','Corporate');
Insert into Product_Multiple_Constraints_at_a_Time values ('FUR-CH-10004063','Global Deluxe High-Back Managers Chair','Chairs','Furniture','Home Office');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-BI-10004632','Ibico Hi-Tech Manual Binding System','Binders','Office Supplies','Home Office');
Insert into Product_Multiple_Constraints_at_a_Time values ('OFF-AR-10001662','Rogers Handheld Barrel Pencil Sharpener','Art','Office Supplies','Home Office');
------------------- End - Multiple_Constraints_at_a_Time -------------------
------------------------------------------------------------------------------------------------
------------------- End - Multiple_Constraints_at_a_Time -------------------
-------------------------------------
Insert into Product_1 values ('OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_1 values ('OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_1 values ('OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_1 values ('OFF-BI-10004094','GBC Standard Plastic Binding Systems Combs','Binders','Office Supplies','Home Office');
Insert into Product_1 values ('OFF-AR-10003478','Avery Hi-Liter EverBold Pen Style Fluorescent Highlighters, 4/Pack','Art','Office Supplies','Consumer');
Insert into Product_1 values ('OFF-PA-10002005','Xerox 225','Paper','Office Supplies','Consumer');
Insert into Product_1 values ('OFF-AR-10002399','Dixon Prang Watercolor Pencils, 10-Color Set with Brush','Art','Office Supplies','Corporate');
Insert into Product_1 values ('FUR-CH-10004063','Global Deluxe High-Back Managers Chair','Chairs','Furniture','Home Office');
Insert into Product_1 values ('OFF-BI-10004632','Ibico Hi-Tech Manual Binding System','Binders','Office Supplies','Home Office');
select * from EMP_CUSTOM_TABLE;
describe EMP_CUSTOM_TABLE;
-----------------------------------Sample Tables for Analysis ----------------
----------------------------------- Creating Tables and inserting Data into Tables ----------------
----------------------------------- Adding constraints after create a Table ----------------
drop table Product_Sample;
create table Product_Sample (
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_Sample values ('OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Sample values ('OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Sample values ('OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_Sample values ('OFF-BI-10004094','GBC Standard Plastic Binding Systems Combs','Binders','Office Supplies','Home Office');
Insert into Product_Sample values ('OFF-AR-10003478','Avery Hi-Liter EverBold Pen Style Fluorescent Highlighters, 4/Pack','Art','Office Supplies','Consumer');
Insert into Product_Sample values ('OFF-PA-10002005','Xerox 225','Paper','Office Supplies','Consumer');
Insert into Product_Sample values ('OFF-AR-10002399','Dixon Prang Watercolor Pencils, 10-Color Set with Brush','Art','Office Supplies','Corporate');
Insert into Product_Sample values ('FUR-CH-10004063','Global Deluxe High-Back Managers Chair','Chairs','Furniture','Home Office');
Insert into Product_Sample values ('OFF-BI-10004632','Ibico Hi-Tech Manual Binding System','Binders','Office Supplies','Home Office');
Insert into Product_Sample values ('OFF-AR-10001662','Rogers Handheld Barrel Pencil Sharpener','Art','Office Supplies','Home Office');
Insert into Product_Sample values ('TEC-PH-10004977','GE 30524EE4','Phones','Technology','Home Office');
Insert into Product_Sample values ('TEC-PH-10004539','Wireless Extenders zBoost YX545 SOHO Signal Booster','Phones','Technology','Home Office');
Insert into Product_Sample values ('OFF-FA-10001883','Alliance Super-Size Bands, Assorted Sizes','Fasteners','Office Supplies','Home Office');
Insert into Product_Sample values ('OFF-PA-10000955','Southworth 25% Cotton Granite Paper and Envelopes','Paper','Office Supplies','Home Office');
describe Product_Sample;
ALTER TABLE Product_Sample MODIFY Product_ID NOT NULL;
ALTER TABLE Product_Sample MODIFY Product_ID UNIQUE;
ALTER TABLE Product_Sample MODIFY Product_Name PRIMARY KEY;
ALTER TABLE Product_Sample ADD CONSTRAINT CHECK_VALUES_SEGMENT_2
Check (Segment in ('Home Office' , 'Consumer','Corporate')) ;
describe Product_Sample;
select * from Product_Sample;
--------------------
drop table Sample_Sales;
create table Sample_Sales (
Row_ID varchar2(500) ,
Order_ID varchar2(500) ,
Order_Date Date,
Ship_Date Date,
Ship_Mode varchar2(500) ,
Product_ID varchar2(500) ,
Customer_ID varchar2(500),
Postal_Code varchar2(500),
Sales number(6,2),
Quantity number(6,2),
----- ADDING FOREIGN KEY ON COLUMN 'Product_ID' -----
CONSTRAINT FOREIGN_KEY_PID FOREIGN KEY (Product_ID)
REFERENCES Product_Sample (Product_ID)
ON DELETE SET NULL -- second options is 'ON DELETE CASCADE'
);
------------------------
Insert into Sample_Sales values ('7981','CA-2011-103800',TO_DATE('01/02/2020', 'mm/dd/yyyy'),TO_DATE('01/06/2020', 'mm/dd/yyyy'),'Standard Class','OFF-PA-10000174','DP-13000','77095',16.448,2);
Insert into Sample_Sales values ('740','CA-2011-112326',TO_DATE('01/03/2020', 'mm/dd/yyyy'),TO_DATE('01/07/2020', 'mm/dd/yyyy'),'Standard Class','OFF-LA-10003223','PO-19195','60540',11.784,3);
Insert into Sample_Sales values ('741','CA-2011-112326',TO_DATE('01/03/2020', 'mm/dd/yyyy'),TO_DATE('01/07/2020', 'mm/dd/yyyy'),'Standard Class','OFF-ST-10002743','PO-19195','60540',272.736,3);
Insert into Sample_Sales values ('742','CA-2011-112326',TO_DATE('01/03/2020', 'mm/dd/yyyy'),TO_DATE('01/07/2020', 'mm/dd/yyyy'),'Standard Class','OFF-BI-10004094','PO-19195','60540',3.54,2);
Insert into Sample_Sales values ('1760','CA-2011-141817',TO_DATE('01/04/2020', 'mm/dd/yyyy'),TO_DATE('01/11/2020', 'mm/dd/yyyy'),'Standard Class','OFF-AR-10003478','MB-18085','19143',19.536,3);
Insert into Sample_Sales values ('5328','CA-2011-130813',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/07/2020', 'mm/dd/yyyy'),'Second Class','OFF-PA-10002005','LS-17230','90049',19.44,3);
Insert into Sample_Sales values ('7181','CA-2011-106054',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/06/2020', 'mm/dd/yyyy'),'First Class','OFF-AR-10002399','JO-15145','30605',12.78,3);
Insert into Sample_Sales values ('7475','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','FUR-CH-10004063','ME-17320','42420',2573.82,9);
Insert into Sample_Sales values ('7476','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','OFF-BI-10004632','ME-17320','42420',609.98,2);
Insert into Sample_Sales values ('7477','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','OFF-AR-10001662','ME-17320','42420',5.48,2);
Insert into Sample_Sales values ('7478','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','TEC-PH-10004977','ME-17320','42420',391.98,2);
Insert into Sample_Sales values ('7479','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','TEC-PH-10004539','ME-17320','42420',755.96,4);
Insert into Sample_Sales values ('7480','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','OFF-FA-10001883','ME-17320','42420',31.12,4);
Insert into Sample_Sales values ('7481','CA-2011-167199',TO_DATE('01/05/2020', 'mm/dd/yyyy'),TO_DATE('01/09/2020', 'mm/dd/yyyy'),'Standard Class','OFF-PA-10000955','ME-17320','42420',6.54,1);
Insert into Sample_Sales values ('7661','CA-2011-105417',TO_DATE('01/06/2020', 'mm/dd/yyyy'),TO_DATE('01/11/2020', 'mm/dd/yyyy'),'Standard Class','FUR-FU-10004864','VS-21820','77340',76.728,3);
Insert into Sample_Sales values ('7662','CA-2011-105417',TO_DATE('01/06/2020', 'mm/dd/yyyy'),TO_DATE('01/11/2020', 'mm/dd/yyyy'),'Standard Class','OFF-BI-10003708','VS-21820','77340',10.43,7);
Insert into Sample_Sales values ('593','CA-2011-135405',TO_DATE('01/08/2020', 'mm/dd/yyyy'),TO_DATE('01/12/2020', 'mm/dd/yyyy'),'Standard Class','OFF-AR-10004078','MS-17830','78041',9.344,2);
Insert into Sample_Sales values ('594','CA-2011-135405',TO_DATE('01/08/2020', 'mm/dd/yyyy'),TO_DATE('01/12/2020', 'mm/dd/yyyy'),'Standard Class','TEC-AC-10001266','MS-17830','78041',31.2,3);
Insert into Sample_Sales values ('866','CA-2011-149020',TO_DATE('01/09/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Standard Class','OFF-LA-10004272','AJ-10780','22153',2.89,1);
Insert into Sample_Sales values ('867','CA-2011-149020',TO_DATE('01/09/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Standard Class','FUR-FU-10000965','AJ-10780','22153',51.94,1);
Insert into Sample_Sales values ('717','CA-2011-130092',TO_DATE('01/10/2020', 'mm/dd/yyyy'),TO_DATE('01/13/2020', 'mm/dd/yyyy'),'First Class','FUR-FU-10000010','SV-20365','19901',9.94,2);
Insert into Sample_Sales values ('764','CA-2011-162775',TO_DATE('01/12/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Second Class','OFF-EN-10001990','CS-12250','71111',11.36,2);
Insert into Sample_Sales values ('765','CA-2011-162775',TO_DATE('01/12/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Second Class','OFF-EN-10001532','CS-12250','71111',50.94,3);
Insert into Sample_Sales values ('766','CA-2011-162775',TO_DATE('01/12/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Second Class','TEC-AC-10003174','CS-12250','71111',646.74,6);
Insert into Sample_Sales values ('767','CA-2011-162775',TO_DATE('01/12/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Second Class','OFF-BI-10004187','CS-12250','71111',5.64,3);
describe Sample_Sales;
select * from Sample_Sales;
-- ********************** Altering Database objects **********************
------------------- Start Create Table 'Product_Table_Sample' & Insert Data into Table 'Product_Table_Sample' -------------------
drop table Product_Table_Sample;
create table Product_Table_Sample (
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_Table_Sample values ('OFF-PA-10000174','Message Book, Wirebound, Four 5 1/2" X 4" Forms/Pg., 200 Dupl. Sets/Book','Paper','Office Supplies','Consumer');
Insert into Product_Table_Sample values ('OFF-LA-10003223','Avery 508','Labels','Office Supplies','Home Office');
Insert into Product_Table_Sample values ('OFF-ST-10002743','SAFCO Boltless Steel Shelving','Storage','Office Supplies','Home Office');
Insert into Product_Table_Sample values ('OFF-BI-10004094','GBC Standard Plastic Binding Systems Combs','Binders','Office Supplies','Home Office');
Insert into Product_Table_Sample values ('OFF-AR-10003478','Avery Hi-Liter EverBold Pen Style Fluorescent Highlighters, 4/Pack','Art','Office Supplies','Consumer');
Insert into Product_Table_Sample values ('OFF-PA-10002005','Xerox 225','Paper','Office Supplies','Consumer');
Insert into Product_Table_Sample values ('OFF-AR-10002399','Dixon Prang Watercolor Pencils, 10-Color Set with Brush','Art','Office Supplies','Corporate');
Insert into Product_Table_Sample values ('FUR-CH-10004063','Global Deluxe High-Back Managers Chair','Chairs','Furniture','Home Office');
Insert into Product_Table_Sample values ('OFF-BI-10004632','Ibico Hi-Tech Manual Binding System','Binders','Office Supplies','Home Office');
Insert into Product_Table_Sample values ('OFF-AR-10001662','Rogers Handheld Barrel Pencil Sharpener','Art','Office Supplies','Home Office');
Insert into Product_Table_Sample values ('TEC-PH-10004977','GE 30524EE4','Phones','Technology','Home Office');
Insert into Product_Table_Sample values ('TEC-PH-10004539','Wireless Extenders zBoost YX545 SOHO Signal Booster','Phones','Technology','Home Office');
Insert into Product_Table_Sample values ('OFF-FA-10001883','Alliance Super-Size Bands, Assorted Sizes','Fasteners','Office Supplies','Home Office');
Insert into Product_Table_Sample values ('OFF-PA-10000955','Southworth 25% Cotton Granite Paper and Envelopes','Paper','Office Supplies','Home Office');
select * from Product_Table_Sample;
select COUNT(*) AS NUMBER_OF_RECORDS from Product_Table_Sample;
------------------- End - Create Table 'Product_Table_Sample' & Insert Data into Table 'Product_Table_Sample' -------------------
----------------------------------------------------------------
------------------- Start - Changing Values in a columns using 'UPDATE' statement -------------------
select DISTINCT Segment from Product_Table_Sample;
UPDATE Product_Table_Sample set Segment='Home_Office_Segment' where Segment='Home Office' ;
select DISTINCT Segment from Product_Table_Sample;
select * from Product_Table_Sample;
------------------- End - Changing Values in a columns using 'UPDATE' statement -------------------
----------------------------------------------------------------
------------------- Start - Delete values in a column -------------------
DELETE from Product_Table_Sample where Product_ID ='OFF-BI-10004094' ;
select * from Product_Table_Sample;
DELETE from Product_Table_Sample where Product_ID IN ('OFF-PA-10000955','FUR-FU-10004864');
select * from Product_Table_Sample;
------------------- End - Delete values in a column -------------------
----------------------------------------------------------------
------------------- Start - ADD new column to table -------------------
-- we can add only one column at a time.
ALTER TABLE Product_Table_Sample ADD NOTES VARCHAR2(200);
select * from Product_Table_Sample;
ALTER TABLE Product_Table_Sample ADD NOTES1 VARCHAR2(200) ;
--ALTER TABLE Product_Table_Sample ADD NOTES2 VARCHAR2(200),NOTES3 VARCHAR2(200) ;
select * from Product_Table_Sample;
--- DELETING NOTES1, NOTES1 COLUMNS from TABLE 'Product_Table_Sample';
--ALTER TABLE Product_Table_Sample DROP COLUMN NOTES1 ;
--ALTER TABLE Product_Table_Sample DROP COLUMN NOTES ;
------------------------ End - ADD new column to table ------------------------
------------------------------------------------------------------------------------------
-------------------------- Start - Where Caluse --------------------------
-- we use where caluse to restrict the data by applying some conditions - Filtering - getting required data
-- 'where' caluse apply the condtion on individual records; (***** we apply 'Having Clause" on aggregated records *****)
-- we EQUAL symbol to get only
-- = for single value
-- in for multiple values
------------ Single Condition ------------
select * from Product_Table_Sample where Sub_Category = 'Paper';
select * from Product_Table_Sample where Category <> 'Office Supplies';
select * from Product_Table_Sample where Sub_Category in( 'Paper' , 'Binders');
--- ABOVE AND BELOW QUERIES ARE SAME
select * from Product_Table_Sample where Sub_Category = 'Paper' OR Sub_Category = 'Binders' ;
select * from Product_Table_Sample where Sub_Category NOT in( 'Chairs' , 'Phones' , 'Art');
select * from Sample_Sales where Quantity =4;
select * from Sample_Sales where Quantity <4;
select * from Sample_Sales where Quantity <=4;
select * from Sample_Sales where Quantity >4;
select * from Sample_Sales where Quantity >=4;
select * from Sample_Sales where Quantity !=4;
select * from Sample_Sales where sales >100;
--- Multiple Conditions
select * from Sample_Sales where sales >100 and Quantity >=4;
select * from Sample_Sales where sales >50 or Quantity >=4;
select * from Sample_Sales where sales BETWEEN 10 AND 20;
--BETWEEN INCLUDES BORDER VALUES IT MEANS IT FETCHES RECORDS WITH 10 & 20 AS WELL IN THE ABOVE QUERY
select * from Sample_Sales where sales NOT BETWEEN 10 AND 20;
select * from Sample_Sales where Quantity NOT BETWEEN 3 AND 8;
select * from Sample_Sales where Quantity BETWEEN 3 AND 8;
select * from Sample_Sales ORDER BY Quantity DESC
-- IS NULL, NOT IS NULL, (***** = NULL WILL NOT WORK, i.e. COUNTRY = NULL WILL NOT WORK)
-- ADDING DUMMY NULL DATA INTO TABLE
INSERT INTO Sample_Sales(ROW_ID, sales,Quantity) VALUES( 'R_NULL_1', NULL,1);
INSERT INTO Sample_Sales(ROW_ID, sales,Quantity) VALUES( 'R_NULL_2', 2,NULL);
INSERT INTO Sample_Sales(ROW_ID, sales,Quantity) VALUES( 'R_NULL_3', NULL,NULL);
SELECT * FROM SAMPLE_SALES WHERE QUANTITY IS NULL;
SELECT * FROM SAMPLE_SALES WHERE QUANTITY IS NOT NULL;
SELECT * FROM SAMPLE_SALES WHERE SALES IS NULL;
SELECT * FROM SAMPLE_SALES WHERE SALES IS NOT NULL;
SELECT * FROM SAMPLE_SALES WHERE (QUANTITY IS NULL) AND (SALES IS NULL);
SELECT * FROM SAMPLE_SALES WHERE (QUANTITY IS NULL) OR (SALES IS NULL);
SELECT * FROM SAMPLE_SALES WHERE (QUANTITY IS NOT NULL) OR (SALES IS NULL);
--- DELETE NULL RECORDS
DELETE FROM SAMPLE_SALES WHERE (QUANTITY IS NULL) OR (SALES IS NULL);
------------------- End - Where Caluse -------------------
------------------------------------------------------------------------------------------
-------------------------- Start - SORT THE DATA USING 'ORDER BY CLAUSE' --------------------------
-- WE USE ORDER BY CLAUSE TO SORT THE DATA EITHER IN ASCENDING(ASC) ORDER IT MEANS LOW TO HIGH
-- OR SORT THE DATA IN DESCENDING(DESC) ORDER IT MEANS HIGH TO LOW
-- DEFAULT SORTINF ORDER IS ASCENDING(ASC)
-- NULL RECORDS WILL BE SHOWN AT LAST IN ASCENDING, ON TOP IN DESCENDING ORDER
-- NULL IS TRRATED AS GREATER THAN ALL VALUES
SELECT ROW_ID, SALES,QUANTITY FROM SAMPLE_SALES ORDER BY QUANTITY ;
SELECT ROW_ID, SALES,QUANTITY FROM SAMPLE_SALES ORDER BY QUANTITY DESC;
SELECT ROW_ID, SALES,QUANTITY FROM SAMPLE_SALES ORDER BY QUANTITY DESC , SALES;
SELECT ROW_ID, SALES,QUANTITY FROM SAMPLE_SALES ORDER BY QUANTITY DESC , SALES DESC;
SELECT ROW_ID, SALES,QUANTITY FROM SAMPLE_SALES ORDER BY QUANTITY ASC , SALES DESC;
SELECT ROW_ID, SALES,QUANTITY , ROUND(SALES/QUANTITY) AS "JUST NUM"
FROM SAMPLE_SALES ORDER BY "JUST NUM" ASC;
SELECT ROW_ID, SALES,QUANTITY AS QTY, ROUND(SALES/QUANTITY) AS JUST_NUM
FROM SAMPLE_SALES ORDER BY QTY, ROUND(SALES/QUANTITY) DESC;
SELECT ROW_ID, SALES,QUANTITY AS QTY, ROUND(SALES/QUANTITY) AS JUST_NUM
FROM SAMPLE_SALES ORDER BY 3 DESC; -- ORDER BY COLUMN POSITION, HERE 3RD COLUMN IS 'QUANTITY'
SELECT ROW_ID, SALES,QUANTITY AS QTY, ROUND(SALES/QUANTITY) AS JUST_NUM
FROM SAMPLE_SALES ORDER BY 3 DESC, JUST_NUM ASC;
SELECT DISTINCT CATEGORY, SUB_CATEGORY FROM PRODUCT_TABLE_SAMPLE ORDER BY CATEGORY ;
SELECT DISTINCT CATEGORY, SUB_CATEGORY FROM PRODUCT_TABLE_SAMPLE ORDER BY SUB_CATEGORY DESC, CATEGORY ;
------------------- End - SORT THE DATA USING 'ORDER BY CLAUSE -------------------
------------------------------------------------------------------------------------------
-- ************* FUNCTIONS **************
-------------------------- Start - STRING FUNCTIONS --------------------------
SELECT * FROM PRODUCT_TABLE_SAMPLE;
Insert into PRODUCT_TABLE_SAMPLE values ('DUMMY_1',' DUMMY1 ','DUMMY@YAHOO.COM','DUMMY ASDF DUMMY','ASDF');
Insert into PRODUCT_TABLE_SAMPLE values ('DUMMY_2','DUMMY2','DUMMY2@GMAIL.COM','DUMMY 123 DUMMY',' ASDF ');
Insert into PRODUCT_TABLE_SAMPLE values ('DUMMY_3','DUMMY3 ','DUMMY3@MAIL.COM','DUMMY_DUMMY','ASDF ');
Insert into PRODUCT_TABLE_SAMPLE values ('DUMMY_4','duMMy4','DUMMY4@MSN.COM','DUMMY+DUMMY','asdf ');
SELECT
PRODUCT_ID
, SEGMENT SEG
, UPPER(SEGMENT) UPPER_SEG
, LOWER(SEGMENT) LOWER_SEG
, INITCAP(SEGMENT) INITCAP_SEG
, LENGTH(SEGMENT) LENGTH_SEG
, LTRIM(SEGMENT) LTRIM_SEG
, RTRIM(SEGMENT) RTRIM_SEG
, TRIM(SEGMENT) TRIM_SEG
, LENGTH(TRIM(SEGMENT)) LEN_TRIM_SEG
, CONCAT(CONCAT(TRIM(SEGMENT) , ' ; '), PRODUCT_ID) CONCACT
FROM PRODUCT_TABLE_SAMPLE
WHERE Product_ID LIKE 'DUMM%';
SELECT
PRODUCT_ID
, CATEGORY
, SUB_CATEGORY
, INSTR(SUB_CATEGORY, '@') POSITION
, SUBSTR(SUB_CATEGORY, 1,3) FIRST_3_CHARS
, SUBSTR(SUB_CATEGORY, -3) LAST_3_CHARS
, SUBSTR(SUB_CATEGORY, 3,5) BW_3_N_8
, INSTR(SUB_CATEGORY, '@')+1 POS1
, INSTR(SUB_CATEGORY, '.') POS2
, SUBSTR(SUB_CATEGORY, INSTR(SUB_CATEGORY, '@')+1, INSTR(SUB_CATEGORY, '.') - INSTR(SUB_CATEGORY, '@')-1) EML_SERV
, LPAD(SEGMENT,2, '~' ) LPAD
, RPAD(SEGMENT,3, '%%') RPAD
FROM PRODUCT_TABLE_SAMPLE
WHERE Product_ID LIKE 'DUMM%';
SELECT DISTINCT CATEGORY, SUB_CATEGORY FROM PRODUCT_TABLE_SAMPLE
delete FROM PRODUCT_TABLE_SAMPLE
WHERE Product_ID LIKE 'DUMM%'
------------------- End - STRING FUNCTION -------------------
------------------------------------------------------------------------------------------
-------------------------- Start - NUMERICAL FUNCTIONS --------------------------
/*
ROUND(INPUT , NUM_OF_POSITIONS) :
IF THE DECIAL VALUE IS LESS THAN .5 THEN IT GIVES NEAREST INTEGER LESS THAN GIVEN INPUT ---- FLOOR
IF THE DECIAL VALUE IS GREATER THAN .5 IT GIVES NEAREST INTEGER GREATER THAN GIVEN INPUT ---- CEIL
CEIL(INPUT_NUMBER): NEAREST INTEGER GREATER THAN GIVEN INPUT
CEIL(4.000001) = 5
FLOOR(INPUT_NUMBER) : NEAREST INTEGER LESS THAN GIVEN INPUT
FLOOR(4.9999) = 4
TRUNC(INPUT , NUM_OF_POSITIONS) : ROUND TOWARDS ZERO, IT TRUCATE THE NUMBER
TRUNC(2.45678, 2) = 2.45
*/
SELECT
2.7943 AS INPUT_1
, ROUND(2.7943,2) ROUND_1
, CEIL(2.7943) CEIL_1
, FLOOR(2.7943) FLOOR_1
, TRUNC(2.7943,2) TRUNC_1
, 2.4329 AS INPUT_2
, ROUND(2.4329,2) ROUND_2
, CEIL(2.4329) CEIL_2
, FLOOR(2.4329) FLOOR_2
, TRUNC(2.4329,2) TRUN_2
, 2.5 AS INPUT_3
, ROUND(2.5,2) ROUND_3
, CEIL(2.5) CEIL_3
, FLOOR(2.5) FLOOR_3
, TRUNC(2.5,2) TRUNC_3
, TRUNC(2.5) TRUN_3_1
, REMAINDER(10,4) REM
, MOD(50,6) FACTOR
FROM DUAL;
SELECT
ROUND((10/4) ,3) DUM
, REMAINDER(45,4) REM
, MOD(50,9) FACTOR
FROM DUAL;
------------------- End - NUMERICAL FUNCTIONS -------------------
------------------------------------------------------------------------------------------
-------------------------- Start - DATE FUNCTIONS --------------------------
/*DATE Functions in Oracle */
select ('HTTPS://SQL4DATAANALYSIS.BLOGSPOT.COM/') as ORACLE_SQL_Blog from dual;
-- 1) Get current date & time
SELECT sysdate, current_date , CURRENT_TIMESTAMP , SYSTIMESTAMP from dual;
-- 2) Get current date & Custom Format
SELECT sysdate, to_char( sysdate , 'YYYY-MM') as Year_MonthNum , to_char( sysdate , 'YYYY-MONTH') as Year_MonthFullName,
to_char( sysdate , 'YYYY-MON') as Year_MonthShortlName, to_char( sysdate , 'YYYY-WW') as Year_WeekNuminYear,
to_char( sysdate , 'YYYY-W') as Year_WeekNuminMonth,
to_char( sysdate , 'MONTH-DAY') as Month_WeekDay, to_char( sysdate , 'd') as Day_of_Week
, to_char( sysdate , 'ddd') as Day_of_Year, to_char( sysdate , 'DY') as WeekDay
from dual;
-- 3) Difference between 2 dates
SELECT sysdate, trunc(sysdate , 'MM') First_Day_of_Month, trunc(sysdate , 'WW') First_Day_of_Week,
round( sysdate- (trunc(sysdate , 'MM'))) Days_Between_First_Day_of_Month_and_Today
from dual;
-- 4) Difference between 2 dates in terms of months
SELECT sysdate, trunc(sysdate , 'MM') First_Day_of_Month, trunc(sysdate , 'MM') First_Day_of_Year,
round(MONTHS_BETWEEN( sysdate , trunc(sysdate , 'YYYY'))) Months_Between_First_Day_of_Year_and_ThisMonth
from dual;
-- 5) Getting parts of date i.e. Year, Month, Day......
SELECT sysdate, current_date , CURRENT_TIMESTAMP , SYSTIMESTAMP,
extract( Day from sysdate) Day_of_Month, extract( Month from sysdate) Month, extract( Year from sysdate) Year,
extract( hour from CURRENT_TIMESTAMP) Hour , extract( hour from SYSTIMESTAMP) Hour2,
extract( minute from SYSTIMESTAMP) minute, extract( minute from CURRENT_TIMESTAMP) minute2
from dual;
-- 6) String to Date convertion
select to_date('2022-Jan-25', 'yyyy-mon-dd' ) string_to_Date1
from Dual;
SELECT TO_DATE('21-MAY-2017', 'DD-MM-YYYY') AS INPUT_DATE,
TRUNC(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'YYYY') AS FIRST_DAY_OF_YEAR,
TRUNC(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'MM') AS FIRST_DAY_OF_MONTH,
TRUNC(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'WW') AS FIRST_DAY_OF_WEEK, -- Sunday is first day of week
TRUNC(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'W') AS FIRST_DAY_OF_WEEK2 , -- First WeekDay of month treats as first day of week then get nearest weekday in the past
TRUNC(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'Q') AS FIRST_DAY_OF_QUARTER
FROM DUAL;
SELECT sysdate AS INPUT_DATE,
TRUNC(sysdate,'YYYY') AS FIRST_DAY_OF_YEAR,
TRUNC(sysdate,'MM') AS FIRST_DAY_OF_MONTH,
TRUNC(sysdate,'WW') AS FIRST_DAY_OF_WEEK,
TRUNC(sysdate,'W') AS FIRST_DAY_OF_WEEK2, -- First WeekDay of month treats as first day of week then get nearest weekday in the past
TRUNC(sysdate,'Q') AS FIRST_DAY_OF_QUARTER,
ADD_MONTHS( TRUNC(sysdate,'YYYY'), 12)-1 AS LAST_DAY_OF_YEAR,
ADD_MONTHS( TRUNC(sysdate,'Q'), 3)-1 AS LAST_DAY_OF_QUARTER,
ADD_MONTHS( TRUNC(sysdate,'MM'), 1)-1 AS LAST_DAY_OF_MONTH
FROM DUAL;
--SELECT DATEADD(MONTH, 5, '2023-01-01’) FROM DUAL; (IT IS NOT WORKING IN MY CURRENT VERSION)
-- SO I AM USING ADD_MONTHS INSTEAD OF DATEADD FUNCTION IN THE ABOVE QUERY
SELECT TO_DATE('21-MAY-2017', 'DD-MM-YYYY') AS INPUT_DATE,
ROUND(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'YYYY') AS FIRST_DAY_OF_YEAR,
ROUND(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'MM') AS FIRST_DAY_OF_MONTH1,
-- DAY IS MORE THAN '15' SO WE GET NEXT MONTH 1ST DATE
ROUND(TO_DATE('11-MAY-2017', 'DD-MM-YYYY'),'MM') AS FIRST_DAY_OF_MONTH2,
-- DAY IS LESS THAN '15' SO WE GET CURRENT MONTH 1ST DATE
ROUND(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'WW') AS FIRST_DAY_OF_WEEK, -- Sunday is first day of week
ROUND(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'W') AS FIRST_DAY_OF_WEEK2 , -- First WeekDay of month treats as first day of
--week then get nearest weekday in the past
ROUND(TO_DATE('21-MAY-2017', 'DD-MM-YYYY'),'Q') AS FIRST_DAY_OF_QUARTER,
-- DAY IS MORE THAN '45' IN QUARTER SO WE GET NEXT QUARTER 1ST DATE
ROUND(TO_DATE('10-MAY-2017', 'DD-MM-YYYY'),'Q') AS FIRST_DAY_OF_QUARTER2
-- DAY IS LESS THAN '45' IN QUARTER SO WE GET CURRENT QUARTER 1ST DATE
FROM DUAL;
SELECT
TO_DATE('21-MAY-2017', 'DD-MM-YYYY') AS INPUT_DATE
, NEXT_DAY(TO_DATE('21-MAY-2017', 'DD-MM-YYYY') , 'MONDAY') AS NEXT_MONDAY
, NEXT_DAY(TO_DATE('21-MAY-2017', 'DD-MM-YYYY') , 'SATDAY') AS NEXT_SATDAY
, LAST_DAY(TO_DATE('21-MAY-2017', 'DD-MM-YYYY') ) AS LAST_DAY_OF_MONTH
FROM DUAL;
SELECT SYSDATE
, to_char( SYSDATE , 'DY') DAY_OF_WEEK_STR
, to_char( SYSDATE , 'D') DAY_OF_WEEK_NUM
, to_char( SYSDATE , 'DDD') DAY_OF_YEAR
, to_char( SYSDATE , 'DD') DAY_OF_MONTH
, to_char( SYSDATE , 'DAY') DAY_OF_WEEK
, to_char( SYSDATE , 'IW') WEEKNUM_YEAR
, to_char( SYSDATE , 'WW') WEEKNUM_YEAR2
, to_char( SYSDATE , 'Q') QUART
, to_char( SYSDATE , 'MM') MONTHS
, to_char( SYSDATE , 'MON') MONTH_ABB
, to_char( SYSDATE , 'MONTH') MONTH_ABB2
, to_char( SYSDATE , 'YYYY') YEAR
from dual;
SELECT SYSDATE
, TO_DATE('2023-JAN-10', 'YYYY-MM-DD') SAMP_DT
, TO_DATE('2023-10', 'YYYY-DD') SAMP_DT2
, TO_DATE('2023-12', 'YYYY-MM') SAMP_DT3
, TO_DATE('2023-31', 'YYYY-DD') SAMP_DT4
from dual;
------------------- End - DATE FUNCTIONS -------------------
------------------------------------------------------------------------------------------
-------------------------- Start - AGGREGATIONS --------------------------
-- GROUP BY : WE USE GROUP BY CLAUSE TO AGGREGATE THE DATA
-- SPLIT THE DATA THEN APPLY AGGRGATE FUNCTION THEN COMBINE
--1) COUNT
SELECT * FROM SAMPLE_SALES;
SELECT COUNT(*) AS NUM_RECS FROM SAMPLE_SALES;
SELECT COUNT(*) AS NUM_RECS FROM SAMPLE_SALES WHERE SALES>150;
SELECT COUNT( DISTINCT SHIP_MODE) AS NUM_SHIP_MODE FROM SAMPLE_SALES;
SELECT COUNT( DISTINCT SHIP_MODE) AS NUM_SHIP_MODE FROM SAMPLE_SALES;
SELECT COUNT(DISTINCT CATEGORY) DIST_CNT_CATEGORY
, COUNT(ALL CATEGORY) CNT_CATEGORY
, COUNT(DISTINCT SUB_CATEGORY) DIST_CNT_SUB_CAT
, COUNT( SUB_CATEGORY) CNT_SUB_CAT
FROM PRODUCT_TABLE_SAMPLE ;
SELECT DISTINCT CATEGORY FROM PRODUCT_TABLE_SAMPLE ORDER BY 1 ;
SELECT DISTINCT UPPER(SUB_CATEGORY) SUB_CAT FROM PRODUCT_TABLE_SAMPLE ORDER BY 1;
--2) SUM, MAX, MIN, AVG
SELECT SHIP_MODE
, SUM(SALES) TOT_SALES
, COUNT(ALL SHIP_MODE) NUM_REC
, COUNT(DISTINCT SHIP_MODE) NUM_REC
, MAX( SALES) MAX_SALES
, MIN( SALES) MIN_SALES
, AVG( SALES) AVG_SALES
, MEDIAN( SALES) MEDIAN_SALES
, STDDEV( SALES) STD_SALES
FROM SAMPLE_SALES
GROUP BY SHIP_MODE
ORDER BY SHIP_MODE;
---------- JOINS --------------
-- EQUI JOIN OR INNER JOIN - Getting All records Common Records from both tables;
---- ADDING DUMMY DATA
drop table Product_Table_Joins;
create table Product_Table_Joins (
Product_ID varchar2(500),
Product_Name varchar2(500) ,
Sub_Category varchar2(500),
Category varchar2(500),
Segment varchar2(500)
);
Insert into Product_Table_Joins values ('DUMMY_5','P1','PSC_1','PC_1','Consumer');
Insert into Product_Table_Joins values ('DUMMY_6','P2','PSC_2','PC_2','Corporate');
Insert into Product_Table_Joins values ('DUMMY_7','P3','PSC_3','PC_3','Consumer');
Insert into Product_Table_Joins values ('DUMMY_8','P4','PSC_4','PC_4','Home Office');
Insert into Product_Table_Joins values ('DUMMY_9','P5','PSC_5','PC_5','Consumer');
Insert into Product_Table_Joins values ('DUMMY_10','P6','PSC_6','PC_6','Corporate');
Insert into Product_Table_Joins values ('DUMMY_11','P7','PSC_7','PC_7','Consumer');
Insert into Product_Table_Joins values ('DUMMY_12','P8','PSC_8','PC_8','Consumer');
Insert into Product_Table_Joins values ('DUMMY_13','P9','PSC_9','PC_9','Corporate');
Insert into Product_1 values ('DUMMY_5','P1','PSC_1','PC_1','Consumer');
Insert into Product_1 values ('DUMMY_6','P2','PSC_2','PC_2','Corporate');
Insert into Product_1 values ('DUMMY_7','P3','PSC_3','PC_3','Consumer');
Insert into Product_1 values ('DUMMY_8','P4','PSC_4','PC_4','Home Office');
Insert into Product_1 values ('DUMMY_4','P5','PSC_5','PC_5','Consumer');
Insert into Product_1 values ('DUMMY_5','P6','PSC_6','PC_6','Corporate');
Insert into Product_1 values ('DUMMY_1','P7','PSC_7','PC_7','Consumer');
Insert into Product_1 values ('DUMMY_2','P8','PSC_8','PC_8','Consumer');
Insert into Product_1 values ('DUMMY_3','P9','PSC_9','PC_9','Corporate');
Insert into Product_Table_Joins values ('DUMMY_5','P1','PSC_1','PC_1','Consumer');
Insert into Product_Table_Joins values ('DUMMY_6','P2','PSC_2','PC_2','Corporate');
Insert into Product_Table_Joins values ('DUMMY_7','P3','PSC_3','PC_3','Consumer');
Insert into Product_Table_Joins values ('DUMMY_8','P4','PSC_4','PC_4','Home Office');
Insert into Product_Table_Joins values ('DUMMY_4','P5','PSC_5','PC_5','Consumer');
Insert into Product_Table_Joins values ('DUMMY_5','P6','PSC_6','PC_6','Corporate');
Insert into Product_Table_Joins values ('DUMMY_1','P7','PSC_7','PC_7','Consumer');
Insert into Product_Table_Joins values ('DUMMY_2','P8','PSC_8','PC_8','Consumer');
Insert into Product_Table_Joins values ('DUMMY_3','P9','PSC_9','PC_9','Corporate');
Insert into Product_Table_Joins values ('P1','P10','PSC_10','PC_10','Consumer');
Insert into Product_Table_Joins values ('P2','P11','PSC_11','PC_11','Corporate');
Insert into Product_Table_Joins values ('P3','P12','PSC_12','PC_12','Consumer');
Insert into Product_Table_Joins values ('P4','P13','PSC_13','PC_13','Corporate');
DROP TABLE Sales_Table_Joins;
create table Sales_Table_Joins (
Row_ID varchar2(500) ,
Order_ID varchar2(500) ,
Order_Date Date,
Ship_Date Date,
Ship_Mode varchar2(500) ,
Product_ID varchar2(500) ,
Customer_ID varchar2(500),
Postal_Code varchar2(500),
Sales number(6,2),
Quantity number(6,2)
);
Insert into Sales_Table_Joins values ('DUMMY_1','DUMMY_ORDER_1',TO_DATE('01/01/1999', 'mm/dd/yyyy'),TO_DATE('01/02/1999', 'mm/dd/yyyy'),'NORMAL Class','P1','JG-15160','98026',68.46,2);
Insert into Sales_Table_Joins values ('DUMMY_2','DUMMY_ORDER_2',TO_DATE('01/02/1999', 'mm/dd/yyyy'),TO_DATE('01/03/1999', 'mm/dd/yyyy'),'Standard Class','P2','MC-17845','40214',1207.84,8);
Insert into Sales_Table_Joins values ('DUMMY_3','DUMMY_ORDER_3',TO_DATE('01/03/1999', 'mm/dd/yyyy'),TO_DATE('01/04/1999', 'mm/dd/yyyy'),'First Class','P1','KB-16600','47201',209.3,2);
Insert into Sales_Table_Joins values ('DUMMY_4','DUMMY_ORDER_4',TO_DATE('01/04/1999', 'mm/dd/yyyy'),TO_DATE('01/05/1999', 'mm/dd/yyyy'),'Standard Class','P1','BP-11185','10009',323.136,4);
Insert into Sales_Table_Joins values ('DUMMY_5','DUMMY_ORDER_5',TO_DATE('01/05/1999', 'mm/dd/yyyy'),TO_DATE('01/06/1999', 'mm/dd/yyyy'),'Same Day','P2','BS-11755','10009',90.93,7);
Insert into Sales_Table_Joins values ('DUMMY_6','DUMMY_ORDER_1',TO_DATE('01/01/1999', 'mm/dd/yyyy'),TO_DATE('01/02/1999', 'mm/dd/yyyy'),'NORMAL Class','P3','JG-15160','98026',68.46,2);
Insert into Sales_Table_Joins values ('DUMMY_7','DUMMY_ORDER_2',TO_DATE('01/02/1999', 'mm/dd/yyyy'),TO_DATE('01/03/1999', 'mm/dd/yyyy'),'Standard Class','DUMMY_4','MC-17845','40214',1207.84,8);
Insert into Sales_Table_Joins values ('DUMMY_8','DUMMY_ORDER_3',TO_DATE('01/03/1999', 'mm/dd/yyyy'),TO_DATE('01/04/1999', 'mm/dd/yyyy'),'First Class','DUMMY_5','KB-16600','47201',209.3,2);
Insert into Sales_Table_Joins values ('DUMMY_9','DUMMY_ORDER_4',TO_DATE('01/04/1999', 'mm/dd/yyyy'),TO_DATE('01/05/1999', 'mm/dd/yyyy'),'Standard Class','DUMMY_1','BP-11185','10009',323.136,4);
Insert into Sales_Table_Joins values ('DUMMY_10','DUMMY_ORDER_5',TO_DATE('01/05/1999', 'mm/dd/yyyy'),TO_DATE('01/06/1999', 'mm/dd/yyyy'),'Same Day','DUMMY_2','BS-11755','10009',258.75,3);
Insert into Sales_Table_Joins values ('DUMMY_1','DUMMY_ORDER_1',TO_DATE('01/01/1999', 'mm/dd/yyyy'),TO_DATE('01/02/1999', 'mm/dd/yyyy'),'NORMAL Class','DUMMY_3','JG-15160','98026',209.3,2);
Insert into Sales_Table_Joins values ('DUMMY_2','DUMMY_ORDER_1',TO_DATE('01/03/1999', 'mm/dd/yyyy'),TO_DATE('01/04/1999', 'mm/dd/yyyy'),'First Class','DUMMY_1','KB-16600','47201',323.136,4);
Insert into Sales_Table_Joins values ('DUMMY_3','DUMMY_ORDER_2',TO_DATE('01/04/1999', 'mm/dd/yyyy'),TO_DATE('01/05/1999', 'mm/dd/yyyy'),'Standard Class','DUMMY_2','BP-11185','10009',90.93,7);
Insert into Sales_Table_Joins values ('DUMMY_4','DUMMY_ORDER_3',TO_DATE('01/05/1999', 'mm/dd/yyyy'),TO_DATE('01/06/1999', 'mm/dd/yyyy'),'Same Day','DUMMY_3','BS-11755','10009',52.776,3);
Insert into Sales_Table_Joins values ('DUMMY_5','DUMMY_ORDER_5',TO_DATE('01/05/1999', 'mm/dd/yyyy'),TO_DATE('01/06/1999', 'mm/dd/yyyy'),'Same Day','DUMMY_4','BS-11755','10009',90.93,7);
Insert into Sales_Table_Joins values ('DUMMY_1','DUMMY_ORDER_1',TO_DATE('01/01/1999', 'mm/dd/yyyy'),TO_DATE('01/02/1999', 'mm/dd/yyyy'),'NORMAL Class','DUMMY_5','JG-15160','98026',209.3,2);
Insert into Sales_Table_Joins values ('DUMMY_2','DUMMY_ORDER_1',TO_DATE('01/03/1999', 'mm/dd/yyyy'),TO_DATE('01/04/1999', 'mm/dd/yyyy'),'First Class','DUMMY_1','KB-16600','47201',323.136,4);
Insert into Sales_Table_Joins values ('DUMMY_3','DUMMY_ORDER_2',TO_DATE('01/04/1999', 'mm/dd/yyyy'),TO_DATE('01/05/1999', 'mm/dd/yyyy'),'Standard Class','DUMMY_2','BP-11185','10009',90.93,7);
Insert into Sales_Table_Joins values ('DUMMY_4','DUMMY_ORDER_3',TO_DATE('01/05/1999', 'mm/dd/yyyy'),TO_DATE('01/06/1999', 'mm/dd/yyyy'),'Same Day','DUMMY_3','BS-11755','10009',52.776,3);
Insert into Sales_Table_Joins values ('DUMMY_5','DUMMY_ORDER_5',TO_DATE('01/05/1999', 'mm/dd/yyyy'),TO_DATE('01/06/1999', 'mm/dd/yyyy'),'Same Day','P29','BS-11755','10009',90.93,7);
select * from Sales_Table_Joins;
select * from Product_Table_Joins;
----- INNER JOIN FETCHES ONLY COMMON RECORDS BETWEEN TWO TABLES BASED ON THE CONDITION;
SELECT DISTINCT A.PRODUCT_ID ,B.PRODUCT_ID PID
FROM SALES_TABLE_JOINS A
INNER JOIN
PRODUCT_TABLE_JOINS B
ON (A.PRODUCT_ID = B.PRODUCT_ID)
----- LEFT JOIN FETCHES ALL FROM LEFT SIDE TABLE ONLY COMMON RECORDS FROM RIGHT SIDE TABLE BASED ON THE CONDITION;
--- IF ANY VALUE FROM LEFT TABLE THOSE DONT HAVE RELATED VALUES IN RIGHT SIDE TABLE THEN WE GET THE NULLS
SELECT DISTINCT A.PRODUCT_ID ,B.PRODUCT_ID PID
FROM SALES_TABLE_JOINS A
LEFT JOIN
PRODUCT_TABLE_JOINS B
ON (A.PRODUCT_ID = B.PRODUCT_ID)
----- RIGHT JOIN FETCHES ALL FROM RIGHT SIDE TABLE ONLY COMMON RECORDS FROM LEFT SIDE TABLE BASED ON THE CONDITION;
--- IF ANY VALUE FROM RIGHT TABLE THOSE DONT HAVE RELATED VALUES IN LEFT SIDE TABLE THEN WE GET THE NULLS
SELECT DISTINCT A.PRODUCT_ID ,B.PRODUCT_ID PID
FROM SALES_TABLE_JOINS A
RIGHT JOIN
PRODUCT_TABLE_JOINS B
ON (A.PRODUCT_ID = B.PRODUCT_ID);
----- RIGHT JOIN FETCHES ALL FROM RIGHT SIDE TABLE ONLY COMMON RECORDS FROM LEFT SIDE TABLE BASED ON THE CONDITION;
--- IF ANY VALUE FROM RIGHT TABLE THOSE DONT HAVE RELATED VALUES IN LEFT SIDE TABLE THEN WE GET THE NULLS
SELECT DISTINCT A.PRODUCT_ID ,B.PRODUCT_ID PID
FROM SALES_TABLE_JOINS A
FULL OUTER JOIN
PRODUCT_TABLE_JOINS B
ON (A.PRODUCT_ID = B.PRODUCT_ID);
SELECT DISTINCT PRODUCT_ID FROM SALES_TABLE_JOINS;
SELECT DISTINCT PRODUCT_ID FROM PRODUCT_TABLE_JOINS;
-----------------------------------Sample Tables for Analysis ----------------
----------------------------------- Creating Tables and inserting Data into Tables ----------------
----------------------------------- Adding constraints after create a Table ----------------
No comments:
Post a Comment