Intro to SQL for the New Grad Developer

Ever since studying and working with databases 6 months ago, I’ve become fascinated with SQL and its relatively simple syntax. As a guide to the reader, I’ve gathered the essential criteria that employers expect you to know when applying for a database-related job.

In this blog, I will cover what SQL is, its different flavors and syntactical differences centered around an example query, the importance of EXPLAIN and SQL query runtime, and finally some example problems specifically in MySQL.

SQL

Structured Query Language, or SQL, is used to access and manipulate databases. Some basic functions of SQL include the ability to execute queries, modify data, create new tables, create new databases, and set permissions. There are several scenarios where SQL can be useful such as finding salaries for employees, fetching an nth record from a table, finding and deleting duplicate rows, finding table-specific information, duplicating tables, finding database names, and determining email validity. Types of jobs that require SQL include business analyst, data scientist, software engineer, database administrator, quality assurance tester, researcher, and journalist.

Now that you have a refresher on what SQL is, the scenarios it is used in, and the types of jobs that require SQL, it is easier to understand the impact it has on an employer. Next, we’ll dive into the flavors of SQL and get a glimpse of the major differences they have between each other. The more flavors of SQL you know, the more your potential employer will be impressed.

Flavors of SQL

The most popular flavors of SQL are MySQL, PostgreSQL, Oracle, and SQL Server.

Unique features of MySQL:

• Support for native replication topologies
• Session and global variables emulate functions and PL/SQL functionality
• Many storage engines

Unique features of PostgreSQL:

• Extensible
• Support for abstract data types
• Extends itself to include PL/pgSQL

Unique features of Oracle:

• Strong ACID compliance, high reliability, high durability, high resiliency
• Support for hierarchical queries

Unique features of SQL Server:

• Support for T-SQL
• Advanced query statistics

Now that we have finished covering the major differences between the SQL flavors, let’s dive into how they are syntactically different. Don’t worry! Syntactically, these languages only slightly differ.

Sample Query in MySQL, PostgreSQL, Oracle, and SQL Server

There is one example question presented below which will be answered by all languages in their respective syntax. Don’t worry, the data model is not important yet.

Count how many times “John” appears in the “Customers” table.

MySQL:

SELECT FirstName, COUNT(ContactName) AS occurrences 
FROM Customers
WHERE FirstName='john'
GROUP BY FirstName;

MySQL is case-insensitive, hence “John” can be spelled “john”, and the choice of single or double quotations does not matter. Because of its case-insensitivity, it is more convenient, which is why more example queries will be presented later in the blog.

PostgreSQL:

SELECT FirstName, COUNT(ContactName) AS occurrences 
FROM Customers
WHERE FirstName='John'
GROUP BY FirstName;

PostgreSQL is case-sensitive and double quotation marks are not allowed.

Oracle:

SELECT FirstName, COUNT(ContactName) AS occurrences 
FROM Customers
WHERE FirstName='John'
GROUP BY FirstName;

Oracle is case-sensitive and double quotation marks are not allowed.

SQL Server:

SELECT FirstName, COUNT(ContactName)=occurrences 
FROM Customers
WHERE FirstName='John'
GROUP BY FirstName;

SQL Server is case-sensitive and the “=” symbol is used in place of AS when assigning the result of an aggregate function to a variable.

See? Not too bad! Now, I’ll give a brief overview of EXPLAIN.

EXPLAIN in MySQL

EXPLAIN is used to provide a description of how SQL queries are executed and it is a tool to optimize slow SQL queries. EXPLAIN should be placed before SELECT, INSERT, UPDATE, etc. The EXPLAIN output is described below:

1. id: Represents the id of the query
2. select_type: Type of complexity
3. table: The name of the table
4. partitions: Number of partitions joined
5. type: Type of join
6. possible_keys: Possible keys used
7. key: Keys used
8. key_len: length of key used
9. ref: Reference tables listed
10. rows: Number of rows the query uses
11. filtered: The rows that are filtered from the WHERE clause
12. extra: Additional details

With the output explained above, it can help improve your queries if you focus on the “type” and “possible_keys” columns. Usually, the “type” should never be ALL if you are focusing on only generating the data you need. Also, indexes can be used to only focus on data from a table’s columns. In the example below, however, I will only demonstrate optimizing a type ALL query.

EXPLAIN Example

Let’s say we have a “sales” table and want to query for the countries and the type of item they sell, but instead we list all records using the asterisk (*):

SELECT * FROM sales;

Result:

53335 rows in set (0.25 sec)

This will produce unnecessary runtime (0.25 sec) and it will produce information that isn’t needed.

Instead, we should specify the columns we want to see:

SELECT Country, ‘Item Type’
FROM sales;

Result:

53335 rows in set (0.06 sec)

By specifying the columns needed, it reduces the runtime significantly (0.06 sec).

Runtime should always be on your mind as you write SQL queries. As an applicant, it will impress your potential employers by demonstrating efficient and fast querying.

Next, we will cover MySQL queries to refresh your brain as you prepare for an interview.

MySQL Example Queries

Take note of the tables below as some example questions will follow:

pokedex

Here you have a table that refers to some Pokémon in the Pokémon Trading Card Game (TCG):

pricing

Here you have some pricing information for cards in the Pokémon TCG:

SQL Example #1

Sum all the holo Pokémon cards for each set released between 2018 and 2022.

SELECT pokemonSet, COUNT(pokemonID) as holoQuantity 
FROM pokedex
WHERE cardType = 'Holo' AND dateReleased BETWEEN '2018-01-01' AND '2022-12-31'
GROUP BY pokemonSet
ORDER BY holoQuantity DESC;

The results for the above query can be seen below.

SQL Example #2

Group Pokémon by set and determine which set has more value. Use the JOIN operator in this example.

SELECT pokemonSet, SUM(PriceInDollars) AS price
FROM pokedex
JOIN pricing ON pokedex.pokemonID = pricing.pokemonID
GROUP BY pokemonSet
ORDER BY price DESC

The results for the above query can be seen below.

SQL Example #3

What Pokémon card in your dataset is worth the most? List the name and the price.

SELECT pokedex.pokemonID, PriceInDollars
FROM pricing
JOIN pokedex ON pokedex.pokemonID = pricing.pokemonID
WHERE PriceInDollars= (SELECT MAX(PriceInDollars) FROM pricing)

The results for the above query can be seen below.

Conlusion

You made it! With this information, you’ll be able to grasp the most important fundamentals of being in a SQL-related job. Use this blog as a study guide for interviews and when you land that job, you can always come right back here for a refresher.

Brandon Raboy

Brandon Raboy is a graduate of the University of the Pacific with a Bachelor of Science in Computer Science. He has 2 years of experience with Python and has written scripts using Python, Bash, and YAML syntax. With 6 months of experience working with SQL, he has modeled and managed several large databases. He also has 6 months of experience working with AWS and deploying, configuring, and managing tools such as Docker, Kubernetes, and Ansible on AWS instances.