SQL Introduction/SQL Developer/SQL Practice

We’ve already explored DynamoDB a little bit, which is a NoSQL database because it relies on a key-value (in our case it was the BYU ID). But what if some of the data doesn’t have a BYU ID? SQL databases store information in tables and that data that can be accessed with SQL (structured query language). This is really powerful. They have different use cases. Most of our data at BYU is on a relational database on-premise called Oracle.

Before we start, make sure that you have the VPN/SQL Developer -> VPN/SQL Developer

To access the data we are going to need to make sure we are on the VPN.

You will connect from your machine to machines in the Data Center and other locations with a secure VPN connection. You do this using a Virtual Private Network (VPN). We use a product by Cisco Systems Inc. called AnyConnect

Using SQL Developer

Now we are ready to use the SQL Developer. Open up CESCPY. It should look like this:

If you look in the “Tables” files, you will notice that it is empty. That’s because all the table are in the “Other Users” file.

That is a lot of tables. Let’s look at “PRO” user with the table “PERSON”

If you click on “PERSON,” the first thing you will see is a table that has all all the the columns you can see. As you might notice, there is some data in there that merits a a 14 to 30 letter password.

Go to the data tab:

This table has the information of anyone that has ever been connected to BYU. As you can imagine, that is a lot of people. You could probably scroll through this for hours and not find the information you are looking for. Let’s do this a better way. Go back to the CESCPY page:

Once you are there, type in the following SQL code using your BYU ID:

This will find the first name, surname, and home town of the person with the given BYU ID. Left click the code and then run it.

You should get something like this:

Pretty cool, right? Well, what else can you do with SQL statements? Let’s try to count the data. Count how many people from your hometown we have records for here at BYU.

Have you ever wanted to change your name? You can do it now. This is only in a copy of the actual data so it won’t do anything permanent. Just note that you are going to run the functions separately. Run the second and then the first:

And for the last trick, delete yourself from the table.

Obviously, this is a command you are going to use sparingly and carefully in the future.

There is a lot more you can do with SQL statements. Take some time to check out one of the tutorials below:

The SQL tutorials for these are very thorough and if/when you run into something you’ve forgotten how to do, these should be your first resources. Code academy asks for you to sign up but w3schools doesn’t, so which ever you’re more comfortable with.

• https://www.codecademy.com/learn/learn-sql (Good source for a very general basic introduction to SQL)
• https://www.w3schools.com/sql/default.asp (Basic SQL programming information/syntax)

After you have checked those out, take some time to make some query to different tables in the database.