This is my 56th days of #100Daysofcode and #python. Today I learned more properties of SQL including BETWEEN, WHERE IN, SELECT COUNT(*), LIKE and NOT LIKE, Aggregate function, AVG(),SUM(),MAX(),MIN() etc from datacamp.
Day 56 Of #100DaysOfCode and #python
— Durga Pokharel (@mathdurga) February 22, 2021
More Properties Of SQL#womenintech #DEVCommunity #CodeNewbie pic.twitter.com/0e8EZYmCDL
Top comments (2)
Gotchas:
BETWEEN with dates is inclusive, but if you have date time fields it will only consider up to the date AND time given so no time means the upper cutoff will be midnight at the start of the day.
SELECT COUNT (*) will do a table scan and can be expensive. Most RDBMS will have a method to read the statistics which includes the rowcount. Alternatively count the primary or clustered key as this will only be an index scan.
Don't ignore SELECT COUNT DISTINCT (field) which effectively does a GROUP BY on the field or fields and then counts the group's. Or SELECT DISTINCT field1, field2 which also does a implicit group by. It is a good way to check for Cartesian joins: if the distinct returned fewer rows then you have a bad join somewhere.
The aggregate functions can be used with the OVER() clause which defines a context for the aggregate. E.g.
Select customer.ID, customer.name,AVG(order.value) OVER (PARTITION BY customer_id)
Means you don't need a GROUP BY that includes all the customer fields.
Thank you for shearing your knowledge