In relational databases, the SQL ORDER BY statement is a powerful tool for sorting data in a table. The statement is used to arrange queried data into rows in a specific order, based upon one or more columns. In this database programming tutorial, we will learn how to use the SQL ORDER BY statement to sort retrieved data in ascending or descending order.

Read: Best Courses for Database Administrators

SQL ORDER BY Statement Syntax

The syntax for the SQL ORDER BY statement is shown in the following example code:

SELECT columnA, columnB, columnC
FROM table_example
ORDER BY columnA, columnB, columnC ASC|DESC;

In the example code above, the SELECT statement is used to first select the columns to be displayed from a given table. Then, the FROM statement defines the table name the data will be pulled from. The ORDER BY statement then sorts the retrieved data in either ascending or descending order, based on one (or more) columns.

They keywords ASC and DESC are optional parameters of ORDER BY, which dictate the order of the sort. ASC – which is short for ascending – is the default sort order, while DESC – which is short for descending – sorts the data in the reverse order.

How to Sort Data in Ascending Order in SQL

In SQL databases, database developers can sort data in ascending order using the SQL ORDER BY statement and the optional parameter (discussed above) ASC, alongside the column (or columns) you want to sort by.

For instance, if a programmer or database administrator has a table named employees that has the columns: employee_id, first_name last_name, and hire_date. If we wanted to sort the queried data by ascending order by the last_name column, we could use the following example SQL query:

SELECT * FROM employees
ORDER BY last_name ASC;

The above query chooses every column from the employees table. It then sorts the data in ascending order by the last_name column.

How to Sort Data in Descending Order in SQL

Sorting data in descending order in SQL is possible as well, also using the SQL ORDER BY statement. All database developers need to do is add the DESC keyword after the column (or columns) you intend to sort by. As an example, if you want to sort the employees table by descending order using the last_name column, you would use the following SQL query:

SELECT * FROM employees
ORDER BY last_name DESC;

The above query retrieves all columns from the employees table and then sorts the data by last_name in descending order.

How to Sort by Multiple Columns in SQL Using ORDER BY

Database administrators and database programmers can also sort by multiple columns using the SQL ORDER BY statement. To do this, all you need to do is note which columns you want to sort by in the ORDER BY clause, using commas as separators. The retrieved data gets sorted first by the first column, and then using the second column. If there are more than two sort clauses, the sort continues in order as expected. For instance, if we wanted to sort our employees table by the last_name column, and then using the first_name column, we would use the SQL query below:

SELECT * FROM employees
ORDER BY last_name ASC, first_name ASC;

The above SQL query retrieves every column from the employees table and then sorts the information by ascending order using the last_name column. In cases where more than one employee has the same last name, then the returned data will then be sorted in ascending order by the first_name column.

Read: Project Management Software for Database Developers

How to Sort Data by Calculated Columns in SQL

Database developers and db admins can also sort data by columns in a table using calculated columns. Calculated columns are columns that are created when we use a calculation or equation on one or more existing columns in a table. For example, in our table employees if we wanted to sort by the sum of the salary and bonus columns, in ascending order, we could use the following SQL query:

SELECT *, salary + bonus as total_income FROM employees
ORDER BY total_income ASC;

The above SQL query chooses all columns from the employees table and then creates a calculated column named total_income by using the sum of salary and bonus. The returned data is then sorted in ascending order courtesy of the total_income column.

How to Sort Data by Alias Names in SQL with ORDER BY

There are some instances where database programmers might need to sort data using an alias name that we define in our SELECT statement. To achieve this, put the alias name in the ORDER BY clause.

As an example, using our employee table let’s say we wanted to sort our data using a total_income table we set as an alias name, by descending order, we can use the following SQL query and statement:

SELECT *, salary + bonus as total_income FROM employees
ORDER BY total_income DESC;

Our example query works by selecting every column from our employees table and creates a new calculated column named total_income (our alias named column) by adding together the salary and bonus columns. This data is then sorted in descending order using our new total_income column.

Sorting Data by NULL Values

Sometimes you need to sort data in a relational database that contains a NULL value; querying this sort of data can cause problems if the database developer does not account for this possibility. The reason for this is that NULL is not technically a value, but, instead, a placeholder representing the absence of a value. If you are sorting data containing NULL, they are typically sorted at either the beginning or end of the results, depending on how you set the sort order.

Developers can specify where NULL values are sorted using the NULLS FIRST or NULLS LAST keywords in the ORDER BY clause. Let’s say you wanted to sort the employees” table in ascending order using the manager_id column, with NULL values sorted last. To do so, we could use the following SQL query:

SELECT * FROM employees
ORDER BY manager_id ASC NULLS LAST;

The query above selects all columns from the employees table, then sorts the data in ascending order by the manager_id column, sorting the NULL values last.

You can learn more about NULL values in our tutorial: Working with NULL Values in SQL.

Final Thoughts on SQL ORDER BY

The SQL ORDER BY statement is used to sort data in a relational database. Using it, database developers and database administrators can sort data in ascending or descending order, using one or more columns, calculated columns, or alias names. You can also specify where NULL values should be sorted in the results, avoiding some common query errors caused by NULL values. Understanding how to use the ORDER BY statement in SQL lets programmers and admins manipulate data to create more robust queries and make your relational database more efficient.

The post SQL SORT BY Statement appeared first on Database Journal.

Read: Best Online Courses to Learn SQL

What is a NULL Value in SQL?

In relational databases and RDBMS’, NULL values are a special value in SQL that are used to represent an unknown – or in some instances, a missing – value in a column or cell. A lot of newer database developers think that NULL is the same as a zero value integer or an empty string; however, in reality NULL is a value representing the absence of any value. You should note that NULL values are not equal to any other value, including other NULL values.

For database administrators and programmers working with NULL values in SQL, it is important to understand how this unique value is handled in different situations. For instance, when performing mathematical calculations, NULL values always return a NULL value. Because of this, we need to handle NULL values properly in order to avoid errors in calculations and our SQL queries.

