Intro To Database and SQL: How to Write Basic Queries
One of the courses I took earlier this year skimmed the surface of some of the varied responsibilities required of a database administrator. I learned how to design, create, manipulate various data and query (searching a database in order obtain valuable information), a relational database. To do this, it was necessary to learn the computer language SQL (pronounced S-Q-L or “sequel”) which stands for “Structured Query Language”.
In this post, I’ll be covering the very basics of this relatively simple, quick and non-procedural language where you, as the end user, tells the application what must be done, not how it’s to be done. In other words, you issue the commands that the software needs to perform, while letting the software take care of all the background work and complex algorithms. Ready to learn a new skill and more about databases? Keep reading!
What is a database?
First things first, let’s brush up on the term database. A database is a collection of information which is organized so that it can be easily retrieved, managed and updated. Data is a crucial component for any business and being able to have access to good quality data enables a company to assess previous and current information in order to make well-informed business decisions.
Databases have been around for a long time. They’ve been used since the 1960s, which is about a whopping 56 years ago! These electronic databases have allowed companies to accumulate and track a tremendous amount of data, such as customer and accounting information.
The larger your business and database record, for example, a customer database with hundreds or thousands of records, investing in the maintenance and installation of a heavy duty relational database management system such as Microsoft SQL Server, Oracle or MySQL would be highly beneficial.
The smaller the business, a spreadsheet platform, such as Microsoft Excel, would suffice–but keep in mind, a spreadsheet is not a database. Although it can arrange data, it’s functionality lies in its ability to perform calculations. Indexing more than 100+ items will inevitably slow the program down and managing various tables on a single Excel spreadsheet can be cumbersome and will be more prone to error. Another disadvantage includes scalability. Only one person can access an Excel spreadsheet compared to a more robust database management.
Bigger databases like the systems mentioned allows many users to access, request or update data at the same time. Take Amazon as an example. I had to call them once about an issue and after providing them with my order number, they were able to check my information and the status of my order in just a few keystrokes. Giving customer service representatives access to customer information, in a timely manner, allows a company to be more efficient and productive.
So without further ado, let’s finally get into querying relational databases, shall we?
What’s a relational database?
A relational database gets its name by being able to store related data together, for example, customers and orders. Below is an example of a Sales Database schema stored in Microsoft SQL Server which has a total of 5 interlinked tables Product, OrderItem, Orders, Customer and SalesPerson.
Did you notice the keys? These are what links the tables of a relational database together. Each table may have one primary key (PK) and any number of foreign keys (FK). In the Product table, it’s PK is in ProdNo. A primary key shows record (row) uniqueness. In the OrderItem table, its PK is OrdNo and FK is ProdNo. As you can see, a foreign key is just a PK from one table, that’s been placed in another table. Key fields allow tables to be joined so that a user can successfully query the database.
For example, a user can then query the database to find out the name and quantity of different products that the company has sold by gathering information from the Product and OrderItem table. We can even filter the results from the product that has the highest quantity to the lowest and select to view specific columns that we want in the output, or all, depending on what data the user wants to gather. As this is just an introduction to SQL, we’ll only be querying and modifying data from just one table.
Example 1: SELECT, FROM, WHERE Clause
Given the Customer table, let’s say we want to gather all possible information of the customers from a specific city, like Seattle. Statements used in this query will be SELECT, FROM, and WHERE which I will capitalize to show emphasis (but is not required in the actual program since it’s not case sensitive).
SELECT * FROM Customer
WHERE City = ‘Seattle’
The asterisk after SELECT tells the program to output all columns associated with the Customer record table (CustId, CustName, City, State, CreditLimit, OSBal). It’s quite useful so that we can save time on having to input all the column names associated.
Using FROM, we input the name of the table from which the program will extract the information from, which in this example, will only be the Customer table.
Lastly, the WHERE clause is used to get only those records that fulfill a specific criteria. In this case, only customers from the city of Seattle. Once the query is executed, this is the resulting output:
Example 2: AND, ORDER BY and DESC Statements
In this example, we’ll refer back to the Customer table. This time, we’ll be more specific. Say you want to find out only the customer id (CustId), customer name (CustName) and outstanding balance (OSBal) from customers in Seattle who have an outstanding balance of at least $100. And you want that information to be ordered from the highest to the lowest amount.
SELECT CustId, CustName FROM Customer
WHERE City = ‘Seattle’ AND OSBal >= 100
ORDER BY OSBal DESC
Here you can see that we have used similar clauses shown in the first example. Although this time, SELECT is no longer followed by an asterisk. Instead, we’ve input specific columns that we want to target (CustId, CustName).
FROM stays static because the only table we’ll be referring to is Customer.
In this case, the WHERE clause has 2 criterions that need to be satisfied which is fulfilled by using the AND clause and relational operators (i.e., greater than or equal symbol >=, less than or equal <=, etc).
Lastly, to order the OSBal (Outstanding Balance) from highest value to lowest, the DESC clause is used. If we wanted lowest to highest instead, all we’d need to do is omit DESC.
This is the resulting output:
Example 3: INSERT INTO, UPDATE, DELETE Statements
This example will show how we’re able to manipulate data stored inside a table. The tables that we’ll be working with this time is Product.
The queries that will be executed will involve adding a new Product to the database, updating its data (changing it values) and lastly deleting the product added above.
Below is how the actual queries will look like in the database management program I used which is Microsoft SQL Server 2012 Express.
Adding a New Record
Adding a new record to a table requires the INSERT INTO and VALUES statement. It’s important to make sure that the inserted values are in the same order as the columns. Its syntax is described as follows:
INSERT INTO Table_Name (column1, column2, column3, column4, etc)
VALUES (‘value1’, ‘value2’, ‘value3’, ‘value4’, etc)
SELECT * FROM Table_Name
Note: If the value is quantitative, it does not need to be enclosed with apostrophes. To insert a date, however, it should follow this format ‘yyyy-mm-dd’.
This is how the output looks after executing the statements required to add a new record into a table:
Updating a Record
If we want to adjust its values, the syntax would then look like this:
SET ColumnName1 = NewValue, Column2 = NewValue
WHERE Some_Column = Some_Value
SELECT * FROM Table_Name
Comparing the state of row 3 below with the image above, we can see that the values for two columns (ReorderLevel, NextReorderDate) have been updated using the UPDATE statement.
Deleting a Record
Deleting a record requires very minimal effort. It uses the DELETE statement and its syntax is as follows:
DELETE FROM Table_Name
WHERE Some_Column = Some_Value
SELECT * FROM Table_Name
And there you go. The product that was previously on Row 3 in the previous example, uniquely identified with its product name, P0039429, has been deleted off the table.
Ready to give it a go?
Download the free Microsoft SQL Server 2012 Express program here and the sample Sales Database demo file used in the examples. After downloading, I highly recommend watching this YouTube video on how to attach the database files onto the Microsoft SQL application.
Leave them below and let’s discuss!