Week 19

We’re halfway through the course, and the last four weeks have helped me connect the dots. I learned how to organize messy data into cleaner tables so we don’t repeat information, and how to write queries that combine tables while still showing items that don’t have a match. I also got the idea of indexes—they’re like a book’s table of contents that make lookups faster—and I saw how data is stored behind the scenes in simple blocks/rows with a map of which spots are free. Finally, I built a small ordered index and practiced keeping it updated when rows are added or removed, then used it to speed up lookups instead of scanning everything.

I still have a few questions. When is it worth creating an index, and when is it overkill? How do I pick the best column (or columns) to index for real-world queries? How does the database decide whether to use an index or just scan the table? And what are some easy rules of thumb for designing tables that will work well for reports we haven’t thought of yet?

Another big takeaway for me is the value of slowing down and planning before I touch the keyboard. Sketching a quick diagram or writing the query in plain English first has saved me from a lot of trial-and-error. When I did rush, I usually ended up with confusing results or repeated data, so I’m trying to be more intentional: define the goal, check the columns I really need, then write the query.

Looking ahead to the rest of the course, I want to get faster at reading my own results and spotting mistakes early. I’ll keep practicing small, focused exercises—like turning a messy table into a clean set of tables, or timing a simple query with and without an index—to build confidence. My goal is to leave the class with a solid routine: plan the data model, write clear queries, and use indexes only when they actually help.

Week 18

This week we had our first exam, and it was harder than I expected. I realized there were details in SQL that I needed to review more carefully, so it was a good reminder that practice is very important. Even though it was challenging, it also helped me see which parts of SQL I understand well and which ones I still need to improve.

1. What is an SQL view.  How is it similar to a table? In what ways is it different (think about primary keys,  insert, update, delete operations) ?

An SQL view is a virtual table that is created from a query. It looks like a table because I can select rows and columns from it, but it is different because the data is not stored separately. A view usually does not have a primary key, and in most cases I cannot do insert, update, or delete operations on it like I can with a real table. It is mostly used for simplifying queries or for security, when I only want to show part of the data.

