SQL Introduction

Here is an introduction to writing SQL statements rather than using the user interfaces to build objects for you, insert, update, delete and query data within them.
It’s targeted at SQL Server, but with a little modification will work for My SQL.
Creating a database
First let’s create a database. Open SQL Server Management Studio and click ‘New Query’ and paste the following statement into the query windows and click ‘Execute’.
CREATE DATABASE tracks;
Then let’s switch to that database so we can work in it.
Use tracks
Command(s) completed successfully is always a good sign.
Creating tables
CREATE TABLE dbo.tracks (
track_id int IDENTITY(1,1) PRIMARY KEY,
track_name varchar(255) NULL,
track_release_date DateTime NULL,
label varchar(255) NULL
);
Inserting Data
Add some data to the newly created table. Notice on the insert we don’t pass in track_id, as this was setup as a IDENTITY (identity seed) that will be automatically generated for us. Also I’m using GetDate() here to fill in the release date with the current date and time.
INSERT INTO dbo.tracks
(track_name, track_release_date, label)
VALUES ('my song', GetDate() ,'my label')
(1 row(s) affected) so let’s add another track.
INSERT INTO dbo.tracks
(track_name, track_release_date, label)
VALUES ('your song', GetDate() ,'another label')
Selecting Data
In the simpliest form we can do the following query to extract data from the newly created tracks table.
SELECT * FROM dbo.tracks
We can be specific about the information we want to return.
SELECT * FROM dbo.tracks WHERE track_id = 1
We can return multiple rows
SELECT * FROM dbo.tracks WHERE track_id IN (1,2)
Ordering Data
SELECT * FROM dbo.tracks ORDER BY track_name
The following statement will order the track names in descending order.
SELECT * FROM dbo.tracks ORDER BY track_name DESC
Updating Data
Now we know the track_id on the newly created entries we can write some SQL to update the data in one of the columns.
UPDATE dbo.tracks SET track_name = 'my song updated' WHERE track_id = 1
If you run the following SELECT statement you’ll see the updated changes.
SELECT * FROM dbo.tracks
Let’s remove the data we’ve added to the tracks table and add another table to show us how to create a relationship between two tables.
The following statement will delete all tracks from the table.
DELETE FROM dbo.tracks
(2 row(s) affected)
Altering Tables
Lets run the following two statements to create a relationship between our tracks table and newly created table called artists. First we’ll add a artist_id field to our tracks field. This will be the column for us to associate a track with an artist.
ALTER TABLE dbo.tracks
ADD artist_id int NOT NULL;
Let’s create an artists table to hold our artists
CREATE TABLE dbo.artists (
artist_id  int IDENTITY(1,1) PRIMARY KEY,
artist_name varchar(255) NULL,
artist_web_site varchar(500) NULL
);
INSERT INTO dbo.artists 
(artist_name, artist_web_site)
VALUES ('an artist', 'http://www.somewebpage.com')
(1 row(s) affected)
We can now create a relationship between tracks and artists to ensure when we release an artist to a track the artist has to exist in the artists table.
ALTER TABLE dbo.tracks
ADD FOREIGN KEY (artist_id) REFERENCES artists(artist_id);

ALTER TABLE dbo.tracks
ADD CONSTRAINT FK_ArtistTrack
FOREIGN KEY (artist_id) REFERENCES artists(artist_id);
If we were to look at the a diagram of the database right now it would look like this.
track-artist-diagram
Now, let’s insert our track again into the tracks table.
INSERT INTO dbo.tracks
(track_name, track_release_date, label)
VALUES ('my song', GetDate() ,'my label')
Well that failed!
Cannot insert the value NULL into column ‘artist_id’, table ‘tracks.dbo.tracks’; column does not allow nulls. INSERT fails.
This is because we’ve said artist_id cannot be null so we must provide a value.
INSERT INTO dbo.tracks
(track_name, track_release_date, label, artist_id)
VALUES ('my song', GetDate() ,'my label', 1)
Run the following against and see the artist id has been set to 1
SELECT * FROM dbo.tracks
Try and run this we will just set the artist id to 1 again and this will be allowed.
UPDATE dbo.tracks SET artist_id = 1 WHERE track_id = 4
If we try and run this the insert will fail as artist_id 2 doesn’t exist.
UPDATE dbo.tracks SET artist_id = 2 WHERE track_id = 4
The UPDATE statement conflicted with the FOREIGN KEY constraint “FK__tracks__artist_i__398D8EEE”. The conflict occurred in database “tracks”, table “dbo.artists”, column ‘artist_id’.
Deleting Related Data
Let’s try and delete an artist now
DELETE FROM dbo.artists WHERE artist_id = 1
The DELETE statement conflicted with the REFERENCE constraint “FK__tracks__artist_i__25869641”. The conflict occurred in database “tracks”, table “dbo.tracks”, column ‘artist_id’.
This fails because artist_id 1 is in use against a track in the tracks table. So we need to clear out any tracks related to that artist.
DELETE FROM dbo.tracks WHERE artist_id = 1
and then execute the following for that to work.
DELETE FROM dbo.artists WHERE artist_id = 1
SQL to determine duplicate values
Let’s insert another couple of songs, but the same title as the original one we created.
Add the artist first
INSERT INTO dbo.artists 
(artist_name, artist_web_site)
VALUES ('an artist', 'http://www.somewebpage.com')
Then the tracks
INSERT INTO dbo.tracks
(track_name, track_release_date, label, artist_id)
VALUES ('my song', GetDate() ,'mylabel', 2)

INSERT INTO dbo.tracks
(track_name, track_release_date, label, artist_id)
VALUES ('my song', GetDate() ,'another label', 2)
Here is a useful snippet of sql that I use on a regular basis. Find duplicate values in a specific column in a table. Just replace <columnname> with the column name you are looking for the duplicate values.
SELECT     track_name, COUNT(*) AS 'Duplicate Count'
FROM         dbo.tracks 
GROUP BY track_name 
HAVING      (COUNT(*) > 1)
This will show me I have two tracks named ‘my song’ in the table.
Simply SQL Join
How about if we want to know what artist artist_id? Well because artist_id cannot be null, we can always assume artist_id is filled. We can do an inner join onto the artists table to see the artist name.
SELECT tracks.artist_id, track_name, artists.artist_name FROM dbo.tracks
INNER JOIN dbo.artists ON dbo.tracks.artist_id = dbo.artists.artist_id
Run this and to prove it is working we’ll update the artist on the 2nd track.
INSERT INTO dbo.artists 
(artist_name, artist_web_site)
VALUES ('another artist', 'http://www.someotherwebpage.com')
Let’s run this query to get the artist_id of the newly created artist.
SELECT * FROM dbo.artists
So we can update track 2 with artist_id 3. Let’s run an update statement
SELECT * FROM dbo.tracks
We can see the 2nd track has track_id 2. So if we execute…
UPDATE dbo.tracks SET artist_id = 3 WHERE track_id = 6

Running the INNER JOIN query again you can see we have two tracks from two different artists.

SELECT tracks.artist_id, track_name, artists.artist_name FROM dbo.tracks
INNER JOIN dbo.artists ON dbo.tracks.artist_id = dbo.artists.artist_id
Clean Up
Let’s clean up our test data and tables.
DELETE FROM dbo.tracks
DELETE FROM dbo.artists

DROP TABLE dbo.tracks
DROP TABLE dbo.artists
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s