Week 19: CST 363 - Introduction to Database Systems

 

 WK03: Completing our study of SQL

This week capped a very short introduction to the language: we touched nearly all the basics but didn't drill deeply. Even so, it clarified a lot. I especially liked finally seeing how indexes work under the hood - why they speed equality/range lookups, when they hurt writes, and how choices like clustered vs. heap storage change access paths. We also took a quick look at query execution plans. Reading operators such as full/index scans, unique-key lookups, nested loops, DISTINCT, and ORDER BY made the optimizer feel less magical. I only wish we had spent more time interpreting plans and estimating costs.

SQL Views

A SQL view is essentially a saved query that presents data as if it were a table. Like a table, you can select from it, join it, or filter it. The big difference is that most views don't actually store data - they are virtual, built on top of base tables. Because of this, they don't usually have their own primary keys. Whether you can insert, update, or delete through a view depends on how simple it is; if the system can map changes back to one table, it may allow updates, but many views are read-only unless special triggers or materialized views are used.

 SQL vs. Java

Comparing SQL to a language like Java highlights how different programming models can be. Java is procedural and object-oriented: you control the steps with loops, conditions, and classes. SQL, on the other hand, is declarative - you say what result set you want, and the database figures out how to get it. Still, there are parallels. Java's if statements resemble SQL's WHERE predicates, and the SELECT clause is like a return statement specifying output. But Java has full control flow and data structures, while SQL specializes in set-based operations and optimization. This difference is what makes SQL so powerful for data retrieval and manipulation, even with its unusual syntax.