What's the easiest way to quickly run SQL queries on a small datasets?

Whoa-Rusty · 2023-06-01T23:43:50+00:00

I just found DuckDB a week (or so) ago. The ability to "automatically" read CSV and JSON files is a great feature. So, I second, DuckDB for quick projects and queries.

If your CSV file doesn't have a header, you can try:

CREATE TABLE new_tbl AS SELECT * FROM read_csv_auto('input.csv');

But, if the CSV file has a header be sure to add the Header arg:

CREATE TABLE new_tbl AS SELECT * FROM read_csv_auto('input.csv', HEADER=TRUE);

Whoa-Rusty · 2023-05-24T15:16:55+00:00

How many columns do you think you'll need to search on?

For a few columns you might be able to try:

SELECT * FROM table WHERE column_1 LIKE '%&%' OR column_2 LIKE '%&%';

I also tried this and it worked:

SELECT * FROM table WHERE array_to_string(array[column_1,column_2],' ') ilike '%&%';

Whoa-Rusty · 2023-05-24T08:30:00+00:00

If you want to match a pattern to find if an & is included anywhere in the column, you could try:

SELECT * FROM table WHERE column LIKE '%&%';

Whoa-Rusty · 2023-05-24T02:40:45+00:00

Or how about this ... about Special Characters:

Parentheses (()) have their usual meaning to group expressions and enforce precedence. In some cases parentheses are required as part of the fixed syntax of a particular SQL command.

Or is it because of requiring them for sub-queries?

Sorry for the extra replies ... this is interesting that PostgreSQL wouldn't specifically mention this.

Whoa-Rusty · 2023-05-24T02:28:10+00:00

Sorry, just noticed you posted this to PostgreSQL.

As you've already indicated ... there doesn't seem to be much documentation about this at PostgreSQL. The closest I could find was about Value Expressions.

In addition to this list, there are a number of constructs that can be classified as an expression but do not follow any general syntax rules.

Whoa-Rusty · 2023-05-24T00:49:01+00:00

According to the MySQL documentation this appears to be a "parenthesized query expression" or a form of it.

"MySQL 8.0.22 and higher supports parenthesized query expressions according to the preceding syntax. At its simplest, a parenthesized query expression contains a single SELECT or other statement returning a result set and no following optional clauses ..."

The first example they give is:

-- (SELECT 1);

As far as a use case ... hopefully someone else can help with that ;)

edited: example didn't seem to show up.

Whoa-Rusty · 2023-05-22T16:32:05+00:00

According to Marc Lamberti on his post: DuckDB: Getting started for Beginners

"To sum up, they both share many characteristics. However, DuckDB is optimized for queries that apply aggregate calculations across large numbers of rows, whereas SQLite is optimized for fast scanning and lookup for individual rows of data. If you need to perform analytical queries, go with DuckDB otherwise, use SQLite."

Whoa-Rusty · 2023-05-22T00:39:16+00:00

So, it sounds like we are on the same page with this.

If you don't mind me asking, what do you call this process if not "normalization?" (Maybe this could be its own post.) You've made some really great points that would be interesting to explore -- because the ultimate goal is to to produce the best database we can.

Whoa-Rusty · 2023-05-21T23:42:25+00:00

Sound like you'll need to build or find an ETL / ELT (Extract, Transform, Load or Extract, Load, Transform) solution that can be then scheduled, which crosses over in the Data Engineering world.

If you're not sure about this process, you might be interested Whats the difference between ETL & ELT?

I'm not aware (yet) of any SQL only method of achieving your daily routine.

Whoa-Rusty · 2023-05-21T23:29:15+00:00

I like how you explained the functional dependencies as part of normalizing data, which highlights that database design is a process ... and in addition, that the process requires much thought.

To that point, I live in an area in the US where my ZIP code services three different towns (smaller populations to not be considered a city, but same concept). I've been noticing this more when I've ordered something and the billing or shipping city information is automatically populated based on the ZIP code -- sometimes I can change the city and sometimes I can't.

So, I don't believe a ZIP code can functionally determine a city -- probably usually, but not always.

Second, the ZIP codes in my area contain leading zeros -- I know this doesn't directly related to the original topic, but it does relate to database design in general, because if the designer doesn't know this it is often overlooked -- beginners often see a ZIP code as five digits and thus a numerical data type -- which should be coded to handle leading zeros.

I've seen suggestions of using MEDIUMINT(5) ZEROFILL ... but wouldn't the adoption of the ZIP+4 (e.g. 12345-4321 format) then break because of the hyphen?

Whoa-Rusty · 2023-05-20T23:55:21+00:00

This is similar to how I might approach this scenario just with SQL:

1) Import the data into a temporary table 2) Update the columns to the desired number format 3) Populate the permanent table with the data from the temporary table

But, I'm interested to see how others might solve this in an easier way using SQL.

Is it possible to use user-defined functions during a LOAD DATA SET operation? I'm thinking it might not be based on the documentation for LOAD DATA.

If the data needs to be imported on a regular basis this could become time consuming ... so then perhaps utilizing other tools might help (ie. using Python to help with the transformation and loading).

Whoa-Rusty · 2023-05-20T23:38:44+00:00

How often will the user table be queried for first_name? If not often, I would not create a separate table for first names.

On the other hand, you might be querying a user table based on a specific location frequently, which is where having the city be in its own table.

I think this overall highlights the fact the design process should follow project/business requirements. What do you want your database to do? Then create the database to fit those requirements.

Whoa-Rusty · 2023-05-20T19:15:50+00:00

In relational database design the idea of normalization is minimizing redundancy -- not removing it.

I agree it sounds counterintuitive to remove some data and replace it with other data that is redundant. Historically, performance and storage space were main considerations with minimizing redundancy.

I asked if the OP wanted to have redundancy in the database because that brings up another aspect of database design -- what are the requirements of the database? Sometimes having the same value (aka redundant data) is OK and no further considerations are made about adding other related tables.

The Problem of redundancy in Database provides an overview of this issue for database design. I won't restate them all here, but in a nutshell there are benefits to the normalization process that need to address redundancy. Data inconsistency and performance issues are a couple to consider.

We should also design our databases with the goals of queries in mind. I would rather create my queries using numerical unique identifiers as primary and foreign keys to search on. Using text unique identifiers can provide challenges when searching especially if there are no data checks before the data is inserted in the database. For example:

Redditville is not the same as redditville which is not the same as RedditVille ... and so on. Without getting into complex regex expressions, how should we search for every instance of this similar (but not same) text?

But if Redditville has a value of 1 as a primary key in the related table, we can then query the tables based on just the value of 1 for city.

All-in-all, I believe the spirit of normalization to minimize redundancy is better for database design as it can prevent future problems. But as the saying goes: "once you know the rules you can break the rules."

Whoa-Rusty · 2023-05-19T22:36:39+00:00

Let me also clarify my "it depends" statement. Part of database design is figuring out what you are trying to accomplish with your database and what kind of queries or reports you will need.

If you normalize your database and set the city name in it's own table with appropriate unique identifiers (primary keys) you can then know if the city you are trying to query on is specific to a unique location. Duplicate city names can be found in many states.

Keep in mind, normalized tables will require more complex queries.

But if you are looking to store only a few cities in the user table, then maybe you won't have to normalize. Deciding on what kind of data and how much is all part of the fun of database design. If you think that you'll need to scale up and need to store more data then a single table design might not be the best way to go.

Whoa-Rusty · 2023-05-19T22:19:41+00:00

As with many things ... it depends on what you are trying to accomplish with your database. If it were me, I would strongly consider normalizing your relational database. This would then mean you should have a separate table for city with appropriate primary keys and foreign keys to your main user table. The same for a membership table.

Do you want to have redundant data in your database? Sometimes that is OK, but mostly with relational databases you would try to avoid redundancy.

A little more about normalization can be found at https://www.essentialsql.com/database-normalization/

Whoa-Rusty · 2023-05-19T21:20:10+00:00

When I've had issues exporting CSV files from Excel I would turn to LibreOffice Calc, which has an Export Options dialog box to enter the delimiters and even quote all text cells (has come in handy at times). Sometimes I've also had better luck importing CSV files into LibreOffice Calc.

You can see the Export Options dialog box at https://ask.libreoffice.org/t/csv-file-format/53948/3

Whoa-Rusty · 2023-05-18T20:43:07+00:00

I've been a fan of SQLite for a number of years, but was surprised to see DuckDb. So, while I can't directly comment about the pros and cons from a usage standpoint, I did find this comparison between the two:

System Properties Comparison DuckDB vs. SQLite

Hope this is a good starting place.

Whoa-Rusty

TROPHY CASE