Trending November 2023 # Comparison Of Different Sql Clauses # Suggested December 2023 # Top 20 Popular

You are reading the article Comparison Of Different Sql Clauses updated in November 2023 on the website Minhminhbmm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested December 2023 Comparison Of Different Sql Clauses

This article was published as a part of the Data Science Blogathon.

Introduction to SQL Clauses

SQL clauses like HAVING and WHERE both serve to filter data based on a set of conditions. The difference between the functionality of HAVING and WHERE as SQL clauses are generally asked for in SQL interview questions. In this article, we will explain the functionalities and differences between the HAVING and WHERE clauses using an example.

HAVING Clause

SQL HAVING clause fetches the necessary records from the aggregated rows or groups on the basis of the given condition. It is generally used along with the GROUP BY clause and applies to the column operations. It operates on the aggregate functions such as ‘SUM’, ‘COUNT’, ‘MAX’, ‘MIN’, or ‘AVG’. We can use the HAVING clause only with SELECT statements. It cannot be used with UPDATE or DELETE statements. The syntax is as follows:

Syntax of the HAVING Clause with the SELECT statement:

FROM Table_Name

WHERE condition

GROUP BY column_1, column_N

HAVING condition

ORDER BY column_1, column_2, column_N;

WHERE Clause

SQL WHERE clause fetches the necessary records from a single table or multiple tables that meet the given condition. The WHERE clause can function without the GROUP BY clause and can perform row operations. It is used with single row functions like character functions, general functions, case conversion functions, date functions, or number functions. We can use the WHERE clause with any SELECT, UPDATE, and DELETE statement. The syntax is as follows:

a) Syntax of the WHERE Clause with the SELECT statement:

SELECT column_1, column_2, column_3, column_N

FROM Table_Name

WHERE condition;

b) Syntax of WHERE Clause with the UPDATE statement:

UPDATE Table_Name

SET column_1=value_1, column_2=value_2, column_3=value_3, column_N=value_N

WHERE condition;

c) Syntax of WHERE Clause with the DELETE statement:

DELETE FROM Table_Name WHERE condition;

Examples of HAVING and WHERE Clause Functionality

In the following example, we will demonstrate the functionality of the HAVING and WHERE clause:

Let’s start by creating a database called Student:

Use the Student database:

Let’s create the Student_Score table with Student_ID as the primary key:

Student_ID INT NOT NULL PRIMARY KEY,

Student_Name varchar(20),

Gender varchar(20),

Math_Score INT,

Bio_Score INT);

Insert the values into the Employee_detail table, then use the SELECT command to view the contents:

VALUES(1001, ‘Tom Ford’, ‘Male’, 68, 90),

(1002, ‘Ananya Verma’, ‘Female’, 97, 86),

(1003, ‘Eva Jackson’, ‘Female’, 86, 72),

(1004, ‘John Smith’, ‘Male’, 65, 91),

(1005, ‘Tanvi Sharma’, ‘Female’, 89, 63),

(1006, ‘Lilly Mathew’, ‘Female’, 74, 82);

HAVING clause with SELECT statement:

Our goal is to find out the average Math_Score of students who are gender grouped and have an average Math_Score greater than 60 and arranged in descending order.

FROM Student_Score

GROUP BY Gender

ORDER BY AVG(Math_Score) DESC;

WHERE clause with SELECT statement:

FROM Student_Score

WHERE clause with an UPDATE statement:

For the student with Student_ID 1004, we want to update the Math_Score column to 65 and the Bio_Score column to 95.

SET Math_Score=70, Bio_Score=95

WHERE Student_ID=1004;

We can use the SELECT statement to view the updated Student_Score table:

WHERE clause with DELETE statement:

WHERE Gender=’Male’;

We can use the SELECT statement to view the filtered record:

Difference Between HAVING and WHERE Clause

HAVING WHERE

