7 - Relational Databases and Structured Query Language (SQL)

Explain the concept of a database

A database is a structured collection of data that allows easy storage, retrieval (using queries) and management of information - eg Google Classrooms, calendars, social media data - can be flat file or relational

Explain the concept of a flat file data base

A database that stores a all data in a single table so its simple and easy to understand - data is stored using comma separated values for different fields and each record appears on a separate line

What are some issues with flat file data bases?

It causes data redundancy + inconsistency, which makes it harder + longer to search and sort data , inefficient storage and is harder to maintain

What is data inconsistency?

As we often repeat data in each record, data can sometimes be wrongly inputted or be missing something eg (BMW 5 vs BMW 5 series - series is left out accidentally) - this makes it hard to search and sort data

What is data redundancy?

As we often repeat data in each record, the same piece of data may be stored more than once in a data base - this takes longer to search the data

Explain the concept of a relational database

Its a database that organises data into multiple tables, using keys to connect related data (relationships) between each table (relations)

How is using relational databases better for data?

It facilitates elimination of data inconsistency + redundancy - as related data is connected by keys across many tables, redundancy is reduced, giving efficient storage use + easier maintenance, and if 1 bit of data is changed, the entire database updates, avoiding inconsistency

What is a table?

A complete set of records about the same subject or topic in a database

What is a record?

A complete set of fields on a single row (basically just a row)

What is a field?

A single column in a table that represents a single piece of data

What is a data type?

The type of data that can be held in a database field, which defined when designing a table

What are the different data types?

Integer, Real, Datetime, Char, Varchar, Text and Boolean

What is an 'integer' data type?

A whole number

What is a 'real' data type?

A number with a decimal components

What are date, time, date time data types?

To store dates and times

What is a 'char' data type?

A fixed length string up to 8000 characters

What is a 'varchar' data type?

A variable length string up to 8000 characters

What is a 'text' data type?

A variable length string up to 2GB of data

What is a 'boolean' data type?

True or False

What is a primary key?

A unique field that stores an individual collection of data to identify a record in a table

What is a foreign key?

A foreign key is a field in a table that references the primary key of another table, to link them and create relationships

What is SQL?

Structured Query language - a programming language that allows you to create, query, update and delete data from a database - its used to interact with Database Management Systems

What does the SELECT command in SQL do?

It retrieves data and lists the fields to be displayed from a database table

Give an example of the SELECT command in SQL

SELECT name, age FROM users - retrieves names and ages from the table 'users'

What does the FROM command in SQL do?

It specifies the table name to retrieve the data from

Give an example of the FROM command in SQL

SELECT name, age FROM 'users' - retrieves names and ages from the table 'users'

What does the WHERE command in SQL do?

It filters the data based on a specified search criteria or condition

Give an example of the WHERE command in SQL

SELECT * FROM users WHERE age > 30 - retrieves users older than 30

What are some operators that can be used in SQL?

=, =!, >, <, >=, <=, AND, NOT and OR

What does the ORDER BY command in SQL do?

It allows a query to sort and organise data by ascending or descending order when it is retrieved

Give an example of the ORDER BY (ASC) command in SQL

SELECT * FROM members ORDER BY surname ASC - retrieves all members and displays them in ascending order by surname

Give an example of the ORDER BY (DESC) command in SQL

SELECT * FROM members ORDER BY surname DESC - retrieves all members and displays them in descending order by surname

What is a wildcard?

Its used to select all columns in a table (* only in FROM) , or as a substitute for 0, 1 or more characters (% only in LIKE)

Give an example of a wild card in SQL

SELECT * FROM users - retrieves all columns from the users table

Give an example of a wild card in a LIKE string in SQL

SELECT * FROM users WHERE name LIKE 'H%' - retrieves users from all columns in the users table who's names start with H

What does the INSERT command in SQL do?

It adds new data to a database table

How do you use the INSERT command to insert data into a relational database?

INSERT INTO table_name (column1, column2...) VALUES (value1, value2...)

Give an example of the INSERT command in SQL

INSERT INTO users (name, age) VALUES ('John Doe', 25) - inserts a new user with the name 'John Doe' and age 25

What does the UPDATE command in SQL do?

It edits data stored in records in a database table - more than 1 record can be changed at a time

How do you use the UPDATE command to edit data in a database?

UPDATE table_name SET column1 = value1, column2 = value2... WHERE condition

Give an example of the UPDATE command in SQL

UPDATE users SET name = 'Bob', age = 56 WHERE ID = 4 - updates name and age for user ID 4 (Bob, 56)

What does the DELETE command in SQL do?

Deletes data from a database table or tables (using tables with relationships that aren't affected by the deletion)- can be more than 1 record at a time

How do you use the DELETE command to remove data from a database table?

DELETE FROM table_name WHERE condition

Give an example of the DELETE command in SQL

DELETE FROM users WHERE age < 18 - deletes all users younger than 18 from 'users' table

How can you select information from 2 tables

Use the command table_name.field - combines data from 2 or more tables based on a related column

Give an example of a query to select information from 2 tables in SQL

SELECT owners.Firstname, dogs.breed FROM owners, dogs WHERE owners.DogsID = dogs.DogsID AND dogs. Age = 4 - selects owner and dogs breeds for all 4 year old dogs