Home
Videos uploaded by user “Prabhat Sahu”
Session3: RDB Terminologies and SQL Statements
 
15:21
Todays agenda: "Relational Database Terminologies” "SQL Statements in ORACLE"
Views: 94 Prabhat Sahu
session2 RDBMS Properties
 
06:24
Agenda: RDBMS Relational Database properties Generic Features of SQL
Views: 125 Prabhat Sahu
Session6 Data type in Oracle
 
15:25
Session 6: Datatypes In Oracle   ALPHABET           : A-Z , a-z NUMBER              : 0-9 (with precision and scale) DATE / Temporal  : any Date and time (Hours, Minutes, Seconds, Mili-seconds, Timestamp, Timezone etc)   Alphabet + Number = Alphanumeric Data                                     = String / CHARACTER Datatype Category Number                    =  Numeric Datatype Category Date                         = Date Datatype Category 1. CHARACTER Datatype: CHAR, VARCHAR, NCHAR: CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9 , all keyboard characters etc. The default size is 1 character and it can store maximum up to 2000 bytes. Example : EName, EmpID, PassportNo, SSN, etc. EName CHAR(10) := ‘TOM’; wastage of 7 space after the string EName VARCHAR(10) := ‘TOM’; Spaces can be Reuse which left after the string NCHAR additionally handles NLS(National Language Support). Oracle supports a reliable Unicode datatype through NCHAR , NVARCHAR2 , and NCLOB  VARCHAR2, NVARCHAR2: These are Variable length datatype. VARCHAR2 handles alphanumeric character string whereas NVARCHAR2 handles alphanumeric character string with NLS(National Language Support). The default size is 1 character and it can store maximum up to 4000 bytes.   LONG:  Variable length string.  (Maximum size: 2 GB - 1) Only one LONG column is allowed per table. RAW:    Variable length binary string (Maximum size 2000 bytes) LONG RAW: Variable length binary string (Maximum size 2GB) 2. NUMERIC Datatype: NUMBER: It stores Numeric values and performs numeric calculations. NUMBER,   NUMBER(n),   NUMBER(p,s) It stores Numbers up to 38 digits of precision. SeqNo NUMBER;                     1, 123, 12345678 EmpID NUMBER(4);                 1, 123, 1234 Sal NUMBER(7,2);                     23456.78 , 123.45 — correction in video: Sal NUMBER(a7,2); which is wrong please ignore. 1234567 can be a type of NUMBER, NUMBER(7), NUMBER(7,0) It can store both integer and floating point numbers NUMERIC(p,s) FLOAT:   Ex:  EmpSal FLOAT;    FLOAT(7)       Decimal Points allowed DEC(p,s), DECIMAL(p,s) , REAL, DOUBLE PRECISION INTEGER:   Ex:  SSN INTEGER;       Decimal Points are not allowed INT, SMALLINT 3. DATE Datatype: DATE: It stores DATE(Date, Month, Year) and Time(Hour, Minute, Second, AM/PM) and performs calculations with such data. Default DATE format in Oracle is “DD-MON-YY” Based on "Gregorian calendar" where the date ranges from “JAN 1 4712 BC” to “DEC 31 9999 AD” doj DATE;    “18-MAR-2010 12:30:00 PM” TIMESTAMP:    It can store all parameters as DATE datatype and additionally it can have “Fraction of seconds” and TIMESTAMP WITH TIMEZONE / TIMESTAMP WITHOUT TIMEZONE. Range from 0-9 digits, the default size is 6. 4. LOB Datatype: LOB: “Large Object” data. It can store pictures, motion pictures, Textfiles etc. CLOB: “Character Large Object” is used to store structured information like a text file with a specific file format. BLOB: “Binary Large Object” is used to store Un-structured information like Image, JPEG files, MPEG files etc. BFILE: “Binary File” is used to store the pointer to a specific file / Just store the location of a file. Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) Extra Information: NCLOB : It supports all the character set supported by CLOB and additionally it handles NLS(National Language Support ) Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) ROWID and UROWID(optional size) Datatype: contains fixed length Binary data. BBBBBBB.RRRR.FFFFF combination of BLOCK-ROW-DATABASE FILE Physical and Logical ROWID Upcoming Session: Session 7: Populating Data into Tables(INSERT Statement): Inserting data into all columns of a table Inserting data into Required columns of a table Inserting NULL value into a table Inserting Special Values(USER / SYSDATE) into a table Supplying data at runtime(using & and &&) THANK YOU :)
Views: 235 Prabhat Sahu
Session5: CREATE TABLE Syntax and Rules
 