How to Create a Table with NULL Values in SQL

To create a table in SQL, you first specify which columns will allow NULL values. By default, every column will allow NULL values unless programmers say otherwise.

The SQL syntax and statement below shows how to create a table containing NULL values:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone_number VARCHAR(20) NULL,
  hire_date DATE,
  job_id INT,
  salary DECIMAL(10,2) NULL,
  commission_pct DECIMAL(4,2) NULL,
  manager_id INT NULL,
  department_id INT
);

In the above code example, we created an employees table with several columns. The phone_number, salary, commission_pct, and manager_id columns can contain NULL values. This is achieved using the NULL keyword.

How to Insert NULL Values into a Table in SQL

You can insert NULL values into a table that allows NULL types by using the NULL keyword. Optionally, you can omit the value altogether to achieve the same result.

For instance, if we want to insert a NULL value into the phone_number column of the employees table from our previous example, we would use the following SQL syntax:

INSERT INTO employees (id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (1, 'John', 'Doe', 'johndoe@email.com', NULL, '2023-01-01', 1, 5000.00, 0.10, NULL, 1);

Here, In this SQL example, we inserted a new employee into the employees table. The phone_number and manager_id columns both contain NULL values.

How to Query NULL Values in SQL

If we query data in a relational database that contains NULL values, we must first understand how they are handled in different situations.

For instance, if we want to query data that contains NULL values, we can use the IS NULL or IS NOT NULL keywords. Let’s say we want to select every employee from the employees table who do not have a phone number; in this instance, we would would use the following SQL query:

SELECT * FROM employees
WHERE phone_number IS NULL;

In the above example, we selected every column from the employees table where the phone_number column contains a NULL value.

Another way to use NULL values in SQL is to retrieve data where a specific record is not NULL. For instance, if we want to select every employee from the employees table who have a phone number, you could use the following SQL query:

SELECT * FROM employees
WHERE phone_number IS NOT NULL;

In this SQL example, we selected all values from the employees table where the phone_number column is not NULL.

Read: SQL Data Types

Using SQL NULL Values in Calculations

If you add a NULL value to a numeric value, the result is always NULL. The same goes for any mathematical operation involving a NULL value. Because of this, it is important for database programmers to use the IS NULL keyword to check for NULL values before performing any mathematical operations in SQL or relational databases.

As an example, if we wanted to calculate the average salary of every employee in our employees table, we could use the SQL query below:

SELECT AVG(salary) AS average_salary
FROM employees
WHERE salary IS NOT NULL;

In this query, we used the AVG function to calculate the average salary of every employee. Additionally, we used the IS NOT NULL keyword to exclude any employees that have no salary.

SQL COALESCE Function

In SQL, the COALESCE function is used to handle NULL values in SQL. The COALESCE function takes multiple arguments and returns the first non-NULL value. This function is used to replace NULL values with a default value, or if you want to prioritize one value over another.

If we wanted to select the salary of an employee and replace any NULL values in the table with a default value of 0, we could use the following SQL query:

SELECT COALESCE(salary, 0) AS salary
FROM employees;

Here, we used the COALESCE function to retrieve the salary column of the employees table. If the value representing salary is NULL, the function will return a default value of 0.

How to Use the IFNULL Function

The SQL IFNULL function works similar to the COALESCE function, but it only takes two arguments. The IFNULL function returns the first argument if it is not NULL; otherwise, the second argument is returned.

To retrieve the salary of an employee and replace all NULL values with a default value of 0, the following SQL query can be used:

SELECT IFNULL(salary, 0) AS salary
FROM employees;

This example uses the SQL IFNULL function to return the salary column of the employees table. If the salary is NULL, IFNULL returns a default value of 0.

Final Thoughts on SQL NULL Values

In SQL, NULL values are important for managing data. NULL values are placeholders for missing or unknown values in a column. Understanding how to work with NULL values helps database developers avoid errors and create more accurate results with our queries.

Keep in mind that when you create a table in SQL, database programmers can specify which columns can allow NULL values. By default, all columns will allow NULL values unless otherwise specified. If you want to insert data into a table that allows NULL values, you can insert a NULL value using the NULL keyword. You can also simply omit the value as well.

If you query data that contains NULL values, programmers can use the IS NULL or IS NOT NULL SQL keywords to select data containing NULL values or if you want to exclude data that contains NULL values.

The SQL COALESCE and IFNULL functions are great for handling NULL values in SQL. The COALESCE function uses multiple arguments and returns the first non-NULL value, while the IFNULL function only takes two arguments and returns the first argument if it is not NULL. If not, it returns the second argument.

Read: SQL Best Practices

The post Working with NULL Values in SQL appeared first on Database Journal.

In a previous tutorial, we learned how to work with the WHERE clause in SQL, which database developers can use when they wish to retrieve a group of records that meet a certain criteria or condition. For instance, a database administrator may wish to find every instance in a database where a user’s name is equal to “Ronnie” – or, more likely, not equal to “Ronnie”. As powerful as the WHERE clause is in SQL, we can make it even stronger when we apply SQL’s NOT, AND, and OR operators.

This database development tutorial shows db admins and programmers how to use the NOT, AND, and OR operators in SQL, complete with syntax, code examples, and use cases.

Before we get started, however, you may wish to review our tutorial: How to Use the SQL WHERE Clause.

What is the AND Operator in SQL?

The AND operator in SQL is used to display a given record if every condition separated by the AND operator are TRUE. It is used in conjunction with the WHERE clause to filter records based on two or more conditions.

SQL AND Syntax

The syntax for the AND operator in SQL is as follows:

SELECT columnA, columnB
FROM table_example
WHERE conditionA and conditionB …;

SQL AND Code Example

For our SQL AND operator code example, let’s pretend we have a database named Nakamura Industries and within that database is a table named employees, which, as you can imagine, hold information pertaining to employee records. Let’s further posit that our table has the following columns:

• firstName: Holds first name of employee
• lastName: Holds last name of employee
• employeeID: Holds unique number representing the employees ID number

Let’s say that this table has the following information:

firstName	lastName	employeeID
Ronnie	        Payne		001
Donnie	        Layne		002
Ronnie	        Donnie	003

How to Use SQL AND to Retrieve a Record

Using our sample table above, to retrieve a record from a database using SQL AND, we would write the following SQL query:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne”;

In this example query, we are looking for an employee in the database whose first name is Ronnie and whose last name is Payne. If no record exists that meets both of those requirements, no records will be returned. Since we do, indeed, have a record where both sets of criteria are met, we get the result:

Ronnie Payne 001

when we execute our query.

Querying Multiple AND Criteria in SQL

We can, of course, query for more than two sets of criteria in SQL using the AND operator. In those cases, every piece of criteria must be met. Here is an example showing a query where three conditions must be met:

SELECT fiRead: rstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne” AND employeeID=007;

In this example SQL query, we are asking the database to return every record that has a first name of Ronnie, a last name of Payne, and an employee ID matching 007.

Keen observers will note that the employee Ronnie Payne has an employee ID of 001; since his employee ID is not 007 – and therefore not all three conditions are met, the query would return an empty result.

If we rewrote our SQL query to reflect the following, it would, instead, return the desired result:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne” AND employeeID=001;

Using this updated SQL query and AND operator, we would get the following output:

Ronnie Payne 001

Read: Best Online Courses to Learn SQL

What is the SQL OR Operator?

Like the SQL AND operator, the OR operator works in conjunction with the WHERE clause to query database tables for information where one or more conditions are TRUE.

Unlike with the AND operator, where all conditions must be TRUE, in scenarios where OR is used, only one condition must evaluate to TRUE.

SQL OR Operator Syntax

The syntax for SQL’s OR operator is as follows:

SELECT columnA, columnB
FROM table_example
WHERE conditionA OR conditionB …;

SQL OR Example Code

In the following example, we will use the same example database from before, and this time, create a query where we are seeking firstName values that equal either Ted or Larry:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ted” OR firstName=”Larry”;

Running the above SQL query will return no results, as table employees contains no firstName records containing Ted or Larry.

Consider this SQL example:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ted” OR firstName=”Ronnie”;

In this example, we set our criteria to search the column firstName for either Ted or Ronnie. Since one of the OR operator’s criteria is met, we get the result:

Ronnie Payne 001
Ronnie Donnie 003

Using SQL OR to Query for Multiple Criteria

Like the AND operator, database programmers and database administrators can use the OR operator to retrieve data from a table using multiple criteria. Here is an example of how to use the OR operator with the WHERE clause to query for data based on three sets of criteria:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ted” OR firstName=”Ronnie” OR firstName=”Donnie”;

Here, we are looking for the value Ronnie, Donnie, or Ted – if any of that criteria is met, results will be returned. Below is the output of running this query:

Ronnie	Payne		001
Donnie	Layne		002
Ronnie	Donnie	        003

What is the SQL NOT Operator

The SQL NOT operator can be a little confusing and take some time to get used to using properly. Instead of using it to find criteria that is TRUE, instead, database programmers use it to find data where a set of criteria is NOT TRUE, which is technically different than FALSE.

Because there are values that are equal to null in SQL, the NOT operator is sometimes used so query databases. The value null is neither TRUE nor FALSE – also known as not 0 or 1. So if you wanted to query a table that had null values in it, you could use the NOT operator to find such values.

SQL NOT Operator Syntax

Below if the syntax for using the NOT operator in SQL queries:

SELECT columnA, columnB
FROM table_example
WHERE NOT conditionA …;

SQL NOT Operator Code Example

Of course, the SQL NOT operator can also simply retrieve values from a table that are not equal to TRUE as well. Consider the following example SQL query:

SELECT firstName, lastName, employeeID
FROM employees
WHERE NOT firstName=”Ronnie”;

In the above example, we are specifically looking for records that do not contain Ronnie (or, put another way, where the value of firstName is not equal to Ronnie). Since there are two records in firstName that contain the value Ronnie – or the employees Ronnie Payne and Ronnie Donnie – the query will return the results that do not contain those values.

Here would be the output of running the above query:

Donnie	Layne		002

SQL Operators: NOT, AND, OR Used Together

Finally, it is plausible to use an SQL WHERE clause that used the AND, OR, and NOT in the same statement:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne” OR lastName=”Donnie”;

Similarly, you can combine NOT with AND or OR operators too:

SELECT firstName, lastName, employeeID
FROM employees
WHERE NOT firstName=”Ronnie” AND NOT lastName=”Payne” OR lastName=”Donnie”;

Read: Best Courses for Database Administrators

The post Working with SQL AND, OR, and NOT Operators appeared first on Database Journal.

Python allows developers to interact with various relational databases (such as Oracle, SQLite, and MySQL) and relational database systems (RDBMS) through several different libraries, which must conform to standards defined in PEP 249.

In this database programming tutorial, we will discuss how to connect to a MySQL database using Python and make database queries. You can read more about the PEP 249 by visiting the Python Database API Specification.

How to Create a Database Connection in Python

To create a connection to a MySQL database in Python, developers first need to download a database connector, which is a module that will enable your Python scripts to interact with data. This tutorial will be using the MySQL database since it is one of the most popular and widely used databases.

Not familiar with MySQL or want to increase your MySQL development skills? We have a list of the Best Online Courses to Learn MySQL to help get you started.

To download the Python MySQL database connector, you can use PIP with the following command:

$ pip install mysql-connector-python

There are a number of other modules programmers can use to make database connections, such as PyMySQL, MySqlClient, and OurSQL. However, this programming tutorial uses the MySQL Python connector, which is officially supported by Oracle and is written purely in Python.

To begin working with this module, you will need to import it into your script using the following command:

import mysql.connector

Next, you need to create a connection to the database. There are two ways of doing this: you can either use the connect() function or use the MySQLConnection object.

How to Use the connect() Method in Python

Here is an example of of a connection string you can use to connect to a database named student:

conn = mysql.connector.connect( user='jane', password = 'my-password' , host = "192.168.5.7" , database = 'student')

There are four key arguments that a programmer needs to connect to a database:

• user: The username used to connect to a database
• password: The users password
• host: IP address of the host where the database is hosted. Note that the default is 127.0.0.1 (localhost)
• database: Name of the database you are trying to connect to

You may be wondering why there is no argument for your MySQL server’s port. In actuality, there is, and the default is 3306. In case you set your server to use another port you can use the port argument.

The connect() does have more optional arguments that you can read about in the official Connector/Python documentation.

How to Use MySQLConnection to Connect to a Database

Here is some example code showing how to use Python and the MySQLConnection object to connect to a database:

from mysql.connector import (connection)

conn = connection.MySQLConnection (user='jane', password = 'my-password' , host = "192.168.5.7" , database = 'student')

The rest of this database programming tutorial will use the connect() method.

How to Execute a MySQL Query in Python

In order to execute a MySQL query, you need a MySQLCursor object. This object interacts with a MySQLConnection object and the database.

You can create a MySQLCursor in Python using the following code example:

cursor = cnx.cursor()

Programmers can then use the execute() method to process a query:

my-query = “SELECT * FROM Students”
result = cursor.execute(my-query)

If you are using the INSERT, DELETE, or UPDATE statements, then you need to commit your connection after executing the query. You can do so with the commit() method, show in the following code example:

conn.commit()

After you are finished interacting with the database, ensure that you close the connection and the cursor to release system resources. You can do this using the close() method:

cursor.close()
conn.close()

Here is a fully working code example showing how to connect to a MySQL database with Python, query a table, and close the connection:

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode


try:
   connection = mysql.connector.connect(database='school', password='pass456', user='root')
   if connection.is_connected():
       dbVersion = connection.get_server_info()
       print("Succeful connection to MySQL Server version:", dbVersion)
except mysql.connector.Error as err:
   if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
       print("Please check your access credentials (username or password)")
   elif err.errno == errorcode.ER_BAD_DB_ERROR:
       print("Please check the database name")
   else:
       print(err)
connection.close()

Notice the try.. except clause; developers can use this catch error in their database interactions to catch any errors.

Final Thoughts on Using Databases with Python

In this database programming tutorial, we learned how to connect a Python application to a MySQL database. Remember, you always need to close your connection after using the database to free up system resources using the close() method.

Read: Project Management Software for Database Developers

The post How to Use Databases With Python appeared first on Database Journal.

In SQL, data types help to define the type of value that can be stored in a database cell, or, more specifically, in a column. There are many kinds of data types in SQL and in this database programming and administration tutorial, we will look at each one and learn their syntax, how to use them, when to use them, their use case, and how they differ in specific relation database vendors, such as MySQL, Oracle, PostgreSQL and MS SQL Server.

Before we begin, if you prefer to learn in a classroom or online environment, we have a list of the Top Online Courses to Learn SQL to help you get started.

What Data Types Does SQL Have?

Data types in SQL – and its offshoots – define the type, size, and range of information that can be stored in a database object. In SQL, objects that can have a data type include columns, expressions, parameters, and variables.

A column in a table runs up and down or vertically and consists of cells that hold data that are related to one another (hence SQL being a relational database language). A variable can be thought of as a box that hold data; a database programmer can look inside the box to see that data, replace it with another piece of data, or change the data – so long as it shares the same type. More on that in a moment.

An expression can be made up of one or more values, operators, and functions that a programmer can use to evaluate to obtain a piece of data. Think of it as a mathematic formula, though expressions do not only perform math or work on numeric values.

Parameters are used to facilitate the exchange of data in what are known as SQL functions and stored procedures.

SQL has three main types of data types, including built-in, user-defined alias, and user-defined common language runtime (CLR). These can be further broken down into categories like numeric, string, date and time, spatial, and miscellaneous. Within each of those categories, there are sub-categories as well, which we will discuss in-depth in the following section.

Read: SQL Best Practices

SQL Numeric Data Types

Numeric data types in SQL represent number values as either INT (integer or whole numbers) or FLOATS (decimal point or floating-point numbers). Within these two categories, there are subtypes as well that database programmers can use to represent versions of these two numeric data types. Here is a list of all numeric data type in SQL:

• BIT: Used to represent numbers of a single byte, ranging from 0 – 1
• TINYINT: Used to represent small integer values ranging from 0 – 255
• SMALLINT: Used to store integer values ranging from -32,768 – 32,767
• INT: Used to represent integer or whole number values ranging from -2,147,483,648 – 2,147,483,647
• BIGINT: Used to represent integer values ranging from -9,223,372,036,854,775,808 – 9,223,372,036,854,775,807
• DECIMAL: Used for decimal points numbers ranging from -10^38 + 1 – 10^38 -1
• NUMERIC: Used for numbers ranging from -10^38 + 1 – 10^38 -1
• FLOAT: Used for decimal numbers ranging from -1.79E+308 – 1.79E+308
• REAL: Used for approximate floating-point numbers ranging from -3.40E + 38 – 3.40E + 38
• SMALLMONEY: Used for monetary values ranging from -214,748.3648 – 214,748.3647. Specific to MS SQL Server
• MONEY: Used for monetary values ranging from -922,337,203,685,477.5808 – 922,337,203,685,477.5807. Specific to MS SQL Server

SQL String and Character Data Types

Character and text data types in SQL consists of text-based values, which can include the upper and lowercase version of any letter ranging from a-z, A-Z, and special characters like !@#$%^&*(). SQL has two types of character and string data types: character and strings and Unicode characters and strings.

Without getting too deep into the subject (it is beyond the scope of this tutorial), Unicode is an encoding standard used to assign a unique numeric value to a letter, digit, or symbol, to ensure it works regardless of language or script being used. For instance, the Unicode for an uppercase letter A is U+0041, while the Unicode for a lowercase letter a is U+0061.

Below is a list of the Character and String data types in SQL:

• CHAR: Used for fixed length text with a maximum length of 8,000 characters
• VARCHAR: Used for variable-length text with a maximum length of 8,000 characters
• VARCHAR(max): Used for variable-length text with provided max characters. Note that this is not supported in MySQL flavor of SQL
• TEXT: Variable-length text values with a maximum size of 2GB worth of data

Here is a list of the different Unicode Character and String data types allowed in SQL. It should be noted that these data types are not supported in MySQL:

• NCHAR: Used for fixed length text with a maximum length of 4,000 characters
• NVARCHAR: Used for variable-length text with a maximum length of 4,000 characters
• NVARCHAR(max): Used for variable-length storage with max characters provided
• NTEXT: Used for variable-length text with a maximum storage capacity of 1GB

Learn more abut different flavors of SQL in our tutorials: What is MySQL and What is PostgreSQL.

Date and Time Data Types in SQL

As you might have guessed, date and time data types are used for date and time values. Here is a list of the supported date and time data types supported in SQL:

• DATE: Stores date values in the YYYY-MM-DD format
• TIME Stores time values in the HH:MI:SS format
• DATETIME: Stores both date and time values in the YYYY-MM-DD HH:MI:SS format
• TIMESTAMP: Used to store the number of seconds that have passed since the Unix epoch, or 1970-01-01 00:00:00 UTC
• YEAR: Used to store a year value in either a 2-digit or 4-digit format, ranging from 1901 – 2155 in 4-digit format, or 70 – 69 in a 2-digit format, which represents the time frame of 1970 – 2069

Binary Data Types in SQL

Binary data is data that is represented by 1s and 0s; in SQL, binary data types are usually used for binary data such as images or text files. SQL supports the following types of binary data types:

• BINARY: Used when the size of the data is consistent; used for fixed-length binary data with a maximum length of 8,000 bytes
• VARBINARY: Used for variable-length binary data up to 8,000 bytes
• VARBINARY(max): Used for variable-length binary data with a provided maximum byte size
• IMAGE: Used for variable-length binary data with 2GB maximum size

Other Data Types in SQL

SQL supports other data types not listed in this tutorial (at the time of this writing) – the data types contained herein are the most commonly used. In addition, it should be noted that vendor-specific relational databases – or other flavor of SQL – like MySQL, T-SQL, Oracle, and MS SQL Server have their own variations and syntax for the data types in this database programming tutorial. We will cover those in a future tutorial, which will be linked here, so be sure to check back often.

Before we wrap up this tutorial, however, let’s discuss four more commonly used SQL data types:

• BLOB: Used for storing large binary objects
• CLOB: Used for storing large character objects up to 2GB in size
• JSON: Used to store JSON data and JSON documents
• XML: Used to store XML schema data

Read: How to Comment in SQL

The post SQL Data Types appeared first on Database Journal.

Read: How to Use the SQL SELECT Statement

SQL WHERE Clause

As stated in the intro, the SQL WHERE clause is used when we need to filter the records returned from a table to show a result based on a given condition or set of criteria. The syntax for the WHERE clause is:

SELECT column1, column2, column3
FROM name-of-table
WHERE condition

In addition to being used alongside the SELECT statement, the SQL WHERE clause can be used with UPDATE and DELETE statements as well.

SQL WHERE Clause Code Example

For our first code example, let’s assume that we have a database with a table named Superheroes that contains the HeroNames, HeroID, and SuperPowers of known super heroes. As a sample dataset, let’s say the following entries exist in our Superheroes table:

HeroNames SuperPowers HeroID
AwesomePossum Play dead 110
Whatawoman Jump rope of doom 120
TheSpoiler Ruins movie endings in a single bound 130

If we want to only pull up the record for super heroes whose name is AwesomePossum, we would write the follow SQL query, which shows how to use the WHERE clause:

SELECT * FROM Superheroes
WHERE HeroNames'AwesomePossum;'

Running this query would result in the following output:

AwesomePossum Play dead 110

The above SQL example showed us how to use the WHERE clause to query a condition based on a text value – hence the inclusion of single quotation marks in our query (do not use double quotations!). If we want to query a numeric value using the WHERE clause, we would omit the single quotation marks, as shown in the following SQL example:

SELECT * FROM Superheroes
WHERE HeroID=110;

Running this query will give us the result:

AwesomePossum Play dead 110

Read: How to Use SQL SELECT DISTINCT Statement

SQL WHERE Clause Operators

Of course, using only the equals or = operator to determine criteria in an SQL WHERE clause is very limiting. Fortunately, there are a number of other operators you can use in conjunction with the WHERE clause to set query criteria. SQL WHERE clause operators include:

• = Signifies values must equal x
• > Signifies values must be greater than x
• < Signifies values must be less than x
• >= Signifies values must be greater than or equal to x
• <= Signifies values must be less than or equal to x
• <> Signifies values must not be equal to x
• BETWEEN Signifies values must be within a range
• IN Used to specifiy more than one possible value in a column
• LIKE Used for searching a given pattern

SQL BETWEEN Operator Example

To use the BETWEEN operator with a WHERE clause on our example table, we would write the following SQL statement:

SELECT * FROM Superheroes
WHERE HeroID BETWEEN 110 and 115;

Executing this SQL query would result in the output:

AwesomePossum Play dead 110

SQL IN Operator Example

Here is an example of how to use the IN operator with a WHERE clause in SQL:

SELECT * FROM Superheroes
WHERE HeroNames ('AwesomePossum', 'Whatawoman');

Running the above query on our sample dataset would return the data for the AwesomePossum and Whatawoman records.

SQL LIKE Operator Example

Here is an example showing how to use the LIKE operator with a WHERE clause in SQL:

SELECT * FROM Superheroes
WHERE HeroNames LIKE 'A%';

In this example SQL query, we are searching the Superheroes table for any records where HeroNames start with A. The % symbol is used to say that any value after A is okay. Using our dataset sample, the result of running this query would be:

AwesomePossum Play dead 110

Had their been another hero with a name that began with A, it, too, would have been returned.

Read: Best Online Courses to Learn SQL

The post How to Use SQL WHERE Clause appeared first on Database Journal.

PostgreSQL is one of the world’s most widely used database systems in the world, offering support for not only structured query language (SQL), but JSON as well, making it a great choice for database-driven enterprise applications. In this database programming and database administration tutorial, we discuss what PostgreSQL is, its relation to SQL, and the benefits of working with the hybrid database system.

Read: What is MySQL?

Overview of PostgreSQL

PostgreSQL, like other offshoots of SQL database systems, is often viewed as a database programming language. However, this is a misnomer, as PostgreSQL is actually an open source relational database system. What is unique about PostgreSQL is that it supports not only structured query language (SQL) querying (like other relational database management systems), but JSON as well, which is used for non-relational database queries.

The phrase relational in the realm of databases refers to the way in which data is structured in a database. Using structures known as tables, which are comprised of rows (horizontal) and columns (vertical) that intersect to create a cell that stores a singular data point, relational databases store data in such a way that information has relationships with other pieces of data.

For instance, you may have a table with columns named FirstName, LastName, and Social. These columns, which run vertically or up and down, hold that representative of the column headers. Database administrators can infer that column FirstName will hold “first names”.

The same logic follows for LastName (for “last names”) and Social (presumably for social security numbers). Meanwhile, the rows running horizontally will hold those data points. The first row, for instance, might contain these three data points:

FirstName  LastName  Social
Ronnie     Payne     111-11-1111

Because we are using a relational database, the data points Ronnie, Payne, and 111-11-1111 are all related. If we queried the database to find all records associated with Social whose value is 111-11-1111, then the values Ronnie and Payne would be returned as well, but no other records, unless they also contained the Social value of 111-11-1111.

That is a loose example of how relational databases work.

One last point about relational databases. You may also here a relational database referred to as a relational database management system (RDBMS); the two, however, are not the same thing. A relational database is simply a type of database, while an RDBMS is an entire database system, including database management tools.

To learn more, check out our tutorial: What is a Relational Database Management System?

What are the Benefits of PostgreSQL

PostgreSQL first hit the scene back in 1986 at the University of Berkeley in California. Originally called “POSTGRES” after the original “Ingres” database. The intention of its developers was to create a database that supported multiple data types, which it currently does.

With over two decades of support and development, PostgreSQL has a number of benefits for both database developers and database administrators, which we will highlight below.

Language Support

PostgreSQL supports many programming languages, including, of course, SQL. Other languages supported by the relational database include:

• Java
• JavaScript
• Perl
• Python
• R
• Shell
• Tcl
• Lua
• Go and Golang
• C#
• C and C++
• Ruby

Support for Data Types

As stated, PostgreSQL was created with the thought of having the primary feature of supporting many data types. To that end, PostgreSQL supports the following data types:

• Primitives (integers, strings, Booleans, datetimes)
• Geometric types
• hstore type
• Ranges
• Monetary types for currency types
• Multidimensional arrays
• Network addresses
• JSON objects

Open Source

PostgreSQL is an open source relational database, meaning its source code is available for download. With this codebase in hand, developers can tailor the database to their organizations needs. This also means that the codebase is available to the general public, who help contribute to the reliability, functionality, and security of the database.

Further, since PostgreSQL is open source, it is free, making it an ideal choice for budget conscience software development teams looking for license free database options.

Hybrid Database

PostgreSQL offers support for both relational and non-relational queries. This means that database developers and database administrators can perform SQL queries on rows containing transaction or statistics data (where the data is related) while also using NoSQL to store and process JSON documents.

This hybrid functionality is great for flexible development shops that require multiple types of database systems.

Data Compliance

Another benefit of PostgreSQL has to do with data compliancy. PostgreSQL supports ACID semantics, which are used for transactions. In addition PostgreSQL allows for foreign keys, joins, views, triggers, and stored procedures.

Binary large objects, like images, videos, and sound can also be stored in PostgreSQL databases, making it an excellent choice for media heavy, database-driven applications.

PostgreSQL is Object Oriented

PostgreSQL is also an object-relational database, meaning it supports object oriented programming (OOP) concepts such as classes, objects, function overloading, and custom data type creation. This includes support for inheritance as well, as a child table can inherit columns from parent tables, making PostgreSQL a truly powerful database option.

PostgreSQL is Extensible

In computer programming, the phrase extensible means that you can add to the functionality of a given technology – usually the language or database itself. PostgreSQL is an extensible database, meaning database programmers and database administrators can extend the functionality of PostgreSQL by adding new data types, index methods, aggregate functions, functions, and operators.

Spatial Data

PostgreSQL is well-known for its support of spatial data, such as having a specific data type for handling geometrical objects and geographic objects. Using the PostGIS extension, PostgreSQL can be used as a geospatial data store, making it popular among government agencies, geographic information systems (GIS), and location-based service providers.

PostgreSQL Features

PostgreSQL supports the following database features:

• Foreign keys
• Table inheritance
• Object orientation via classes and objects
• User-defined types
• Multiple data types
• Async replication
• Multi-version concurrency control (MVCC) architecture
• Nested transactions
• Tablespaces
• Point-in-time recovery

Final Thoughts on PostgreSQL

Is PostgreSQL the right solution for your software development team? That will depend largely on your organizations needs. If your team wants a flexible database that can handle both SQL and NoSQL queries, then you should strongly consider working with PostgreSQL. Likewise, if your company relies works with geospatial or location-based datasets, you can not go wrong choose PostgreSQL as your database.

On the other hand, if you develop web applications that are database-driven, you might want to choose a PostgreSQL alternative such as MySQL. If your development team technology stack is heavily dependent on Microsoft-related technologies and languages like C# and ASP.NET, MSSQL Server is probably a better option.

Read: Best Online Courses to Learn SQL

The post What is PostgreSQL? appeared first on Database Journal.

An SQL injection is an attack on a database server where a malicious actor enters – or injects – some nefarious input in a form, other than the expected input. The attack can be used to gain access to restricted data, wipe out databases, and examine the server info, among other things.

In this database programming tutorial, database developers and database administrators will learn a number of ways that a hacker can exploit your database. This database tutorial will also show you how you can protect your application from these vulnerabilities.

Read: SQL Best Practices

What are the Types of SQL Injections?

As mentioned earlier, SQL injection attacks depend on some input other than the expected input into a form. Simply put, the attacker defines the goal of the exploit, then passes an SQL statement in a form field, instead of the expected input, such as a name or number.

In the following section we will answer the question: What are the different types of SQL injection attacks?

SQL Injection Due to 1=1

This is an attack based on the fact that the condition 1=1 is always TRUE. This means that whenever a query runs, a true result is always returned, regardless of the condition (a WHERE clause).

This kind of exploit enables a hacker to gain unauthorized access to data. Here is an example of a normal SQL query:

SELECT *  from Student WHERE age=27;

Here is an example of a malicious SQL query demonstrating a 1=1 attack:

SELECT *  from Student WHERE age=27 OR 9=9;

As you can see from the above code example, if a malicious user enters 27 OR 9=9 in the form field, instead of just 27, then they will be able query the database for all student records regardless of the query condition.

Notice that the given example uses 9=9 instead of 1=1. This was intentionally chosen to show you that you do not have to only use 1=1 to make the exploit in the section.

You can use any number=number or character value on both sides of the equal sign ( e.g ‘g’=’g’ ).

SQL Injection Due to – –

The double-dash (—) character sequence is used to write comments in your SQL query. However, a hacker can take advantage of this property to remove/void certain segments of your SQL query. For example, to bypass password checks and query conditions.

Here is an example of how to perform an SQL — attack:

SELECT * FROM Users WHERE username = 'userX'--' AND password = '' 

The above database query will allow userX to access the Users table, without making a password check.

Read: Best Certifications for Database Administrators

SQL Injection Due to Batched SQL Statements

Depending on the database server you are using, you may be able to query multiple (batched) statements at once.

A malicious actor can exploit this to add their own SQL statement. Take an example of a user who wants to access all student records. If this user is malicious, they could also add a query to DROP the Teacher table:

SELECT *  from Student WHERE subject="Japanese"; DROP TABLE Teacher;

SQL Injection Due to UNION

The UNION command is used to query results from two tables. Note that the columns selected for each table used must have the same data type. A hacker can take advantage of this in the following manner:

SELECT fname, age FROM Student UNION SELECT fname, age FROM Teacher;

SQL Injection Based on “”=””

The “”=”” SQL injection attack is similar to the 1=1 injection. A hacker simply makes the “” OR “”=”” entry instead of an expected value to meet the query condition. Hence, the condition will always be true regardless of the WHERE clause:

SELECT * FROM Student WHERE fname= "" OR ""="";

SQL Injection to Examine Database

There are typically three steps involved in database vulnerability testing or hacking: (1) reconnaissance, (2) planning, and (3) attack. It is interesting to note that the military also uses a similar framework before making an attack.

Reconnaissance (or simply recon) refers to gathering info about your intended target. This is very important to help you in the next step in your hacking process – planning.

A database hacker is able to gather invaluable information about your database, such as the version, name, and distributor using either of the following SQL statements:

SELECT version();
STATUS;

How to Prevent SQL Injections

The database exploits in the above section shows you that it is important to protect your database against such known vulnerabilities. Take note that the responsibility is upon you, the application developer or database administrator, to provide protections for your database (and not the database vendor).

One way of protecting your database is using prepared statements. These are statements that do not directly insert the values they obtain from the user. In a prepared statement, the SQL server binds the values at execution time.

Here is a code example of how you can use prepared statements in PHP:

 
$stmt = $db->prepare("INSERT INTO Student(fname, lname, age) VALUES (?, ?, ?)");

In this code, the ? is used to replace the parameters. The bind_param() method is then used to bind the respective values to the given parameters.

$stmt->bind_param("ssi", $fname, $lname, $age);

After, you can now execute the statement:

$stmt->execute();

When binding the values, notice the string “ssi” arguments. This string specifies the data type of each input value. There are four possible letter use can use to represent the respective types:

• d: double
• i: integer
• s: string
• b: BLOB

Final Thoughts on How to Prevent SQL Injections

It is important for database programmers and database administrators to protect your database from SQL Injections. Remember, databases do not usually ship with this protection, so it is upon you, the developer or admin, to implement it.

Read: Best Online Courses to Learn SQL

The post How to Prevent SQL Injections appeared first on Database Journal.

If you are new to database administration or database development, you have no doubt heard the phrase “relational database management system” being tossed around. As database programmers or those that work with databases, we tend to think of database systems in terms of languages or platforms, like SQL, MySQL, or T-SQL. In today’s database programming tutorial, we are going to discuss RDBMS systems and answer the question: “What is a Relational Database Management System” and provide some tips for working with one.

Read: Top Online Courses for Learning MySQL

What is a Database?

While this tutorial deals specifically with relational databases, we should briefly discuss what database software, in general, is. To that end, a database is a system or program where data is stored. More than that, databases tend to store information or data in a way that is organized, easily accessible, and able to be manipulated. Manipulation of data or datasets, in this sense refers to being able to add, edit, delete, append, or query (we’ll cover this more later) information.

What is a Relational Database?

A relational database is a type of database that uses structures known as tables to store data for later use. We call it “relational” because of the method that data is stored – that is, in relation to other data. For instance, a table within a database may contain first names, last names, and social security numbers. Suppose we have the following information in a table:

Ronnie Payne 555-55-5555
Seth Rollins 666-66-6666
Clark Griswold 777-77-777

In the above example, the name Ronnie, last name Payne, and social security 555-55-5555 all belong to one record or group of information. If I want to be able to find out Ronnie’s social security number, then those two pieces of data must be connected – or related – for me to retrieve the information. Otherwise, all you have is a database full of random information that means nothing on its own.

To better understand this thought, we have to look at how tables are made, which we discuss in the next section.

What are Tables?

Tables in a database are used for storing information in an organized – and related – manner. You can think of a table as a grid, such as you might see in Microsoft Excel or other spreadsheet software. Tables are made up of columns, which run up and down or vertically, and rows, which run side to side or horizontally. Where columns and rows intersect is known as a cell, or, in database terminology, a record. Consider the following image:

In the above image, the word First Name acts as the header for our first column, letting the database administrator or programmer know what the rest of the data in that column represents. For instance, the First Name column holds values Ronnie, Seth, and Clark. Likewise, the second column, Last Name holds the values Payne, Rollins, and Griswold.

Row one, in this instance, would contain the following information:

Ronnie Payne 555-55-5555

Row two contains:

Seth Rollins 666-66-6666

And so on.

Further, each column will contain a data type: text-based values are known as string data types, while numeric values are known as integers for whole numbers and floatsfor decimal-point numbers.

What is a Relational Database Management System

Thus far, we have discussed databases as a whole, and relational databases; in this section, we will discuss relational database management systems – also known as RDBMS – and what they are used for.

A relational database management system (RDBMS) is software that lets database administrators and database programmers work with, view, manipulate, create, update, edit, delete, or query information stored in a relational database. This data manipulation occurs through a language known as Structured Query Language or SQL – pronounced “S-Q-L”.

You can learn more about SQL in our tutorial: What is SQL?

Types of Relational Database Management Systems

Just as there are different types of database systems, so, too, are there different types of RDBMS. Some of the most popular relational database management systems include:

• MySQL
• PostgreSQL
• Oracle DB
• SQL Server
• SQLite

We discuss each of this in more depth in our upcoming tutorial: What Are the Different Types of RDBMS?

The post What is a Relational Database Management System appeared first on Database Journal.

To avoid this issue, this database programming tutorial will cover the best practices for writing SQL code and querying relational databases.

Looking to learn SQL or database development in an online course? We have a list of the Best Online Courses to Learn SQL to help get you started.

Best Practices for Structure Query Language

Below is a list of best practices for database programmers and database administrators to follow when querying relational databases with SQL.

SQL Keywords Should Use Uppercase

Perhaps one of the most basic best practices for SQL is to always ensure that you are using uppercase letters for your SQL keywords. This can be a bone of contention among db admins, as different flavors of SQL will have different casing for code; in general, however, the core SQL keyword commands should always use uppercase, such as:

SELECT FirstName, LastName FROM Customers;

or

SELECT * from Customers;

versus writing it:

--THIS IS WRONG
select FirstName, LastName from Customers;

While some debate the matter, using uppercase for SQL functions is also a best practice – if nothing else, for consistency.

Comment Your SQL Code

One of the most important best practices in any programming language is to always add comments to your code. This is true of your SQL statements as well. Commenting is a way to leave notes to yourself – or other developers – explaining what a particular line or block of code was meant for.

You should only comment your SQL code when a statement is not self-explanatory or its intent is not readily apparent. Adding comments to code whose purpose is obvious is redundant and can lead tl clutter in your codebase – something you want to avoid. Try to create a good balance and only comment when it makes sense to do so.

Here is an example of how to comment in SQL:

-- This is a comment
SELECT * FROM Customers;

Single-line – or inline – comments in SQL begin with two — symbols. When the SQL interpreter sees the — symbols, it ignores the remaining code on that line.

You can also leave more than one line of comments. There are two ways to achieve this in SQL. The first involves simply leaving multiple single-line comments, as show in this code example:

--This is a comment
--This is also a comment
SELECT * FROM Customers;

Another way to leave more than one comment is to use multi-line commenting. In multi-line comments, you start the comment by using the opening /* symbol. You then right your comments and close the multi-line comment using the closing */ symbol. Here is an example of how to write multi-line comments in SQL:

/*This is the start of a multi-line comment
This is a continuation of our multi-line comment
SQL does not read this line or any other line
Until we close the comment.
*/
SELECT * FROM Customers;

The SELECT Asterisk Problem

In our SQL examples above, you may have noticed that we use the SELECT * statement. If you are unfamiliar, SELECT lets you choose data from a given table. If we add the wildcard asterisk character (*), then all of the information in a table will be chosen or processed. This becomes a problem in Big Data scenarios or where large amounts of data exist in a table. Instead of selecting every column in a table, instead, choose only the columns you need to query. For instance, if you only need the First Name from our Customers table example, you would use:

SELECT FirstName FROM Customers;

That isn’t to say you shouldn’t ever use SELECT * – just make sure you only use it in scenarios where you actually need all of the data in a table.

Format Queries

Another SQL best practice is to format the text in yoru queries. This means that yuo want to place each clause on its own line. This makes your code easier to read and less prone to mistakes. For example, instead of writing:

SELECT FirstName, LastName, FROM Customers WHERE FirstName = 'Ronnie';

You would want to format the query this:

SELECT
	FirstName,
	LastName
FROM
	Customers
WHERE
	FirstName = 'Ronnie';

This makes understanding the SQL easier and finding errors much simpler.

Final Thoughts on SQL Best Practices

There are many best practices for writing clean, efficient, and maintainable SQL statements; this database programming tutorial only scratches the surface. We will update this listing from time to time, so be certain to check back often to learn more tips for writing quality SQL.

The post SQL Best Practices appeared first on Database Journal.