Welcome to the SQL/Databases part of the Working with Data Module, part of the MSc in Computing (Data Analytics).

In this part of the module we will look at how to use SQL to perform various tasks with processing, understanding, manipulating, etc data in a Database.

SQL appears in the top three most common languages in most Data Science surveys. The reason for this is most, if not all, data is stored in a Database. The could be in the various transnational databases, or in various NoSQL, Hadoop or Big Data storage engines (Databases). SQL is the common language used to access and process the data in these Databases.

Most students have some experience of using SQL. This is good. For those that have never used SQL before, that is OK. The first couple of weeks of the module goes over the basics. YES you will need to do some independent learning. I’ve links to various tutorials and useful websites. Google will be you friend.

Notes

Click here to download these notes. The course overview and admin slides.

L0-Working with Data Intro

Video

Is not available at this time.

Pr-requisites – What you need to do before the first class.

We do not have a computer lab room allocated for this module.  We will be doing all out work in the class room.

All students must have their own Laptop for this module. You will need to install some software on your laptop (see below).

1. Review your previous SQL notes.  Remember there are various standard ways of writing SQL.  The SQL standard supports various ways of writing SQL.

2. Install Oracle SQL Developer

SQL Developer is the client software you need to use to access the database and run your SQL code. This is a free tool and needs to be downloaded and unzipped on your laptop/desktop. There is no install. Just unzip and it is ready to use.

SQL Developer is installed on all lab PCs.

To download SQL Developer go to the Download Page.

Click on the ‘Accept License’ radio button.

Then download the version of SQL Developer that has the JDK built into it.

After downloading the file, unzip it.

Then go to the unzipped folder and run the ‘sqldeveloper.exe’ file

More details about the tool can be found on the SQL Developer webpage.

3. Install Oracle Data Modeller.

Oracle (SQL Developer) Data Modeler is data modelling software you will be using in second week of this part of the module. This needs to be downloaded and unzipped on your laptop/desktop. There is no install. Just unzip and it is ready to use.

Oracle Data Modeller is installed on all lab PCs.

To download SQL Developer go to the Download Page.

Click on the ‘Accept License’ radio button.

Then download the version of Oracle Data Modeler that has the JDK built into it. (this is the first one in the download list).

More details about the is product cane be found on the Data Modeller webpage.

4. Check that you can log into the Wifi.  The School of Computing (SoC) Oracle Database should be available over the wifi.  To work on your lab exercises and assignment, you will need to connect to the wifi or use the SoC Virtual Desktop service.

5. Check out the Oracle Tutorials on SQL and PL/SQL.

6. (Optional) Install a Virtual Machine that contains an Oracle Database.

The first step is to install VirtualBox. This is software to allow you to run virtual machines.

Second step is to download and install a pre-built virtual machine. Oracle provides several virtual machines that are pre-built with lots of software. Download this pre-built virtual machine. It contains an installed Oracle Database Enterprise Edition (it has all the database features), contains some additionally installed software, and also some tutorials. Follow the installation instructions.

You will need a PC/Laptop that is 64bit and capable of running Virtualisation.

You will need to change some of the configuration settings for this machine. Change RAM to 4G, and number of CPUs to 2.

Books