Many versions of sql use FROM in their queries, but in all versions since SQL server 2005, including SQL Server 2019, one can use SELECT statements without using the FROM clause.

SELECT statement

A table with its data can be viewed using the SELECT statement. The SELECT statement in a query displays the required information in a table. The SELECT statement retrieves the rows and columns for one or more tables. The output of the SELECT statement is another table called the result set. The SELECT statement also joins two tables or retrieves a subset of columns from one or more tables. The SELECT statement defines the columns used for a query. The syntax of the SELECT statement can include a series of comma-separated expressions. Each expression in the statement is a column in the result set. The columns appear in the same order as the expression in the SELECt statement. The SELECT statement retrieves rows from the database and allows to select one or more rows.

Regarding execution sequence, the SELECT clause will be executed last, even though it is written at the beginning of the sentence

Syntax:

 SELECT <column_name1> [,column_name2] FROM <table_name>

For example:

 SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2019].[Person].[Person]

Select without FROM

Many versions of SQL use FROM in their queries, but in all versions since SQL Server 2005, including SQL Server2019, the SELECT statement can be used without using the FROM clause.

For example:

 SELECT LEFT ('Hello World',5)

Show all columns

The asterisk (*) keyword is used for the SELECT statement to retrieve the data of the entire column in the table. It is used as a shorthand instead of writing out a list of all the columns in the table.

Syntax:

 SELECT * FROM <table_name>

For example:

 SELECT * FROM [AdventureWorks2019].[Person].[Person]

The * sign is very convenient in case there are too many columns and you can't remember all the column names, or it's too long to write down. However, in practice, you should not abuse the * sign too much because that way the system will run very hard because the data will be redundant. For example, if you build a function that displays usernames, just write select username is ok, not necessarily SELECT * because taking out more columns will reduce performance and consume more ram.

Expressions other than SELECT

The SELECT statement allows the user to specify different expressions to view the result set in an ordered manner. These expressions assign different names to columns in the result set, calculate values, and remove duplicate values.

Using constants in result sets

String constants are used when character columns are concatenated together. They help with proper formatting or readability. These constants are not specified as a separate column in the result set. It is usually more efficient for the application to build constant values into the results as they are displayed rather than using the server to combine the constant values. For example, to include ':' and '->' in the result set to display the country name, country code and its corresponding group, the SELECT statement is shown in the following example:

 USE AdventureWorks2019 SELECT Name+':'+CountryRegionCode+'->'+[Group] FROM Sales.SalesTerritory

Rename column in result set

The columns displayed in the resultset of queries with the corresponding title can be changed, renamed, or can be assigned a new name using the AS clause. By customizing headers, they become more understandable and semantic.

For example:

 USE AdventureWorks2019 SELECT Name+':'+CountryRegionCode+'->'+[Group] AS NameRegion FROM Sales.SalesTerritory

Calculate the value in the result set

The SELECT statement can contain mathematical expressions by applying operators to one or more columns. It allows a result set containing values that do not exist in the base table, but are calculated from the values stored in the base table.

Example 15% off result set to use for promotional display:

 USE AdventureWorks2019 SELECT ProductID,StandardCost,StandardCost*0.16 AS Discount FROM Production.ProductCostHistory GO

Using DISTINCT

The DISTINCT keyword prevents retrieval of duplicate records. It removes repeating rows from the result set of a SELECT statement. For example, if the StandardCost column is selected without using the DISTINCT keyword, it will display every StandardCost record just once like for example:

 USE AdventureWorks2019 SELECT DISTINCT ProductID,StandardCost,StandardCost*0.16 AS Discount FROM Production.ProductCostHistory GO

Using TOP and in SELECT

The TOP keyword will show only part of the result set. The set of records will be limited by number or percentage (%), TOP expression can also be used with statements like INSERT, UPDATE, DELETE.

