fbpx

Structured Query Language (SQL)

A specialized programming language designed for interacting with a database

What is Structured Query Language (SQL)?

Structured Query Language (SQL) is a specialized programming language designed for interacting with a database. SQL allows us to perform three main tasks:

  1. To define the scope of the database
  2. To add, delete or modify the data in the database
  3. To read data from the database

 

Structured Query Language (SQL)

 

Building Blocks of SQL

Modern SQL consists of three major types of query languages. Each of the languages corresponds to a function or task described above. All three combine to form a fully functioning language that allows a user to perform all possible functions on a relational database.

  • Schema: Schema Query Language (SchemaQL) is used to create tables and define relationships between them. The most common example of SchemaQL is the ‘CREATE TABLE’ statement.
  • Transact: Transact SQL provides the ability to insert, update, and delete data stored in a relational database.
  • Data: The data query language is the part used to interact with stored data. This serves to enable users to query the data and conduct the task of data analysis.

 

Commonly Used SQL Statements

The following is a list of commonly used SQL commands that can be used to create tables, insert data, change the structure of the tables, and query the data.

 

Defining and Creating Tables

 

CREATE

The CREATE statement is used to create tables in a database. The statement can define the field names and field data types within a table. The CREATE statement is also used to define the unique identities of the table using primary key constraints. It is also used to describe the relationships between tables by defining a foreign key.

Template:

CREATE TABLE [tableName] (

Column_1 Datatype1,

Column_2 Datatype2..

);

 

ALTER

The ALTER statement is used to change the structure of a table in the database. The statement can be used to create a new column or change the data type of an existing column.

Template:

ALTER TABLE [tableName]

ADD Column_1, Datatype_1

 

DROP

The DROP statement is used to delete a table from a database. It must be used with caution as deletion is irreversible.

Template:

DROP TABLE [tableName]

 

Adding, Modifying, and Deleting Data

 

INSERT

The INSERT statement is used to add records or rows to a table. The name of the table to insert records into is defined first. Then, the column names within the table are defined. Finally, the values of the records are defined to insert into these columns.

Template:

INSERT INTO [tableName] (Field_1,…,Field_N)

VALUES (Value_1,…,Value_N)

 

UPDATE

The UPDATE statement is used to modify records in a table. The statement changes the values of a specified subset of records held in the defined columns of the table. It is a good practice to filter rows using a WHERE clause when updating records. Otherwise, all records will be altered by the UPDATE statement.

Template:

UPDATE [tableName]

SET Column_1 = Value_1, …, Column_N = Value_N

WHERE [filter citeria]

 

DELETE

The DELETE statement is used to delete rows from a table based on criteria defined using a WHERE clause. The statement should be used carefully, as all deletion in a database is permanent. If a mistake is made using a DELETE statement, the database will need to be restored from a backup.

Template:

DELETE FROM [tableName]

WHERE [filter criteria]

 

Extracting and Analyzing Data

 

SELECT

The SELECT statement is one of the most used statements in SQL. It is used to select rows from one or more tables in a database. A SELECT statement is usually used with a WHERE clause to return a subset of records based on a user-defined criterion. The SELECT statement is used to conduct most data analysis tasks as it allows the user to extract and transform the desired records from a database.

Template:

For specific columns:

SELECT Column_1, …, Column_K FROM [tableName]

WHERE [filter criteria]

 

For all columns:

SELECT * FROM [tableName]

WHERE [filter criteria]

 

Building a Financial Database

In the following example, we will use some of the SQL statements discussed above to create a financial database with pricing and fundamental data. Microsoft Access is an accessible tool that can be used to build relational databases within the Microsoft Office ecosystem.

First, we use the CREATE statement to define the structure of the table. Our table will have the following columns and data types:

  • ID: A unique identifier for every record entered in the table (INTEGER)
  • Company Name: The name of the company (TEXT)
  • Ticker: Company ticker used to identify the company stock on an exchange (TEXT)
  • Price: The latest closing price of the stock (FLOAT)
  • PE Ratio: The price to earnings ratio of the stock (FLOAT)

 

We can follow the template described above to write the CREATE statement. The following statement creates the table:

CREATE TABLE priceTable (

ID INTEGER,

companyName TEXT(100),

ticker TEXT(20),

price FLOAT,

peRatio FLOAT

);

 

Next, we insert some records into the table using the INSERT statement. Following are two examples of inserting rows in our table.

INSERT INTO priceTable ( ID, [companyName], [ticker], [price], [peRatio])

VALUES (5, “Walmart”, “WMT”, 138, 26);

 

INSERT INTO priceTable ( ID, [companyName], [ticker], [price], [peRatio])

VALUES (6, “General Motors Co.”, “GM”, 60 11);

 

Suppose we want to analyze the stocks in the database. We want to analyze the best value stocks that are available. We can define value stocks as those with a price-to-earnings ratio of less than twenty. It is accomplished using a SELECT statement with a WHERE clause as shown below:

SELECT * FROM priceTable WHERE peRatio < 20;

 

The instruction returns the three stocks: Intel, Ford, and General Motors. The above is a simple example, but a larger database with more securities and fundamental data can be a powerful tool in identifying potential investments.

 

Results of the SELECT query
Fig. 1: Results of the SELECT query

 

A Short History of SQL

Structured Query Language (SQL) was first introduced in a paper from 1970, “A Relational Model of Data for Large Shared Data Banks,” by Edgar F. Codd. Codd introduced relational algebra, which is used to define relationships between data tables. It is the theoretical foundation of SQL. The first implementation of SQL was developed by two researchers at IBM: Donald Chamberlin and Raymond Boyce.

 

Additional Resources

Learn more about Structured Query Language through CFI’s SQL Fundamentals course. CFI is the official provider of the Business Intelligence & Data Analyst (BIDA)® certification program, designed to transform anyone into a world-class financial analyst.

To keep learning and developing your knowledge, we highly recommend the additional resources below:

  • Database
  • Power BI – Uses in Finance
  • Dashboards and Data Visualization Course
  • Tableau – Uses in Finance