Handy SQL commands in a Data Analyst's tool kit! Part 1

Data Analyst works day in and day out with data. This data can be stored in multiple ways. One among them is to store in a relational database. SQL is a structured query language that is known to be an intuitive language, which is instrumental in querying data from a relational database system.

All professionals who are needed to work with data in terms of retrieving, manipulating, and updating data, will come across situations where they have to interact with a relational database system. And SQL is specially designed for the purpose of working with a relational database management system. SQL is a language used to communicate with the computer to resolve simple to most sophisticated problems.

Now that we know the relevance of SQL in data analysts' role, lets us see the top 10 SQL commands that come in very handy. Let’s get started.

1. SELECT and FROM

SELECT is a very frequently used command that belongs to the data manipulation language. SELECT and FROM are used for the retrieval of data from the database. They are used as part of the most basic statement to the most complicated statements. The SELECT command can be used by itself as well to simply retrieve data. SELECT tells us which columns we are looking for and FROM tell us the table in which we are looking for the data we attempt to query.

Let us see this with examples.

EX 1: Getting the column “VisitStatus_ID” from the table “VisitStatus” Query: SELECT VisitStatus_ID FROM VisitStatus;

EX 2: Getting all the columns data from the table “VisitStatus”

Query: SELECT * FROM VisitStatus;

2. WHERE

WHERE is always used alongside the SELECT command. WHERE is used to filter rows of data that meet a certain criterion. Many times is used with a combination of other operators like AND, OR, NOT, IN, NOT IN, BETWEEN.

Examples of these are as follows.

EX 1. SELECT with a WHERE clause

The row of data that satisfies the condition in the where clause is returned.

Query: SELECT * FROM VisitStatus WHERE VisitStatus_ID=’VS001';

EX 2: SELECT with a WHERE + AND clause

The row of data that satisfies the condition on both sides of the AND clause is received.

Query: SELECT * FROM “VisitStatus”

WHERE “VisitStatus_ID”=’VS002' AND “VisitStatus” = ‘Completed’;

EX 3: SELECT with a WHERE + OR clause

The row of data that satisfies the condition on either side of the OR clause is received.

Query: SELECT * FROM “VisitStatus”

WHERE “VisitStatus_ID”=’VS002' OR “VisitStatus” = ‘No Show’;

EX 4: SELECT with a WHERE + NOT clause

The row of data that satisfies the complement of the condition of the NOT clause is received.

Query: SELECT * FROM “VisitStatus”

WHERE NOT “VisitStatus” = ‘No Show’;

EX 5: SELECT with a WHERE + IN clause

All the records which have the items mentioned in the IN clause which are present in the referred column are fetched.

Query: SELECT * FROM “VisitStatus”

WHERE “VisitStatus” IN (‘No Show’, ‘Canceled’);

EX 6: SELECT with a WHERE + NOT IN clause

All the records which do not have the items mentioned in the IN clause are present in the referred column and are fetched.

Query: SELECT * FROM “VisitStatus”

WHERE “VisitStatus” NOT IN (‘No Show’, ‘Canceled’);

A combination of the logical operators can be used as per the need of the requirement (AND+OR+NOT) in any of the SQL commands.

EX 7: SELECT with a WHERE + BETWEEN clause

The record which have values lying in between the range specified in the BETWEEN clause for a column is fetched.

Query: SELECT * FROM “Patients”

WHERE “Patient_ID” BETWEEN 1 AND 5;

3.FILTER by Wildcard % and _

The records to be fetched can be filtered based on any particular pattern in a column. In doing this we can use the LIKE keyword, and this can be used in conjunction with % and _ which are the wildcard.

The %, as well as the _, can be used as a combination. There are other options that can be exercised which are combining the conditions with wildcards using AND or OR operator. Also, I would like to mention that when using LIKE it considers the patterns with case sensitivity.

· The % refers to 0, 1, or multiple characters

· The _ refers to 1 or a single character

Let us see how to use these wildcards through these examples.

EX 1: To find the patient's first names which start with K QUERY: SELECT * FROM “Patients”

WHERE “FirstName” LIKE ‘K%’;

EX 2: To find the patient's first names which contains ‘ck’ QUERY: SELECT * FROM “Patients”

WHERE “FirstName” LIKE ‘%ck%’;

EX 3: To find the patient's first names which start with ‘k’ and are at least 3 characters in length. QUERY: SELECT * FROM “Patients”

WHERE “FirstName” LIKE ‘K__%’;

EX 4: To find the patient's first names which have a in the 3rd position QUERY: SELECT * FROM “Patients”

WHERE “FirstName” LIKE ‘__a%’;

4. AS

When we need to rename a table or a column we can use the AS command. It behaves as an alias and does not change the original name of the table or column.

EX: Renaming the column VisitStatus_ID to Visit_Code in the table VisitStatus

QUERY: SELECT “VisitStatus_ID” as Visit_Code FROM “VisitStatus”;

5. DISTINCT

The DISTINCT keyword can be used in a SQL statement if we want to return rows of data where a certain column or a combination of columns returns a distinct value.

EX: Trying to get the unique entries in the gender column in the gender table

QUERY: SELECT DISTINCT “Gender” FROM “Gender”

Contd on to the next blog in part 2…