If you plan to have any type of dynamic website, you need SQL. Structured query language (SQL) is the syntax for databases and data manipulation. While there are slight variances between the different database systems, the syntax is similar for each type of query – search, store, delete and update. Once you understand one system, you can manage others including MSSQL, Oracle, and MySQL. Standard SQL expressions include update, select, insert and delete. These are the four statements you'll learn to view and edit your data. 

SQL is used when you have a relational database as the dynamic storage for your website. The most common relational databases include Microsoft SQL Server, Oracle, MySQL and Microsoft Access. The solution you choose is usually determined by your developer, but you can decide which database platform you want to use in the software design stages. Make sure you research your database solution before you decide. It's not an easy task to change in the future, so once you choose a platform, you are usually tied to it. 

Before you dive into SQL development or even minor changes on your website, you need to know the basics. 

What is a Database?

A database is a file system that stores your data in an organized fashion. The organized data is then retrieved and edited using the SQL language. For instance, you probably have contact information stored on your desktop or mobile. You need this information organized so that you can later find a certain contact. You need to search by last name, first name or phone number. You may not see the SQL statement from your contact software, but usually the backend code uses SQL to search data. The software takes your input such as last name, builds a SQL statement, and finds your contact in the database file. 

Most databases are built on a relational standard. The relationships between tables help link data. Relational databases can span hundreds and even thousands of tables. To understand how your database stores data, you should understand table structure. 

What is a Table?

A table is a basic data structure. Below is an example of a table structure: 

The above image represents a data structure used to store accounts. When a person types information into your web page, the data is stored into the appropriate column. Each record (or account in this example) contains a field for the information. Each record has the user's first and last name, for example. 

A table stores data in a structured file on a server's hard drive. Each file joins data together using relationships. You can think of relationships as links that join tables together using related information. For instance, using the above accounts table, you would want to link the user with his orders. You would then build an "Orders" table and add the "AccountId" from the account table into the Orders table. SQL would then link the two relational tables together. The way you link tables affects your database and application, so it's important to understand data types and table design. You want to build a design that avoids data redundancy but performs well. As you add more data, your design matters more. A poor design affects accuracy and performance. 

Notice the key icon in the above image. The key indicates that the AccountId column is the primary key. All tables require a primary key for good design. A primary key is a unique, clustered index that identifies each record. Indexes are more advanced concepts, but indexes are necessary for the stability and performance of your table design. Consider the primary key as the unique identifier for a specific table. For instance, a customer can't have more than one account Id. In the above table, the AccountId is set as the primary key to identify a unique record throughout the data. 

Storing Different Data Types

SQL relies on your ability to know the type of data you need to store. Each column is set to a data type. If you don't set your data types correctly, your database returns errors or you might store corrupted, unusable data. 

SQL allows several data types. When you create your tables and lay out your application, you set a data type for each piece of information you collect. Incidentally, when you build your table relationships, you should match data types between your links. For instance, AccoutId is set to integer in the image example. Any linked data such as an order table should also use the integer data type for AccountId. 

The following are some of the most common data types you'll see in SQL: 

• Integer

• Varchar(x) or NVarchar(x)

• Char(x) or NChar(x)

• Bit

• DateTime

• Decimal, Numeric or Float

• XML

The above list isn't a definitive list. SQL includes other data types, but the above examples are the most common. When you design your table, you need to envision how your application works. For instance, if you have an ecommerce store, you need a table for customers, orders, shipping and home addresses, products, inventory, and customer service records. The better you plan your application, the better your database design will be. The more experience you have with application design, the better you'll be at foreseeing the data you need in the future. Your database tables must be scalable, so you can add features. You also want to store all the data you need for reports and running the business, so you don't need to redesign the database later. 

The integer, decimal and float data types store numbers. The main difference between integers and the other numeric data types is that integers only store whole numbers. You can store negative or positive whole numbers, but they cannot contain any decimals. If you try to store a decimal in a table column designed for integers, SQL truncates or rounds the value. 

Most developers are familiar with the string primitive data type. A string in SQL is the "varchar" and "nvarchar" data types. The "x" in parenthesis indicates the number of characters you can store. For instance, a column set to varchar(25) can hold up to 25 characters. The difference between nvarchar and varchar is that nvarchar stores multibyte, Unicode characters. If you use extended characters for data storage, you should set your table column data types to nvarchar instead of varchar. 

Char and NChar are similar to varchar and nvarchar. Varchar means "variable character length." You use a varchar when you don't know the length of a storage column. For instance, you don't know the length of your customer's first name. For that reason, you set your first name column to varchar. Char is a known character length. For instance, zip codes for certain countries usually have a set length. Since you know the length for the column, you can set this length as "char(10)" or "nchar(10)" where 10 is the length of the column. Note that char data types will automatically truncate or append characters if you try to store a different character length. If you use SQL to store 5 characters in a column set as char(10), SQL will automatically append 5 additional characters to your data. Just like nvarchar, nchar supports Unicode characters. 

Bit is used similarly to a boolean in software design. A bit is either a one or a zero (true or false). You use a bit when you want to flag a record with true or false. For instance, you can disable or enable users with a bit field. Suppose you want users to verify their accounts using an email sent after the user creates an account. Once the user clicks a link in the email, the account is verified and active. You would set the initial bit value to zero and then change (update in SQL) it to one after the user verifies his account. 

A DateTime data type is exactly what it sounds like – stores a date and time value. Depending on the SQL platform, you can store milliseconds for a precise date and time stamp. Each one of your tables should have a DateTime column. These fields are called audit fields. You should have a column for the date and time a record was created and a field that logs any updates. In the example image, the table has a create date and a modify date. The designer stores a date when the record is created and then updates the modified date when the record changes. You can create a more advanced database design by logging details about the changes. 

Float, decimal and numeric data types allow fractional precision. The difference between the three is the length of the digit. If you work with the .NET programming languages, numeric and decimal map to the decimal data types in .NET. If you use .NET and want to use decimal or numeric data types in your table design. Decimal and numeric data types allow for up to 38 digits. A float data type is similar, except it allows you to increase precision. You should only use a float data type if you need extremely precise decimal storage. In most applications, a numeric or decimal value is sufficient. 

Why Use SQL?

After you design your database tables, you need to store and manipulate data. This is where SQL is necessary. You need SQL to search and display data from the structured tables. You don't need to know the underlying table file format. SQL handles it for you. 

Every input and output is handled with your SQL queries, so it's important to understand how these queries work. You might need to create a quick report, review your data, insert some product into your tables or review your customer orders. All of these tasks require SQL. SQL takes you from a frontend user to a backend administrator who knows data from a technical level.