One use case that happens often in SQL is the need to retrieve today’s date. This could be you are doing an analysis based on recent data, or it could be you are building a script that needs to retrieve the date dynamically.
Unfortunately there is no standard SQL that allows you to retrieve today’s date across different database systems. So in this post we have gathered the SQL statement needed to retrieve today’s date for the most popular database systems:
SELECT SYSDATE FROM DUAL;
SELECT current date FROM sysibm.sysdummy1;
SELECT CURRENT_DATE or SELECT CURRENT_DATE();
Technically the answer is yes, it is possible to have multiple WHERE clauses in a SQL statement. For example, if you are using a subquery, you can certainly employ a WHERE clause within the subquery, and then again in the outer query. Another example is when you are combining two SQL statements with a UNION / UNION ALL / INTERSECT / MINUS. In this case, both SQL statements can have their own WHERE clause.
Sometimes when you look at other people’s SQL code, you’ll see
as part of the code. If you haven’t seen this before, you might be wondering why people do this. After all, 1=1 always evaluates to TRUE, so that apparently makes this line of code redundant. As it turns out, there are two reasons why this is done:
Reason 1: Testing
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.
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.
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:
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:
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.
In MySQL, we can rename a table using one of the following methods:
RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME
ALTER TABLE OLD_TABLE_NAME
RENAME TO NEW_TABLE_NAME
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.