Tables store data, views show saved query results, indexes help the database find rows faster, cursors process query results row by row, triggers run automatically when specified events happen, and stored procedures save reusable database-side logic. This chapter explains all six objects from zero with simple SQL examples, practical use cases, important differences, limitations, exam points and DBMS-specific warnings so you can understand when each object is useful instead of only memorising definitions.
SQL Database Objects in One Simple Picture
Memory shortcut
Table = Store · View = Show · Index = Speed · Cursor = Loop · Trigger = React · Procedure = ReuseIf you are still learning basic SELECT queries, revise SQL SELECT Queries for Beginners. For multi-table querying and grouping, revise SQL Joins, Subqueries & Aggregate Functions in DBMS.
Database Object Control Room
Table: permanent structured storage
What to remember
| Object | Main use | Stores rows? |
|---|---|---|
| Table | Permanent data storage | Yes |
What Is a Table in SQL?
A table is the main structure used to store data in a relational database. It contains columns that describe attributes and rows that contain records.
Table design
student_id, name, branch, marks
Stored rows
101 · Asha · CSE · 85
102 · Ravi · ECE · 72
Create a table
CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
branch VARCHAR(20),
marks DECIMAL(5,2)
);Add a row
INSERT INTO Student
(student_id, name, branch, marks)
VALUES
(101, 'Asha', 'CSE', 85);Change the table structure
ALTER TABLE Student
ADD email VARCHAR(100);Remove the table
DROP TABLE Student;DROP TABLE removes the table object itself. Exact behavior around dependencies, transactions and cascading options depends on the DBMS.
Key idea
A table stores the base data. Views and indexes usually depend on tables rather than replacing them.
Important Constraints Used With Tables
For a full explanation, revise Integrity Constraints in DBMS.
What Is a View in SQL?
A view is a named query that behaves like a virtual table for reading data. A normal view usually stores the query definition, not an independent copy of all result rows.
Create a simple view
CREATE VIEW CSE_Students AS
SELECT student_id, name, marks
FROM Student
WHERE branch = 'CSE';Query the view
SELECT *
FROM CSE_Students;Why views are useful
Can You INSERT, UPDATE or DELETE Through a View?
Some views are updatable and some are not. A simple view based on one table may allow updates in many DBMSs, while views containing GROUP BY, aggregate functions, DISTINCT, set operations or complicated joins often become non-updatable or restricted.
Usually easier to update
CREATE VIEW Active_Students AS
SELECT student_id, name, branch
FROM Student
WHERE status = 'Active';Usually not directly updatable
CREATE VIEW Branch_Avg AS
SELECT branch, AVG(marks) AS avg_marks
FROM Student
GROUP BY branch;Exact updatability rules differ across products, so check your DBMS documentation when writing real applications.
View vs Materialized View
Normal View
Usually stores the query definition. The underlying tables are read when the view is queried.
Materialized View
Stores a physical result that must be refreshed according to DBMS features and configuration.
Materialized views can speed up expensive reporting queries, but they introduce refresh and freshness considerations. Support and syntax vary by DBMS.
What Is an Index in SQL?
An index is a database structure that helps the DBMS find rows faster without scanning every row of a table. It improves read performance for suitable queries, but it also consumes storage and can make INSERT, UPDATE and DELETE operations more expensive because the index must be maintained.
Without a useful index
The database may need to inspect many table rows to find one student by email.
With a useful index
The optimizer may use the index to reach matching rows more directly.
Create an index
CREATE INDEX idx_student_email
ON Student(email);Composite index
CREATE INDEX idx_student_branch_marks
ON Student(branch, marks);A composite index contains more than one column. The order of columns matters because it affects which search and sort patterns the index can support efficiently.
Primary, Unique, Composite, Clustered and Nonclustered Indexes
Index terminology is product-specific. For example, SQL Server prominently uses clustered and nonclustered terminology, while other systems organize indexes differently internally.
When an Index Helps and When It Can Hurt
Often helpful
Columns frequently used in selective WHERE conditions, JOIN keys, unique lookups and some ORDER BY patterns.
Can be wasteful
Tiny tables, columns with very few distinct values in some workloads, or tables with heavy writes and little indexed reading.
Creating many indexes can slow writes because each data change may require multiple index updates. The optimizer decides whether to use an available index; creating one does not guarantee that every query will use it.
Index memory line
Faster reads can mean slower writes + extra storageWhat Is a Cursor in SQL?
A cursor is a mechanism used to process a query result row by row. It is useful when procedural logic truly needs one row at a time, but set-based SQL is usually simpler and faster for operations that can be expressed as one statement.
Conceptual cursor example
DECLARE student_cursor CURSOR FOR
SELECT student_id, marks
FROM Student;
OPEN student_cursor;
/* FETCH rows one by one */
/* process each row */
CLOSE student_cursor;Exact cursor syntax differs significantly among MySQL, PostgreSQL, Oracle and SQL Server. In exams, learn the logic and the syntax used by your syllabus.
Cursor vs Set-Based SQL
Row-by-row thinking
Fetch one student, update marks, fetch next student, repeat.
Set-based SQL
UPDATE Student
SET marks = marks + 5
WHERE branch = 'CSE';One statement updates all matching rows.
Use a cursor only when the problem genuinely needs row-by-row procedural behavior that is difficult or inappropriate to express using normal SQL statements.
What Is a Trigger in DBMS?
A trigger is database code that runs automatically when a specified event occurs. Common events are INSERT, UPDATE and DELETE on a table.
Example idea: audit changed marks
CREATE TRIGGER trg_marks_audit
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
INSERT INTO Marks_Audit
(student_id, old_marks, new_marks)
VALUES
(OLD.student_id, OLD.marks, NEW.marks);
END;This example uses MySQL-style row trigger syntax. PostgreSQL, Oracle and SQL Server use different trigger definitions and transition-row syntax.
Advantages and Problems of Triggers
Advantages
Useful for auditing, automatic history tables, enforcing some database-side rules and keeping related data synchronized where appropriate.
Problems
Triggers can hide behavior because an INSERT or UPDATE may cause extra work automatically. Complex trigger chains can become difficult to debug and may hurt performance.
Do not use a trigger merely because automation sounds convenient. Prefer clear constraints for basic data validity and explicit application or procedure logic when that gives better readability and control.
What Is a Stored Procedure?
A stored procedure is a named block of database-side code that can contain SQL statements, parameters and procedural logic. Applications or users can call it whenever the same operation is needed.
Simple procedure idea
CREATE PROCEDURE GetStudentsByBranch(
IN p_branch VARCHAR(20)
)
BEGIN
SELECT student_id, name, marks
FROM Student
WHERE branch = p_branch
ORDER BY marks DESC;
END;This example uses MySQL-style procedure syntax. SQL Server, PostgreSQL and Oracle use different syntax for procedures and parameters.
Call the procedure
CALL GetStudentsByBranch('CSE');Procedure memory line
Write logic once → call it whenever neededProcedure Parameters: IN, OUT and INOUT
Parameter modes and calling syntax vary by database product. The conceptual difference is more important than memorising one vendor's exact punctuation.
Stored Procedure vs Function
Stored Procedure
Usually called as an operation. It may contain multiple SQL statements, transaction logic and output parameters depending on the DBMS.
Function
Usually returns a value and can often be used inside SQL expressions, although exact capabilities differ by DBMS.
Do not memorize one universal rule such as “functions cannot modify data” because vendor rules differ. Learn your syllabus DBMS and understand the conceptual distinction: procedure as reusable operation, function as value-producing routine.
Trigger vs Stored Procedure
| Point | Trigger | Stored Procedure |
|---|---|---|
| How it starts | Runs automatically on a defined event. | Called explicitly by a user, application or other database code. |
| Main use | React to data changes or events. | Package reusable operations and business logic. |
| Parameters | Usually uses event context rather than normal call parameters. | Can accept parameters in many DBMSs. |
| Visibility | Can be hidden from application flow if poorly documented. | Call is usually visible in application or SQL flow. |
One Realistic Database Workflow Using All Six Objects
Tables vs Views vs Indexes vs Cursors vs Triggers vs Procedures
| Object | Main purpose | Stores base rows? | Runs automatically? | Main caution |
|---|---|---|---|---|
| Table | Store data | Yes | No | Needs good constraints and design |
| View | Saved query interface | Usually no | No | Updatability and performance vary |
| Index | Faster lookup | No | Maintained by DBMS | Extra storage and write cost |
| Cursor | Row-by-row processing | No | No | Often slower than set-based SQL |
| Trigger | Automatic reaction | No | Yes | Hidden side effects and debugging |
| Procedure | Reusable database logic | No | No | Vendor-specific syntax and portability |
Mistakes to Avoid
How These Objects Connect to DBMS Theory
For the logical foundation, revise Relational Model in DBMS. For table relationships and keys, revise Keys, Mapping Constraints, Generalization & Aggregation.
How to Write These Topics in Semester Exams
Definition + purpose
Write one direct definition and one main use. Example: “An index is a structure used to speed up data retrieval.”
Definition + syntax + example
Explain the object, show one SQL example and list two advantages or limitations.
Explain + compare
Use diagrams or flow, syntax, example, advantages, disadvantages and a comparison such as trigger vs procedure or table vs view.
For presentation, revise How to Write High-Scoring Answers in University Semester Exams. For deciding what to revise first, use the previous-year-paper priority method.
Practice Questions With Answers
1. What is a table in SQL?
A table is a structured database object that stores rows and columns of data.
2. What is a view in DBMS?
A view is a named query that behaves like a virtual table. A normal view usually stores the query definition rather than an independent copy of all result rows.
3. What is the main purpose of an index?
An index helps the DBMS locate matching rows more efficiently for suitable queries.
4. Why should we not create too many indexes?
Indexes consume storage and must be maintained when data changes, so too many indexes can slow INSERT, UPDATE and DELETE operations.
5. What is a cursor?
A cursor is a mechanism for processing a query result row by row.
6. What is a trigger?
A trigger is database code that runs automatically when a defined database event occurs.
7. What is a stored procedure?
A stored procedure is a named reusable block of database-side SQL and procedural logic that can be called when needed.
8. What is the difference between a trigger and procedure?
A trigger runs automatically because of an event, while a stored procedure is normally called explicitly.
9. What is the difference between a view and a table?
A table stores base rows, while a normal view is usually a stored query definition whose result is derived from underlying data.
10. When should a cursor be avoided?
A cursor should usually be avoided when the same task can be expressed clearly with set-based SQL such as one UPDATE, INSERT, DELETE or SELECT statement.
One-Minute Revision Sheet
Ultimate memory line
Store with table · show with view · speed with index · loop with cursor · react with trigger · reuse with procedureFrequently Asked Questions
What is the difference between a table and a view?
A table stores base data in rows and columns, while a normal view is usually a named query whose result is derived from underlying tables or views.
Why are indexes used in DBMS?
Indexes are used to help the DBMS find matching rows faster for suitable queries, but they also use storage and add maintenance cost to data changes.
What is the use of a cursor?
A cursor is useful when query results need genuine row-by-row procedural processing. Set-based SQL is usually preferred when the same task can be expressed as one statement.
What is the difference between a trigger and a stored procedure?
A trigger starts automatically because of a defined database event, while a stored procedure is normally called explicitly and can accept parameters.
Are SQL trigger and procedure syntaxes the same in every DBMS?
No. MySQL, PostgreSQL, Oracle and SQL Server use different stored-programming and trigger syntax, so practical code should follow the DBMS being used.
What should I study after tables, views, indexes, cursors, triggers and procedures?
Continue with transactions, concurrency control, recovery, normalization, query optimization and database security.
What Should You Learn Next?
Next: Transactions, ACID Properties, Concurrency & Recovery
You now know the main SQL database objects used to store, expose, speed up and automate database work. The next step is understanding how a DBMS keeps changes correct and safe using transactions, COMMIT, ROLLBACK, ACID properties, schedules, locking, deadlocks and recovery.
For video revision after reading, use the DBMS YouTube study guide only as a secondary resource.
