Trending February 2024 # Most Frequently Used Linux Iptables Rules With Examples # Suggested March 2024 # Top 4 Popular

You are reading the article Most Frequently Used Linux Iptables Rules With Examples updated in February 2024 on the website Tai-facebook.edu.vn. 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 March 2024 Most Frequently Used Linux Iptables Rules With Examples

This article will help you to create IPtables rules that you can directly use for your daily or routine needs, These examples will act as basic templates for you to work on iptables with these rules which suit your specific requirement.

Deleting the IPtables or Existing Rules

Before you start building new IPtables set of rules, you should clean up all the default rules, and existing rules. Use the IPtables flush command, below are some examples –

#iptables --flush (or) # iptables --F Default Policies Chain

The default policy is ACCEPT, change the policy to DROP for all the INPUT, FORWARD, OUTPUT.

# iptables -P INPUT DROP # iptables -P FORWARD DROP # iptables -P OUTPUT DROP

For every firewall rule, we need to define two rules, i.e., one for In-coming and another for Out-going.

If we trust the internal users, we can use the DROP for incoming rules, and the default outgoing will be ACCEPT.

Allowing HTTP & HTTPS Incoming Connections

The below rules will allow all the incoming traffic of HTTP & HTTPS (80 & 443)

iptables -A INPUT -i eth0 -p tcp --dport 80 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 80 -m state --state ESTABLISHED -j ACCEPT iptables -A INPUT -i eth0 -p tcp --dport 443 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 443 -m state --state ESTABLISHED -j ACCEPT Allowing only SSH to a Network

The below rules will allow only outgoing ssh connection from the internal network means we can ssh only from 192.168.87.0/24 network only

iptables -A OUTPUT -o eth0 -p tcp -d 192.168.100.0/24 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A INPUT -i eth0 -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT Allowing the Incoming MySQL port (3306) for TCP Traffic.

Below is the example which has incoming & outgoing traffic on port 3306 (mysql) for eth0 adaptor.

iptables -A INPUT -i eth0 -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT Allowing Incoming MySQL Port (3306) for a Specific Network

The below example will allow 3306 (mysql) for a specific network 192.168.87.x.

iptables -A INPUT -i eth0 -p tcp -s 192.168.87.0/24 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT Allowing Multiple Ports with a Single Rule

The below rules will allow incoming connections from outside to multiple ports, instead of writing multiple rules, we can also write rules with multiple ports together as shown below.

Here, were are allowing mysql, Http & Https in a single rule.

iptables -A INPUT -i eth0 -p tcp -m multiport --dports 3306,80,443 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp -m multiport --sports 3306,80,443 -m state --state ESTABLISHED -j ACCEPT Allowing Outgoing MySQL

This is different from the incoming connection, we allow both new and established connections on the OUTPUT chain, but whereas in INPUT, we allow only the established chain.

This rule will allow only outgoing connection to MySQL when we try to connect to MySQL server from our Linux box.

iptables -A OUTPUT -o eth0 -p tcp --dport 3306-m state --state NEW,ESTABLISHED -j ACCEPT iptables -A INPUT -i eth0 -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT Allow Sendmail Traffic

These rules will allow mails using sendmail or postfix port 25.

iptables -A INPUT -i eth0 -p tcp --dport 25 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 25 -m state --state ESTABLISHED -j ACCEPT Allowing IMAP & POP3 Ports

This rule will allow to send or receive emails from IMAP or POP3

iptables -A INPUT -i eth0 -p tcp --dport 143 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 143 -m state --state ESTABLISHED -j ACCEPT iptables -A INPUT -i eth0 -p tcp --dport 110 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 110 -m state --state ESTABLISHED -j ACCEPT Forward a Port to 5722 to 22(SSH)

These rules will forward the total traffic which comes from port 5722 to port 22. That means, the incoming connection for ssh can come from both 5722 and 22.

