6:20 This is also a really dangerous query as it only tells you the affected rows in the Gender tables and don't tell you anything about the other 4 rows deleted from the Students and Teachers table.
Hello Jack - Absolutely, please let me know if you have a specific question or a concept in mind that you want me to cover. All the very best with everything you are doing.
Hi Venkat, I'd second this. Your SQL videos have been invaluable. I always direct others to them if there's a concept they need to learn. The main thing I was missing is more detail on the DBA side of things. Particularly difficult to find good information on design considerations for indexes - all the different types, when and why to use one over another, how many indexes to add to a table, how many columns to include in a single index, how to effectively test them, managing them in ETLs, maintenance etc.
@@Csharp-video-tutorialsBlogspotHi Venkat, As you have covered most I see some topics on Normalisation like BCNF, 4F, 5F are left. This is just an example so if you can cover any leftover from the current list it helps a lot
Based on your example is cascade delete faster if you are using FK? I am thinking it would not have to recheck the FK constraints again on the parent delete
Hi Akash - Good question. I always prefer soft delete over hard delete, just in case you need to undo the delete in future. Most data these days is soft deleted except where regulations doesn't allow that. If it's a hard delete, I don't think there is an easy way to recover deleted data.
Hello. Thanks for this video. But I am working with a huge database in which more than 30 tables are referencing the primary key of a table. In this case is there any alternate way of deleting entries from child table without writing explicit 30 delete commands for 30 child tables?
Everything about a table can be changed afterward with an ALTER TABLE (as long as you don't create a conflict by this action). In fact this video showed how you could add the "ON DELETE CASCADE" by dropping the initial foreign key constraint then adding it back with a ON DELETE CASCADE specification. Of course for those kind of policy, it would probably be best if you thought about it beforehand and imposed it from the get go (you should modify your schema anyway).
Hi sir, faced one question in interview like how to show only top 5 salary record in the result. They don't want top 5 command and something else they expected. Please help
Big thanks to you sir, miku possible aithe ee course ni Telugu lo upload cheyandi sir because non IT students,and Telugu medium students kosam videos cheyandi sir,vallaki enka easy ga avuthundhi sir.
You are very welcome and thank you very much for the feedback Abhiram. Means a lot. The following is the link our complete SQL Server tutorial. Hope you will find it useful. th-cam.com/play/PL08903FB7ACA1C2FB.html
Great Video! Can I request you to make video on following features of SSMS 1) SQL Jobs 2) Profiler vs xevent profiler 3)Database Tuning Adviser Thanks 😊
When a record from parent table is to be deleted... Can we update a default value to be updated for the columns in the child tables instead of deleting them?? Can you explain that if that's possible?
Hi Sriharsha - Yes, the related records in the child table can be updated with default or null values. We discussed this in detail in our SQL Server tutorial. The following is the link to the video. Hope it helps. th-cam.com/video/ETepOVi7Xk8/w-d-xo.html
Hey Akash - Thank you very much for taking the time to provide feedback. Many of the advanced concepts are already covered in our SQL Server tutorial. The following is the link. Hope you will find it useful. th-cam.com/play/PL08903FB7ACA1C2FB.html Please let me know if you have a specific concept in your mind that you want me to explain. All the very best with everything you are doing.
You can loop through no of days in a month , then use IF DatePart(WEEKDAY,@Start_Date) = 1 or DatePart(WEEKDAY,@Start_Date) = 7 SET then increment counter @count=@count+1
Removing FK constraints or enabling cascading deletes isn't really using a query to delete from multiple tables. The last example is more work than just deleting the data via multiple queries. I was hoping for a cool trick where you could use a delete force command or something. I don't want to spend more time to delete.
Hello Sharbashis - Grouping sets and roll up is discussed in our SQL tutorial for beginners course. Please check out videos from Part 101 to 105. csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
Hello Sir, every topic you cover is something Motivational to me. I have a question, suppose we have multiple duplicate entries in the table A with identity column and also have foreign key reference constant with table B having multiple duplicates inserted into table B. Now how to delete duplicate rows from both the tables without changing to cascade
Hello Naveen - I think I understood your question. However, just to make sure I have got it correct, can you please email me the sample tables with data in excel to kudvenkat@gmail.com. I will try to record and upload a video as quickly as I can.
Wow....Great question Jalal. I will record and upload a video as soon as I can. Thank you for asking. In the meantime, the following is the query. CREATE TABLE Students ( NAME NVARCHAR(50) ) GO INSERT INTO Students VALUES ('Sara') INSERT INTO Students VALUES ('Tom') INSERT INTO Students VALUES ('Mike') INSERT INTO Students VALUES ('Tom') GO SELECT TOP 1 NAME, COUNT(Name) AS Total FROM Students GROUP BY Name ORDER BY Total DESC -- If you just want the value use the SQL query below SELECT TOP 1 NAME FROM Students GROUP BY Name ORDER BY COUNT(Name) DESC
Hi Kudvenkat, I am a new to SQL SERVER and recently the msdb database is in suspect recovery mode and all the databases are in recovery pending and SQL AGENT is not visible on the instance.My challenge is I can’t solve the problem as the file path after the sql server upgrade from sql 2016 to sql2019 is not recoverable as I tried to rebuild the msdb database.I moved the msdb and renamed it to make it easier to rebuild the msdb but to no avail.Please if you can do a video on msdb suspect recovery mode.Thank you! Keep it up you already helping plenty of people like myself. Thank you in advance.
I have one doubt. Anyone can explain? He added constraint again. But where he took that constraint? Because drop constraint I could understand. But add constaint where he took?
Please sir upload video on if absentees of a particular student greater than certain condition then automatically alert message sent to that student that you are going to be dissmiss ( as.net mvc , sql server)
Assume I have 15 columns I need to exclude only two columns in select So writing 13 columns takes time and increase size of query How to make short and concise
Hello Syedmujeeb - Good question. I don't think there is an easy way to specify an exclude list other than creating a view with the columns you want and selecting from it or using temp tables which is more tedious than just including the list of columns you want.
Hi Kudvenkat. Thanks for the video. Can you please make a video to show how to insert into, select delete and update several one to many tables. For example a table student, teacher, address, phone and email. Both the teacher and the students can have several phone number email, phone and address. Thank you
Misleading title - altering the foreign key (which you don't always have permission to do) is NOT an "sql query to delete from multiple tables" like you have in oracle...
Hi Sir, I'm a 31 Y.O. undergraduate and unable to get a job because of my graduation status. I'm an OCA with good SQL knowledge. I'm stuck with 20K salary for the past few years. I've learnt alot from you. Please help me.
Why don’t you deliver dinners to IT companies and leave your business card in those companies, maybe someone will like your attitude and you will get better job.
Not sure what the goal is? "I should be able to cascade delete"? If yes, then it means I don't care of the implications or best practices, but I simply love to delete anything that comes in the way :)
Best quick intro to references, referential integrity and cascading deletes for SQL beginners I've seen so far
Thank you very much, spent ages on the internet trying to find how to do this until I found your video on TH-cam, it was a lifesaver!
Sir in this year's u had thought us sql big thanx for that, I have one request sir please release videos of no-sql for a change take it to next level.
6:20 This is also a really dangerous query as it only tells you the affected rows in the Gender tables and don't tell you anything about the other 4 rows deleted from the Students and Teachers table.
You could also make a before delete trigger using instead-of trigger on the parent table which deletes the child rows first.
Absolutely Jan, another way of doing. Thank you for sharing your knowledge.
Very clear explanation with real case scenarios.
As Many people asking please make more videos on SQL
Hello Jack - Absolutely, please let me know if you have a specific question or a concept in mind that you want me to cover. All the very best with everything you are doing.
Hi Venkat, I'd second this. Your SQL videos have been invaluable. I always direct others to them if there's a concept they need to learn.
The main thing I was missing is more detail on the DBA side of things. Particularly difficult to find good information on design considerations for indexes - all the different types, when and why to use one over another, how many indexes to add to a table, how many columns to include in a single index, how to effectively test them, managing them in ETLs, maintenance etc.
@@Csharp-video-tutorialsBlogspotHi Venkat, As you have covered most I see some topics on Normalisation like BCNF, 4F, 5F are left.
This is just an example so if you can cover any leftover from the current list it helps a lot
Please do video's on SSIS, SSAS nd SSRS in detail.
Please do videos on ssis Ssrs ssas
Thanks Sir, You always give perfect explanation.
Best channel, best vedios on every topic that i learned. Going to complete all videos and practice them😍
Thank you sir. The way you tech and the way you explain the topic is awesome it's directly hit into the brain. Once again thanks sir 👏👏👏👏👏👏👏👏👏👏👏👏👏
Based on your example is cascade delete faster if you are using FK? I am thinking it would not have to recheck the FK constraints again on the parent delete
Which one you suggest? soft delete (isDeleted) or hard delete?
Is there any method to track or retrieve deleted rows?
Hi Akash - Good question. I always prefer soft delete over hard delete, just in case you need to undo the delete in future. Most data these days is soft deleted except where regulations doesn't allow that. If it's a hard delete, I don't think there is an easy way to recover deleted data.
You could use an After Delete DML trigger to store the deleted records in an audit table, or just use a temporal table.
Thank you Simon, yes, temporal table is a great option. Thank you for your time.
Thanks both of you. 💕 I don't know about temporal table and DML trigger, but surely I'll do a research on them.
Best ever tutor at TH-cam please share all in one video on asp.net n c#
Thank you Venkat ! Good stuff as always !
Hi Venkat, how about disabling the FK constraint(s); deleting the rows; and enabling the constraint(s)? Please advise.
In your video @7:36 you are deleting the child records first and then the parent record, so why do you need to remove the FK first?
Golden info for newbie like me. Marvelous
Your teaching is awesomely understanding.
Hello. Thanks for this video.
But I am working with a huge database in which more than 30 tables are referencing the primary key of a table. In this case is there any alternate way of deleting entries from child table without writing explicit 30 delete commands for 30 child tables?
Did I get it correctly, that cascading policy should be given when we create a table and is not changeable afterwards?
Everything about a table can be changed afterward with an ALTER TABLE (as long as you don't create a conflict by this action). In fact this video showed how you could add the "ON DELETE CASCADE" by dropping the initial foreign key constraint then adding it back with a ON DELETE CASCADE specification.
Of course for those kind of policy, it would probably be best if you thought about it beforehand and imposed it from the get go (you should modify your schema anyway).
@@chaddaifouche536 Thank you so much for your response. I'll try it out right now.
Wat if both primary and foriegn key on the same table i.e.. a primary code that will be a parent for another primary code...plz reply sir
Same approach.
Hi sir, faced one question in interview like how to show only top 5 salary record in the result.
They don't want top 5 command and something else they expected.
Please help
@sandeep kumar Please write "select salary from table order by salary desc limit 5;" I guess this should work
@@gagandeepgogna thank you so much, will try this query
Big thanks to you sir, miku possible aithe ee course ni Telugu lo upload cheyandi sir because non IT students,and Telugu medium students kosam videos cheyandi sir,vallaki enka easy ga avuthundhi sir.
Awesome. Your video always gives good concept. Whenever I face confusion then I use to watch your videos.
Hi Kudvenkat, someone doing Frodd with your name. Please reply
thank you so much for such nice explanation. Finding it very usefull
You are very welcome and thank you very much for the feedback Abhiram. Means a lot. The following is the link our complete SQL Server tutorial. Hope you will find it useful.
th-cam.com/play/PL08903FB7ACA1C2FB.html
Great Video! Can I request you to make video on following features of SSMS 1) SQL Jobs 2) Profiler vs xevent profiler 3)Database Tuning Adviser
Thanks 😊
Many thanks sir, but can we do same action with access database? Or do you have any advice?
Thanks and regards
Great explanation and examples
When a record from parent table is to be deleted... Can we update a default value to be updated for the columns in the child tables instead of deleting them?? Can you explain that if that's possible?
Hi Sriharsha - Yes, the related records in the child table can be updated with default or null values. We discussed this in detail in our SQL Server tutorial. The following is the link to the video. Hope it helps.
th-cam.com/video/ETepOVi7Xk8/w-d-xo.html
@@Csharp-video-tutorialsBlogspot Thank you sir, that really helped...
Hey there sir. Big fan of the works, would really appreciate if you could also teach us advanced sql server. Thank You
Hey Akash - Thank you very much for taking the time to provide feedback. Many of the advanced concepts are already covered in our SQL Server tutorial. The following is the link. Hope you will find it useful.
th-cam.com/play/PL08903FB7ACA1C2FB.html
Please let me know if you have a specific concept in your mind that you want me to explain. All the very best with everything you are doing.
@@Csharp-video-tutorialsBlogspot Thank You sir
Hi Sir, I love your content. Also, please make videos on SSIS.
Please like his comment if u also want separate video on SSIS.
Awesome content. Keep posting content so that we can continue lerning :)
Hi Venkat. How to find out number of weekends in given month. How we can do that? Thanks in advance
Great question Ankith. Was this asked in an interview? I will record a video and upload soon. Thank you for asking.
@@Csharp-video-tutorialsBlogspot Thank you very much for response venkat. Yes in interview this was asked.
You can loop through no of days in a month , then use IF DatePart(WEEKDAY,@Start_Date) = 1 or DatePart(WEEKDAY,@Start_Date) = 7 SET then increment counter @count=@count+1
Removing FK constraints or enabling cascading deletes isn't really using a query to delete from multiple tables.
The last example is more work than just deleting the data via multiple queries. I was hoping for a cool trick where you could use a delete force command or something. I don't want to spend more time to delete.
Excellent video. Thanks a lot.
Only one key from composite primary key as foreign key- is this possible in sql server?
Hi Venkat,
Can you please do a video on parameter sniffing and what is the best approach to solve parameter sniffing problem?
Please make a video on Grouping sets and Roll Up function with multiple tables joined!
Great video appreciate it!👍
Hello Sharbashis - Grouping sets and roll up is discussed in our SQL tutorial for beginners course. Please check out videos from Part 101 to 105.
csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
Hello Sir, every topic you cover is something Motivational to me. I have a question, suppose we have multiple duplicate entries in the table A with identity column and also have foreign key reference constant with table B having multiple duplicates inserted into table B. Now how to delete duplicate rows from both the tables without changing to cascade
Hello Naveen - I think I understood your question. However, just to make sure I have got it correct, can you please email me the sample tables with data in excel to kudvenkat@gmail.com. I will try to record and upload a video as quickly as I can.
@@Csharp-video-tutorialsBlogspot thank you sir, nice of you responded very quick, I will send all the details in an email.
what if we want to delete master data but don't want to lose transaction data?is it possible?if not, what is your suggestion?
It is specific to Microsoft SQL or applicable for oracle also??
700 like from me and a sub, im starting to watch all your vids, gg for work 😊
Why you didn't deleted the HR depatrment?
Hahaha......no specific reason to be honest. Hope I did not offend you by deleting the IT department 😀
Hi Vankat,
Can you please make video on Temporal table
Hey Mohammad - Temporal Table is a great concept. Thank you for asking. Sure, I will record and upload very soon. Great question Mohammad.
@@Csharp-video-tutorialsBlogspot thank you for the response
How to write a query to display the most repeated value in a column?
Wow....Great question Jalal. I will record and upload a video as soon as I can. Thank you for asking. In the meantime, the following is the query.
CREATE TABLE Students
(
NAME NVARCHAR(50)
)
GO
INSERT INTO Students VALUES ('Sara')
INSERT INTO Students VALUES ('Tom')
INSERT INTO Students VALUES ('Mike')
INSERT INTO Students VALUES ('Tom')
GO
SELECT TOP 1 NAME, COUNT(Name) AS Total
FROM Students
GROUP BY Name
ORDER BY Total DESC
-- If you just want the value use the SQL query below
SELECT TOP 1 NAME
FROM Students
GROUP BY Name
ORDER BY COUNT(Name) DESC
@@Csharp-video-tutorialsBlogspot thank you soo much sir
Anyone knows if exist some way to add intellisense code completion and auto-format T-SQL code in SQL Server Management Studio?
If your willing to spend, try checking Red gate SQL Prompt. The best intellisense and TSQL formatter I've used so far.
Hi Kudvenkat, I am a new to SQL SERVER and recently the msdb database is in suspect recovery mode and all the databases are in recovery pending and SQL AGENT is not visible on the instance.My challenge is I can’t solve the problem as the file path after the sql server upgrade from sql 2016 to sql2019 is not recoverable as I tried to rebuild the msdb database.I moved the msdb and renamed it to make it easier to rebuild the msdb but to no avail.Please if you can do a video on msdb suspect recovery mode.Thank you! Keep it up you already helping plenty of people like myself.
Thank you in advance.
Thank you very much sir for this excellent tutorial!
Hey, can you record video about all kind of table joins (inner, outer, left)etc.
Hello Szymon - The joins are discussed in the following video. Hope this helps.
th-cam.com/video/wW4xcQ3FFp4/w-d-xo.html
Kudvenkat please post more sql related videos your sql server videos is awesome.
Using a function in where condition can impact query performance. If you are playing with big data
Thank you!
I watch all your videos.. great content. Can you please make Azure Synapse Analytics series ?
Great info. Thank you for this
good explanation.. thanks sir..
Excellent..
Thank you sir,,
Hi venkat could you pease make video on fill factor and table partition
Thanks Bro.
I have one doubt. Anyone can explain? He added constraint again. But where he took that constraint? Because drop constraint I could understand. But add constaint where he took?
Please I need a T-SQL script to delete multiple or all user databases in a server/instance
Thanks for sharing
While deleting tha large data log size is increasing even its in simple recovery any solution for how to limit the log size increasing??
Please sir upload video on if absentees of a particular student greater than certain condition then automatically alert message sent to that student that you are going to be dissmiss ( as.net mvc , sql server)
Oh wow, the organization must be seriously downsizing if they are deleting entire departments 😊 Good explanation though.
I am big fan sir
How to use sql view in asp.net mvc?
dear sir, hur man send email to customer email when his subscription come near to ending, thanks
Thanks sir
Assume I have 15 columns I need to exclude only two columns in select
So writing 13 columns takes time and increase size of query
How to make short and concise
Hello Syedmujeeb - Good question. I don't think there is an easy way to specify an exclude list other than creating a view with the columns you want and selecting from it or using temp tables which is more tedious than just including the list of columns you want.
Thanks
Hi Kudvenkat. Thanks for the video. Can you please make a video to show how to insert into, select delete and update several one to many tables. For example a table student, teacher, address, phone and email. Both the teacher and the students can have several phone number email, phone and address. Thank you
Hi Venkat can you make a videos on Snowflake Please with real time scenario's ? Please sir
Nice video
Nice explanation
Misleading title - altering the foreign key (which you don't always have permission to do) is NOT an "sql query to delete from multiple tables" like you have in oracle...
Can you please teach us asp boilerplate
good
Hi Sir, I'm a 31 Y.O. undergraduate and unable to get a job because of my graduation status. I'm an OCA with good SQL knowledge. I'm stuck with 20K salary for the past few years. I've learnt alot from you. Please help me.
Why don’t you deliver dinners to IT companies and leave your business card in those companies, maybe someone will like your attitude and you will get better job.
Not sure what the goal is? "I should be able to cascade delete"? If yes, then it means I don't care of the implications or best practices, but I simply love to delete anything that comes in the way :)
Cascade Delete
Child First then -> Parent
anyone thinks the intro music has a Sopranos theme feel? Either way awesome
Delete cascade is a terrible thing to be adding, especially on large databases. You give users the ability to wipe out data they might not want to.
Gender its just M and F.