Thousands of organizations need technology professionals who have mastered Structured Query Language (SQL). Those with SQL developer skills can potentially land jobs in a variety of organizations and industries—especially if they have experience with huge relational databases.
Many hiring managers and recruiters want job candidates to prove their SQL developer skills with certifications such as the Oracle Database SQL Certified Associate certification and Oracle Database PL/SQL Developer Certified Professional certification. Whether or not you possess SQL-related certifications, though, the job-interview process will likely involve a series of highly technical questions designed to test your SQL developer skills.
If you’re considering a career as an SQL developer, training options abound. For example, Udemy has SQL courses that range in price from around $100 to $175, while Coursera lists numerous SQL courses in collaboration with major universities and colleges. There’s also this helpful offering from w3schools, which breaks down the various elements of SQL into “chapters.”
At its most fundamental level, though, mastering key SQL developer skills hinges on knowing how relational databases work. Let’s dive into relational databases that depend on SQL, such as SQL Server, Oracle and Db2 (as well as MySQL, PostgreSQL and SQLite).
What is a Database?
As you master SQL developer skills, you’ll learn there are many types of databases, such as NoNQL, graph or time-based—but the only ones we're interested in are relational databases. These hold the data in several tables. A table is like a spreadsheet with data held in rows of many columns.
The difference is that, in a relational table, the contents of each column have to be exactly the same type on each row. If column one holds integers, then every row in that table must have an integer in column one.
When you are retrieving data, you'll do a fair bit of searching. Imagine looking through a book to see which pages have the words “Humpty Dumpty.” You'd have to read every page, which is a slow process—but many books have an index at the back which tells you that “Humpty Dumpty” is found on pages 10,11 and 187. With relational databases, you can also add indexes to any column in a table to help you search; when you have tables with thousands or millions of rows, indexes are essential to make searches work in a short time.
File or Server Database?
Most relational databases run as servers and are accessed from either a standalone client or your software, connecting locally or over a network. SQLite is the exception here, as it is file-based and works directly on a file. File-based databases are better suited for single users or small numbers of users working concurrently; if you have a lot of users, then a server database is best.
When you use a database for manually storing, retrieving and manipulating data, then you'll need a database client. There are many options, both free and commercial. For example, many SQL developers opt for DBeaver, which works with MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server and more.
About SQL
There are two main sets of SQL instructions. One is for creating or deleting tables and databases, adding columns to a table and so on; the other set is for fetching, updating and deleting data. When you are learning SQL, the first commands you need to learn are those for manipulating and fetching data.
SQL Commands for manipulating data: There are four commands that you need to know: Insert, Update, Select and Delete. All four commands can apply to an entire table or to a subset of rows.
Let’s say we have a Table called Hours with three columns. The first column is a text field called datevalue, which holds the date. Next is an int called employeeId. The third column is the number of hours worked on that date by that employee. Here’s the SQL generated by DBeaver:
CREATE TABLE Hours (
datevalue text NOT NULL,
employeeid INTEGER,
hoursworked INTEGER
);
CREATE UNIQUE INDEX Hours_date_IDX ON Hours (datevalue,employeeid);
There can only be one entry for an employee on a given date so having a unique index enforces it. The ‘Not Null’ means that the date column always has a value. We're never going to insert data into this table without a date for each row.
Putting data into a table: Before we look at Select and Update SQL commands, we need data to work on. The easiest way to get lot of data is by entering it in a spreadsheet, saving as a CSV file (Comma Separated Value), then using a database client to import it.
The following represents data for three employees. We’re using a text field for holding dates because SQLite has a very limited range of column types; other databases will let you store dates as date types. (This page shows the data types for SQLite).
datevalue,employeeid,hoursworked
'2022-09-05',0,8
'2022-09-05',1,4
'2022-09-06',2,6
'2022-09-06',0,8
'2022-09-06',1,4
'2022-09-07',0,8
'2022-09-07',1,4
'2022-09-07',2,6
After the import data process, all eight rows are now in the table Hours.
Let's display the days worked for employee id = 0. This uses the select statement. Open a new SQL Script tab in DBeaver so you can enter a SQL query in it:
select date,hoursworked from Hours where employeeid=0;
This is parsed as select the specified columns from the table, with an optional where condition. Columns can be the actual named columns or * for all columns (the ‘where’ clause means it only shows the three rows for employeeid 0).
Inserting Data: An Insert SQL statement will let you insert data into tables (either single rows or multiple rows from other database tables). If I wanted to add an 8-hour record for employeeid 3 on September 8, the insert statement would look like this:
INSERT INTO Hours (datevalue, employeeid, hoursworked) VALUES('2022-09-08', 3, 8);
The values after the word VALUES correspond to the columns specified in brackets.
Deleting Data: Let’s fire employee 1 and remove their hours. For this, we use the delete statement:
delete from Hours WHERE employeeid = 1;
The where clause again lets you specify a subset of rows. We only want to remove the rows for employeeid 1. Run this query below after the delete and it will return 0, showing that the specified rows were deleted. Count tells you how many rows there are in the subset matching the where:
select count(*) from hours where employee=1;
Updating Data: This command lets you change values in one or more rows. Say we've got the working hours wrong for employee 2 on every day. They left early on those days so it should be four hours not six.
update Hours set hoursworked=4 where employeeid =2;
This sets hoursworked to 4 on every row where employeeid = 2.
One Key SQL Coding Trick to Remember
Imagine it's Friday evening, you are just about to leave and are asked to remove an employee from the database. But instead of that delete statement above, you accidentally leave out the where clause because you're in a rush. It's now just delete from Hours;
Unfortunately, that just deleted every employee's working hour’s records and there's no Undo! A full restore from backup is needed. Let’s hope it was a recent backup!
If you do any delete or update statement, you should run the following SQL command first.
Begin transaction;
Now you can do your update or delete. If you get it wrong, you just do a rollback; command and your mistake is wiped out. If it’s correct, then you do a commit; and your change is made permanent. This can save a lot of grief. Always remember: when you do a begin transaction; you MUST always do a corresponding commit; or a rollback;.
Conclusion
If you’re interested in mastering SQL developer skills, learning how to use DBeaver (or another database client of your choice) and the four SQL commands will get you started. All relational databases use the same SQL commands for creating databases and tables as well as manipulating data. Where they differ is the extensions in their SQL implementation that add more power but makes it harder to move between databases.
Related SQL Developer Jobs Resources:
