Posted on

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
Posted on

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

Posted on

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
Posted on

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
Posted on

SQL: Insert Into

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

The are generally 2 ways to use insert into statement.

insert into tableName (column1, column2, column3..) values (value1, value2, value3..)
insert into tableName values (value1, value2, value3...)

In our earlier examples, we have already used method 1 and so let’s look at method 2.

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

insert into artiste
values
(0,'Rod Stewart'),
(0,'New Order');

Note that for the id column, it is an auto increment primary key value and we can put 0 to let the sql auto increment itself.

The 0 can also be replaced by using NULL.