1. to fetch necessary records from the aggregated rows or groups on the basis of the specified condition. WHERE clause enables you to fetch necessary records from the table on the basis of the specified condition.

2. HAVING clause should be used along with the GROUP BY clause and is used after the GROUP BY clause. It is possible for the WHERE clause to function without the GROUP BY clause and with the GROUP BY Clause, it’s been used before the GROUP BY Clause.

3. HAVING Clause applies to the column operations. WHERE Clause applies to the row operations.

4. Aggregate functions can be used in the HAVING Clause Aggregate functions cannot be used in the WHERE Clause

5. HAVING Clause is also known as a post-filter since it selects rows after aggregate calculations have been carried out. conducted.

6. HAVING Clause can only be used with ‘SELECT’ statements, but not with ‘UPDATE’ or ‘DELETE’ statements. WHERE Clause can be used with the ‘SELECT’, ‘UPDATE’, and ‘DELETE’ statements.

7. HAVING clause can be used with multiple row functions, such as ‘SUM’, ‘COUNT’, ‘MAX’, ‘MIN’, or ‘AVG’.

WHERE Clause can be used with a single row function such as character, general, case conversion, date, or number functions such as UPPER, LOWER, REPLACE, etc.

Conclusion

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

You're reading Comparison Of Different Sql Clauses

Syntax And Examples Of Sql Ceiling

Introduction to SQL Ceiling

SQL Ceiling function is the mathematical function available in SQL that is used for the numeric type of values. These numeric values can be either integers or floating-point numbers. If the numeric value is formatted inside the string type of value as parameter then that value is also allowed for Ceiling function. In short, any value or expression that can be deduced to the numeric value can be used as a parameter to the Ceiling() mathematical function in SQL. This function helps us retrieve the minimum integer value that is greater or equivalent to the passed value.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In this article, we will learn about how Ceiling function can be used to retrieve the integral value not less than the passed numeric value or expression, its syntax, and some of the examples that can help to make the implementation of the Ceiling() function easy and understandable.

The below is the syntax for the mathematical Ceiling() function in SQL –

Ceiling(expressionOrNumber);

expressionOrNumber: expressionOrNumber can be any integer or floating-point value or even a decimal value. In case if this numeric value is wrapped into strings then these string values are also acceptable. Other than the direct specification of numeric value any expression that will ultimately deduce to a numeric value is also allowed as a parameter to the Ceiling() function.

Return value: The return value of the Ceiling() function is the smallest integer value that is greater than or equal to the value that is passed as the parameter to the function. The type of the return value is dependent on the data type of the value that is passed as the parameter to the Ceiling() function. In case if the parameter is or integer data type then the return value is also of integer type. While in other cases if the deduced value of the parameter of the direct specification of the value of the parameter is of floating-point type then the datatype of the return value is of the floating-point data type itself.

Examples of SQL Ceiling

Following are the examples are given below:

1. Using Ceiling() function with Positive Values

We will consider the positive numeric value say 3.59 and then use the Ceiling function to retrieve the smallest integer value that is greater than or equivalent to the 3.59 value. Let us execute the following SQL query statement and observe the output –

SELECT Ceiling(3.59);

Output:

Let us consider one more example of positive value. But in this example, we will use an expression that will evaluate to the value that is of numeric type. Simply consider the expression 5 * 1.65 whose actual value is 8.25 and use this expression in Ceiling() function to retrieve minimum integral value greater than passed value using following query statement –

SELECT Ceiling(5 * 1.65);

Output:

Now, we will consider a positive number wrapped as a string and use it as a parameter to Ceiling() function. For example, consider “56.569” value that is used in the following manner –

SELECT Ceiling(56.569);

Output:

2. Using Ceiling() Function with Negative Values

We will consider the negative numeric value say -65.55 and then use the Ceiling function to retrieve the smallest integer value that is greater than or equivalent to the -65.55 value. Let us execute the following SQL query statement and observe the output –

SELECT Ceiling(-65.55);

