Promoted SQL's set-based advantages

[johnthomaswright]

SQL's set-based syntax is different for those who have only been exposed to iterative programming languages, particularly those reading and writing to delimited text files. SQL creates more concise, self-describing ways to model, relate, and aggregate data.

[johnthomaswright]

You’re absolutely right, and I think the subtlety of a set as unordered conveys. SQL illustrates set-based design because you provide no basis for the order in which rows are searched, filtered, and joined – only specifying the order of end results through the ORDER BY clause (in a pure, no-hints query like 99% of all queries where you don’t tip off the execution plan engine – definitely not a fruitful 1% topic for digression in Carpentries...!)

https://www.itprotoday.com/analytics-reporting/programming-sql-set-based-way

The referenced article provides some great examples. Let me know if this makes more sense as a way to think about things in sets and not in iterative/programmatic loops. Thanks!

John Wright Manager, IT Clinical & Research Architecture 207-288-6504 t | 207-691-3433 m [email protected]mailto:[email protected]

The Jackson Laboratory Bar Harbor, ME | Farmington, CT | Sacramento, CA www.jax.orghttp://www.jax.org/

The Jackson Laboratory: Leading the search for tomorrow's cures

From: Remi Rampin [mailto:[email protected]] Sent: Friday, October 26, 2018 1:55 PM To: swcarpentry/sql-novice-survey [email protected] Cc: John Wright [email protected]; Mention [email protected] Subject: Re: [swcarpentry/sql-novice-survey] Promoted SQL's set-based advantages (#268)

@remram44 requested changes on this pull request.

Hi @johnthomaswrighthttps://github.com/johnthomaswright, thanks for the contribution! I'm with you, I think it makes sense to make it clear what SQL is about. However I am not sure about "set-based language".

---

In _episodes/01-select.mdhttps://github.com/swcarpentry/sql-novice-survey/pull/268#discussion_r227114664:

@@ -12,6 +12,7 @@ keypoints:

• "A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records."

• "A database manager is a program that manipulates information stored in a database."

• "We write queries in a specialized language called SQL to extract information from databases."

+- "SQL is a set-based language, where we specify what data and format to return or save; but not how to save it or how to retrieve it (like you would specify in iterative loops in other languages.)

Maybe "query-based" language instead? "set" usually refers to unordered collections.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/swcarpentry/sql-novice-survey/pull/268#pullrequestreview-167080548, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AksQtgn7xu8rhpdWmzG8U89nc6DbR4VEks5uo0yMgaJpZM4Xz9gP.

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.

[henrykironde]

How about something like SQL is a query-based language, where we specify what data and format to return or save; and it's the work of the database manager to plan on how to save the data or how to retrieve the data.

[johnthomaswright]

Very good feedback. The reason for this pull request has to do with my lecturing experience at U. of Florida from years ago and teaching interns and employees who didn’t take database classes. Two common (anti-)patterns I saw were writing inline queries to grab a single row in joins, which essentially created a fetch loop in the DBMS and destroyed performance, and grabbing individual rows one at a time in a parameterized loop.

If we instead think about how to specify each set and how to join those sets, we’re maximizing the utility and differentiation of SQL when compared even to iterators and lambda/anonymous functions. This also helps people appreciate how to avoid, or expect, having multiplicity when more than 1 row matches on a join. That’s where I saw procedural fallbacks to forced iteration, poor use of cursors, and extremely messy inline queries.

Maybe some compare/contrast examples would be more time-effective and practical than trying to acknowledge sets and relational algebra principles?

[zkamvar]

Thank you for your contribution. This lesson has migrated to use The Carpentries Workbench, but unfortunately, due to various factors, the Maintainers of this lesson were unable to address this pull request before the transition. Because of this, we had to close your pull request.

Please note that this does not mean that your contribution was not valued. There are many reasons why a pull request is not merged. It's important to remember that the Maintainers are first and foremost people---people who maintain this lesson on a voluntary basis. Sometimes pull requests become stale because other responsibilities take precedence. Thank you for taking the time to open the pull request in the first place.

If you wish to contribute again, you will need to delete and re-fork your repository.

How to contribute

If you wish to contribute, you will need to use the following steps to delete, re-fork, and re-create your pull request (aka the burn it all down strategy):

:video_camera: How to update (delete) your fork https://carpentries.github.io/workbench/faq.html#update-fork-from-styles

1. Save your edits on locally or in a scratch space.
2. Delete your fork
3. Create a new fork or use the "edit" button on the page you wish to edit.
4. Apply your changes (NOTE The Workbench uses a different syntax. Here is a Transition Guide from Styles to Workbench for your reference).

Questions

If you have any questions or would like assistance, please contact @core-team-curriculum (email: [email protected]) or you can respond to this message.