#313 - Python script to copy an excel file, open and extract some information, and add the data to a database

- Python script to handle excel data and populate a database, which is simpler, easier to troubleshoot and maintain and does not fill the drive with error dumps:

#283 - Migrating a SQL database from one PC to another

Environment Setup: Install the management tool, SQL Server Management Studio (SSMS), and the database engine, SQL Server Configuration Manager.

#115 - Chat with Luiz Vitor Lima

(English version at the bottom)

Tive o prazer de bater um papo com Luiz Vitor Lima, formado em ciência da computação, consultor SQL Server e especialista em otimizar bancos de dados. Conversamos sobre suas experiências e técnicas de melhoria de desempenho de bancos de dados.

#96 - Chat with Fabiano Neves Amorim

(English version at the bottom)

Tive o prazer de bater um papo com Fabiano Neves Amorim, engenheiro de projetos que possui uma sólida experiência em banco de dados, tornando-se uma das referências sobre SQL no Brasil. Além de obter diversas certificações nesse setor, Fabiano ganhou em 2011 o prestigioso prêmio da Microsoft Most Valuable Professional (MVP) SQL Server, que reconhece profissionais de excelência pelas suas contribuições na comunidade. Nossa conversa abordou alguns pontos da carreira do Fabiano, bem como dicas práticas para profissionais melhorarem suas habilidades em banco de dados.

#94 - MySQL how to add, remove and check a constraint in a table

In this post is shown a simple example about how to add, remove and check a constraint in a MySQL table. In this case the constraint is that the grade field is a INT value between 0 and 10.

#93 - MySQL command line client options

This link has the list of command line options that can be used by the clients. Here below some examples using for instance the option “-e” that means execute the SQL statement inside the double quotes and quit: 

#90 - MySQL cleaning imported database

In real world applications involving databases, often the data has to be cleaned. In this post, some techniques are presented, such as converting the data type of a field, creating a script to repeat the steps in different columns and using functions from MySQL such as ENUM(), IF(), SUBSTRING(), CONCAT(), REGEX, PREPARE and EXECUTE statement. Some of the commands are sent from the MySQL Workbench while other commands are sent directly from the windows prompt.

#81 - MySQL using the RANK() function

The RANK() function was introduced on MySQL Server 8.0, and it enables the programmer to rank the data by different ways. 

#80 - Another resource for practicing Data Science interview questions

On the previous post, it was showed one website with great content to develop your skills in Data Science by practicing in interview questions.

#78 - MySQL style tips

Writing SQL code following some style guidelines help to improve the quality of the queries making them easier to read and to maintain.

#76 - MySQL Managing users and privileges

One of the database admin tasks is to manage the user’s privileges. In some applications the databases hold critical information, therefore if should be carefully implemented. Some of the types of access that can be granted to the users are typically Read, Write, Edit and Delete. The syntax to execute these actions in MySQL are for example SELECT, INSERT, UPDATE, DELETE.

#75 - MySQL dumping and restoring a database

Dumping a database is useful for backing-up and restoring tasks. 

The prompt commands are almost identical in both cases:

#74 - MySQL Scripting

Scripting in MySQL is useful to automate tasks and procedures. The scripts are written in the MySQL syntax inside a .sql file, which can be written with any text editor or directly from the MySQL GUI.

#73 - Data Science interview questions

This website offers a great way to keep continuously practicing on key topics related to data science, such as Python, statistics and SQL. The user can subscribe to their mailing list and receive for free 3 interview questions per week. There's also a paid version with additional benefits. 

#67 - Importing and exporting csv data to/from MySQL

How to upload and download a csv file into/from MySQL. This is particularly useful to manipulate data for data analysis. 

#53 - MySQL Tips

Below is a summary of frequently used commands in MySQL. The types of data handled are usually numbers, strings, date and time.

#47 - Changing the password in a MySQL DB and updating the connection with Ignition SCADA server

In this post we will see a method for changing a MySQL database password and how to update this parameter when connecting to a SCADA Ignition server. I recently had to do this operation after updating the MySQL version.