Helps student to access their work database, if they have one, and how to download software if they don't
- [Instructor] Now we're going to get you ready to run your first query. This video is in two parts. If you don't have access to a database, it will tell you how to download what you need. If you do have access to a database, it's going to suggest a few tasks for you. First, for those who already have a database, to run an SQL query, you obviously need access to the database, and there are three main methods for you to do this. First, you might run your queries from reporting software, such as a business intelligence package, which allows you to run SQL queries and then returns the data to you in a report.
Second, you might be using an SQL-specific application, such as Toad. These applications let you write a query, and then display the results in a table. Third, you might be using a database interface, such as phpMyAdmin, which shows you the database structure, as well as letting you write queries and return results. If you have the choice between the three, please pick the database interface because it can be really useful to see the structure of the database when designing your query.
Whichever of the three you pick, if you're learning SQL at work, you might need to ask your IT department for new software or permissions. If they look horrified, do reassure them that you only need read-only permissions. You will not be editing or deleting a single byte of data in this course. Now, some final tips for those of you accessing a database at work. It's almost certain that your database software, interface and data will be different from mine. This is fine.
In terms of database software, I'm going to be using MySQL, but I will point out to you where queries differ between SQL versions and give you the alternative syntax for Microsoft Oracle and PostgreSQL. Now, in terms of interfaces, I'm going to be using phpMyAdmin. If you're using another database GUI, such as Oracle Express, you can look up any features I show you in your own software. phpMyAdmin is pretty basic, so if the feature exists in my software, it should exist in yours.
Now, if your interface is, instead, reporting software or an SQL editor, you won't be able to see your database at all. In these cases, just look at my screen and learn from my setup. Finally, I mentioned that our data might differ. I'm going to be using a sample MySQL database called Sakila. If you're using your own database, your field and table names will all be different. You just need to adapt my queries to suit your database. Now, for those of you who don't have access to a database at work, you're going to need server software, database software and a database interface.
I recommend that you download the same ones I'm using. It's not as scary as it sounds, and you can do it all for free. You remember that we have to run our database on a server. Now, it's a bit mind bending, but what we do is use your single computer as both client and server. You do this by putting server software onto your computer. Part of your computer can then broadcast data to the other part, and this mimics the normal setup. I recommend that you download a package of software called MAMP.
MAMP is free and available for both PCs and Macs. MAMP is a bundle. It gives you a server software called Apache HTTP Server, and it give you MySQL, the database software, too. You can download it here, and then install it, keeping the default settings. Once you've downloaded MAMP, you're still going to need phpMyAdmin, the GUI, on your server. Now, when you download MAMP, you will be invited to buy MAMP Pro, which includes phpMyAdmin in the bundle, but I recommend that you avoid that and save yourself some money, and download phpMyAdmin separately.
You must download phpMyAdmin after you have downloaded and installed MAMP. Once you have downloaded and unzipped phpMyAdmin, drag the entire thing into the bin folder. You can see there's mine there. The bin folder is within the MAMP folder. By default, the MAMP folder will be in your applications folder if you're on a Mac, or on your C drive if you're on a PC. Once you've installed both MAMP and phpMyAdmin, you need to set your server running by opening MAMP and clicking start servers.
Also, a quick note while we're here. People who've recently installed MAMP and phpMyAdmin often wonder which port to use. If you click on preferences, as I have just done, and take the Apache port number that's given by default, that should help you locate where to find phpMyAdmin. On a Mac, it defaults to 8888. Once you've got your server running, you go to a browser and type in local host, normally followed by the port number, and then phpMyAdmin, and you should see this screen.
If you get stuck, there are entire courses dedicated to tell you how to do this in greater detail. Once you've got both your server and your interface, you can download the Sakila database from MySQL, which is free.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries