Bigginer SQL Server Tutorial

SQL SERVER

About:
 Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).

Introduction:
SQL stands for “Structured Query Language” and can be pronounced as “SQL” or “sequel – (Structured English Query Language)”. It is a query language used for accessing and modifying information in the database. IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS). Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc. Most of these have provided their own implementation thus enhancing it's feature and making it a powerful tool. Few of the sql commands used in sql programming are SELECT Statement, UPDATE Statement, INSERT INTO Statement, DELETE Statement, WHERE Clause, ORDER BY Clause, GROUP BY Clause, ORDER Clause, Joins, Views, GROUP Functions, Indexes etc.

In a simple manner, SQL is a non-procedural, English-like language that processes data in groups of records rather than one record at a time. Few functions of SQL are:

• store data
• modify data
• retrieve data
• modify data
• delete data
• create tables and other database objects
• delete data

Sql Server Topics:

1.      SQL Statements

a.      Create Database

Syntax: Create Database DatabaseName eg:  Create Database MyProject To Use Database write the following code, Use Database DatabaseName

b.      Create Table

Syntax: CREATE TABLE table_name (column_name1 datatype, column_name2 datatype, ... column_nameN datatype ) eg: CREATE TABLE employee ( id number(5), name char(20), dept char(10), age number(2), salary number(10), location char(10) )

c.       Insert Statement

Syntax: INSERT INTO TABLE_NAME (col1, col2, col3,...colN) VALUES (value1, value2, value3,...valueN); eg: INSERT INTO employee (id, name, dept, age, salary location) VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);

d.      Select Statement

Syntax: SELECT * FROM Table_Name eg: SELECT * FROM employee (here * refers to all columns of a particular table)

e.      Update Statement

Syntax: UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... [WHERE condition] eg: 1. UPDATE employee SET location ='Mysore' WHERE id = 101; 2. UPDATE employee SET salary = salary + (salary * 0.2);

f.        Delete Statement

The DELETE Statement is used to delete rows from a table.

Syntax: DELETE FROM table_name [WHERE condition]; eg: 1. To delete an employee with id 100 from the employee table, the sql delete query would be like, DELETE FROM employee WHERE id = 100; 2.To delete all the rows from the employee table, the query would be like, DELETE FROM employee;

g.      Truncate Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax: TRUNCATE TABLE table_name; eg:  To delete all the rows from employee table, the query would be like, TRUNCATE TABLE employee;

Difference between DELETE and TRUNCATE Statements:
DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

h.      Drop Statement

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using DROP command. When a table is dropped all the references to the table will not be valid.

Syntax: DROP TABLE table_name eg: DROP TABLE employee