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: Insert current time or timestamp

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

It is common for developer to insert a timestamp into each record marking the time a record is created.

With timestamp, we could for example, know when a purchase of an item is made.

In the example below, we make a slight modification to the example sql fiddle and add a time that a record is created.

CREATE TABLE supportContacts 
	(
 id int auto_increment primary key, 
 type varchar(20), 
 details varchar(30), 
 current datetime
 );

INSERT INTO supportContacts
(type, details, current)
VALUES
('Email', 'admin@sqlfiddle.com', now()),
('Twitter', '@sqlfiddle',now());

Notice that we use a variable current with a datetime datatype.  The timestamp is inserted by now().

Sometimes, we use select now() to check the current time of the server system.

SQL: More Select Examples

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

Using the same table, let’s try more select statements.

If you work in the IT industry, and there are databases in your applications, being proficient with querying the database could become crucial to your daily work.

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);
IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992
4Rod StewartOut of Order1988

SQL: Select distinct

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

For most real world tables, there will be repeated values similar to the table below.

IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992
4Rod StewartOut of Order1988

Using select count, we will have 4 records in the table.

However, we know that we only have 3 artistes in this table.  How do we correctly display the 3 artistes?  We use select distinct query statement.

SELECT DISTINCT column_name,column_name
FROM table_name;

select distinct artiste from artist

To count the unique artistes:

select count(distinct artiste) from artist

SQL: Select count

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

IDartistealbumyear
1Rod StewartMaggie May1973
2New OrderSubstance1987
3NirvanaSmell Like Teen Spirit1992

In a database table, we can use select count to retrieve the number of records in a table.

Syntax:  select count(*) from table_name

select count(*) from artiste

We can also use the syntax where we count a particular column such as id or album.

select count(id) from artiste
select count(album) from artiste