Małgorzata Krętowska, PhD. Faculty of Computer Science Bialystok University of Technology

Database Systems (FCS-00016)

Schedule of practical exercises (30h)

1. Introduction to relational databases. 2. Normalization of relational databases (1NF, 2NF, 3NF). 3. Simple queries. 4. Functions which operate on rows (ORACLE). 5-6. Aggregate queries. 7-8. Sub-queries. 9. Sub-queries with EXISTS. 10. Sub-queries after SELECT and FROM. 11. Views. 12-13. DDL and DML language. 14. Test. 15. Discussion about the test results.

1. Introduction Requirements: 1. Relational database model Each student have to prepare a scheme of relational database model. It should consists of: 1. Project description (what it is for, assumptions) 2. Description of tables (relations, min 8 relations): table name, attributes (name, type, description if needed), primary and foreign keys 3. Types of relationships among tables (1 to 1, 1 to many) The database schema should be normalized (3NF – Third Normal Form) Realization time: two weeks

2. SQL During the exercises students have to learn how to write SQL queries. Students may use Oracle SQL Plus tool or other database system (MySQL, Acces). The ability of SQL queries will be verified on the base of test (at the end of the semester). Scheme of database used during the exercises:

Www pages: SQL statements http://www.w3schools.com/sql/sql_syntax.asp http://www.firstsql.com/tutor3.htm SQL in Oracle http://dbis.ucdavis.edu/courses/sqltutorial/tutorial.pdf

2. Normalization of relational databases (1NF, 2NF, 3NF)

Theory is available on the following pages: http://www.ibm.com/developerworks/web/library/wa-dbdsgn2/index.html http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx Exercises:

1. Create a set of normalized relations given the set of attributes: Card_id Name Surname Address Catalogue_no Title Author Year Rent_date Return_date

2. List the functional dependencies and create a set of normalized relations (3NF) given the table of data:

3. Simple queries 1. Check what columns have the following table: emp, dept, salgrade, assignments, projects (use the command DESCRIBE (DESC) table_name). 2. Select a name and a salary of each employee, the data should be ordered by hire date. 3. For each employee calculate a sum of his/her salary (salary with commission), specify a label for this expression. 4. Check what salary grade has each employee. 5. Select those employees that work in the departments that are placed in NEW YORK or DALLAS. 6. Select names of the employees that do not have a manager. 7. Select the employees which names start with a letter C. 8. Select the employees which names start with letters J or M. You should consider only those employees that do not receive a commission. 9. Select a name and a salary of the employees that have salary grade equal to 1 or 2. 10. Select the names of all employees with the names of the projects that they took part in. 11. Check which departments are associated with the projects. The list should consists of department and project names respectively. 12. Select all jobs appeared in each of the department. 13. Select those employees that work as a manager or researcher. Help:

SELECT [DISTINCT] expression [[AS] alias], ...

FROM table_name, ...

[WHERE condition]

[ORDER BY expression [ASC|DESC], ...];

DISTINCT – eliminates repeated rows,

expression – column name or the expression consists of column names, '*'

stands for all columns,

alias – name given for the expression appeared in SELECT,

Increasing (ASC) – by default or decreasing (DESC) ordering,

Interpretation of the wildcard characters: "%" stands for a sequence of

characters, "-" stands for one character

comparison operators: IS, LIKE

operator IN(list of values) BETWEEN ... AND ...

IS NULL

Logical operators:

=; <; >; >=; <=

Negation:

!=;

NOT (NOT nazwa_kolumny = ...; NOT BETWEEN; NOT IN; NOT LIKE; IS NOT

NULL)

Complex criterions:

AND

OR

Functions: NVL(column_name, value)

4. Functions, which operate on rows

1. Write a query, which for a given employee (a name as a parameter) gives his salary, hire date and department name.

2. Change the above query to enable writing the name in capital or small letters.

3. For each employee give the name of his manager and the difference between their salaries. Add the columns aliases.

4. Write a query, which gives the information about the employee which job begins with ‘S’ or ‘A’. Report the names and the first letters of jobs.

5. Report those departments, for which localization has at least 7 letters.

6. Report the system date in the following form:

Name of day (no of day), name of month (no of month), year – hour, minutes, seconds.

7. Write the information in what days of week (name) and months (separate columns) all the employees were hired.

8. For employees working in New York write the names and localizations of the departments of their managers.

9. Give the names of those employees which earn more that their managers.

10. Give the names of those employees which work in the same department as their manager.

Help:

ORACLE row functions:

http://www.baskent.edu.tr/~tkaracay/etudio/ders/dbase/sql/pdfSQL/RowFunctions.pdf

