SQL: Let there be select

This entry is part 16 of 20 in the series SQL Tutorial

At one time I was working on database reporting.  I found that I spent about 90% of my time typing select into the PL/SQL developer program that I was using.

Select, combining with a where clause, is the most common and the most important statement that we need to master.

String Pattern Matching – LIKE and NOT LIKE

For strings, we can perform pattern matching using operator like (or not like) with wildcard characters.

‘%’ matches any number of characters (including zero). For example,

IN, NOT IN

Another common and important operator is IN.  It is quite similar to = but with a cleaner code.

select * from artiste where year in (1973, 1992);

SQL: Update

This entry is part 14 of 20 in the series SQL Tutorial

Update allow us to modify data in a table.

In the achronym CRUD, we have looked at Create (insert), Retrieve (select).  Next in the list will be Update (update).

In sqlfiddle.com, we put insert statement on the left box.  The same goes to update and delete statement.

Syntax: update table set column = {value} where condition

create table artiste
	(
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10) 
 );

insert into artiste 
(artiste, album, year)
values
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992),
('Rod Stewart', 'Out of Order', 1988);

update artiste set album='Every Picture Tells A Story' where artiste='Rod Stewart';

CAUTION:

If the where clause is omitted in the update command, ALL ROWS will be updated.

Hence, it is a good practice to issue a select query, using the same criteria, to check the result set before issuing the update. This also applies to the delete statement in the upcoming posts.

The SQL fiddle without setting the where clause can be seen here.

SQL: char data type

This entry is part 13 of 20 in the series SQL Tutorial

char(3) is a fixed-length alphanumeric string of 3 characters.

If we create a table as below:

create table artiste
	(
 id int auto_increment primary key, 
 artiste varchar(30),
 category char(3)
 );

insert into artiste
(artiste, category)
values
('Rod Stewart', 'Rock'),
('New Order', 'Pop');

We could see that Rock has 4 characters which is 1 character bigger than char(3) that is defined for column category.

An error is generated as shown in the screenshot below.

char_3

A correct insert statement would be:

create table artiste
 (
 id int auto_increment primary key, 
 artiste varchar(30),
 category char(3)
 );
 
insert into artiste
(artiste, category)
values
('Rod Stewart', 'Roc'),
('New Order', 'Pop');

SQL: Data Types

This entry is part 12 of 20 in the series SQL Tutorial

When creating a table, we always have to decide on the right data type for use in the columns.

CREATE TABLE IF NOT EXISTS products (
         productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
         productCode  CHAR(3)       NOT NULL DEFAULT '',
         name         VARCHAR(30)   NOT NULL DEFAULT '',
         quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
         price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
         PRIMARY KEY  (productID)
       );

We define 5 columns in the table products.

The data types are:

  • productID is int unsigned – non-negative integers.
  • productCode is char(3) – a fixed-length alphanumeric string of 3 characters.
  • name is varchar(30) – a variable-length string of up to 30 characters.
  • quantity is also int.
  • price is decimal(7, 2) – a decimal number with 2 decimal places with a maximum of 7 digits.

The attribute not null specifies that the column cannot contain the null value.

We set the column productID as the so-called primary key. Values of the primary-key column must be unique.

Every table shall contain a primary key. This ensures that every row can be distinguished from other rows. An index is build automatically on the primary-key column to facilitate fast search.

SQL: Retrieving filtered data from a table

This entry is part 7 of 20 in the series SQL Tutorial

The most common task is to retrieve filtered data from a table.

SQL Select Syntax
select column_name,column_name
from table_name where column_name = ;

and

select * from table_name where column_name = ;
Example Table
IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992

If we have a table like above, we could ask ourselves questions like:

  1. What is the name of the album released by Rod Stewart?
  2. When did Rod Stewart release the album Maggie May?
  3. Who released the album Substance in the year 1987?

What is the name of the album released by Rod Stewart?

select album from artiste where artiste='Rod Stewart'

Answer:  Maggie May


When did Rod Stewart release the album Maggie May?

select year from artiste where album='Maggie May'

Answer:  1973


Who released the album Substance in the year 1987?

select album from artiste where year=1987

Answer:  Substance