SQL Cheat Sheet: What is SQL, SQL Commands, and SQL Injection

Using Structured Query Language (SQL) can seem complicated at first, but further, it will become easier and much more convenient. You only need practice.

If you are looking for better ways to manage data in your database or if you are simply curious about the possibilities that SQL opens up to you, then you have come to the right place!

Why SQL?

Most people have heard of SQL but may not be completely familiar with its work. You can think of SQL as a special language for talking to databases. Since computers don’t understand natural languages like English, you need a way to translate. And here we have SQ to command the databases to present some data or to store new data. Various databases, such as Oracle, Microsoft SQL Server, IBM DB2, MySQL, PostgreSQL, etc. use it.

SQL allows us to retrieve, insert, update, and delete data from databases with the help of SQL queries. An SQL query uses words called Clauses. Clauses consist of several elements that work together to carry out some action on the database. The three most common clauses are SELECT, UPDATE, and DELETE. You will find them in almost every SQL statement.

Let’s take the most basic query syntax:

SELECT * FROM table_name;

This query will select all data stored in the particular table. The asterisk (*) indicates that all columns in that table should be returned, no matter how many of them the table has.

SQL Query Cheat Sheet: What Commands are There?

Here we present you a selection of the most commonly used SQL commands and clauses that you will need while working with databases. Let’s start.

SQL CREATE DATABASE

You can’t do much in SQL without a database, so it’s best to get that out of the way first.

CREATE DATABASE is a command for creating new databases on your server. For example, you want a fresh start with no existing tables or data. The syntax is below:

CREATE DATABASE testDB;

SQL CREATE TABLE

Relational databases store the data in tables. Thus, to make use of our databases, we need to create tables in them. This operation is closely related to changing table structures, e.g., renaming columns, adding new columns, dropping columns, etc.

But altering the table is possible for an existing one only. Thus, you will have to CREATE TABLE first. Another related command is ADD COLUMN which will allow you to add additional columns to an existing table.

CREATE TABLE [dbo].[Students](
	[Name] [nvarchar](100) NOT NULL,
	[Bio] [ntext] NULL,
	[DateOfBirth] [datetime] NOT NULL,
);

SQL DROP TABLE

There’s a time and place for deleting tables, and it’s never a good idea to do it on a whim. DROP TABLE removes an entire table with all its records, indexes, and constraints from the database.

There is no way to recover a table once you’ve dropped it. Be sure that you need and want to remove this particular table. Otherwise, the only option to get the data back is to restore it from backup.

DROP TABLE [dbo].[Students];

SQL SELECT

Perhaps the most commonly used function in SQL is Select. Its purpose is to retrieve data from one or more tables for further manipulation and analysis. This functionality allows users to isolate and analyze subsets of data (tables) by specifying various attributes (filters).

To select a group of rows, you write a query that identifies which rows should be returned, based on one or more filters. Filters are specified by any number of different functions or expressions combined with other operations according to your goals.

SELECT TOP (1000) [Name]
      ,[Phone]
      ,[Website]
      ,[Address]
      ,[City]
      ,[State]
      ,[Country]
      ,[Description]
      ,[Employees]
FROM [test].[dbo].[company]

SQL INSERT

The INSERT function inserts rows of data into a table in SQL. The basic structure of this statement specifies the table to add data to and the columns to include that data. Next, it defines each value that should be placed into each column within that row. If you are just learning how to use SQL it can seem confusing, but with some practice, it becomes much easier.

INSERT INTO [dbo].[company]
           ([Name]
           ,[Phone]
           ,[Website]
           ,[Address]
           ,[City]
           ,[State]
           ,[Country]
           ,[Description]
           ,[Employees])
     VALUES(
           'NameValue'
           ,'PhoneValue'
           ,'WebsiteValue'
           , 'AddressValue'
           , 'CityValue'
           , 'StateValue'
           , 'CountryValue'
           , 'DescriptionValue'
           , 2 ) 

SQL UPDATE

The UPDATE statement is necessary when we want to change the data stored in tables. It is also one of the most commonly used SQL statements applicable to both a simple row and multiple records.

The default syntax includes the SET and WHERE components. SET defines how the records should be updated, and WHERE determines which record(s) must be updated. Without the WHERE clause, the command will update all records in the table.