Output:

Let us consider one more example of a negative value. But in this example, we will use an expression that will evaluate to the value that is of numeric type. Simply consider the expression 8 * -91.65 whose actual value is −733.2 and use this expression in Ceiling() function to retrieve minimum integral value greater than passed value using following query statement –

SELECT Ceiling(8 * -91.65);

Output:

Now, we will consider a negative number wrapped as a string and use it as a parameter to Ceiling() function. For example, consider “-98.154″value that is used in the following manner –

SELECT Ceiling(-98.154);

Output:

3. Using Ceiling() Function with Values in The Table

Let us now see how we can use the Ceiling() function in the query statements on the values of the columns of the table. We will create one table named workers using following query statement –

CREATE TABLE `workers` ( `developer_id` int(11) NOT NULL AUTO_INCREMENT, `team_id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `position` varchar(100) DEFAULT NULL, `technology` varchar(100) DEFAULT NULL, `salary` int(11) DEFAULT NULL, PRIMARY KEY (`developer_id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

Output:

INSERT INTO `workers` VALUES (1,1,'Payal','Developer','Angular',30000), (2,1,'Heena','Developer','Angular',10000), (3,3,'Vishnu','Manager','Maven',25000), (4,3,'Rahul','Support','Digital Marketing',15000), (5,3,'Siddhesh','Tester','Maven',20000), (6,7,'Siddharth','Manager','Java',25000), (7,4,'Brahma','Developer','Digital Marketing',30000), (8,1,'Arjun','Tester','Angular',19000), (9,2,'Nitin','Developer','SQL',20000), (10,2,'Ramesh','Administrator','SQL',30000), (11,2,'Rohan','Admin',NULL,20000), (12,2,'Raj','Designer',NULL,30000);

Output:

Now we will calculate the average salary using the following query statement –

SELECT avg(salary) from workers;

Output:

If we want to retrieve the average salary in integer format with the greatest value that is greater than or equivalent to the average value using Ceiling() function using the following query statement –

SELECT Ceiling (avg(salary)) from workers;

Output:

Conclusion

Mathematical function Ceiling() is used rounding numeric values in SQL. We can retrieve the minimum value in integer format that is greater or equivalent to the passed numeric number or expression whose results can be either floating value of an integer or decimal value. The working of Ceiling() function is exactly different than Floor() function. Though both of them are used for rounding. Ceiling() funtion rounds up while Floor() function rounds down the numeric value.

Different Alternatives Of Ccleaner In Detail

Introduction to CCleaner

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

List of CCleaner Alternative

Given below is the list of CCleaner Alternatives:

1. Ashampoo WinOptimizer

It’s a highly customizable Windows cleaner and accelerator from Ashampoo. Your PC will be faster, more efficient, and safer with this software’s 23 modules. There is also a network optimizer, which allows for better network settings. The automatic section is my favorite. Auto Clean, Game Booster, and Live Tuner are all included. While working, Auto Clean consumes very little RAM. Using Live Tuner, you can speed up all of your heavy applications. This program has always provided me with a slight boost in computing power while playing a game that requires lots of processing power. Background services and programs that are not related to gaming can be disabled.

2. Advanced SystemCare

One of the most powerful optimization programs for Windows, Advanced System Care, is available. It has artificial intelligence (AI) and creates a personalized plan for your computer. Learns from your optimization habits and PC performance as you use it. This program can clean up all kinds of junk files, private information, and internet speed, making your PC faster and cleaner.

3. CleanMyPC 4. Wisecleaner

Using an easy PC optimizer, you can safely remove unusable files and increase your computer’s performance. It also provides various custom selection options that allow you to eliminate files that are no longer needed. The program removes your computer’s internet history and other traces, preserving your privacy. Rearranging and defragmenting files on your hard drive will improve computer performance.

5. AVG TuneUp

AVGIntuneUp is an AVG system optimization tool. It is most commonly used to clean up discs, increase computing performance, and speed up the startup process of a computer system. It keeps your computer running smoothly and automatically cleans your registry. As a result, computers running AVG TuneUp have very few junk programs or bloatware installed.

6. KCleaner

KCleaner was designed to be the most effective solid-state drive cleaner or hard drive available today. To provide you with all the resources you may need for documents, songs, pics, and cinema, it tracks and eliminates every useless byte. You don’t have to open KCleaner whenever you want to clean your computer because it runs in the background, and you don’t have to be concerned about it.

7. Jv16 8. Winutilities Pro

Use WinUtilities Pro, a system utility software that’s easy to use. Your computer’s performance will be improved using one of the best alternatives to CCleaner available today. Information that slows down your system is removed from discs. All traces of activity on your PC are erased with this software. Executable files can be password protected. It makes managing the memory of Windows easier.

9. Glary Utilities

Glary Utilities provide system maintenance, repair, and protection. The program includes a registry cleaner, disk cleanup, spyware detection, memory optimization, etc. Provides a comprehensive and authoritative PC cleaning utility. Fixes crash and errors that can be frustrating. Optionally, you can automate it and make it secure. Make your PC perform at its best. Glary utilities provide an intuitive and easy-to-use interface.

10. Avira Recommended Articles

We hope that this EDUCBA information on “CCleaner Alternative” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Learn The Different Methods Of Junit Assert

Introduction to JUnit assertEquals

JUnit assertEquals is one of the methods in the JUnit Library to verify the equality of two objects. For example, one of the objects may be the program output, and the other may be externally supplied to the code. The intention of the verification is to ensure that the program works as per the desired manner during the test and take action if it is not so.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

JUnit is a very powerful testing tool, in an open-source format, used for testing small as well as large blocks of code, and it has a huge collection of libraries to make testing easy and vibrant. assertEquals is one such library, and there are many more libraries under the assert family, and in this article, we will analyze them.

JUnit Assert Equals Class

The earlier version of JUnit, i.e., JUnit 3, had two different classes: the main class junit.framework.Testcase and an inherited class junit.framework.Assert. All the assert methods are called through the junit.framework.Assert class.

From JUnit version 4 onwards, Annotations were used to mark the tests, and there was no need to extend Testcase class which means that the assert methods are not directly available, but these methods could be imported in static mode from the new Assert class, and that is the reason all such methods under the new class are static methods.

The Assert methods can be imported using the statements

import static org.junit.Assert.*;

Post this import; these static methods can be used without prefix.

JUnit assertEquals Usage

Here the two objects are compared for equality. These objects may have integer, floating-point, and Boolean values. If the values are not equal, then AssertError will be thrown, and if the values are equal, it returns true.

assertEquals(a, b) is the exact code used in the comparison of the objects.

The objects A and B should be of the same data type

Objects A and B will get converted to their wrapper object type

Value in object A will be compared with B.

If values are equal, it will return true; otherwise, it will trigger failure.

JUnit Assert Methods

The class org.junit.Assert class contains the following methods in it, which can be called in to test various conditions.

assertArrayEquals

Method Name Access Specifier Return Type Details

assertArrayEquals – byte[ ] Static void Verifies bytes arrays (a, b) are equal

assertArrayEquals – char[ ] Static void Verifies char arrays (a, b) are equal

assertArrayEquals – double[ ] Static void Verifies double arrays (a, b) are equal

assertArrayEquals – float[ ] Static void Verifies float arrays (a, b) are equal

assertArrayEquals – int[ ] Static void Verifies integer arrays (a, b) are equal

assertArrayEquals – long[ ] Static void Verifies long arrays (a, b) are equal

assertArrayEquals – objects[ ] Static void Verifies object arrays (a, b) are equal

assertArrayEquals – short[ ] Static void Verifies short arrays (a, b) are equal

The exact syntax is

assertArrayEquals ( xxxx, expecteds, xxxx, actuals ) xxxx – Byte, Char, Double, Float, Int, Long, Objects, Short assertArrayEquals ( String, message, xxxx, expecteds, xxxx, actuals) - With message xxxx – Byte, Char, Double, Float, Int, Long, Objects, Short assertEquals

Access Specifier Return Type Method Name /

Detail of the method

static Void assertEquals ( double expected, double actual )

Verifies whether variables (a, b) with floating-point type are equal.

This functionality is not in use. Instead of this, a new function

assertEquals ( double expected, double actual, double epsilon ) is used

static void assertEquals ( double expected, double actual, double delta )

Verifies whether variables (a, b) with floating-point type are equal within a positive delta value

static void assertEquals ( long expected, long actual )

Verifies whether variables (a,b) with long integers type are equal

static void

Verifies whether multiple objects(a, b) are equal. It is not in use. Instead, it is recommended to use assertArrayEquals

static void assertEquals (object expected, object actual )

Verifies whether objects (a, b) are equal.

assertEquals (With Message)

Access Specifier Return Type

Method Name / Detail of the method

static Void assertEquals ( String message, double expected, double actual )

Verifies whether variables (a, b) with floating-point type are equal.

This functionality is not in use. Instead of this, a new function

assertEquals ( String message, double expected, double actual, double epsilon ) is used

static void assertEquals ( String message, double expected, double actual, double delta )

Verifies whether variables (a, b) with floating-point type are equal within a positive delta value

static void assertEquals ( String message, long expected, long actual )

Verifies whether variables (a,b) with long integers type are equal

static void assertEquals ( String message, objects [ ] expected, objects [] actuals )

Verifies whether multiple objects(a, b) are equal. It is not in use. Instead, it is recommended to use assertArrayEquals

static void assertEquals ( String message, object expected, object actual )

Verifies whether objects (a, b) are equal.

Others

Access Specifier Return Type

static void assertFalse (boolean condition)

Verifies whether the given condition is false

static void assertFalse (String message, boolean condition)

Verifies whether the given condition is false (with a message)

static void assertNotNull(String message, Object object)

Verifies whether the given object is not null

static void assertNotsame(String message, Object unexpected Object actual)

Verifies whether the two objects referred are not the same

static void assertNull(String message, Object object)

Verifies whether the given object is null

static void assertsame(String message, Object expected Object actual)

Verifies whether the two objects referred are the same

static void assertTrue (String message, boolean condition)

Verifies whether the given condition is true

Example of assertEquals import org.junit.Test; import static org,junit.Assert.assertEquals; String firstobject = "Jupiter"; String secondobject = "Jupiter"; assertEquals(firstobject , secondobject);

The above code will return true because both the strings have the same value.

Conclusion – JUnit assertEquals

Methods in Assert class, especially assertEquals, facilitate testing of equality conditions and ensure that an error-free product is delivered on time.

Explain Different Kinds Of Generators In Javascript

As we know JavaScript is a lightweight programming language in which generators were introduced in ECMAScript 2023. A generator is a process that has many output values and may be stopped and started. In JavaScript, a generator is made up of a generator function that produces an iterable Generator object.

In this article, we are going to discuss the generators in JavaScript and also the different types of generators in JavaScript with syntax and examples in detail.

Introduction to Generators in JavaScript

The generator’s function is as same as the regular function but there is a bit of difference in that the generator function can be resumed and paused. In JavaScript generally, functions are not stope when once they are invoked. Usually, the concept of generators is seen in asynchronous programming.

Syntax of Generator function in JavaScript

Now we will be going to discuss the syntax of the generator function in the JavaScript and also compare it with the regular function.

The function * syntax is used to build generator functions, and the yield keyword is used to pause them.

function * genFunc() { yield 'Hello'; yield 'World'; } const g = genFunc(); g.next(); g.next(); g.next(); …

When a generator function is first called, none of its code is run, Instead, a generator object is returned. Values are consumed by invoking the next() method of the generator, which runs code until it comes across the yield keyword, at which point it pauses and waits until the next() is invoked once more.

In the above code, after our final statement, continually calling g.next() will only produce the same return object: {value: undefined, done: true} because we have not defined anything after the ‘world’ in our genFunc() function.

The yield keyword pauses the generator function’s execution and gives the caller of the generator the value of the expression that follows it. It is comparable to the generator-based version of the return keyword. It can only be directly called from the generator function that contains yield.

Comparison with regular function function regFunc() { console.log("Hello World"); }

In the regular function, we do not use the ‘*’ function as you can see above example it also does not use the yield function. As we discussed above that the main difference between the regular function and the generator function is that the generator function can be stopped and paused. So by the above example, you can see that we don’t have the choice to stop it and directly print the whole statement together i.e “Hello world”.

As we have seen the basic of the generator functions, now let’s move to the different types of generator functions −

Normal Generator

In a normal generator, the generator works as the iterator that generates the next value after every next() method call is executed to generate a function. Let’s see an example, where we are going to yield the numbers one by one until the list ends.

function* generator_function(){ for(var cur = 0 ; cur<7; cur++){ yield cur; } } var object_generator = generator_function(); console.log(object_generator.next().value); console.log(object_generator.next().value); console.log(object_generator.next().value); console.log(object_generator.next().value); console.log(object_generator.next().value); console.log(object_generator.next().value); console.log(object_generator.next().value);

In the above code, we have created a normal general function with the yield keyword present in it and used the next() function to call it several times.

Generator with Parameters

The generator with parameters is a bit different from the normal generators and this time we have to pass a parameter with the next() function to send it to the generator function. Also every time we pass a parameter it kind of stores after the yield keyword, not before that, we will understand this concept in the upcoming example −

function* generator_function(){ console.log("start of the function") temp = yield; console.log("This is the first value passed: " + temp) temp = yield; console.log("This is the second value passed: " + temp) } var object_generator = generator_function(); object_generator.next("This is not the first "); object_generator.next("this is first"); object_generator.next("this is second"); object_generator.next();

In the above code, we have defined the generator function, and this time we are passing the parameters to it. When we first called the object the given parameter is not printed because that is for sending before the ‘yield’ keyword, then after the sent values are stored in the variables and printed and after the second printed value there will nothing happens because there is no yield present.

Generator with Object Property

Generators can be used as objects and when we will call them they will simply return the value that is assigned to them and that can be printed. To understand this concept let’s see an example.

function* generator_function(){ yield "First value" yield "Second value" yield "Third value" } var object_generator = generator_function(); console.log(object_generator.next().value); console.log(object_generator.next().value); console.log(object_generator.next().value);

In the above code, first, we have defined three yield expressions with a string present after them and when we have called the generator then the string present after them will be returned.

There are other types of generators also present like with return type and some contain another generator inside of them, etc.

Conclusion

In the article, we have learned that the generator’s function is as same as the regular function but there is a bit of difference in that the generator function can be resumed and paused. In JavaScript generally, functions are not stope when once they are invoked. Usually, the concept of generators is seen in asynchronous programming. There are various types of generators like a normal generator, with parameters, objects like property, generator contains another generator, etc.

The Art Of Query Building: Data Problems To Sql Queries

Introduction

Learning Objectives

Understand how data flows through a SQL query and use this to solve data problems.

Transform data problems into SQL queries using a keyword-based approach.

Dos and Don’ts when it comes to SQL keywords.

Finally, we’ll go through an example of using the underlying approach.

This article was published as a part of the Data Science Blogathon.

Table of Contents TABLE: Where Is My Data?

First, I like to start by considering all the tables I need in the query. You can do this by considering all the fields that will be needed to get the desired result, and then we can find them. An important thing to note is that multiple tables may have the same field. For example, user data can be present in multiple tables with different levels of aggregations. Hence, knowing what grain you want to pull in the results is essential. When building the query, I want you to pick one table, go through the steps, and return to the table. Also, if any array fields are needed in the table, now is a good time to unpack them.

FROM table_name LEFT JOIN UNNEST(table_array) AS array WHERE: What I Don’t Want?

Now that you know where your data is coming from, it’s time to know what information you need and, more importantly, what you don’t need from the table. So if the table has a partition or if the query demands filtering a certain type of record, now is the time to use it. Also, I need you to look at all fields in a table and think about all possible ways to filter your data here. You should really push yourself to add more filters.

To put it simply, the lesser data your query sees, the better it performs and avoids mistakes. Further, we often skip obvious filters as they seem too trivial; for example, if you’ve filtered on the partition date, it might still have multiple dates, so look for other date fields and add the filter.

WHERE partition_field = "date_value" AND col1 = "xyz" AND col2 IS NOT NULL ... GROUP BY: What’s the Grain?

Before you SELECT anything, I’d recommend doing a GROUP BY. This is because having this first will often constrain what you select in your query. You can no longer do a `SELECT *`, which rarely makes sense. This will also leave out duplicate records before anything, and trust me; you don’t want duplicates flowing through your query as it’s difficult to determine their origin later. This also forces you to perform aggregations.

You often don’t need a field but only the aggregated value. Having this out of the way is helpful so that the rest of the query sees lesser data. So I’d recommend having a GROUP BY in your query for every table; even if it’s not explicitly needed, it’s an excellent way to avoid duplicates and only pulls in relevant data for the query.

SELECT col1, col2 FROM table_name GROUP BY col1, col2 SELECT: What Do I Actually Want?

After doing all the work above, you can now think about what fields you’ll actually pull from the specific table. If you have followed the above steps, the scope of the fields has already been reduced to the fields that are needed for the specific results.

A `SELECT *` slows down your query and may lead to incorrect results, as you may end up with extra records. The only time you should do it is when you’re trying to do a preview of all the fields in a table. On the contrary, selecting fewer fields first and then adding them later when needed is also feasible.

CASE: Conditions

A case statement is SQL’s way of making IF-ELSE statements. These enable you to capture complex logic and show SQL’s real ability. In addition to using CASE statements for traditional applications, you should also use them to alter fields before selection. For example, if you’re not concerned about a field’s specific value but only want a discrete value like Y/N, this is the time to convert the field using CASE statements.

One thing to note here is always to have an ELSE condition that tells SQL what to do if none of your conditions are met. We’re often confident that we’ve covered all the scenarios in our CASE statement, but data always surprises us. Hence it’s better to have an ELSE condition to avoid unknown behavior. Personally, I like to add `ELSE NULL` so I can see that data didn’t fall into any of my expected scenarios.

CASE WHEN col = "value" THEN "Y" ELSE "N" END AS new_col Aggregations (Level 1): The Math

In this article, we’ll be talking about aggregations twice. At first, you should only worry about aggregations at a single table level. These are usually math-based, like sum, average, max, and min, or count-based. One thing to note for counts is that in 99% of the cases, you’d want to do a `COUNT(DISTINCT field_name)` instead of a regular `COUNT(field_name)` as the latter gives you a record count with duplicates in the specific field. A useful strategy is combining aggregations and CASE statements to capture complex logic in an easy manner. For example, building a purchase_indicator using the total transaction amount as below.

ALIAS: Nicknames

This may seem trivial, but this step is important for readability and writing correct queries. This is because many times, you’ll be deep down in your query looking for a field and not know what it is called. Hence it’s essential to make these worthwhile. Also, rather than using aliases for aggregated or derived fields, it’s helpful to use them for renaming fields with long or funky names in the table. In this way, even though you cannot do anything to the actual table, you can still call it something easy to work with in your own query.

Now if the query you’re building only uses a single table, this is where you stop. However, in most cases, there’ll be more than one table, so you can read further.

CTE: Building Blocks

CTEs or Common Table Expressions can build a temporary table inside your query without creating a real table. These are most useful for compartmentalizing your SQL query. This helps you to think clearly as every element becomes a separate table that can be combined.

At this point, you should put together all the above steps and wrap it in a CTE as done below. These also help in making changes to the query; for example, if you’re trying to edit certain conditions on a table, you can directly go to the associated CTE and make the change, enabling your change to cascade to the rest of your query.

WITH table_cte AS ( SELECT col1, chúng tôi AS col2_alias, FROM table_name LEFT JOIN UNNEST(table_array) AS array WHERE col4 = "value" GROUP BY col1, array.col2 )

Now go back to TABLEs and repeat the steps above for any other tables in your query.

JOINs: Watch Out SELECT col1, col2 FROM cte1 AS c1 JOIN cte2 AS c2 ON chúng tôi = c2.col1 GROUP BY col1, col2 Aggregations (Level 2): More Math

Now is the time to combine the metrics in the final result by aggregating the JOIN results. Because these will make our final results, it’s useful to throw in things like final aliases and FORMAT that make sure the numbers are readable with the appropriate commas.

SELECT FORMAT("%'d", SUM(amount)) AS total_amount ORDER BY: Make it Pretty

Ordering the results should always be saved for the last, as this can’t go in any CTE or subquery. The only time this can be avoided is when your query will be a production query where results are used and not read by someone. Otherwise, adding an `ORDER BY` is helpful, even if not explicitly required, as it will make reading the results much more accessible. Also, you can use fields here and CASE statements to allow for custom ordering of results.

LIMIT: Make it Digestible

Finally, if the plan with the query is to export or use the results to drive another calculation, you can skip this. However, in other cases, having the LIMIT clause is a must, which will only return a certain number of records, making it easier for you and your SQL engine. If you forget this and your query is about to return a million rows, your query will fail even without errors.

LIMIT 100 Putting It All Together

So let’s use our newly gained skills as an example. If you need more examples of queries with data and stories, head to my blog here.

The problem: We have an e-commerce store, and the marketing team wants a report of users who’ve not made a purchase in the last month. The state should break this down the user is in and the last interaction they had on the website.

WITH user_demographics AS ( SELECT user_id, address.state AS state FROM demographics LEFT JOIN UNNEST(address) AS address WHERE country = "USA" GROUP BY user_id, address.state ), user_purchases AS ( SELECT user_id, FROM transactions GROUP BY user_id ), SELECT * EXCEPT(rnk) FROM ( SELECT user_id, event, RANK() OVER(PARITION BY user_id, event ORDER BY date DESC) AS rnk ) t WHERE chúng tôi = 1 ), user_no_pruchases AS ( SELECT a.* FROM user_demographics a LEFT JOIN user_purchases b ON a.user_id = b.user_id WHERE (b.user_id IS NULL OR agg_purchase = "N") ), user_no_purchase_events AS ( SELECT user_id, state, event USING(user_id) GROUP BY user_id, state, event ) SELECT state, event, COUNT(DISTINCT user_id) AS user_count FROM user_no_purchase_events GROUP BY state, event ORDER BY state, event LIMIT 100 Conclusion

Here’s what we learned today:

We started by visiting the importance of SQL and building queries to solve business problems.

Then we delved into a step-by-step approach that leverages SQL keywords to transform data problems into queries.

In this, we highlighted common mistakes that go along with SQL keywords, for example, not having an `ELSE NULL` in a CASE statement.

We also reviewed best practices when writing SQL queries, including `GROUP BY`, to prevent duplicates.

Finally, we discussed an approach to query building using CTEs to compartmentalize your query.

Following these steps, you can transform any business problem into a SQL query that yields desired results.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Update the detailed information about Comparison Of Different Sql Clauses on the Minhminhbmm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!