A database is described as a “persistent, organised store of related data”. It is not always a digital store of data – in fact, a wall calendar meets the conditions for a database.
For digital databases, data is organised into tables of data. Within the tables, the columns are known as fields, and the rows as records.
By organising data in this way, we can extract specific fields from a record.
Want to know the first name of the person with ID number 342? Easy! Just run a query.
When we talk about manipulating the data in a database, there are four main actions that can be performed on a database using a query. It’s useful to use the acryonym CRUD for this:
These actions can be performed using a Database Management System (DBMS) that allows us to enter in the criteria for our query, or by using code to type in the query. When we use the first method, the DBMS builds the code for us. The code that runs database queries is called Structured Query Language, or SQL. It is a very logical language that simply tells the computer what to do, not how to do it.
For example, if we wanted to query our superhero database that has a table called SUPERHEROS. With a query that should return the ID, FirstName, and Surname in reverse alphabetical order by Surname of all heros whose first name begins with A or who have a cape, we would create the query by design in this way:
Often, this will take longer to create than simply writing out the SQL query. However, because of the use of the table this visual way of designing a query can seem more logical. This same query n SQL would look like:
SELECT ID, FirstName, LastName
WHERE FirstName LIKE 'A%'
AND HasCape = 'Yes'
ORDER BY LastName DESC
SQL, like query by design has four commands:
INSERT – Creates new records in the database
SELECT – Reads the data from the database records
UPDATE – Updates existing data in a record
DELETE – Deletes entire records (not tables)