SQL

Articles in the SQL category in Technology Tips.

Prepare For a SQL Interview

Last updated on by topcat

For data-related roles such as data scientists or data analysts, one of the interviews will inevitably be on SQL. This makes sense because SQL is what you use to extract data from databases, and as such is considered as an important fundamental skill set for the role.

One question people generally ask is, “How do I prepare for a SQL interview?” Here we will provide the answer to that question. We’ll start of with the general format of a SQL interview, then go into how to prepare for the interview, and finally we provide a few tips on what to do during the actual interview.

General format

In a SQL interview, the first five minutes or so will be spent on doing introductions. For this part, you should have a short script ready (no more than two minutes) that goes into your background and why you are interested in the role.

Continue reading →

Posted in SQL

Composite Key In SQL

Last updated on by topcat

Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types.

Below is the SQL syntax for specifying a composite key:

Continue reading →

Surrogate Key In Database Design

Last updated on by topcat

In database design, it is a good practice to have a primary key for each table. There are two ways to specify a primary key: The first is to use part of the data as the primary key. For example, a table that includes information on employees may use Social Security Number as the primary key. This type of key is called a natural key. The second is to use a new field with artificially-generated values whose sole purpose is to be used as a primary key. This is called a surrogate key.

A surrogate key has the following characteristics:

Continue reading →

Rename Table in SQL

Last updated on by topcat

Sometimes, it may be necessary to rename a table. There is no standard way to rename a table, and the implementation varies by RDBMS. Below we discuss how we can rename a table in MySQL, Oracle, and SQL Server.

MySQL

In MySQL, we can rename a table using one of the following methods:

Method 1

RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME

Method 2

ALTER TABLE OLD_TABLE_NAME
RENAME TO NEW_TABLE_NAME

Continue reading →

Find 2nd Largest Value Using SQL

Last updated on by topcat

We all know that the MAX function can be used to find the largest value in SQL. How, then, can we write a single-pass SQL that can be used across different database systems to find the second largest value in a column? Single-pass means only one SQL query gets executed, as opposed to having multiple SQL statements using temporary tables to store intermediate results.

Continue reading →

Find Duplicates in SQL

Last updated on by topcat

SQL does not provide a built-in capability to find duplicates in a table. Fortunately, it is fairly easy to write a SQL query that does it. The idea is to count the number of occurrences for each value, and then use the HAVING condition to show only the values that appear more than once.

SELECT COLUMN_NAME, COUNT(*)
FROM TABLE_NAME
GROUP BY COLUMN_NAME
HAVING (COUNT(*) > 1);

Continue reading →

Copy a table in SQL

Last updated on by topcat

In a relational database, sometimes there is a need to copy a table in SQL. This post talks about several different scenarios on doing this, and how to use SQL to accomplish each scenario.

Copy a table with all data

To copy a table with all the data, simply create a new table and populate the table with SELECT * from the original table. This will copy over the table structure as well as all the data that was in the original table.

The syntax you would use is

CREATE TABLE TABLE_NAME_2
SELECT * FROM TABLE_NAME 1;

Please note that this will not copy over the constraints or indexes associated with this table.

Continue reading →