Author: sqlschool

Happy Pongal From SQL School Training Institute

 

SQL School Training Institute is one of the world’s leading training providers. We partner with companies and individuals to address their unique needs, providing training and coaching that helps working professionals achieve their career goals.

We provide Online Training, Classroom Training and Video Training in disciplines such as SQL Server, SQL DBA, MSBI, Azure SQL, Power BI, Oracle PL-SQL, Oracle DBA, GoldenGate, RAC DBA, Python and Data Science among others, where technologies and best practices are changing rapidly and demand for qualified candidates significantly exceeds supply.

Website:

http://www.sqlschool.com

Call us:

Email: contact@sqlschool.com

India (+91): (0)40 64577244 (Office) | +91(0) 9666440801 (Mobile) | +91(0) 90 1434 1434 (24×7)

USA (+1): (510) 400-4845

#sqlserver #sqldba #msbi #plsql #Oracledba #python #datascience #powerbi #azuresql

Using of And, OR and NOT in SQL Server

AND: The AND operator displays a record if all the conditions separated by AND is TRUE.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;

Example:

SELECT * FROM Customers
WHERE Country=’India’ AND City=’Hyderabad’;

The above SQL statement selects all fields from “Customers” where country is “India” AND city is “Hyderabad”

OR:The OR operator displays a record if any of the conditions separated by OR is TRUE.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;

Example:

SELECT * FROM Customers
WHERE City=’Hyderabad’ OR City=’Pune’;

The following SQL statement selects all fields from “Customers” where city is “Hyderabad” OR “Pune”

NOT:The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE NOT condition;

Example:

SELECT * FROM Customers
WHERE NOT Country=’India’;

Using of Where Clause in SQL Server

Using of Where Clause in SQL Server

This SQL Server tutorial explains how to use the WHERE clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

WHERE conditions;

Parameters or Arguments

conditions : The conditions that must be met for records to be selected.

For example:

SELECT * FROM employees WHERE first_name = ‘Jane’;

In this SQL Server WHERE clause example, we’ve used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is ‘Jane’. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

SSIS School of Training

Date data types

DATE() A date. Format: YYYY-MM-DD

Note: The supported range is from ‘1000-01-01’ to ‘9999-12-31’

DATETIME() A date and time combination. Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

TIMESTAMP() A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’

TIME() A time. Format: HH:MI:SS

Note: The supported range is from ‘-838:59:59’ to ‘838:59:59’

YEAR() A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

Types of keys is SQL Server

What is Key? Keys are fields in a table which participate in below activities in RDBMS systems:

  • To create relationships between two tables.
  • To maintain uniqueness in a table.
  • To keep consistent and valid data in database.
  • Might help in fast data retrieval by facilitating indexes on column(s).

SQL Server supports various types of keys, which are listed below:

  1. Candidate Key
  2. Primary Key
  3. Unique Key
  4. Alternate Key
  5. Composite Key
  6. Super Key
  7. Foreign Key

Before discussing each type in brief, have a look on the below image used as an an example to define types of keys.

Lets discuss each type in detail:

  • Candidate Key: Candidate key is a key of a table which can be selected as a primary key of the table. A table can have multiple candidate keys, out of which one can be selected as a primary key.

Example: Employee_Id, License_Number and Passport_Number are candidate keys

  • Primary Key: Primary key is a candidate key of the table selected to identify each record uniquely in table. Primary key does not allow null value in the column and keeps unique values throughout the column. In above example, Employee_Id is a primary key of Employee table. In SQL Server, by default primary key creates a clustered index on a heap tables (a table which does not have a clustered index is known as a heap table). We can also define a nonclustered primary key on a table by defining the type of index explicitly.

A table can have only one primary key and primary key can be defined in SQL Server using below SQL statements: CRETE TABLE statement (at the time of table creation) – In this case, system defines the name of primary key ALTER TABLE statement (using a primary key constraint) – User defines the name of the primary key Example: Employee_Id is a primary key of Employee table.

  • Unique Key: Unique key is similar to primary key and does not allow duplicate values in the column. It has below differences in comparison of primary key:

It allows one null value in the column. By default, it creates a nonclustered index on heap tables. Alternate Key Alternate key is a candidate key, currently not selected as primary key of the table. Example: License_Number and Passport_Number are alternate keys.

  • Composite Key: Composite key (also known as compound key or concatenated key) is a group of two or more columns that identifies each row of a table uniquely. Individual column of composite key might not able to uniquely identify the record. It can be a primary key or candidate key also.

