Relational databases and structured query language (SQL)

What is a database?

Structured collection of data stored for easy access and management

What is a relational database?

Database storing data in related tables, using keys to link them

What are the advantages of relational databases?

Reduce data redundancy and inconsistency, improve data integrity

Define 'table' in databases.

Structure storing data in rows (records), columns (fields)

What is a record?

A complete set of related data in a table (single row)

What is a field?

Column in a table, holds single type of data (e.g., age, name)

What is a primary key?

Unique identifier for each record in a table

What is a foreign key?

A field linking one table to the primary key in another table

What is data redundancy?

Unnecessary repetition of data in a database

What is data inconsistency?

Content conflicts/contradictions due to repeated or mismatched data

How does a relational database help eliminate redundancy?

Stores each unique item only once, linked by keys

How does a relational database help eliminate inconsistency?

Data changes update once, reflected everywhere it’s referenced

What is an entity in database design?

Object or thing about which data is stored (e.g., Student)

What is an attribute?

Property or detail describing an entity (e.g., Student Name, Age)

What is an entity identifier?

Unique value for each entity (usually the primary key)

What is SQL?

Structured Query Language—for managing and querying relational databases

What is a query in SQL?

Command to get, set, or change information in the database

Write an SQL SELECT statement to retrieve all columns.

SELECT * FROM table_name;

How do you select specific columns in SQL?

SELECT column1, column2 FROM table_name;

How do you filter records in SQL?

Use WHERE: SELECT * FROM table_name WHERE condition;

Write an SQL statement to find students aged 16.

SELECT * FROM Students WHERE Age = 16;

How do you sort results in SQL?

Use ORDER BY: SELECT * FROM table_name ORDER BY column1 ASC/DESC;

What does ASC mean in ORDER BY?

Sort ascending (lowest to highest)

What does DESC mean in ORDER BY?

Sort descending (highest to lowest)

How do you retrieve data from two tables in SQL?

Use JOIN: SELECT … FROM table1 JOIN table2 ON table1.key = table2.key;

How do you insert data into a table in SQL?

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

How do you update data in SQL?

UPDATE table_name SET column1 = newvalue WHERE condition;

How do you delete data in SQL?

DELETE FROM table_name WHERE condition;

What is a JOIN in SQL?

Combines rows from two tables using a related column

What is an INNER JOIN?

Returns records where there is a match in both tables

What is a one-to-many relationship in databases?

One record in a table linked to multiple in another

What is a many-to-many relationship?

Records in both tables are linked to multiple in the other, often via link table

What is normalization?

Structuring a database to reduce redundancy and improve data integrity

What does 'referential integrity' mean?

Making sure relationships between tables are consistent (foreign key rules)

Explain how to set a primary key in SQL.

PRIMARY KEY column when creating table

Describe SQL command to create a table.

CREATE TABLE table_name (column1 datatype PRIMARY KEY, column2 datatype, …)

What is a wildcard in SQL?

% used to match any sequence of characters in LIKE queries

Write an SQL statement using LIKE.

SELECT * FROM Names WHERE Name LIKE 'A%';

How do you count records in SQL?

SELECT COUNT(*) FROM table_name;

How do you find the highest value in a column?

SELECT MAX(column) FROM table_name;

How do you find the lowest value in a column?

SELECT MIN(column) FROM table_name;

How do you find the average value in SQL?

SELECT AVG(column) FROM table_name;

How do you group records in SQL?

Use GROUP BY: SELECT column, COUNT(*) FROM table_name GROUP BY column;

How do you prevent SQL injection attacks?

Use parameterized queries, validate user input

What does SQL injection mean?

Manipulating SQL command using unsanitized user input

How is a foreign key set up in SQL?

FOREIGN KEY references primary key in another table

Explain the difference between SELECT and UPDATE.

SELECT gets data; UPDATE changes data

How are relationships shown in a database diagram?

Lines linking keys between tables

Why might you use two tables in a query?

Get richer data by combining related information

What is a transaction in databases?

A sequence of database operations that are treated as a single unit

Why use transactions?

Ensure data integrity, reliability

What is data integrity?

Accuracy and consistency of data over its lifecycle

What is meant by 'schema'?

Structure/definition of database tables and relationships

How do you search for all users with surname 'Smith'?

SELECT * FROM Users WHERE Surname = 'Smith';

How do you insert a new record into Students table?

INSERT INTO Students (Name, Age) VALUES ('Sam', 16);

How do you delete all students aged 20?

DELETE FROM Students WHERE Age = 20;

Write SQL to update surname for student with id 5.

UPDATE Students SET Surname = 'Jones' WHERE StudentID = 5;

What is an index in a database?

Structure that speeds up searching and sorting

How do you create a unique constraint?

UNIQUE(column) when creating table

What is a composite key?

Primary key made from more than one column

How do you limit results in SQL?

Use LIMIT: SELECT * FROM table_name LIMIT 10;

How to select distinct values in SQL?

SELECT DISTINCT column FROM table;

What is the advantage of using SQL for databases?

Fast, standardized, powerful for complex queries

What does SELECT * mean?

Select all columns in the table

How do you join Students and Courses tables?

SELECT … FROM Students JOIN Courses ON Students.CourseID = Courses.CourseID;

How are attributes and fields related?

Field is a column in a table, attribute describes entity

Why do databases use primary keys?

Guarantee each record is unique

What is a backup in databases?

Copy of data for safety in case of loss/failure

Give a reason for using relational database in business.

Data integrity, reduce repetition, combine info easily

What is an example of a relational database system?

MySQL, PostgreSQL, MS Access, Oracle

How is data stored physically in databases?

On disk in files; managed by DBMS (database management system)

What is the purpose of a DBMS?

Software to create, manage and control access to databases

How do you remove all records from a table?

DELETE FROM table_name;

What is meant by "cascade delete"?

Deleting record automatically deletes related records in other tables

When is a one-to-one relationship used?

When each record in a table links to one record in another table (rare)

Why is referential integrity important?

Prevents orphaned records, maintains link correctness

How do you write an SQL query to find all books published after 2015?

SELECT * FROM Books WHERE Year > 2015;

When should you use an index?

To speed up searching large datasets

What does 'NULL' represent in SQL?

No value or unknown data in a field

How do you select records where City is not NULL?

SELECT * FROM table WHERE City IS NOT NULL;

Write an SQL query to find all employees with salary over 30,000.

SELECT * FROM Employees WHERE Salary > 30000;

How do you use the BETWEEN operator?

SELECT * FROM Marks WHERE Score BETWEEN 70 AND 90;

What is cascading update?

Changes to primary key automatically update foreign key links

How do queries extract just two fields, Name and DateOfBirth?

SELECT Name, DateOfBirth FROM table_name;

How do you combine criteria in SQL queries?

Use AND or OR operators

Write a query for students aged 16 in London.

SELECT * FROM Students WHERE Age = 16 AND City = 'London';

How do you use aliases in SQL?

SELECT Name AS StudentName FROM Students;

What does JOIN ON mean in SQL?

Specifies column to link tables when joining

How to search for users whose name starts with S?

SELECT * FROM Users WHERE Name LIKE 'S%';