DML statement

Today we are trying to cover Basic commands of DML statement.

For DDL & DML basic Description you can just go through my previous article.

https://flamboyencewithsql.wordpress.com/2014/02/17/sql-tutorial-what-is-sql-rdbms-ddl-dml-dcltcl/

Data manipulation language is subdivided into two categories:

Retrieval commands: SELECT

Editing commands:  INSERT, UPDATE, DELETE, MERGE

(DML statement enhanced with some more commands or clause, which we’ll cover in future posts)

Before jump into the DML Command we need to go through a very important and basic clause “WHERE”.

Where clause is used for specifies the condition or filters the result for a table or view.   We can choose a specific row for select, update or delete the data by the “where” clause. We are going to use this clause in this article.

  • SELECT

Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables.

Syntax:

SELECT Column list FROM Table WHERE ColumnName = Value

1

  • INSERT

Insert is used to add new row to a table or view.

Syntax:

Insert into TableName (coulumn list)

Values (value list)

Value list is equal to the column list in insert statement for the table,

In Insert list values are expected for all columns except Identity column.

1

  • UPDATE

Update is used to change an existing row in a table or view, we can change one or more values in a table depend on condition.

Syntax:

Update  TableName

Set ColumnName = ‘NewValue’

Where  ColumnName  = Value

1

  • DELETE

Delete removes one or more rows or all rows from a table or view.

From far end the DDL command TRUNCATE and DELETE are perform approximate same functionality.

But the main difference between them is, with DELETE we can use where clause to delete specific data from a table but we can’t use where clause with truncate and it removes all data from a table.

There is also some internal storage difference between both of them which we’ll discuss in future post.

Syntax:

Delete From TableName Where  ColumnName = Value

1

  • MERGE

By the merge statement we can performs insert, update, or delete operations on a table based on the results of a join with another table.

Overall within a single statement we can insert, update, or delete rows in one table based on differences found in the other table.

In my Next Blog we’ll discuss it deeply.


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Basic commands of DDL statement

Today we are trying to cover Basic commands of DDL statement.

For DDL & DML basic Description you can just go through my last blog

https://flamboyencewithsql.wordpress.com/2014/02/17/sql-tutorial-what-is-sql-rdbms-ddl-dml-dcltcl/

  • CREATE

CREATE statements is used to define new Objects

So here we CREATE a TABLE for a specific database.

Firstly need to use the database where we want to create the table;

USE DATABASENAME

GO

“Go” is like a separator which is used for execute the commands separately

Now we have to just write the create statement and execute it

Create Table Employee

(  EmpID  INT,

EmpName Varchar(30),

EmpLastName Varchar(30),

DeptNo INT )

1

The table Employee is now in your database

  • ALTER

ALTER statements is used to change the defination of the objects,

In simple words by this command we can modify  the object.

ALTER Table Employee ADD Manager_Name Varchar(30)

1

  • TRUNCATE

Truncate statement is used to remove all the data from the table

TRUNCATE TABLE Employee

1

  • RENAME

Rename statement is used to modify the name of table or column or many more of objects in Database

EXEC sp_rename  ‘Employee’, ‘Emp’

1

After run this query the new name of the table is ‘Emp’

  • DROP

DROP is used to drop full definition of that object,

e.g. remove table definition with data by the below command,

1

DROP TABLE Emp

 


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

SQL Tutorial – What is SQL (RDBMS, DDL, DML, DCL,TCL)

Hi Friends,

SQL stands for structured Query Language, It is an ANSI (American National Standards Institute) standard, and with SQL you can retrieve or manipulate data (Insert, Update or delete data from the database).

So we can say that SQL is a language by which we can interact with RDBMS.

Now here is new question, what is RDBMS?

firstly DBMS is stands for Database Management System,

For every organization, data is most important assets and for every organization it needs to take care and manage the data is very important, DBMS is a system which is provides the functionality to store and manage data.

RDBMS is just a relational model of DBMS so that it is called RDBMS.

So again SQL is a language by which we can interact with RDBMS.

Types of interaction with RDBMS by SQL are:

1.       DDL (Data Definition Language)

DDL is language which allows you to define objects or allow you to set or change the definition of object like:

CREATE

ALTER

DROP

TRUNCATE

RENAME

2.       DML (Data Manipulation Language)

DML is language which allows you to manipulate data from database object like:

SELECT

INSERT

UPDATE

DELETE

MERGE

3.       DCL (Data Control Language)

DCL is language which allows you to configure security access to relational databases.

Overall it is used to modify the user rights for the database.

GRANT

REVOKE

4.       TCL (Transaction Control Language)

 TCL is language which allows you to manage the changes made by DML statements.

COMITT

SAVEPOINT

ROLLBACK

SET TRANSACTION


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

SQL Tutorial – Basic Information about SQL and versions

Hi Friends,

Microsoft SQL Server is a product of Microsoft, and developed by Microsoft as a database, it is a Relational Database Management System (RDBMS) whose primary function is to store and retrieve data as requested by other software applications for the same computer or those running on another computer across the network, Its primary query language is T-SQL.

There are different editions of Microsoft SQL Server (some of the main release are):

 

Version Realease Year Release Name
1 1989 SQL Server 1.0
     
1.1 1991 SQL Server 1.1
     
4.21 1993 SQL Server 4.21
     
6 1995 SQL Server 6.0
6.5 1996 SQL Server 6.5
7 1998 SQL Server 7.0
  1999 SQL Server 7.0 OLTP
8 2000 SQL Server 2000
8 2003 SQL Server 2000 64-bit Edition
     
9 2005 SQL Server 2005
10 2008 SQL Server 2008
10.25 2010 SQL Azure DB
10.5 2010 SQL Server 2008 R2
11 2012 SQL Server 2012
12 2014 SQL Server 2014

 

Also here is some of the SQL specification which we need to know before dive into SQL world

Name

Microsoft SQL Server

Description

Microsofts relational DBMS

Website

                                http://www.microsoft.com/en-in/sqlserver/default.aspx

Developer

Microsoft

Initial release

1989

License

commercial

Implementation language

C++

Server operating systems

Windows

Database model

Relational DBMS

Data scheme

yes

Typing

yes

Secondary indexes

yes

SQL

yes

Supported programming languages

Net ,Java ,PHP ,Python ,Ruby, Visual Basic

Server-side scripts

Transact-SQL and .NET languages

Triggers

yes

Partitioning methods

tables can be distributed across several files (horizontal partitioning), but no sharding

Replication methods

yes, but depending on the SQL-Server Edition

Foreign keys

yes

Transaction concepts

ACID

Concurrency

yes

Durability

yes


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |