トピック

The DATE_ADD command adds or subtracts a specified number of days, months, or years to/from a date. The REPLACE command replaces occurrences of a substring within a string. The TRIM command removes specified prefixes or suffixes (or whitespace by default) from a string. The REVOKE command is used to take away privileges previously granted to users or roles.

SELECT INTO copies the specified data from one table into another. Below is a comprehensive list of SQL commands, organized by the top-level of each (e.g. SELECT TOP is within the SELECT category). Now, let’s move on to the last section of this article on SQL Commands i.e. the Transaction Control Language Commands. This field generates a unique number automatically when a new record is inserted into a table. The MS SQL Server uses the IDENTITY keyword for this feature. Let’s consider the below table apart from the Employee_Info table, to understand the syntax of joins.

Learn Latest Tutorials

Aggregate functions commands are SQL statements used to perform calculations on a set of values and return a single value as a result. This will retrieve all employee records and their corresponding department information. The query above selects the category column and the count of each distinct category from a table named products. The results will provide the count of items in each category. The query above selects all columns from a table named customers where the age is greater than 30. The set of DDL commands, such as CREATE, ALTER, DROP, and TRUNCATE is used to create, manage, and modify the structure of database objects.

The query above uses the UNION operator to combine the first_name and last_name columns from both the customers and employees tables. The result will include unique combinations of first_name and last_name from both tables. The result is returned as a new column named replaced_description. Replace old_string and new_string with the actual strings you want to replace and replace them with. The query above uses the CONCAT function to concatenate the first_name and last_name columns from the employees table, separated by a space.

DCL (Data Control Language)

SELECT DISTINCT only returns data that is distinct — in other words, if there are duplicate records, it will return only one copy of each. Now, use the below SQL queries to understand the transactions in the database. The AVG function returns the average value of a numeric column that you choose.

Each transaction begins with a specific task and ends when all the tasks in the group are successfully completed. DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system. It is the component of the SQL statement that controls access to data and to the database. Basically, DCL statements are grouped with DML statements.

What is SQL?

This operator groups data from one or more columns and is often used with the following functions. XOR selects values if only one of the conditions listed with XOR is met. DISTINCT and ALL filter the output records when executing the SELECT command. The first allows you to retrieve unique strings without repetition. The second outputs all rows without exceptions and is applied by default.

The GROUP BY statement groups rows with the same values into summary rows. For example, the code below will display the average age for each name that appears in our customers table. An outer join will combine rows from different tables even if the join condition is not met.

SQL Views

This operator is similar to WHERE and is related to the previous operator. CASE statements are used to create different outputs (usually in the SELECT statement). To update a record in a table you use the UPDATE statement. As an example, first we will show you a SELECT statement and results without a WHERE statement. Then we will add a WHERE statement that uses all five qualifiers above.

• SELECT DISTINCT only returns data that is distinct — in other words, if there are duplicate records, it will return only one copy of each.
• It is comparing the employee_id from one instance of the table (t1) to the employee_id from another instance of the table (t2).
• The examples provide better understanding of the SQL commands and teaches correct way to use them.
• Practice these commands to effectively use them on any dataset and produce quality results.

In this section of this article, I will explain to you how to use the Date functions and also the Auto-Increment fields. Next, in this article, let us look into the date functions and auto-increment basic database queries fields. Aliases are used to give a column/table a temporary name and only exists for a duration of the query. The BETWEEN operator is used, when you want to select values within a given range.

The ROLLBACK TO SAVEPOINT command is used to roll back to a specific savepoint within a transaction. The SAVEPOINT command is used to set a point within a transaction to which you can later roll back. The NULLIF() function returns null if two specified expressions are equal.

This operator sorts data in ascending (ASC) or descending (DESC) order. The four basic SQL commands are SELECT, INSERT, UPDATE, and DELETE. Below, we'll list the main integrity constraints with brief descriptions and examples. TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

SQL Union – A Comprehensive Guide on the UNION Operator

Now, if you rollback to S2 using the below queries, the output is mentioned in the below table. So if you wish to rollback to any point, then you can save that point as a ‘SAVEPOINT’. This command is used to restore the database to the last committed state. This command is used to withdraw the user’s access privileges given by using the GRANT command.