Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect

Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect
5 (100%) 1 vote[s]

Introduction

This tutorial includes information about SQL (DDL, DML) that I have gathered during my professional life. This is the minimum you need to know while working with databases. If there is a need to use complex SQL constructions, then usually I surf the MSDN library, which can be easily found on the internet. To my mind, it is very difficult to keep everything in your head and, by the way, there is no need for this. I recommend that you should know all the main constructions used in most relational databases such as Oracle, MySQL, and Firebird. Still, they may differ in data types. For example, to create objects (tables, constraints, indexes, etc.), you may simply use integrated development environment (IDE) to work with databases and there is no need in studying visual tools for a particular database type (MS SQL, Oracle, MySQL, Firebird, etc.). This is convenient because you can see the whole text, and you do not need to look through numerous tabs to create, for example, an index or a constraint. If you are constantly working with databases, creating, modifying, and especially rebuilding an object using scripts is much faster than in a visual mode. Besides, in my opinion, in the script mode (with due precision), it is easier to specify and control rules for naming objects. In addition, it is convenient to use scripts when you need to transfer database changes from a test database to a production database. Read More

Conditional Split Transformation: Export Data from SQL Server into Oracle and MySQL Databases

Conditional Split Transformation: Export Data from SQL Server into Oracle and MySQL Databases
4 (80%) 9 vote[s]

In this article, I have explained how we can divide and export data of SQL Server database table into Oracle and MySQL databases. That process is based on the condition defined in SSIS as “conditional split transformation”. Conditional split transformation is like case statement of any programming language. Using conditional, we can redirect the output of a component in multiple destinations that is based on a condition defined in the conditional split component. Read More

Exporting Oracle Table to Excel Worksheet

Exporting Oracle Table to Excel Worksheet
4 (80%) 4 vote[s]

In this article, I will explain how we can divide and export data of an Oracle database table into multiple worksheets of an Excel file with help of the SQL Server integration service package.

Often, DBAs asked to generate Ad-Hoc reports from a database. Recently I have been asked to generate a report from the database. They want me to provide a report in multiple worksheets of an Excel file. There are different ways to do it but the query was resource-intensive. Hence I want to extract data from the database with one execution and decided to use the SQL Server integration services package. SQL Server data tools have one component called Conditional Split Transformation which can be used to achieve the task. Read More

Configuring Heterogeneous Database Replication – SQL Server to Oracle

Configuring Heterogeneous Database Replication – SQL Server to Oracle
3 (60%) 4 vote[s]

Introduction

SQL Server Replication is an SQL Server feature that allows us to transfer data from one instance to another for such purposes as consolidating data into a reporting environment or migrations. I personally would not consider SQL Server Replication as a high availability technology even though some people consider it to be.

Read More

Oracle Database Security: Database Auditing

Oracle Database Security: Database Auditing
3.9 (77.78%) 9 vote[s]

In this article, I will continue with Oracle Database Security and I will present some important facts about standard database auditing, audit triggers, and audit policies in Oracle. Database auditing has two components: monitoring and persistent registration of established database activity sets and events. The purposes of database auditing are non-repudiation, investigation of suspicious activities, detection of problems generated by configurations regarding authorization (resources access), compliance with actual legislation and control. Read More

Database Security in Oracle

Database Security in Oracle
3.3 (66.67%) 9 vote[s]

There is no secret that information makes the world go around currently. If an enterprise takes care of its intellectual property and each employee can easily get the necessary information, the enterprise can hope for the growth. If there is chaos in data, the enterprise will fail despite the team spirit.

In this article, we are going to explore the database security basics and examples of information protection in Oracle. Actually, the theoretical basics for protecting information in the database, which we are going to consider in this article, will be also useful to people working with other databases.

Read More

Example of Improving Query Performance with Indexes

Example of Improving Query Performance with Indexes
4.3 (86.67%) 3 vote[s]

In this article, we’ll look at how an index can improve the query performance.

Introduction 

Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement.

They aren’t a “silver bullet” – they don’t always solve performance problems with SELECT statements. However, they can certainly help.

Let’s consider this on a particular example.

Read More

50 Shades of Oracle Database Certification Exam

50 Shades of Oracle Database Certification Exam
4.3 (86.67%) 3 vote[s]

In this article, I would like to talk about one of the basic certifications from Oracle – Oracle Database SQL Certified Expert. Unfortunately, this certification has become unavailable recently, but still, this article may be useful for preparing for other certifications and exams from Oracle. I wish a good read to everyone who wants to know which questions and tricks may await them and wants to be ahead of the game. Read More