How to find SQL Server Version,Edition,Server Name?

SQL Server provides a System Defined function SERVERPROPERTY(propertyname) .

By using this function you can find a number of things

Property Name	Description	syntax
Edition	Return SQL Server edition installed on machine.	select ServerProperty('edition')
EditionID	return Edition ID	select ServerProperty('editionid')
InstanceName	Return instance name if it is not default.In case of default return Null.	select ServerProperty('InstanceName')
ProductVersion	return Product version	select ServerProperty('ProductVersion')
BuildClrVersion	return version of the .NET framework Common Language Runtime (CLR)	select ServerProperty('BuildClrVersion')
EngineEdition	return 1 = Desktop 2 = Standard 3 = Enterprise 4 = Express 5 = SQL Azure	select ServerProperty('EngineEdition')
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, or an error.	select ServerProperty('IsClustered')
MachineName	Return machine name	select ServerProperty('MachineName')
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated	select ServerProperty('ResourceLastUpdateDateTime')
ProductLevel	Returns Level of the version of SQL Server instance 'RTM' = Original release version 'SPn' = Service pack version 'CTP', = Community Technology Preview version	select ServerProperty('ProductLevel')

SQL Views

          A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

The Syntax to create a sql view is

CREATE VIEW view_name 
AS 
SELECT column_list 
FROM table_name [WHERE condition]; 

• view_name is the name of the VIEW.
• The SELECT statement is used to define the columns and rows that you want to display in the view.

For Example: to create a view on the product table the sql query would be like

CREATE VIEW view_product 
AS 
SELECT product_id, product_name 
FROM product; 

SQL Joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

The Syntax for joining two tables is:

SELECT col1, col2, col3...
FROM table_name1, table_name2 
WHERE table_name1.col2 = table_name2.col1; 

If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Lets use the below two tables to explain the sql join conditions.

database table "product";

product_id	product_name	supplier_name	unit_price
100	Camera	Nikon	300
101	Television	Onida	100
102	Refrigerator	Vediocon	150
103	Ipod	Apple	75
104	Mobile	Nokia	50

database table "order_items";

order_id	product_id	total_units	customer
5100	104	30	Infosys
5101	102	5	Satyam
5102	103	25	Wipro
5103	101	10	TCS

SQL Joins can be classified into Equi join and Non Equi join.

1) SQL Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.

For example: You can get the information about a customer who purchased a product and the quantity of product.

2) SQL Non equi joins

It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=

1) SQL Equi Joins:

An equi-join is further classified into two categories: 
a) SQL Inner Join 
b) SQL Outer Join 

a) SQL Inner Join:

All the rows returned by the sql query satisfy the sql join condition specified.

For example: If you want to display the product information for each order the query will be as given below. Since you are retrieving the data from two tables, you need to identify the common column between these two tables, which is theproduct_id.

The query for this type of sql joins would be like,

SELECT order_id, product_name, unit_price, supplier_name, total_units 
FROM product, order_items 
WHERE order_items.product_id = product.product_id;

The columns must be referenced by the table name in the join condition, because product_id is a column in both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT statement.

The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number of tables involved. The rule must be true to avoid Cartesian product.

We can also use aliases to reference the column name, then the above query would be like,

SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units 
FROM product p, order_items o 
WHERE o.product_id = p.product_id; 

b) SQL Outer Join:

This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only.

The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join".

If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below:

SELECT p.product_id, p.product_name, o.order_id, o.total_units 
FROM order_items o, product p 
WHERE o.product_id (+) = p.product_id; 

The output would be like,

product_id	product_name	order_id	total_units
-------------	-------------	-------------	-------------
100	Camera
101	Television	5103	10
102	Refrigerator	5101	5
103	Ipod	5102	25
104	Mobile	5100	30

NOTE:If the (+) operator is used in the left side of the join condition it is equivalent to left outer join. If used on the right side of the join condition it is equivalent to right outer join.

SQL Self Join:

A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.

The below query is an example of a self join,

SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name 
FROM sales_person a, sales_person b 
WHERE a.manager_id = b.sales_person_id; 

2) SQL Non Equi Join:

A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, >

For example: If you want to find the names of students who are not studying either Economics, the sql query would be like, (lets use student_details table defined earlier.)

SELECT first_name, last_name, subject 
FROM student_details 
WHERE subject != 'Economics' 

The output would be something like,

first_name	last_name	subject
-------------	-------------	-------------
Anajali	Bhagwat	Maths
Shekar	Gowda	Maths
Rahul	Sharma	Science
Stephen	Fleming	Science

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key, Not Null, Unique, Check.

Constraints can be defined in two ways 
1) The constraints can be specified immediately after the column definition. This is called column-level definition. 
2) The constraints can be specified after all the columns are defined. This is called table-level definition. 

1) SQL Primary key:

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

column name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

• column_name1, column_name2 are the names of the columns which define the primary Key.
• The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be like.

Primary Key at column level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
);

or

CREATE TABLE employee
( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

Primary Key at column level:

CREATE TABLE employee 
( id number(5), 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);

Primary Key at table level:

CREATE TABLE employee 
( id number(5), NOT NULL,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)
);

2) SQL Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items". 

Foreign Key at column level:

CREATE TABLE product 
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, 
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

Foreign Key at table level:

CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10) 
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10) 
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE 
);

or

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE 
);

Unique Key at table level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location) 
);

5) SQL Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.

Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the query would be like

Check Constraint at column level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1) CHECK (gender in ('M','F')), 
salary number(10), 
location char(10) 
); 

Check Constraint at table level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1), 
salary number(10), 
location char(10), 
CONSTRAINT gender_ck CHECK (gender in ('M','F')) 
); 

SQL ALTER TABLE Statement

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.

1) Add, drop, modify table columns 
2) Add and drop constraints 
3) Enable and Disable constraints 

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee MODIFY salary number(15,2);

SQL CREATE TABLE Statement

The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.

The Syntax for the CREATE TABLE Statement is:

CREATE TABLE table_name 
(column_name1 datatype, 
column_name2 datatype, 
... column_nameN datatype 
);

• table_name - is the name of the table.
• column_name1, column_name2.... - is the name of the columns
• datatype - is the datatype for the column like char, date, number etc.

For Example: If you want to create the employee table, the statement would be like,

CREATE TABLE employee 
( id number(5), 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
);

In Oracle database, the datatype for an integer column is represented as "number". In Sybase it is represented as "int".

Oracle provides another way of creating a table.

CREATE TABLE temp_employee 
SELECT * FROM employee 

In the above statement, temp_employee table is created with the same number of columns and datatype as employee table.