I know cognizant exam is on the corner and you want to a it recently you appeared for your communication round and I know whoever watched my video will definitely qualified for their respective cluster so in this video I will cover the most ask or repeated pattern in previous year times I gathered all the problems of technical sections okay so this is a very first video so we'll take it in easy medium stuff but I recommend you to have a pen paper with because in this particular video we'll cover SQL and much types of questions based on technical stuff so do Mark each and every answer which is right wrong so that you can take out what all things you know and in which aspect you need to prepare so I am adya and you're watching Prime coding in this video we solve cognizant complete technical assessment which will help you to Ace your cluster o exam now coding in Python okay so you will get query database MySQL problems SQL commands ddl it's a totally subjective just a written okay it's totally depend upon each cluster of different languages so ddl DML DCL TCL understanding constraints and their types what is this what do you mean by it guys in this particular thing I'm letting know about the syllabus because in the very first I'm letting you know about the syllabus if you're not confident with stop this videos right away and start watching the SQL one shot you will get the intuition of all these topics then only you can able to get good marks in this assessment okay now the knowledge in McQ which is required will be covering in this particular thing which will be unix commands shell scripting data warehouse Basics ETL and Cloud fundamentals guys in behalf of you me and my team searched a lot on the internet and gathered all the problem fetching out the repeating patterns and just putting up soou because many many many people were request me on my telegram Channel if you have not joined it we have at most or we have 20K at most 20K candidates in our Prime coding Community where they are open to help each other so if you have any issues want to rectify I I'm also there I will rectified otherwise our bliant mind is ready to help you out so if you have not joined the link in the description okay so very first question how do you create table name student with the column ID number and name text as this type of question you can expect in your cognizant exam where you need to write your query so query to should be very accurate and to the point so we know the syntax of creating a table is it create now table what is a table name that we need to create it's a students or student whatever you want to but as question mentioned so do follow that so create table students now what is a two parameter we have an ID which would be in the integer format and for keeping it unique and not n value you you should provide it as a primary key or it's totally depend upon you whatever you want to because in this they don't State about the constraints okay and what else you need to do you need to add it as here is name whereare 255 now 255 great so go will not come here it will come here okay now the next question that I want to ask you is or the next question can be framed in your exam would be what is the difference between where car and car so what I meant to tell you is if let's say initialized a where care L you initialize a care okay let me write so that you will get an intuitive knowledge of where care and care so if you initialize Vare of 20 space and can to 20 space the memory and the memory will be allocated in Vare only respective to the length of the particular character okay let's say ADI okay so ADI has three letters so only three letter out of 20 would be allocated and the 17 space is still free it is not being used but if you append the same name here ADI it will allocate 20 space irrespective of the length of the letters or word okay so yeah so it is better to use workare every time which is space efficient as well so always focus on Ware writing okay so basically we usually provide 255 so that the range of whole word would be 255 not exced that because it can exced the range of 20 I hope you got this point so yeah let's see the solution as well so create table students ID in name vcat 50 and explanation would be this if you want to read it what actually happening here so plus one Mark if you mark this question right is it now the second problem which is insert data into table which is a DML command why I'm mentioning DML is because in the exam you may get a McQ where you need to St the command comes into DML so insert is one of the command that comes into the DML family great so how do you insert a new student with ID equivalent to one and name is Alex so if you know the syntax of inserting a data into a table it would be very easy so insert into what table that we recently created the very first question which is students and the next is the schema it is totally Dependable but I would highly recommend you to write the schema so ID name now what else that you need to put you need to put the value as one is an integer I don't need to put the quotation mark But in Alx I need to so this is the particular syntax to append a data into my database or insert a data into my database as you can see insert into ID values would be one and Alex fantastic okay if you want to go through the explanation you can go through it plus one marks again so how do you change the name of the student with the ID to one Alis okay great so here if they're asking about the DML operation again so update is the hint so now you need to use the update keyword here so update now here table will not come or update table table it will come update table name directly update table name students what I need to do I need to set I need to set what the ID is equivalent to one okay change the name okay I need to set the name okay so ID will not come set name is equivalent to Alice okay where where it is given where my ID is equivalent to one so this would be the uh syntax to update the the ID go into one and S their name to Alice whatever we have just now okay so yes update student set name Alice where ID is equivalent to one so as I state in the early stages as well you might think for most of them who are brilliant they would think okay these questiones are very easy so try to grab it try to score much marks in this assessment but in the next series like next assessment series the question level would be high so do prepare it okay so yeah this is a correct answer which match with our query so again plus one Mark if you mark this query right and we have a given explanation we found the student ID with ID one and Chang the name of Alex Alex to Alice okay fantastic now delete delete data DML so again we till now what on DML commands we covered insert update now we are here with delete so how do you delete a student with ID equivalent to one from the students table okay so yeah what I need to do is I need to use a delete keyword word here now delete from students where ID is equivalent to one so this would be the correct syntax because we know that delete now where I need to delete from where students there's a table where you need to find the ID is equal to one and this delete the particular row okay so this is the particular syntax that we need to do now let's see what would be the correct query so delete from students where ID equivalent one so explanation we remove the student whose ID is from the table imagine that taking a card out of the box fantastic so set a primary key constraint that we already did in a very first problem so how do you make sure each ID in the students table is unique and cannot be empty so if you want to make it unique and cannot be empty then definitely you will use primary key but there is a homework for you let me know in the comment section what is the difference between unique and primary key okay so yeah I hope you know about unique key as well okay so check that out now let me know that so just a simple difference that we created in the first thing I wrote in the very early stage but here this is the Cal that popped in recent times so do check that out so name is work at 50 again and the explanation will remain the same as I said in the very first problem so I hope you got this again plus one marks for free use SQL operator how do you select all students with ID greater than five very easy question if you want to select all data so select St from where I need to select from my students table fantastic where my what id should be greater than five so ID should be greater than five and this is a very easy quering that you need to write and I hope you again mark this question right because I I need all of you to score full on full in this particular test but I will challenge you to let me know how much you scored so yeah plus one marks again whatever you got it if you got it wrong either the semicolon mistake do don't cheat me okay sub ract it from your particular marks if you want to be very strict with your marking scheme otherwise zero okay so the finds all student whose ID is bigger than five it's like picking cards from the box where the number are more than five fantastic so use SQL functions now the functions comes into picture how do you find the total number of students now total number of students after reading it directly into my mind the aggregation functions come into the picture I hope you know about aggregation function because it's a trendy question ask interview as well so in the students table so we need to find everything so select is the key that we need to use can you let me know which type of command select Us in the comment section do let me know so total so total will be happened using countstar method from where I need to do from students I need to do from students I need to find the count total number of students where uh yeah that's a thing okay great so this is the particular syntax of this particular question is it yeah select count star from students okay plus one again for marking this question right explanation the count functions counts how many students there are in the Box fantastic now using joins okay joins comes to the picture which most of the people are scared of but stay with me this question is not that hard not that easy I will let you know how you can write join query in very very easy manner okay so how do you show all students all students so I got to know there's a student data and their grades from grades table using joint now I know that if they are relating the student table with grades then definitely there would be a relation of primary key and foreign key okay so I will create an another Circle having grades and having students as this okay so I need to return all the students which has a corresponding grades with it okay so with join I will use I will CH simple in a join is it so now how I can write this code is Select what I need to find I need to find all student to show all students and their grades from a grade table so all students so s dot I would say name okay name and G do grade fantastic from where and you do from now in this case I need to use both of the table and simultaneously I need to use join so what I will use join here is let's see so student students is the table that I'm going to use and why I use S such as a analysis okay so s join okay please join what grade okay so grade G now on what basis okay simple like English want you need to select I select the name and the grade now what I from where I need to select from student and please join the grade as well because question asked me to do that I did now on on which on what basis you are connecting both of the table let me know the common instance I will let you know the common instance would be S do student ID which student ID which in the students table we must have a student ID which is relatable or same in the grade as well so equivalent to s. student or G dos ID so this will do my work and find me all the name and the grade associated with all the students I hope you got this now it's totally depend upon you if you want to print all the data you can use star as well you will get all the informations of student and the respective grade and whatever you want to print it's just a simple change in the select statement I hope it is pretty clear to you so let's see the correct option so select students. name grades. grade it's totally depend upon if you want to use the table name directly or if you want to use the alling okay if the features is given to you do use that so from students join grades on students. equivalent to grades. students. ID fantastic so basically from what you need to select from which table from which table you are going to select okay I am considering student now which join let me know the join operation as well so join then next table on what basis that you need to do and any other conditions if you have so in this case we don't have any condition that's why not using anywhere or any subqueries fantastic so explanation this joins links the student table with the grade table based on the table's ID students ID it's like matching students with their report card fantastic subquery use of subquery how do you find the name of the student with the highest ID now I give you the hint about subquery do let me know whether you are able to write the subquery but I will let you know the very easy way to write this query because using subquery the query time would be increased but if you're able to write a query which will take less time it is very nice for you okay so what I need to select is I need to select the name okay so I will select the name as question States fantastic now on what basis from the students okay I need to write the location as well so please get me the name from the students table on what basis on the highest ID and how you will find the ID if you if you yes sort it according to descending okay so I will use order by operation here order by please sort my particular uh list as per DSC okay and and and what I need to do next is just put the limit equivalent to one do I missing something do I am missing something here yes I am missing here is the ID don't you think yes so this is the subquery that the question state which is very easy name from students where ID is Select Max ID from students or this is the query without using subquery so if the question asks you to you use or write a query without using subquery you are also equipped with it and you are also equpped with the subqueries I hope you got the intuition behind behind this particular query is it create a view a very easy problem but do check that out how can create procedure and Trigger as well and do let me know in the comment section what's the difference between View and triggers okay so yeah create okay so I very enthusiastic so that's why directly writing the code but this is not the case that you need to do so how do you create a view that shows only the students with a greater than five okay so then it is very easy so create what I need to create view okay I will create view now I need to give a name for the queue view so student view I would say now how on what cases as create a view as per the query that you going to write this is the line please create a view so create view view name on what basis as in and this basis I will write so select what I need to do only students with G okay so students would be star from where I need to do from the students table I need to find and or where the ID is greater than five so this is the respective query of this particular question and I guess we are right so create view student view as select star from students where ID is greater than five fantastic explanation is a view is like a window into a part of the table here we only show the table whose ID is bigger than five now in the runtime if you want to uh rerun this particular query you just need to uh select star for student view it will do the same task okay totally dependable and it reduces code as well fantastic so now let's discuss Shand sh adesu these candidates who recently cracked essential Camp cognizant TCS Prime digital ninja roles so you can be the next for that you need to take a one toone session from Prime coding because we have a success rate of 19% and you can be the next who will crack the respective interview that you got shortlisted for because it contains 1 hour of extensive extensive and comprehensive session in which it includes your technical test your coding test your vocabulary test your representation skill test and much more analysis will happen in this one hour of session resume review where I will let you know all your weakness how you need to create your resume such a way that you will impress your interviewer and personalized feedback depend upon your mock session what all things that you need to improve how you can improve your communication what things that you are lagging what is your weakness and how you can overcome from it what would be your future guidance everything would be given in the suggestion for future Improvement such as road map mentorship doubt solving motivation guidance and much more for that just head on to the Prim coding. in find your respective company plan and book we will meet you in your session and make your interview opportunity converted to the offer letter so that you can make your parents proud okay so which command is used to list all files and directories in Unix you guess I will miss this topic no guys I will cover it so Unix LS CD RM PWD it is for password it's for remove it's for going to a directory Lis will help me to visit or to see everything at a one stats yes so used to list all files and directly we use LS so LS is the right option but but but I will not leave you I will let you know about all these stuffs because in the future they can ask you about CD RM PD so let's check it down so LS command is to list files directories in the current directory okay it helps you to see what H what's inside a folder the CD change directory command allows you to navigate between the directories fantastic it changes your current working directory to the another specified directory okay so the RM which is remove command deletes file on directories on directories okay be careful while using this as it permanently remove files now the homework for you would be how you can delete a file using RM because in the coent exam the when we are tiing all the problems I saw that there was a problem where they ask you which particular command is Right according to the RM command where you need to remove a file so do check that out it will definitely help you in your upcoming cognizant exam so PWD print working directory okay I was wrong there print working directory command shows full path of the current directory you are working in fantastic so helping you understand your location within the file system so if you worked in this Unix or Linux system there would be very hard to find actually where you you are from if you're continuously navigating in the different different different folders okay so it will help you to get you know about your current location where are you currently now how do you create a new file in Unix using a command so if you want to create a new file just touch it yes touch is the right option what would be the se but I will let you know the rest as well the cat concat command is used to display the contents of a file it can also be used to combine multiple findes and display them together fantastic but are you forgetting to give you plus one marks if you're not if you solve this problem correctly if you are please do give you okay the touch command creates an empty file if the file already exists it Updates this modification time stamp without changing the file content I hope you got it this is a very important point because the future you can expect the problem is what is a specification or the feature if the file is already exist and you created the same file using a touch keyword so you need to know about this definition and you're good to go so so the mkd make directory command creates a new directory folder your your specify name of the directory you want to create MV which is move command is to used to move or rename files and directories you can move a file to another location or to rename it entirely fantastic what symbol is used to represent the current user home directory in Unix so it is it is it is stilla guys a is the right option which is stilla symbol is a short end for current user home directory where there are personal files are stored again again again plus one marks if you mark this question right so what is the default shell used in most Unix based system default shell if you ever used in your vs code and click on the number of terminals you have then definitely you will end up getting bash as an option but bash also has a full form you know it I guess most of the people don't know will let you know it so bash is a Bor again shell so is the most commonly used cell in the unique cell system helping user to interact with the system via commands I hope you know it somewhere think think think it may be good let me know okay so in Shell scripting which symbol is used to comment out the line simple gu simple gu simple it is a hashtag so yeah hashtag symbol is used to com write commments in the cell script these lines are not executed and are used to ex explain the script fantastic what does the term ETL if you are from data analytics background data analysis background if you have done some uh of the data analysis stuff you might know about ETL so for in the data warehousing extract transfer load no extract transform load no it's an execute extract transform load yes it makes sense I don't need to check else so C is the right option so ETS stands for extract transform load which refers to the process of getting data from the sources transforming it as needed requirement of the problem and loading it into the data warehouses now we started talking about Warehouse so the next question is regarding the warehouse so which of the following is not not not a type of data warehouse Enterprise data warehouse operational data store data mod data structure is not a data warehouse It's a technique to manipulate data is it so yeah the correct option would be D let's check that out yes so explanation Enterprises data warehouses operational data stores and data Mars are all types of data warehouses while a data structure refers to how data is organized in memory easy question very easy problem now again I will not leave you on the data warehouse problems okay what is a primary purpose of a data Mar okay to store all complete data in one place no every data do the same to store a subset of data for a specific business unit yes it makes sense to store operational data to backup data no no the right option as for me is B and let's check that out yes the B is the right option so plus one Mark again for marking this question right so data Mark holds focused data that serves the need of a specific department or group within a company such as sales or marketing fantastic what is the one of the major advantages of using cloud computing okay data is stored only on local machines user need to buy their own Hardware it provides scalable resources as needed data cannot be accessed from different location so using cloud computing what other stuffs it will get you so don't you think it provides scalable resources whenever I need that's the particular thing that every company is looking for and even I am also looking for so yes C is the right option cloud computing allows user to scale resources up to their requirement it may be horizontal scaling or something okay so which is a business of pay business on pay model that we making it cost effective whatever you use you need to pay which cloud computing platform provides a platform of developer to build and deploy application without waiting wasting time pass is the right option because it provides platform to us so yes pass is a right option you can go through the explanation okay so unique and previous year coding question let's start this but but but I will not continue this in this particular video why because it will go like 1 hour or 2 hour of single video but I don't want to this is a spoiler for the next video if you want it do let me know in the comment but I will not leave you like this I will show you the spoiler of all the most ask frequently based problems which will help you to crack the company but if you are eager to solve problems here is a sheet which has top 30 coding question which were mostly asked and all these were asked in previous year questions so we find out from multiple websites from few of the premium sites as well so that it will help you to get everything at a single place and you can prepare for your upcoming cognizant assessment so definitely do check that out because few problems are also being repeated and I will cover in the next video as by your demand please like this video If this video hits 200 like definitely the uh coding sheet video will come or the coding solution which I just show you I will put the solution of all on that problem so do comment down the homeworks that I given and the particular thing that you need or not okay so if you want to prepare for TCS nqt essener capj mini cognizant you are at the right place because you will find end number of resources to prepare and crack your off- campus or on campus company opportunities so so thanks for watching if you are watching till now do consider subscribing this channel like this video if you are not and do share with your friends so that that your friend can do something in their career we'll meet you with a new video soon