iptables -t nat -A PREROUTING -p tcp -d 192.168.87.100 --dport 5722 -j DNAT --to 192.168.87.200:22 Allowing Port 873 (rsync) for Backups

These rules will allow to you to take backups or copy data using rsync from a specific network

iptables -A INPUT -i eth0 -p tcp -s 192.168.87.0/24 --dport 873 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -o eth0 -p tcp --sport 873 -m state --state ESTABLISHED -j ACCEPT Blocking an IP address

If we want to block a particular IP address.

BLOCK_ADDRESS="192.168.87.100" # iptables -A INPUT -s "$BLOCK_ADDRESS" -j DROP

This will be useful if we want to block some IP address where they are downloading or trying to access the server, where we can block the IP for further investigation.

# iptables -A INPUT -i eth0 -s “$ BLOCK_ADDRESS ” -j DROP # iptables -A INPUT -i eth0 -p tcp -s “$ BLOCK_ADDRESS ” -j DROP

This above example will block the TCP/IP traffic on the eth0 for that particular IP address.

We can add a network in the variable if you want to restrict access to the server from outside

By using the above iptables rules or modifying the rules and ports, we can secure the connection or network/server. We can also modify the network or ports accordingly to fit our environment. And these iptables rules are written in a simple shell script format, so we can use them in writing the shell scripts to apply on multiple servers.

You're reading Most Frequently Used Linux Iptables Rules With Examples

Most Frequently Asked Postgresql Interview Questions

The f

Relational object database.

SQL support and extensibility

API and database validation flexibility.

MVCC and procedural languages,

WAL and Client-Server.

2. List the various data types supported by PostgreSQL.

The following are some of the new data types added to PostgreSQL:

UUID

Numeric types

Boolean

Character types

Temporal types

Geometric primitives

Arbitrary precision numeric

XML

Arrays, etc.

3. What are PostgreSQL’s tokens?

In PostgreSQL, tokens serve as the building elements of source code. They are comprised of a variety of special character symbols. Tokens are keywords mainly made up of predefined SQL commands and meanings. Commands comprise a sequence of tokens separated by a semicolon (‘;’). These may comprise of a constant, a quoted identifier, additional identifiers, a keyword, or a constant. Typically, Whitespace separates tokens.

4. What are the PostgreSQL Indices?

Indexes are a special PostgreSQL tool used to improve data retrieval from databases. A database index is similar to a book index. An index provides access to all the values in the indexed columns.PostgreSQL indexes let the database server locate and retrieve specific rows contained within a particular framework. B-tree, hash, GiST, SP-GiST, GIN, and BRIN are other examples. In PostgreSQL, users are indeed free to create their own indices. However, indices increase the complexity of data processing operations and are rarely employed.

5. How do I set up a PostgreSQL database?

There are two ways to generate databases. The CREATE DATABASE SQL command comes first.

Using the following syntax, we may build the database:-CREATE DATABASE ;

The second approach makes use of the createdb command.

We could establish the database with the following syntax:- createdb [option…] [description].

Depending on the use scenario, the createDB command may take many arguments.

6. How you can create a table in PostgreSQL?

You may create a new table by defining the table’s name, along with the names and types of each column:

CREATE TABLE [IF NOT EXISTS] table_name ( column1 datatype(length) column_contraint, column2 datatype(length) column_contraint, . . . columnn datatype(length) column_contraint, table_constraints );

7. Contrast ‘PostgreSQL’ to ‘MongoDB’

PostgreSQL is a SQL database in which data is stored in rows and columns of tables. It supports notions like entity-relationship integrity and JOINS. The PostgreSQL querying language is SQL. PostgreSQL supports vertical scaling. This necessitates the usage of large servers for data storage. This results in the need for downtime to update. It performs better if your application requires relational databases or if you need to carry out complex queries that exceed the capabilities of chúng tôi contrast, MongoDB is a NoSQL database. There is no necessity for a schema, therefore unstructured data can be stored. Data is saved in BSON documents, the structure of which may be reformed by the user. MongoDB queries are written in JavaScript. As a result of its adaptability for horizontal scaling, extra servers may be added as needed with low to no downtime. A use case that necessitates a highly scalable, distributed database that holds unstructured data is suited for this technology.

