In this tutorial, we’ll cover the useful SQL commands for database management, with examples for practice.
SQL is an essential skill that’s in-demand for data science/machine learning. Most companies still have their data stored on SQL databases (servers).
Following this tutorial, you’ll learn:
- How to create SQL databases and tables, including data types, CONSTRAINT, INDEX, PRIMARY KEY.
- How to manage tables such as UPDATE, DELETE, INSERT INTO, ALTER TABLE, TRUNCATE/DROP.
- How to create views.
Whether you are a beginner of SQL or want a cheat sheet for interviews of SQL, you’ll love this practical guide.
Let’s dive in!
Further Reading: This article is part of a series of SQL database tutorials. If you want to use SQL for data analysis, check out SQL Tutorial for Beginners: Learn SQL for Data Analysis with all the important data manipulation queries.
Prerequisite: FAQs and Download SQL Server
First of all, if you still have questions such as:
- What is SQL?
- Why learn SQL? Is SQL required for data science?
- Is SQL easy to learn? How long does it take to learn SQL?
Please find answers to these FAQs at the FAQ section of this article.
To practice SQL in a professional environment, you may install a Microsoft SQL server for FREE.
Please follow the guide below with step-by-step instructions.
If this is not an option for you, you can still follow this tutorial for practice by typing the commands on Notepad. But you won’t get the benefits of practicing in a real professional SQL environment.
Note before the tutorial: it’s unnecessary (or possible) to memorize all the SQL commands within a short time. Plus, there might be other commands you need in the future.
We suggest you follow the tutorial to learn systematically, which covers the essentials. When you are practicing data science with SQL, a simple Google search would often solve the problem.
What are SQL Databases and Tables?
A database in SQL Server is made up of a collection of tables that stores a specific set of structured data.
A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers.
SQL server documentation
In this tutorial, we will learn how to create and manage our SQL database and tables. A SQL server can have multiple databases. And under one database, there could be multiple tables.
CREATE DATABASE
To begin our practice, let’s first create a new database.
Syntax
CREATE DATABASE database_name
Example
Question: Given that we have the admin right to create databases, let’s create one named mytest.
Answer:
CREATE DATABASE mytest
If this is your first time using the SQL server, here’s how you run the commands: open a “New Query” window and copy the command above into it. You can either click the “Execute” button with the green arrow or press F5 on the keyboard to run it.
Upon running, you should see Messages “Commands completed successfully.” come up.
To check the database we just created, you can look at the “Object Explorer” panel at the left side by expanding “Databases”. If it’s not shown there, click the “Refresh” button and check again.
Great! Now we have our SQL database.
To store information in our new database, we’ll need to create tables.
Before creating a table, let’s learn some related concepts: the basic data types in SQL.
Data Types
As mentioned earlier, each column within the table stores a specific type of information, hence is of a particular data type. This data type is an attribute that determines the type of data that this column can hold, such as integer data, character data, monetary data, date and time data, binary strings.
The list below covers the popular data types for data science in the SQL server:
- int, bigint, smallint, and tinyint: int is the primary integer data type. There’re also bigint, smallint, tinyint of different ranges and storages. Use the smallest types that can hold all possible values.
For example, a column with human age data will be within the range 0 to 255, so we can use tinyint, which needs the least space. Check out the details about ranges here. - decimal: the decimal data type. We can specify the precision and scale.
For example, decimal(5,1) has 5 as the maximum total digits (both right and left sides of the decimal point) and the number of decimal digits (to the right of the decimal point) being 1. - char and varchar: the character data types of either fixed-size (char) or variable-size (varchar).
The general rule of thumb is to use char when the columns have consistent data entries size, otherwise use varchar.
We can define the character with a string size in bytes such as CHAR(16), VARCHAR(12). - date: the date data type. The default format is ‘YYYY-MM-DD’.
- datetime: the date and time data type. For example, the format could be ‘YYYY-MM-DD HH:MM:SS’.
There are also other data types. Please visit data types in SQL Server for the full list.
CREATE TABLE (CONSTRAINT)
Now we are ready to build our first table under the database.
Syntax
CREATE TABLE table_name(
column1 datatype CONSTRAINT,
column2 datatype CONSTRAINT,
…,
columnN datatype CONSTRAINT)
Note that the constraints for each column are optional. The two commonly used constraints for the columns are:
- NOT NULL: if we put this after the column definition, the column can’t contain NULL values. A field with a NULL value is a field with no value.
- UNIQUE: if we put this after the column definition, the column can only contain UNIQUE values for each row.
Example
Question: Within the database mytest, create a table named Employee with the following column names and data types:
- Employee_ID, int
- Employee_Name, varchar(15)
- Job_Title, varchar(50)
- Birth_Date, date
- Gender, char(1)
Also, we want to make sure the columns Employee_ID and Employee_Name don’t have NULL values.
Hint: to specify the database context, use the USE statement. We can use USE to make sure the new table is created under the database we want.
Answer:
USE mytest
CREATE TABLE Employee(
Employee_ID int NOT NULL,
Employee_Name varchar(15) NOT NULL,
Job_Title varchar(50),
Birth_Date date,
Gender char(1)
)
If we expand “Tables” under database mytest, we can view the table Employee just created.
If we use the SELECT statement to query the table, we can see the empty table.
Another way of creating a table is by using the SELECT statement to query from another existing table, check out the SELECT Statement Syntax section.
INSERT INTO
Our new table has no records, how to add them?
We can use the insert statement.
Syntax
We can either insert rows by specifying values:
INSERT INTO table_name (column1, …, columnN)
VALUES (value1, …, valueN)
Or insert rows from another table:
INSERT INTO target_table_name (column1, …, columnN)
SELECT column1, …, columnN FROM source_table_name
WHERE <condition>;
Examples
Question: Insert the below records into the new table Employee.
| Employee_ID | Employee_Name | Job_Title | Birth_Date | Gender |
| 1 | James | Accountant | 1980-03-01 | M |
| 2 | Mary | VP | 1986-05-12 | F |
| 3 | Jennifer | CEO | 1970-03-23 | F |
| 4 | John | Data Analyst | 1990-09-01 | M |
| 5 | Robert | Data Scientist | 1993-12-01 | M |
| 6 | Karen | Data Scientist | 1989-04-06 | F |
Answer:
INSERT INTO Employee (Employee_ID, Employee_Name, Job_Title, Birth_Date, Gender)
VALUES (1, ‘James’, ‘Accountant’, ‘1980-03-01’, ‘M’),
(2, ‘Mary’, ‘VP’, ‘1986-05-12’, ‘F’),
(3, ‘Jennifer’, ‘CEO’, ‘1970-03-23’, ‘F’),
(4, ‘John’, ‘Data Analyst’, ‘1990-09-01’, ‘M’),
(5, ‘Robert’, ‘Data Scientist’, ‘1993-12-01’, ‘M’),
(6, ‘Karen’, ‘Data Scientist’, ‘1989-04-06’, ‘F’)
Note that we don’t have to specify the column names after the INSERT statement since we are inserting values for all the columns in the same order:
INSERT INTO Employee
VALUES (1, ‘James’, ‘Accountant’, ‘1980-03-01’, ‘M’),
(2, ‘Mary’, ‘VP’, ‘1986-05-12’, ‘F’),
(3, ‘Jennifer’, ‘CEO’, ‘1970-03-23’, ‘F’),
(4, ‘John’, ‘Data Analyst’, ‘1990-09-01’, ‘M’),
(5, ‘Robert’, ‘Data Scientist’, ‘1993-12-01’, ‘M’),
(6, ‘Karen’, ‘Data Scientist’, ‘1989-04-06’, ‘F’)
By using the SELECT statement (SELECT * FROM Employee), we can view the table content below.
Question: Create another table Employee_extra with two rows of data as below. And then insert these data into the Employee table.
| Employee_ID | Employee_Name | Job_Title | Birth_Date | Gender |
| 7 | Liam | Data Analyst | 1996-03-21 | M |
| 8 | Sarah | VP | 1988-01-19 | F |
Answer:
USE mytest
CREATE TABLE Employee_extra(
Employee_ID int NOT NULL,
Employee_Name varchar(15) NOT NULL,
Job_Title varchar(50),
Birth_Date date,
Gender char(1)
)
INSERT INTO Employee_extra
VALUES (7, ‘Liam’, ‘Data Analyst’, ‘1996-03-21’, ‘M’),
(8, ‘Sarah’, ‘VP’, ‘1988-01-19’, ‘F’)
INSERT INTO Employee
SELECT * FROM Employee_extra
Again, we can query the table to check that the two new rows were added to the table.
Primary Key (CONSTRAINT)
Before learning about managing the table, let’s discuss another important concept of the table: the primary key.
The primary key is a type of constraint used to guarantee data integrity in the SQL database tables. The primary key (PK) is the column(s) with values that uniquely identify each row in the table. A table can have only one PK constraint.
It can be formed of a single column or multiple columns. If a PK constraint is defined on multiple columns, values may be duplicated within one column, but the combination of values from all the columns of the primary key constraint must be unique.
When we create a primary key constraint for the table, a unique index for the PK columns will be automatically created. So we can have fast access to the data when we use these primary keys in queries.
Syntax
There are two main ways of creating the primary key, either by ALTERing an existing table:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_names)
Or creating one when creating the table:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
…,
columnN datatype,
CONSTRAINT constraint_name PRIMARY KEY (column_names)
)
Example
Question: We created the table Employee without a primary key, let’s add one as the column Employee_ID.
Answer:
ALTER TABLE Employee
ADD CONSTRAINT emp_primary_key PRIMARY KEY (Employee_ID)
Note that the primary key columns can’t allow NULL values. We have specified the constraint on Employee_ID to be NOT NULL when creating the table. But if we didn’t do that, we would get an error message “Cannot define PRIMARY KEY constraint on nullable column in table ‘Employee'”.
If we expand the table Employee’s ‘Keys’ and ‘Indexes’ folder, we can see that the PK and the index were created.
CREATE and DROP INDEX
As we just learned, creating a primary key can create an index. Indexes within the table can speed up the queries against the table. Besides the primary key, we can create indexes directly. We should only create indexes on columns that will be frequently searched against since the table with indexes takes longer to be maintained.
Syntax
We can create an index that allows duplicate values:
CREATE INDEX index_name
ON table_name (column_names)
Or create an index that only allows unique values:
CREATE UNIQUE INDEX index_name
ON table_name (column_names)
To drop indexes, we use the below statement:
DROP INDEX table_name.index_name
Example
Question: Assume we often want to query the table using the columns Employee_name and Birth_date. Create an index on these columns. And then drop it.
Answer:
CREATE INDEX emp_id
ON Employee (Employee_name, Birth_date)
Before dropping the index, we can view it in the Object Explorer.
DROP INDEX Employee.emp_id
Next, we’ll look at a couple of statements to manage the tables.
UPDATE
What if we want to change the existing data in a table?
We can use the UPDATE statement.
Syntax
UPDATE table_name
SET column1 = value1, …, columnN = valueN
WHERE <condition>
Example
Question: Karen (Employee_ID = 6) just got promoted to Senior Data Scientist, can you help update her job title in the database?
Answer:
UPDATE Employee
SET Job_Title = ‘Senior Data Scientist’
WHERE Employee_ID = 6
Be careful when updating records! If we don’t have the WHERE clause, ALL records will be updated.
We can see that the table is updated.
DELETE
What if we want to remove some rows from a table?
We can use the delete statement.
Syntax
DELETE FROM table_name
WHERE <condition>
Example
Question: We learned that John (Employee_ID = 4) left the company; let’s remove his record from the Employee table.
Answer:
DELETE FROM Employee
WHERE Employee_ID = 4
ALTER TABLE (ADD and DROP COLUMNS)
Besides changing the records/rows, we might also want to change the columns in the table. Let’s see how the ALTER statement can help us with that.
Syntax
ALTER TABLE table_name
ADD column_names datatype
ALTER TABLE table_name
DROP COLUMN column_names
Example
Question: For the Employee table, let’s first add two columns department_name and team_name. But later, let’s also remove them.
Answer:
ALTER TABLE Employee
ADD department_name varchar(50), team_name varchar(50)
If we query the table before dropping the columns, we can see the empty columns were added.
ALTER TABLE Employee
DROP COLUMN department_name, team_name
We’ve covered a lot of SQL database and table commands, let’s learn about another useful concept: VIEW.
CREATE VIEW
A view is a virtual table whose contents are defined by a query.
Like a table, a view consists of a set of named columns and rows of data.
Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
SQL server documentation
We use views mainly as a filter on the tables referenced in the query. The query can involve one or more tables. It’s also used when we want to limit users’ access to the actual tables (behind the views).
After the view is created, we can use SELECT statements to query its contents like an actual table.
Syntax
CREATE VIEW view_name AS
SELECT statements
Example
Question: Create a view all_employee to show all the employee names from the Employee table.
Answer:
CREATE VIEW all_employee AS
SELECT Employee_Name FROM Employee
Note that the view will be updated when the referencing table is changed, which we’ll show in the example of the next section (TRUNCATE).
At the end of this tutorial, let’s learn how to remove the tables and database we created.
TRUNCATE and DROP TABLE
We can either remove all the data inside the table or delete the entire table.
Syntax
To remove data inside the table while keeping the structure, we can use:
TRUNCATE TABLE table_name
TRUNCATE TABLE is like the DELETE statement without the WHERE condition, but TRUNCATE TABLE is faster and uses fewer resources.
To delete the entire table, we can use:
DROP TABLE table_name
Example
Question: Let’s first remove all the records from the table Employee, then drop the table as well.
Answer:
TRUNCATE TABLE Employee
If we query the table before dropping it, we can see an empty table still exists.
If we also query the view created earlier (SELECT * FROM all_employee), we’ll notice that it’s updated with the original table, so it becomes empty as well.
DROP TABLE Employee
DROP DATABASE
Syntax
DROP DATABASE database_name
Example
Question: As the last exercise in this tutorial, let’s delete the entire SQL database mytest.
Answer:
USE master
DROP DATABASE mytest
Note that we specified another database context before dropping the database mytest. Otherwise, you’ll get a warning saying it’s currently in use.
Well done! You’ve learned many useful SQL database management commands in this tutorial.
For continuous learning, check out SQL Tutorial for Beginners: Learn SQL for Data Analysis.
Leave a comment for any questions you may have or anything else!
Related “Break into Data Science” resources:
How to Learn Data Science Online: ALL You Need to Know
A detailed review of resources online includes courses, books, free tutorials, portfolio building, and more.
Python crash course: breaking into Data Science
A FREE Python online course, beginner-friendly tutorial. Start your successful data science career journey.
What are the In-Demand Skills for Data Scientists
Why Python, SQL, Machine Learning are the most in-demand skills for data science.
