/* 
*/
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
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');
/*
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) )
;
--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.
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.
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
number(3,1) it means total 3 digits in that 1 for decimal remain two for integer;
xyz.abc - 'xyz' is integer part, 'abc' 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 & 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')) ;
-------------------Looking at the structure of the table by using the following query;
describe Product_Sample;
Looking at the records of the table by using the following query;
select * from Product_Sample;
Looking at the records of the table by using the following query;
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)
);
------------------------
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);
Insert into Sample_Sales values ('768','CA-2011-162775',TO_DATE('01/12/2020', 'mm/dd/yyyy'),TO_DATE('01/14/2020', 'mm/dd/yyyy'),'Second Class','OFF-ST-10000025','CS-12250','71111',572.58,6);
-------------------------- 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 -------------------
EXCEL FILE
----------------------- FILTERS --------------------------
----------------------- Joins ----------------------
We are going to use the following tables to see how joins work.
LEFT JOIN FETCHES ALL FROM LEFT SIDE TABLE AND 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.
IN THE FOLLOWING EXAMPLE, P29 EXIST IN LEFT SIDE TABLE BUT DOES NOT EXIST IN RIGHT SIDE TABLE, SO WE ARE GETTING NULL;
---------------
Right join fetches all records from right side table AND only common records from left side table based on the condition.
If any value from right table those don't' have related values in left side table then we get the nulls.
--------------SET FUNCTIONS ----------------
No comments:
Post a Comment