8. What is PostgreSQL’s Multi-Version concurrency control?

PostgreSQL uses MVCC, also regarded as Multi-version concurrency control, to implement transactions. It is used to prevent unintended database lockout in the system. Each transaction that queries a database sees a different version of the database. This prevents the display of inconsistent data and provides transaction isolation for each database session. MVCC locks for data reading do not interfere with locks acquired for writing data.

9. What exactly is pgAdmin?

pgAdmin is a Web-based GUI utility for interacting with Postgres database sessions. It is applicable to both local and distant servers. Its most recent release, pgAdmin4, is distributed under the PostgreSQL License. pgAdmin4 creation required a complete rebuild of the original pgAdmin program. This version was made with a mix of Javascript/jQuery and Python. pgAdmin can now be used as a desktop runtime or as a web application server, depending on your needs.

10. How is the database deleted in PostgreSQL?

Using the syntax, databases may be removed in chúng tôi DATABASE [IF EXISTS] ;

Please note that only inactive databases can be discarded.

11. What does a schema consist of?

Schemas are elements of databases, including tables. They include data types, functions, and operators, among other named object types. The object names are compatible across schemas; unlike databases, schemas are divided more freely. This indicates that a user can access objects in any of the schemas in the database they are linked to until they are granted the appropriate permissions. Schemas are incredibly beneficial when several users must access a single database without interfering with one another. It facilitates the organization of database items into logical categories for better management. To prevent name-based conflicts, third-party applications could be placed in distinct schemas.

12. What are the most significant differences between SQL and PostgreSQL?

PostgreSQL is a sophisticated SQL variant. PostgreSQL views cannot be updated.PostgreSQL does not support calculated columns. However, it does provide functional indexes. In PostgreSQL, replication takes the form of reports. PostgreSQL provides actions that are dynamic.

The PostgreSQL server provides several levels of encryption and flexibility to improve data security from disclosure in an insecure network scenario. Meanwhile, the SQL server is designed to provide a safe database platform. To that end, it includes several capabilities that can encrypt data, decrease authorization, and protect data from unethical acts.

13. Explain Write-Ahead Logging in detail.

Write-ahead logging (WAL) is vital to Postgres’ durability and data consistency. All modifications are initially captured to this append-only log, then to the data files on disk. Using write-ahead logging, you can protect your data against corruption. As a result of this method, a complete record of all operations and alterations is maintained. It’s well-known that backing up database changes before implementing or updating them improves the stability of certain databases. A backup log is kept here in case the database fails. When Postgres enforces write operations, it creates WAL (e.g., INSERT, UPDATE, etc.). In situations when WAL creation exceeds the capability of WAL archival off-disk or where a high database load reduces archiver performance, WAL capacity is limited.

14. What is the definition of a non-clustered index?

A non-clustered index in PostgreSQL is a simple index used to quickly retrieve data with no assurance of uniqueness. It includes references to other places where data is kept. This is known as a secondary index too. You can have several indices of this category as you want on a given table. Non-clustered indexes are analogous to a document’s “Table of Contents.” We check the page number first, then the page numbers after that to view the entire content. It keeps a pointer to corresponding heap data to get the whole data based on an index. It’s exactly similar to knowing the page number and then going to that page to retrieve the actual content of the page.

15. How does PostgreSQL provide security?

PostgreSQL employs two tiers of security.

Network-level security- Utilization of Unix Domain sockets, TCP/IP sockets, and firewalls for network-level security.

Transport-level security- Transport-level security employs SSL/TLS to ensure the safe database communication

Database-level security- Roles and permissions, row-level security (RLS), and auditing are all characteristics of database-level security.

