SQL and databases Flashcards
structured data
fixed fields with data types
has metadata
unstructured data
no discernible structure
no metadata
cannot be queried without being transformed
data base
collection of information that is organised so it can be accessed, managed and updated
field
column, single piece of data about an entity
records
rows, the group of fields about a specific entity
primary key
field that identifies each record uniquely
foreign key
field in one table that is the primary key in another table, links the tables together
specified by a *
why are data bases structured
unstructured information/data makes it hard to work with large datasets and to interpret
flat file data base
data base with one table
relational data base
has multiple tables that are connected through their key fields - foreign key
why use relational data bases
- flat file data bases can lead to redundant data (repeats)
- inconsistencies likely to be introduced (as you have to manually add everything in so more likely to make mistakes)
SQL
structured query language
SQL select
SELECT Field(s)
FROM Table
WHERE Condition
ORDER BY Field ASC/DSC
What are the album titles stored?
SELECT Title
FROM Albums
What are the albums made after 2015, show the title and artist with newest first
SELECT Title, Artist
FROM Albums
WHERE Year > 2015
ORDER BY Year DESC
What are the song names stored, how many streams did they have and what album are they in?
SELECT Name, Stream_Count, Title
FROM Albums, Songs
WHERE Albums.ID = Songs.ID
Who is the teacher and what room are 12B/It in?
SELECT Teachers.Name, Room
FROM Teachers, Classes
WHERE Classes.Name = “12B/It” AND Teachers.TeacherCode = Classes.TeacherCode
INSERT INTO
Adds a record to a table
INSERT INTO Table (fields)
VALUES (values)
a null field
gaps, empty field in record
DELETE FROM
removes record from table
DELETE FROM Table
WHERE Field = Value
UPDATE
changes value or values in table
UPDATE table
SET Field = Value
WHERE condition
Change price of protractor to £2.99
UPDATE Products
SET Price = “£2.99”
WHERE Name = “Protractor”