What is SQL

SQL full form is Structured Query Language. SQL is a domain-specific language. It is used in application development language to enable a programmer to work with the data. The data is stored in a relational database. To manage this data, we have relational database management systems like SQL Server, MySQL, MS Access, etc. that use SQL as a standard database language.

This introduction to SQL tutorial will give you a quick overview on:

What Is Data?

Data is distinct pieces of information, which can be facts, figures, or details that are stored in or used by a computer.

What Is Data

What Is a Database?

A database is a well-organized collection of data that is stored in an electronic format. To be more specific, a SQL database is an electronic system that allows to easily access, manipulate, and update the data.

What Is a Database

What Is a Database Management System?

Consider a School SQL Database which has a record of the present students and the previously studied students in the Student Details table. Similarly, it may contain Faculty Details, Management Details, Staff Details , and many more depending on the school’s requirement. As the data is in huge amount, to manage it we need a database management system.

Watch this Introduction to SQL and SQL Commands video:

Database Management System Almost all modern databases are managed by a Database Management System (DBMS). Basically, it is a system software used for creating data in a systematic way and managing databases. 

How Does SQL Database manage Data?

DBMS provides, for both users and programmers, a fundamental way to create, retrieve, update, and manage data.

Consider a scenario where a student XYZ wants to change his address. The DBMS searches the details of XYZ in the table ‘Student Details’ from the database ‘School Database’ and displays it for the user, and then the user edits it.

Now, we have a clear picture of a database and its managing system. Let’s move on.

Types of Database Architecture

We have two types of database architecture:

File Server Architecture

File Server Architecture In the file server architecture, files are located on the local system. It is useful for sharing information across a network. The client sends a request for a file over the network, and the file server forwards the file to the client. This is considered to be the most primitive type of data service used for exchanging information over a network. The file server provides access to remote server processors too. Here is an example to understand the implementation of the file server.

File Server Architecture 2Suppose, you have an Excel file and one of your friends requests you to send that file for some information. So, you send a copy to your friend. Now when you make any changes in your original Excel file, those changes would not be reflected in the file which is with your friend.

In this example, you are a file server and your friend is the device requesting for the information. We can conclude on the file server architecture by quoting that the server acts as a sorting device and only one person at a time can have access to it.

Client Server Architecture

In the client server architecture, the database is the server and any application that uses the data is a client. Here is an example to explain the working of this server. Consider, three OLE DB components or client systems accessing the database at the same time. The systems have logged into the IRCTC website to know the number of trains running from X destination to Y destination.

Client Server Architecture

The client system sends the request to the network server. The network server sends the same request to the database and the final result is sent to the client system. This process is conducted when one system sends the request. But in real time, there will be n number of systems and there can be multiple requests that are sent at a time for the same data. Database server will have to process all the requests simultaneously and send back the requested data to the client systems.

What Is SQL?

As discussed before SQL full form is a structured query language, it helps you to communicate with the database by commands. Here are some of the features of SQL Database:

It allows users to extract data from relational database.

It allows to create database and tables.

It allows updating, inserting, deleting and altering database and tables.

It provide security and allow to set a permission.

Allows people to handle data in new ways.

A Brief Explanation on SQL

Types of SQL Commands

SQL commands are traditionally divided into four categories:

Data Query Language (DQL Commands in SQL)

Data Definition Language (DDL Commands in SQL)

Data Manipulation Language (DML Commands in SQL)

Data Control Language (DCL Commands in SQL)

Categories of SQL Commands

Data Query Language (DQL Commands in SQL)

Data Query Language comprises only one command ‘select.’ This command can be accompanied by many other clauses to compose queries.

Data Definition Language (DDL Commands in SQL)

Data Definition Language is power for SQL, which allows a user to create and restructure database objects. The basic DDL commands in SQL are Create Tables, Alter Tables, and Drop Tables.

| CREATE TABLE | It creates a new table |

| DROP TABLE | It deletes the ENTIRE table. |

| ALTER TABLE | Modifies the existing table |

Data Manipulation Language (DML Commands in SQL)

Data Manipulation Language is used to manipulate data within the tables. The basic DML commands in SQL are Insert, Update and Delete.

| SELECT | Retrieve information from database |

| INSERT | Add new information to a database |

| UPDATE | Modifies the information currently stored in a database |

| DELETE | Delete information from the database |

Data Control Access Language (DCL Commands in SQL)

