SQL: Introduction

Data: Data is information, it may be in the form of text, images,audio, software, video etc.

DataBase: A database is an organized collection of data, generally stored and accessed  from a computer system.

DBMS: A DBMS is a software package designed to define, maipulate, retrive and manage data in a database.

RDBMS: RDBMS is a type of database management system that stores data in the form of related tables.

Different types of RDBMS:
1. MYSQL
2. Oracle
3. MS SQL
4. Sybase
5. MS Access
6. IBM DB2

SQL Data Types:

1. Number:
  a) Int: 4 bytes storage space. A signed 32 bit integer
  b) Decimal: 5 to 17 bytes. A decimal number containing upto 38 digits. Decimal/Numeric is fixed-precision data type, which means that all        the values in the data type range can be represented exactly with precision and scale.
  c) Numeric: Functionally equivalent to the decimal data type.
  d) Float: Float is approximate-number data type, which means that not all values in the data type range can be represented exactly.

2. String:
  a) Varchar: Variable-length, non-Unicode character data. The database collection determines which code page the data is stored using.

  b) Nvarchar: Variable-length, Unicode character data. Dependent on the database collection for comparisons.

  c) Text: Text is used for large pieces of string data. If the length of the field exceed a certain threshold, the text is stored out of row.
               Varchar is always stored in row and has a limit of 8000 characters.

Data Types in SQL:

Data type                       Storage      Range

--------------------------------------------------------------------------------------------------------------

Tinyint                            1 byte     0-255

Smallint                          2 bytes     -2^15 to 2^15-1

Int                                   4 bytes -2^31 to 2^31-1

Bigint                              8 bytes   -2^63 to 2^63-1

Bit                                  1 bit      0 or 1

Smallmoney                   4 bytes         -2^31 to 2^31-1

Money                            8 bytes     -2^63 to 2^63-1

Float (n value 1-24)       4 bytes             7 digits

Float (n value 25-53)     8 bytes             15 digits

Real                               4 bytes         

Datetime                        8 bytes from jan 1, 1753 to dec 31, 1999

Smalldatetime               4 bytes from jan 1, 1900 to jun 6, 2079

Char                               n char string of fixed length and max.length of 8000 chars.

Varchar                          n variable width character string and max 8000 chars.

Nvarchar 2*n unicode char string of variable length and max is 4000 chars

Text                                n char string of variable length and max is 2^31-1 chars.

Decimal and Numeric:

decimal[(p[,s])]: Numeric data type with fixed precision and scale.

numeric[(p[,s])]

                    Precision                          storage

                   --------------                             --------------

                     1-9                               5

                     10-19                             9

                     20-28                            13

                     29-38                            17

XML: XML data type can store either a complete xml document or a fragment of xml. Before xml data type the xml data was stored  in varchar or text data type, that was proven to be poor in terms of querying and manipulating the XML data.

When to use XML data type:
1. If data structure is semi-structured or unstructured or unknown.
2. When you want to create platform-independent model.
3. If data represents containment hierarchy or nested hierarchy.
4. If you want to query into the data or update parts of it, based on its structure.



Where the sql files are stored:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

MDF and LDF:

The extension file .MDF stands for “Master Database File.” This file contains all the startup information for the database to run and tracks all the databases on the SQL server. It also points to the other files in the database. This file is also a key file in storing information that is very important in admittance and supervision of data content on the server.

LDF is the file extension for the server transaction log for the main data file. It saves the database information in addition to keeping a record
of all the actions and changes made on the information on the server. This includes; date, time, details of all changes, user information regarding
who made the changes. In addition, the log also features the computer terminal where the changes were made.



Port number:

TCP 1433:
TCP port 1433 is the default port for SQL Server. This port is also the official Internet Assigned Number Authority (IANA) socket number
for SQL Server. Client systems use TCP 1433 to connect to the database engine; SQL Server Management Studio (SSMS) uses the port
to manage SQL Server instances across the network. You can reconfigure SQL Server to listen on a different port, but 1433 is by far the
most common implementation.



SQL Commands:

DML:

Data Manipulation Language is used to store, retrieve, modify, delete and update data in database.

Commands:

SELECT - Retrieve data from a table.

INSERT - Insert data into a table.

UPDATE - Update existing data in a table.

DELETE - Delete data from a table.

DDL:

Data Definition Language is used to create and modify the structure of database objects in database.

Commands:

CREATE - Create objects in a database.

ALTER - Alter objects of the database.

DROP - Delete objects of the database.

TRUNCATE - Delete all the data from the table and resets table identity to initial value.

DCL:

Data Control Language is used to create roles, grant and revoke permissions and referential integrity in database

Commands:

GRANT - Gives permissions to users to access and modify the data in a database

REVOKE - Withdraws user permissions to database given with grant command.

TCL:

Transaction Control Language is used to manage different transactions occurring within a database

Commands:

COMMIT - Saves work done in transactions.

ROLLBACK - Restores database to original state since the last commit command in transactions.

SAVE TRANSACTION - Sets a savepoint within a transaction.