sql

SQL joins as Venn diagrams alvin March 20, 2019 - 1:06am

I saw this “SQL joins as Venn diagrams” image on this Twitter page. To give attribution to the original author, it appears to have been created by C.L. Moffatt and documented in this Visual Representation of SQL Joins article. However, this article makes the case that Venn diagrams are not an accurate description of SQL joins.

A Play Framework Anorm SQL SELECT query that queries for a single value alvin October 23, 2018 - 7:39pm

As a quick note today, here’s an example Play Framework Anorm SQL SELECT query that queries for a single value:

A ScalikeJdbc SQL SELECT example that maps results to a list of case class instances alvin May 15, 2018 - 6:40pm

Here’s another ScalikeJdbc example. The main benefits of this example are:

  • It shows how to perform a SQL SELECT with ScalikeJdbc
  • How to map the ResultSet values to create a series of objects from the ResultSet
  • How to connect to a database with ScalikeJdbc using a JDBC style of connecting (as opposed to putting the JDBC parameters in a configuration file)

Here’s the ScalikeJdbc example code:

A ScalikeJdbc SQL UPDATE syntax example alvin May 15, 2018 - 6:36pm

Here’s an example of how to write a SQL UPDATE query with ScalikeJdbc:

def updateUrlClickGeoInfo(urlClickId: Long, gi: GeoInfo): Int = NamedDB('kbhr) localTx { implicit session =>
    sql"""
    update url_clicks
    set city = ${gi.city}, state = ${gi.state}, country = ${gi.country}
    where id = ${urlClickId}
    """.update.apply()
}

(As a note to self, this code comes from my PopulateKbhrGeo1 app.)

A ScalikeJdbc SQL SELECT example with WrappedResultSet alvin May 15, 2018 - 6:29pm

Here’s another Scala ScalikeJdbc SQL SELECT example. A few unique things about this example is that it shows the JDBC-style parameters; it shows how to use a case class and the “service” style approach; and it shows how to use the WrappedResultSet as shown. Here’s the code:

A ScalikeJdbc SQL SELECT example with JDBC-style parameters alvin May 15, 2018 - 6:25pm

Here’s another ScalikeJdbc SQL SELECT example. If I remember right, this one doesn’t rely on a configuration file, it just uses the JDBC parameters shown:

A ScalikeJdbc SQL SELECT “service” example alvin May 15, 2018 - 6:17pm

Here’s another ScalikeJdbc SQL SELECT query example. In this example I use the concept of a “service,” which I probably originally got from the ScalikeJdbc website:

A simple ScalikeJdbc SQL SELECT example alvin May 15, 2018 - 6:06pm

I don’t have time to write about ScalikeJdbc right now, so I’ll just say that it’s been working well for me. Here’s one of the first test examples I created, a simple SQL SELECT query that prints some output from a database table:

A SQL select group by, order by, and count query alvin May 13, 2018 - 12:39pm

As a note to self, this is how I wrote a “group by” and “order by” SQL query that gives me the number of times each country code occurs in a database table named url_clicks:

select country, count(1) as the_count
from url_clicks
where url_id=6
and country != ''
group by country
order by the_count desc

Results of this query look like this:

US, 50
CA, 30
GB, 15

That tells me that 50 people in the US clicked on the link with the id=6, 30 people from CA clicked on the same link, and 15 people from GB clicked on that link as well.

How to get the count of detail rows in a master/detail SQL SELECT query and subquery alvin April 28, 2018 - 4:03pm

As a brief note to self, I just used this SQL SELECT query to show both (a) master/summary information about each URL row in the urls database table, and also (b) detail information in the form of the number of rows in the url_clicks database table for each row in the urls table: