Database Management SQL Tutorial

SQL Tutorial

So that you can experience writing and running SQL yourself, we are forgoing exercises from the book where you cannot test your statements and replacing it with this guide. This guide has embedded questions that you will need to enter the values for or paste screen shots where needed. It is worth 200 points (the value of exercises and case examples combined).

NOTE: Do NOT copy/paste code examples from this guide. Word embeds “smart quotes” into the document which are not compatible with phpMyAdmin. Please type the queries as you see them.

NOTE: MySQL can be case-sensitive. Please double-check the case on your statements when you enter them. My convention is that all table names and field names within our sample database will be lowercase, but that may not hold true for all databases. In addition, my convention for SQL keywords is to capitalize them within the statement.

Part 0 – Getting Started

Your Name: Click or tap here to enter text.

Part I – Logging In

1. Refer to the email sent to you at your SUNYSCCC.edu email address from me with the subject MySQL Credentials

2. Go to https://dbf.do.mdzi.me/pma

3. In the login box, enter your Username in the Username field and your Password in the Password field, then click Go

4. You will now be on the phpMyAdmin index page. The phpMyAdmin index page contains general server statistics information on the right and a tree of databases that you can access on the left. The databases include:

a. employees – a database with an example human resources system with a large number of seeded values *read-only*

b. information_schema – a system database containing a view into MySQL configuration *read-only*

c. menagerie – a small veterinary information database *read-only*

d. sakila – an example movie information database *read-only*

e. world – information about world cities *read-only*

f. hafh – book example database *read-only*

g. zagi – book example database *read-only*

h. zstu_s_*username* – a database for you to test in that you have read/write access on

5. Click on the [+] symbol next to sakila and then click on the [+] symbol next to Tables

6. Here, you see a list of database tables

7. Please use the Print Screen button on your computer and Paste a screenshot of your phpMyAdmin screen in the box below:

8. Note the tables in this database – they include actor, address, category, city, and more. phpMyAdmin allows you to view the table configuration settings and data without typing SQL, and allows you to see the SQL that you are actually running.

9. Click on actor

10. Note, at the top of the screen, you see SELECT * FROM actor That is the DML that is being executed to give you the grid data.

11. Answer this question:

What is the purpose of DML SQL Statements? Click or tap here to enter text.

12. Now, say that we want to filter those data to only show actors whose last name begins with the letter N. For that, we need to specify a WHERE clause and that our last_name field matches ‘N%’ (percent sign being the multiple character match character) using the LIKE operator.

a. Click on the Edit inline link under the SELECT statement

b. Click the empty space to the right of `actor`

c. Press Enter

d. Enter the following:

WHERE last_name LIKE ‘N%’

e. Click Go

13. Answer these questions:

How many results are there? Click or tap here to enter text.

What is the first_name of the actor whose actor_id is 122? Click or tap here to enter text.

14. As it was noted in the book, most SQL dialects have similarities, while there are some parts of the syntax that are different between dialects. In this particular SQL statement, note that phpMyAdmin placed left accents around the table name. This is technically not needed for MySQL and is a confusing standard for that DBMS. Let’s see that removing them will give the same result set.

15. Click Edit inline again

16. Remove the ` characters from around actor, leaving this SQL:

SELECT * FROM actor WHERE last_name LIKE 'N%'

17. Click Go

18. Take a screenshot of the application and paste it here:

19. phpMyAdmin has additional functionality that helps MySQL users compose UPDATE statements (Edit), INSERT statements (via Copy and Insert modes), and DELETE statements via the Delete button. As our sample databases are read-only to your User, you will not be able to use those options on the sample databases. You could however use them on your database, though we will avoid using them leaving preference to SQL.

20. Collapse the sakila database by clicking on the [-] button next to the database name and icon

Part II – CREATE(ing) and INSERT(ing) into our first Table

1. Answer this question:

What is the purpose of DDL SQL statements? Click or tap here to enter text.

1. Expand the zstu_s_*username* database

1. Note that there are no tables in the database. Let’s create one.

1. Click on the zstu_s_*username* database

1. Click on the SQL icon on the menu bar

1. Enter the following DDL into the SQL data entry field

CREATE TABLE people (

person_id INT AUTO_INCREMENT,

first_name VARCHAR(30) NOT NULL,

last_name VARCHAR(30) NOT NULL,

date_of_birth DATE NULL,

PRIMARY KEY (person_id)

);

Note: AUTO_INCREMENT is a special designation within MySQL that allows you to store a counter in the database metadata so that you do not have to calculate the next value when inserting data.

1. Click Go

1. Please paste a screenshot of your PhpMyAdmin screen here:

1. Please answer this question:

What type of SQL is the INSERT statement? Click or tap here to enter text.

1. Now that we have our table, let’s add some data.

1. Click on the SQL button at the top of the screen

1. Enter the following into the SQL data entry field:

INSERT INTO people (first_name, last_name, date_of_birth) VALUES (‘Bob’, ‘Smith’, ‘1997-01-01’);

1. Click Go

1. Note that MySQL assigned a Row ID.

What is the Row ID for Bob? Click or tap here to enter text.

NOTE: The reason that I am not telling you the Row ID is that if you were to make a mistake and create a second user by accident, our Row IDs would be out of sync. So, I will be asking you to enter the Row IDs for the remaining people in the form of a query.

1. Compose additional INSERT statements to create the following people:

First Name	Last Name	Date of Birth
Jane	Augustine	NULL
Paul	Smith	1942-10-04
Sean	Blakely	2001-09-05
Richard	Findlay	1978-11-04

Note, when entering unknown dates of birth, use the NULL keyword without single-quotes around the value. Only columns that are marked NULL on creation are NULLable. Example:

INSERT INTO people (first_name, last_name, date_of_birth) VALUES (‘John’, ‘Seymore’, NULL);

Enter your INSERT statements here before clicking GO:

Click or tap here to enter text.

1. Now, let’s query all of those people. Please compose a SELECT statement to get all columns from the people table for all rows (hint: no WHERE clause). You may use the * operator for the column select list in this query if you would like to. Enter your query here:

Click or tap here to enter text.

The results will look like this:

Part III – Adding a second table to our database and enforcing referential integrity

Now that we have our first table and some data, and we can SELECT from it, let’s create another table and link it to our current table using a foreign key constraint. This will enforce the relationship between the two tables, giving you a “Field X in table Y must exist as Field Z in table A” binary relationship.

1. Click on the zstu_s_*username* database

2. Click on SQL

3. Answer the following question:

When adding a field and selecting the data type, if the value of the data in the field is not added to, subtracted from, multiplied, or divided, we should use a VARCHAR data type instead of a numeric type. True or False? Click or tap here to enter text.

4. Enter the following SQL to create a people phones table:

CREATE TABLE people_phones (

person_id INT NOT NULL,

phone_type VARCHAR(4) NOT NULL,

phone_number VARCHAR(10) NOT NULL,

PRIMARY KEY (person_id, phone_type)

);

5. There is one issue with the design of this table: it is subject to an insertion error. See, the phone type column is not set to refer to any constrained set of values, like a validation list, so when someone accidentally enters HMOE instead of HOME, the database will take it, and when you go looking for everyone’s HOME phone numbers, you will miss that individual’s number. To fix this, we create another table address_types, which will be used for validation on both our people_phones table and our people_addresses table that you’ll create later.

6. Enter the following SQL to create a address_types table and link it to the existing people_phones table:

CREATE TABLE address_types (

address_type VARCHAR(4) NOT NULL,

address_type_description VARCHAR(15) NOT NULL,

PRIMARY KEY (address_type)

);

ALTER TABLE people_phones

ADD CONSTRAINT people_phones_address_types

FOREIGN KEY (phone_type)

REFERENCES address_types (address_type);

This will add the address_types table and then ensure that “address_type in people_phones must exist as address_type in address_types.” The people_phones_address_types is the name of the foreign key – I usually include the table names in the foreign key name, so that it is easier to identify by name. It could be FK1, but you don’t know much about what FK1 is by looking at that constraint name.

7. Now that we’ve seen an example of how to create a foreign key constraint, please create the SQL for an ALTER TABLE statement to ensure that “person_id in people_phones must exist as person_id in people.” Enter that statement here and run it on phpMyAdmin:

Click or tap here to enter text.

8. To ensure that your entry is correct, please double-check your work against this set of directions:

a. Click on zstu_s_*username*

b. Next to people_phones, click on Structure

c. Click on Relation view

d. Your screen should look like this:

9. If it does not, please update the drop-downs to match

a. Constraint properties: people_phones_people

b. Column: person_id

c. Database: zstu_s_*username*

d. Table: people

e. Column: person_id

f. Click Save

10. So, now that we have created the relational data, we need to populate the tables.

11. As people_phones relies on people for the person_id and address_types for the address_type, we must work out way in from the outside of the relationships (think ER diagramming, there would be two 1:N relationships here, one between people and people_phones and the other between address_types and addresses, where the 1 side is on the first noted table and the N is on the second noted table.

12. Create SQL statements to INSERT values into the address_types table following these data:

address_type	address_type_description
HOME	Home
CELL	Mobile
WORK	Work

Run your statements in phpMyAdmin, copy the statements using the Edit Inline button and paste them here:

Click or tap here to enter text.

13. Create SQL statements to INSERT values into the people_phones table following these data:

person_id	phone_type	phone_number	*NOTE
1	WORK	5183811200	Bob Smith
1	CELL	5185551212	Bob Smith
2	WORK	5183811201	Jane Augustine
3	CELL	5185551213	Paul Smith
4	CELL	5185551214	Sean Blakely
4	WORK	5183811202	Sean Blakely
5	CELL	5185551215	Richard Findlay
5	WORK	5183811203	Richard Findlay

* if your person_ids are different than those that I have noted here for the people indicated, I have included the person’s name in the NOTE column so you can use the appropriate person_id on your INSERT statement

Run your statements in phpMyAdmin, copy the statements using the Edit Inline button and paste them here:

Click or tap here to enter text.

14. To ensure that your data matches, please run the following SQL query and compare it with the table above:

SELECT * FROM people_phones;

Part IV – Joining Tables in a SELECT (employees database)

Before we get back to our data tables, let’s look at the employees database to get an idea of how joins work. Employees is a sample database that has a large number of rows in its six tables – one containing over 2.8 million, another over 331 thousand.

Schema:

Review these articles:

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

https://www.codeproject.com/Tips/712941/Types-of-Join-in-SQL-Server

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

At this point, we know that the primary key from one table is copied into a dependent table as a collection of keys called foreign keys. In the example above, employees emp_no is the Primary Key for employees, and in titles, emp_no is the foreign key representing that that title belongs to that person.

Click on the employees database on the left pane (it will highlight in grey)

Click on SQL

Run This

SELECT e.emp_no,

e.first_name,

e.last_name,

e.hire_date

FROM employees e;

Count? Click or tap here to enter text.

Next, let’s run this:

SELECT T.emp_no,

T.title,

T.from_date,

T.to_date

FROM titles T

Count? Click or tap here to enter text.

Now, to get the information from the related items, we can go one by one and get the Employee and then look at each of the titles for that person, or we can JOIN the two tables to give us the information. Now, if we don’t specify that emp_no is the common key, we will explode the output into what is called a Cartesian join – effectively multiplying the count from titles with employees (which is quite a few, don’t you think?). Don’t run a query without an ON syntax or equivalent keys in the WHERE clause as it would explode into a Cartesian Join, and you don’t want to do that on large data sets.

Inner Joins

Include data on the left and the right when the ON criteria matches.

So, we specify the join criteria using ON

SELECT e.emp_no,

e.first_name,

e.last_name,

e.hire_date

FROM employees e

INNER JOIN titles T ON

T.emp_no = e.emp_no

Count? Click or tap here to enter text.

Now, notice that we aren’t including any new information, even though we could have titles, though the count increased. That is because we haven’t changed the SELECT list – the list of columns whose data we are returning.

SELECT e.emp_no,

e.first_name,

e.last_name,

e.hire_date,

T.title,

T.from_date,

T.to_date

FROM employees e

INNER JOIN titles T ON

T.emp_no = e.emp_no

Count? Click or tap here to enter text.

See how some people have multiple rows? That is because you are effectively denormalizing the data when you do these joins, so if you had an excel spreadsheet with information about Peac Sumant (10009), you’d have three rows just like this where the first name and last name is copied a few times along with the changed data.

Where’d Michael go (emp_no=10000)?

Let’s modify the query for a moment:

SELECT e.emp_no,

e.first_name,

e.last_name,

e.hire_date

FROM employees e

WHERE e.emp_no = 10000;

Count? Click or tap here to enter text.

SELECT T.emp_no,

T.title,

T.from_date,

T.to_date

FROM titles T

WHERE T.emp_no = 10000;

Count? Click or tap here to enter text.

Notice that there are no rows for the second query, so that means that if we wanted to include him, we couldn’t use that type of join.

Left Outer Join

Include all data on the left table, but only right data if there is a match.

SELECT e.emp_no,

e.first_name,

e.last_name,

e.hire_date,

T.title,

T.from_date,

T.to_date

FROM employees e

LEFT JOIN titles T ON

T.emp_no = e.emp_no

Count? Click or tap here to enter text.

Notice, Michael is back, he is represented by one row, but what is in his title?

Answer? Click or tap here to enter text.

As you recall, that was a placeholder for nothingness. When there is something in the left table (employees in this query), but nothing in the right table (titles in this query), the DBMS has nothing to return.

Exclusive Left outer join

What if we’re from HR and want to figure out who doesn’t have a title record? We’d use another type of join – the excluding LEFT OUTER JOIN

SELECT e.emp_no,

e.first_name,

e.last_name,

e.hire_date,

T.title,

T.from_date,

T.to_date

FROM employees e

LEFT JOIN titles T ON

T.emp_no = e.emp_no

WHERE T.title IS NULL

Count? Click or tap here to enter text.

Part V – Joining Tables in a SELECT (our database)

Now that we’ve seen an example from a really large dataset, we can take a look at our own.

1. Click on the zstu_s_*username* database on the left pane (it will highlight in grey)

2. Click on SQL to bring up the SQL Editor

3. Enter the following SQL and click Go:

SELECT people.person_id,

first_name,

last_name,

phone_number

FROM people

INNER JOIN people_phones ON people_phones.person_id=people.person_id AND people_phones.phone_type='CELL'

4. Answer these questions:

How many results did you get back? Click or tap here to enter text.

Why isn’t Jane Augustine in this list? Click or tap here to enter text.

5. Please adapt the SQL given above to show Jane (hint: join type) and enter it here:

Click or tap here to enter text.

Part VI – Deleting

Sometimes we have to delete data from a database, to do so, we use DELETE. We almost always want to delete items in a constrained manner – always using a WHERE clause, otherwise we run risk of deleting more data than we mean to.

1. Click on SQL to bring up the SQL Editor

2. Enter the following SQL and click Go:

DELETE FROM address_types

WHERE address_type=’CELL’;

3. Why didn’t this work? Please use your own words, as I know MySQL gives a 1451 error. Click or tap here to enter text.

4. Create a SQL statement to delete the address_types entry HOME.

Run your statements in phpMyAdmin, copy the statements using the Edit Inline button and paste them here:

Click or tap here to enter text.

5. Why did this delete work? Click or tap here to enter text.

Part VII – Updating

Using the UPDATE statement, we can update a person’s data in one place without risking side-effects of non-normalized data.

1. What anomaly of non-normalized data would we be talking about if we were to update a person’s name in a database that was non-normalized? Click or tap here to enter text.

2. In this example, we are going to fix Bob Smith’s name to be his official name – Robert

3. Click on SQL to bring up the SQL Editor

4. Enter the following SQL and click Go:

UPDATE people

SET first_name=’Robert’

WHERE person_id=1;

5. Run the following SQL statement and paste a screenshot below:

SELECT * FROM people;

6. We did not have Jane Augustine’s date of birth on file. She has told us that her birthdate is March 15, 1999.

Run your statements in phpMyAdmin, copy the statements using the Edit Inline button and paste them here:

Click or tap here to enter text.

Part VIII – Wrapping Up

SQL is a powerful language. It is both simple and complex at the same time. It is very important that you understand the relationships between your tables (entities) so that you can craft SELECT, INSERT, UPDATE, and DELETE statements to your needs. There are other statements that you can investigate as well that were not covered in this guide. Pick five SQL statements for the hafh and zagi databases from the book chapter 5 and execute them, pasting a screenshot of the output here.

1. Example SQL number: Click or tap here to enter text.

2. Example SQL number: Click or tap here to enter text.

3. Example SQL number: Click or tap here to enter text.

4. Example SQL number: Click or tap here to enter text.

5. Example SQL number: Click or tap here to enter text.