Category: Data Science

Data Science, SQL

SQL Distinct — Getting the Basics Down

Relational databases store information in tables — columns that are analogous to elements in a data structure and rows which are one instance of that data structure. In those cases where this data set contains duplicate values (like membership location by state or province) SQL Distinct traverses these data, filters them, and returns just one of each duplicated value (making it easy to determine, for example, the number of members living in each state). SQL Distinct evaluates the span of a particular set of values.

Starting With SQL Distinct

We start with a table of some of Charles Dickens’ characters and the novels in which they appear. This table is imagined not as an exhaustive index of every character but those most worth discovering; it’s a table of the most notable and memorable characters in Dickens’ oeuvre.



Continue Reading
Data Science, SQL

SQL Alter — Changing Your Mind

SQL Alter modifies existing columns and constraints in  relational database tables.

A well-designed set of tables is the foundation of any database application. As time passes, however, initial assumptions are refined, additional scope is added to the project, and new understanding of the problem domain necessitate changing the table schema. Rather than deleting live production tables with a data migration process, the SQL Alter statement modifies the existing column asd constraints; generally a less invasive adjustment.

SQL Alter can change tables by adding and deleting columns, converting between (casting) data types, deleting (dropping) tables, and — for some database — renaming tables. The syntax of SQL Alter varies between commercial database vendors; check documentation specific to your type and version of database. Significant syntax variations are noted below.



Continue Reading
Data Science, SQL

SQL Group By — Refining Your Queries

Relational databases store information in tables, with columns that are analogous to elements in a data structure and rows which are one instance of that data structure. SQL Select retrieves those rows that satisfy the search criteria.

SQL Group By — along with Having and Order By — gives you the power to fine-tune the results, making it easier to deliver actionable data directly, without further massaging before sharing.



Continue Reading
Data Science, SQL

SQL Tutorial — Database Programming

SQL (Structured Query Language) — pronounced “sequel” or “ess queue ell” — is a computer programming language tailored to interacting with data stored in relational databases. SQL provides all the necessary tools to create, read, update, and delete (CRUD) that data.

This SQL tutorial shows how you can execute “SQL queries” — tailored requests for information — from virtually any database (including MySQL, SQLite, Apache Presto, Firebird SQL, Google BigQuery, Oracle, Microsoft SQL Server, Sybase, SAP HANA, IBM DB2, and many others).



Continue Reading
Data Science, SQL

An Introduction to SQL Case

The key to unlocking the power of computer programming is conditional flow control, the if-then-else constructs which cause the program to perform separate actions depending upon the evaluation of some Boolean (true or false) assertion. SQL has its own conditional flow control; the SQL Case statement.



Continue Reading