YT YouTube2Text

YouTube2Text → Transcripts → ORACLE - SQL AND PLSQL - 100 QUESTIONS WITH ANSWERS

ORACLE - SQL AND PLSQL - 100 QUESTIONS WITH ANSWERS — Transcript

by Karthick Ravichandran · 7,746 words · 1,442 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:01hey guys good morning this is
0:05and this video especially we're
0:07recording for those people who are
0:10searching for SQL and pl SQL jobs can
0:14refer this video
0:16not only for a specific batch I'm
0:18recording for this uh
0:20recording this
0:22interview question session it's for all
0:25SQL SQL Developers
0:28foreign
0:34community
0:45in the video
0:48helpful for English
0:51between case and decode
0:54defaulty or interview question
0:57is
1:05concept
1:10records
1:37and decode is a Only Solution in SQL
1:40okay case was
1:48relational and logical operators
2:07okay
2:08so case law
2:15foreign foreign
2:50both are used to compare data between
2:54columns
2:55we can use relational and logical
2:57operators in case whereas we cannot use
3:00and decode case will be faster than
3:03decode
3:04so by default the code is acting as an
3:07equal to operator
3:10we can use case insect select segment as
3:14well as an independent Block in PL SQL
3:16block
3:17but decode can be used only in
3:20select the statement
3:24okay and also we can use a case and
3:27decode
3:28in where condition
3:31wherever conditions are applied based on
3:33if this then this if that then this in
3:38such kind of situations
3:40if you want to apply this case or decode
3:44in
3:45where condition we can apply so there is
3:48no issues in that
3:51difference between nvl and nvl2
3:56randomly
3:57single row function will have null
3:59functions
4:02null value is
4:09nvl will accept two arguments n will 2
4:13will accept three arguments
4:16in nvl if the first argument is null
4:19then the second argument will be
4:21displayed
4:22in nbl2 if the first argument is null
4:26then third argument will be displayed if
4:29the first argument is not null then
4:31second argument will be displayed
4:35null's first nulls last
4:39null values management
4:45hippo employees stable under Justin
4:48records it is
4:52commission PCT
5:04foreign
5:38foreign
5:58Union will display unique values in
6:01ascending order
6:03Union all will display the records as it
6:07is in the query
6:10Union all will be faster than Union
6:13videos
6:33primary key will not accept duplicate
6:36values and null values
6:38unique constraint
6:40will not accept duplicate values but it
6:43will accept multiple null values no
6:46issues in that
6:47a table can have only one primary key
6:50but unique connection can have multiple
6:53both primary K and unique concern if you
6:56are keeping on a column Oracle will
6:59create unique index on that column
7:03is
7:08View
7:14database objects
7:16view is used to store a query
7:21materialist view is used to store a
7:24query result
7:28and generally we just hide the logic by
7:32using View
7:35if I want to store that logic data I
7:38will be using materialized view
7:42we have two types of view simple View
7:44and complex View we can perform BML
7:47operation on simple view but we cannot
7:49perform
7:50on complex view we can create a view for
7:54non-existing object by using Force View
7:57we have with read only in which check
7:59option as a restriction method in views
8:03in check option we need a where
8:05condition only the condition satisfied
8:08records alone will be enabled for edit
8:12option
8:13and in simple view we can able to create
8:16this restriction method
8:18and complexity is nothing but if the
8:20query having group functions analytical
8:23functions sub queries joins set
8:25operators dictionary tables single row
8:28functions
8:30and these kind of things that we have
8:32formed as a query and that query is
8:35stored in a view
8:36we can call it as a complex View
8:40we can create triggers for view by using
8:44instead option
8:46and view will be
8:49error when you are making changes on the
8:52base table
8:54which we are mentioning in the query are
8:56if any changes happen the column name
9:00The View will not work because the query
9:02will not work
9:03so V will not be work
9:05the solution for this is we need to
9:08recreate The View
9:10else
9:11The View still show
9:13Seattle
9:15metal issue we have three refresh
9:17Concepts like a complete refresh sorry
9:20complete refresh fast refresh and force
9:23refresh
9:25for fast and force log table is
9:27mandatory
9:29log table is used to track the row ID or
9:32primary key ID to track what data got
9:36you know changed
9:39so that changes will be tracked by log
9:43table so at the time of refreshing the
9:45metal issue Oracle will refresh the log
9:48table
9:49so either
9:51cost or Force
9:53are complete Oracle will be choosingly
9:56so for complete refresh we will be using
9:59dbms underscore embu.refresh of Metals
10:02view name
10:03comma
10:05C
10:07okay for fast we'll be using f
10:11and for Force we'll be using question
10:13mark
10:14to refresh the most Limitless View
10:17and Matrix you generally used
10:20for the purpose of data report
10:23generation
10:26instead of accessing keep on the master
10:29table
10:30where I can have the same data in my
10:32Mentalist view whenever the same data is
10:35getting accessed I can use
10:38metal issue
10:40which improves the database performance
10:42generally the metal issues are used for
10:46remote kind of
10:47database systems
10:50in the points don't mention any other
10:53points we cannot perform it in a DML
10:56operation on em View
10:58we cannot create index or View but we
11:02can create index and mu
11:06and next Point replace and Translate
11:10both are string manipulation function
11:14our character manipulation function
11:17if we want to change a word by another
11:20word I can use replace
11:24if I want to convert a letter by another
11:26letter I can use Translate
11:30in string and substring
11:32these function are also string
11:35manipulation function
11:37if I want to search
11:39a particular character position
11:43in a word I can use in string
11:47if I want to extract a particular
11:51word or letters from a statement or from
11:54a word I can use substring
11:57in string will accept four arguments
12:00substring will accept the three
12:01arguments
12:03replace will accept three arguments
12:07and translate is also accepting three
12:10arguments
12:11if you are missing the third argument
12:15the argument which you mentioned as a
12:17second argument will be removed from the
12:21given input if you are not mentioning
12:24the third argument whereas if you are
12:27removing if you are not mentioning the
12:29third argument it will be an error in
12:30Translate
12:32then
12:34foreign
12:41are applicable for SQL loader feature
12:44SQL loader is a bulk loader utility
12:47where we are using to load data in a
12:50bulk manner
12:52so these files
12:54are nothing but if I want to track the
12:57process summary
12:59of the loading I can use log file
13:03while loading data if I face any Oracle
13:06kind of issues those errors will be
13:09tracked in bad file
13:12if I mention any condition
13:14Over Control file
13:16using when clause
13:18if those condition failed records will
13:21be tracked then discard phase
13:25and
13:27there are two types of methodology
13:30one is
13:32direct path and another one is
13:34conventional power
13:36if you are mentioning direct equal to
13:38true the record loading concept will be
13:41faster whereas false it will be checking
13:45every constraints and tables and what
13:47are the parameters we have mentioned on
13:50the table
13:51so these parameters will be bypassed in
13:54direct path
13:56so records loading concept will be
13:58faster
14:00and which version of Oracle that you are
14:02using currently currently I'm using
14:04lebanci version of Oracle and I have
14:06basic knowledge of 12C in that g is
14:10nothing but grid and C is something but
14:12cloud
14:13and ie stands for integration
14:16what about your DB size you can mention
14:197 TB
14:21or 6 DB TB in the sense terabyte
14:25what are the connection strings required
14:27to connect DB
14:29so for that uh
14:31service name host port
14:36and
14:39user ID and password
14:42these are the things are required
14:45to connect to DB
14:48and next question
14:52when you get a viewers already have
14:55mentioned
14:56while discussing about view on M View
14:58when our word test synonym translation
15:02is no longer valid I have created a
15:05synonym
15:06I have dropped a base object if if I now
15:10trying to access that synonym this error
15:13will come
15:14object is not there
15:16but synonym is still there then it will
15:20be a problem while accessing the
15:22particular synonym
15:23list of some dictionary tables
15:26user tables
15:28user objects user synonyms user
15:32sequences user in the columns user tab
15:36columns user con columns user indexes
15:41user underscore M views user underscore
15:44snapshots
15:45DB underscore history snapshot
15:49user underscore Source user underscore
15:52errors
15:54user underscore procedures
15:57so these are the
15:59Oracle dictionary tables
16:02list 5 11g feature virtual column Escape
16:07interval table partitioning regular
16:10expression
16:11and pivot unpivot
16:14and follows trigger compound trigger
16:18and these are the things that we can
16:20mention here
16:22list some of Oracle separate packages
16:25so for that we can say like DPMS
16:29underscore output dbms underscore
16:32scheduler dbms underscore jobs util
16:36underscore file
16:37and utl underscore SMTP
16:41dbms underscore utility
16:43dbms underscore metadata
16:46and deformation underscore output
16:49we can mention all this
16:53what is context switching
16:55important interview question
16:57context switching is something but the
16:59communication between SQL engine and
17:01plsql engine
17:03so while the the processing in a bulk
17:06manner the context switching will be
17:09less if you're using Loop the context
17:12switching will be high
17:13because while using Loop the records
17:16will be processed one by one
17:19and delete Cascade and non red signal
17:21these statements are applicable whenever
17:24we have a relationship between two
17:25tables
17:26one table should have primary key the
17:29primary key should refer by another
17:30table
17:32so I just want to remove a record
17:37which is a connection between two tables
17:39I cannot
17:40for that now if I want to if I you know
17:44trying to remove the records directly
17:46from
17:47parent table so I cannot able to remove
17:50that because there is a relationship
17:52between the child table
17:54so in that case
17:56I will be removing uh records from the
17:58child table then only I can able to
18:00remove record from parent table
18:02I want to
18:04remove the child record automatically
18:08when I remove the record from parent
18:09table
18:11so for that I will be using on Direct
18:13cascading
18:15cash can sense a reflection
18:18so I am trying to remove the record from
18:20parent table
18:22automatically the child record data will
18:25get removed so I will be using ondil
18:27Cascade in foreign key table
18:30so consider for example here Department
18:33table is parent table and employee
18:34stable is table table
18:36Department ID is you know linked with
18:39employees table
18:40so there we have three records Stephen
18:43ninalex
18:44if I'm trying to remove a record 90
18:48executive record from departments table
18:51it will throw an error what I am trying
18:53to do is
18:55I am just going to mention handle
18:58Cascade in employee stable foreign key
19:00column
19:01so you will be removing the foreign key
19:04and again you will be altering the table
19:07alter table table name
19:09add constraint constraint name for in
19:13key of Department ID
19:15references employees table of Department
19:18ID
19:21sorry so we are going to keep foreign
19:23key right yes
19:25references departments table of
19:28Department ID and cash cap
19:31I am repeating the statement alter table
19:34employees
19:36add constraint consent name foreign key
19:39of Department ID references Department
19:41stable Department ID and cascade
19:45so we need to keep on Del Cascade only
19:48in
19:48parent if not in the parent table only
19:51in child table
19:52and even on will set null as well
19:56so on the Sentinel is nothing but if I'm
19:59removing record from the parent table
20:01automatically
20:03that foreign key data will set null
20:06so it will not be removed from the table
20:10instead of that we'll be using on Direct
20:13set null
20:14so for example if it is 90 I am removing
20:17from a department stable automatically
20:20the streamer Nina Lex Department ID will
20:23be null
20:25if you are using on delete set null
20:29no violated in the sense
20:33came on a point no validated
20:36I have a table and column where there is
20:40no primary key and table is having some
20:43duplicate values
20:44now if I'm trying to keep primary on a
20:47column Oracle will not allow me to keep
20:49primary chemical because
20:51primary key will not accept duplicate
20:54values and as well as null values since
20:56the column is already having duplicate
20:58values
20:59you will be getting an error called
21:01unique connection violation error
21:03so but the requirement is I need to keep
21:06primary key against the data
21:08so what I am doing here is I will be
21:13bypass that unique index creation
21:15because
21:16while creating primary key on a column
21:18Oracle will create a unique Index right
21:21so to bypass that in unique index
21:25I have to create a normal B tree index
21:27on that column the column which you want
21:29to keep primary key so create a normal
21:32beta index create index index name on
21:35the column name
21:37after that we have to alter the
21:41table by using alter table table name
21:44add constraint constraint name primary
21:47key of that column name with no validate
21:51so primary key of the column name space
21:54no value data
21:56if you are using this statement
21:59the statement is
22:02alternate table table name add
22:05constraint
22:07constrain name
22:10primary key
22:13of that column name
22:16no validate
22:20when you run the statement the existing
22:22duplicate record will not be considered
22:25so it will be acting as a primary key
22:27hereafter
22:30so that's one statement how to rebuild
22:32index
22:34you can rebuild index by using alter
22:37index index name
22:39rebuild
22:43if the requirement is
22:45for production database
22:48because we have three different database
22:50systems like Oneness for development and
22:53another one is for testing and finally
22:56we have for protection
22:58so if the issue is for production
23:00database then we will need to we need to
23:03add one more keyword online if it is not
23:06production no need to mention online
23:09foreign
23:13whenever a column frequently accessed in
23:17where condition we can think about index
23:20and indexer
23:23you know used to retrieve the data
23:25faster but not all the times
23:27whenever
23:29the retrieval percentage is between 25
23:32to 30 percentage then it is good to have
23:35an index on a column else it is not good
23:37Optimizer will not access that index we
23:41can able to see whether the created
23:45index accessed in that query or Not by
23:48explaining but seeing where to explain
23:50plan
23:51so how will you take explain plan
23:53explain plan four followed by the query
23:56which you want to take explain plan then
23:59select a dbms underscore expand or
24:02display from dwell will help you to
24:05generate the explain Plum and see the
24:08explain plan
24:09and types of index B tree index bitmap
24:12index
24:13unique index Composite Index reverse key
24:16index function based index
24:19and if the column is having very less
24:21duplicate values we can call it as a
24:24high quality column and this column is
24:27frequently accessed in where Clause we
24:30can go for battery index
24:32the column is having only duplicate
24:34values and called frequently in wire
24:36condition
24:37such column can be indexed by bitmap
24:40index
24:42we can call it as a low quantity column
24:45and explain about refresh methods in
24:48view that I mentioned already while
24:50discussing about mu we have three
24:53refresh methods complete fast Force I
24:56want to refresh the entire records
25:01of a material metal issue
25:03what are the things that have made
25:05changes in the base table
25:08is more than 60 to 70 percentage means
25:11then I can go for complete refresh
25:14if it is less than that you can use fast
25:17or Force refresh so those refresh can be
25:21done by execute dbms underscore mu dot
25:25refresh
25:26May place your name and C
25:28C stands for complete refresh
25:31what is correlated supporting
25:34either remember
25:48um
25:53So based on inner query the subway got
25:56defined right if inner query joining
26:00with outer query or depending on outer
26:02query we can call it as a correlated sub
26:06query so incarnated subquery inner query
26:09cannot work independently
26:13so if inner query depending on outer
26:17query we can say it is a correlated
26:20subquery
26:24okay fine
26:27next
26:29difference between group functions and
26:32analytical functions
26:34group functions will return one row for
26:37each group
26:39analytical function will return one row
26:41for each row
26:44and min max some average count or group
26:48functions rank density
26:52pivot and pivot roll up Cube
26:55those are nothing but analytical
26:57functions
26:59what is stable
27:02collection of records that composed of
27:05rows and columns nothing but a table it
27:08is a database object
27:10where we can use to store data in it
27:14table space
27:16is nothing but
27:19a space we can have
27:21multiple objects in it it is not only
27:24for table
27:26so don't understand by the name it is
27:29only for table no it is not so we can
27:32have index in tablespace we can have
27:35other database objects also inside one
27:37tablespace so table space is nothing but
27:40a separate memory space where we can
27:43have other database objects as well
27:47how an SQL statement works for that we
27:51should aware of three steps
27:54the first thing is something but syntax
27:56check
27:57whether the returns SQL statement uh
28:01you know created properly
28:04the second check is nothing but a
28:06semantic check
28:08semantic is something but whether the
28:10column name and table name view name the
28:13object names and
28:14stuff you mentioned after select
28:17or after from or after where you
28:20mentioned
28:21correct or not so that will be checked
28:23in semantic the syntax and symmetic we
28:26can call it as a compile time check
28:29if you have any issues in that then it
28:31will be an error
28:33so for syntax error we will be receiving
28:36SQL command not properly ended from
28:38Clauses expected
28:41where
28:42we cannot use a group function in where
28:45clause
28:46Windows function not already here
28:49these are the things we can say at the
28:50system syntax issue
28:53and semantic nothing but invalid
28:55identifier table or view does not exist
28:59these things that you can say as a
29:01semantic issue the finally Oracle will
29:05do third check as a shared pull check or
29:08Library catch a check
29:09which is nothing but the statement which
29:12are executing second time or first time
29:15if it is first time Optimizer will work
29:18on that it will generate explain plan
29:21based on the cost
29:23the the least cost explain plan will be
29:27generated by the optimizer
29:31so in that we'll be we can able to see
29:33the scans and access paths
29:36okay so how Optimizer worked to retrieve
29:41the data from the database
29:44okay so if second time we are running
29:48that same query
29:51Oracle will identify that SQL ID from
29:53the respective catch
29:55if catch is found then Oracle will go
29:58for soft Crossing else Oracle will go
30:01for hard parts
30:03so here definitely you have to mention
30:04about hard parsing and soft passing
30:08how to take explain just to have
30:10explained
30:12if I want to filter the data directly
30:15from the table I can use where clause
30:17if I want to filter the records from the
30:21group by concept then
30:23I can use having Clause so condition
30:26with group function I have to mention
30:28having Clause from directly table I can
30:32use where clause
30:35one sequence is created if you run curve
30:37well then it will be an error because
30:40sequence must be initialized at the time
30:43of next well
30:44once you create the sequence you're
30:46supposed to run next 12 statement not
30:48the curveball statement if curable then
30:51error
30:53joints
30:56query that can be connected with the
30:59query
31:01one query will be acting as a inner
31:03query and another query I will be acting
31:04as
31:05outer pouring
31:07so in a query that outer query
31:09connection means so we can call it as a
31:11sub query
31:13and we have seven types of sub query
31:15single row sub query multi row sub query
31:18multiple Columns of query correlated sub
31:21query scale or sub query inline view sub
31:23query nested sub query
31:26so these are the sub query we can call
31:28it as uh you know types of subqueries
31:32set operators
31:35we can relate
31:37between
31:38two queries data
31:41so in between queries we'll be using set
31:43operators so we have four operators
31:45union union or intersect and minus which
31:48helps us to compare data between two
31:51queries
31:53and whereas joins
31:55if I want to retrieve record from
31:58multiple tables I will be using joins
32:01so single query you can able to see
32:03multiple table data we have seven types
32:07of joints inner join are equally join
32:10natural join
32:12left out Regine operator joins full
32:14outer join self join and cross join
32:18cross chain is also known as Cartesian
32:20product
32:22and after that we have types of tables
32:27I have already mentioned that
32:29how data import export happening in
32:32database
32:34so we have Excel table SQL loader impdb
32:38and exp DB
32:39so you all have to you know aware of
32:43this uh acceler table concept which will
32:46help you to
32:47load the data or pump the data so in
32:50Excel table we will use uh
32:54what data loading as well as data
32:57pumping so Oracle underscore loader
33:00Oracle underscore data pump
33:02is a type of
33:04thing that we are working in Excel table
33:06we need to create a directory
33:09and we need to understand the data after
33:12that we have to form a column structure
33:16then we have to mention type
33:18organization XML before that
33:20organization external type default
33:23directory and access parameter in that
33:25three things to be mentioned records
33:27will delimited by new line Fields turned
33:30by comma missing field values are null
33:33and then column mapping should be
33:35happening and then location should be
33:36mentioned and then reject limited
33:39unlimited parallel five things to be
33:41none so using this particular syntax we
33:44can able to load the data and similarly
33:46for
33:47unload the data
33:49and how to check
33:52data count of a table
33:55so already we have discussed about this
33:57query count of star sum of One Max of
33:59problem from employees table which helps
34:01you
34:02to identify the number of Records
34:04available in the respective table and as
34:06well as
34:07you can use num underscore rows from
34:10user tables
34:11so that's what we're calling it as a
34:13dictionary table where you can identify
34:15all the information about the table
34:16which you want to see
34:19so in where condition you have to
34:21mention the table name in capital letter
34:22already over aware of that the digital
34:25table will be captured in the record in
34:27capital letter
34:36capital letters else
34:45difficult in the sense
34:51okay without creating additional object
34:54how to stop insert data on weekends
34:58time
35:02seconds
35:08took care of that column name comma the
35:14in one comma 7
35:17or not till 1 comma Seven
35:20Corners because
35:24foreign
35:47both will generate a sequence number
35:49both will generate sequence number based
35:53on over and order by column
35:57if order by column is you know having
35:59duplicate values then the sequence
36:01number will be skipped we can say it is
36:04a ram
36:05if sequence number does not skip means
36:08we can call it as a dense run
36:10and difference between row number rho ID
36:13and row number
36:16ronum and row ID both are pseudo columns
36:20in SQL and we can say row number as a
36:23mechanical function
36:25ronum
36:27will generate a sequence number from one
36:31to something so on
36:33and how many records will be displayed
36:37it is purely based on the query data
36:41so how many records the query is
36:43retrieving that many numbers will be
36:45displayed as a sequence number
36:47so row num is a pseudo column will
36:49generate sequence number automatically
36:51based on the query result
36:54which will not be stored in database you
36:57can use where condition using row num
36:59equal to 1 less than or less than or
37:02equal to apart from that it will not
37:04work for anything
37:05whereas rhoad is something but a soda
37:08column and which will generate
37:1018 digit alpha numeric unique ID that
37:15will be stored in database
37:17while inserting data into a table Oracle
37:19will create this ID
37:21it is an unique representation of every
37:24row in a table
37:26Oracle refers row ID for
37:30data retrieval faster
37:34okay and row number
37:36and you can apply wire condition by
37:38using a row ID which will retrieve the
37:41record faster
37:42row number is also generating a sequence
37:46number automatically based on the over
37:49order by clause
37:51and finally
37:5315th question we have
37:56how many primary key and long data type
37:59we can
38:00able to keep in a table only one
38:04answer this one
38:06what is the difference between
38:09daily Trend truncate and drop
38:11delete is a DML command
38:14truncate and drop is a ddl command which
38:18is an auto commit language
38:20there it is a non-auto commit language
38:23we can apply where condition
38:27or we cannot apply web condition if you
38:29are not applying wire condition means
38:31entire record will get removed from the
38:33database
38:35and not finally like temporarily the
38:38record will be handled in redo log file
38:41the DML operations not only delayed
38:44insert update delete merge all these
38:48commands
38:49records will be captured temporarily in
38:51redo log file once we do commit then it
38:55will reflect in database otherwise
38:57once we do rollback
39:00the records there in the redo log file
39:02will be transferred to RK block files
39:06in truncate that concept is not
39:08available
39:13truncate will remove entire record we
39:15cannot apply wire condition
39:17their entire record will get remote but
39:20table will be there the structure will
39:22be there if I want to remove the table
39:24or any object I can use a drop command
39:27so drop is also Auto committed
39:30and the difference Main
39:33about David and truncate is high water
39:37high water mark is the boundary line
39:39between used and unused space
39:44so it will be refreshed in truncate it
39:46will not get refreshed in delayed so we
39:49need to refresh the high Watermark by
39:51using Gathering statistics of the table
39:54while refreshing the table we can easily
39:57update the high water mark execute
40:01dbms underscore stats dot gather table
40:05stats of schema name and table name
40:07which help you
40:10to refresh the table and refresh the
40:12heaven work you can able to see whether
40:15the table got refresh or not
40:18user tables last analyzed column you can
40:22able to see whether the table is
40:23invarried or valid
40:25okay in Num underscore we can see how
40:27many records are there in the table
40:30and in same way in last and list column
40:32we can able to see at what time the
40:35table got analyzed finally
40:38merge syntax
40:41much syntax is uh so merge is a syntax
40:46where we can you know
40:49uh accomplish multiple DML statement in
40:52a single statement
40:54and it is nothing but merge into table
40:57name using table name
40:58on condition when matched then
41:03update
41:04and delete also can be mentioned when
41:07not matched then inset
41:09this is syntax you have to mention no
41:11other statement merge into table name
41:13using table name on condition when
41:16matched then update will not match then
41:19insert so this statement that you have
41:21to run
41:25and after that we have what is interval
41:28table partitioning
41:29applicable only for range partitioning
41:33the records which we not partition
41:37those records will be captured
41:41in interval partitioning which generated
41:44by our you know Oracle automatically
41:46while inserting
41:48and partition data into the table or
41:50okay will create a new Partition
41:53Oracle itself will be generating the
41:56name of the partition so it will be
41:58starting
41:59from its sys
42:01and you can able to see that in user tab
42:03partitions this feature introduced in
42:06leveraging
42:07how to print 1 to 10 in SQL select a
42:11level from viewl connected by level less
42:13than equal to
42:1510.
42:16how to print a to z so for that we have
42:20to use C HR function
42:23so in that we'll be using a 60
42:26five I think so 65 what is the alphabet
42:29for uh
42:3165 let me check
42:36yes 65 is uh
42:38a what we can do is 64 plus level
42:43so I need 26 characters nine so connect
42:46my level less than or equal to 26. so
42:49this query will generate uh alphabetical
42:52order
42:54and then we have a value used gtt in
42:58your project
42:59and types of gdt
43:02whenever my project requirement says I
43:05need to manage temporarily a bulk amount
43:08of data
43:09and I want to
43:11I'm supposed to make some DML operation
43:13on such data I can keep the data inside
43:16gtt so what I can do I will be
43:19maintaining a temporary table either by
43:23on Commit electrodes on Commit pressure
43:25rows up to my requirement
43:27and I can manage the data so recently we
43:30have used gtt to manage bulk amount of
43:33data instead of accessing the master
43:35table I can access
43:37GTD and gtt record will be captured to
43:40and move to
43:42master table
43:45is it possible to change data type when
43:47the table is having data already we
43:50cannot make changes in data type if
43:54table is already having data what we are
43:57supposed to do is we need to
44:01create a new column
44:04with a different name and then we have
44:06to transfer the data toward
44:08towards that column and then we need to
44:12change the data type of the existing
44:14column so meaning you need to transfer
44:17the data to the newly created column is
44:20you need to go for update segment once
44:23the transfer is done then you can able
44:24to
44:26change the data type and then we will be
44:28transferring again to the existing
44:29column so that thing should be happen
44:32else you cannot create a you cannot
44:36update you cannot change the data type
44:39of the column which column is already
44:40having data
44:42how to realign the column means we can
44:46use invisible and visible to realign the
44:49column
44:52print
44:53given
44:55number inverse so this video
44:58I'm just trying to you know do for a
45:01theoretical way so I will be you know
45:05skipping uh the technical queries I will
45:08be teaching different video so the first
45:10in the video
45:12theoretical Concepts because it is
45:19not good actually
45:37so which question we are coming around I
45:40think this query
45:41this statement would just say lead lager
45:47and the date of a previous date
45:50I will be using lead
45:53and the rate our next date
45:55lag I will be using both our analytical
45:58function
46:00because
46:25[Music]
46:28but next to PL SQL stuffs
46:32management
46:41what is cursor cursor is something but
46:44SQL private work area
46:46and there are two types of cursor
46:48implicit cursor and explicit cursor
46:51cursor can process only one row at a
46:54time
47:00what whatever
47:01we have studied what are the things that
47:04you know about cursor you can mention
47:07Ed
47:14something but SQL private area
47:20then again I'm telling what cursor can
47:23process only water at a time
47:26cursor can process compile time data
47:31okay there are two types of cursor
47:33implicit cursor and explicit cursor
47:37and cursor can manage the attributes
47:40like found not found is open and broken
47:44difference between Loop and while loop
47:48Loop will start the execution process
47:51and will come out from the loop
47:54when the condition is satisfies
47:56while
47:58before executing when the condition is
48:01satisfies it allows the loop to execute
48:07difference between cursor and refursor
48:09cursor is an SQL private work area
48:13there are two types of cursor in place
48:15in the next place what are the things
48:16that we can
48:17mention we have mentioned here we can
48:20mention okay
48:23cursor can process only one row at a
48:25time whenever there is a scenario that I
48:29need to validate data one by one
48:31I will be using uh cursor
48:34rough cursor is a data timing
48:38which is
48:40two types
48:42strong layer of person weak layer of
48:44cursor and general used as an output
48:47parameter and whenever the application
48:50screen having Lov
48:52Lov list of values or any any screen
48:55content to be displayed in a detailed
48:57manner multiple columns are single
49:00columns multiple data or single data
49:03everything will be managed by rough
49:05cursor parameter
49:07mostly used as an output output
49:09parameter
49:11and different between row level and
49:14statement level
49:15trigger so trigger automatically for us
49:19when an event occurs events are nothing
49:21but insert update delete
49:23and timing points are nothing but before
49:25and after instead of
49:27if we create trigger for view we'll use
49:30instead of
49:32and statement level if I want to fire a
49:34trigger for a statement for insert or
49:36for update for a delete a new statement
49:39level trigger it will fire the trigger
49:41only once
49:43whereas roller will trigger how many
49:45records got affected because of that
49:47statement that many times trigger will
49:49get fired I am doing an update statement
49:51that will reflect on 10 rows means that
49:55particular PL SQL block will execute 10
49:57times
50:00foreign
50:04so this thing can be solved by using
50:07compound Trigger or pragma autonomous
50:09transaction keyword
50:10and whenever we are inserting
50:14our data into a table and the same
50:17action for the trigger then it will be a
50:20mutating table error
50:22possible to perform DML operation on MBO
50:25trigger
50:26in mu we cannot perform
50:28intrigger we can perform but
50:33we cannot commit inside a trigger
50:36so if you need commit for DML operation
50:39then we should go for problem autonomous
50:42transaction you should mention declare
50:44keyword
50:45after that that statement we can also
50:48perform a DML operation but we cannot
50:51run in select statement for that also we
50:54need to mention program autonomous
50:56transaction and whenever we use this
50:59keyword commit is required
51:01is nothing but
51:03an independent transaction
51:08independent transactions nothing but
51:10compiler redirective statement
51:14the statement which you are running with
51:16the program autonomous transaction
51:18will not affect the commit outside of
51:21the statement which you outside of the
51:22program which we executed
51:25those commit statement is applicable
51:28only for the block
51:30which having problem autonomous
51:32transaction Raghu Mata Namaste
51:34transaction the keyword is
51:48foreign
52:13overloading
52:17is nothing but we can have the same name
52:21of the sub program names and that can be
52:25differentiated by
52:27word parameter time parameter name and
52:31data type number of variables
52:34we can differentiate by that so we can
52:37say it is a package overloading
52:41Global things are nothing but what are
52:44the things that we are declaring inside
52:45package specification either can be a
52:48type or variables constant variables
52:52cursor sub programs anything rough
52:55cursor what are the things that we are
52:57declaring inside package specification
52:59we can call it as a global steps
53:02and in between
53:04procedure or function if you are
53:07declaring any variables those variables
53:10can be used only within that procedure
53:12or function
53:13we can calling it as a
53:15local variables
53:19and
53:20procedure and function difference
53:23Evergreen question
53:25both are sub programs
53:27both are used to accomplish specific
53:30business requirement from DB and
53:34procedure may or may not return a value
53:36function must return a value
53:38function can be called in a select
53:41statement whereas procedure we cannot
53:43call
53:44procedure and function both are
53:46performing literally the same thing
53:49but why we are going for function is
53:52when the requirement
53:55no says that I need to call
53:59in a select statement then that case we
54:01can only go for a function not by a
54:03procedure so we can accomplish by
54:06function remaining steps we can do
54:09everything by procedure
54:11and we can perform anything whatever we
54:14want inside function procedure all are
54:16saving
54:18okay fine forward declaration is nothing
54:20but
54:21I have uh not declared
54:25as a program in a package specification
54:29I am just
54:31no declare or used
54:34inside package body still like unable to
54:37access that function or procedure only
54:41within that package body
54:44outside of the body I cannot access so
54:47you cannot call that procedure by using
54:49package in m dot
54:51that procedure name no it will throw an
54:53error because
54:55you can access
54:56those kind of method
54:59you can go for that kind of method only
55:01if you are declaring that in package
55:04specification
55:05so if you are trying to access
55:08that package local procedure
55:12which you didn't declare in package
55:15specification
55:17that procedure should be declared in
55:20that particular package body
55:23without declaring in that package body
55:26you cannot able to access
55:28so the Declaration should happen before
55:31the procedure defining in another
55:34program so such a declaration we can
55:36call it as a forward declaration so I'm
55:39just explaining about this in a detailed
55:42manner for example my back range is
55:45having
55:45three procedures sp1 SP2 and sp3
55:49and package body
55:52sp4 I'm defining
55:55say sp4s I'm doing some PL SQL Concepts
55:59here
56:02so here I'm doing some insert operation
56:05where the pl SQL compiler will ask you
56:09to declare
56:10before this uh
56:12procedure you are using anywhere else
56:15inside the package body
56:17so you have to declare you know
56:19a package in the package or the first
56:24line create a replace package body
56:27package name as
56:31procedure sp4
56:35semicolon which it is
56:53so that's very very important point
56:55about the
56:57forward declaration
57:03so can I create a package without body
57:07yes
57:08declaration
57:10don't get confused about this kind of
57:12question declaration mandatory
57:18can I create a package without a species
57:24so you have to create a spec yes fine
57:27you have to create only body no it is
57:30not alone
57:32so without specification package cannot
57:34be created without body we can create a
57:37package
57:39can a call function inside a procedure
57:41inside a function we can call a process
57:43yes we can call function inside
57:45procedure closer inside function
57:48is it possible to call a procedure
57:51inside a trigger yes I want to perform a
57:54particular action that action is stored
57:56in database that procedure is to be
57:58called in a
57:59trigger yes we can still able to use
58:03can I call a standalone procedure inside
58:06package yes
58:08what is procedure
58:11I want to perform a specific action I
58:15just stored that action inside a
58:16procedure I want to perform that perform
58:19the same action then I can call anywhere
58:22else
58:24if a created means I can call anywhere
58:26if I declared means I cannot call only
58:30the package specification declaration
58:33part alone can be used globally
58:36okay
58:40why we use with cursor as I mentioned
58:42already
58:44collection there are three types of
58:46collections
58:47associative array
58:49nested table and V array first last next
58:54prior delete the term
58:58limit count delete
59:01exist extend these are nothing these are
59:03the methods or collection methods and
59:06generally we use collections to handle
59:08multiple rows
59:10okay whenever use collection we used to
59:13go for bulk collector statement to
59:15process uh
59:16you know bulk amount of data in single
59:19stage
59:21raise and raise application error both
59:23are
59:24user different exceptions
59:28we can Define we can you know go for
59:31exception in two ways
59:33one thing is I just want to divert
59:36I know in between PL SQL program 2
59:39exception block I can go for raise
59:42and I want to stop the execution in
59:44between the pl SQL block and I want to
59:48go for a concept same like Oracle how it
59:52is you know throwing error I can go for
59:54price application error the error code
59:56range between minus 20 000 to minus 999
1:00:01and message can be your wish
1:00:04list of some predefined uh exception uh
1:00:10no data found
1:00:12cursor already open invalid cursor
1:00:16and do 12 and index 0 divide
1:00:22too many rows
1:00:24invalid number so these are the things
1:00:26that we can say
1:00:27that's a pretty different exception
1:00:36how to debug PL SQL program
1:00:39so for example my program is having two
1:00:42or two thousand of lines of coding there
1:00:43I couldn't able to find the mistake
1:00:45where it is happening
1:00:47so in that case I can have a D minus
1:00:50output statement every 100 rows of your
1:00:53PL SQL Block in between that you can
1:00:55mention like dbms output 1 dbms output 2
1:00:59so you can have 20 dbms statements
1:01:01inside 2000 lengths of your program
1:01:03after executing that you can able to see
1:01:06three not four five six and all then it
1:01:09will be a problem between three and four
1:01:11what you can do between three and four
1:01:14you have 100 statements you can near
1:01:16that statement you can hear the mistake
1:01:18by using again dbms output like 3.1 3.2
1:01:223.4 in between 20 lines of every
1:01:25uh that in between that PL SQL uh three
1:01:29and four blocks so after running this
1:01:32you can able to see 3.3 not 3.4 3.5
1:01:353.600 so then the mistake nothing but
1:01:38between three point four and five so you
1:01:41can able to near the error which happens
1:01:45so in that way you can debug the pl SQL
1:01:48program or you can use dbms profiler
1:01:51issue
1:01:54so trigger cursor syntax or mandatory
1:01:58create or replace trigger trigger name
1:02:01after inset or update or delete on table
1:02:05name begin null and
1:02:08cursor cursor name is followed by a
1:02:10select statement open cursor fetch
1:02:13cursor into variable variable should be
1:02:15declared and closed cursor
1:02:17refers or type type name is ref cursor
1:02:20or sister of cursor
1:02:24possible to create trigger for a view
1:02:26case by using instead of timing point
1:02:29difference between wear and nested table
1:02:31yes both can be created and stored in
1:02:34database
1:02:35both are nothing but to handle
1:02:38bunch of Records in a single stitch
1:02:41and V array is a limited one
1:02:43and nested table is an unlimited one we
1:02:46can have n number of data in V8 nested
1:02:49table
1:02:50generally we use a nested table for our
1:02:52projects
1:02:55SQL is nothing but structure query
1:02:58language
1:02:59the extension to SQL is something but
1:03:01procedural language structure language
1:03:03that is nothing but PL SQL
1:03:05you can have
1:03:07you know uh single statements to be
1:03:10executed kind of uh condition is an SQL
1:03:13it is an english-like language can be
1:03:16written in multiple lines case
1:03:18insensitive language whereas PL SQL
1:03:23is also case in sensitive language
1:03:25multiple SQL statements can be executed
1:03:28in single block
1:03:29by using PL SQL
1:03:32what is the coding standard coding
1:03:34standard is something but for every
1:03:36company people will be following some
1:03:38coding structure you have to follow this
1:03:41unit cap structure for every statement
1:03:43you have to follow for cursor name like
1:03:46this and the table name like this and
1:03:49procedure name like this
1:03:51and we can say it as a naming convention
1:03:55so name of the object names and column
1:03:58names should be in a proper way
1:04:00it should be understandable readability
1:04:02is mandatory for every program
1:04:04when you're writing a query so you have
1:04:07to split the query into select column
1:04:08names next line from table name next
1:04:11Lane where condition so that the
1:04:13readability of the program will be good
1:04:15so that nothing but
1:04:17coding standard and naming convention
1:04:20TTS stands for detailed technical
1:04:23specification
1:04:25dpfs stands for detailed functional
1:04:27specification
1:04:29and BRD stands for business requirement
1:04:32document
1:04:33and test cases nothing but once you've
1:04:36created a procedure or function or
1:04:37package or trigger you just go into unit
1:04:41test
1:04:42okay whether the written procedure
1:04:45written object is you know working
1:04:47perfectly or not that thing is nothing
1:04:49but test case we can say those will be
1:04:52recorded those will be you know uh
1:04:55captured in a test case document so
1:04:57meaning if any Java developer or any
1:05:00front-end team is facing any issues with
1:05:03the respective database object which you
1:05:05did unit test case you'll be sharing the
1:05:08document to them so that they will be
1:05:11fixing their X Box uh from there and
1:05:18difference between Authority definer and
1:05:21Authority current user
1:05:23obtained a question
1:05:26is
1:05:36for example I am having a
1:05:39schema
1:05:40S1 or SK Market
1:05:46foreign
1:06:15executed
1:06:25on SP to RK is
1:06:40we'll be using exec is
1:06:58foreign
1:07:40foreign
1:08:06statements which cannot be executed
1:08:09directly
1:08:10kind of detail statement DCL statement
1:08:12or
1:08:13the statement which are framing by
1:08:16developer
1:08:18and those statements can be executed
1:08:21only through Dynamic SQ execute
1:08:23immediately is a keyword which help you
1:08:26to execute the framed SQL statement not
1:08:29only select
1:08:30all kind of framed executable statements
1:08:34can be executed by using
1:08:37Dynamic SQL execute immediate is the
1:08:40keyword
1:08:41how to
1:08:44fix PL SQL performance issues so for
1:08:47that you have to learn about DPMS
1:08:50profiler
1:08:52you have to start the profiler and we
1:08:54have to execute the pl SQL program which
1:08:56having performance issues and then we
1:08:59need to stop the profiler by using dbms
1:09:03underscore profiler.stop profiler
1:09:06okay and then we have to
1:09:09check the pl skill profiler data unit
1:09:13run stable what are the entries are
1:09:16available in the respective table then
1:09:18we have to identify the issues and then
1:09:20we have to fix the issues with the
1:09:22fixing Concepts
1:09:24foreign
1:09:59helpful to proceed controlling into them
1:10:02can I return multiple values inside a
1:10:04function can you return multiple values
1:10:08in a function yes using collections
1:10:12we're going to create or replace
1:10:14function function name
1:10:16return collection type to incredible and
1:10:20the name
1:10:22as
1:10:27foreign yes we can return multiple
1:10:31values
1:10:32is it possible to
1:10:34use multiple returns
1:10:41multiple written statements
1:10:44yes still we can able to see able to
1:10:47write mention multiple return for
1:10:49example begin intercola return one
1:10:51return to return three written four
1:10:54upper endless
1:11:06first return statement rate will come
1:11:08out still you can have multiple returns
1:11:10keyword but it will come out from the
1:11:12first return statement itself for
1:11:14winning this alone
1:11:17what is bulk collect and bulk binder so
1:11:20I have already told you like on the
1:11:21program
1:11:23um
1:11:27so that's important thing about uh well
1:11:31collect and bulk Point program
1:11:36you have to use
1:11:38who are bulk collector and bulk mind
1:11:41context switching reduce of by news
1:11:44operations
1:12:06okay
1:12:10related question sir
1:12:12parameters
1:12:20so my program is depending on runtime
1:12:23input means I can go for parameterized
1:12:25cursor so I do not have a value directly
1:12:31to pass in the sense I can go for
1:12:32parameters to cursor so
1:12:35Dynamic cursor is nothing but
1:12:38we can have a
1:12:41you know the pl SQL program
1:12:44to be executed through
1:12:47executive million
1:12:49okay the program should have a cursor we
1:12:52can execute those kind of a PL SQL
1:12:55program inside your executive immediate
1:12:57keyword such a thing is something but
1:12:59Dynamic cursor
1:13:01so you know DPA over any questions
1:13:05possible to use cursor inside a query
1:13:07yes I have already mentioned this
1:13:11foreign foreign
1:13:48foreign

About this transcript

This page contains the full transcript of ORACLE - SQL AND PLSQL - 100 QUESTIONS WITH ANSWERS by Karthick Ravichandran, generated from the public captions YouTube serves with the video. The transcript has 7,746 words across 1,442 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.