SQL lessons for NoSQL developers

Because no software developer is complete without at least a passing knowledge of the world’s most popular query language

1 2 3 Page 3
Page 3 of 3

SQL insert statement

Before we insert a new product, let’s look at an existing product. Find some tofu.

select * from products where product_name='Tofu';
product_id | product_name | supplier_id | category_id | quantity_per_unit | unit_price | units_in_stock | units_on_order | reorder_level | discontinued
------------+--------------+-------------+-------------+-------------------+------------+----------------+----------------+---------------+--------------
         14 | Tofu         |           6 |           7 | 40 - 100 g pkgs.  |      23.25 |             35 |              0 |             0 |            0
(1 row)

Now let’s get the max product_id so that we don’t end up duplicating a primary key (product_id) in the new record.

select max(product_id) from products;  
-----
  77
(1 row)

Now based on the earlier row, we’ll insert a new product, Extra Firm Tofu, with a product_id of 78.

insert into products values(78,'Extra Firm Tofu',6,7,'14 oz', 1.99, 50, 0, 0, 0);
INSERT 0 1

Now if we select from tofu where product_id=78 we should see the new row:

select * from products where product_id=78;
 product_id |  product_name   | supplier_id | category_id | quantity_per_unit | unit_price | units_in_stock | units_on_order | reorder_level | discontinued
------------+-----------------+-------------+-------------+-------------------+------------+----------------+----------------+---------------+--------------
         78 | Extra Firm Tofu |           6 |           7 | 14 oz             |       1.99 |             50 |              0 |             0 |            0
(1 row)

Again, doing the select from products first was just so that you do not have to look up categories or any of the other field values, but can just use an existing Tofu record is a template. Looking up the max(product_id) was so that you could add one to it and generate a new primary key. This is not a “safe” way to do this in a system with multiple users, but that is a much longer topic.

The insert statement we used here is the simplest form, just using the values clause and setting the column values in order. However the “correct” or cleaner way is to include the field names in a separate clause. This is safer especially if the table definition changes. These concerns are irrelevant in a simple dataset with one user. In other words:

select * from products where product_name='Tofu';
  • In order to avoid trying to figure out what product category or supplier to pick, just select an existing row to use as a template.
select max(product_id) from products;
  • The product_id is the primary key. It cannot be a duplicate, so just increment it.
  • Danger – this is not the way to generate a primary key in production code (a topic for another day).
insert into products values(78,'Extra Firm Tofu',6,7,'14 oz', 1.99, 50, 0, 0, 0);
  • Inserted in order... but this is bad hygiene.
insert into products (product_id,product_name,supplier_id,category_id,quantity_per_unit,unit_price,units_in_stock,units_on_order,reorder_level,discontinued) values(78,'Extra Firm Tofu',6,7,'14 oz', 1.99, 50, 0, 0, 0);
  • This is the “right" way.

If not included the column will be “null.”

SQL left outer join

In addition to inner joins, there are outer joins. Perhaps you want a list of the products whether or not they are included in an order, and you want to count how many orders there are for a given product. For example, it so happens that 33 orders contain Pâté chinoise, but no orders have Extra Firm Tofu.

select p.product_name, count(d.order_id) from products p left outer join order_details d on p.product_id=d.product_id group by p.product_name;
           product_name           | count
----------------------------------+-------
 Pâté chinois                     |    33
 Camembert Pierrot                |    51
 Inlagd Sill                      |    31
 Gustaf's Knäckebröd              |    14
 Thüringer Rostbratwurst          |    32
 Carnarvon Tigers                 |    27
 Röd Kaviar                       |    14
 Rhönbräu Klosterbier             |    46

 Extra Firm Tofu                  |     0

 Gumbär Gummibärchen              |    32
 Original Frankfurter grüne Soße  |    38
 NuNuCa Nuß-Nougat-Creme          |    18
 Chang                            |    44
 Manjimup Dried Apples            |    39
 Escargots de Bourgogne           |    18
 Chef Anton's Cajun Seasoning     |    20
 Ikura                            |    33
 Uncle Bob's Organic Dried Pears  |    29
(78 rows)

A left outer join includes everything from one table, whether or not it occurs in the other table… However, it only includes the rows in the other table (B, orders) if they are in the first table (A, products). In this case the query includes all of the products, but only orders for those products.

select p.product_name, count(d.order_id) from products p left outer join order_details d on p.product_id=d.product_id group by p.product_name;
  • count(d.order_id) – count the number of orders for the product_name
  • left outer join order_details d on p.product_id=d.product_id – join products against the order_details but still include products that have no records in order_details

SQL update statement

Finally, let’s change the name of the Extra Firm Tofu to Super Firm Tofu.

update products set product_name='Super Firm Tofu' where product_id=78;

The update statement just takes the table and a comma separated list of fields in the set clause. The where clause works essentially the same as in a select statement.

SQL delete and drop

Now let’s delete the tofu…

Further, you may decide that you want to not only delete a table but remove its definition. For that you can drop it. (Do not do this if you do not want to reload everything before querying.)

If you disconnect from the Northwind database… by connecting to a different database... you can drop the entire database. (Again, do not do this unless you want to start over completely.)

For PostgreSQL-compatible databases, \q quits. 

delete from products where product_id=78;
                            DELETE 1
drop table us_states;
DROP TABLE
\c postgres
You are now connected to database "postgres" as user "yugabyte".
postgres=# drop database northwind;
DROP DATABASE
\q

The where clause in the delete statement is indeed the same as used for the select statement. Deleting by primary key ensures just the single record is deleted.

  • delete from products where product_id=78; – similar to a select statement but the row is deleted from the table

drop table drops a whole table…

  • drop table us_states; – does not just delete the rows but the whole table definition

If you want to drop a database you must connect to a different database because you cannot drop the database you are connected to. This is not SQL but a ysqlsh or psql shell thing. 

drop database drops a whole database... 

  • drop database northwind; – drops not just the data but the table definitions
  • \c postgres – run this to connect to a different database (the default) because you cannot drop a database that you are connected to
  • drop database northwind; – everything is gone... back to before you started

What next?

So many things... 

SQL tutorials and resources

SQL is a whole world

SQL is not just a database query language but an ecosystem of tools and technologies. Everything from Excel to Tableau to Microsoft Access can connect to and query SQL databases or use them as a source. There are scores of people who cannot code and do not know JavaScript or the MongoDB Aggregation Pipeline but know SQL.

The wonderful thing about SQL is that it is a very terse and simple language. There are many comparative queries that are a line or two of SQL that are many lines in a NoSQL database’s custom query language. There are even NoSQL databases, like Couchbase, that support a SQL dialect. And many GraphQL frameworks integrate with one or more SQL databases, usually through an object-relational mapping tool.

So really, no developer is complete without at least a passing knowledge of the world’s most popular query language.

Copyright © 2020 IDG Communications, Inc.

1 2 3 Page 3
Page 3 of 3
How to choose a low-code development platform