Section 11: Databases and Software Development Flashcards
Chapter 64:
What does SQL stand for?
Structured Query Language.
Chapter 64:
What command is used to retrieve data from an SQL database?
SELECT.
Chapter 64:
What does the SQL command SELECT do?
Retrieves data from a database.
Chapter 64:
What is the syntax for an SQL SELECT statement?
SELECT column, another_column, …
FROM my_table;
Chapter 64:
What command is used to indicate the table that is to be searched in an SQL database?
FROM.
Chapter 64:
What does the SQL command FROM do?
Used to indicate the table that you want to search.
Chapter 64:
What command is used to filter searches in SQL?
WHERE.
Chapter 64:
What does the SQL command WHERE do?
Used to filter searches.
Chapter 64:
What is the syntax for SQL WHERE statement?
SELECT column, another_column, …
FROM my_table
WHERE condition;
Chapter 64:
What are the 4 main commands in SQL?
SELECT
FROM
WHERE
ORDER BY
Chapter 64:
What command is used to order the search results in an SQL database?
ORDER BY.
Chapter 64:
What does the SQL command ORDER BY do?
Orders the search results from an SQL database.
Chapter 64:
Write a general SQL query using SELECT, FROM, WHERE, and ORDER BY.
SELECT column, another_column, …
FROM my_table
WHERE condition AND/OR another_condition
ORDER BY field;
Chapter 64:
How do you select a whole table in an SQL query?
SELECT *
FROM my_table;
Chapter 64:
What does SELECT * do?
Selects all fields in a table.
Chapter 64:
There are 13 SQL conditions.
(6 Arithmetic, 4 General, and 3 Logic)
List them.
Arithmetic = Equal to. > Greater than. < Less than. >= Greater than, or equal to. <= Less than, or equal to. <> Not equal to.
General
IN Equal to a value in a set of values.
LIKE Similar to.
BETWEEN … AND Within a range (inclusive).
IS NULL Field doesn’t contain a value.
Logic
AND Both expressions must be true.
OR Either expression must be true.
NOT True if false, false if true.
Chapter 64:
In SQL, how do you SELECT Fields from multiple different tables?
SELECT table1.field, table2.field
You can just use field if the field name is not defined in another table.
Chapter 62:
What is a Data Entity?
A Data Entity is a category of object, person, event, or thing of interest to an organisation about which data is to be recorded.
Examples: Employee, Actor, Recipe, Player Character.
Chapter 62:
What is a Data Attribute?
Data Attributes are the data categories that make up a Data Entity.
A characteristic of an object.
Examples: Location, Type, Health, Velocity.
Chapter 62:
A Dentist’s Surgery employs several dentists, and has many patients. They book appointments for patients to see specific dentists at specific times.
What are the Entities, and what are the Attributes in a database for this scenario?
Entities:
Dentists, Patients, Appointments.
Attributes of Dentists:
Dentist ID, Firstname, Surname, Qualification, Phone Number…
Attributes of Patients:
Patient ID, Firstname, Surname, Dentist ID (?), Address, Phone Number…
Attributes of Appointments:
Appointment ID, Dentist ID, Patient ID, Date, Time…
Chapter 62:
What form do Entity Descriptions usually take?
Entity1 ( Attribute1, Attribute2… )
Chapter 62:
What is an Entity Identifier?
An Attribute that is unique for every Entity in the database.
Known as a Primary Key in a Relational Database.
In most cases, the Entity Identifier / Primary Key will be an arbitrary ID, rather than another field (e.g. Name) as they can conflict (i.e. different people can have the same name).
Chapter 62:
Is a National Insurance Number a suitable Primary Key for a Patient?
Why?
No.
National Insurance numbers are issued to residents of their country when they turn 16.
This means that Patients under the age of 16 and/or not originating from the operating country will not have a National Insurance Number.
{
Patients often don’t know their National Insurance Number either.
}
Chapter 62:
What are the three different types of relationship between Entities?
one-to-one
one-to-many
many-to-many
