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 a Database – Just an Overview

What a database is simply a repository of data. Data are pieces of information that is important for some purpose; business or otherwise. Typically, you would need to add to the repository, pieces of data may need to be updated/ modified. When a piece of data has outlived its purpose, users would like to delete the same. A software system that lets you manage data and meet your needs as described is a Database. Examples of databases we are familiar with would be,

  • Phone directory
  • Phone book on your cellular phone
  • Information about all your employees
  • Hospital records

RDMS or relational databases are in popular use for storing such data in an enterprise. These use an organization known as tables. These tables themselves are organized in a manner familiar to all of us as rows of information that has columns to store related pieces of data for that row. Tabular organization helps searching flexibly. Relational databases allow relating multiple tables together for the data finding and manipulation purposes. This is something we do at personal levels too. Our telephone book is a prime example of this organization. A name with first name and second name, telephone number, possibly the address goes into such a phone book. Often important dates like birthday, wedding anniversary and other information are included. One such entry will be a record in a particular database row. Each of the items will go into a column that is common to all the records. Some well-known and popular databases are Oracle, SQL Server and MySQL.

Sometimes the database may be organized in a distributed manner. The data are placed at different physical locations and parts of the database may have copies at different spots on the network.

An associated concept to a database is a database management system. These database products let you store multiple databases and have very large capacities to do so. In a typical business, you may need to store data about customers, sales made, details of your inventory and so on. There is a need for a management process to let the diverse databases work properly. The DBMS or the database management system lets read or write access in a disciplined manner. Authorization of users is managed by the system. Analysis of usage and performance are also provided. Then, of course necessary types of reports are usually provided by these database management systems.

Database and database managers are available on the full spectrum of systems; from mainframes to personal computers and everything in between. Major difference is in the capability of the amount of data that could be handled by the different versions of each product. In all these incarnations of the database products, it is the relational database that is currently used predominantly. All the products use a form of a query language that has become an ANSI as well as ISO standard. This is known as Structured Query language or SQL. This language lets the users specify queries to find data, manipulate or update them. It has commands for changing the structure of the database too.

SQL Server Enterprise – The most powerful edition of SQL Server

The Microsoft RDBMS product SQL Server is available in several editions. The most common ones are the Express, Standard and the Enterprise editions. Standard versions of each release will have more features and capabilities than the Express version. SQL Server Enterprise edition, in turn, has more capability than the standard editions of each release. Depending on the release version of the product, there may be additional versions.

For example, the 2012 release has a business intelligence edition. We shall concentrate on this latest product here, the SQL Server 2012 Enterprise version. While the Express edition is for familiarization, the SQL Server Enterprise is for serous applications like implementing high capability data centers with extensive database and business intelligence solutions.



Some of the notable features of the SQL Server Enterprise edition 2012 include the following:

  • Data quality services that help profile, clean up and match data ensuring the quality of the data going into the database is dependable
  • Power View or a means for data visualization and exploration  by users conveniently
  • Distributed replay features helps testing applications, configurations and upgrades. This can be very handy in SQL 2012  Enterprise adoption in the organization. A range of free tools are available so that users can be sure of a hassle-free  transition to the new version
  • Enhanced audit facility helps flexibility in auditing, and making audit possible in all versions of the product. The audit trail thus created in SQL Server Enterprise helps ensure all activity by authorized people can be tracked
  • SQL Server Enterprise has an AlwaysOn feature that manages higher uptime, quickest possible fail-over and utilization of hardware. The SQL Server AlwaysOn feature has been newly introduced and ensures higher availability and business continuity in case of disasters
  • xVelocity features in the SQL Server 2012 Enterprise help in a significant rise in performance in the intended applications of business intelligence and warehousing. xVelocity supports in-memory data processing technologies and can boost performance by a factor of 10 to 100 times.

The huge amounts of data that gets generated today needs to be managed and stored. One never knows what kind of insight is available from the big data collection. Besides the efficient storage and management implemented in SQL Server Enterprise edition, it ensures the ability to view the data to discern any valuable patterns. An additional tool of Power Pivot in the Power View feature of the SQL Enterprise version helps users to access and combine data from other sources to derive meaning to data visualizations being made. Support of cleansing of data helps the IT department to ensure BI solutions that are dependable. There will be no need to drill down to low-level data to ensure they are good data.

Whenever a new version of a product arrives on the scene, a question that arises without fail is whether existing users should upgrade. ROI percentage and the time it takes to recoup the investment are good metrics to help you decide. According to a Forrester study commissioned by Microsoft for an upgrade to SQL Server Enterprise 2012 has indicated that ROI percentage is 189%, and the payback time is just one year. In order to learn more, check out SQL Server 2012 Training.

What’s new with Microsoft SQL Server 2008 r2