17:50
Session5: CREATE TABLE Syntax and Rules: -- Required Privileges: conn scott/tiger CREATE USER usr1 IDENTIFIED BY usr1; GRANT UNLIMITED TABLESPACE TO usr1; GRANT CREATE SESSION TO usr1; conn usr1/usr1 CREATE TABLE emp1(id NUMBER); -- Insufficient privileges conn scott/tiger GRANT CREATE TABLE TO usr1; conn usr1/usr1 CREATE TABLE emp1(id NUMBER); -- Should Pass -- Table name can't be duplicate: CREATE TABLE emp1(id NUMBER); -- Table already exists. -- Table name is not Case-Sensitive. CREATE TABLE EMP2(id NUMBER); INSERT INTO emp2 VALUES(1001); SELECT * FROM emp2; SELECT * FROM EMP2; CREATE TABLE us_emp_details(id NUMBER); CREATE TABLE us_emp#details(id NUMBER); -- Casesensitive Table Naming: CREATE TABLE "[email protected]"(id NUMBER); SELECT * FROM "[email protected]"; SELECT * FROM [email protected]; -- connection description for remote database not found SELECT * FROM [email protected]; -- It will consider the @ character as dblink -- Reserved keywords are not allowed as table name: CREATE TABLE create (col1 NUMBER(5)); CREATE TABLE table (col1 NUMBER(5)); CREATE TABLE insert (col1 NUMBER(5)); CREATE TABLE into (col1 NUMBER(5)); CREATE TABLE null (col1 NUMBER(5)); -- keyword within double codes consider as character string, and works fine. CREATE TABLE "create" (col1 NUMBER(5)); CREATE TABLE "table" (col1 NUMBER(5));
Views: 57 Prabhat Sahu
Session7 INSERT Statement
 
