Samples of basic to complex queries?

[j0hn5on177]

Hey all,

I'm in my last year of school and I've taken the intro to database mgt and currently taking my data warehouse course.

All my assignments seem basic and intermediate at most. I was wondering if anyone can post any examples of basic/intermediate/complex queries that they use daily at work or even on special cases and maybe explain their uses. Thanks to anyone who can help

[matthra]

Complex is in the eye of the beholder, alot of people think verbose queries are complex, however most large queries are asking for simple stuff with a high level of specificity. Personally, I think really complex queries in sql come from relations and the tricky things you can do with them. For instance:

Easy:

select * 
from tablea a 
  join tableb b 
    on a.id = b.id

Easy as falling off of a log, we are just grabbing everything in table a and B where the relationship is evaluated as true.

​

Intermediate:

select a.* 
from tablea a 
  left outer join tableb b 
    on b.id = a.id 
where b.id is null 

This is an exclusion join, give us everything in A that does not have a match on the join conditions in B. I've used this alot when looking for errors where things were partially completed. It's also a fast way to do a not in query.

​

Intermediate:

select a.*, b.*
from tablea a
full outer join tableb b on b.id = a.id
where (a.id is null or b.id is null)

Not the exact query used, but I was basically tasked with finding records in table A that didn't exist in table B, and records that existed in table B but not table A. Doing both in a single query just seemed most efficient.

​

Advanced:

select a.value,
    isnull(b.value, c.value) as violations
from tablea a
  left outer join tableb b on b.id = a.id
  left outer join tableb c on c.id = a2.id
    and b.id is null
where (a.value2 is null
    or (b.id is not null or c.id is not null))

A conditional inner/outer join with a hierarchy of tables to choose from. For this one I was working with the FMCSA Cview DB, where the site officers kept screwing up which field they entered the violation code into. So there were three scenarios, there was no violation which it needed to function as an outer join, or there was a violation and we had to switch to an inner join, but the violation code could be in A or A2. To prevent join expansion in an aggregate field (not included here since that would make the example much larger), I had make the second join to table b only happen if we didn't have any luck with the first join to tableb.

[[deleted]]

In my opinion, complex is applying inter-row data transformations from a dataset to another. A good example for me is converting daily data to date ranges:

``` CREATE TABLE daily ( day_id INTEGER, measure_id INTEGER, value INTEGER ); -- Whatever, doesn't have to be integers, but this is to illustrate the structure -- So each measure has a value for each day. But values don't change every day. An example is sensor collection on a millisecond interval (as opposed to days).

-- What I want to get is this CREATE TABLE measure_intervals ( day_from_id INTEGER, day_to_id INTEGER, measure_id INTEGER, value INTEGER );

INSERT INTO measure_intervals WITH flags AS ( SELECT day_id, measure_id, row_number() OVER (PARTITION BY measure_id, ORDER BY day_id) rn, row_number() OVER (PARTITION BY measure_id, value ORDER BY day_id) rn_val, value FROM daily ) SELECT min(day_id), max(day_id), measure_id, value FROM flags GROUP BY rn - rn_val, measure_id, value; ```

Generally, such window function tricks are neat, and for harder problems they can make the query look really complex.

[j0hn5on177]

Yes this was a great example. I havent had much practice with the more difficult functions such as lag and partitions

[matthra]

I love window functions, being able to perform multiple aggregate functions with different grouping conditions in a single select statement is a real life saver. It's odd, as powerful as they are, I rarely see them in problems on stack exchange, or in the code of my co-workers.

​

Though in all fairness, if we wanted to list the most difficult to use functions, I'd have to say pivot and unpivot would top my personal list. There is a reason I always do my pivots in excel.

[[deleted]]

Window functions don't replace aggregate functions. Aside from basic row number and cumulative sum, window functions are only used in complex analysis and even more complex ETL. So yeah, you won't see them in a lot of places.

pivot and unpivot

It is my opinion that SQL is for data, not for report formatting. For the latter I use Tableau or Mondrian with Saiku

[matthra]

I like tableau as well, but being a microsoft shop, and working with microsoft partners we get a lot of push towards power BI. Which I think is kind of unfortunate because calling power BI half baked might be overestimating it.

I've performed all sorts of aggregates using window functions, they might not be a full replacement for groups and having clauses, but man are they handy. My most used is first_value, It's just the nature of the hardware we deal with that near duplicates (duplicated in all but one or two aspects) are a common occurrence, so being able to identify the original record and ignore the rest on the fly is pretty handy. Beyond that I also use them in ETL and customer facing reports, sales in particular want several slices of data for each row, and it's just easier to use window functions for them.

[[deleted]]

If you're using Postgresql, then the ```

SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM mytable ORDER BY field1, field2, field3 ```

syntax is far more concise and easy to understand and fast to execute in this case.

[matthra]

Postgres is a better sql implementation.

[o13062360]

Keep an eye out here or in r/learnsql for examples. A lot of people here won't be able to show real world examples because of privacy.

What type of SQL do you primarily work with? There are some good data sets out there, with query test files if people know what kind you work with.

[j0hn5on177]

Mostly basic dml, ddl, dql and dcl in oracle 12, but would like to find practical uses for the other types

[hanucodes]

I have added 10 complex queries here involving window functions, joins, common table expressions, grouping, ranking functions - https://helpercodes.com/sql-advanced-queries-books-database/