Conclusion

In this article, we have seen important PostgreSQL questions. We got a good understanding of different PostgreSQL terminologies. Below are some major takeaways from the above article:

1. We have seen PostgreSQL’s tokens and what are the benefits of using PostgreSQL.

2. We learned about How to set up a PostgreSQL database?

3. We got an understanding of how we can create a table in PostgreSQL?

And much more.

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

6 Of The Most Useful Linux Commands For New Users

Making the switch to Linux can be a little intimidating. A new operating system? Different Desktop? Everything that you have known in computing has changed? Although the switch may seem scary at times, it can be more productive and quite rewarding. Learning new things is your first step.

What is terminal?

Before you can begin learning the ins and outs of Linux, you will need to understand what terminal is. The terminal is Linux’s version of the command prompt or powershell. Linux, which was originally created as a command-line operating system, added a graphical interface for ease of use because of its growing popularity The Linux operating system, in its most productive form, uses terminal.

Terminal commands are unlike most applications you may be familiar with. They are unlikely to spawn a new window, as the results are executed immediately within the terminal application. Terminal commands may also have additional options, which are called “switches.” Switches are used to add functionality to the requested command line operation. Below you will find 5 of the “must know” commands when switching to Linux.

1. “man” command

The man command is going to be the most useful of commands for users new to Linux or terminal. Man is short for “manual,” which is the term for a Linux help document. Every terminal command has a man page, even the “man” command.

If you know the name of the Linux command, function or file you are interested in, simply type man command name and hit Enter. Below represents what running the above command results would be.

2. “ls” command

The ls command is a great tool to see what directories or files reside in your current location or throughout the file system. It is installed in Linux distributions by default, as it is part of the GNU core utilities package. To use this command is as simple as typing ls in your terminal window. This will show you the results of your current working directory.

To show files and directories, size, modified date / time, owner and permissions:

To sort files in current working directory by size:

To list directory information for a directory that is not the current working directory:

3. “cd” command

The cd command is another useful command and is used to change from the current working directory. The below examples will show the functionality of the “cd” command.

To change to a subdirectory:

To change to a subdirectory multiple directories away from the current location:

To change the directory to the home directory of the user:

4. “ifconfig” command

This Linux command may look a little odd if you have worked a long time using Windows operating systems. “ifconfig” is very similar to the Windows version of the command “ipconfig.” This command is not installed by default in all Linux distros. In order to obtain this functionality, the user can install the package by running sudo apt get install net-tools from the terminal (if you are using Debian-based distro). Once installed, using this command will provide the user with the current IP-addressing information for your device.

To view network settings of a specific interface:

To disable a network interface:

To enable a network interface:

5. “find” command

When attempting to find a file by a specific name or extension in Linux, it is best to use the find command. Some of the examples below can be very useful for new user learning to navigate within the Linux terminal.

To search for a file with the filename:

To search for all the “png” files in “/home/Desktop” and its subdirectories:

6. “clear” command

When your terminal is filled up with text, the best way to clear it is with the clear command. This clears the screen in your view and allows you to start again with just the prompt. In some cases you can also use Ctrl + l, which will perform the same task.

clear

In conclusion, we covered the most useful commands that a beginner should know. In addition to these, there are many more commands which you can run in a Linux terminalas well as some commands you should never run. Learning these commands won’t make you want to quit your day job and become a tech guru, but it may make your transition to Linux that much easier.

Josh Grimes

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.

By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time.

Oracle Says Google Knowingly ‘Broke The Rules’ With Java

“This case is about Google’s use, in Google’s business, of somebody else’s property without permission,” said Michael Jacobs, an attorney for Oracle, in his opening remarks to the jury.

Oracle sued Google 18 months ago, arguing that its Android operating system infringes Java patents and copyrights that Oracle acquired when it bought Sun Microsystems. Google denies any wrongdoing and says it doesn’t need a license for the parts of Java it used.

