Another (IMO better) way to do #5 is just write it as a SELECT query to start. You need to validate your records before updating/deleting anyway. Once it's correct you can replace SELECT with DELETE; or write UPDATE/SET clause without the FROM and WHERE; and copy/paste the WHERE from the SELECT query. I often end up with something like this, and once my result set is what I want I flip the commenting from UPDATE to SELECT: select COLA, COLB, COLC from TBLA --update TBLA -- set COLA = x -- , COLB = y -- , COLC = z where CRITERIA1 and CRITERIA2 etc...
It depends on your database, but you can usually query the data dictionary to find this information. Something like SELECT * FROM information_schema.columns WHERE table_name = 'person'; - this could work on MySQL to see column information. Other vendors (Oracle, SQL Server, Postgres) have similar features.
Nothing wrong with using select * if you do not know all the columns in the table. And to prevent getting to much data, just limit the rows with 'ROWNUM < 10' for example, or like 'fetch first 10 rows only'
Good point, yeah using Select * is helpful to see all of the columns. Using it to find out about the rows and columns is a good way to use it. Using it in production - different story!
@@DatabaseStar i do this in production daily and limit the result to 1 row I have to use a couple of dozens of tables and can't remember all of the columns in all those tables, only the most common ones used. So i use it whenever i have joins, or want to see if any additional columns help to present better data. Sure i can go into an object browser and go and look at the columns there, but that takes much more time. As long as it is used wisely i see no issue of using select * in production
Hello sir. Are you planning to cover window functions like over-partition by clause and topics like cte? please consider the employee database that you used in your udemy course as it will be easier to understand from it. please cover these topics.
7:52 NO NO NO NO! YOU DO NOT RUN AN UPDATE OR DELETE CLAUSE WITHOUT CHECKING WITH SELECT FIRST! REDO THIS ADIVE I BEG YOU! Write a SELECT statement and see if the returned data is truly what you wanted to mofiy. Then only then, replace SELECT with UPDATE OR DELETE.
Great point! Yes you should always run the query as a Select first to see the rows that are impacted by the Delete or Update. Then it’s simple to replace it with a Delete or Update. My tip about always adding a Where clause to a Delete or Update still stands - perhaps I should have added your suggestion to this video to make it better.
*Mistake 3 - Not using functions* 4:17 "SQL includes many in-built functions to process data and give you a result." Where is the *MEDIAN* function ????
The suggestion #2 is absolutely wrong and it may and certainly will cause your application to halt. Let's imagine a real application consisting of something like follows: table Orders ( OrderId primary key, -- ~ 20 columns with data ) table OrderItems ( OrderId int reference to Order, ProductId int reference to Product, primary key (OrderId, ProductId) -- ~ 8-10 other columns with data ) table Products ( ProductId primary key, -- 10 other columns with data ) Now, we need to fetch information about 10 orders with their items and products to construct business logic models. Let's assume that each order has 20 items. In case of one select query which joins all related tables and return one result we will have: 200 rows - 10 orders * 20 order items each row consists of 40 fields - 20 field of order + 10 field of order item + 10 field of product So that we will have 200 * 40 = 8000 values fetched from the db if we execute three separate queries to receive the same data table by table, we will have: 10 * 20 = 200 values for orders 20 * 10 * 10 = 2000 values for order items 20 * 10 * 10 = 2000 values for products and it is only of all products are unique throughout all orders which is very rear case. So that we will have 200 + 2000 + 2000 = 4200 and even less values fetched from the db. And this difference grows very quickly as information from new tables are needed because they have to be joined to the one select statement. The main problem of a join operation that it duplicates information in rows, this is the reason of difference in field count mentioned above. Moreover, one statement approach does not allow to use caches to reduce common amount of data needed from db.
Thanks for the detailed comment. Are you saying it's bad to join separate tables because it could cause your application to halt? If so, I believe you have been mistaken. Your example indicates 200 rows would be returned, with data from three tables. This is a tiny amount of data for a database to handle, and even in a high-volume system, there are features and techniques that can be used to improve the performance if it suffers. Your scenario of the number of values is OK as well. You may not need to select all values in all tables, but if you do, and you need to select 8000 values (200 rows x 40 values), this is also not a problem for the database to handle. If you execute three separate queries, there may be fewer fields returned, as you've shown. But you would still need to combine the results together to show the correct order and product data. If this is not done in the database, you'll need to do it in the application, which is not ideal and would still take some processing. There is no problem with returning common data if it relates to different rows. You'll have to combine data in one way - either in the database or the application. The database is designed specifically to do this, so it's better to do it there than the application.
Great video, I am just learning SQL in uni and this video explained me a lot
Thanks!
I'm glad it helped!
Another (IMO better) way to do #5 is just write it as a SELECT query to start. You need to validate your records before updating/deleting anyway. Once it's correct you can replace SELECT with DELETE; or write UPDATE/SET clause without the FROM and WHERE; and copy/paste the WHERE from the SELECT query. I often end up with something like this, and once my result set is what I want I flip the commenting from UPDATE to SELECT:
select COLA, COLB, COLC
from TBLA
--update TBLA
-- set COLA = x
-- , COLB = y
-- , COLC = z
where CRITERIA1
and CRITERIA2
etc...
Great advice! Yeah checking the records you are about to change from an Update or Delete by running a Select is very helpful.
Your channel helped (and still) me a lot while learning SQL
Thanks man ♥️
Keep the good work up
Glad it’s been helpful!
Thanks, everything is clear ☺️
Glad to hear that!
what command will reveal all the columns/attribute of a table without using "SELECT * FROM table" in order to avoid mistake n.1?
It depends on your database, but you can usually query the data dictionary to find this information. Something like SELECT * FROM information_schema.columns WHERE table_name = 'person'; - this could work on MySQL to see column information. Other vendors (Oracle, SQL Server, Postgres) have similar features.
Bunch of thanks
You’re welcome!
Nothing wrong with using select * if you do not know all the columns in the table.
And to prevent getting to much data, just limit the rows with 'ROWNUM < 10' for example, or like 'fetch first 10 rows only'
Good point, yeah using Select * is helpful to see all of the columns. Using it to find out about the rows and columns is a good way to use it. Using it in production - different story!
@@DatabaseStar i do this in production daily and limit the result to 1 row
I have to use a couple of dozens of tables and can't remember all of the columns in all those tables, only the most common ones used. So i use it whenever i have joins, or want to see if any additional columns help to present better data.
Sure i can go into an object browser and go and look at the columns there, but that takes much more time.
As long as it is used wisely i see no issue of using select * in production
Hello sir. Are you planning to cover window functions like over-partition by clause and topics like cte? please consider the employee database that you used in your udemy course as it will be easier to understand from it. please cover these topics.
Good idea! I have it on my list of things to cover in future videos.
7:52 NO NO NO NO! YOU DO NOT RUN AN UPDATE OR DELETE CLAUSE WITHOUT CHECKING WITH SELECT FIRST!
REDO THIS ADIVE I BEG YOU! Write a SELECT statement and see if the returned data is truly what you wanted to mofiy. Then only then, replace SELECT with UPDATE OR DELETE.
Great point! Yes you should always run the query as a Select first to see the rows that are impacted by the Delete or Update. Then it’s simple to replace it with a Delete or Update.
My tip about always adding a Where clause to a Delete or Update still stands - perhaps I should have added your suggestion to this video to make it better.
*Mistake 3 - Not using functions*
4:17 "SQL includes many in-built functions to process data and give you a result."
Where is the *MEDIAN* function ????
The Median function is not included in many vendors of SQL, but some Google searching indicates it's possible to write a function to calculate it.
Thanks!
No problem!
I need explanation for indexing
I created a video on Indexing about a month ago that could be helpful.
Subscribed :)
Thanks!
The suggestion #2 is absolutely wrong and it may and certainly will cause your application to halt. Let's imagine a real application consisting of something like follows:
table Orders
(
OrderId primary key,
-- ~ 20 columns with data
)
table OrderItems
(
OrderId int reference to Order,
ProductId int reference to Product,
primary key (OrderId, ProductId)
-- ~ 8-10 other columns with data
)
table Products
(
ProductId primary key,
-- 10 other columns with data
)
Now, we need to fetch information about 10 orders with their items and products to construct business logic models. Let's assume that each order has 20 items. In case of one select query which joins all related tables and return one result we will have:
200 rows - 10 orders * 20 order items
each row consists of 40 fields - 20 field of order + 10 field of order item + 10 field of product
So that we will have 200 * 40 = 8000 values fetched from the db
if we execute three separate queries to receive the same data table by table, we will have:
10 * 20 = 200 values for orders
20 * 10 * 10 = 2000 values for order items
20 * 10 * 10 = 2000 values for products and it is only of all products are unique throughout all orders which is very rear case.
So that we will have 200 + 2000 + 2000 = 4200 and even less values fetched from the db.
And this difference grows very quickly as information from new tables are needed because they have to be joined to the one select statement.
The main problem of a join operation that it duplicates information in rows, this is the reason of difference in field count mentioned above. Moreover, one statement approach does not allow to use caches to reduce common amount of data needed from db.
Thanks for the detailed comment. Are you saying it's bad to join separate tables because it could cause your application to halt? If so, I believe you have been mistaken.
Your example indicates 200 rows would be returned, with data from three tables. This is a tiny amount of data for a database to handle, and even in a high-volume system, there are features and techniques that can be used to improve the performance if it suffers.
Your scenario of the number of values is OK as well. You may not need to select all values in all tables, but if you do, and you need to select 8000 values (200 rows x 40 values), this is also not a problem for the database to handle.
If you execute three separate queries, there may be fewer fields returned, as you've shown. But you would still need to combine the results together to show the correct order and product data. If this is not done in the database, you'll need to do it in the application, which is not ideal and would still take some processing.
There is no problem with returning common data if it relates to different rows. You'll have to combine data in one way - either in the database or the application. The database is designed specifically to do this, so it's better to do it there than the application.
No 5 is disaster
Yeah it is!