How to Learn SQL and Databases: CS50 Full Course

TL;DR

SQL stands for structured query language, and it lets you create, read, update, and delete data inside a database. This Harvard CS50 course, taught by Carter Zenke with David J. Malan, starts with SQLite for portability and moves to MySQL and PostgreSQL for scale. It covers designing tables, normalizing data, joining with primary and foreign keys, views, indexes, and connecting to Python.

Transcript

This course is CS50's introduction to databases using a language called SQL. This course is from Harvard University and is taught by Carter Zeni with help from Dr. David J. Men. In this course, you'll master the fundamentals of SQL, including how to create, read, update, and delete data. You'll learn to model real world scenarios by designing table... Read More

Key Insights

• SQL stands for structured query language. It is structured because it has keywords you use to interact with a database, and it is a query language because it can ask questions of the data inside a database. It supports creating, reading, updating, and deleting data.
• A database is a way to organize data so you can create, update, read, and delete it. Those four interactions are the core operations performed against a database, and SQL is the language used to carry each of them out.
• A table stores a set of information where every row represents one item in that set and every column holds one attribute of that item. In a table of books, each row is a book and the two columns hold its title and its author.
• A database management system, or DBMS, is the software used to interact with a database, through either a graphical interface or a textual language. Examples named in the course include MySQL, Oracle, PostgreSQL, SQLite, Microsoft Access, and MongoDB.
• Scale is one reason to move from spreadsheets to databases. An organization storing not tens or hundreds of thousands of users but millions or billions of them is better served by a database than by spreadsheet software.
• Update frequency is a second reason to choose a database. A service where users post multiple times per second needs capacity that a spreadsheet alone cannot handle, while a database manages that write volume.
• Speed is a third reason to prefer databases. Searching a spreadsheet with Command F or Control F walks through rows one by one, whereas a database gives access to more kinds of algorithms that can search the same data much faster.
• Proprietary and open source database software trade support against responsibility. Paying for proprietary software buys additional support for implementing your database, while free options like PostgreSQL, MySQL, and SQLite leave implementation to you.

Questions & Answers

Q: What does SQL stand for?

SQL stands for structured query language. It is structured in the sense that it provides a set of keywords you use to interact with a database, and it is a query language in the sense that it can be used to ask questions of the data stored inside a database. SQL is the language used throughout the course to create data, read data, update data, and delete data. The name appears inside the names of several database management systems, including MySQL, PostgreSQL, and SQLite.

Q: What is a database and how does it differ from a database management system?

A database is a way to organize your data such that you can create data, update data, read data, and delete data. Those four interactions are the ones most often performed against a database. A database management system, abbreviated DBMS, is separate software used to interact with that database, either through a graphical interface or through a textual language. The relationship is comparable to using VS Code to write a program, or using desktop icons to interact with an underlying operating system.

Q: Why should you use a database instead of a spreadsheet?

Three reasons are given. Scale: a service storing millions or billions of users rather than tens or hundreds of thousands is better served by a database. Update frequency: a platform where users post multiple times per second needs capacity a spreadsheet cannot handle. Speed: looking up information in a spreadsheet means using Command F or Control F and going row by row, while a database gives access to more kinds of algorithms that search the data much faster. Spreadsheet software such as Apple Numbers, Google Sheets, and Microsoft Excel still stores tables, but hits these limits.

Q: What is a table in a database?

A table stores a set of information. Every row in that table stores one item in that set, and every column holds one piece of information about that item, meaning one attribute. In an ancient temple record, each row was one worker and each column was that worker's stipend for a particular month. In a library example, each row is one book and the table has two columns, one for the title and one for the author, so together the rows and columns form a table of books.

Q: Which database management system does this course start with and why?

The course begins with SQLite, then gradually moves on to MySQL and PostgreSQL. SQLite is chosen for portability. As the name implies, it is lighter weight than the alternatives, requiring less computation to run, while still allowing you to do most of the same work those heavier systems allow. MySQL and PostgreSQL are described as heavier weight and more fully featured as a consequence, and they are introduced later in the course for scalability's sake.

Q: What topics does the CS50 databases course cover from start to finish?

The course starts with SQLite to query a single table of data, such as a table of books. It then moves to working with multiple tables and defining relationships among them. Next comes designing your own database from scratch, learning about schemas and data types, followed by adding data through inserts, updates, and deletes. After that come views, which are alternate ways of simplifying your data for yourself and others, then optimizing queries to reduce the time they take at the cost of some space, and finally other database management systems such as MySQL and PostgreSQL for working at much bigger scale.

Q: Who teaches CS50's Introduction to Databases with SQL?

The course is from Harvard University and is taught by Carter Zenke, with help from Dr. David J. Malan. Both introduce the course at the start. CS50 itself is an introduction to computer science in which you learn about SQL and more, while this course dives more deeply into SQL specifically. It can be taken before, during, or after CS50 itself, and is aimed at computer scientists, data scientists, programmers, or simply anyone interested in databases.

Q: What is the trade-off between proprietary and open source database software?

Proprietary database software costs money, and what that money buys is additional support for actually implementing your own database. Open source or free software such as PostgreSQL, MySQL, and SQLite has no such cost, but the downside is that you are then responsible for implementing the database yourself. A second trade-off runs alongside it: some systems are heavier weight and therefore more fully featured but require more computation to run, while lighter options like SQLite still let you do most of the same work.

Q: What does the course teach beyond basic queries?

Beyond creating, reading, updating, and deleting data, the course teaches how to model real world entities and the relationships among them using tables with appropriate types, triggers, and constraints. It covers normalizing data to eliminate redundancies and reduce the potential for errors, joining tables together using primary and foreign keys, automating searches with views, and expediting searches with indexes. It also covers connecting SQL with other languages such as Python and Java, with assignments inspired by real world datasets.

Summary & Key Takeaways

• The course is CS50's introduction to databases with SQL from Harvard University, taught by Carter Zenke with help from Dr. David J. Malan. It teaches how to represent, organize, and manage data, and how to ask questions of the data around you. It can be taken before, during, or after CS50 itself.

• The material progresses in stages: querying a single table with SQLite, relating multiple tables and defining relationships among them, designing a database from scratch with schemas and data types, writing data by inserting, updating, and deleting, creating views, optimizing queries with indexes, and finally scaling.

• Tables are an ancient idea. A diagram from a few thousand years ago recording stipends for temple workers already used rows and columns, with each row storing one item in a set and each column storing one attribute of that item. The same structure applies to a modern table of book titles and authors.