Microsoft released their product SQL Server 2008 r2 in 2010. As it is usual with their products, MS SQL Server 2008 R2 also has an Express edition that is available for evaluation and use in small systems. Microsoft SQL Server 2008 r2 is available in Standard and Enterprise editions. Having tried out the SQL Server 2008 r2 Express, you would eventually graduate to either the standard or the Enterprise editions depending on your actual needs.  Two other special editions also were released with the MS SQL Server 2008 R2. These are the SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These two special editions were introduced to meet increasing demands for datacenters and parallel data warehouse applications (Business Intelligence applications).

As with other versions, Microsoft SQL Server 2008 R2 Express edition is supplied free by Microsoft.  This facilitates your team becoming familiar with the database product, get used to developing web based and desktop applications, etc. You will need the help of Microsoft Visual Studio and Visual Web Developer Express.

Some features added over the MS SQL Server 2008 R2 include the following:

  • A Master Data Services that helps in master data management
  • Multi Server Management, this is a feature that helps manage multiple instances of the server
  • Relational databases, reporting services, analysis and integration services are also managed by the multi server management feature



For getting started with the 2008 R2 you need to download the Express edition first. It is fairly simple to download and install. It is free to use, but you will need to get the product registered with Microsoft. Your team can start getting familiar with it right away. When the team gets into applications development activities, there is another issue you need to take into account. All such database applications need to have the database as part of the application and then the question is if you can re-distribute the database to a third party (your customer). This too is free for the Express edition, but you will have to apply and get the right approved by Microsoft.

The so-called core editions of Enterprise and Standard are the most popular ones of the SQL Server 2008 R2 product.  The SQL Server 2008 R2 Standard is positioned as the database tool for data management as well as business intelligence for departments and also the small and medium business segment. This configuration is supposed to take up minimum of IT resources. Some new features in the Standard edition are:

  • Backups could now be compressed significantly for faster backups
  • The SQL Server 2008 R2 could become a managed instance in applications that use multiple servers and need to manage them
  • Support for processing of complex events

The SQL Server 2008 R2 Enterprise is capable of delivering a data platform but provides the user a secure, highly available and scalable product. Business intelligence features are supported. Some new features in the Enterprise edition include:

  • Complex event processing support
  • Mater data service to make sure of data consistency
  • Support for SharePoint collaborative work
  • Up to 25 server instances could be managed with the available multi-server management

The Microsoft SQL 2008 R2 also offers a free Compact edition for applications on mobile devices besides desktop and the web.

Additional Resources on SQL 2008

  1. Sql server 2008 Tutorial
  2. Download SQL Server 2008 Management Studio
  3. Related info on MS SQL 2008

 

Database Management Software – An Overview

In simple terms, database management software is a set of software tools that help manage databases. To understand other functions of database management software, one needs to look at what databases are and what they do?  Databases are repositories of data. Business and other applications access this data, manipulate/modify it and store it back or may even delete the data records. How the data is organized internally affects how effectively and efficiently data could be found and manipulated. This structure or the arrangement of data inside a database has evolved over time. From hierarchical and networked models, most database today are relational. Data in these relational databases are organized in a tabular fashion. Database management software will make sure such structures are maintained properly. This organization is much like the spreadsheet kind of arrangement like Microsoft Excel. Each row has information or data items that are related to the information contained in the row or record. Most of us are familiar with phone books and they are typically organized in a table. One row of the table is dedicated to details of contact about a particular person. The items in the columns or fields are attributes or information/ data elements related to that person.  Names (may be split as first and last names) and phone number would be the minimum amount of details.  Work phone, home phone, mobile phone and any number of other details (birthday, and other anniversaries and so on) could be held in such a tabular arrangement.



Some examples of info in a database are:

  • Employee information
  • Flight data information
  • Movie ticket booking
  • On-line shopping support

Why do we need database management software then? Well it is apparent from the prior discussion, you may need to create different kinds of tables, modify its structure sometimes or remove the table entirely. With the relational model it is possible to have multiple tables in a database where another table may have related data. Any organization will need many types of data to be organized in unique databases. Database management software helps with creation of all kinds of database you need.

A DBMS or database management system is a collection of software that helps creation of these databases. The database management software system also implements a query language. The query language lets the user search for relevant data specified through the query language. With relational databases this query language is the SQL or structured query language and is supported by all the relational database management software systems (RDBMSs). Though supposedly standard, each manufacturer has a little twist to the language support through their DB management systems. Essential these are some proprietary extensions to the core functions of the operations defined in the SQL standard. When these data searching and manipulations are carried, one needs records of what operations and changes were done. Thus report generation is an important function of the database management software systems. Often the reporting functions are supported by graphical charting functions to help add meaning to the reported information easily. On-screen viewing and printing support are other functions provided. One important set of features help configure the database management software system and tweak parameters to fine-tune performance. Some examples of RDBMS are Oracle, SQL Server, DB2 and MySQL. A lot of housekeeping work including backup, restore and disaster recovery, etc. are often included in database management systems.

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:

 

