SQL Where — Getting At Your Dreams

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 — the order of which is undetermined; in no way guaranteed to reflect the order in which the data was inserted into the table.

The SQL Where clause restricts actions to those rows which satisfy a condition. The general form of SQL Where is:

SELECT column1, column2, … 
FROM table 
WHERE predicate;

Because SQL creates, reads, updates, and deletes (CRUD) database information, the WHERE predicate is used to corral its actions in SELECT, UPDATE, and DELETE statements.



Continue Reading

SQL Delete — All Good Things Must End

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 — which are brought into existence via the SQL Create Table statement. 

The SQL Delete statement is used to remove rows from data from the tables. It looks like:

DELETE FROM table WHERE condition ;

Be exceedingly careful around the WHERE clause; an error in restricting the scope of deletion can have shocking consequences. (Consider making a table backup with the SQL Create Table From statement.)



Continue Reading

SQL Create Table — Storing All The Things

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 — which are brought into existence via the SQL Create Table statement. When creating tables, each column specified may have two characteristics:

  • type — what kind of data can be held
  • constraint — restrictions on the data

This blog entry covers many of the common SQL data types and the constrains that may be placed upon them. Practical examples and source code is provided. Table structure may be changed after creation with the SQL Alter statement.



Continue Reading

Career Spotlight: Data Scientist vs Machine Learning Engineer

Woman interacting with computing touch-screen containing images about data science and machine learning.

You live in a world dominated by technology and information, a world where you can’t avoid being a tech user. Technology fields like artificial intelligence, data science, robotics, machine learning, and cybersecurity are interwoven with your contemporary life. 

Data science and machine learning engineering are two tech fields that can offer great salaries and entail fascinating work. Maybe you want to expand your career skill set to work with one of many interesting topics like machine learning for big data.

What do data scientists and machine learning engineers do? Read on to learn more.



Continue Reading

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

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

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