What is a view in SQL?
A saved SELECT query you can query like a table.
Views provide a way to simplify complex queries and enhance security.
Do views store their own data?
No — they read live data from underlying tables.
Views are dynamic and reflect the current state of the data.
Why do views update automatically?
Because they always read from the base tables, not stored snapshots.
This ensures that views always present the most current data.
One reason views improve security?
They can hide sensitive columns.
This allows users to access necessary data without exposing sensitive information.
One reason views improve simplicity?
They hide complex joins behind a simple name.
This makes it easier for users to work with data without needing to understand the underlying complexity.
What’s the real-world analogy for a view?
A pre-organised tray of ingredients (not the whole messy store room).
This analogy illustrates how views present a simplified interface to complex data.
What is a stored procedure?
A saved block of SQL code you can run by name.
Stored procedures encapsulate logic for reuse and can improve performance.
Why do stored procedures improve performance?
They are often pre-compiled.
This reduces the time needed to execute the SQL code.
How do stored procedures improve security?
Users can run the procedure without accessing underlying tables.
This limits direct access to sensitive data.
What can stored procedures contain?
- Multiple statements
- Transactions
- Error handling
- Parameters
This flexibility allows for complex operations to be performed in a single call.
Real-world analogy for stored procedures?
A saved recipe instead of rewriting full instructions every time.
This highlights the efficiency of using stored procedures.
What does SELECT do?
Chooses which columns to show.
This is fundamental for retrieving specific data from a database.
What does FROM do?
Chooses which table to read data from.
This specifies the source of the data in a query.
What does WHERE do?
Filters which rows you want.
This clause is essential for narrowing down results based on conditions.
In SELECT, where do commas go?
Between column names — never inside numeric values.
Proper syntax is crucial for executing queries successfully.
Where should formatting (like £ symbols) normally happen?
In the application layer, not the database.
This separation of concerns helps maintain data integrity.
What does AND require?
All conditions must be true.
This logical operator is used to combine multiple conditions in a query.
What does OR require?
At least one condition must be true.
This operator allows for more flexible query conditions.
Why use brackets with OR?
To make the logic behave as expected.
Brackets clarify the order of operations in complex queries.
What does BETWEEN do?
Checks if a value is within a range (inclusive).
This operator simplifies range queries.
What order must BETWEEN values be in?
Low value first, high value second.
This ensures the correct interpretation of the range.
What does NOT do?
Flips a condition — shows everything except the match.
This operator is useful for excluding specific results.
Why be careful using NOT with NULL?
NULL behaves unpredictably with NOT — use IS NULL / IS NOT NULL.
Understanding NULL handling is crucial for accurate query results.
Safe practice when inserting rows?
Always list the columns you are inserting into.
This prevents errors if the table structure changes.
