Structured Query Language (SQL) is an easy-to-learn, high level language that you’ll find being used pretty much wherever you come across relational databases. It is used in essentially two ways: building databases and retrieving data from these databases.
That is, we can divide the language into two broad categories:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
Data Definition Language
Data Definition Language is what we use when we are creating, altering, or removing database objects. For instance:
Suppose we are creating a database for a website that allows users to sign up and listen to music (a Spotify clone).
Let’s start by creating a really simple table on the default schema:
This table would contain a record for every single song in our database.
The column data types could be defined as:
And if we were to look at the data in
dbo.Song, it could look something like:
|1||Under Pressure||Queen & David Bowie|
|2||Billie Jean||Michael Jackson|
|3||The Winner Takes It All||ABBA|
CREATE TABLE dbo.Song ( ID int IDENTITY(1,1) PRIMARY KEY , Name nvarchar(100) , Artist nvarchar(100));
Notice we have also added a couple things:
IDENTITYproperty: values of
IDstart at 1 and increment by 1.
PRIMARY KEYconstraint: each value must be unique and not contain any
Now the values of
ID will be generated automatically and correctly as the table grows.
One less thing to worry about.
Now say we’ve decided that we don’t like having the Artist field in
dbo.Song because we think Artists should get their own table.
So we will remove it from our table using
ALTER TABLE dbo.Song DROP COLUMN Artist;
But we’ve also realised that it would be helpful if we could store the duration of each song in this table instead.
Let’s measure the duration in seconds so we can store this as an integer.
ALTER TABLE dbo.Song ADD Duration int;
Now our the schema of our table looks like:
To remove this table from our database:
DROP TABLE dbo.Song;
To drop and immediately recreate the table:
TRUNCATE TABLE dbo.Song;
Note: This performs an operation that produces an outcome very similar to a
DELETE statement (details below).
The key difference is that
TRUNCATE is DDL, whereas
DELETE is DML.
This means you can rollback a
DELETE statement. But not a
Data Manipulation Language
Data Manipulation Language is what we use when are working with the data itself. For example, this could mean :
- returning data with
- populating a table with
- changing data with
- removing data with
SELECT statement is our bread and butter in the SQL world (ed: why is it not at the top of this page then?).
And there are a lot of way of augmenting it in order to define exactly what data we want it to return.
But in its most basic configuration,
SELECT looks like this:
SELECT * FROM dbo.Songs;
|3||The Winner Takes It All||295|
|5||Take On Me||225|
SELECT implies that `dbo.Songs’ only has 5 songs.
Let’s populate it with more songs.
INSERT INTO dbo.Song VALUES ('The Tide Is High', 231) , ('Red Red Wine',182) , ('Do You Really Want To Hurt Me',263) , ('Relax',238) , ('Gold',231);
Now, manually entering data into our table in this fashion is really slow and tedious.
Suppose I’ve got another table,
dbo.moreSong which has many songs in it and has both columns I need:
INSERT INTO dbo.Song SELECT SongName , Duration FROM dbo.moreSong;
This essentially copies everything from
SELECT INTO is an interesting example of syntactic sugar.
It both DDL and DML because it comprises
Put simply, allows us to easily copy data from one table into a new table.
SELECT * INTO dbo.newSongTable FROM dbo.Song;
UPDATE, we can change the values in our table to something else.
Let’s rename Billie Jean to Thriller.
UPDATE dbo.Song SET 'Thriller' WHERE Name = 'Billie Jean';
Suppose it turns out we’re not allowed to have any Thriller in our database (the lawyers say no). And we have to delete it from Songs.
DELETE dbo.Song WHERE Name = 'Thriller';