Join Bill Weinman for an in-depth discussion in this video Installing the test environment on a PC, part of SQL Essential Training.
In this movie I'll show you how to install a test environment to follow along with the exercises on a PC running Windows. In order to follow along with the exercises, there are a few things that you will need. You need a web browser. Any modern web browser will do. I'm using Google Chrome, but that is just my preference. If you like Firefox, or Internet Explorer, or Safari that'll work just fine. You just need a modern up-to-date browser. You need a web server with PHP and a database engine. In this movie, I'll show you how to install XAMPP.
That's X-A-M-P-P. XAMPP is a package of things that include the Apache web server, PHP, SQLite and a bunch of other things. This is the environment I'll be demonstrating with. So, unless you know how to install and configure all the things yourself, it may be best that you install XAMPP and follow along with me. If you have your own installation of PHP and a database engine, and you know how to install scripts, and configure them, that will work, too. Otherwise, follow along and install XAMPP with me. Also, keep in mind that every database system has its own dialect of SQL.
So, some of the examples may not work on a different database system. So, if you want to follow along with the exercises you'll need an up to date version of SQLite installed, many installations of PHP already have SQLite and a corresponding PDO driver available. You'll also need a plain text editor, this is different than a word processor. A word processor is designed for writing documents and includes a lot of formatting along with the text. The formatting will actually interfere with the SQL code in the example files so, word processor will not work for this purpose.
Unfortunately, the Notepad program that ships with Windows also doesn't work well with this purpose. So, for windows I suggest Notepad Plus Plus. It's an excellent text editor and it's free, and we'll be installing it here in this movie. Finally, you'll need the SID and CRUD applications that I wrote for demonstrating SQL. These are included in the exercise files and I'll show you how to install them with XAMPP. We're going to start by installing Notepad Plus Plus, and this is the webpage where you would download it from.
I've already downloaded the installer for Notepad Plus Plus so I'm just going to double-click on it there from the desktop. So, you may get this user account control dialog box. If you do, just go ahead and click, yes, and select the language. And go ahead and press next here. Accept the agreement and accept the default location, and say next, and accept the default here. And if you want a shortcut on the desktop you can click that and just press next and it installs.
It goes along pretty quickly. I'm actually going to uncheck the run Notepad here and select finish, because the first time I run it, I want to run it as an administrator. And that means that I just need to go in here and find it. And I'm pretty sure it is right here under local disk. Jumps around. Under Local Disc and Program Files, x86 and Notepad Plus Plus. And then I'm going to right-click on it and select, run as administrator.
You only need to do this the first time and accept the User Access Control. The reason for this is that I need to change some preferences that require the administrator access. First time you run it, you'll also get this little update thing. If there are updates to any of the plugins. I'm just going to select update. It doesn't matter for this purpose. And I'm going to accept the restarting of Notepad Plus Plus. There we go. Now, I'm going to go into settings, and under preferences, and file associations, I want to associate Notepad Plus Plus with the types of files that we're going to be using.
So, under Notepad, I'm going to select text, and under web HTML, I'm going to need a few of these, and you can only do one at a time, so HTML. HTM and PHP. For each one of these I'm pressing this little right button to move them over there to registered extensions. And then finally under Fortran Tech and SQL, I want to select SQL and move that over. So, now we have text HTML, HTM, PHP and SQL. I can press close here.
And there's just one more thing that I want to do, and you don't necessarily have to do this. This is just for the purposes of demonstration. I'm going to go into the style configurator, and for the PHP files, just because I'm going to be editing some PHP files in this movie, their default is hard to read on this screen with the low resolution that we have. You don't necessarily need to do this, you should be able to see it on your screen. But I just need to change the foreground color on a few of these things so that they are more readable because the contract is really low and the default Microsoft fonts are not terribly readable on this kind of a display.
So I'm just setting them all to just dark colors. That one's pretty dark already. And I'll save and close. And again, you probably don't need to do that, or you can select from one of their templates or do what looks pretty on your screen. So, now we've configured Notepad Plus Plus and I'm going to close it. And, I'm just going to test it really quick here. I'm going to bring up the exercise files and go into Chapter One. And just double click on this text file. And, the first time I do that I'll get this little dialogue box that asks me about how I want to open this type of file.
When I select Notepad Plus Plus, it'll open it in Notepad Plus Plus, which is great. And that all looks just fine. And also, from now on, for the other ones that I set the default for, it'll open those types of files as well. So that's fine. And we have now installed Notepad Plus Plus and it's working as expected. Next, we're going to install XAMPP and I strongly recommend that you download XAMPP from my web page. And my web page is here at bw.org/ldcsql.
It's up there on the screen. The reason for this is if you download it from the Apache Friends website, and here's their website. And you can download it from here. But, if you do download it from there, you may get a later version. Which sounds like a good thing, but in my experience, they change so much about XAMPP with every version that the installation process will likely be different. And you may not be able to successfully install everything by following the instructions in this video. So, I'm suggesting that you download this one here. Which is the one that I've already downloaded.
It's the version 1.8.3-2 for Windows. That's the one that I downloaded here, and I strongly suggest that you install this version. Again, the user account control, we're going to say yes, and now the installer will start. You may or may not get questions like this about your antivirus. With the default antivirus settings on Windows 8.1, I've said yes to this and it's installed fine, so I'm going to go yes. If you have trouble with the installation you may want to try installing it over again with your antivirus turned off, and it's also giving a warning about user account control.
All this means is that it's not going to install it in program files. Instead it's going to install it in it's own directory c:/xampp, which is just fine and is exactly what we want. So I say OK, and we start following the bouncing ball, as they say. Click next, you don't need all this stuff, I'm just going to uncheck a few of these things that you don't need. You may or may not want MySQL, we're not using it for this. Really, all we're using in this installation is Apache and PERL, you could actually uncheck everything else and it would be fine.
You may want to play around with some of this stuff, that's up to you. I'm going to select Next. And you notice that it's installing it at this location C:/xampp, and that's exactly where we want it, so I'll select Next. I uncheck this, all it does is open up a webpage with advertisements for this Bitnami stuff. You can leave it if you want, I'm unchecking it. And next, and next to begin installing. Now, this process actually takes awhile, so we're going to skip ahead.
I'll leave that checked, we're going to close it and open it again anyway. And click finish. And there's the control panel. This is the control panel for XAMPP. This is for starting and stopping the Apache server. I also did leave MySQL server installed. We're not going to actually use that. And there's a bunch of different things here that you mostly don't need. And so, for now I'm just going to close this and I'm going to navigate over to the new XAMPP directory that we got here on our c drive.
And scroll down. And there's the XAMPP control panel. And I'm just going to right click on this and say pin to task bar. And so there it is. I'm going to move it over here to the side. And now we could always start it there. There's our control panel. I'm going to go ahead and start the Apache server. I'm just going to press the start button in the column next to Apache. Again, the first time you do this you may or may not get this security alert. If you do, you want to make sure that private networks is checked and public networks is not checked.
And this'll make it work and this'll make it relatively safe. Keep in mind that running a server on your laptop or on your desktop is an inherently dangerous thing. You're a developer you need to do this, you want to actually make sure you have a good firewall, and that port 80, which is the web port for web servers, is not allowed from the outside world. We'll talk a little bit more about this in a minute, but for now I'm going to click this, allow access.
And we see that Apache is running and it's running on port 80 and port 443 which is the SSL version of port 80 and so you really want to make sure both of those are blocked in your main firewall for your network to the outside world. So, now that Apache is running we can actually close this control panel, we don't need it anymore. And we're going to open up a new tab here in whatever browser you're using, and just type in the word localhost. Like that and that should bring up this XAMPP screen. And if it does, you have successfully installed XAMPP and started the Apache web server.
Just going to click on English. That just brings up this XAMPP for Windows with the version number and everything. I'm going to click here on security. It's opening it up in a new tab. That's okay. And I just want you to see this. This is telling you that this is an unsecure installation. Which for the purposes of development, is actually pretty much what you want. You need to make sure that you have that firewall on your network all the way out of your router. And you know, if you're on a home network, most home network routers, WiFi routers, and also sometimes even the modem that you get from your cable company or however you're getting your internet, have firewalls built into them.
You just need to login to it and configure it. And so, really pay attention to that when you're doing something like running a web server inside your house or your office. So really, I just wanted to show you that. You can read it later if you like. But XAMPP, it's default configuration is very insecure. Because that gives you all the power that you need for a development environment. So now we're going to install SID and CRUD. And I'm going to open up my exercise files here. And I'm going to open another explorer Window. I'm just pressing control N on my keyboard.
And in this other window, I'm going to navigate out to our XAMPP directory, which is right there, and find htdocs, and open that. And this is where we are going to install SID and CRUD. It's very simple. Simply select all three of these directories: CRUD, SID and SQL, and, I'm going to Control drag them over there so that it says, Copy. You see there it says copy to hddocs and that's what I want. And let go of the mouse button. And, those are copied.
And then, come back over here and make sure they're still here. Yes, they are. So, these are copies and that's what we want. I'm going to open up this SQL directory and I'm just going to delete these three files. I'm holding down the control key as I select them so that they are all selected. And then I right-click and I hold down the shift key while I press delete, and, that actually deletes them instead of just throwing them in the trash. Now, I come back up here to htdocs and that's just because we don't need those. All we need in this directory is the .dv files.
These are the actual databases. SQLite stores an entire database in the file. It's really brilliant that way, and so, these are the databases that we're going to be using and they're actually now installed. And now we're going to come out to SID, and we're going to open up sid.php in our Notepad Plus Plus. And you'll notice, it's already saying that this is a Notepad Plus Plus document, because of those preferences that we set earlier. So, I just double-click on that, and it will open up in Notepad Plus Plus. And even though it's PHP, it's got some contrast, and we can read what we need to read, because of the settings that I made.
I'm going to come down here where it says, uncomment for SQLite 3. See they're already uncommented. And I'm just going to select the text in side the quote marks here, Line 39 it says define dbdir. And inside these quote marks I'm just going to select all of this. And I'm going to type something else in it's place. I'm going to type c:/, and I'm using a forward slash instead of a backslash. In Windows you normally would use a backslash, but in this context you really don't want to, you want to use a froward slash here.
And I'm going to type, XAMPP, X-A-M-P-P and another slash htdocs and another slash and capital SQL. And so that's the path to our SQL directory where our databases are. And, we need to put that there inside those quote marks. So, I'm pressing Ctrl S to save, and I'm going to close this. We had another file open before. I'm going to close that too. Come back out here to the other one. This one here, and I'm going to come back to out to htdocs, move this over here where I can get at it, and open the CRUD directory and the crud.php, and we're going to do the same thing.
Come down here. This time it's on line 29, and I'm just going to select everything up to album.db. We can leave that, so it's going to say c colon slash XAMPP, X-A-M-P-P, slash, htdocs slash SQL, in caps, slash and then, album.db. I'll press Ctrl+S and save it and close this, and now SID in XAMPP should be installed and working.
And so I'm just going to come right up here and I'm going to type localhost slash, capital SID for SID, slash, lowercase sid.php. And there's SID. SID is now working. This is our database demonstrator, SQL interactive demonstrator which I wrote, it's in PHP, and we'll talk a little bit more about it, towards the end of the course. And that is up and working, we see all of our databases here, memory, album, test, world, and that's exactly what we're suppose to see there.
Just going to drag this out onto my bookmark bar, so that I can get at it later. And then I'm going to open up CRUD instead. So, the directory is CRUD, all caps. And instead of SID, here, we're going to type CRUD. And so, it' should look like that, local host slash, capital CRUD, lower case CRUD, PHP. Press my enter key, and there's our CRUD application. And again, we'll look more at this later. For now, it's installed, I'm going to drag a bookmark out and now we can bring up either one of them pretty easily.
So, at this point everything is working, you have now successfully installed the development environment that you need to follow the exercises in this course. Now let's get started.
- 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.