SQL — Structured Query Language — Hub

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 hub lists all the Udacity blog posts which cover SQL. Armed with this information you can craft “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

SQL Between — Limiting Rows Returned

Relational databases store information in tables — in columns that describe aspects of an item and rows which tie together the columns. The SQL Between clause modifies a SQL Select statement by filtering values between a top and bottom boundary, which may be numbers, text, dates. The SQL Between operator is inclusive, that is to say, begin and end values are included.

The syntax of the SQL Between clause is:

SELECT column1, column2, … 
FROM table 
WHERE columnX BETWEEN value1 and value2 ;


Continue Reading

SQL Top Limit Fetch RowNum — Too Much Of A Good Thing

Relational databases store information in tables — tables with columns analogous to elements in a data structure and rows which are an instance of that data structure. We sometimes need to limit the number of returned rows. SQL Top, SQL Limit, SQL Fetch, and SQL RowNum (the exact syntax varies by database) are our tools in extracting the exact data we want.

In contrast to the usual behavior of the SQL Select statement, returning all relevant data, the limit function in SQL — expressed as one of Top, Limit, Fetch, or Rownum — provides a mechanism for limiting the data returned to either an absolute number or percentage of the rows. Limiting returned rows to bite-sized manageable chunks drastically reduces the storage and processing overhead requirements on software that consumes database data, resulting in a faster and more reliable code.

Rows are stored in databases at random. One adds certainty to which rows are retrieved by crafting the SQL Select statement with a winnowing WHERE clause and an ORDER BY clause. The techniques in this blog entry show how to retrieve a subset of those nicely-ordered rows, or how to iterate through those subsets across the entire results set.



Continue Reading

An Introduction to Relational Databases and SQL

The term “databases” has filtered through to the common consciousness, from police procedurals where characters search through national crime databases to frequent news stories about database breaches exposing our personal information. Learning about databases is important to better reframe the news, to understand the capabilities available to you on your phone and at work. SQL, the language used to access stored data, is important to have a basic understanding in order to speak the language of techies and to demystify the workings of databases behind web pages and on our company services.

This blog post covers the basics and provides references for further exploration.



Continue Reading

SQL Aggregate Functions — AVG, COUNT, DISTINCT, MAX, MIN, SUM

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 SQL language is used to interact with that database information.

The SQL aggregate functions — AVG, COUNT, DISTINCT, MAX, MIN, SUM — all return a value computed or derived from one column’s values, after discarding any NULL values. The syntax of all these functions is:

SELECT AGGREGATE_FUNCTION( 'column_name' )
FROM TABLE_NAME

The aggregate functions are:

functionreturns
AVG()the mean average of the elements in the column
COUNT()the total number of elements in the column
DISTINCT()the number of distinct values across the column
MAX()the largest-value element in the column
MIN()the smallest-value element in the column
SUM()the arithmetic total of all values in the column


Continue Reading

SQL Injection — All Your Data Are Ours

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 SQL language is used to interact with that database information.

SQL injection refers to programming laziness when dealing with processing SQL allows clever attackers to manipulate HTML forms to “poison” SQL to subvert security measures and open up all your data to their scrutiny. There are few circumstances more terrifying than discovering your precious intellectual property, customer information including credit card details, and more are being perused by your competition or being sold on the dark web.

Herein we cover the basics of SQL injection, a huge topic with decades of background, to provide the first steps in understanding the importance of properly handling your SQL, the techniques used by your adversaries (even if you didn’t realize you had adversaries), and starting points to armoring your database.

The SQL injection attack works on “poisoning” dynamic SQL statements to cause a behavior other than what the programmers intended. A “dynamic statement” is one that’s generated at run-time using parameters passed in from a web form or URI query string.



Continue Reading