Syntax:

 SELECT [ALL|DISTINCT] [TOP expression [PERCENT] [NUMER]] FROM <table_name>

For example:

 USE AdventureWorks2019 SELECT TOP 100 ProductID,StandardCost,StandardCost*0.16 AS Discount FROM Production.ProductCostHistory GO

SELECT combined with INTO

The INTO clause creates a new table and inserts the rows and columns listed in the SELECT statement into it.
The INTO clause also inserts existing rows into the new table. To execute this clause with a SELECT statement, the user must have CREATE TABLE permission in the target database.

Syntax:

 SELECT <column_name1>,[, <column_name2> ...] INTO <new_table> FROM table_list

For example:

 USE AdventureWorks2019 SELECT ProductModelID, Name INTO Production.ProductName FROM production.ProductModel GO

SELECT combined with WHERE

The WHERE clause with the SELECT statement is used to conditionally select or limit (more understood, filter records) the records retrieved by the query. The WHERE clause specifies a Boolean Expression to examine the rows returned by the query. Rows are returned if the expression is true and discarded if false.

Syntax:

 SELECT <column_name1> [, <column_name2> ...] FROM <table_name> WHERE <search_condition>

Relational operators in SELECT:

Example using WHERE clause to display data voiws Endate as specified date

 USE AdventureWorks2019 SELECT * FROM Production.ProductCostHistory WHERE EndDate ='2013-05-29' GO

All SQL queries use single quotes to enclose text values .

For example:

 USE AdventureWorks2019 SELECT * FROM Person.Address WHERE City='Bothell'

Numeric values do not need to use single quotes to enclose

For example:

 USE AdventureWorks2019 SELECT * FROM HumanResources.Department WHERE DepartmentID < 10 GO

The WHERE clause can be used with wildcard characters , which is the character concept used for the LIKE keyword to create an exact and specific query statement.

The WHERE clause can also be used with logical operators such as AND, OR, NOT . Operators used in conjunction with search conditions in the WHERE . clause

The AND operator combines 2 or more conditions and returns TRUE only if both conditions are met, the result will return all records if the condition is satisfied.

For example:

 USE AdventureWorks2019 SELECT * FROM Person.Address WHERE AddressID > 900 AND City='Seattle' GO

The OR operator returns TRUE and displays records if it satisfies one of the conditions in the WHERE clause.

For example:

 USE AdventureWorks2019 SELECT * FROM Person.Address WHERE AddressID > 900 OR City='Seattle' GO

The NOT operator is the negation of the search condition

For example:

 USE AdventureWorks2019 SELECT * FROM Person.Address WHERE NOT AddressID = 5

GROUP BY . clause

The GROUP BY clause partitions the results into one or more subsets. Each subset has common values and expressions. If an aggregate function is used in the GROUP BY clause, the result set will produce single values for each aggregate

The GROUP BY keyword is followed by a list of columns, called grouped column. Each grouped column restricts the number of rows of the result set. For each grouped column, there is only one row.

The GROUP BY clause can have more than one grouped column.

 SELECT <column_name1>, [, column_name2 , ...] FROM <table_name> GROUP BY <column_name>

For example:

 USE AdventureWorks2019 SELECT WorkOrderID,SUM(ActualResourceHrs) FROM Production.WorkOrderRouting GROUP BY WorkOrderID HAVING WorkOrderID < 50 GO

The result will return a WorkOrderID group table, and sum the ActualResourceHrs column based on WorkOrderID with WorkOrderID < 50

ORDER BY . clause

It specifies the sort order of columns in a result set. It sorts the query by one or more columns. Sorting can be in ascending (ASC) or descending (DESC) order. By default, records are sorted in ASC order. To switch to descending mode use the optional keyword DESC, When using multiple fields, SQL Server treats the leftmost field as the primary sort level and the others as the lower sort level.

Syntax:

 SELECT <column_name> FROM <table_name> ORDER BY <column_name> (ASC|DESC)

