this conference will now be recorded okay so SQL statements we can start these are different types of SQL statements first one already we have done for a few classes which is data re driver language DRL under that select is there so using select many things we did now some other SQL statements also there next one is ddl DCL past one BCL is not required for tester that is for database administrator cable so for today we can do this ddl tomorrow we will do DML and TCL parallel under this ddl data definition language which are the commands available create alter drop rename prom kit these are the five commands available in this ddl create is used for creating the table alter is used for modifying the table structure modification drop is used for dropping means removing the table as well as column rename is used to change the name of column also change the name of table and truncate is used to make the table empty if any data is there it will make it empty okay so directly we can see example how to write so in this syntax we can write how to create a table so create table table name any table name we can give start with parenthesis close with parenthesis then list of table name as a release top column name column name data type list of column name data type if data type is needed with some length then provide the length also number 10 comma 2 gender here one data type related topic already discussed so these are the data types using here first name worker 50. last name marker 260. address worker 70 CT country birth date so one date data type also covered int data type also covered Care number to all categories are completed here so all these lists we can write with comma comma comma in one line also in five line also in free line also the best approach is one column better to write in one line next column next line next column next line so if any mistake happened somewhere easily it can be eye-catching this is the best format otherwise we can write everything in one line that is also fine or start with this table name parentheses close with parentheses and in this way also we can write that is also fine anyway is fine this is just like English sentence as a paragraph We have to write that's all the best one is this format this format only always prefer for looking value also it is very nice okay now we can take this and create the table foreign so the same thing only here mentioned now if we'll execute this this customer table will be created so before creating in the same database under same schema it will check that customer table is exist or not if it is already exist then the same name again it will not recreate it will throw some error so whenever we are giving the name this names will be unique it should not exist with the same name under the same schema it can be present in some other schema with same name no problem but within same schema same name is not allowed execute okay so the same name is not present it is created table customer created now if we want to see the structure describe table name foreign then data type sequence everything it is displaying in which manner we created the same thing only it is present like birth date we have given date data type data type not required with any length date data type country where care 225 where care we have given 25 so in Oracle database 2 both are same worker if you are writing also it will behave as worker too foreign Carriage care only gender care one so here air one only int Ola numeric int decimal float numeric number all these are coming under numeric only so any of these we can mention it will come up with the number length is not given by default highest length it will come in int the length is optional we cannot mention also so if you will not mention highest length it will come if you will mention that that length only it will come so salary number now number 10 comma 2 10 comma 2 so if you will give the length along with that length it will Define if you will not give the length using int then by default highest length it will Define so this table is created now this is the way how to create simple syntax only nothing is there create table table name list of column names we have to give within parentheses along with the data type length data type with 1 1 comma after complete just execute table will be created give the unique name if any exist name giving in that schema which is present then it will not create like now already we have created again we are creating with the same name so under same schema this name is present customer again creative this will give this error name is already used okay so this is about create command next is rename command so under ddl which are available create rename truncate drop alter okay so before moving to rename any question in this create thank you any questions no I'm good okay so rename command uh I have a question so here if it is Data is numeric so we can give numbers uh the type is number int hint or number right yes int you can give number you can give numerical so can be given this email also can be given anyone and what is the size of that one maximum size of all the trees I've already told all these things so you attended on a data type topic is the R1 plus taken already okay let me refer that one here thank you number maximum 38 care maximum 255 then worker maximum 2000 where care to maximum 44 000 yeah within that any number we can give no objection but maximum limit is that one okay so for rename we can change the name of table as well as we can change the name of column so directly rename we are doing now and we can change the name of table another rename again we can do that will be used along with alter alter plus rename both we can use then column name will be changed only rename is using not alter not anything then table name will be changed so how to do rename all table name to new table name very simple so we have suppose customer old table name or existing table name customer two first so this is our existing table name this is our new name what we want both name we have to give in between two keyword that's all rename just English like sentence so the name of table will be changed and once it is changed the previous name will be not available new name will be available in the database customer succeeded now if you will see this describe customer not exist if you will do describe cost then that is exist exist so same thing only structure data anything is present everything will be taken only the name will be changed table name no other thing this is rename command next is truncate command using truncate command table will make empty so in which case we can tell it at empty table if data is not there then table is called as empty table it will contain two things one is structure and another is data so right now we created this table this is only having structure column name data type length we have not kept any data inside that so this is empty table if any table we have which is having some data but we want to make that empty only structure will remain and data will be removed so that table will be called as empty table so if we want to make any existing table as empty table then we can use strong kit so how to write pronkit table table name so we can search one table which is unwanted okay suppose this is your table products table product name is there some code is there product code and that product description so these three products table we don't need this data Maybe previously can be added so in that case we can make this tablets empty name table products truncated now if we will see again select star from products no data is there table is remain same it is having the the same data type length and everything again we can write new data holes any data can be Rewritten no problem but for the timing whenever we'll use this command data is gone foreign next one is drop so what is drop do drop command used to remove table permanently okay so using this drop command we can delete or we can remove the table permanently and after that we cannot get it back again if we want the same table same data same structure again we have to recreate again reinsert again undo facilities not available once drop means that is dropped so here data along with structure everything will be removed structure Plus data remove from database it is not like that it will go and store into somewhere temporarily or recycle bin is there not like that completely it will be deleted from database so how to use drop table table name so this product table is empty table so we can use this table okay so this table content product table only con structure data is not there and we are dropping that is also fine now dropped so see this not available if that table contain any data also same case it will not exist it will remove permanently along with data structure everything so any other table we can see okay so this is one table promotions name code date begin date and discount percentage so here data also they are structured also there we want to remove this see now not exist so data is exist then also we can drop data is not there only structure is there then also we can drop in both the cases it will remove permanently from the device next is Alter okay alter little bit lengthy so all those are clear drop rename truncate further try okay sorry go ahead that's fine you can carry on yeah for the trunk since you said drop we cannot undo can we do undo for the trunk it all these ddl statements are permanent we cannot do any undo for any of the commands Okay gun means gun done means done you cannot revert so from the memory it will be gone right it is because data is there means some memory is assigned okay uh there is one more dispatch is there right what is that actually uh we'll see that tomorrow um that is added from 11g but not working perfectly in 11g so 12 C onwards that is working okay in that software we can see okay and uh there is nothing called dilute right in SQL delete also yes delete also there in DML that is available tomorrow we'll see okay in ddl drop is there not delete delete is different prop is different can we use drop for column PS for column also we can use along with alter already told second [Music] drop so alter with drop column drop only drop means table drop early rename also only rename is table rename alter with rename is column rename H2 okay so next is Alter in alter these are the four features available add new column we have 5 or 10 columns in a table already created so later on we want to add few more columns two or three or four columns yes we can add that that is ADD operation modify so we already defined some columns with data type length so those data type and length can be modifiers using modify another is drop so drop is already done that is without using alter Now using alter this drop can be used to renew columns also single column multiple column similarly rename change the name of column so rename is used for changing the name of column one by one at a time we cannot do or three columns are there then two for three statements we have to write an execute but for drop multiple columns we can drop in a single query for modify also multiple facilities there for add also multiple facility in rename no single one single column single time only we can remember another column again we have to write another query and rename thank you okay so these are the Syntax for alter alter table table name modify column name data type so which column data type we are going to change that column name we can give then what data type we need newly Pressly that data type with length we can give more than one column then within parenthesis how many number of columns we can mention along with the data type if single column no need a parenthesis if more than one column need these parenthesis similarly for adding the column same syntax only in place of modify add column name data type since new column is adding means data type is most required so column name data type if more than one column again same process parenthesis parenthesis list of column names and data type next drop so drop a single column drop column column drop the multiple column list of column names within parenthesis if we are dropping means data type is not required it is going already so data type is not required rename alter table table name rename column holds them to new name alter table table names for all these activity alter table table name is must alter table table name alter table table name now using all these we can change all these operations in cost table which is created so we have the table name that is cost foreign [Music] okay so these are the columns data type available in this cost table first one we can see alter table table name modify a column name data type so any column we can pick suppose this one cost ID modify a cost ID data type already number 38 is there we can change to where care some length we can give suppose 50. so data type is changed length also we are changing thank you table cost altered now check this describe cost back to 50. their care will be considered as worker 2 only in Oracle 250 . similarly multiple tables we can do now this cost ID we can change to int again another column we can take gender one is there so let it be care 1 only but the length we can change to 10 suppose another column we can take birth date deep data type is there so we can change to suppose worker 225 okay for more than one column or multiple column we are changing the data type and length modified so for changing the data type and length modify will be used they will cost all data check now birth date Market 225 cost ID number 38 hint we have given gender care only but length is changed then yes all these are changed alter table table name add column column name now we can add one column add column name suppose one column name we can give joined it data type is data alter table table name add column name data type table cost auditor check now last column is a direct join underscore data type so whenever we are adding The Columns it will add in last only not in middle in Middle how to add that provision is not given how to add in last that provision is given so if we want that into in Middle then some lengthy process need to follow swap those columns change the values change the name lengthy process that is but directly no provision is there how we can add in MIDI so whenever We'll add in any add any column one column two column three column always it will add in last term now multiple columns we can add alter table table name add within parenthesis multiple means within parenthesis to call one call to call three only we can give one we can give the data type int another we can give data type um date another one we can keep the data type here 24.
table cost auditor see here after joined it last three columns added call One Call underscore two call underscore three whatever data type we have given same only it is coming now we can remove those columns also if any column is not required we can remove that also how to do alter table table name drop column column name so any column we can remove suppose birth date column alter table table name drop column column name altered birth rate is good so single column is removing means no need of parenthesis now multiple columns also we can remove call one call to call three we have added that one only we can give here okay alter table table name drop list of column names within parenthesis foreign not available so here one observation is there can you see and tell in this two drop example or syntax that only they are asking an interview for multiple columns column is not required it's a single requirements yes so color pivot is required only for single column right column keyword is not required for multiple column drops note down this try to remember [Music] and many mistakes happening so who are telling the syntax in single column Dropbox of column they are telling in multiple also Columbia but some are telling in both the places no column keyword this is giving lot of confusion so you can just try to remember this last one is rename alter table table name rename column old name to new name so in economy can take salary support salary column we can change to 10. alter table table name rename column old name to new name existing name to new name table custom auditor sell so for linear multiple is not there that's syntax again we have to write the same Syntax for another column for modify add and drop multiple option is available for rename multiple option is not available one by one these are about alter so what you learned from all these ddl first one is commands which commands are there locate drop alter what is the full form data definition language operation mode permanent any activity we are doing any command we are using permanent we cannot revert back TCL is not used not applicable undo option that is called as rollback command so TCL is used for DML commands not for ddl so TCL is totally not applicable this is structure based or database purely structure based foreign operation type is purely structure based not databased BML is database ddl is not database purely structured so whatever we did today all are structure related we've not touched with any data column multiple column data type change length change column name rename table removed make the table empty everything is structured based so ddl is purely structure oriented or structure based you can tell memory it will occupy or not no only empty structure we are creating means memory it will not take and in those structure if you are removing something adding something then it will not impact in the memory but if data is there then memory will be impacted data is increasing memory is increasing data is decreasing memory is decreasing till the time data is not exist in the table level or inside the table then low memory we can do anything no memory no utilization of the memory log file generate or not no so what is log file if you are working in some other kind of project or environment maybe you know about log file so log file is just like a text file if any transaction or operation execution anything is going on one file will be generated in that all the history will be maintained what is going on when started any failure is there or not which time started which time completed so everything will be maintained as a history so later on in future if required if any problem is happen we want to cross verify or C or backtrack back Trace then we can go for the log file to check similarly in database also is it generating any log file for all these ddl operations no never but for a DML operation yes because those are data related so if you are removing the data or adding the data or modifying the data maybe after one hour two hour or three hour we can revert back we want back for the timing we are doing all these after some time we want to revert back so if any history will be there then only we can trace back so for DML log file is required for ddl log file is not required so they have not given this feature index is used or not yes by default index is not ETL testers topic that is for developers so what is index index is one object of database which is used to huge to speed up the query if a query is very slower it should complete within two minutes but it is taking two hours so it is very very slow so testers will raise a defect developer will fix that so developer will analyze in which column what kind of what kind of index need to be used they will analyze and they will add that after adding query will be faster again then we can execute the query within two minutes it will complete so all these create alter drop rename tronkit 5 commands we are using various means easy way just writing the query for one one line but for that one one program is written in C language create is having one program in C language 50 lines or 100 lines of poor similarly rename also truncate also so for all those index is by default used they have used already index in their core level for all these commands no need to use from our side or anything they have already used it since they have already used index in all of these commands so these commands are very very faster if crores of Records we are removing or dropping a table which contents with crore Scrolls of data within fraction of second it will be removed if you are truncating 10 million records within fraction of second it can be done why because index is already used in these commands and for that it is very very very faster in DML commands index are not used so those are very very slower Auto committed or not yes so what is auto committed commit is one command present in TCL tomorrow we'll see TCL so in TCL commit comma is available and commit means save purpose we are doing some DML operation insert update delete those things we can save in database like in a text text pad Excel Word document also we are saving if we are doing some operation same here also we can save otherwise it will not present next time if we'll close our system again we are reopen then it will not available so we can save that that is called as commit command so this ddl commands are not required with any commit these are Auto committed commands so automatically it will be committed don't do save after create after rename after truncate after drop after alter all these are Auto committed in interviews so they are asking can you tell some Auto committed commands in SQL you can tell all ddl commands are Auto committed which are create alter drop rename trumpet okay so these are few points about ddl if anybody is asking anywhere can you explain about ddl you can tell these points foreign duplicate or clone so how to create a duplicate table duplicate means clone or we can tell it as replica one table we have already exist how we can create the same table no need to write create and all those things like the process shortcut approach we have we can follow that so we need data also or we need only structure based on that we have to form a quiz so how to write one table we can use select star from dep okay so see let's start from Dept this is a table three columns are there data also present structure is there data is there now we want to create a replica of this table so how to create create table table name test 201 suppose test select star from DBT where one correct condition we can give a one two condition we can give the two condition can be anything one equal to one tiger equal to Tiger India equal to India Pakistan equal to Pakistan anything but that should be a true condition so in many article it is mentioned one equal to one one equal to 1 is fine but if cross question is coming instead of one of one equal to 1 if you are giving lion equal to Lion then what will happen many people are silent no I don't know so the logic is you have to give a true condition it can be anything it can be Vegeta equal to visit fine it can be laptop equal to laptop 5 x equal to x y equal to Y anything you can give but that should be a true condition otherwise don't eat true condition without giving also by default it indicated true condition thank you so s select star we have given instead of star we can mention some columns also all columns not required one column required two column required five column required just mention those columns which are available in DBT so I have given here star 2 0 1 may be already exists two zero five creator select star from test two zero five this is the table column name structure data everything came together so true condition is by default if you are not giving also fine if you are giving also five so two condition is given means structure Plus data both will come as a replica if true condition we are not giving then data will not come structure will come as required another table we can create two zero six so on wrong condition giving 1 equal to 8 this year wrong condition not true so what is this this is false if it is a false condition then only structure it will come plot data foreign table is created structure is there column is there everything is available but no data only structurally no data because we have given a false condition so this is the way how to create a duplicate table a replica of a table or plural of a table with with structure with data with structure without data okay so all these are about ddl for today
2023-06-07