Judge William Alsup, who is hearing the case, warned both sides on Monday that they’ll need to show good cause for any evidence submitted at trial to be kept from the public, and that unflattering details about either side might emerge.

“Unless it’s the recipe for Coca-Cola, it’s going to be public,” Alsup said. “If it reveals something embarrassing about the way one of these companies works, too bad. That’s going to be out there for the public to see.”

Most of the opening day was taken up with jury selection, but Jacobs had time to deliver Oracle’s opening statement before the proceedings wrapped up. Google will give its opening statement Tuesday morning.

Jacobs cited several emails to and from Google executives that he said would show that Google knew it needed a license for Java and that, having failed to negotiate one, it developed Android with Java anyway.

Google’s use of Oracle’s intellectual property wasn’t a mistake or the result of any confusion, Jacobs told the jury.

“The decision to use Oracle’s intellectual property in Android was taken at the highest levels, with a lot of comprehension and awareness about what was going on,” he said.

Google had to develop Android quickly, and it had to attract developers to be successful, he said. “How did they meet those requirements? The answer is with components of Java.”

Other companies such as eBay, Cisco Systems and General Electric bought licenses to use Java, but Google “broke the basic set of rules governing the Java community,” Jacobs said.

Some big Silicon Valley names are expected to be called to testify in the trial. Oracle’s witness list includes its CEO, Larry Ellison, Google CEO Larry Page, Google Executive Chairman Eric Schmidt and former Sun CEOs Scott McNealy and Jonathan Schwartz.

Before jury selection took place Monday, Alsup had to settle some last-minute disputes between the two sides.

Google thought it would be unfair if Oracle were allowed to tell the jury it paid $7.4 billion to buy Sun, because it might inflate the value of Java in the minds of the jurors.

“They’ve been dying to throw that number around,” Robert Van Nest, an attorney for Google, told the judge.

Alsup ruled against him but nevertheless cautioned Oracle to be careful how it used such figures. “The idea that you can throw big numbers around in front of the jury and somehow jack up the damages award if there is one … that’s not going to be allowed,” Alsup said.

The trial will be held in three phases: first to hear the copyright claims, then the patent claims, and then any damages Oracle might be awarded. Oracle is seeking about $1 billion in damages and an injunction to block Google from shipping any infringing code.

The lawsuit is seen by many as a test case for whether software APIs (application programming interfaces) can be subject to copyright.

The trial resumes at 8 a.m. Pacific Time Tuesday morning. It’s being held at the U.S. District Court in San Francisco.

4 Different Methods With Examples

What is CONCATENATE in Excel?

CONCATENATE in Excel is an essential function that allows users to combine data from different cells and display the result in a single cell. For instance, if you have a list of addresses with the name, street name, city, etc., in different columns, you could use the CONCATENATE function to combine each section into a complete address.

You can then copy the combined address or use it elsewhere.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The CONCATENATE in Excel function is helpful for those who work with large volumes of data and want to combine the values of different columns into one. The utility of the function extends from connecting first and last names to linking data sections for generating unique codes.

Key Highlights

The function CONCATENATE in Excel can combine a maximum of 30 values.

This function always gives a text string, even if the source contains all numbers.

To work correctly, we must provide at least one text argument in the formula for the CONCATENATE function.

It gives a #N/A error if we provide an invalid value to an argument.

The function CONCATENATE in Excel combines the source values and displays the result in a new cell. Therefore, it does not alter the source giving users the flexibility to work on new and old values.

Syntax of CONCATENATE in Excel

The syntax for CONCATENATE function is as follows-

Text 1: It’s a required argument and the first item to join. It can be a text value, cell reference, or number.

Text 2: It’s also a required argument. We can join up to 255 items that are up to 8192 characters.

How to Use CONCATENATE Function in Excel?

You can download this CONCATENATE in Excel Template here – CONCATENATE in Excel Template

1. Using the Formula