Example: In salary table, Employee_Id and Salary_Month_Year are combined together to identify each row uniquely in Salary table. Independently Employee_Id or Salary_Month_Year column cannot identify each row uniquely. We can create a composite primary key on Salary table using Employee_Id and Salary_Month_Year columns.

  • Super Key: Super key is a set of columns on which all columns of the table are functionally dependent. It is a set of columns that uniquely identifies each row in a table. Super key may hold some additional columns which are not strictly required to uniquely identify each row. Primary key and candidate keys are minimal super keys or you can say subset of super keys.

In above example, In Employee table, column Employee_Id is sufficient to uniquely identify any row of the table, so that any set of column from Employee table which contains Employee_Id is a super key for Employee Table. For example: {Employee_Id}, {Employee_Id, Employee_Name}, {Employee_Id, Employee_Name, Address} etc. License_Number and Passport_Number columns can also identify any row of the table uniquely. Any set of column which contains License_Number or Passport_Number or Employee_Id is a super key of the table. For example: {License_Number, Employee_Name, Address}, {License_Number, Employee_Name, Passport_Number}, {Passport_Number, Employee_Name, Address, License_Number}, {Passport_Number, Employee_Name}, {Passport_Number, Employee_Id} etc.

  • Foreign Key: In a relationship between two tables, a primary key of one table is referred as a foreign key in another table. Foreign key can have duplicate values in it and can also keep null values if column is defined to accept nulls.

Example: Employee_Id (primary key of Employee table ) is a foreign key in Salary table.

Use SQL Server Management Studio (SSMS) on Windows to manage SQL Server on Linux

This topic shows how to use SQL Server Management Studio (SSMS) to connect to SQL Server 2017 on Linux. SSMS is a Windows application, so use SSMS when you have a Windows machine that can connect to a remote SQL Server instance on Linux.

After successfully connecting, you run a simple Transact-SQL (T-SQL) query to verify communication with the database.

Install the newest version of SQL Server Management Studio

When working with SQL Server, you should always use the most recent version of SQL Server Management Studio (SSMS). The latest version of SSMS is continually updated and optimized and currently works with SQL Server 2017 on Linux. To download and install the latest version, see Download SQL Server Management Studio. To stay up-to-date, the latest version of SSMS prompts you when there is a new version available to download.

Connect to SQL Server on Linux

The following steps show how to connect to SQL Server 2017 on Linux with SSMS.

  1. Start SSMS by typing Microsoft SQL Server Management Studioin the Windows search box, and then click the desktop app.
    1. In the Connect to Serverwindow, enter the following information (if SSMS is already running, click Connect > Database Engine to open the Connect to Server window):

     

    Setting Description
    Server type The default is database engine; do not change this value.
    Server name Enter the name of the target Linux SQL Server machine or its IP address.
    Authentication For SQL Server 2017 on Linux, use SQL Server Authentication.
    Login Enter the name of a user with access to a database on the server (for example, the default SA account created during setup).
    Password Enter the password for the specified user (for the SA account, you created this during setup).

  2. Click Connect.
  3. After successfully connecting to your SQL Sever, Object Explorer opens and you can now access your database to perform administrative tasks or query data.

    Run sample queries

    After you connect to your server, you can connect to a database and run a sample query. If you are new to writing queries, see Writing Transact-SQL Statements.

    1. Identify a database to use to run a query against. This could be a new database you created in the Transact-SQL tutorial. Or it could be the AdventureWorks sample database that you downloaded and restored.
    2. In Object Explorer, navigate to the target database on the server.
    3. Right-click the database and then select New Query:

    4. n the query window, write a Transact-SQL query to select data from one of the tables. The following example selects data from the Production.Product table of the AdventureWorks database.

       SELECT TOP 10 Name, ProductNumber
       FROM Production.Product
       ORDER BY Name ASC
    5. Click the Execute button:

Why to use SSIS?

PURPOSE OF SSIS:
E 1. TO PERFORM DATA EXTRACTION OPERATION FROM HETEROGENOUS (DIFFERENT FORMAT) SOURCES
EX: GATHER DATA FROM SQL DATABASE + EXCEL SHEET + TXT FILE

T 2. TO PERFORM DATA TRANSFORMATION OPERATIONS
EX: DUPLICATE DATA TO MULTIPLE SERVERS, COMPARE & MERGE

L 3. TO LOAD DATA ONTO ANY DESTINATIONS, OF ANY FORMAT.
EXTRACTION OF DATA(E), TRANSFORMATION (T), DATA LOADS (L)

