Examples

Here you can find examples for each chapter. Just select desired chapter from the list. All examples are written for one concrete database that's definition you may find below.

• SELECT
• INSERT
• UPDATE
• DELETE
• CREATE
• ALTER
• DROP
• GRANT/REVOKE
• Stored procedures
• Transactions

Example's database definition

The database handles a single catalog of audio CDs. It consists from two tables called 'album' and 'song'.

Definition of the table 'album'

This table holds information about audio CDs, as there is one album on one audio CD.

Definition of the table 'song'

This table holds information about songs on albums.

The SELECT statement

Example #1

Select all albums of Elvis Presley and sort them descending by the date of release.

SELECT * FROM album WHERE interpreter = 'Elvis Presley' SORT BY released DESC

Example #2

Select name and notes of album where the 'Only You' song is recorded and performed by Elvis Presley.

SELECT a.name, a.notes FROM album a, song s 
WHERE s.name = 'Only You' AND s.album = a.id AND a.interpreter = 'Elvis Presley' 

Example #3

Select all songs at which the name begins with 'Th'

SELECT * FROM song WHERE name LIKE 'Th%'

Example #4

Select all notes of albums, which don't begin with '50%'.

SELECT notes FROM album WHERE note NOT LIKE '%50@%%' ESCAPE '@'

Example #5

Select minimum, average and maximum lengths of songs of each of interpreters where the song length is not between 2:00 and 2:40 minutes.

SELECT a.interpreter, MIN(s.length) as 'Minimum', MAX(s.length) as 'Maximum', AVG(s.length) as 'Average'
FROM album a, song s
GROUP BY interpreter HAVING length BETWEEN 0:2:00 AND 0:2:40

Note that text format of time generally depends on locale settings.

Example #6

Select all dates when any album has been released and handle cases of not entered values.

SELECT DISTINCT NVL(released, 'Not all dates were entered!') FROM album

Top of the page

The INSERT statement

Example #7

Insert new album of Elvis Presley and by filling only id, interpreter and name.

INSERT INTO album (id, interpreter, name) VALUES (1, 'Elvis Presley', 'Love Me Tender')

Example #8

Insert the song 'Are you lonesome tonight?' for this album and fill all attributes.

INSERT INTO song VALUES (1, 1, 'Are you lonesome tonight?', 'My almost most favorite song', 0:3:06)

Note that text format of time generally depends on your locale settings.

Top of the page

The UPDATE statement

Example #9

There are some albums where the interpreter is 'Elvis'. Correct it to 'Elvis Presley'.

UPDATE album SET interpreter = 'Elvis Presley' WHERE interpreter = 'Elvis'

Top of the page

The DELETE statement

Example #10

There is the song 'Bad Medicine' in the table 'song' and belongs, in this database, to the album 'Top Ten Hits' by Elvis Presley, but this song is by Bon Jovi and there is no their album in the table 'album'. Delete this song.

DELETE FROM song WHERE name = 'Bad Medicine'

Top of the page

The CREATE statement

Example #11

Create the tables 'album' and 'song'.

CREATE TABLE album 
(id INTEGER PRIMARY KEY, name VARCHAR(80), interpreter VARCHAR(80),
notes LONG VARCHAR, released DATE)

CREATE TABLE song
(id INTEGER PRIMARY KEY, album INTEGER NOT NULL,
name VARCHAR(80), notes LONG VARCHAR, length TIME,
CONSTRAINT albumkey FOREIGN KEY (album) REFERENCES album(id))

Note that the (album) is the 'song' table's attribute, while the album(id) means the attribute 'id' of the 'album' table.

Example #12

Create a view containing only albums of Elvis Presley.

CREATE VIEW ElvisAlbums AS SELECT * FROM album WHERE interpreter = 'Elvis Presley'

Top of the page

The ALTER statement

Example #13

There is a name of CD manufacturer on each CD. Add this attribute into the table 'album'.

ALTER TABLE album ADD manufacturer VARCHAR(50)

Example #14

Because the name of manufacturer is useless, delete this attribute from the example #13.

ALTER TABLE album DROP manufacturer

Top of the page

The DROP statement

Example #15

You are closing the whole CD catalog. Delete both tables.

DROP TABLE song

DROP TABLE album

Note that the table 'song' must be delete first because it contains reference to the second table.

Top of the page

The GRANT and REVOKE statements

Example #16

As you may noticed, the CD catalog mainly contains albums of the rock'n'roll king Elvis Presley. He, as the king, should have adequate rights. Give them to him; his user name is Elvis.

GRANT SELECT, INSERT, UPDATE, DELETE ON album, song TO Elvis WITH ADMIN OPTION

Example #17

Because the database may be accessed by a wide public, grant them only the SELECT statement.

REVOKE INSERT, UPDATE, DELETE ON album, song TO PUBLIC

Example #18

Create a group of users, which can add and modify records, but not delete them.

CREATE ROLE editors

GRANT SELECT, INSERT UPDATE ON album, song TO editors

Example #19

You have got new guy in your team and you want to authorize him to delete records. His user name is JBlack. Take an advantage of the example #18.

GRANT editors, DELETE ON album, song TO JBlack

Example #20

Once again you are alone to maintain the CD catalog, because all your co-workers are gone. Only the editors role is left and useless. Delete it.

DROP ROLE editors

Top of the page

Stored procedures

Example #21

There is a procedure named GetAlbum that returns the value of the attribute 'id' of the table 'album' for a record represented by interpreter and album name. Get the primary key for the 'Top Ten Hits' album by Elvis Presley.

{CALL GetAlbum('Elvis Presley', 'Top Ten Hits')}

Note that the result will be displayed and then discarded.

Top of the page

Transactions

Example #22

You are gonna to give the album 'Greatest Hits' by Roxette to your best friend as a birthday present. Delete all songs for this album and it itself from the catalog. Use the procedure from the example #21.

Here you have to start the transaction with taken of specific features of your DBMS.

DELETE FROM song WHERE album = GetAlbum('Roxette', 'Greatest Hits')

DELETE FROM album WHERE id = GetAlbum('Roxette', 'Greatest Hits')

COMMIT

Note that in case of failure of any used statement, you have to use ROLLBACK. Some database engines don't support direct using of COMMIT and ROLLBACK.

Top of the page