Suppose we have a list of Names in Column A and a list of Dates in Column B and want to display the Name & Date together in Column C.

Here’s how we can do it:

Explanation of the formula:

A6: This is the first value we want to combine.

“: To insert a space between the combined values, we enclose it in double quotes.

TEXT(B6, “DD-MM-YYYY” ): The TEXT formula converts the number into a readable format. B6 represents the date, and DD-MM-YYYY is the format to display the same.

Note: Excel will display the output in a non-readable format if we concatenate a date with text without converting it.

2. Using the Function “fx.”

Step 1: Create a new column with the heading Product Code.

Step 2: Select the cell where you want to display the combined result. In this case, it is cell E6.

3. Using Ampersand Operator (&)

Suppose we have a list of Employee names in Column A and their Email IDs in column B. We want to concatenate these values to create a list of Employee Emails with their Display name using the Ampersand Operator (&).

Put the “=” sign and select the first cell to combine (A6). Enter the & operator and open double quotes. Put a space and open angle brackets. Enclose the second value (B6) between ampersand operators and double quotes. And lastly, end the formula with double quotes. The double quotes indicate that we want the output in text format.

Step 3: Press Enter key to get the combined result

To concatenate the Email ID with the Names of the remaining cells, drag the formula into the other cells.

4. Combine Text String and Cell Value Using CONCATENATE Function

Explanation of the formula

A6: The cell contains the first value we want to combine.

“: A space enclosed with double quotes to separate the combined values with space.

B6: We want to combine the cell containing the second value.

“MICROSOFT EXCEL”: It is the third value to combine. It is enclosed in double quotes to indicate that we want to display a text value.

Things to Remember

Same Order: The CONCATENATE function combines the values in the same order as in the data set. For example, if a column contains the alphabets in random order, i.e., A, C, D, F, and H, then the formula =CONCATENATE(A, C, D, F, H) will display it as

Manual Cell Reference: If you want to combine the values of a cell range, you cannot use an array (such as B1:B10). You must refer to each cell manually, e.g., =CONCATENATE(B1, B2, B3,..).

CONCAT function: In EXCEL 2024 and later versions of Excel, the CONCAT function in Excel has replaced the CONCATENATE function. However, it is still available to use for compatibility.

Frequently Asked Questions (FAQs)

Answer: The shortcut for CONCATENATE uses the ampersand operator (&). To combine values using the (&) operator, follow the steps:

Step 2: Type the formula,

=A6&” “&B6

Note: To separate the values with space, we enclose space with double quotes in the formula.

Answer: Working with numerical data in Excel is relatively easy to manipulate, but combining or manipulating text can be challenging. This is where the CONCATENATE function comes in handy. This function allows users to merge text strings without changing the original values, making it an ideal option for financial reporting or presentations.

Unlike merging cells, the CONCATENATE function does not alter the original values. It also allows for combining different data types, such as text strings, numbers, and dates, which is beneficial for data analysis and presentation.

The CONCATENATE formula is =CONCATENATE(text1, text2, text3,…), where,

text1, text2, text3,… are the values we want to combine. We can combine up to 255 strings and 8192 characters in one formula.

Recommended Articles

The above article is a guide to CONCATENATE in Excel using different methods and downloadable templates. To learn more about such useful functions of Excel, you can read the following articles.

Expressions In Sql With Examples

Introduction to SQL Expressions

SQL expressions are the collective formula consisting of values, column names, operators, and functions present in SQL. The expressions in SQL are further classified as Boolean expressions, numeric expressions, and date-time expressions. The expressions are formulas that evaluate a particular value that can be further used according to our necessity. Boolean expressions are used to evaluate a condition that results in either true or false and can be used anywhere but mostly used in the where clause to perform restrictions on column values.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The numeric expressions are used to evaluate the summarized and cumulative numeric values that are mostly used for reporting purposes. The numeric expressions can be formed by using aggregate functions of SQL such as SUM, COUNT, AVG, etc, and operators like addition, subtraction, multiplication, etc. Date and time-related expressions are used to evaluate and retrieve the current date and time or some manipulated dates and times.