4. TO DESIGN “DATAWAREHOUSE” (DWH). ALSO FOR DATA LOAD OPERATIONS @ ETL TECHNIQUES

DATAWAREHOUSE IS A DATABASE USED TO STORE HISTORICAL, BUSINESS LEVEL INACTIVE
INFORMATION. SSIS CAN BE USED TO COMPARE THE LIVE (REAL-TIME) DATA WITH EXISTING
WAREHOUSE DATA AND IDENTIFY NEW ROWS / UPDATES TO EXISTING ROWS.

5. TO PERFORM WINDOWS ADMIN ACTIVITES EX: IIS, WMI

6. TO PERFORM SQL DATABASE ADMIN ACTIVITES EX: DATABASE MIGRATIONS

7. TO PERFORM ANY TYPE OF SQL OPERATIONS (T-SQL, PL/SQL, EXCEL SQL…)

SSIS School of Training

 

SQL DBA Course with SQL Azure Database & Certification

SQL DBA Course Includes:
* SQL Server T-SQL
* Performance Tuning
* Clustering
* Always On Availability
* Certifications (70 – 764, 765)
* SQL Azure Databse (Developer, Admin)

Register today for LIVE Online Training
http://www.sqlschool.com/SQLDBA-Online-Training.html

Call us today: +91 90 1434 1434 [Toll Free]

#SQLDA #DBA #Azure #AzureAdmin #AzureDatabase #Database #Administration #TSQL #SQL #Training #Online

Azure SQL Database Power Shell – TOP TEN Commands

Azure SQL Database Power Shell – TOP TEN Commands

Example 1: Create a new Azure SQL Database server
New-AzureRmSqlServer -ResourceGroupName “ResourceGroup01” -Location “Central US” -ServerName “server01” –
Example 2: Create a new Azure SQL Database
New-AzureRmSqlDatabase -ResourceGroupName “ResourceGroup01” -ServerName “Server01” -DatabaseName “Database01”
Example 3: Get all databases on a server
Get-AzureRmSqlDatabase -ResourceGroupName “resourcegroup01” -ServerName “server01”
Example 4: Get a database by name on a server
Get-AzureRmSqlDatabase -ResourceGroupName “ResourceGroup01” -ServerName “Server01” -DatabaseName “Database02”
Example 5: Create an elastic database on a specified server
New-AzureRmSqlDatabase -ResourceGroupName “ResourceGroup01” -ServerName “Server01” -DatabaseName “Database01” –
ElasticPoolName “ElasticPool01”
Example 6: Get all deleted database backups on a server
Get-AzureRMSqlDeletedDatabaseBackup -ResourceGroupName “ContosoResourceGroup” -ServerName “ContosoServer”
Example 7: Get a specified deleted database backup
Get-AzureRMSqlDeletedDatabaseBackup -ResourceGroupName “ContosoResourceGroup” -ServerName “ContosoServer” -DatabaseName
“ContosoDatabase”
Example 8: Delete a communication link
Remove-AzureRmSqlServerCommunicationLink -ResourceGroupName “ResourceGroup01” -ServerName “ContosoServer17” -LinkName
“Link01”
Example 9: Remove the auditing of an Azure SQL server
Remove-AzureRmSqlDatabaseAuditing -ResourceGroupName “ResourceGroup01” -ServerName “Server01”
Example 10: Remove a server
Remove-AzureRmSqlServer -ResourceGroupName “ResourceGroup01” -ServerName “Server01”

Azure SQL Database Power Shell – TOP TEN Commands

HOW TO EXECUTE ABOVE COMMANDS?

Step 1: Login to your Azure Account  (portal.azure.com)

Step 2: Create one or more SQL Servers and One or more SQL Databases in your Azure Account

Step 3: Log in to shell.azure.com

Step 4: Select the option : POWERSHELL from TOP >> LEFT Corner as shown below:

 

For more information and in-depth practical training on Azure SQL Database & Power Shell, register today for free demo at: http://sqlschool.com/SQL-Azure-Online-Training.html

Register today for Azure SQL Database & Power Shell Training

   +91-9666440801 (INDIA)
   +91-9542916779 (INDIA)
   +91-(0)40 64577244 (INDIA)
   +1-510-400-4845 (USA/Canada)
Reach us on our Toll Free Number:
+91 90 1434 1434  (Available : 24 x 7)
Website: http://www.sqlschool.com
Power Shell Training @ http://sqlschool.com/SQL-Azure-Online-Training.html