Join Bill Weinman for an in-depth discussion in this video Course overview, part of SQL Essential Training (2014).
The goal of this course is to provide you with the basic knowledge that you need to use SQL effectively in any database environment. In this course, I use the XAMPP package to provide a consistent cross-platform environment for following the exercises. XAMPP, that's XAMPP. Provides the Apache Web server with PHP and SQL Light. Everything you need to effectively run the exercises in this course. I'm using this same environment on the screen here. So, your screen should look just like mine.
At least inside the browser window. I'm using Google Chrome on a Mac. But, any modern web browser on any modern operating system should work just the same. The SID application that you see on the screen here is my SQL interactive demonstrator. I wrote this application for the purpose of providing a consistent cross-platform tool for demonstrating and experimenting with SQL. In order to use it, simply select a database here from the database drop down.
And type your SQL in here where it says SQL. And press the handy button with the word go on it that's the number five in Japanese. It's not relevant, but just a bit of trivia there. Go Speed Racer! And you see you get your results down here and a little status line up here. You can even put in multiple SQL statements And press go. And you see the status says there's 3 queries, 12 rows were returned. The elapsed time in milliseconds. And there's the results.
The status bar gives you information about performance. And it also gives you any error messages. If I make a type here, and press go. You get the error message. There's no such table x customer. These error messages come from the database engine. And for the most part, they're pretty helpful. And you see that the other queries that did not have errors were executed just fine. The CRUD application is a demonstration of the four major functions of the database. CRUD is an acronym for create, read, update and delete.
Both of these applications are written in PHP. They're open source, and I encourage you to look at the code and use them or modify them to suit your own needs. I'll give you a tour of the credit application source code later in the course. The database engine that I'm using for this course is SQLite. SQLite is a small, fast cross-platform fully functional, fully self-contained database engine. That makes it perfect for this course. Sql is a standard database language. But every database system uses it's own dialect.
This can be confusing so I do my best to provide you with sufficient knowledge so that you can navigate those differences, easily and effectively. All the major database engines, oracle, Microsoft sql server, post for sql. My SQL and SQL lite, all of them use their own dialect of SQL. The reasons for this have to do with the legacy of SQL. SQL was already a major language before it was ever standardized, and most of these database systems have their own legacies, their predate SQL.
So in order to continue supporting their legacy users and applications, they must maintain their own dialects and quirks. SQLite is simple to install. It's widely used for mobile and web applications. And it's closer to the SQL standard than most of the other database systems. It's seems to me a good choice for this purpose. What this means to you is that the SQL you learned here will prepare you well for whatever environment you end up working in, but there will be differences and that's just the state of the database ecosystem today.
These tools were all selected with one goal in mind. To provide an environment where you can focus on learning SQL efficiently, effectively and hopefully enjoyably.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.