How to Learn SQL

Starting out on how to learn SQL is relatively easy nowadays. Many resources are easy to find with a web search if you want to learn SQL. Most RDBMS providers share documentation on their SQL dialect which often includes tutorials. SQL is very standardized, but most providers have proprietary extensions you need to be aware of. Using such extensions makes migrations complex. The areas you find the most differences are in the procedural language and advanced functions. Vendor-specific extensions are usually not well documented because they like to retain customers for as long as possible.

Why Learn SQL?

SQL has been in use since the 1980s, and there is no likely successor in the foreseeable future, so it is not a skill set that will become obsolete during your career. Relational databases are the most common DBMS on the market. The RDBMS has evolved for decades to adapt to new data types, data formats, platforms, and access methods, making SQL skills very relevant. SQL is a skill that is valued across many disciplines, including Software Development, IT Administration, Marketing Automation and Business Intelligence functions. This versatility makes SQL a transferable and sought-after skill. As Business Intelligence becomes more democratized, being used beyond IT, SQL can be considered a business and IT skill.

Who Needs to Learn SQL?

Many business roles need SQL skills. Technical roles such as Software Development and Database Administrators (DBA) must have SQL skills to be effective. Functions such as Sales Operations who extensively use reporting in tools such as Salesforce find that knowing SQL makes reporting much easier because, under the covers, Salesforce uses a lot of SQL queries. Business Analysts working for executives or within lines of business make extensive use of BI tools such as Qlik Sense, Tableau and Power BI to visualize data to show business performance. IT Admins using products such as System Center must know SQL as SQL Server is the embedded management database.

What Do You Need to Learn SQL?

SQL is used to manage data that is stored in a set of related tables. Relational Algebra is the basis of interaction at a low level within the database. As a user of SQL, it is very useful to visualize the database as a set of related tables consisting of rows and columns. Visualizing relationships using a Venn Diagram can be helpful. Understanding data modeling is an excellent prerequisite to learning SQL. This helps you understand why the data is structured the way it is. SQL is not as hard to learn as a traditional programming language because it is declarative. You are simply telling the database what data you want, and it takes care of how to get it.

If you want to learn how an RDBMS functions, many offer the option of viewing query execution plans or query profilers. These query plans show you things such as when a full table scan is done because a table lacks a suitable index or it is too small to bother indexing and how intermediate result sets are merged and sorted when a range scan is done. Most people only worry about these things if their queries are running slowly or IT complains about inefficient use of resources, which can be expensive if using an external cloud service that charges for CPU cycles.

How Long Does it Take to Learn SQL?

Basic SQL skills can be learned in days. Complex joins and nested queries can take weeks to learn. Database tuning can take years to master. Many modern enterprise-class database systems offer tools that make tuning much more straightforward. Many modern cloud database services offer elastic scaling to automate parallelization of SQL operations to keep response times short, even if you have to touch terabytes of data.

Where Can I Learn It?

A popular starting point for learning SQL is Code Academy.

Codecademy is very intuitive, teaching you SQL syntax and the ability to try it for yourself. It is also an excellent resource for mapping career paths to required classes for specific paths.

There are additional options to specify referential actions, sequences and partitioning, which are described at the Actian Documentation website.

If you want a simple-to-use tool to learn different flavours of SQL by vendor, then W3 School is a great resource.

What SQL to Learn

The aspects of SQL you need to learn depend on your job function. If your role involves data analysis, then learning all the variations of the SELECT statement is the best starting point. Then move on to understanding views and indexes. If you have the permission needed to create objects, then experimenting with your own schema can be very useful.

If your role is in Software Development, you need to learn database design before diving into SQL. You may need specialist skills in writing user-defined functions and SQL procedures so you can store and execute application code close to the data it is operating on.

As a DBA, you will need to start with a Database Administration class. This class teaches you to create a database, add users, load data, and recover a backup.

The description of the SELECT statement below is intended to show the power and scope of this query statement.

If, for example, you want to know who Customer 10 is in the CUSTOMERS table. You can use the statement below:

SELECT CUSTOMER-NAME FROM CUSTOMERS WHERE CUSTOMER-ID =10;

If the table contains a STATE column, the following SQL statement will retrieve just the customers in Colorado:

SELECT * FROM CUSTOMERS WHERE STATE = 'CO';

Multiple tables can be related to each other using sharing common columns. These columns can be optionally indexed to allow for faster access.
Tables can be logically joined at execution time, and the nature of the join can be expressed in SQL.

