SQL : Structured Query Language

SQL is a standard language for storing, manipulating and retrieving data in databases

SQL is an ANSI(American National Standards Institute) standard

Database:

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects

Database:

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects.

DBMS:A database management system (DBMS) is a software  that interacts with the user, other applications, and the database itself to capture and analyse data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.

RDBMS:

 RDBMS (Relational database management system) is a database management system (DBMS) that is based on the relational models invented by E. F. Codd.

RDBMS is the basic for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, My SQL and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Note: SQL keywords are not case sensitive. Update is the same as UPDATE.

Importance of semicolon after SQL statements:

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statements to be executed in the same call to the server.

Some Important SQL Commands:

•          SELECT – Extracts data from a database
•          UPDATE – Updates data in a database
•          DELETE – Deletes data from a database
•          INSERT INTO – Inserts new data into a database
•          CREATE DATABASE – Creates a new database
•          ALTER DATABASE – Modifies a database
•          CREATE TABLE – Creates a new table
•          ALTER TABLE – Modifies a table
•          DROP TABLE – Deletes a table
•          CREATE INDEX – Creates an Index (Search Key)
•          DROP INDEX – Deletes an Index

           

MS SQL Server:

SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment.

• It is a software, developed by Microsoft, which is implemented from the specification of RDBMS.
• MS SQL Server is highly scalable.
• MS SQL Server is platform independent.
• MS SQL Server can be run on a single laptop or a network of cloud servers.
• MS SQL Server is both a GUI and command based software.
• MS SQL Server supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language.

Usage of MS SQL Server:

• To create databases.
• To maintain databases.
• To analyze the data through SQL Server Analysis Services (SSAS).
• To generate reports through SQL Server Reporting Services (SSRS).
• To carry out ETL operations through SQL Server Integration Services (SSIS).

Versions of MS SQL Server:

Version	Year	Code Name	SQL Server Name
6.0	1995	SQL95	SQL Server 6.0
6.5	1996	Hydra	SQL Server 6.5
7.0	1998	Sphinx	SQL Server 7.0
8.0	2000	Shiloh	SQL Server 2000
9.0	2005	Yukon	SQL Server 2005
10.0	2008	Katmai	SQL Server 2008
10.50	2010	Kilimanjaro	SQL Server 2008R2
11.0	2012	Denali	SQL Server 2012
12.0	2014	Hekaton (initially), SQL 14 (current)	SQL Server 2014
13.0	2016	Helsinki	SQL Server 2016
14.0	2017	vNext	SQL Server 2017

MS SQL Server Data Types:  

Data types can be divided in three types:

• String Types
• Number Types
• Date Types

String Types

char(n): It is a fixed width character string. It can store maximum 8,000 characters

varchar(n): It is a variable width character string. It can store maximum 8,000 characters

varchar(max): It is a variable width character string. It can store maximum 1,073,741,824 characters.

Text: It is a variable width character string. It can store maximum 2GB of text data.

Bit: It allows 0, 1, or NULL values.

binary(n): It is a fixed width binary string. It can store maximum 8,000 bytes

image: It is a variable width binary string. It can store maximum 2GB.

Number Types

Tinyint: allows whole numbers from 0 to 255

Smallint: allows whole numbers between -32,768 and 32,767

Int: allows whole numbers between -2,147,483,648 and 2,147,483,647

Bigint: allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

decimal(p,s): fixed precision and scale numbers.allows numbers from -10^38 +1 to 10^38 ?1.the p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. default is 18.the s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. default value is 0

numeric(p,s): fixed precision and scale numbers.allows numbers from -10^38 +1 to 10^38 ?1.the p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. default is 18.the s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. default value is 0

smallmoney: monetary data from -214,748.3648 to 214,748.3647

money: monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807

float(n): floating precision number data from -1.79e + 308 to 1.79e + 308.the n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. default value of n is 53.

Real: floating precision number data from -3.40e + 38 to 3.40e + 38

Date Types

Datetime: It specifies a date and time from january 1, 1753 to december 31, 9999 with an accuracy of 3.33 milliseconds.

datetime2: It specifies a date and time from january 1, 0001 to december 31, 9999 with an accuracy of 100 nanoseconds.

Smalldatetime: It specifies a date and time from january 1, 1900 to june 6, 2079 with an accuracy of 1 minute.

Date: It is used to store a date only. It specifies a date from january 1, 0001 to december 31, 9999.

Time: It is used to store a time only to an accuracy of 100 nanoseconds.

Datetimeoffset: It is the same as datetime2 with the addition of a time zone offset.

Timestamp: It stores a unique number that gets updated every time a row gets created or modified. the timestamp value is based upon an internal clock and does not correspond to real time. each table may have only one timestamp variable.

Editions of SQL Server :

Depends on version Microsoft released different types of editions.

SQL Server offers the right edition to meet your needs, including Enterprise for mission critical applications, enterprise business intelligence and data warehousing; Business Intelligence for self-service and corporate business intelligence; and Standard for basic database, reporting and analytics capabilities.

In SQL Server 2012 there are several types of editions are released.

•          Enterprise Edition
•          Standard Edition.
•          Business Edition.
•          Express Edition.
•          Web and Developer Editions.

Enterprise Edition:

SQL Server 2012 Enterprise Edition is the high end of the SQL Server 2012 product line up. It supports the maximum number of cores and RAM in the host OS and provides the complete SQL Server feature set, including support for all of the advanced availability and BI features.

The Enterprise edition supports up to 16-node AlwaysOn Failover Clusters as well as AlwaysOn Availability Groups, online operations, PowerPivot, Power View, Master Data Services, advanced auditing, transparent data encryption, the Column Store index, and more. The Enterprise edition is licensed per core.

Standard Edition:

SQL Server 2012 Standard Edition is limited to 16 cores and 64GB of RAM. It provides the core relational database engine and basic business intelligence (BI) capabilities. It doesn't include support for the advanced availability features or the more powerful BI features such as PowerPivot, Power View, and Master Data Services. The Standard Edition does include support for two-node AlwaysOn Failover Clusters, and it's licensed either per core or per server.

Business Edition:

SQL Server 2012 Business Intelligence Edition is a new member of the SQL Server family. Like the Standard edition, the Business Intelligence edition is limited to 16 cores for the database engine and 64GB of RAM. However, it can use the maximum number of cores supported by the OS for Analysis Services and Reporting Services. The Business Intelligence edition includes all of the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for the advanced availability features like AlwaysOn Availability Groups and other online operations. The Business Intelligence edition supports two-node AlwaysOn Failover Clusters, and it's licensed per server.

Express Edition:

SQL Server 2012 will continue to offer three versions of the free SQL Server Express Edition: Express (Database Only), Express with Tools, and Express with Advanced Services. Microsoft will also continue to offer a download of SQL Server Management Studio Express. The Express editions are limited to support for one CPU and 1GB of RAM. Databases are limited to 10GB per database. In addition, a new option called LocalDB will also be available.

Web and Developer Edition:

SQL Server 2012 Web Edition and SQL Server 2012 Developer Edition will continue to be part of the SQL Server 2012 family. The Developer edition provides the same feature set as the Enterprise edition. However, it's licensed per developer and can't be used for production work. The Web edition is licensed only to hosting companies with a Services Provider License Agreement (SLPA).

You can refer my another blog too uftinformation.blogspot.in

Thanks for reading….😊

-By Vikasini