Objectives
- Modification of database structure (ALTER).
- SQL statements to modify database content (INSERT, UPDATE, DELETE).
- SELECT statement to define questions over the database.
- Order results using ORDER BY.
Motivation
So far we have been working with a database description. Today, we are going to manipulate the data we want to store in the database. As part of this laboratory class, we will practice the SELECT statement:
SELECT what
FROM table
WHERE what_we_are_interested_in
ORDER BY sorting_column;
We are working on the Chinook database with the following schema.
Instructions
Step 1
First we will start by last week's topic and try to modify database structure, which is how the tables actualy look. If you look at the Chinook database model again, you might notice there is calculated total column in the invoice table, which can be calculated as sum of each item of invoice.
Task 1.1
Since you know that any calculated column can cause data modification anomalies (violates 3NF), change the invoice table and remove the total column. Fulfillment of the task can be checked by displaying structure of the invoice table.
Step 2
Well, let's say you do not have the problem with modifying the database structure. So let's put some data into the database. It is true that Chinook has a fairly decent amount of data, but you should try it too,
Task 2.1
In the customer table, create a record of yourself and one record of a person of the opposite sex. However, you can create several records (father, mother, sister, brother, president, "John Rambo",...). Insert only the required information! Other values are not needed to insert. Start with using CUSTOMERID set to 100 and for each next record increase it by 1.
Comment
Database systems support transactions - sequences of statements to be executed as a whole. We will talk in details about transactions in later lecture, but you need to know that if you want the commands to actually modify the data in the database, you must confirm the current transaction (transaction starts automatically with the first statement and ends with the confirmation (commit) or cancellation (rollback)). In order for the changes to be written into the database, you need to send to database statement::
COMMIT;
Comment
Note that using SELECT statement showing you actual inserted data does not mean that the data were trully stored in DB. It will show you only temporary how it would look like if you would store them.
Using the INSERT statement, we can insert new rows (records) into the database. If you want to change existing ones, we can use the UPDATE statement. The DELETE statement used for deletion of the rows will not be practiced now, but you can try to delete some of your newly inserted records and not using COMMIT after but ROLLBACK in order to undo the deletion.
Task 2.2
As part of the promotion of employees, the general manager decided to promote Margaret Park to the title 'Sales Manager'. Write an update on the table employee, which will reflects this fact. Fulfillment of the task should be detected using select statement.
Step 3
So now let's try the most interesting part of the database communication - we will start asking questions using the SELECT statement
But let's start lightly...
Task 3.1
Find out the names of all the tracks that the 'U2' composer has made.
Task 3.2
Find out how many seconds the song "One" from U2 takes.
Task 3.3
Which customers do we have from Central Europe?
Comment
This question is a bit harder. Is there a column in the customer table that records area of origin of customers? Or will you need to find out which countries are part of Central Europe?
Step 4
Easy, right? Let's try to incorporate some built-in functions.
Task 4.1
Find out, which employees have their birthday in February.
Comment
Which function allows you to find out the month number from the date? (Remember, you are working with Oracle database.)
Task 4.2
List a list of all tracks by showing its name, composer, unit price in dollars (UnitPrice column value), unit price in euro (1$=0.76€), unit price in czech crowns (1$=19.20CK). When listing individual converted prices, remember to correctly name the column (eg. USD, EUR, CZK) and add the unit ($, €, CK) after the value. Each price should be rounded to 2 decimal places.
Comment
Harder or more complex tasks is good to break into sub-tasks, which are easier. This way you can gradually solve even more complex tasks. For example described task can be split into simple sub-tasks like:
- How will you round the number to 2 decimal places?
- How will you calculate the value of a track in euros or CZK?
- How will you concatenate symbol of currency to the price?
- And how do you name the column?
Step 5
Okay, and now we'll try to play a little with the strings:
Task 5.1
Get a list of all customers who have their email account at gmail.com or yahoo.com.
Comment
You need to take care for any special possible problems, like not allowing emails like milan.nosal@mygmail.com
Step 6
In this last task, we will try to sort out the results. If you will find the task difficult, try again to split it into simpler sub-tasks.
Task 6.1
Get the list of all employees who were born in 70s of the last millennium. Order them starting with oldest to youngest.
This is the end of today's excercise. If you want to practice more, try to look at the additional tasks bellow.
Resources
Additional Tasks
Task A.1
Chinook
Extend the CUSTOMER table with a new column called SEX that will note customer's biological gender. About this column:
- will have a size of 1 character,
- will be mandatory,
- it can only contain the values 'M' (for Male) or 'F' (for Female),
- will have the default value set to 'M'.
The success of the task should be checked with displaying the table schema.
Task A.2
Chinook
Change the value in SEX column for each of CUSTOMER, whose first names are in following list: 'Helena', 'Kara', 'Fernanda', 'Jenifer', 'Kathy', 'Heather', 'Julia', 'Martha', 'Ellie', 'Madalena', 'Hannah', 'Camille', 'Isabelle', 'Emma' and 'Puja'
Task A.3
Company
Insert the following data into the Company table:
Ev_number, Surname, Name, Birthdate, Street, City, Num_children, Status, Salary
11, 'Bajny', 'Ivan', '05-dec-1967', 'Ruzova 12', 'Kosice', 2, 'married', 15000
22, 'Maly', 'Jan', '22-feb-1973', 'Letna 15', 'Presov', 1, 'married', 12000
33, 'Velky', 'Igor', '19-dec-1969', 'Bulharska 34', 'Zilina', 0, 'single', 10500
44, 'Kral', 'Peter', '18-jan-1945', 'Vihorlatska 50', 'Kosice', 0, 'married', 21000
55, 'Malorolnik', 'Valer', '24-mar-1972', 'Hlavna 5', 'Presov', 1, 'married', 14500
66, 'Vodny', 'Marian', '24-nov-1957', 'Gerlachovska 1', 'Zilina', 0, 'single', 16000
77, 'Kristof', 'Jan', '25-dec-1978', 'Internatna 5', 'Kosice', 1, 'married', 8000
88, 'Kocis', 'Martin', '04-máj-1977', 'Biela 56', 'Moldava', 0, 'single', 13000
99, 'Rybansky', 'Marian', '22-júl-1969', 'Ruzova 7', 'Bratislava', 3, 'married', 14000
100, 'Dodatocny', 'Jozef', '01-jan-1967', 'Modra 5', 'Bratislava', 0, 'single', 9000
Task A.4
Company
Get a list of all the cities, which are unique in table Company.
Task A.5
Company
From the Company table show surname, name and birthdate of each employees, who were born in the period between 1.1.1985 and 31.12.2005. Sort them from the oldest to the youngest.
Task A.6
Company
Find all employees not living in Kosice, who have surname containing letter "a" or their name starts with "M". Sort them alphabetically (first by surname and then by name).
Task A.7
Company
List all employees from the Company table sorted by their city. Only show their surname, name, city and salary.
Task A.8
Company
Find in table company all employees from 'Presov', who are retiring in more than 15 years (assume retirement age 60 years :-) ).