SQL (Structured Query Language)

Code and Commands


Creating a Database

(Wikipedia) "SQL... is a special-purpose domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)."

SQL commands can vary from RDBMS to RDBMS, but some of the common ones include:

And so on.

Besides using something like Microsoft Access to artificially create databases, schemata (pl. of schema) and tables of data, entering commands into a console is the way in which to create a database from the data provided. It is necessary to have a clear idea of the structure of the database before creating it, and to speed things up, one may use an Excel spreadsheet to format the data and add it together before entering it into the database.

Some sample SQL code is as follows:

drop database if exists mtgDB;
create database mtgDB;
\c mtgDB

drop table if exists tblCards;

create table tblCards
(card_id varchar(5) not null primary key,
card_name varchar(35) not null,
artist varchar(35) not null,
colour varchar(5),
rarity char(1) not null

Firstly, this code makes the system erase any databases with the same name as the one we are going to create. Then it creates one called mtgDB, supposing that we are creating a set list of cards from the Magic: the Gathering trading card game. \c mtgDB now connects to this database, and \l lists it among the other databases present in the RDBMS (usually there will be a default database in PostgreSQL called postgres, which it is advisable not to delete because this is also the database server program!)

After creating the database, we will want to create a table in it that contains our data. DROP TABLE erases any existing table with the same name, and CREATE TABLE makes us a new one. The following attributes (or, "columns") are generated in the relation (or, table):

And so on; we could also list the mana cost for each card, which is the amount of magical energy used to play the card, and also the card text, which describes what it does. For the purpose of conciseness, let us just stick with the top five criteria for Limited Edition Beta, the second ever edition of the original game to be released. Here is our SQL code that we use to create the database, having created a table for each card's data to go in, and having copied all the data from an Excel spreadsheet to save much time: Magic SQL code. (Note how the SQL interpreter ignores white space.) As you may well observe, it would take too much time to enter all this into a text editor and then paste it into the SQL console, so what I did was to create the Excel spreadsheet first and then the data entry commands for the table. It is now a large table consisting of 302 cards, which are our records.

Here is a screen snip of the relation (table) as it is shown in expanded mode in psql:

PostgreSQL select all query

Figure 12: Records from the database. It has listed all the white cards first, but their artists, rarities and of course names all vary.

Running Queries

We can perform queries on the table in the database, which is just what it sounds like. Suppose we wanted to ask the database to show us all rare cards listed in alphabetical order. The code would be as follows:

select card_id, card_name, rarity
from tblCards
where rarity='R'
order by card_name, card_id;

And the result would be as follows:

PostgreSQL alphabetical/rarity query

Figure 13: Our first query. Note the parameters that we have specified in our code above.

Suppose we want to display all the basic lands in the set sorted in a) alphabetical order and b) by artist's name. This is the query:

select * from tblCards
where rarity='L'
order by card_name, artist, card_id;

PostgreSQL alphabetical/basic land query

Figure 14: The result. Note that in Magic, lands have no colour.

Up until now we have just been looking at a flat-file database (one that has only one relation/table) and running only very basic queries. The next few pages of this website will explore SQL in greater detail and what happens when we have two or more tables in a database, and the relationships between them.

Excursus: An Advanced Relational Database

Wizards of the Coast, the producers of this wonderful game, have a database dedicated to the entirety of the thousands and thousands of different Magic: the Gathering cards released since its inception in 1993 with Limited Edition Alpha, Beta and Unlimited. I chose Beta for my flat-file database; theirs is much more complicated, includes all editions, and can indeed search by mana cost, card text, and more.

The Gatherer database

Figure 15: Do you like card games? Check out http://gatherer.wizards.com/Pages/Default.aspx.


Figure 16: Example of a green (rare) card from Limited Edition Beta.
It costs one green mana (magic energy) to "cast," as indicated by the tree symbol in the top right corner.
Fastbond allows you to play as many lands as you want per turn... for a price.

© 2016-2020 Leo Coroneos
Certificate IV in Information Technology
South Regional TAFE, Albany WA Australia