SQL Server Tutorial: Database Basics

sqlservertutorialAlthough SQL Server is a proprietary Microsoft product, SQL is used to communicate within any Relational Database Management System (RDBMS). As a complete beginner, the first thing you need to understand is the basics of how a relational database works and how SQL fits into this puzzle. Once you understand the basics, migrating to SQL Server or another database management system such as Oracle, Sybase, or Access is relatively easy. SQL Server Essentials is a course designed for beginners with no prior database experience.

SQL stands for Structure Query Language. It is used almost exclusively to communicate within a database. SQL statements perform tasks such as updating and retrieving data from tables within the database. Each of the RDBMS systems mentioned above use proprietary extensions that are specific to each respective system; however, the standard SQL commands such as “Select, Update, Delete, Create, and Drop” are used to accomplish most functions within the database anyway.  You can learn more about standard SQL commands in Practical SQL Skills from Beginner to Intermediate.

Database Administrators (DBAs) are in high demand. If you are thinking about getting into the IT profession, becoming proficient in SQL is one of the best things you can do to improve your marketability as a professional candidate. That said, this article will give you a solid foundation in SQL basics that you can apply to further educational opportunities and ultimately to a respectable job as a DBA.

Tables

Every relational database system contains one or more objects called tables. All of the data contained within a database is stored in a table. Just like the tables you may create in Microsoft Word, a database table is comprised of both rows and columns. Columns contain the column name, data type, and other attributes specific to the column. Rows contain individual records (i.e. the data) for each table. For example:

Weather

City

State

High

Low

New York

New York

82°

66°

San Diego

California

107°

89°

Las Vegas

Nevada

102°

62°

Orlando

Florida

94°

77°

In this example, the headings of City, State, High, and Low represent the columns in this data table which is known as “Weather.” Each city represents a row with associated data for each location. Now imagine something like this for every city in the entire country. That’s what a relational database management system does.

Selecting Data

The select statement is used to query the database and retrieve data that matches the criteria you have specified. A select statement looks something like this:

select “column1”

 [,”column2″,etc]

 from “tablename”

 [where “condition”];

 [] = optional

The column names that you include in the select statement determine which columns are actually queried for results. If you want to select data from all columns within a table, you can simply use “*” as a variable. In this example, after the where clause it simply says “condition.” This is actually replaced with one of the following conditional selections:

  • = Equal

  • > Greater than

  • < Less than

  • >= Greater than or equal to

  • <= Less than or equal to

  • <> Not equal to

  • LIKE

Using these conditional statements allows you to identify specific parameters you are looking for in the database. For instance, if you wanted to find out the low temperature in Las Vegas, your SQL statement would look like:

select low

   from “weather”

 where city = las vegas;

This SQL statement will only select table data that matches this specific set of parameters. Keep in mind that these are simplified examples to demonstrate basic principles of using SQL to manipulate database information. In a normal business environment, databases contain millions of records and SQL query statements are usually much more complex.

Other Commands

The great thing about learning SQL is that all of the other commands follow this same basic format but use a different keyword such as Insert or Delete. For instance, if you wanted to insert a new city into the table above, your SQL statement would look like this:

Insert into weather

(city, state, high, low)

Values (‘Houston’, ‘Texas’, ‘91’, ‘77’);

Notice that each value is enclosed in single quotes; this is part of SQL formatting. The insert command allows you to specify which rows you wish to modify by listing them first and then listing the associated values or data in the following line.

Database Design Introduction explains the techniques needed to properly create and normalize a relational database.

Practicing SQL

The best way to get better at using SQL Server is to play around with it. You can create databases and modify them as you see fit to gain experience and a better understanding of how various elements within the database interact with one another.

You can download Microsoft’s SQL Server Express free for private use. Follow the instructions to set up SQL Server on your computer. Unlike many other programs, there is a bit of configuration required when setting up this environment on your PC. The best way to do this is by following the installation instructions provided on Microsoft’s website or by checking out Microsoft SQL Server 101.

As you become more accustomed to creating queries in SQL Server, you quickly realize how advanced your queries can become. These advanced queries are the reason why DBAs can make so much money. As more companies look at data mining as a good way to understand their customer base, they need professional SQL developers who can write complex commands that harvest useful data to generate reports and ultimately make marketing decisions for the company’s future.

SQL Server professionals are in high demand. By learning these basics and applying this knowledge to personal projects in your own SQL Server Express environment, you will soon be ready to enter the exciting field of Database Administration as a true professional.