What is ODBC – An Introduction and overview

ODBC is the abbreviation of “Open Database Connectivity”. It is a standard that specifies how an application using data from database will interact with a database. ODBC is a standard adopted by the IT industry. The standard specifies the commands that can be issued irrespective of the actual database product. How database products (both RDBMS and non relational ones) respond to requests for finding data, modifying or editing them as also how the requests are to be made vary from product to product. Open Database Connectivity strives to make the requests from applications the same irrespective of which database product serves the data. Databases, on their part, interpret these requests and process data according to their own internal processes (requests and command syntax) to respond the same way irrespective of its make.

With this kind of standardization, it is possible for any application to write front end that issues the standard requests to databases. This is the front end part of the ODBC standardization/ specification. This makes it possible for any application to issue data requests that will be recognized any ODBC compliant database. In fact, the application with this kind of Open Database Connectivity compliant front end would be transparent to what database is going to supply the data requested by it. The database could be any ODBC compliant product, working on any computer on the network and on any operating environment. Like the application front end needs special code to transform the requests from the application to standardized requests, the database needs a driver that will recognize the Open Database Connectivity requests and transform them to commands in specific syntax and sequence required by the given database. These drivers are, obviously, specific to a database product of a particular company. This driver is installed with the client application, the database could be anywhere on the network. The chain of modules for getting ODBC compliant work done is as follows:

  • Application issues requests to the front-end
  • Front-end (part of ODBC requirements) issues standardized requests
  • Open Database Connectivity driver specific to a database product, issues commands as needed by the product



Database serves up data requested and changed made by application up the same chain. After this ODBC standards have been complied with creating an application with a database, removing the parts and replacing them with another is a simple affair. As long as the standard connections through the front end and the Open Database Connectivity driver are maintained, substituting the database with another or an application with another is as simple as changing the tire in a car. It does not matter if the tire is from Bridgestone, Michelin or something else as long as the dimensions (specs of database) of the tire are similar. One could write monolithic application that has the database as a part of it. The application communicates with the database in its native protocol. This will be alright if there was no need for any change during the lifetime of the application. If there is a change needed, you will need to re-write the application all over again. The flexibility arising from ODBC standardization will be lost entirely.

Related links to ODBC

  1. Open Database Connectivity – MSDN
  2. ODBC – A Technical overview
  3. SQL Server 2012 training
  4. What is an ODBC compliant database

 

SQL Server Management Studio 2012 – What is it?

SQL Server Management Studio or SSMS is a product that is a part of Microsoft’s SQL Server 2012 product and has been available from the time the SQL Server 2005 came out. This is a component that helps manage all the parts of the SQL Server. SQL Server Management Studio 2012 can do the following for all the software modules in the server:

  • Configuring
  • Administering
  • Managing

SSMS is a crucial product and a critical piece in managing the database product; it is necessary to become familiar with the product first. Starting with the SQL Server Management Studio Express studio then seems to be a prudent approach. Getting the express version of the SQL Server, which includes the Management Studio could be downloaded from the Microsoft site. Installing the product should not create any problems. You should invest in a regular version after your team is comfortable with the SQL Server Management Studio 2012.

The SQL server Management Studio is like the control center for the product and helps you manage the features of the major modules of the product. MS has their products available in different configurations. The express version is usually a free product that helps users get familiar with the tool before plunging in into larger configurations that are priced. The SQL Management Studio 2012 product download module includes a complete tutorial. The first step for familiarization should be to study this guide. You are provided an access to the user community. This can help with any queries you may have regarding the product, its installation and configuration.

The SQL Management Studio provides the following features that will help manage the server:

  • A minimal database engine
  • An integrated environment to let you work with the product, and any scripting necessary
  • Data analysis services
  • Reporting services

The Microsoft SSMS 2012 provides an activity monitor. It is possible to schedule actions at different times and to have multiple tool open at the same time. The SQL Server Management Studio can monitor these activities. If you needed an uncluttered and reduced higher level view, it is possible to apply filters.

The SQL Management Studio has an object explorer that can provide access to all types of objects within the server environment. Access to the SQL server agent for administering them in a replicated environment, filtering of schema, name or date is available. The SSMS will let you filter parts of these objects too. Objects inside the database could be populated asynchronously. If necessary, the objects could be filtered with the metadata associated to the objects.

Scripting capabilities keep getting updated with the server version releases. This is supported with templates for quick development; script editing could be off-line, even when disconnected from the server and a source control tool for managing the script sources.

SQL Server Management Studio capabilities could be extended, given the way it is built. However, the Microsoft does not promise any support for extensions users may build. No official documents that may help in such developments are also published. This policy is so strict that when you seek customer support, you need to remove any such extensions if you have any installed.

SQL Server Management Studio – Related Links

 

 

Follow us on: