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 some of the lab exercises in previous weeks.

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.

L1-Intro to DBs and SQL

Lab Exercises

Lab 1. Connection to the Database Server

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.

Task 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 lecturer for details about your schema name and password. These will be different to your network username and password.

Schema Name = <assigned in class>
Passsword = <Student Number>
Host = redwood.ict.ad.dit.ie
Port = 1521
Service Name = pdb12c.ict.ad.dit.ie

Task 2 – Create your connection
You will be using Oracle SQL Developer to connect to the Oracle Database and to run your SQL.
SQL Developer is installed on the PCs in all the computer labs.
Alternatively, you can download and install SQL Developer on your own laptop. then connect to the DIT network and access the database from there.
IMPORTANT: Just because you have installed SQL Developer does not mean you have an Oracle Database installed!

To create your connection to your schema in the Oracle Database, open SQL Developer. Click on the green plus icon

Then enter your schema and connections details

 

 

 

 

 

 

 

 

Sometimes there might be DNS issues and if you do encounter these you can use the IP address (147.252.234.49) instead of the service name

Errors: 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.

Task 3 – 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

Task 3 – 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;

Task 4 – 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.

  

Task 4 – Write some SQL statements to Insert, Update, Select and Delete some data
Write SQL statements to perform the following

4.1 – Insert a record for your own student information

4.2 – Query the student record that you just inserted

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

4.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)

4.5 – Insert at least 4 records into the table created in 4.4

4.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.

4.7 – Update the records in course codes table to have the correct value for the FULL_PART_TIME attribure.

4.8 – Query the course codes table to verify that the data is correct.

4.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

4.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

Task 5 – Draw an ER diagram (optional)
Draw an ER diagram (on paper) to represent the two tables (Students and the course codes)
Are there any issues with the design vs what you have defined for the tables.
If there are any issues, how should these be resolved.

Task 6 – Use Oracle Data Modeler (complete this after we have covered ER Diagrams in the next class)
Create the ER diagram using Oracle Data Modeler
Generate the DDL script
Compare the DDL script with your code for creating the two tables.
What are the differences?

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

Task 7 – 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.

Task 8 – 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

Task 9 – Write SQL queries for the following

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

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

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

9.4 – List the Minimum, Maximum and Average salaries where job title is ‘MANAGER’

9.5 – List details of employees who has a commission that is greater than 50% of their salary

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

9.7 – List the details of the manager of each department

9.8 – List the average salary by each job title

9.9 – List the average salary by each location

9.10 – Create a new table that has the same structure as EMPLOYEE and call it MANAGERS. Write a SQL statement that will inserted into the MANAGERS tables all employees who have the job title of ‘MANAGER’ and ‘PRESIDENT’

Lab4. Connect to the Database from Home (Optional)

You need to be connected to the wifi to gain access to the database. Alternatively, if you want to work from home you will need a way to connect into DIT. To do this you will need to use the VDI service from the School of Computing to access the Oracle Database from home – VDI Insructions
   login into the VDI
SQL Developer can be found on the desktop

Additional Reading

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