WWD_WK1

Introduction

This is the first week of Working with Data Module covering SQL and Databases. This week we will cover some of the basics. Some of you will know all of this already, some of you might be a bit rusty and this might be all new to some of you.

We will move quickly through various SQL features each week. There will be some exercises each week and it is expected that you will have completed these before the next class. Some of the exercises are dependent on you completing the previous lab exercises.

This week we look at Databases, what they are used for and a quick introduction to SQL.

Check out this SQL Cheat Sheet

Notes

Click here to download the notes.

Video

Videos of Notes/Lecture


130 SQL Statements in 20 Minutes

Lab Exercises

Lab 1. Connection to the Database Server

[this lab exercise will take approx. 10-15min to complete. Depending in the number of issues (which I hope will be none!) this could take approx 30min]

The following instructions are for getting connected to the Oracle Database that is provided by the School of Computing.

The same instructions will work with almost all Oracle Databases except for the connection details. You will need to get these from the DBA.

Step 1 – Get your connection details
The following instructions only relate to connecting the the Oracle database in the School of Computing. For this you need to be connected to the DIT network.

Contact your me for details about your schema name and password.   I will give details of this in class.

Step 2 – Create your connection

Follow the instructions here to create a connection to the Oracle Database.

Errors logging in: If you encounter an error connecting to the database then

You probably entered some of the connection details incorrectly. Re-­‐check everything.
Are you connected to the network.

Step 3 (Optional) – Changing your password — You don’t have to do this!

NB: If you do the following and forget your password, we will need to ask the DBA to create a new password for you and re-enable your schema. The DBA is scary.

When connected to your schema in SQL Developer, open a SQL Worksheet and run the following command.

alter user <username> identified by <password>;

You will need to change <username> to the name of your schema, and <password> to your new password.

Don’t forget your password. Remember the DBA is scary

Lab 2. Creating some tables, adding data and writing some SQL

[this lab exercise will take approx 30-40min to complete]

Step 2-1 – Create a Table and Insert some data
Here is the demo script that was used in class. Load this into a SQL Worksheet in SQL Developer and run each command. Make sure you understand what is happening

drop table student cascade constraints;

create table STUDENT (
student_number VARCHAR2(20) PRIMARY KEY,
first_name VARCHAR2(20),
surname VARCHAR2(20),
dob DATE,
prog_code VARCHAR2(6));

insert into STUDENT (student_number, first_name, surname, dob, prog_code)
values ('D020150120', 'Brendan', 'Tierney', to_date('19/01/1995', 'DD/MM/YYYY'), 'DT228B');

insert into STUDENT (student_number, first_name, surname, dob, prog_code)
values ('D020150121', 'Damian', 'Gordon', to_date('20/06/1965', 'DD/MM/YYYY'), 'DT228B');

insert into STUDENT (student_number, first_name, surname, dob, prog_code)
values ('D020150122', 'Deirdre', 'Lawless', to_date('04/10/1973', 'DD/MM/YYYY'), 'DT228B');

insert into STUDENT (student_number, first_name, surname, dob, prog_code)
values ('D020150123', 'Robert', 'Ross', to_date('28/12/2000', 'DD/MM/YYYY'), 'DT228B');

select * from student;
select first_name from student where prog_code = 'DT228B';
select first_name from student where first_name like 'D%';

select * from student where student_number = 'D020150123';

update STUDENT
set prog_code = 'DT228A'
where student_number = 'D020150123';

select * from student where student_number = 'D020150123';


delete from STUDENT where student_number = 'D020150123';
delete from STUDENT where first_name like 'D%';
select * from STUDENT;

Step 2-2 – Use the Object Tee (on left hand side of SQL Developer)
Expand the object tree/menu on the left-hand side of SQL Developer.

Select the STUDENT table and use the various information tabs and menus to explore the table and the data.

  

Step 2-3 – Write some SQL statements to Insert, Update, Select and Delete some data
Write SQL statements to perform the following

2-3-1 – Insert a record for your own student information

2-3-2 – Query the student record that you just inserted

2-3-3 – Update your student record changing two of the attributes. These two updates should be done using the one update statement

2-3-4 – Create a table that contains Course Codes with the following attributes. You can decide the appropriate name of the table and the data types of each attribute.

COURSE_ID (contains a unique identifier for the course)
COURSE_CODE (contains the course code e.g. DT228A, DT228B, …)
COURSE_DESCRIPTION (contains a description/name of the course)

2-3-5 – Insert at least 4 records into the table created in 2-3-4

2-3-6 – Modify the table structure to contain a new attributed called FULL_PART_TIME. This attribute is to contain a value that indicates if the course is for full-time or part-time students.

2-3-7 – Update the records in course codes table to have the correct value for the FULL_PART_TIME attribure.

2-3-8 – Query the course codes table to verify that the data is correct.

2-3-9 – Write a query that joins the STUDENT table with the course codes table, and returns the following

Student Name (this should be one value that combines FIRST_NAME and SURNAME, separated by a space)
DOB
COURSE_DESCRIPTION

2-3-10 – Write a query that displays the following

COURSE_DESCRIPTION
a count of the number of students in each course.

You will need to use a COUNT function and a GROUP BY clause

Lab 3. More queries, adding, updating and removing data

[this lab exercise will take approx 15-20min to complete. Can be completed as Home Work before the next class]

Step 3-1 – How to run a Script containing multiple SQL statements

Download the following file and save to your computer  master_detail.sql

Run the following command in a SQL worksheet in SQL Developer

@<location where your file is saved>\master_detail.sql

replace <location where your file is saved> with the name for the directory, for example

@c:\downloads\master_detail.sql

This script will create four new tables in your schema, called DEPT and EMP.

Step 3-2 – Explore these new tables using the Object Tree and other tabs and features of SQL Developer
Explore the structure of these new tables in the Object Tree and associated tabs.

You many need to refresh the Object Tree contents. To do this right click on ‘Tables’ and select ‘Refresh’ from the menu.

You list of Tables should look like the following

Step 3-3 – Write SQL queries for the following

3.1 – List the employee details and the name of their department location

3.2 – List the total number of employees at each department location name

3.3 – List the employees who have the job title of ‘MANAGER’

3.4 – List the employees and their length of service (based on HIREDATE)

3.5 – List the details of the manager of each department

What to prepare for next week

Complete the above exercises

Additional Reading

The Next 50 Years of Databases

Michael Stonebreaker – Those Who Forget the Past Are Doomed to Repeat It

Say No to Venn Diagrams when Explaining JOINTS

Additional Tutorials using the HR Schema

SQL here’s 8 reasons we still use it today

Introduction to Oracle Database
Introduction to SQL
Oracle SQL Quick Start Guide Tutorial
Instead of connecting to the HR schema, run this script instead. It will load the tables and data to your schema

SQL Language Reference Manual

Gartner – Critical Capabilities for Operational DBMS

16 Parts of the SQL Standard