24:08
Session 7: Populating(INSERT) data into database table in Oracle. 1. Insert data into all columns(complete record) in a table. 2. Insert data into a table in same/random order of column. 3. Insert data into selective columns of a table. 4. Inserting NULL value into table. 5. Prompt the column to insert values in runtime using ‘&' and ‘&&'. 6. Insert data using select statement. 7. Create table as select.(create table with/without data). 8. Insert huge amount of dummy data using a PL/SQL block. — Create a table. CREATE TABLE emp (EmpID NUMBER(4) PRIMARY KEY, EName VARCHAR2(20) NOT NULL, Job VARCHAR2(20), DeptNo NUMBER(3), Sal NUMBER(7,2)); Insert data into all columns(complete record) in a table. same order of column: INSERT INTO emp VALUES(111, 'SCOTT', 'MANAGER', 10, 50000); INSERT INTO emp(EmpID, EName, Job, DeptNo, Sal) VALUES(112, 'JONES', 'DESIGNER', 20, 40000); Random order of column values: INSERT INTO emp VALUES(113, 45000, 10, 'SMITH', 'MANAGER'); — show error, why ?? INSERT INTO emp(EmpID, Sal , DeptNo, EName, Job) VALUES(113, 45000, 10, 'SMITH', 'MANAGER'); Insert data into selective columns of a table. INSERT INTO emp VALUES(122, 10, 'SMITH'); — show error INSERT INTO emp(EmpID, DeptNo, EName) VALUES(222, 10, 'SMITH'); Inserting NULL value into table. INSERT INTO emp VALUES(214, 'JONES', NULL, NULL, 40000); INSERT INTO emp(EmpID, EName, Sal) VALUES(215, 'ADEMS', 40000); Prompt the column to insert values in runtime using ‘&' and ‘&&'. INSERT INTO emp VALUES(&empid, '&ename', '&job', &deptno, &sal); INSERT INTO emp VALUES(&empid, &ename, &job, &deptno, &sal); INSERT INTO emp(EmpID, EName, Job, DeptNo, Sal) VALUES (&empid, '&ename', '&job', &deptno, &sal); INSERT INTO emp(EmpID, DeptNo, EName) VALUES (&empid, &deptno, '&ename'); INSERT INTO emp(EmpID, DeptNo, EName, Sal) VALUES (&empid, &&deptno, '&ename', &&sal); Insert data using select statement. INSERT INTO emp (SELECT 222, 'SATISH', 'MANAGER', 30, 60000 FROM DUAL); INSERT INTO emp VALUES(SELECT 223, 'RAHUL', 'SALESMAN', 30, 30000 FROM DUAL); — show error. Create table as select.(create table with/without data). CREATE TABLE emp_copy AS SELECT * FROM emp; — create table with same table structure, with data. SELECT * FROM tab; SELECT * FROM emp_copy; CREATE TABLE emp_copy2 AS SELECT * FROM emp where 1 != 1; — create table with same table structure, without data. SELECT * FROM tab; SELECT * FROM emp_copy2; Insert huge amount of dummy data using a PL/SQL block. BEGIN FOR i IN 1..100 LOOP INSERT INTO emp VALUES(i, 'Ename_'||i, 'Job_'||i, (MOD(i,5)+1)*10, (MOD(i,5)+1)*10000); END LOOP; END; / SELECT * FROM emp;
Views: 32 Prabhat Sahu
Session4 Part1
 
07:59
Part1: Connecting to Oracle using SQL*Plus prompt Next Related Part: Part2: SQL Buffer Use of ED / EDITOR Saving SQL Buffer text as SQL Script Execute those saved script file Part3: Use of Spool file Changing Column Format.
Views: 85 Prabhat Sahu
Session4 Part3
 
06:10
Session 4: Part1: Connecting to Oracle using SQL*Plus prompt https://youtu.be/40CJQvr-98A Part2: SQL Buffer https://youtu.be/DYEGakrBpGM Saving SQL Buffer text as SQL Script Execute those saved script file Part3: Spool file Current Video Changing Column Format.
Views: 32 Prabhat Sahu
Session4 Part2
 
10:35
Due to some video clarity issue in the previous video(for Session4 Part2), re-uploading the same with better quality. Session 4: Part1: Connecting to Oracle using SQL*Plus prompt https://youtu.be/40CJQvr-98A Part2: SQL Buffer Current video Saving SQL Buffer text as SQL Script Execute those saved script file Part3: Spool file Next Video Changing Column Format.
Views: 18 Prabhat Sahu
Session4 Part2
 
10:35
Session 4: Part1: Connecting to Oracle using SQL*Plus prompt https://youtu.be/40CJQvr-98A Part2: SQL Buffer Current video Saving SQL Buffer text as SQL Script Execute those saved script file Part3: Spool file Next Video Changing Column Format.
Views: 37 Prabhat Sahu
Session4 Part3
 
06:11
Due to some video clarity issue in the previous video(for Session4: Part3), re-uploading the same with better quality video.
Views: 14 Prabhat Sahu
Session 1 Introduction To Database
 
19:18
Agenda: •Data •Information •Metadata •Database •Problem with respect to Database •DBMS •Features of DBMS •Various DBMS Model
Views: 471 Prabhat Sahu