And finally, we have Data Control Access which allows the user to control access to data within the database. These DCA commands are generally used to control the distribution of privileges among users and create objects related to user access. The basic DCL commands in SQL are Grant and Revoke.

This bring us to the end of Introduction to SQL. Here we have learnt what is SQL, SQL full form, SQL Database, all basic SQL commands and its types – DDL, DCL, DML and DQL with examples.

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

This Edureka tutorial video on Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples.
The following are the topics covered in this tutorial:

  • What is DBMS?
  • Types of DBMS
  • What is SQL?
  • What is SQL Server?
  • Features of SQL Server
  • SQL Server Installation
  • SQL Server Command Categories
  • SQL Server Data Types
  • DDL Commands
  • Keys in Database
  • Constraints in Database
  • DML Commands
  • Operators
  • Nested Queries
  • Joins
  • Stored Procedures
  • DCL Commands
  • TCL Commands
  • Exception Handling

What are the differences between Standard SQL and Transact-SQL?

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

#1 Names of Database Objects

In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets ([]). Look at these examples for the name of a table in T-SQL:

CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100));
CREATE TABLE dbo.test.[first name]  ( Id INT, Name VARCHAR(100));

Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.

What Is Different in a SELECT Statement?#2 Returning Values

The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:

SELECT 12/6 ;

In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.

#3 Limiting Records in a Result Set

In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:


T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:


As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:

#4 Automatically Generating Values

The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:


In T-SQL we can also automatically generate values, but in this way:

#5 Math Functions

Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.

T-SQL function ROUND:


#6 Aggregate Functions

We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.

Standard SQL:



But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.

#7 Retrieving Parts of Dates and Times

Most relational database systems deliver many functions to operate on dates and times.

In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).

There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.

#8 Operating on Strings

Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).

Another very-often-used string function is SUBSTRING.

The standard SQL syntax for the SUBSTRING function looks like:

SUBSTRING(str FROM start [FOR len])

but in T-SQL, the syntax of this function looks like:

SUBSTRING(str, start, length)

There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:

As you can see, this syntax makes use of the || operator to add one string to another.

But the equivalent operator in T-SQL is the plus sign character. Look at this example:

SELECT col1 + col2  FROM tab;

In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:

SELECT CONCAT(col1, str1, col2, ...)  FROM tab;

We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:


where x indicates how many times to repeat the string or character.

#9 Inequality Operator

During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:

SELECT col3 FROM tab WHERE col1 != col2;
#10 ISNULL Function

In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.

Which Parts of DML Syntax Are Different?

In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.

#11 OUTPUT Keyword

The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.

Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.

UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;

We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.

#12 Syntax for INSERT INTO ... SELECT

Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

It is not a standard feature but a feature characteristic of SQL Server.

#13 FROM Clause in DELETE and UPDATE

SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:

FROM Author
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

SET Book.Price=Book.Price*0.2
FROM Author
WHERE Book.AuthorId=Author.Id AND Author.Id=12;

The SQL standard doesn’t provide this syntax.


You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:

DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;

This feature is not in the SQL standard.


This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on https://dzone.com

Building PHP apps using SQL Server on Windows

Building PHP apps using SQL Server on Windows

3 step: Building PHP apps using SQL Server on Windows

Step 1: Set up your environment

In this section, you will get SQL Server on Windows. After that you will install the necessary dependencies to create PHP apps with SQL Server

1. Install SQL Server

  1. If you don’t have SQL Server Developer (or above) installed, click here to download the SQL Server exe.
  2. Run it to start the SQL installer.
  3. Click Basic in Select an installation type.
  4. Click Accept after you have read the license terms.
  5. (Optional) if you need to, you can choose a custom installation location for SQL Server.
  6. Click Install to proceed with the installation.

You now have SQL Server installed and running locally on your Windows computer! Check out the next section to continue installing prerequisites.

2. Install PHP and Chocolatey

You can download PHP using the Web Platform Installer. Once you download Web PI, open it up and download the entry which says ‘PHP 7.2.7 (x64) for IIS Express’.

Next, install Chocolatey. Chocolatey is a package manager like apt-get and yum for Windows. We will use Chocolatey later in the tutorial. Use an elevated Command-line session (run as administrator):

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

For Chocolatey to work, you now need to restart the terminal session by closing and opening the terminal.

You have succesfully installed PHP and Chocolatey on your machine!

3. Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

  1. Install the ODBC Driver.
  2. Install the SQL Server Command Line Utilities.

After installing SQLCMD, you can connect to SQL Server using the following command from a CMD session:

sqlcmd -S localhost -U sa -P your_password
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.

This how to run a basic inline query. The results will be printed to STDOUT.

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Apr 29 2016 23:23:58
Copyright (c) Microsoft Corporation
Developer Edition (64-bit)

1 rows(s) returned

Executed in 1 ns.

You have successfully installed SQL Server Command Line Utilities on your Windows machine!

Step 2: Create PHP application with SQL server

In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

1. Install the PHP Drivers for SQL Server

Download the Microsoft PHP Drivers for SQL Server from the download center.

Pick the appropriate dll - for example php_pdo_sqlsrv_72_nts.dll for the PDO Driver and php_sqlsrv_72_nts.dll for the SQLSRV driver.

Copy the dll’s to the C:\Program Files\iis express\PHP\v7.2\ext folder.

Register the dll’s in the php.ini file.

cd C:\Program^ Files\iis^ express\PHP\v7.2\ext
echo extension=php_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini
echo extension=php_pdo_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini

2. Create a database for your application

Create the database using sqlcmd.

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

3. Create a PHP app that connects to SQL Server and executes queries

mkdir SqlServerSample
cd SqlServerSample

Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.

$serverName = "localhost";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "sa",
"PWD" => "your_password"
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
echo "Connected!"

Run your PHP script from the terminal.

php connect.php


Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"

Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

$serverName = "localhost";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "sa",
"PWD" => "your_password"
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query
echo ("Inserting a new row into table" . PHP_EOL);
$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
$params = array('Jake','United States');
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);


//Update Query

$userToUpdate = 'Nikita';
$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
$params = array('Sweden', $userToUpdate);
echo("Updating Location for user " . $userToUpdate . PHP_EOL);

$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);

//Delete Query
$userToDelete = 'Jared';
$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("Deleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);

//Read Query
$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);

function FormatErrors( $errors )
/* Display errors. */
echo "Error information: ";

foreach ( $errors as $error )
    echo "SQLSTATE: ".$error['SQLSTATE']."";
    echo "Code: ".$error['code']."";
    echo "Message: ".$error['message']."";


Run your PHP script from the terminal.

php crud.php

Inserting a new row into table
1 row(s) inserted:
Updating Location for user Nikita
1 row(s) updated:
Deleting user Jared
1 row(s) deleted:
Reading data from table
2 Nikita Sweden
3 Tom Germany
4 Jake United States

Congratulations! You have created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

Step 3: 3 Make your PHP app up to 100x faster

In this section we will show you a simple example of Columnstore Indexes and how they can improve data processing speeds. Columnstore Indexes can achieve up to 100x better performance on analytical workloads and up to 10x better data compression than traditional rowstore indexes.

1. Create a new table with 5 million rows using sqlcmd

sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

2. Create a PHP app that queries this table and measures the time taken

cd ~/
mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

Using your favorite text editor, create a new file called columnstore.php in the SqlServerColumnstoreSample folder. Paste the following code inside it.

$time_start = microtime(true);

$serverName = "localhost";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "sa",
"PWD" => "your_password"
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Read Query
$tsql= "SELECT SUM(Price) as sum FROM Table_with_5M_rows";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Sum: ");
if ($getResults == FALSE)
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
echo ($row['sum'] . PHP_EOL);


function FormatErrors( $errors )
/* Display errors. */
echo "Error information: ";

foreach ( $errors as $error )
    echo "SQLSTATE: ".$error['SQLSTATE']."";
    echo "Code: ".$error['code']."";
    echo "Message: ".$error['message']."";

$time_end = microtime(true);
$execution_time = round((($time_end - $time_start)*1000),2);
echo 'QueryTime: '.$execution_time.' ms';


3. Measure how long it takes to run the query

Run your PHP script from the terminal.

php columnstore.php

Sum: 50000000
QueryTime: 363ms

4. Add a columnstore index to your table

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

5. Measure how long it takes to run the query with a columnstore index

php columnstore.php

Sum: 50000000
QueryTime: 5ms

Congratulations! You just made your PHP app faster using Columnstore Indexes!

Check out the PHP Driver on GitHub

Thanks For Visiting, Keep Visiting.

Originally published on sqlchoice