SQL Database Concepts: Tables, Keys, and Queries for Data Retrieval

Database

A structured collection of data

Table

A collection of related data entries. This data is held in rows and columns, like a spreadsheet.

Record

A row in a table - one complete set of data about a single item

Field

A column in a table - stores one type of data (e.g. name, ID)

Primary Key

A field (or combination of fields) that uniquely identifies each record in a table.

Foreign Key

A field (or combination of fields) in one table that links to the primary key in another table.

Composite Primary Key

A primary key formed by combining multiple field types when a single field cannot uniquely identify records.

SQL

A language used to search for, manage, and manipulate data in a relational database.

SELECT Command

Used for retrieving data from a database table.

UPDATE Command

Used to modify existing data in a database table.

INSERT Command

Used to add new data into a database table.

DELETE Command

Used to remove data from a database table.

ORDER BY Clause

An optional clause in SQL that specifies the order in which to return the results.

Condition in SQL

A statement that must be true for the SQL command to execute.

Attribute in SQL

A specific column in a table that is being queried.

Table: Flights

Contains data about flight numbers, pilot numbers, and destinations.

Table: Pilots

Contains data about pilot numbers and pilot names.

PilotNo

The primary key in the Pilots table.

FlightNo

The primary key in the Flights table.

Data Inconsistency

Occurs when data points are stored in more than one place, but with different values.

Data Redundancy

Occurs when data is unnecessarily duplicated in more than one place.

Linked Tables

Tables that are connected through shared attributes, such as primary and foreign keys.

Destination

The location where the flight is headed.

Wildcards

Special characters used in SQL commands to specify any possible value.

Table: Students

A database table containing information about students.

Table: Cars

A database table containing information about cars.

Attributes in SQL

The columns in a database table that define the data stored.

Values in SQL

The actual data entries corresponding to the attributes in a database table.

SQL keywords

Reserved words in SQL that have special meaning.

Database Record

A single entry in a database table.

Email

A field in the Students table that stores the email address of a student.

Year in Students Table

A field indicating the academic year of a student.

Model in Cars Table

A field indicating the model of the car.

Manufacturer in Cars Table

A field indicating the manufacturer of the car.

Price in Cars Table

A field indicating the price of the car.

Wildcard

A symbol used in SQL to return all attributes, usually notated with an asterisk.

Join criteria

A statement used to link two tables in a SQL query, typically by setting the foreign key of one table equal to the primary key of another.

Attribute specific to a table

Denoted as in SQL.

Example 1: Selecting a Field from One Table

SELECT Students.StudentNo FROM Students, Teachers WHERE Students.TeacherID = Teachers.TeacherID

Example 2: Selecting multiple Fields from Both Tables

SELECT Students.Name, Teachers.Name FROM Students, Teachers WHERE Students.TeacherID = Teachers.TeacherID

Example 3: Filtering With a Condition from Another Table

SELECT Students.Email FROM Students, Teachers WHERE Teachers.Class = '13D' AND Students.TeacherID = Teachers.TeacherID

Example 4: Ordering Results

SELECT Students.Name, Students.StudentNo FROM Students, Teachers WHERE Teachers.Name = 'Mr. Davies' AND Teachers.TeacherID = Students.TeacherID ORDER BY Students.Name ASC

Students table

A table that includes fields such as StudentNo, Name, Email, Year, and TeacherID.

Teachers table

A table that includes fields such as TeacherID, Name, and Class.

Ascending order

An order in which results are sorted from lowest to highest, or alphabetically from A to Z.

Email field

A field in the Students table that contains the email addresses of the students.

Class field

A field in the Teachers table that indicates the class taught by the teacher.

Filtering results

The process of narrowing down the results of a query based on specific conditions.

Ordering results

The process of arranging the results of a query in a specific sequence.

Data retrieval

The process of obtaining data from a database using SQL queries.

Multiple tables

Refers to querying data from two or more tables in a database.