SQL Applications

Garant předmětu:

Lecturer:

Tutor:

Supervisor:

Department of Software Engineering

Synopsis:

Practical realization of database system according to general principles of database analysis.

Requirements:

Passing of 18DATS

Syllabus of lectures:

1. Database platforms, interface, script, DDL, DML.

2. Creation of table and index in DDL.

3. Data updating, projection and restriction in DML.

4. Sorting, aggregation and the second restriction in DML.

5. The NULL value in table, expression and during restriction.

6. Set of values and nested query.

7. View as virtual table and its creation in DDL.

8. Realization of integrity constrains in DDL.

9. Joins of data sources and hierarchical views.

10. Optimization of query and fast operations over data sets.

11. Creation of procedures in DDL, branching and loop.

12. Procedure which returns a table and exceptions.

13. Events in database environment and triggers.

Syllabus of tutorials:

1 Databse platforms, interface, script, DDL, DML.

2 Creation of table and index in DDL.

3 Update, projection and restriction in DML.

4 Sorting, aggregation and second restriction in DML.

5 NULL value in table, expression and restriction.

6 Set of values and nested querry.

7 View as virtual table and its creation in DDL.

8 Realization of integrity constrains in DDL.

9 Joining of data sources and hierarchical views.

10 Querry optimization and fast set operations.

11 Creation of procedures in DDL, branching and loop.

12 Table returning procedure and exceptions.

13 Events in database system and triggers.

14 Layer of views and procedures between data and client.

Study Objective:

Knowledge:

Exercise DDL SQL, DML SQL and PL/SQL on various examples. General rules of DB system realization and independency on platform of DB server are the main aims.

Abilities:

Orientation in given subject and ability to solve real tasks in SQL and PL/SQL.

Study materials:

Key references:

[1] Stephens, R., Plew, R., Jones, A. D. Sams Teach Yourself: SQL in 24 Hours. 6th edition. Sams Publishing, 2015.

[2] Rockoff , L. The Language of SQL. 2nd edition. Addison-Wesley Professional, 2016.

Recommended references:

[3] Molinaro, A. SQL Cookbook: Query Solutions and Techniques for Database Developers. Sebastopol (CA, USA):

O’Reilly, 2005.

Note:

Further information:

No time-table has been prepared for this course

The course is a part of the following study plans:

• Aplikace softwarového inženýrství (elective course)
• Aplikované matematicko-stochastické metody (elective course)
• Aplikace informatiky v přírodních vědách (compulsory course in the program)