The following are the types of joins where left and right are the order of the tables named in the SELECT statement:

• INNER JOIN: This join returns those records which have matching values in both the tables.
• FULL JOIN: This join returns all those records which either have a match in the left or the right table.
• LEFT JOIN: This type of join returns records from the left table, along with those records which satisfy the condition from the right table.
• RIGHT JOIN: This kind of join returns records from the right table, along with those records which satisfy the condition from the left table.

Tables can be related through primary and foreign key column relationships. For example, a customer table might have a primary key of Customer-ID. An Orders table containing a Customer-ID column will be known as a foreign key in that table.

Primary keys are usually indexed and contain unique values.

Applications use SQL to retrieve and insert data from databases, including distributed databases.

The SELECT statement can contain aggregation functions, such as:

• COUNT() – returns the total number of rows that satisfy the criteria in the statement
• MIN() – returns the smallest value in the result set
• MAX() – returns the largest value in the result set
• AVG() – returns the average of the values in the result set
• GROUP BY – will group the results by a given column value
• ORDER BY – will sort the results by the specified column

Comments in SQL help with understanding and maintaining applications. Below are single and multi-line examples of comments:

— This is my comment

/* This is
multiple line
comment */

It helps to visualize a database schema using a Venn Diagram, especially when using set operators such as UNION, INTERSECT and EXCEPT in SELECT statements.

• UNION – This operator is used to combine the result-set of two or more SELECT statements.
  Example:

SELECT Columns FROM Table1
UNION
SELECT Columns FROM Table2;

• INTERSECT – This clause is used to combine two SELECT statements and return the intersection of the data sets of both the SELECT statements.
  Example:

SELECT Column1 , Column2 ....
FROM TableName
WHERE Condition
INTERSECT
SELECT Column1, Column2 ....
FROM TableName
WHERE Condition

• EXCEPT – This operator returns those rows that are returned by the first SELECT operation and are not returned by the second SELECT operation.
  Example:

SELECT ColumnName
FROM TableName
EXCEPT
SELECT ColumnName
FROM TableName;

Case expressions provide a decoding capability that allows one expression to be transformed into another. Case expressions can appear anywhere that other forms of expressions can be used.

There are two forms of case expressions:

• Simple
• Searched

The syntax for a simple case expression is as follows:

CASE expr WHEN expr1 THEN expr2 WHEN expr3 THEN expr4... [ELSE exprn] END

The initial case expression is compared in turn to the expressions in each WHEN clause. The result of the case is the expression from the THEN clause corresponding to the first WHEN clause, whose expression is equal to the case expression. If none of the WHEN expressions match the case expression, the result is the value of the expression from the ELSE clause. If there is no ELSE clause, the result is the null value.

The syntax for the searched case expression is as follows:

CASE WHEN search_conditon1 THEN expr1 WHEN search_expression2 THEN expr2...[ELSE exprn] END

The search conditions of each WHEN clause are evaluated in turn. The result of the case is the expression from the THEN clause corresponding to the first WHEN clause whose search condition evaluates to true. If none of the WHEN clause search conditions evaluate as true, the result is the value of the expression from the ELSE clause. If there is no ELSE clause, the result is the null value.

Beyond the SELECT Statement

Once you have mastered the SELECT statement, the SQL statements below allow you to create and modify objects contained in the database:

Below are some examples of SQL statements:

• CREATE TABLE – is used to create a table object
• INSERT – is used to insert new rows into a table
• UPDATE – is used to make changes to data contained in a database table
• DELETE – is used to remove rows of data
• GRANT – is used to give users the permission to view or modify database objects such as tables
• REVOKE – is used to remove permissions from users or groups of users
• ALTER – allows the user to add or remove columns from an object to update permissions
• DROP TABLE – deletes a table
• CREATE INDEX – will create an INDEX to allow for efficient access to data using a key-value
• TRUNCATE TABLE – allows the user to quickly empty the contents of a table

The most complex of the above statements listed above is CREATE TABLE. This is explained in more detail below:

Creating a Table

The CREATE TABLE statement syntax can be as simple as the example below:

CREATE TABLE DEPT (Dept-ID int, Dept-Name varchar(255), Dept-Cost-Center int );

You can create tables using another table as a template. For example, the table below only contains rows that relate to Department-ID 10:

CREATE TABLE DEPARTMENT-10 AS SELECT * FROM DEPARTMENTS WHERE Department-ID = 10;

The following CREATE TABLE syntax is for an Actian database, which has the following format:

CREATE TABLE [schema.]table_name (column_specification {, column_specification })
[table_constraint {, table_constraint}] [with_clause]

• table_name – Defines the name of the new table.
• column_specification – Defines the characteristics of the column.
• table_constraint – Specifies the table-level constraint as described in Table-level and Column-level Constraints.
• with_clause – Specifies WITH clause options separated by a comma. For more information, see WITH Clause.

Column Specification

The column specification in a CREATE TABLE statement defines the characteristics of a column in the table.

The column_specification has the following format: column_name datatype

[[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT] [WITH NULL | NOT NULL] [MASKED [AS {BASIC | NULL | 0 | ' ' }] [GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)] | GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]] [[CONSTRAINT constraint_name] column_constraint { [CONSTRAINT constraint_name] column_constraint}]

• column_name – Assigns a valid name (see Object Naming Rules) to the column.
• datatype – Assigns a valid data type to the column.
• DEFAULT clause – Specifies whether the column is mandatory.
• WITH NULL | NOT NULL – Specifies whether the column accept nulls:
  • WITH NULL – (Default) Indicates that the column accepts nulls. If no value is supplied by the user, null is inserted.
  • NOT NULL – Indicates that the column does not accept nulls.
• [MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }] – Displays the column with the specified mask characteristic. The mask characteristic defines how to display the masked data:
• BASIC – Fills the width of the column with asterisks
• NULL – NULL
• 0 – 0
• ‘ ‘ – blank

GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)] | GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]

Indicates the column is an IDENTITY Column. The column must be defined as integer or bigint.

[CONSTRAINT constraint_name] column_constraint

Specifies checks to be performed on the contents of the column to ensure appropriate data values.

DEFAULT Clause – The WITH|NOT DEFAULT clause in the column specification specifies whether a column requires an entry.

This clause has the following format:

[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT

[WITH] DEFAULT default_spec – Indicates that if no value is provided (because none is required), Actian inserts the default value. The default value must be compatible with the data type of the column.

For character columns, valid default values include the constants: USER, CURRENT_USER, and SYSTEM_USER.

For boolean columns, valid default values include FALSE or TRUE.

WITH DEFAULT – Indicates that if no value is provided, Actian inserts 0 for numeric and money columns, an empty string for character columns, the current date for ANSI date columns, and the current timestamp for timestamp columns.

NOT DEFAULT – Indicates the column is mandatory (requires an entry).

The following is an example of using the DEFAULT clause:

CREATE TABLE DEPT(dname CHAR(10),
    location   CHAR(10)  DEFAULT 'CO',
    creation   DATE      DEFAULT '01/01/22',
    budget     MONEY     DEFAULT 10000);

Constraints

To ensure that the contents of columns fulfill your database requirements, specify constraints.

Constraints can be specified for individual columns or the entire table. For more information, see Table-level and Column-level Constraints.

The types of constraints are:

• Unique constraint – Ensures that a value appears in a column only once. Unique constraints are specified using the UNIQUE option.
• Referential constraint – Ensures that a value assigned to a column appears in a corresponding column in another table. Referential constraints are specified using the REFERENCES option.
• Primary key constraint – Declares one or more columns for use in referential constraints in other tables. Primary keys must be unique.

Table-level and Column-level Constraints

Constraints can be specified for groups of columns as part of the table definition (table-level constraints) or for individual columns as part of the column specification (column-level constraints).

The constraint has the following syntax:

[CONSTRAINT constraint_name] constraint

• constraint_name – Defines a name for the constraint. If the name is omitted, Actian Data Platform assigns one. The constraint name is used when dropping the constraint using the ALTER TABLE statement.
• constraint – Is either a table-level constraint (table_constraint) or a column-level constraint (column_constraint).

table_constraint is one or more of the following:

UNIQUE (column_name {, column_name}) PRIMARY KEY (column_name {, column_name}) REFERENCES [schema.]table_name [(column_name {, column_name})] [enforce_option] [referential_actions]

column_constraint is one or more of the following:

• UNIQUE
• PRIMARY KEY
• FOREIGN KEY (column_name {, column_name})
• REFERENCES [schema.]table_name[(column_name)] [enforce_option] [referential_actions]

where:

• enforce_option – Specifies whether constraints are enforced. Valid values are:
  • NOT ENFORCED – Does not enforce the constraint when it is defined or when the table is updated. Indexes are not created for NOT ENFORCED constraints.
  • ENFORCED – (Default) Enforces the constraint.

There are additional options to specify referential actions, sequences and partitioning, which are described at the Actian Documentation website.