UPDATE [dbo].[company]
   SET [Name] = <Name, nvarchar(max),>
      ,[Phone] = <Phone, nvarchar(max),>
      ,[Website] = <Website, nvarchar(max),>
      ,[Address] = <Address, nvarchar(max),>
      ,[City] = <City, nvarchar(max),>
      ,[State] = <State, nvarchar(max),>
      ,[Country] = <Country, nvarchar(max),>
      ,[Description] = <Description, nvarchar(max),>
      ,[Employees] = <Employees, int,>
 WHERE <Search Conditions,,>

SQL AVG

The AVG function returns the average of numeric values in a column as an integer or a floating-point number. As with most SQL functions, it can be a part of a SELECT statement or an INSERT statement. When used within the SELECT statement, it has to be within parenthesis. Below you can see the query example with AVG() that must return the average age across all employees:

SELECT avg(age) FROM employee;

SQL SUM

The SUM function returns the sum of all values in a column. This is very helpful when you are dealing with multiple columns. The result would be an overview of that table that adds up all of the data in it.

SELECT  Sum(Employees) as Sum, 
		AVG(Employees) as AVG,
		MAX(Employees) as Max, 
		MIN(Employees) as Min
FROM [test].[dbo].[company]

SQL ORDER BY

SQL has many operators, but one of the most commonly used ones is ORDER BY. It sorts data according to the specified expression. Therefore, if your data set contains multiple records with similar values, they will be sorted according to your preferences.

In other words, the ORDER BY operator in SQL is a comparison operator. It allows you to compare values from one column with values from another column and return the results as they are ordered by this comparison.

The syntax is ORDER BY column-name. It can be helpful if you need to find information in a sorted manner. For instance, when you are searching for rows that have the same value or when determining which rows are the most popular.

SELECT * FROM Readers
ORDER BY City;

SQL GROUP BY

The GROUP BY operator is a very important and useful tool in SQL. It can be used to group the rows of data that share some kind of common characteristic or property. Therefore, it sorts the rows by one or more columns specified in the query. This clause is typically placed at the end of the SQL statement after all other clauses are executed.

A typical case is using GROUP BY to summarize data in your database. The SELECT statement uses the WHERE clause to filter out unwanted records from the result set, and then it uses the GROUP BY clause to group related records together based on some characteristic that you specify.

SELECT * FROM Readers
GROUP BY Country;

SQL Injection

Any web programmer has heard of SQL injection attacks. They are the scourge of the Internet, leaving deep wounds in many popular websites and applications that can take years to heal. If you want to avoid falling victim to these attacks, it’s important to understand what they are, how they work, and what you can do to protect yourself against them in the future.

If you’re not familiar with SQL injection attacks, your web application may be vulnerable to them. The threat is serious enough to spark a new technology to detect such attacks. However, relying on technology isn’t enough. To help security professionals, we’ve put together a handy cheat sheet that explains how an SQL injection works in plain English.

Attackers execute unauthorized queries to gain access to sensitive data, modify an existing database, or delete data. This way, SQL injection is a code injection technique that attacks data-driven applications where malicious SQL statements are inserted into an entry field for execution (usually via an HTTP request).

A successful attack gives the attacker access to all database servers of that website. Once inside, an attacker can retrieve databases contents, change them or even destroy them. This is why it’s so critical for web developers to understand how their programming language makes SQL injections easier or harder for potential attackers.

There are two main types of SQL injection attacks: error-based and blind.

An error-based attack occurs when a developer doesn’t sanitize user input. This results in passing bad data to an interpreter and causing errors (or unexpected behavior) for legitimate users. The error-based attack cause is often easy to see. It will be registered in error logs or will show up as a bug during testing.

These kinds of attacks also happen due to poor code review process — if one coder leaves debugging code in the script, others would run it without knowing about possible problems.

If you notice database errors or other strange behavior on your website, you might have an SQL injection problem!

Conclusion

Of course, any database specialist who uses SQL in work should know all these commands by heart. Still, having them as a simple cheat sheet at hand is also helpful. Feel free to share your considerations and professional tricks in the Comments section below!

Tags: sql commands, sql functions Last modified: October 26, 2022