Getting Started Exercises

Checking SQL Server Version

To check the SQL Server version you use the global system configuration function @@VERSION.

SELECT @@VERSION;

Checking the Database Name

To find the database name you use the DB_NAME() function :

select DB_NAME()

Database ID

To get the database id use the DB_ID() function.

select DB_ID()

Checking Your Username

You can check the username you are logged in to SQL using the ORIGINAL_LOGIN(), CURRENT_USER, SYSTEM_USER functions as follows :

SELECT ORIGINAL_LOGIN(), CURRENT_USER, SYSTEM_USER;

Analysis

The ORIGINAL_LOGIN() will show the username used to log in to SQL Server. The CURRENT_USER is the database user current used to run queries to which the ORIGINAL_LOGIN is mapped to. The SYSTEM_USER shows the impersonated user. When running queries using the EXECUTE AS, the SYSTEM_USER will show the user that is being impersonated.

## Listing the Available Tables

The INFORMATION_SCHEMA views used to to get the information about the tables and views. This is an ISO standard approach.

The following would return all the tables in the current database.

SELECT * FROM INFORMATION_SCHEMA.TABLES

You can all use the system views to get all tables in the database as follows :

SELECT * FROM sys.tables

Find all schemas owned by a specific schema

We can find all schema owned by a specific user as follows :

SELECT name
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'HumanResources';

The system catalog tables are not as friendly as the information schema and may not provide all the information you require, e.g instead of return the schema name they return the _schemaid.

Check the existence of a condition

We can use the EXISTS keyword to check for a condition without returning the data that proves the condition, e.g : to find if we have employees that took more than 40 hours of sick leave without knowing who exactly are the employees we can use the following query.

SELECT 1
WHERE  EXISTS (
   SELECT *
   FROM HumanResources.Employee
   WHERE SickLeaveHours > 40
);

or we could also have used :

SELECT TOP(1) 1
FROM HumanResources.Employee 
WHERE SickLeaveHours > 40

Sorting Nulls High or Low

Add a semaphore expression to your ORDER BY clause for the column in question. Then specify ASC or DSC to make the nulls sort first or last as desired. The following example adds such an expression for the Weight column in order to sort that column with nulls last.

SELECT  ProductID, Name, Weight
FROM    Production.Product
ORDER BY ISNULL(Weight, 1) DESC, Weight;

The ISNULL function takes in a value and if its null returns another value. In our case we return 1.

Another way is to use the IIF function as follows :

SELECT  ProductID, Name, Weight
FROM    Production.Product
ORDER BY IIF(Weight IS NULL, 1, 0), Weight;

Getting a Sample of the Table Data

You can use the TABLESAMPLE keyword to get a sample of the data in a table. You can use a percent or the number of rows as follows :

SELECT *
FROM Purchasing.PurchaseOrderHeader
TABLESAMPLE (5 PERCENT);

or using rows :

SELECT *
FROM Purchasing.PurchaseOrderHeader
TABLESAMPLE (200 ROWS);