5-6. Aggregate queries

1. Select the average salary of the employees for different managers. 2. Select the number of the employed every day at each position (column JOB). 3. Select the number of employees at each position. 4. Name the positions which employed more than two people (consider only the employed that earn more than 1200). 5. Select the list of the managers (only identifier) and the number of their subordinates. 6. Select the average, minimal and maximal salary of employees at the different positions. 7. Select how much money was spent on each project. 8. Select the names of managers and their subordinates. 9. Calculate the difference between the salary of the managers and their subordinates. 10. For each department check how many employees were associated with each project. 11. Check how many people were employed for each project. Give project identifier and the number of employees.

Help

Aggregate functions:

Here are the following aggregate functions:

COUNT – number of records (COUNT(*) – the number of records that met

the conditions in WHERE)

AVG - average,

SUM - sum,

MAX, MIN – maximal and minimal value in a column,

Their arguments are expressions (can be DISTINCT key word). If the

expression is NULL then the record is not taken into account.

Aggregate queries

They divide records for the groups and allow to calculate aggregate

function within each group of values for a given column.

SELECT ... FROM ... WHERE ...

GROUP BY expression, ...

[HAVING condition];

7-8. Sub-queries

1. Select the names of the employees that are employed at the same position as JAMES. 2. Select the names of these employees that earn more than MILLER and less than FORD. 3. Select the name and identifier of a person that has the highest seniority. 4. Select the names of the people that were employed later than CLARK. 5. Select the names of the people that earn less than the average salary, consider only the employees from the department where BLAKE works. 6. Select the names of the people that earn more than BLAKE. 7. In each department find an employee with the highest salary. The answer should consists of the department name, employee name and a salary. 8. Check for which positions the average income (salary+commission) is higher than 2000. The answer should consists of a position name, the number of the employed at this position, and the average income. The data should be ordered by the average income. 9. Select the names of people employed at ‘CLERK’ position that earn more than people employed at ‘SALESMAN” position. 10. Select the names of the employed at a given position (a position should be specified as a parameter). 11. Select the names of those departments for which the average salary is higher than the average salary in the company. 12. Select the minimal and the maximal salary in the SALES department. 13. Select an identifier, a name and a salary (including a commission) of the employee with the highest income. 14. Select the names of the employed which salary is higher than each of the salaries of people employed at MANAGER position. 15. Find the department that employs the highest number of the people. 16. Select the names of people employed at ‘SALESMAN’ position that earn more than the average salary of MANAGERS 17. For each department list a name and a salary of the employee which income is the highest within his/her department. 18. Select those departments that employ at least four people. 19. Check in which departments the employees receive a commission. 20. For each department list the available positions and the number of people employed at these positions. Help Subqueries:

Subqueries (in parentheses) can appear inside WHERE, HAVING, and FROM.

In a sub-query you cannot use ORDER BY and UNION.

9. Sub-queries with EXISTS

1. Select projects on which worked only managers.

2. Select these projects in which doesn’t work any CLEARK.

3. Select departments in which work the highest number of employees.

4. Select departments in which works at least one employee, who was working on only one project.

5. Select the department which employs the highest number of SALESMENs.

6. Select department name, min, max, and the average salary for departments in which work people

which do not part in projects.

7. Select employees and their managers (select their names). Consider only these employees that

worked on the oldest project.

8. Select the department with the highest number of employees and in which works the employee

that has the highest salary.

9. Select these employees that have salary higher than at least one manager.

10. For each department select positions for which there is the highest number of employees. Select

the name of the department and positions.

11. Select the department with the highest number of subordinates.

12. Select hire date and salary of employees that worked (or still work) on the most recent project.

13. Select these departments that employ the highest number of managers.

14. Select employees, their salaries, and positions at which they work. Consider only these

employees that have unique salary (nobody else earns the same amount of money).

15. Select employees who spent the highest amount of hours on a project and their salary is lower

than the average salary in the company.

10. Sub-queries after SELECT and FROM

1. For each employee give the difference between its salary and the average salary in the firm.

2. For each employee give the salary and minimum, maximum and average salary in his department.

3. For each department (name) give the percent of employees which work in it.

4. For each project give the information which job in which percent is represented. Chose only those projects, in which work only not managers.

5. Give the names of job, on which work people which earn more than average salary in the firm. Additionally give the number of people which work in projects and the number of people which work in the same department as president.

Help

Example: For each Job give the percent of working people.

Sub-queries after FROM

SELECT job, (x/y)*100 percent

FROM

(select job, count(*) x from emp group by job) A,

(select count(*) y from emp) B;

Sub-queries after SELECT

