Course: SQL query language

Infopulse University Ukraine

Programme Description

Course: SQL query language

Infopulse University Ukraine

This course is instructor-led-online. It is not like video lecture and automatic tasks results checks. You will communicate with the trainer and he will answer your questions and will correct your mistakes.

Please be sure that you have at least B2 level of English to participate this course

SQL (stands for Structured Query Language), is a computer language, which is used to communicate with and manipulate databases. To get the most from the huge amounts of data they collect, many businesses must become versed in SQL. SQL is used by businesses as a way to access and manipulate the information and data that is stored in their databases, as well as for creating and altering new data that stored database tables. A database is a tool for collecting and organizing information. Databases can store information about products, orders, payments or anything else. Many databases often start as a plain text file or spreadsheet, but as they grow larger one will find it helpful to transfer them to a database created by a database management system. This course will familiarize you with SQL and will help you use databases proficiently and with high output.

Course outline

Introduction. Basic concepts and definitions

  • What is SQL and why do you need it
  • SQL standards
  • Strengths and weaknesses
  • What is a database? Database management system MySQL.

Relational theory

  • Basic Concepts
  • Relations. Keys of relations
  • Normal forms. Results of normalization
  • Relational operations

Getting Started with DB

  • Differences between SQL and relational theory
  • RDBMS MySQL
  • Database client application Squirrel
  • Quick notes of SQL syntax

Table definition tools and data manipulation

  • Table definition tools
  • Subordinate relations
  • Data integrity constraints

Data manipulation. Part 1 Fetching data from table

  • Training database and input data
  • Formulating the task
  • Answering the questionnaire
  • SELECT statement syntax
  • Semantics of query
  • Composing SELECT step by step

Fetching data from the table - WHERE clause

  • The concept of a predicate. Rules of the logical expressions
  • Comparisons predicates, LIKE, BETWEEN, IN, IS NULL, MATCH

Fetching data from the table - aggregation

  • Aggregate functions
  • HAVING clause
  • Operators DISTINCT and ALL

Practice tasks set №1 Fetching data from the table - subqueries

  • The scheme of subquery work
  • Subqueries in WHERE clause. Predicates once again
  • Subqueries in columns
  • Subqueries in the FROM clause

Fetching data from the tables – joining tables

  • Relationship with the relational theory
  • What might be in the FROM clause
  • Joining tables using JOIN operator
  • Inner Joins
  • Outer Joins
  • Join on inequality
  • The union, division, subtraction

Data Manipulation. Part 2 Adding data to a table - INSERT Changing table data - UPDATE Adding-or-changing(merging) table data – INSERT … ON UPDATE Deleting data from a table - DELETE e. Merging data. Practice tasks set №2 Recursive queries

  • The concept of hierarchical data representation
  • Data processing hierarchy by using the adjacent data model
  • Data processing hierarchy using the nested set model

Triggers

  • Triggers usage
  • Imposing corporate constraints
  • BEFORE and AFTER triggers
  • INSERT, UPDATE and DELETE triggers
  • ROW and STATEMENT triggers
  • Special objects OLD and NEW

Creating Views

  • Advantages and disadvantages of creating views
  • Updatable views.
  • Views with restrictions

Authorization tools

  • Data dictionary
  • Users manipulation
  • Granting and revoking rights
  • Access levels

Transaction Management

  • ACID transaction
  • Transactions phenomenon
  • Isolation levels

Query performance tuning

  • Large amounts of data
  • Query planner
  • Indexing
  • Partitioning
  • Join algorithms

Practice tasks set №3 Database Design using UML

  • Requirements Analysis
  • Creation of class diagram
  • Association, composition and aggregation relationships
  • Transition from classes diagram to the ERD

Final project: creation of billing system of mobile operator

  • Preparation of functional requirements for project
  • Preparation of technical requirements
  • Creation of database
  • Filling the database and calculation the necessary indicators
  • Composing of user’s bill

To be able successfully to attend our training sessions you have to have the B2 level of English at least.

This school offers programs in:
  • English


Last updated November 26, 2017
Duration & Price
This course is Online
Start Date
Start date
Open Enrolment
Duration
Duration
20 
Full time
Price
Price
120 USD
10 sessions, 2 hours, 2 times a week
Information
Deadline
Locations
Ukraine - Kyiv, Kyiv City
Start date : Open Enrolment
Application deadline Request Info
End date Request Info
Dates
Open Enrolment
Ukraine - Kyiv, Kyiv City
Application deadline Request Info
End date Request Info