2. We have completed our study of SQL for this course.  This is not to imply that we have studied everything in the language.  There are many specialized features such as calculating rolling averages, query of spatial data (data with latitude and longitude) coordinates, and more. But take a minute to think about how SQL compares to other programming languages such as Java.  What features are similar , and which are present in one language but not in the other?  For example,  Java has conditional if statements which are similar to SQL WHERE predicates,  the SELECT clause is similar to a RETURN statement in that it specifies what data or expression values are to be returned in the query result (although it is strange that a statement should specify the RETURN as the first part of a SELECT. 

Now that we have completed our study of SQL for this course, I can also compare it to Java. Both SQL and Java have ways to filter and control what happens: a WHERE clause in SQL works kind of like an if statement in Java, because both decide which rows or conditions are true. The SELECT in SQL is similar to a return in Java, since it shows what data should come back. At the same time, SQL is declarative — I just say what I want and the database does it — while Java is procedural and requires me to write step by step instructions. Java also has things like loops, methods, and objects, which don’t exist in SQL. SQL, on the other hand, has joins and aggregations that are specific to working with large sets of data.

From my point of view, I realized that when working with databases, it is better to use SQL. In my opinion, it is specialized for that purpose and makes the work much easier than trying to do the same tasks in a general programming language. SQL was designed for data, and this makes it more efficient and natural to use when handling tables, queries, and relationships.

Week 17

Sometimes at work I need to match rows by a range, not by IDs. Example: putting students into GPA bands (like “Honors,” “Good Standing,” “Probation”). There’s no foreign key for that—the match is “does this GPA fall between the band’s min and max?”

English:
“Match each student to the GPA band whose range includes the student’s GPA.”

SQL:

SELECT student.id, student.name, student.gpa, gpa_band.band_name
FROM student
JOIN gpa_band ON student.gpa BETWEEN gpa_band.min_gpa AND gpa_band.max_gpa
ORDER BY gpa_band.band_name, student.name;

My opinion of SQL & what trips me up

SQL feels like asking the database a question in a very exact way. I like that I can describe what I want and let the database figure out how to get it. It’s not “hard,” but it is picky—small details change the result.

What I still have to slow down for:

• Turning English into steps. Questions like “students who never took course X” or “for each department, who has the top score?” make me pause and plan before I write the query.

• Counting and then filtering. First you group and count, then (if needed) filter based on that count—easy to mix up the order.

• Joins in general. Missing one condition can duplicate rows; adding the right condition fixes it.

• NULLs. Equality doesn’t work the way you expect with NULL, so I double-check with IS NULL when needed.

Overall, I’m liking SQL more each week. Once I think in sets (tables in, table out), the patterns start to repeat and it gets much easier.

Week 16

This first week felt like a clean reset. I went through the zyBooks SQL basics and got IntelliJ running again for the Java pieces. I finished Labs 1, 3, 4, and 8, plus Homework 1. I actually enjoyed Homework 1 a lot—I’m excited to get more comfortable with SQL because I’ll use it at work, and since I want to move toward a data scientist role, getting past “Excel-only” and into real databases matters. I still love Excel for quick analysis, but SQL gives me reach and structure.

1. Relational database tables and spreadsheets look similar with both having rows and columns.  What are some important differences between the two?

On the surface they look the same—rows and columns—but they behave very differently. In a spreadsheet I can type almost anything into any cell; it’s flexible, but that also means it’s easy to break things. In a relational database I have a schema: real data types, primary keys, foreign keys, and constraints that stop bad data from getting in. That structure is the point. Databases also handle relationships across tables without copy-pasting—joins beat “VLOOKUP gymnastics.” They scale better, too. If I try to mash 300k rows into a spreadsheet with a bunch of formulas, it crawls. A database can index those columns and answer the same question quickly. Finally, databases are built for multiple people at once and keep data consistent with transactions; a spreadsheet shared by email or even online can still get out of sync or overwritten.

2. Installing and configuration a database and learning how to use it is more complicated that just reading and writing data to a file.  What are some important reasons that makes a database a useful investment of time? 

Setting up a database takes more time than reading and writing a CSV, but it pays off almost immediately:

• I can change the question without rewriting the whole pipeline—just write a new query.

• Indexes make “where” and “join” operations fast as data grows.

• Transactions protect me from half-finished writes and weird race conditions.

• Constraints keep the data clean so I don’t spend my life fixing typos later.

• Backups and recovery exist, which matters the first time someone deletes the wrong thing.

• Permissions let me share access safely, instead of passing around mystery copies of a file.

A small, real example from this week: I mixed up an ORDER BY and a GROUP BY in one of the labs and got nonsense results. With a file, I’d probably start over in a notebook and hope I didn’t break something else. In SQL, I just fixed the query, re-ran it, and the underlying data stayed reliable.

3. What do you want to learn in this course that you think will be useful in your future career? 

Short term, I want to be fluent with the “everyday” SQL I’ll need at work: solid joins (inner/left/right), grouping with HAVING, window functions for running totals and rankings, and how to read and speed up slow queries. I also want to get better at data modeling—how to design tables that make analysis easier instead of fighting it later. On the Java side, I want practice connecting code to a database the right way (prepared statements, connection pooling) so that what I write is safe and not brittle.

Big picture, I’m aiming at data science or cybersecurity. I can already get a lot done in Excel, but pairing that with strong SQL will let me pull exactly the data I need, keep it clean, and hand off results that other people can trust. This week was a good start: quick wins in the labs, a few small mistakes (I forgot a semicolon once and chased a typo in a WHERE clause), and a reminder that learning the disciplined way—schemas, keys, and constraints—will save me time when the datasets get bigger and the questions get messier.