SELECT job, count(*)/(select count(*) from emp)*100 percent

FROM emp

group by job;

11. Views

Solve the exercises below using views:

1 For each department list a name and a salary of the employee which income is the highest within his/her department. 2. Select project name on which worked the highest number of people (select project and employee names). Consider the employees that spent the highest amount of hours on a project and their salary is lower than the average salary in the company.

3. Select departments in which work the highest number of employees and at least one employee was working on only one project. Select departments and names of these employees. 4. For each employee give the salary and minimum, maximum and average salary in his department.

5. For each project give the information which job in which percent is represented. Chose only those projects, in which work only not managers.

Help

CREATE [OR REPLACE] VIEW view_name

[(New_column_name1, ..., New_column_nameN )]

AS query

[WITH CHECK OPTION];

Drop VIEW view_name;

Theory:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_8004.htm

12-13. DDL and DML language

1. Create tables STUDENTS , DEPARTMENT, SCHOLARSHIP consists of the following information: - index, first and second names, department, an average of grades for the last semester, sex, birthday, year of studies,... - department name, its address, ... - grades, upper and down thresholds of scholarships 2. Insert some records into the tables. 3. In table STUDENTS include some information about a room in a student dorm. 4. Try to change some of the definitions of the tables you have created (what are the limitations?). 5. How to delete a column from table that has already some records. 6. Create a table for student dorms, modify STUDENTS table respectively. 7. Create a table that will consists of a list of students that receive a scholarship (students data, name of their departments and a value of a scholarship). 8. Increase the value of scholarships for students from a department X. 9. Afterwards, “move all the students to the next year of studies (update table STUDENTS). 10. Delete all graduates. What are the consequences of this command? 11. Delete the tables.

Help

Creating tables

CREATE TABLE table_name

(column_name type integrity_relationship,

...); or CREATE TABLE table_name

AS SELECT ... FROM .... WHERE ....; types:

CHAR(N) – a string with a fixed length,

VARCHAR(N) – a string without a fixed length (in Oracle VARCHAR2(N)),

INTEGER - integrals,

FLOAT - floats,

DATE - date,

TIME - hour,

integrity_relationship:

NOT NULL - value NULL is forbidden

PRIMARY KEY – primary key,

REFERENCES table_name – foreign key from table table_name,

UNIQUE – unique values, CHECK – condition for values,

Examples:

CREATE TABLE Student

(Nr_Ind NUMBER(6) PRIMARY KEY,

Last_name VARCHAR2(20) NOT NULL,

First_name VARCHAR2(15) NOT NULL,

Middle_name VARCHAR2(15),

Birthday DATE,

Dept_no NUMBER(2) REFERENCES DEPARTMENT);

CREATE TABLE Female_Students (Nr_Ind PRIMARY KEY, Last_name,

First_name) AS SELECT Nr_Ind, Last_name, First_name FROM Students WHERE sex='F';

Updating table definition It concerns updating of definition of existing columns or adding new columns

ALTER TABLE table_name

MODIFY (column_name type integrity_relationship,

...);

or

ADD (column_name type integrity_relationship,

...);

Deleting tables

DROP TABLE table_name;

Indexes (based on Oracle) Indexes allow for efficient search for certain information. In SQL there are no strict rules on creating/manipulating the indexes: there depend on a particular database software.

CREATE [UNIQUE] INDEX index_name

ON table_name (column_name [ASC|DESC],

...); DROP INDEX index_name;

Inserting the records

INSERT INTO table_name

VALUES (first_column_value, ..., last_column_value); or INSERT INTO table_name (X_column_name, ..., Y_column_name)

VALUES (X_column_value, ..., Y_column_value);

Example:

INSERT INTO Students

VALUES (25191,'Kowalski','Jan','','79-12-23',1); or INSERT INTO Students (Nr_Ind, Last_name, First_name, Middle_name,

Birthday, Dept_no) VALUES (25331,'Nowak','Anna','',TO_DATE('01-06-75','MM-DD-YY'),2);

Updating records

UPDATE table_name

SET column_name_X = expression, ..., column_name_Y = expression,

[WHERE condition];

Example:

UPDATE TEACHERS SET salary=salary*1.5 WHERE position='Assistant';

Deleting records

DELETE FROM table_name

[WHERE condition];

Confirmation of changes in databases: DDL commands do not change the database by themselves. To confirm the changes a command COMMIT has to be called. To cancel the changes call ROLLBACK (if COMMIT did not appear, yet). In SQL*Plus (ORACLE) there is a possibility of automatic confirmation of each command (AUTOCOMMIT variable has to be set: SET AUTO ON|OFF).