An Introduction to SQL Commands, Part 1

This first installment of a three-part series introduces essential SQL

commands for creating, modifying, and deleting tables within SQL Server
Management Studio.

Brien Posey

April 17, 2025

5 Min Read

Alamy

[Editor's Note: This is Part 1 of a three-part series on commonly used SQL

commands.]

The primary way to interact with a SQL Server database is with SQL
commands. These commands are often embedded into applications but can
also be used in command-line environments, such as PowerShell, or entered
directly into the SQL Server Management Studio.
In this first segment of this three-part series, I'll explain some of the most
commonly used SQL commands. In Part 2, I will show you how to add data to
a table. In Part 3, I'll show you how to write SQL Server queries.

Every SQL Server database contains one or more tables. A table is simply a
collection of data organized into rows and columns, much like a spreadsheet.
The data within a table is normally related in some way. A financial database,
for example, might include one table containing customer data (customer
name, address, phone number, etc.) and another table containing the
invoices sent to those customers. In this example, the types of data stored in
the Customers table (the name, address, phone number, and so on) would
be stored in table columns.

The tables used within a SQL Server database may also contain a primary
key. A primary key is just a unique value that can be used to reference a
particular row of data. In the case of a customer contact information
database, it's entirely possible that a business might have two customers
with the same name. As such, a database would need to have a means of
differentiating between two such customers. One way of handling this
problem would be to assign each customer a number. This unique customer
number could be used as a primary key within the database table.

Related:How To Connect to SQL Server Database From PowerShell

Now that I have introduced a few key concepts, let's look at some SQL
commands that you can use to interact with database tables. First, I need to
mention two things.

First, to use these and other SQL commands, you must be connected to a
SQL Server database and logged into the database using an account with the
proper permissions. For this article, I will use the SQL Server Management
Studio to connect directly to a database.

If you want to follow what I am doing, open the SQL Server Management
Studio and connect to your database. Once you are logged in, click the New
Query button found on the taskbar. This will take you to an interface where
you can write SQL commands. When you are done writing a command (or
series of commands), you can execute the commands you have written by
clicking on the Execute button, shown in Figure 1.

Related:How To Visualize SQL Server Data in PowerShell (With Sample Script)

Figure 1: This is the query interface within SQL Server Management Studio.

The other thing that you need to know is that SQL commands are case-
sensitive. SQL commands usually consist of short phrases and operators.
These phrases and operators must be entered in upper case. Some examples
of such phrases include CREATE TABLE, ALTER TABLE, and DROP Table.

Let's use a SQL command to create a table. As previously noted, the

command for doing so is CREATE TABLE. However, you will also need to
specify the table name. You can also include the names of the columns you
want to create within the table, though you must also specify the type of
data stored in each column.

As an example, let's create a table named CustomerTable. We will add an ID

column that will store the customer ID and act as a primary key. The ID will
be an integer value, designated by INT. We will also create columns for the
customer's first name and last name. These columns will contain string data;
I will set the data type to VARCHAR(50), which means that each of these
columns can store up to 50 characters of data. Here is what the command
would look like:
CREATE TABLE CustomerTable (

ID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50)

);

You can see what these commands look like in Figure 2.

Figure 2: These are the commands used to create a new table.

When you execute the commands and refresh the display, the new table and
its columns are listed within the Object Explorer, as shown in Figure 3.

Related:How To Connect to SQL Server Database From PowerShell

Figure 3: SQL Server Management Studio displays the table and its columns.

You can add additional columns to a table by using the ALTER TABLE
command. In doing so, you will have to specify the table name, the name of
the column that you want to add, and the data type. Suppose I wanted to
add an email column to the CustomerTable table. I could do so by using this
command:

ALTER TABLE CustomerTable ADD Email VARCHAR(50);

As you can see, I allowed for up to 50 characters of data within the Email
column. But suppose that I later realized I needed to allow for up to 100
characters of data. I could make the change by using this command:

ALTER TABLE CustomerTable ALTER COLUMN Email VARCHAR(100);

Now say I needed, for whatever reason, to do away with the email column. I
could delete it by entering this command:

ALTER TABLE CustomerTable DROP COLUMN Email;

Similarly, I could delete the entire table by entering:

DROP TABLE CustomerTable;

Now that I have shown you how to create, modify, and delete tables, I will
continue the discussion in Part 2 by showing you how to add some data to a
table.

Introduction to SQL Commands, Part 2: Adding, Updating Data

This installment of our three-part series on SQL commands explores essential

SQL commands for inserting, modifying, and deleting data within a SQL
Server table.

Brien Posey

April 21, 2025

4 Min Read

Alamy

[Editor's Note: This is Part 2 of a three-part series on commonly used SQL

commands.]
In Part 1 of this series, I showed you techniques for creating and modifying
tables within a SQL Server database. We'll continue the discussion by
demonstrating how to add data to the table you've created. In Part 3, I'll
show you how to write SQL Server queries.

Admittedly, data is normally added through an application, but such an

application uses SQL commands internally to add data to the database.

I have a table named CustomerTable containing fields for customer ID, first
name, and last name. To insert data into a SQL column, you need to know
both the table name and the specific column names you're inserting data
into. For example, to add a new customer to the CustomerTable, you would
use the following command:

INSERT INTO CustomerTable (ID, FirstName, LastName) VALUES

('1','John','Doe');

This command creates one row of data. It inserts a value of 1 into the ID field
and then sets the FirstName to John and the LastName to Doe. You can see
what the command looks like in Figure 1.

Figure 1: A single row of data has been added to the table.

You can see the data itself in Figure 2.

Figure 2: The data has been added to the table.

The easiest way to insert data into a SQL Server database is to add the data
one row at a time. However, it is possible to insert multiple rows of data with
a single INSERT INTO statement (Figure 3 and Figure 4). Once again, you will
need to specify the table name, the column names, and the values. For
example, let's suppose I wanted to add the names Mary Johnson and Mike
Jones to my database, using customer ID numbers 2 and 3. The command for
doing so would look like this:

Related:Introduction to SQL Commands, Part 3: Retrieving and Sorting Data

INSERT INTO CustomerTable (ID, FirstName, LastName) VALUES

('2', 'Mary', 'Johnson'),

('3', 'Mike', 'Jones');

Figure 3: I have inserted multiple rows of data.
Figure 4: This is what the table currently looks like.

Modifying and Deleting Data

Not surprisingly, you can also use SQL statements to modify or delete data
from a table. The UPDATE command is used to modify the data that is stored
within a table. When using this command, you must specify the table name,
the column you want to update, and a qualifier.

Suppose, for example, that you wanted to change Mary Johnson's last name
to Smith. You could accomplish this by using these commands:

UPDATE CustomerTable

SET LastName = 'Smith'

WHERE FirstName = 'Mary';

Although this command works, you may want to structure the command a bit
differently if you were using it in the real world. The reason has to do with
the risk of having identical values stored within your database. Right now,
there are only three names stored in our example database. Imagine,
however, that instead of the database containing three names, it contained 3
million names. In a situation like that, it is almost a certainty multiple people
would have the first name Mary.

With that in mind, consider how this statement works. SQL searches the
table for anyone with the name Mary and changes their last name to Smith.
If we were performing a real-world operation, it would presumably be
intended for changing a single person's last name, yet the operation would
likely make an incorrect modification to numerous names.

Related:An Introduction to SQL Commands, Part 1

The safe way to modify data would be to reference the table's primary key.
Remember, the primary key is unique for every record in the table. Hence,
you might structure the query like this:

UPDATE CustomerTable

SET LastName='Smith'

WHERE ID='2';

In this particular table, we have designated the ID column as the primary

key, meaning that every ID is guaranteed to be unique. As such, searching
for a customer based on their ID guarantees that the correct customer record
is being modified.

As mentioned, you can use SQL statements to delete data from a table.
However, you do have to be careful how you do it. The TRUNCATE TABLE
command will delete all of the records from the table, as will the DELETE
FROM command unless you use a qualifier to specify what you want to
delete.

The safest way to delete a record from a table is to reference the record's
primary key. If, for example, you wanted to delete the third record (which in
this case has an ID value of 3), the command for doing so would be:

DELETE FROM CustomerTable WHERE ID = '3';

