SQL Tutorial

↑

Comments

Single line comments start with --

Multi-line comments start with /* and end with */.

Q: What are the differences between T-SQL, SQL Server and SQL?

A: SQL is the basic ANSI standard programming language for accessing data in a relational database. SQL Server is a database management system, a software that performs SQL queries.

Companies have their own SQL Server products. MSSQL refers to Microsoft SQL Server, plSQL refers to Oracle’s version of SQL, other versions including mySQL, SQLite, etc. Each product uses its own variation of the standard SQL language.

SQL has extensions, like T-SQL (Transact-SQL), are generally considered full-fledged programming languages, complete with looping, if/then, case statements, etc., while SQL itself is limited to simply querying and updating data and is not considered a full-fledged programming language. T-SQL is proprietary to MSSQL. (Proprietary means exclusively used by…)

It is chaotic with different SQL extensions. Inconsistent definitions and syntax… 大同小异

To find which extension of SQL your databases use, refer to your database management system (DBMS) documentation. 用的什么软件，去各自产品网站找文档。

Azure SQL Documentation: https://learn.microsoft.com/en-us/azure/azure-sql/?view=azuresql

Q: Is SQL case-sensitive?

A: Short answer is NO, but there are conventions whether you should use lower or uppercase.

Long answer: it depends on which part of SQL you are referring to.

• Keywords, such as SELECT and WHERE, are case- insensitive. In theory, you can write in either lower and upper case, but the convention is that SQL keywords are written in capital letters.
• Table and column names. This is complicated, depending on the DBMS and the operating system (Windows, macOS, or Linux).
  • MS SQL Server is case- insensitive by default. But you can change this behavior by modifying the collation setting. The convenstion is to write table and column names in lowercase.
  • MySQL is case-insensitive in Windows and macOS, while it is case-sensitive in most Linux systems.
• Column values. Depend on the DBMS.
  • MySQL and MS SQL Server have case- insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc', 'ABC', or 'aBc'. The same goes for the LIKE operator.
  • You can change the collation setting to modify the default behavior.

Q: What are differencec between View and Table?

A: A View is a virtual table that does not store any data itself but presents metadata from one or more Table’s. A table contains data, a view is just a SELECT statement which has been saved in the database. One useful view is INFORMATION_SCHEMA.

SQL Server supports a three-part naming convention when you refer to the current server.

A three-part name specifies the relational database, the schema (or library depending on naming method), and the name of an object for use in an SQL statement.

SQL Server name	Azure SQL name
Database	Catalog
Schema	Schema
Object	Object
user-defined data type	Domain

Select

Full syntax

SELECT [ ALL | DISTINCT ]
[ TOP (top_value) [ PERCENT ] [ WITH TIES ] ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]];

• WHERE to filter which rows to subset

Return all rows and all columns

SELECT *  
FROM DimEmployee  
ORDER BY LastName; 

* to subset all columns

Selected columns and all rows

• Each column is separated by a comma.

SELECT BusinessEntityID, FirstName, LastName, ModifiedDate
FROM [Person].[Person]

Q: Why do we use square brackets?

A: They come in handy if the names of your columns or tables coincide with reserved SQL keywords or contain spaces or hyphens.

-- an example why brackets [] are important
SELECT *
FROM [My Table]      -- Identifier contains a space and uses a reserved keyword.
WHERE [order] = 10   -- Identifier is a reserved keyword.

Q: What does [Person].[Person] mean?

A: [SchemaName].[TableName] .

Selected columns and top 10 rows

This is useful for previewing a table with a large number of rows.

SELECT TOP(10)
     [BusinessEntityID]
    ,[FirstName]
    ,[LastName]
    ,[ModifiedDate]
FROM [Person].[Person]

Using table aliasing

SELECT e.*  
FROM DimEmployee AS e  
ORDER BY LastName;  

Rename a column

SELECT FirstName, LastName, StartDate AS FirstDay  
FROM DimEmployee   
ORDER BY LastName; 

New column based on one existing column

SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay  
FROM DimEmployee  
ORDER BY LastName;  

Unique values in one column

SELECT DISTINCT Title  
FROM DimEmployee  
ORDER BY Title;  

Save the query result into a new table

SELECT * INTO CustomersGermany
FROM Customers

Select from multiple tables

SELECT inventory.inventory_id, products.product_name, inventory.quantity
FROM inventory
INNER JOIN products
ON inventory.product_id = products.product_id
ORDER BY inventory_id;

Filter rows based on values in one column

Return all rows from the employees table where the last_name is either ‘Smith’, ‘Anderson’, or ‘Johnson’.

SELECT *
FROM employees
		WHERE last_name IN ('Smith', 'Anderson', 'Johnson');

Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

Note that: single quotes 'Smith'are used for matched strings. Double quotes are used for columns names or table names.

For instance

select "first_name" from "employees";

where first_Name is a column name from employees table.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM employees
    WHERE last_name = 'Smith'
    OR last_name = 'Anderson'
    OR last_name = 'Johnson';

String

LEFT, RIGHT, and SUBSTRING

• LEFT(string, number_of_chars) subset a number of characters from left
• RIGHT(string, number_of_chars) subset a number of characters from right
• SUBSTRING(string, start, length) subset from a middle position indexed at start

CHARINDEX(substring, string, start) searches for a substring in a string, and returns the position.

• start the postion where the search will start. The 1st position in string is 1.

Subset before a symbol

• LEFT(field_name, CHARINDEX('symbol needed', field_name) - 1)

  -- extract from the left to the hypen symbol (`-`)
  SELECT  
  LEFT(identifier,CHARINDEX('-', identifier) - 1) AS identifier
  FROM table_4
  -- extract from the left to the space (` `)
  SELECT  
  LEFT(identifier,CHARINDEX(' ', identifier) - 1) AS identifier
  FROM table_5
  

Subset after a symbol

• RIGHT(field_name, CHARINDEX('symbol needed', (REVERSE(field_name))) - 1)

  use REVERSE to get the position starting from the right.

Wildcards

Use LIKE pattern to match a specific pattern, rather than one exact value.

Wildcard character	Description	Example
% or *	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word computer anywhere in the book title.
_(underscore) or ?	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range [a-f] or set [abcdef].	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^]	Any single character not within the specified range [^a-f] or set [^abcdef].	WHERE au_lname LIKE 'de[^l]%' finds all author last names starting with de and where the following letter isn’t l.

• Escape a wildcard character: enclose with [].

Print column names

-- print all column names
SELECT * 
	FROM sys.columns 
	WHERE object_id = OBJECT_ID('sym_v1.sym_coverage')

sys.columns a table; returns a row for each column of an object that has columns, such as views or tables.

See HERE for the columns included in sys.columns.

• object_id ID of the object to which this column belongs.

If you use COUNT(*), then it returns the number of columns in the table.

Alternatively, you can use INFORMATION.SCHEMA.COLUMNS

SELECT *
	FROM fds.INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = 'ff_advanced_af'

Note that when you reference the information schema views,

1. You must use a qualified name that includes the INFORMATION_SCHEMA schema name.

   Q: What is a qualified name?

   A: The table catalog that includes the schema you are referring to.

2. TABLE_NAME does not include the schema name.

The preceeding query will return:

What might be of most interest: (Full list)

• COLUMN_NAME
• ORDINAL_POSITION Column identification number.