Databases

• A database is an example of application software to store and manipulate data.
• A table is a set of data about one type of object.
• A field is an individual piece of data being stored about an object.
• A record is a collection of these fields about one object. An example is all the personal details about a single student, because the fields are categories like date of birth.
• Text is a sequence of letters, numbers, or symbols.
• Integers are whole numbers.
• Date/Time can store either just a date or a date and a time.
• Boolean holds a value which is either true or false.
• Reals are numbers with a decimal place in them.
• A primary key is a unique identifier for a record. They can be used to uniquely identify each record.
• Structured Query Language (SQL) is a standard language to define and manipulate databases.
• A script is a set of statements that are executed.
• To start a query, you need to define what fields you want to show, and from what table. For example:

  SELECT fieldnames

  FROM table;

  Remember to add a semicolon at the end. And if you want to select all fields use ‘*’.
• To select specific data from a table, you need to add a WHERE. And you use the same operators as programming (=, <=, >=, <, >, <>). For example:

  SELECT fieldnames

  FROM table

  WHERE condition; (could be WHERE Type = “Comedy”)
• To order the data in a particular order, ascending or descending use ASC or DESC. For example:

  SELECT fieldnames

  FROM table

  ORDER BY fieldname ASC/DESC;
• You can also count the amount of records in a database, using COUNT(). For example:

  SELECT COUNT(ItemNumber)

  FROM SHOES;

  This would give the number of records in ItemNumber. You can change the name of the field that has this number by adding AS nameofchoice to the first line.
• You can also sum the total value of a field, like price, using SUM(). For example:

  SELECT SUM(Price) AS TotalPrice

  FROM SHOES

  WHERE Size=5;

  This would give a field called ‘TotalPrice’ with the amount of money of shoes size 6 in it.
• You can use the AVG(), MIN(), and MAX(), in the first line too.
• AND is used for only data that meets both conditions will be returned. For example:

  SELECT fieldnames

  FROM table

  WHERE field1 = True AND field2 < 3.99;
• OR is used for data to meet one condition, or another condition. If a record meets both then this will be returned only once. For example:

  SELECT fieldnames

  FROM table

  WHERE field1 = True OR field2 < 3.99;