DEV Community


Day 56 OF 100DaysofCode: More Properties OF SQL

iamdurga profile image Durga Pokharel Updated on ・1 min read

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.

Discussion (2)

Editor guide
aarone4 profile image
Aaron Reese

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,,AVG(order.value) OVER (PARTITION BY customer_id)

Means you don't need a GROUP BY that includes all the customer fields.

iamdurga profile image
Durga Pokharel Author

Thank you for shearing your knowledge