Databases
- A database is an organised collection of data. A relational database stores its data in several linked tables.
- Each table holds data about one kind of thing, such as students. Each row is a record, and each column is a field.
- A primary key is a field that uniquely identifies each record, such as StudentID. A foreign key is a field in one table that is the primary key of another table, which links the two tables.
Why use linked tables?
- Each piece of data is stored only once. This avoids data redundancy (the same data repeated) and data inconsistency (the same data stored differently in different places). A change only needs making in one place.
SQL: finding data
- SELECT Name, Age FROM Students WHERE Age > 15 ORDER BY Name ASC chooses the fields, the table, the condition and the order. ASC means A to Z (smallest first) and DESC means Z to A (largest first).
- SELECT * returns every field. Conditions can use =, <>, <, >, <=, >=, AND and OR. You may also see LIKE with a wildcard: WHERE Name LIKE 'A%' finds names beginning with A.
- To use two tables, link them in the WHERE clause: SELECT Students.Name, Forms.Room FROM Students, Forms WHERE Students.FormID = Forms.FormID.
SQL: changing data
- INSERT INTO Students (StudentID, Name, Age) VALUES (7, 'Ali', 15) adds a record.
- UPDATE Students SET Age = 16 WHERE StudentID = 7 changes a record.
- DELETE FROM Students WHERE StudentID = 7 removes a record. Always include WHERE, or every record in the table is changed or deleted.
Key terms
- Database
- An organised collection of data.
- Relational database
- A database that stores data in linked tables.
- Record
- One row of a table, holding the data about one item.
- Field
- One column of a table, holding one piece of data about each item.
- Primary key
- A field that uniquely identifies each record in a table.
- Foreign key
- A field that is the primary key of another table, linking the two tables.
- Data redundancy
- The same data being stored more than once.
- Data inconsistency
- The same data being stored differently in different places.
- SQL
- Structured query language, used to search and change databases.
Quick questions on Relational databases and SQL
- What is a record in a database table?
- One row, holding the data about one item: For example, everything about one student.
- What is a field in a database table?
- One column, holding one piece of data about each item: For example, every student's age.
- What is a primary key?
- A field that uniquely identifies each record: No two records can have the same primary key.
- What is a foreign key?
- A field that is the primary key of another table, linking the two: It creates the link between tables.
- Why do relational databases use several linked tables?
- To avoid storing the same data more than once: This avoids redundancy and inconsistency.
- What is data redundancy?
- The same data stored more than once: It wastes space and can cause mistakes.
- Which SQL keyword names the table to use?
- FROM: For example, FROM Students.
- Which SQL keyword sets a condition?
- WHERE: For example, WHERE Age > 15.
Relational databases and SQL: questions and answers
- What does SELECT * FROM Students do?
- Returns every field of every record in Students. * means every field.
- Which SQL statement changes an existing record?
- For example, UPDATE Students SET Age = 16 WHERE StudentID = 7.
- What happens if you run DELETE FROM Students without a WHERE clause?
- Every record in the table is deleted. Always include WHERE.