For example:

 SELECT * FROM Sales.SalesTerritory ORDER BY SalesLastYear GO

Working with XML

Extensible Markup Language (XML) allows developers of tags to have their own meanings and that other programs can understand the meanings of these tags (similar to HTML tags but for data storage, not for browsers). read). XML is the preferred medium for developers to store, format, and manage data on the web. Applications today have a combination of technologies such as ASP, .NET technology, XML, SQL server working in tandem. In some situations, storing XML data in SQL Server is a reasonable solution.

The native XML database in SQL Server has several advantages as follows:

• Better performance: Queries from well-implemented XML databases are faster than queries on documents stored in the file system. In addition, the database typically parses each document when storing it.
• Easy data handling: Large documents can be handled easily (because XML is structured)

SQL Server supports storing XML data natively using the xml data type. The native XML database defines a logical model for an XML document – as a description for the data in that document – and stores and retrieves the document according to that model. At a minimum, the model must include elements, attributes, PCDATA, and document ordering.

XML data type

In addition to commonly used data types, SQL Server 2019 supports XML data types. The XML data type is used to store XML documents and segments in SQL Server databases. An XML segment is an XML instance with a top-level element missing from its structure.

Syntax:

 CREATE TABLE <table_name> ([column_list,] <column_name> xml [, column_list])

For example:

 USE AdventureWorks2019 CREATE TABLE Person.PhoneBilling (Bill_ID int PRIMARY KEY, MobileNumber bigint UNIQUE, CallDetails xml) GO

XML-style columns can also be added from a table to a table at creation time. XML data type columns support DEFAULT terms as well as NOT NULL constraints.

For example:

 AdventureWorks2019 INSERT INTO Person.PhoneBilling VALUES (100,98326505,'<Info><Call>Local</Call><Times>45 minuetes</Times><Charges>200</Charges></Info>') SELECT CallDetails FROM Person.PhoneBilling GO

The DECLARE statement is used to create variables of type XML. The purpose of the DECLARE statement is used to declare a variable in SQL Server.

Syntax:

 DECLARE @LOCAL_VẢIABLE datatype [= value]

Local variable names must start with an @ sign. The value parameter specified in the syntax is an optional parameter that assigns an initial value to the variable during declaration. If you do not specify any initial value to be assigned to a variable, it will be initialized as NULL

For example:

 DECLARE @xmlvar xml SELECT @xmlvar='<Employee name="Toan"/>'

Note: The xml data type cannot be used as primary key, foreign key, or use UNIQUE constraint.

There are 2 ways to store XML documents in columns with xml data type named typed (styled) and untyped (unstyled) XML. An XML instance that has a schema associated with it is called a styled XML instance. A schema is a header for an XML document or version. It describes the structure and content limitations of XML documents by associating XML schemas with versions or recommended XML documents because data can be validated as it is being stored in the type column. xml data.

SQL Server does not do any validation for the data entered in the xml column. However, it ensures that the data is stored with a good standard. Unstyled XML data can be created and stored in table columns or variables depending on the needs and scope of the data.

Docs: https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-schema-collections-sql-server?view=sql-server-ver16

The first step when using typed XML is to register the schema. Syntax:

 CREATE XML SCHEMA COLLECTION <Schema_Collection_name> AS '[xmldefine]'

For example:

 CREATE XML SCHEMA COLLECTION OrderSchemaCollection1 AS N'<?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="Customer" /> </xsd:schema>' GO

Create a table of data type Order as the example:

 CREATE TABLE myOrder (orderID int identity not null, orderInfo xml (OrderSchemaCollection))

Input the newly created XML type:

 insert into myOrder values ('<Customer></Customer>')

In addition, we can completely create an XML variable using the Schema Collaction. For example:

 use myDB DECLARE @order xml (OrderSchemaCollection1) SET @order = '<Customer></Customer>' select @order GO