Now that I have shown you various techniques for creating, modifying, and
deleting data, I'll wrap up this series in Part 3 by showing you how to write
SQL Server queries.

Introduction to SQL Commands, Part 3: Retrieving and Sorting Data

In the final part of this series, learn how to use SQL Server's SELECT
command to retrieve, filter, and sort data efficiently from a database table.

Brien Posey

April 23, 2025

5 Min Read
Alamy

[Editor's Note: This is Part 3 of a three-part series on commonly used SQL

commands.]

So far in this article series, I have shown you various SQL Server commands
for creating and deleting database tables, as well as commands for creating,
modifying, and removing data records within a table. I'll conclude this series
by demonstrating how to retrieve data from a SQL Server database table.

Retrieving data from a SQL Server database table involves using a SELECT
command. You will need to enter the SELECT command followed by a
statement specifying what you are looking for.

The most basic use of the SELECT statement is using the asterisk (*) as a
wildcard character, indicating that you want to select everything. When
doing so, the only qualifier you must include is the table name. For example,
let's write a query that retrieves all data from the CustomerTable database
table we created earlier in the series. The command for doing so is:

SELECT * FROM CustomerTable;

You can see what this command looks like in Figure 1. In the lower portion of
the figure, you can see the data returned as a result of the query. In this
particular case, our sample database contains three records, and there are
only three columns of data. Even so, all of these columns and records are
included in the results.

Related:Introduction to SQL Commands, Part 2: Adding, Updating Data

Figure 1: This command retrieves all of the data that is stored in the data.

In this example, the table holds only a small amount of data, so displaying
everything isn't an issue. However, it's important to think about how things
would change if you were searching for something specific in a database with
millions of rows of data.

In such a situation, the best option would be to use the WHERE command to
specify what you are looking for. This technique is very similar to how we
modified a record in the previous article. For example, suppose we wanted to
see all of the data for anyone with the last name Smith. To do so, we would
use this command:

SELECT * FROM CustomerTable WHERE LastName='Smith';

You can see what the command looks like in Figure 2. The bottom portion of
the figure also shows what the results look like.

Figure 2: We have retrieved a specific database record.

As I explained earlier in this series, SQL commands are often embedded into
applications. Such an application may sometimes need to retrieve a single
piece of data instead of an entire record. In the case of our tiny database
table, this might mean retrieving one specific first name from the database
instead of displaying the first name, last name, and ID. Suppose, for
instance, that I wanted to display Mary Smith's first name. There are several
ways that we could accomplish this, but here is one example:

SELECT FirstName FROM CustomerTable WHERE LastName='Smith';

I have replaced the asterisk (*) with the name of a specific column. As
written, this statement will look for anyone with the last name Smith and
display their first name. You can see the command and its results in Figure 3.

Related:An Introduction to SQL Commands, Part 1

Figure 3: I have retrieved a single name from the database table.

What if we wanted to modify the command to display the first name and last
name, but not the ID or any other columns that might hypothetically exist?
All we would have to do is modify the SELECT statement to list the columns
that we want to display, in the order that we want to display them. Note that
a comma should separate column names. Here's an example of a command
that displays a single record's first and last name:

SELECT FirstName, LastName FROM CustomerTable WHERE

LastName='Smith';

You can see the command and its results in Figure 4.

Figure 4: I have displayed a record's first and last name columns.

Sorting Query Results

One last trick that I want to show you is how to sort the query results. To do
so, let's retrieve all of the records within the table and display the LastName
column, followed by the FirstName column, and then sort the results
alphabetically by last name. Here is the command:

SELECT LastName, FirstName FROM CustomerTable ORDER BY LastName

ASC;

In this command, we are selecting the LastName and FirstName columns

from the CustomerTable. We are not using the WHERE command because we
want to display all records. We would only use the WHERE command if we
wanted to filter the results.

Next, we use the ORDER BY command to tell the SQL Server that we want to
sort the results. To do so, we have to specify the column name for which we
want to sort the data, and we have to tell SQL whether the data should be
sorted in ascending order (ASC) or descending order (DESC). Here is the
command:
SELECT LastName, FirstName FROM CustomerTable ORDER BY LastName
ASC;

You can see the command and results in Figure 5.

Figure 5: The data has been sorted alphabetically by last name.