Expressions in SQL with Examples 1. Boolean Expressions

The use of Boolean expressions is mainly done in the where clause of the SQL queries which can be either SELECT, INSERT, UPDATE, and DELETE. Note that the conditions and expressions used in the WHERE clause can make the use of AND, OR, NOT, etc operators so that the final value retrieved from the condition will be a Boolean value. This is called a Boolean expression. Let us see the syntax of the usage of Boolean expressions in SELECT query inside the WHERE clause

SELECT column_name1, column_name2, column_nameN FROM name of the table WHERE boolean expression;

Let us consider a simple example where we have an existing table named educba_writers_demo that has the contents as retrieved from the following query statement –

SELECT * FROM educba_writers_demo ;

The execution of the above query statement will give the following output:

Now, we will apply a boolean condition in where a clause specifies that the value of the rate column should be greater than 700. The condition will return either true or false for each of the records in the table. If it’s true then the record will be added in the final resultset or not. Let us execute the following query and observe the output –

SELECT * FROM educba_writers_demo

The execution of the above query statement will give the following output:

2. Numeric Expressions

Numeric expressions involve usage of literal values and column values and that are manipulated with the usage of operators like add, subtract, divide and multiply and different functions that are available in SAQL such as aggregate functions containing SUM(), COUNT(), AVG(), MAX(), MIN(), etc to retrieve a value that can be used further. Most of the time, numerical expressions are used in the retrieval list of the SELECT query statement to retrieve the summarized data that is sent to the client mostly for analysis purposes such as reporting and dashboarding. Let us consider simple examples that use numerical expressions. Syntax of using numerical expressions is as shown below:

SELECT Numerical expression FROM name of the table [WHERE restriction];

Let us firstly consider a simple example that we used above to explain the working of the AVG() function. We will calculate the average value of SQL numbers using the AVG() function. Let us create one simple table named numbers and store the num column value in it. We will use the following query statement to create our table –

CREATE TABLE numbers (num INT) ;

The execution of the above query statement will give the following output:

Now, we will insert the above records in the table. Our query statement will be as follows –

INSERT INTO numbers(num) VALUES (50), (100), (150), (200);

Let us now retrieve the records once:

SELECT * FROM numbers ;

The execution of the above query statement will give the following output:

Now, we will calculate the average of num column of numbers table using AVG() function using the following query statement –

SELECT AVG(num) FROM numbers ;

The execution of the above query statement will give the following output:

Similarly, we can use COUNT(), MAX(), and various other functions and also formulas involving operators. Let us consider the usage of operators along with avg() function in the following example –

SELECT AVG((num * 10) + 1) FROM numbers ;

Now, we will calculate the total of num column of numbers table using SUM() function using the following query statement –

SELECT SUM(num) FROM numbers ;

The execution of the above query statement will give the following output:

3. Date Time Expressions

The expressions that are related to date and time help in fetching the information such as current date, time, and timestamp. This is used for informative purposes and to store temporal values in the table to record the time and date during which the data was being inserted and also for assigning a default value to the fields with datatype like date, time, DateTime, and timestamp. Let us firstly use a simple query statement that retrieves the current timestamp –

SELECT CURRENT_TIMESTAMP;

The execution of the above query statement will give the following output:

Further, we can use the different functions that are available in SQL to handle temporal values such as GETDATE() to retrieve the current date and so on.

Conclusion

SQL expressions can be used to evaluate a value that results from the combination of functions, operators, literal values, and column values. The returned value of the expression is further used either for decision making as summarized data or informational purposes. The expressions are classified into three types in SQL that are boolean expressions, numerical expressions, and date time-related expressions respectively.

Recommended Articles

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

Update the detailed information about Most Frequently Used Linux Iptables Rules With Examples on the Tai-facebook.edu.vn 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!