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: Limit and Order By

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

You can order the rows selected using order by clause, with the following syntax:

select ... from tableName where condition order by column asc|desc

SQL Fiddle

At times, you might only just want to return a certain number of rows.  This can be achieved by using Limit in MySQL.

select * from artiste limit 2

SQL Fiddle

For Sybase, it will be done by using Top instead of Limit.

 

SQL: NULL value

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

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

When creating a table, we can impose a not null value constraint to a column.

The attribute not null specifies that the column cannot contain the null value. NULL is a special value indicating “no value”.

As shown in the below screenshot, a error message column cannot null value is generated.

null

SQL: Select null value

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

If we have a table with null value, the row can be filtered out using is null or is not null.

CREATE TABLE artist
	(
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10)
 );

INSERT INTO artist
(artiste, album, year)
VALUES
(null, 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992);

SQL Fiddle

The query statement is:

select * from artist where artiste is null

SQL Fiddle

select * from artist where artiste is not null

SQL Fiddle

Note that you can’t use comparison operator (such as = or <>) to check for null.  This is a common mistake.

For example, the query below will return 0 record.

select * from artist where artiste is = null

SQL Fiddle

SQL: Functions

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

There are a slew of mathematical functions in SQL such as avg(), max() and sum.  We have already made use of count() which will return the total number of records.

Let’s look at some common functions in SQL.

CREATE TABLE artist
	(
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 price decimal(3, 2) 
 );

INSERT INTO artist
(artiste, album, price)
VALUES
('Rod Stewart', 'Maggie May', 8.90 ),
('New Order', 'Substance', 7.50),
('Nirvana', 'Smell Like Teen Spirit', 9.80);

SQL Fiddle

select sum(price) from artist
select avg(price) from artist
select max(price) from artist
select min(price) from artist
select count(price) from artist