Tag: Sql

Using Vertabelo’s SQL Script Editor

Vertabelo’s new SQL Script tool lets you write and save SQL scripts and run them against a connected database. Creating an SQL Script Start by clicking on the Create Document button. It’s the first one on the main toolbar. The New Document window appears. Now select SQL script and click on the Create button. The New SQL script window will be displayed. Name your script using the Name field and click the CREATE button.

Hire or Get Hired: A Data Model for the Recruitment Process

No matter which side of the equation you’re on, sometimes it’s tough to find a qualified person for a specific job. In this post, we look at a data model to help recruiters and HR departments stay organized during the hiring process. Most of us have been involved in the hiring process – most often as the job applicant. However, we can also find ourselves involved on the hiring side, maybe by testing the applicant’s technical knowledge.

Running Microsoft SQL Server on Linux

If you were a fan of Linux and SQL Server, you basically had to have two operating systems at all times. But now, Microsoft has made a Linux version of their relational database management system. How does it work, and how well does it perform? Microsoft products used to be Windows-centric, but in recent years Microsoft CEO Satya Nadella has started moving the company towards cloud-based and open-source projects and enabling their software to run on other platforms.

A Database Model for a Renting Service

Renting goods and services is very popular today. Services like Airbnb rubbed the renting lamp and let the genie out, especially for travelers. They’ve opened up new horizons, and in the future we can expect that all kinds of rental services will become even more common. In this article, we’ll describe a database model that could be used to run an application for renting apartments, rooms, and anything else you can think of.

A Data Model for a Medical Appointment Booking App

Booking a doctor’s appointment using an online app is an innovation that simplifies the entire process. Let’s dive into the data model behind an appointment booking app. Why use an app? It makes it easier for people to find the doctors of their choice, letting them see the doctor’s professional records and patient reviews. When someone finds a doctor they like, they can book an appointment with them without leaving the app.

An Event Management Data Model

Organizing an event is a lot of work! In this article, we examine the data model behind an event organization app. If you’ve ever tried to organize an event for more than ten people (and don’t count parties or business meetings here) you know how complicated event management can be! Have we invited everyone? Have they confirmed if they are coming? Is the venue booked and prepared? Who will host the event?

A Real Estate Agency Data Model

Other than location, what’s it take to run a successful real estate business? We examine a data model to help real estate agencies stay organized. Buying, selling, and renting apartments or houses is really big business today. Most people are happy to pay a fee and let a professional real estate agency do the work for them. On the other hand, the company could act in its own behalf, buying properties to resell or rent.

Everything You Need to Know About MySQL Partitions

What is MySQL partitioning? What kind of partition types are there? How do you know if this is something your database engine supports? In this article, we tell you what you need to know about partitioning in MySQL. MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data (a.k.a. partitions) across the file system based on a set of user-defined rules (a.

A Data Model to Keep Track of Your Most Precious Possession

Being healthy and fit is a lifestyle, not a fad. People who realize the value of health make it a priority, keeping records of all their fitness-related facts. In this article, we’ll examine the design of the database behind a health and fitness application. There are many applications which let users log their health and fitness information. A couple of big players like Apple, Google, and Microsoft have launched their own development APIs specifically for this market.

A Process Management Data Model

What kind of data model can handle all the planning and activities used in process management? In this article, we discuss one design for a process management database. Process management is a fairly straightforward and common concept. At its core, process management is simply deciding what needs to be accomplished – building a car or creating an app, for example – and then figuring out how to do it. Of course, the actual process itself is more complicated!

Improving Our Online Job Portal Data Model

In this era of tough competition, job portals are not just platforms for publishing and finding jobs. They are leveraging advanced services and features to keep their customers engaged. Let’s dive into some advanced features and build a data model that can handle them. I explained the basic features needed for a job portal website in a previous article. The model is shown below. We’ll consider this model as a base, which we will change to meet the new requirements.

The Proper Way to Handle Multiple Time Zones in MySQL

In this article, I’ll walk you through some fundamental considerations for working with date- and time-related data in MySQL. We’ll also look at how to handle multiple time zones and daylight saving time changes. Let’s first address some core concepts that will help us understand the underlying complexity of time-related data. It is important to notice that these concepts apply when representing a point in time rather than an absolute duration.

Supporting R in SQL Server 2016

SQL Server R Services combine the power and flexibility of the open-source R language with enterprise-level tools for data storage and management, workflow development, and reporting and visualization. This article introduces SQL R Services and the R language. What Is R and How Is It Used? R is the most widely used language for statistics, data mining, and machine learning. (R is also the name of the environment and the engine that executes code written in the R language.

A look at algorithms used in RDBMS implementations of DWH systems

When you’re using a data warehouse, some actions get repeated over and over. We will take a look at four common algorithms used to deal with these situations. Most DWH (data warehouse) systems today are on a RDBMS (relational database management system) platform. These databases (Oracle, DB2, or Microsoft SQL Server) are widely used, easy to work with, and mature – a very important thing to bear in mind when choosing a platform.

Again and Again! Managing Recurring Events In a Data Model

A recurring event, by definition, is an event that recurs at an interval; it’s also called a periodic event. There are many applications which allow their users to setup recurring events. How does a database system manage recurring events? In this article, we’ll explore one way that they are handled. Recurrence is not easy for applications to deal with. It can become a hurricane task, especially when it comes to covering every possible recurring scenario – including creating bi-weekly or quarterly events or allowing the rescheduling of all future event instances.

MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.

.tips { margin-top: 0px; text-align: left; max-width: 90%; padding: 20px 20px 30px 30px; background-color: #f2f2f2; text-indent: 0px; border: 0px solid #360063; border-radius: 8px; -webkit-border-radius: 8px; -moz-border-radius: 8px; -khtml-border-radius: 8px; } .blog_article h5 { font: normal normal bold 1.05em 'Lato'; } Whenever you need to save datetime data, a question arises about what MySQL type to use. Do you go with a native MySQL DATE type or use an INT field to store date and time info as a plain number?

Stretch Databases and Temporal Tables in SQL Server 2016

Two new features in SQL Server 2016 make expanding your database’s historical data storage much easier. Here’s how to implement them. Storage. It’s a problem that anyone with a constantly-growing database has to face. We maintain great volumes of data that are rarely queried by business users. Most of these are historical or versioned data that someone checks once a year to see how “product X sales looked in 2005” or “record Z has changed over time”.

Postresql Monitoring with SQL

PostgreSQL provides an activity-tracking module called the statistics collector, which tracks table access and other internal events. If your database is experiencing long wait times, you may be able to use this tool and some simple SQL to find and fix the problem. Tracking Postgres Activity With the Statistics Collector Tool The statistics collector is designed to keep records about internal activity in a Postgres database manager. It can: count table and index access in both disk-block and individual-row terms.

Database Modeling Tips

When you were learning database concepts, data modeling looked pretty easy, didn’t it? You knew all the rules, and modeling seemed like a game: get a challenge, do your best, and eventually solve it. Job well done! Moving up to the next level – and so on. As you continue, though, you’ll see that database modeling is also an art. Many cases require a totally new approach. Everything can be done ‘by the book’, but sometimes you get better results when you go less orthodox.

CHAR and VARCHAR Data Types in Different Database Engines

Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores a fixed amount of information. However, you don’t have to fill all the available CHAR space – a shorter value will work. This is so similar to VARCHAR that I decided to explore the differences between these two types. Before diving into the details, let’s start with some basic information.

Why Does Oracle Sometimes Not Drop an Index Associated with a Primary Key or Unique Constraint?

Database schema migration is never an easy job. In fact, it can really be a headache, even when you’re working with a familiar system. For example, at times Oracle 10g may not drop the associated index for a primary key or unique constraint that has been dropped. In this article, I am going to explain when and why this happens. The Story: I’ve been working on the development of an e-commerce platform.

The Reference Data Pattern: Extensible and Flexible

Having reference tables in your database is no big deal, right? You just need to tie a code or ID with a description for each reference type. But what if you literally have dozens and dozens of reference tables? Is there an alternative to the one-table-per-type approach? Read on to discover a generic and extensible database design for handling all your reference data. This unusual-looking diagram is a bird’s-eye view of a logical data model (LDM) containing all the reference types for an enterprise system.

Business Logic in the Database. Yes or No? It Depends!

We’ve had tremendously positive feedback on my recent article that talked about “Why SQL is neither legacy, nor low-level, nor difficult, nor the wrong place for (business) data logic, but simply awesome” both within the blog’s comment section as well as on reddit. However, one of the sections triggered very controversial feedback. Clearly, not everyone agreed to: Fallacy #5: The database is the wrong place for business logic

Tip #5 – How to quote table names in generated SQL script

Sometimes, you want all identifiers in your SQL script to be quoted. For example, you want them to have a particular case. Luckily, Vertabelo allows you to quote all SQL identifiers in your generated script to prevent you from any SQL name conflicts. Go to your model: Click the SQL generation settings tab in the Model properties panel on the right: Check Quote all SQL identifiers:

50 Shades of NULL – The Different Meanings of NULL in SQL

Tony Hoare, who is mostly referred to as the inventor of the NULL reference, now calls it a billion-dollar mistake which pretty much all languages are now “suffering” from, including SQL. Quoting Tony (from his Wikipedia article): I call it my billion-dollar mistake. It was the invention of the null reference in 1965. At that time, I was designing the first comprehensive type system for references in an object oriented language (ALGOL W).

Interesting Changes in MySQL 5.7

The General Availability version of MySQL is still version 5.6, but the development release of MySQL 5.7 definitely introduces some exciting changes to the world of database management systems. Is it worth giving a try? In this article, we’ll have a closer look at a few brand-new features that may help you decide to do so. Native JSON Support JSON (short for Java Script Object Notation) is a format for storing information which can be a good alternative to XML.

Why SQL Is Neither Legacy, nor Low-Level, nor Difficult, nor the Wrong Place for (Business) Data Logic, but Is Simply Awesome!

The following fallacies are things that I hear all the time. SQL is legacy. Why can’t we work with more modern tech? – Timeless. E.g. by someone who thinks that NoSQL databases are “modern”. SQL is low level, like assembler. Would you prefer to work with assembler or with Java? Similarly, would you prefer to work with SQL or with Hibernate? – Timeless. Someone who thinks that SQL is low-level.

Analysing the SQL Server Numeric Data Types

A common challenge for database modellers is deciding which data type is the best fit for a particular column. It is a problem which involves consideration of both the properties and the scale of the data that will be stored, and in no case is this more evident than when handling numeric values because of the large variety of alternatives that most relational databases provide for their storage.

SQL Server Datatypes: Common Modeling Dilemmas

When designing a database, early decisions can have a huge impact on the performance and storage requirements. These decisions can be difficult to change later, as most subsequent work will depend on the physical model. This article highlights some common design decisions, flaws, and misconceptions. Creating a Primary Key: Uniqueidentifer or Integer When defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.

Formatting Ad-Hoc Reports in PostgreSQL

OR/M and noSQL guys see SQL as an awkward way to store and retrieve objects from the database. Here at Vertabelo, we see SQL as a useful language to query data. Making reports in pure SQL is a pure pleasure. Running an interactive query is almost the same experience as asking a human to provide information. At some point you’ll have a need to write a report in a few seconds.

Virtual Column

The concept of views and function-based indexes has been known for many years. One of the brand new solutions is a virtual column – a feature introduced in Oracle 11g. Apart from database giant, some well known DB vendors, like MariaDB and SQL Server, support the idea of computed columns. So let’s give virtual columns a try and examine their basic usage. Generally, there are two kinds of virtual columns:

How to Trigger a Crude CRUD Database Prank

Learning new things should be fun. I’ve read about database triggers multiple times but I didn’t have a chance to use them. Triggers are not popular these days especially in web development. Let’s mix learning and making fun (of others). What is a trigger for? It’s a kind of aspect of programming, an extra business logic executed on a given action. With triggers you can do complex validation, update balance on summary tables, record the who, what and when of changes to data and so on.

Global Temporary Table

Many features have arisen throughout the evolution of the Oracle database. In this article, let’s focus on temporary tables. They were introduced fairly late (Oracle 8i) and are now often considered to be indispensable for DBAs and developers. Description It’s worth mentioning up front that the table itself is not temporary, but rather the data within it. The data in such a table is stored only as long as the session or transaction lasts and is private for each session, however the definition is visible to all sessions.

How Does a Database Sort Strings?

Different languages have different alphabets and different ways to order letters within those alphabets. For example, a Polish character Ł comes right after L and before M. In Swedish, a letter Å comes almost at the end, right after Z. In French diacritics marks have no impact on the alphabetical order, so the letters À, Á and  are treated as the letter A when sorting strings. A collation is a set of rules that defines how to compare and sort character strings.

Basic Date and Time Functions in MS SQL Server

As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server. Let’s start with functions that extract a year, month and day from a given date. declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day SQL Server uses GETDATE() and SYSDATETIME() to get a current date and time.

What Is a Database Index?

Sooner or later there comes a moment when database performance is no longer satisfactory. One of the very first things you should turn to when that happens is database indexing. This article will give you a general overview on what indexes are without digging into too much detail. We’ll discuss additional database index topics in future articles. In general, a database index is a data structure used to improve queries execution time.

Oracle Synonyms

Synonyms are a very powerful feature of Oracle. They are auxiliary names that relate to other database objects: tables, procedures, views, etc. They work like Unix hard links; a pointer to point to an object that exists somewhere else. Synonyms can be created as PRIVATE (by default) or PUBLIC. public synonyms are available to all users in the database. private synonyms exist only in specific user schema (they are available only to a user and to grantees for the underlying object) The syntax for a synonym is as follows:

Vertabelo API

.border-radius-5px { border-radius: 5px; } Generating a token We've added an API to our Vertabelo application to help you incorporate some automation into your build system. Here is a short instruction on how to use the Vertabelo API. First of all, you have to enable access to the API. You'll need have at least a "Basic" account plan or be a member of a company. Go to the My account page.

Date Arithmetic in PostgreSQL

If your default programming language, like mine, is Java, you most likely wince at the very thought of date arithmetic. (It’s changed for the better with Java 8 but Vertabelo is not there yet.) The date arithmetic API in PostgreSQL is like a breath of fresh air. Interval Arithmetic You can represent a time interval: select interval '2 days'; '2 days' select interval '3 hours'; 03:00:00

SQL Performance Explained – the must-read book

Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013. Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled “Indexes: The neglected performance all-rounder.” Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought.

Handling Database Structure Changes

In the previous article we wrote a simple Android app allowing the user to manage his ToDo list. He could add new tasks, mark them as done and delete them. That article showed how to create an SQLite DB in an automated way and how to do some simple CRUD operations on it. Let’s say that the first version of an application is released, we distribute it (i.e., it shows up in Google Play), people download and use it.

Top-N and Pagination Queries

Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way. Example Let’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in the skijump_results table. There is no index on the skijump_results table.

Using an SQLite Database on Android Platform – Introduction

According to the report of the International Data Corporation, Android operating system reached more than 80% market share during the 3rd quarter of 2013. Together with iOS, Android dominates in the mobile devices’ world. This made me think that it may be worth writing a few words about how to create mobile applications for these two mobile platforms. But not the kind of “hello world” applications – there are plenty of tutorials about that.

ON DELETE RESTRICT vs NO ACTION

When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities: ON DELETE SET NULL ON DELETE CASCADE ON DELETE NO ACTION ON DELETE RESTRICT Today we’ll investigate the subtle difference between the last two options. In Some Databases There Is No Difference at All In Oracle, there is no RESTRICT keyword. The only option is NO ACTION.

Designing a Database: Should a Primary Key Be Natural or Surrogate?

Suppose we design a database. We’ve created some tables, each one has a few columns. Now we need to choose columns to be primary keys (PK) and make references between tables. And here some inexperienced designers face the dilemma – should a primary key be natural or surrogate? There’s one and only one answer to that question: it depends. If anyone ever tried to convince you that you should have only natural keys or only surrogate keys, just smile :)

How to Count XML Nodes in PostgreSQL 9.1

Recently I was given a task which involved counting, with a single SQL query, all tables in each model version of Vertabelo. Vertabelo internally stores each model version as an XML file (download a sample XML file). So my task was to count all XML nodes satisfying a certain XPATH expression. Vertabelo runs on PostgreSQL 9.1, so PostgreSQL 9.1 tools were all I could use. To begin at the end, the final query looks like this: