SQL Tutorials – online choices for SQL Server Tutorials

Some of the top on-line sql tutorial are available at Tutorial point, w3 schools, and Java. These are the top 3 contenders in sql tutorial top spots as in Google search list in terms of popularity and authority. There are several others available. Any sql tutorial usually provides an overview of the standardized query language that you could use to query, retrieve and manage data in relational databases. These are designed in such a way that even beginners can go through them and gain in expertise as they progress through these courses. The only prerequisites required of learners of sql tutorial is an understanding of what a database, RDBMS is, and what constitutes a programming language, its structure and how does programming language used to construct a program.

Once a learner is generally proficient in SQL, through the use of one of these sql tutorial, he/she is able to work with any of the many databases in heavy use. These include SQL Server, MySQL, Sybase, DB2, Oracle, Access and many others. Obviously, learning SQL would not help you with the new-fangled “no sql” databases.

Many of these sql tutorial offer certification. Candidates are given certificates based on clearing periodic examinations. These certificates accepted by employers as proof of the person being qualified in the SQL programming language.

SQL Tutorial-choices to learn sql online

Every sql tutorial is structured. The structure may be slightly different depending on which sql tutorial you are going to use. Lets take a closer look on some of the sql tutorial available:

  • The top choice in sql tutorial is the on-line course conducted by the Tutorial point. They start their sql tutorial with an overview. This is followed by RDBMS and database concepts. SQL statement syntax and the data types handled are introduced next. The sql tutorial then take the learner through operators, expressions. “Create”, “drop” and “select database statements are then introduced. Table manipulations, insert and select queries, “where” clauses are then introduced. Combining queries, update, delete, like, top clauses are talked about. “distinct” keyword and sorting of results are illustrate. After that advanced features such as Constraints, joins, unions and such others are introduced. Then sql tutorial informs about best practices, questions & answers. They even have a section on HR questions the candidates may have to face while interviewing for a job. Additional reading is suggested too.
  • Sql tutorial by w3 schools is well respected as well. W3 schools offer on-line courses in a very wide range of technologies in computers and the Internet. The w3schools has a little different structure of the material than Tutorial point. It discusses various SQL statement types and data types. The sql tutorial then progresses into a coverage of the functions available. This tutorial too has a quiz section but does not have the “interview” questions of the Tutorial point’s.
  • The sql tutorial from JavaTPoint is another widely used robust technology. It provides web service tutorials for beginners and professionals and has yet another structure.

Take your time to get familiar with different options available. Make your deliberated decision considering your priorities and goals.

Please don’t hesitate to contact us if you have any questions regarding sql tutorial. We, at  SQL Server Tutorial, always happy to help!

 

 

Learning SQL Server: SQL Server Tutorial online

There are many paths to learn SQL Server. In the article below we are going to do a quick overview of the most popular SQL Server tutorials available on the market.

SQL server tutorial-learn sql online

  • MSDN site of Microsoft would be considered by many as the first place to start your SQL Server Tutorial. This site provides plenty of resources for learning for beginners and more advanced users about relational database product. Most of these tutorials are ususally free and several of sql server tutorials are available for users who may not have a SQL Server in their organization. The online sql server tutorials also help in working on the learning project outside working hours. Usually these sql server tutorials provide access to some database for practice. Big advantage is that Microsoft lets you download and use the “express” edition of their current database products.                                                                                                                                         The MSDN site covers most of the important topics in its sql server tutorial. It starts by coverage of the database engine. The sql server tutorial covers the SQL Server management studio, sqlcmd utility, how to tune the database engine, using policy based management and best practices. Database engine covers how to use it and how to use the T-SQL statements. Then the sql server tutorial gets into data analysis and data mining topics. Creation of packages and implementation of packages are dealt with next. Use of replication and reporting services are included too. Topics useful to developers are then covered in sql server tutorial.
  • Going by the rankings in the search results (Google uses popularity and authority to rank sites), one of the top of sql server tutorial sites will be the Tech On Net. This sql server tutorial covers both the relational database technology of the SQL Server as well as the integrated tool, the T-SQL programming language that facilitates the usage of the same. The T_SQL is an extended version of the SQL language. This sql server tutorial starts with the basics of how to retrieve and manipulate data from the database. The tutorial then progressively moves through table creation, users and logins, sql functions and procedures.

There are no prerequisites needed on the part of the learner. The same is more or less true of other such sql server tutorials available on-line. Usually, they provide some form of access to a database for learner to try out their concepts.

  • The sql server tutorial from quackit is another popular and authoritative one. This sql server tutorial starts by familiarizing the learner with the capabilities of the SQL Server, the releases and editions of the same and an intro to the express edition. How to install the product and introduction to the management studio is then provided. The sql server tutorial then gets into the operational aspects and these cover the creation of a database, creation of table followed by manipulation of data in them are taken up. Details of SQL query generation and use are then covered. The sql server tutorial also covers stored procedures. Basic understanding of database is assumed.
  • Lynda.com is another provider that give quality course for sql server tutorials. In most situations, it is likely to be a paid one though.

This is not the full list of the SQL Server Tutorial available. Take your time, explore and evaluate which one is the best for you. If you have any questions regarding SQL Server Tutorial please contact us, – we are ready and always eager to help!

 

SQL Online: Ways to Learn SQL Online

One of the simplest ways to learn sql will be to learn sql online. If you are working already and looking to enhance your skills, online learning lets you do that at your own pace. There are many reasons why you should learn SQL. The overriding one is that it is a skill that can be monetized – there’s enough demand for it. There are also equally a lot of resources available for you to learn sql online. 

  1. First, online book. Like a regular book it has text and many examples codified in a book format. It is popular enough to be at a top location on any search engine list. Some top ranking sites are
  2. Other resources helping learn sql online – online courses, and they introduce some interactivity. Some of these courses offer examinations, passing which earns you a certificate of proficiency. Many sql online tutorials (like w3schools) provide certificates that are quite valuable on the market.

Ways to learn SQL online

SQL (pronounced “sequel”) or the structured query language lets you interact with a range of databases as they use the standardized way of access. All of learn sql online resources teach you about this language. The minimum set of access is often abbreviated as CRUD (create, read, update and delete). These learn sql online courses will take you in a structured manner through the operations on tables of databases. Databases as we know are similar to spreadsheets with the database being like the complete workbook while each page/sheet is a table. Rows and columns have similar logical meaning in both cases.  These learn sql online resources offer practicing on some kind of a database. Most often, learn sql online courses provide some form of a free sql based database, not necessarily the SQL Server.

When you have so many resources to choose from while learning sql online, it could be useful to have some pointers to what you have to pay attantion to. Further in this article we will try to provide this guidance.

  • W3schools is reputedly the largest website development service. They offer learn sql online course that teach beginners sql concepts and syntax. Beyond that, it takes you through the keywords use, important statements, and functions that are used in SQL. Among many learn sql online sites, this one lets you to earn a certificate that is very respected in the industry.
  • Another significant learn sql online course – is the “Intro to SQ: Querying and Managing Data” from Khan Academy. This course gives you a lot of interactivity. Bite sized lectures on video (video on You Tube), exercises, and a learning dashboard are some of its distinctive features.
  • Tutorials Point has a learn sql online course “Learn SQL”. Tutorial Point supposedly has the largest collection of IT tutorials. The complete course in PDF and can be downloaded for off-line use. It covers basic SQL, useful functions and its advanced features.
  • “Introduction to Relational Database” learn sql online course, run by Udacity, is worth a mention as well. Course is a fast, capturing introduction to using a relational database from your code, using examples in Python.

We hope this information will be helpful while you choosing learn sql online course. If you have any further questions regarding learn sql online, please do not hesitate to contact us. We are always ready to help you SQL Tutorials online.

 

 

SQL 2012 Update Statement – How to modify data in SQL?

SQL Update is a command or updating a data record. SQL Update query is part of the SQL standard that is implemented by all product vendors. Thus, the concept applies uniformly across all RDBMS products. Typically, in an RDBMS based database, data is organized as rows and columns. Each row of data has fields in columns that are related. One row of data is called a record. SQL Update is a means of updating a field within a record in the database. The syntax of SQL Update command provides for specifying the particular piece of data into a database table.

The following is the syntax for the SQL Update command. The words in capitals are the keywords that must be used as is. The text strings within quote marks in the syntax of SQL Update query indicated need to be filled by actual parameter values for pinpointing the specific items to be updated.

UPDATE  [table name]
SET [column name1]= ‘column value1′
WHERE [a particular column name]=’existing value of the column’

This above syntax of SQL Update specifies a specific table name on which data updates are to be made. The SET keyword lets the user specify the new values to be created in the given fields/columns of the table. The WHERE keyword specifies the existing value of a keyword so that the particular record can be pinpointed. In the absence of the WHERE keyword in the SQL UPDATE syntax, all the records will get updated. Let us follow this with an example so that the exact meaning is very apparent.

A phone book is something every one of us use. If a typical phonebook was implemented in an RDBMS a table “PhoneDirectory” could look something like the table indicated below.

 

SQL-Server-2012-Update-TSQL

A simple table like this could be updated manually. When the phone book is large, for example, the public phone book for the organization, updating will need database support. One of the most common changes that can happen is that the person may have switched over to another company. His or her work phone or home phone could change. SQL UPDATE statement provides the means to make a change to the database, specifically to a particular record in the database.

For the sake of illustrating the use of the SQL UPDATE, let us assume Robert Halden has been transferred to a different city where the ABC, Inc. has an office. In such a case both his work and home phones would change. SQL UPDATE command for making these desired changes would look like the following.

UPDATE PhoneBook
SET  WorkPhonenumber=’397-478-2294′, HomePhoneNumber=’397-225-8325′
WHERE ContactName=’Robert Halden’

This SQL UPDATE statement chooses the right table in a database and substitutes the work and home phone numbers of a specific record where the contact name is Robert Halden. The update happens in just one record that is required to be updated. If some particular column value(s) for all records were to be updated, dropping the WHERE clause of the SQL UPDATE command would do the job. The SET clause will specify the column and the value to be set in the complete database.

 

What is SQL – Structured Query Language

What is SQL or Structured Query Language becomes easier to understand if we look at database systems first. A database is a repository of data vital to the activities that need large amounts of data to be stored. Businesses are examples of such activities. Usually such data is used again and again. That requires the data to be organized and easily accessible. Retrieval or update of such data is required to be accurate. Relational databases allow users to set up flexible search criteria to accurately locate a specific data item. Relational databases organize data as tables of rows and columns. RDBMS lets users create flexible queries and locate data accurately. Query language offers standardized syntax to specify such queries. When found, operations possible on the data using Structured Query Language are:

  • Reading record(s) or parts of them
  • Modifying record and store back
  • Deleting record(s)
  • Others



RDBMS or the relational database management systems use SQL programming language. SQL is a standardized form of proprietary query languages used by the product companies. Structured Query Language is a standard of both ANSI and ISO organizations. Standardization makes migrating easier. Substantial changes to the SQL statements used in an application should not be required when moved to another database.

Many of the database products use SQL. Major, well-known database products available are Oracle, IBM DB2, Microsoft SQL Server, etc. Open source MySQL is a very popular product. Structured Query Language available with each of these products offer the core functions. Unfortunately, these companies implement additional functions. The core functions offer select, insert, create, update, delete and drop, etc. These are really sufficient to implement most operations one needs. SQL of these products coupled with the extensions are known differently. T-SQL is offered by Microsoft in SQL Server product while the Oracle version is called PL/SQL.

Distributed database applications, typically, work with a central database server known as the backend. Client applications communicate with this back-end to retrieve data and manipulate them. These clients issue queries for data with Structured Query Language statements. The database server is then updated with the changed data.

SQL is able to change/modify data in the RDBMS. Besides, it is able to change the structure of the database tables. Structured Query Language statements will let a user create a table, modify it or even entirely delete it. What SQL statements can do with data is insert data into fields in the table. What SQL lets you do is to “select” a specific table for modifications. A SQL “update” command changes the value of a piece (field) of data.

If you need to work with RDBMS applications, the core functions that constitute what is SQL would be easy to learn. There are only a few commands that are used. Learning the syntax of how the command word and associated parameters are used is quite simple too. Even when your application needs to migrate to any other RDBMS, it should not be difficult to manage. Optimization can be implemented through the core SQL first, the product specific extensions can be done as a next step.

Further Reading on SQL:

 

 

Overview of Transact SQL

IBM is credited with introducing the first version of SQL (Structured Query Language). The initial form of the query language from IBM was named SEQUEL and was standardized as the query language for databases. SQL (sometimes pronounced S Q L) is now a standard of both ANSI and ISO organizations.

Transact SQL or T-SQL is the version of SQL that is modified and extended by Microsoft for their product, SQL Server. It was intended to make transactions on SQL Server versions including SQL 2012 easier. Transact SQL introduces extensions in several areas of the standard SQL language. Significant changes introduced help creating procedural programs like stored procedures. SQL by itself is unable to do that. If one were able to write procedural pieces, these extensions will help writing procedural code blocks.

The changes required cover conditional program control statements (if..then blocks), introduction of local variables, string operations and other support functions. These include math functions, data-related functions, etc. Some changes made to the “Update” and “Delete” commands are also parts of these extensions in Transact SQL. TSQL also introduces a bulk insert command (similar to BCP) that helps insert large amounts of data. The modifications made in SQL Server 2012 T-SQL for program control introduces program structures that provide the equivalent of if-then-else like structure, loop control structure like while loop. A “continue” construct helps continue a loop. Other controls include return, wait for a time function, begin and end of a code block, etc. Useful operations like setting initial values to local variables, etc. are available too in transact SQL.


There are also updates to the “delete” and “update” commands in Server 2012 T-SQL is similar. Both have a variation that helps utilize a table “join” functions. A “from” clause can be used with both the commands, and tables joined prior to doing a delete or an update. The “Bulk Insert” helps speed up situations where insertion of date has to be done on many rows. Rather than issuing an “insert “command for every row, you could utilize this bulk form of insert provided in transact SQL to read off data from a file and insert date in consecutive rows.

Detailed discussions on Transact SQL can be found at TechNet site provided by Microsoft. Transact SQL is the means of communicating with Microsoft’s DBMS SQL Server. Applications will need to issue the Transact SQL statements to the database server to interact with the underlying data. There is a whole range of applications that may need to interact with databases through this Server 2012 T-SQL language. When interacting with the database through an application, one needs to take into account a range of issues. For details, you would need to look up documentation available from Microsoft. But, briefly, following are the issues to be taken into account when utilizing transact SQL with your applications. These are:

  • Transact SQL syntax details
  • Query tools and APIs
  • Process of modifying data, locking of records and managing transactions
  • How to handle distributed queries
  • Tutorial on Transact SQL scripts

 

 

Delete all the user tables in SQL Server 2012

At times we need to delete all the user tables in a SQL Server 2012 database. Why do we need to do that? This may happen if:

  • The data is used for analysis or reporting
  • You are reloading tables every night via a batch
  • There could be application development or testing
  • We have a need to delete objects for a certain schema

 

So this means we have to keep the database intact, just need to drop the tables. There may be other objects like stored procedures, views, user logins and even SQL jobs associated with the database. How do we do that, I mean keep the database but zap all the user tables. Well there are really two options. One is to do it the “old fashioned” way, delete a table one at a time. The other is more elegant and uses TSQL to delete all the user tables in SQL Server 2012. Since we also belong to the Lazy DBA Association, we are going to use TSQL to achieve the end result. We will be doing the following:

  1. Create a temp table
  2. Insert the list of tables into this temp table
  3. Loop through list of tables and zap them one at a time

Note; Make sure the Referential Integrity is disabled, otherwise this will not work

 

use Northwind_test

–declaration of variables

declare
@init_count int,
@rec_count int,
@total_count int,
@table_name varchar(1000),
@sql_stmt varchar(1000)

–create temp table #tmp_tables, more info in another script

create table #tmp_tables
(id int identity(1,1),
tablename varchar(50)
)

–insert into temp table #tmp_tables

insert into #tmp_tables (tablename)
SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME
FROM information_schema.tables
where TABLE_TYPE=‘base table’

set @init_count=1
select @total_count=count(*) from #tmp_tables
set @rec_count=@init_count

print @init_count
print @rec_count
print @total_count

–while loop to go through the tables

while @rec_count < @total_count

begin
select @table_name= tablename from #tmp_tables where id=@rec_count
select @sql_stmt=‘drop table ‘ + @table_name
–print @table_name
print @sql_stmt
exec (@sql_stmt)

set @rec_count=@rec_count + 1
end

drop table #tmp_tables
/* Sample output

drop table dbo.Employees
drop table dbo.Categories
drop table dbo.Customers
drop table dbo.Shippers
drop table dbo.Suppliers
drop table dbo.Orders
drop table dbo.Products
drop table dbo.Order Details
drop table dbo.CustomerCustomerDemo
drop table dbo.CustomerDemographics
drop table dbo.Region
drop table dbo.Territories
drop table dbo.EmployeeTerritories
drop table dbo.Customers_All

*/

 

Transact SQL scripts – SQL 2012 Video Tutorials

As part of the SQL 2012 Video Tutorials – Unlimited Package, you will have to access to Transact SQL (TSQL) scripts. These will help you in understanding SQL 2012 and how to work with objects in MS SQL 2012 database environment. Some of the SQL scripts are used to create objects like database, table, indexes, primary keys and foreign key constraints. Other SQL statements include the DML commands like: INSERT, SELECT, UPDATE AND DELETE. Here’s a list of SQL statements so far:

(Note: The ones with active links can be downloaded for FREE!)

Transact SQL scripts:


 

More information on this can be found on this page SQL 2012 Video Tutorials – Unlimited Package.

 

 

 

Top 5 Transact SQL Commands in SQL Server 2012

SQL Server 2012 has quite a few new Transact SQL Commands. We are going to discuss Top 5 of these SQL statements, here are they:

  1. SEQUENCE
  2. NEW Date functions
  3. New Logical functions
  4. CONCAT TSQL
  5. Format command in SQL 2012

 

SEQUENCE in MS SQL 2012

Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records. We are including some examples here that you can use with SQL Server 2012.

–By Itself

create sequence dbsequence
start with 1 increment by 5

select next value for dbsequence

Returns 1

select next value for dbsequence

Returns 6

drop sequence dbsequence

–With a table

create sequence idsequence
start with 1 increment by 3

create table Products_ext
(
id int,
Name varchar(50)
)

INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, ‘ProductItem’)

select * from Products_ext

/* If you run the above statement two types, you will get the following:-

1    ProductItem
4    ProductItem

*/

drop table Products_ext
drop sequence idsequence

Date Functions in MS SQL 2012

There are a number of new date functions in Microsoft SQL Server 2012. We are going to discuss two of the following functions:

  1. DATEFROMPARTS Function
  2. EOMONTH Function

DATEFROMPARTS function takes three integer parameters and builds a date data type out of them. Here is some SQL 2012 Transact SQL that shows you the DATEFROMPARTS date function in action:

declare
@year int,
@month int,
@day int

set @year=2012
set @month=9
set @day=28

select datefromparts(@year, @month, @day) as mydate

/*
mydate
———-
2012-09-28
*/

EOMONTH Function: Many applications need the last day of the month for a specific month. EOMONTH Function lets you do just that. Lets check out the following Sql statements:

declare @mydate datetime
set @mydate=’07/23/12′

select eomonth(@mydate)
2012-07-31

Other useful datetime functions in SQL Server 2012 are:

-TIMEFROMPARTS
-DATETIMEFROMPARTS
-SMALLDATETIMEFROMPARTS
-DATETIMEOFFSETFROMPARTS

 

New Logical functions in MS SQL 2012

Just like the Data functions, SQL Server 2012 has new features in regards to Logical functions as well. We will take a look at two of them: CHOOSE and IIF SQL functions.

CHOOSE is a simple function and returns the specified index from a list of values. Here are two examples:

Select CHOOSE(2, ‘Kash’, ‘Money’) –Money is second value
Select CHOOSE(1, ‘Kash’, ‘Money’) –Kash is first value

IIF function which is quite popular in MS Office applications is not available in SQL Server 2012. This function takes three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result. In other words, it kinda works like a case statement. Here is the TSQL syntax with examples:

–select iif(first argument, second argument, third argument)

select IIF(100>50, ‘true’, ‘false’)

Returns true

select IIF(100<50, ‘true’, ‘false’)

Returns false

CONCAT Transact SQL

When you need to combine different text values in SQL Server 2012, you can use the new feature CONCAT. This function helps you concatenate two or strings together. Here is my name with CONCAT function!!

SELECT CONCAT (‘Kash’, ‘Money’, ‘Millionaire’)

Returns KashMoneyMillionaire

–Also can be used in a table, using employees from Northwind database

select * from employees

select concat(firstname, ‘ ‘, lastname) as FullName
from employees

FORMAT command in MS SQL 2012

Using FORMAT command is especially helpful when you are working with dates in SQL Server 2012. Syntax is as follows:

FORMAT (value, format [,culture])

Lets go ahead and see the Format command in action.

SELECT
GETDATE(), –native date
FORMAT (GETDATE(), ‘d’), –without leading zero
FORMAT (GETDATE(), ‘dd/MM/yyyy’) –custom format

/*

Output for this is:

2012-08-20 10:36:59.247    8/20/2012    20/08/2012

*/

If you would like to learn more, check out our page on SQL Server 2012 Tutorial.

 

 

 

Follow us on: