SQL Server Basics

Introduction

We will discuss SQL Server Basic commands and functions on one page to make it easier for beginners to navigate through all the necessary controls in one spot.

SQL

Structured Query Language, or SQL (pronounced "ess-que-el"), is a query language. A database may be communicated using SQL. It is the accepted language for relational database management systems, claims ANSI (American National Standards Institute).

The following commands are broken up into sections:

Section 1. Querying data

In this section, you may learn how to query data from the SQL Server database. We'll start with a straightforward query that lets you get information from a single table.

• SELECT will demonstrate data querying against a single table.

Section 2. Sorting data

• Sort the result set using ORDER BY to take into account values from a predefined set of columns.

Section 3. Limiting ows

• OFFSET FETCH can be used to restrict the amount of rows a query returns.
• Limit the number or percentage of rows returned as a result of a query using the SELECT TOP command.

Section 4. Filtering data

• DISTINCT - Choose distinct values from one or more table columns.
• WHERE allows you to select rows from a query's output depending on one or more criteria.
• If both Boolean statements are factual, the AND operator will combine them and return true.
• If any of the two criteria is true OR will combine two Boolean expressions and return true.
• IN – determine whether a value corresponds to any other matter in a list or subquery.
• BETWEEN - Determine if a value falls inside a specific range of values.
• LIKE - Determine whether a character string conforms to a given pattern.
• Show you how to utilise table aliases to make searches simpler to interpret and column aliases to change the heading of the query result.

Section 5. Joining tables

• Give a quick rundown of the many join types available in SQL Server, including complete outer join, left join, right join, and inner join.
• Using an INNER JOIN, you may choose rows from one table that correspond to rows in another table.
• Using the LEFT JOIN method, return all rows from the left table and matching rows from the right table. Use null values for the right table's column values if there are no matching rows in the right table.
• If there are no matching rows, FULL OUTER JOIN will return rows from both the left and suitable tables and each side.
• CROSS JOIN: joins many unconnected tables, producing Cartesian products of the linked tables' rows.
• Show you how to utilize the self-join to compare rows in the same database and query hierarchical data.

Section 6. Grouping data

The query result can be grouped using the GROUP BY option depending on the values in a given set of column expressions.

• HAVING - Define a search requirement for a set of individuals or an aggregate.
• Multiple grouping sets are produced via GROUPING SETS.
• Create grouping sets using all possible combinations of the dimension columns using CUBE.
• ROLLUP generates grouping sets with the premise that the input columns are arranged in a hierarchy.

Section 7. Subquery

This section covers subqueries, which are queries nested inside other statements like SELECT, INSERT, UPDATE, or DELETE notices.

• Subquery: This section will describe the idea and demonstrate how to choose data using different subquery types.
• Let me explain the concept of a linked subquery to you.
• EXISTS is a test for a subquery's returned rows' existence.
• ANY - returns TRUE if a value matches any values in a single-column collection of values returned by a subquery.
• ALL returns TRUE if a value matches all values in a single-column collection of values returned by a subquery.

Section 8. Set Operators

This section demonstrates how to merge several result sets from the input queries using the set operators union, intersect, and except.

• Use the UNION operator to combine the outcomes of two or more searches into a single result set.
• Using the INTERSECT command, return the intersection of the result sets of two or more searches.
• Find the distinction between the two result sets of the two input queries, EXCEPT.

Section 9. Common Table Expression (CTE)

• Use CTE (standard table expressions) to simplify complicated queries.
• Utilize recursive CTE to query hierarchical data.

Section 10. Pivot

Convert rows to columns using PIVOT

Section 11. Modifying data

In this part, you will discover how to modify the information in tables in the SQL Server database. Data manipulation language  refers to the SQL commands for changing data, such as insert, remove, and update (DML).

• The INSERT command adds a row to a table.
• A single INSERT command can insert numerous rows into a table.
• INSERT INTO SELECT is adding data from a query's output to a table.
• UPDATE: To modify a table's current values.
• UPDATE Using JOIN clauses, you may alter a table's values depending on data from another table.
• DELETE: Delete one or more table rows.
• MERGE will guide you through the process of carrying out a single statement that combines insertion, update, and delete operations.
• Show you how to begin a transaction using the words BEGIN TRANSACTION, COMMIT, and ROLLBACK.

Section 12. Data definition

The most important database objects, such as databases and tables, are managed in this part.

• The CREATE DATABASE command and SQL Server Management Studio are used to create a new database in a SQL Server instance.
• DROP DATABASE teaches you how to eliminate current databases.
• Describe how to add a new schema to a database using the CREATE SCHEMA command.
• Show how to move a specific item from one schema to another inside the same database using the ALTER SCHEMA command.
• A schema can be removed from a database by using the DROP SCHEMA command.
• CREATE TABLE will guide you through the process of creating a new table in a particular database schema.
• Discover how to build a table's identity column using the IDENTITY attribute.
• Sequence: Describe the process for producing a series of numerical numbers by a specification.
• It shows you how to add one or more columns to an existing table using the ALTER TABLE ADD column command.
• CHANGE TABLE You can update the definition of existing columns in a table using the ALTER COLUMN command.
• CHANGE TABLE DROP COLUMN teaches you how to remove a column (or several columns) from a table.
• How to utilize calculated columns to resolve the calculation logic across many queries.
• You may learn to remove tables from the database by typing "DROP TABLE."
• TRUNCATE TABLE - more quickly and effectively remove all data from a table.
• Learn how to construct a table and put data from a query into it with SELECT INTO.
• Walk you through the steps in renaming a table to a new name.
• Introduce yourself to temporary tables, which temporarily store current data in stored procedures or database sessions.
• Synonym: This page will explain synonyms and demonstrate how to create them for database items.

Section 13. SQL Server Data Types

• SQL Server data types provide an overview of the default SQL Server data types.
• Bit data, such as 0, 1, or NULL, may be stored in databases using the BIT data type.
• Discover the many integer types in SQL Server, including BIGINT, INT, SMALLINT, and TINYINT.
• Show you how to use the DECIMAL or NUMERIC data types to store precise numerical values in a database.
• Learn how to store non-Unicode, fixed-length character strings in the database using the CHAR data type.
• NCHAR - describe the distinctions between the CHAR and NCHAR data types and demonstrate how to store fixed-length Unicode character strings.
• Store variable-length, non-Unicode string data in the database using the VARCHAR data type.
• NVARCHAR - Understand the critical distinctions between VARCHAR and NVARCHAR while learning to store variable-length, Unicode text data in a table.
• DATETIME2 is an example of storing time and date information in a database.
• DATE: Examine the date data type and the methods for storing dates in a table.
• TIME - This lesson will demonstrate how to store time data in a database using the TIME data type.
• datetimeoffset will demonstrate date-time manipulation using the time zone.
• Learn about the GUID and how to create GUID values using the NEWID() method.

Section 14. Constraints

• Explain the primary essential notion and demonstrate how to use the primary key constraint to control a table's main key.
• Introduce you to the idea of a foreign key and demonstrate how to utilize the FOREIGN KEY constraint to enforce the connection between the data in two tables.
• How to assure a column won't allow NULL using a NOT NULL constraint.
• UNIQUE constraint: Assure that each row in a table has only one instance of the data in a column or collection of columns.
• When implementing logic to ensure that data is accurate before being stored in tables, the CHECK constraint will guide you through the process.

Section 15. Expressions

• CASE - You can add if-else logic to SQL queries by using short, searchable CASE expressions.
• Use the COALESCE expression to handle NULL values efficiently.
• If the two arguments are equal, return NULL; otherwise, return the first argument.

Section 16. Useful Tips

• Show you how to locate duplicate values in one or more table columns using the "find duplicates" function.
• Define the process for deleting redundant rows from a table.
   

Must Read SQL Clauses