SQL language in data analysis
SQL language training for Oracle database. It shows how to create tables and views in a database. Allows you to learn the terminology and tools necessary for proper communication with the server. It allows to manage database objects and use them in everyday work.
Benefit from:
– Polish National Programme for Scientific Education – OPEN
– Operational Programme Knowledge Education Development
and get funding for training.
Scope of training:
- Communication with Oracle SQL Server
- Creation and configuration of a data source using ODBC
- Data exchange with SQL Server – discussion of data import and export tools
- ODBC and Connection Wizard for sheet and pivot table data analysis in Microsoft Excel
- Microsoft Access as SQL database client – data import, combined tables and management
- Oracle SQL Developer in creating queries in text and graphic mode
Oracle SQL Server in practice
- Server objects and their applications, tables, views and constituent procedures
- Building tables using Oracle SQL Developer’s graphical user interface
- Basic data types in SQL and their application
- Selection and saving of SQL views
- SQL statements, download and modification of records SELECT, UPDATE, INSERT, DELETE, SELECT INTO
- Operations on combined tables – JOIN, LEFT, RIGHT, OUTER connections
- Sorting and limiting the result of an ORDER BY ASC/DESC, TOP, TOP PERCENT query
- Combining the results of multiple tables and queries using UNION
- Deleting duplicate values from DISTINCT, DISTINCTROW query result
- Aggregation of results in COUNT, MIN, MAX, SUM queries
- Operators in SQL =, <>, >=, <=, IN, BETWEEN AND, LIKE and criteria in queries AND, OR, NOT
- Functions and expressions – character strings LEFT, RIGHT, MID, REPLACE, numbers and dates DATE, DATEADD, DATEDIFF
Advanced operations in SQL
- Create TABLE, VIEW and other objects using SQL CREATE command
- Modification and deletion of existing database objects ALTER and DROP command
- Advanced table linking in SQL view
- Use of created objects in external applications – Microsoft Excel and Access
- Advanced table creation – default values, relations, indexes
- Optimising the operation of queries
- Creating and launching procedures
- Conversion and modification of data
- Nesting of queries
Introduction to PL-SQL programming
- Controlling the program flow
- Use of variables in procedures, local and global variables
- Use of DECLARE, GO in constituent procedures
- Temporary tables and table variables
Benefits for the organisation:
- Reduced decision-making time by eliminating file sharing with the server
- Increasing the possibilities of interpretation of the data held
- Creating reports and statements that are not possible in the classic way
- Increasing the effectiveness of work related to the creation of reports and statements
- Enhancing the security of stored data and access control
For whom?
- Analysts processing large amounts of data, HR, accounting and analysis staff
- Employees whose task is to collect and process large amounts of data
Benefits for the participant:
- Ability to use data from the database
- Creation of advanced reports directly on the server
- Sharing selected data with colleagues in a file format
T Testimonials
