D a t a b a s e :

A database is a collection of information that is organized so that it can easily be accessed, managed and updated. Using The Database we can manage the data in a

very efficient way.

Data: Collection of Facts And Figures.

Advantages of database:

1) DATA-RETRIVEAL: With the use of Database we can retrieve the data by single line command

2) DATA-REDUNDENCY: Redundancy means modifications made in one copy will be reflected in the other copy also.

3) DATA-INTEGRITY. The DBMS can ensure that not more than one user can update the same record at the same time. It can keep duplicate records out of the database; for example, no two customers with the same customer number can be entered.

4) DATA-SECURITY:

DBMS: [Database Management System] (Database Management System) Software that controls the organization, storage, retrieval, security and integrity of data in a database. It accepts requests from the application and instructs the operating system to transfer the appropriate data. The major DBMS vendors are Oracle, IBM, Microsoft and Sybase.

RDBMS: [Relational Database Management System]:

      RDBMS stands for Relational Database Management System.
      RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM        DB2, Oracle, MySQL, 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.

SQL SERVER 2005 is a User Friendly Database. This Database is designed by Microsoft in the year 1994.

SQL server is a collection of databases; it can contain objects like Tables,

Stored Procedures, Functions, Views.

It is a very useful Database for developing .net applications

SQL server is Platform dependent it works on only windows platform.

How to open SQL Server:

1) Go to run prompt and type sqlwb [Structure Query Language Work Bench].

2) Start → AllPrograms→MicrosoftSqlserver2005→SqlserverManagmentstudio.

In the above window

Server Type: Database Engine

Server Name: “.”Or Local host

Authentication: Sql Server Authentication

Password: SA123

Authentication is of two types:

1) Windows Authentication

2) Sqlserver Authentication

Authentication: It is a process where the server checks the user credentials [LoginId and password]

Windows Authentication: Whenever the developer selects the windows Authentication Sql server depends upon the Security of operating system. By default it takes system login ID and password.

Sqlserver Authentication: In this process, the database provides its own security. Without depending upon the operating System.

In this process the developer explicitly specifies the loginid and password.

The default login id of sql server is SA

Whenever we install the sqlserver it will ask a password what ever u specify that password is used for connecting to the sqlserver

In this window left hand side, shows object explorer. It holds the Databases and some objects.

Now u can select the Databases and right click on databases.

Select New Databases and then give Database Name.

Databases are mainly 2 types

1) System Databases

2) User Databases

System Databases: These Databases are helpful for execution purpose. Whenever we install the sql server by default, the System Databases are installed.

User Databases: These Databases are developed by developers according to their Requirements.

Whenever the developer creates a new Database it will generate automatically 2 files.

1).MDF

2).LDF

.MDF: [Master Data File]

The .MDF file having original information of a database.

EG: Tables

.LDF: [Log Data File]

The LDF file having DML operations Information.

DML [Data Manipulation Language]

This Language is used for performing the some operations in a database.

The DML having 3 commands

Insert, Update, Delete

Where can we see the .mdf and .ldf files?

Program files à Microsoft sqlserver à Mssql à Data..

Data types of sql server:

Each and every language will have its own data types.

Sql server is having richest data types. These data types are very useful in accessing the proper data into a database.

Character strings: It allows Strings.
(n) no. of characters.

Data type	Description	Storage
char(n)	Fixed-length character string. Maximum 8,000 characters	n
varchar(n)	Variable-length character string. Maximum 8,000 characters
varchar(max)	Variable-length character string. Maximum 1,073,741,824 characters
text	Variable-length character string. Maximum 2GB of text data

Unicode strings:

Data type	Description	Storage
nchar(n)	Fixed-length Unicode data. Maximum 4,000 characters
nvarchar(n)	Variable-length Unicode data. Maximum 4,000 characters
nvarchar(max)	Variable-length Unicode data. Maximum 536,870,912 characters
ntext	Variable-length Unicode data. Maximum 2GB of text data

Binary types:

Data type	Description	Storage
bit	Allows 0, 1, or NULL
binary(n)	Fixed-length binary data. Maximum 8,000 bytes
varbinary(n)	Variable-length binary data. Maximum 8,000 bytes
varbinary(max)	Variable-length binary data. Maximum 2GB
image	Variable-length binary data. Maximum 2GB

Number types:
(p,s)
In this p stands for precision [Totalno.of digits in a value]
S stands for scale [The no. of Digits after Decimal Point]

Data type	Description	Storage
tinyint	Allows whole numbers from 0 to 255	1 byte
smallint	Allows whole numbers between -32,768 and 32,767	2 bytes
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
decimal(p,s)	Fixed precision and scale numbers. 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	5-17 bytes
numeric(p,s)	Fixed precision and scale numbers. 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	5-17 bytes
smallmoney	Monetary data from -214,748.3648 to 214,748.3647	4 bytes
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
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.	4 or 8 bytes
real	Floating precision number data from -3.40E + 38 to 3.40E + 38	4 bytes

Date types:

Data type	Description	Storage
datetime	From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds	8 bytes
datetime2	From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute	4 bytes
date	Store a date only. From January 1, 0001 to December 31, 9999	3 bytes
time	Store a time only to an accuracy of 100 nanoseconds	3-5 bytes
datetimeoffset	The same as datetime2 with the addition of a time zone offset	8-10 bytes
timestamp	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

Other data types:

Data type	Description
sql_variant	Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier	Stores a globally unique identifier (GUID)
xml	Stores XML formatted data. Maximum 2GB
cursor	Stores a reference to a cursor used for database operations

How to create a table in a database?

Table: The set of data arranged in rows and columns;

Row: A series of objects placed next to each other, usually in a straight line.
Column: A Line of units following one after another

Using the Create command, we can create a table in a database.

Syntax:

Create table <Table Name>

(

Column Name Data type,

Column Name Data type

)

E.g. Create table SJS

(

BranchID int,

BranchName nvarchar(50),

Location nvarchar(50),

PhoneNumber nvarchar(50)

)

Select and press F5

Verify the sjsdb folder example1

How to insert the values into a database?

Insert: This command is used for insert the records into a table.

Syntax:

Insert into <table name> values ();

Eg: insert into SJs values(1,'Khammam','Philipscomplex','645475')

How to update the values into a database?

Using the update command, we can update the values in an existing table.

Syntax:

Update table name set Column name = new value or name where id=1

Set is a key word.

Eg: update SJS set BranchName='HYD' where BranchID=1

Which statement is used for retrieving the values from a database?

Using the select statement, we can retrieve the data from a database.

Syntax:

Select * from tblname

Eg: select * from SJS

How can retrieve a single row or single value from a database?

Using the where clause we can retrieve the single value or single record in a table.

Syntax:

Select <Column Name> from <tblname> where <condition>

Eg: select * from SJS where BranchID=1

How can delete a record from a table?

Using the delete command, we can delete a record from a table

Syntax: Delete from <tblname> where condition.

Eg: Delete from SJS where BranchID=1

Which command is used to destroy the data in a table?

Using truncate command, we can destroy the data in a table.

Syntax:

Truncate table <table name>

Eg: Truncate table SJS

How can delete a table into a database?

Using the Drop command, we can delete a table in a database.

Syntax:

Drop table <table Name> Eg: Drop table SJS

What is the difference between delete and truncate commands?

Using Delete command, we can delete only one record at a time.

But

Truncate command destroys all records information at a time.

What is the purpose of Alter command?

This command is used for changing the existing table structure.

Using the Alter command, we can perform several operations in a table.

Eg: Alter table SJS add MobileNumber nvarchar(50)//add new column into a table.

Alter table SJS alter column PhoneNumber int//change the datatype nvarchar to int

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist:

 Entity Integrity

 Domain Integrity

 Referential integrity

 User-Defined Integrity

Entity Integrity: ensures that there are no duplicate rows in a table.
Domain Integrity: enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity: ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).
User-Defined Integrity: enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:

 PRIMARY KEY  UNIQUE

 FOREIGN KEY  CHECK

NOT NULL  DEFAULT

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

Eg: Create table College

(

CollegeID int constraint CollegeID_pk primary key

)

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Eg: Create table College

(

AddDetails nvarchar(50)constraint AddDetails_uq unique

)

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

Eg:

Create table College

(

TypeID int constraint TypeID_fk references tblMasterVehicleTypes(TypeID ) )

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

Eg: Create table College

(

fee money constraint fee_ck check(fee>20000)
)

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Eg: Create table College

(

Details nvarchar(50) constraint Details_nn not null

)

You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

Implementation of Foreign key:

Foreign key is also called as Referential Integrity constraint.

This Relation ship is used for establishing the connection between two tables.

Rules:

First create a Base Table

And then create child Table

Syntax:

Create table <tbname>

(

Column name Data type constraint Constraint Name,// Primary key

Column name Data type constraint constraint name

)

Child table:

Create table <tbname>

(

Column name Data type constraint Constraint name,

Colum name Data type constraint constraint name,

Reference key name Data type Columnname_fk References Table Name (column name)

)

Eg:

Primary key table (or) Base Table

Create table College

(

CollegeID int constraint CollegeID_Pk primary key,

CollegeName nvarchar (50) constraint CollegeName_nn not null

)

Foreign key table (or) Child table

Create table Students

(

Studentid int constraint Studentid_pk primary key,

StudentName nvarchar (50) constraint StudetnName_uq unique,

CollegeID int constraint CollegeID_fk references College (CollegeID))

** Once the Relation ship is establish between two tables never insert the values in foreign key column is not present in the base table.

** Can’t delete or update the foreign key values or tables.

When the situation is raise first destroy the relation ship in tables.

Default constraint:

Default constraints are a special case of column defaults.

A column default is some value or function that the column will take when an INSERT statement doesn't explicitly assign a particular value.

In other words, the column default is what the column will get as a value by default.

Eg:

create table Marks

(

StudentID int,

ClassName nvarchar(50) default 'M.C.A',

M1 int,

m2 int

)

FUNCTIONS:

SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.

Functions that take a character string input and return a character string output use the collation of the input string for the output. Functions that take no character inputs and return a character string use the default collation of the current database for the output. Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string

Advantage:

Functions can be used for retrieving the data from tables in various scenarios using the select statement

Functions are categorized into two types

§ Single Row Functions

§ Group Functions

Single Row Functions:

Single row functions are applied once for each row under a table.

Single_row_functions:

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses and HAVING clauses.

Single row Functions are divided into

§ Arithmetic Functions

§ String Functions

§ Date and time Functions

§ System Functions

§ Conversion functions

Arithmetic functions: Using the Arithmetic functions we can perform the mathematical operations.

Those are:

Sqrt (n): Returns a square root of given n

Here”n” is numeric.

Using the select statement we can utilize the functions.

Eg:

select sqrt(9)

ROUND (number, length, [function]) - Returns the number rounded to the precision specified by length. If length is positive, the digits to the right of the decimal point are rounded. If it's negative the digits to the left of the decimal point are rounded. To truncate the number rather than round it code a non zero value for function.

Eg: SELECT ROUND(123.9994, 3)

Square(number);

This function returns the square value of the given number.

Eg:

select square(3)

ABS (number) - Returns the absolute value of number.

This function Returns only positive integers of the given number.

Eg:

select abs(8)

select abs(-8)

Log(number):

This function returns the logarthmic value of the given number

Eg:

select Log(7)

** CEILING (number) - Returns the smallest integer that is greater than or equal to the number.

Eg:

select ceiling(16.8)

**5 FLOOR (number) Returns the Highest integer less than the given number

Eg:

Select Floor(16.8)

String Functions:

These functions perform an operation on a string input value and return a string or numeric value.

1) ASCII (character expression)

Returns the ASCII code value of the leftmost character of a character expression.

Eg:

select ascii('sjs')

select ascii(4)

LEN (string) - Returns the number of characters of the specified string expression, including spaces also.

Eg:

select len('welcome to sjs')

*** LTRIM (string) - LTRIM function removes the empty spaces which are present in the left hand side of the given string.

Eg:

select ltrim(' vamshi')

*** RTRIM (string) - RTRIM function removes the empty spaces which are present in the right hand side of the given string.

Eg:

select rtrim('vamshi ')

LOWER (string) - Returns the string converted to lowercase letters.

Eg:

select lower('SJS')

UPPER (string) - Returns the string converted to uppercase letters.

Eg:

SELECT UPPER('sjs')

LEFT (string, length) - Returns the specified number of characters from the beginning of the string.

Eg: select left('vamshi',4)

8) RIGTH (string, length) - Returns the specified number of characters from the end of the string.

Eg: select right('vamshi',2)

9) REPLACE (search, find, replace) - Returns the search string with all occurrences of the find string replaced with the replace string

Eg: select replace('vamsee','e','i')

10) REVERSE (string) - Returns the string with the character in reverse order.

Eg: select reverse('jobschool')

11) SUBSTRING (string, start, length) - Returns the specified number of characters from the string starting at the specified position

Eg: select substring('haihello',1,3)

12) CHARINDEX (find, search [, start]) - Returns an integer that represents the position of the first occurrence of the find string in the search string starting at the specified position. If the starting position isn't specified, the search starts at the beginning of the string. If the staring isn't found, the functions returns zero.

Eg: select charindex('j','sjs')

13) soundex(s): This function is used for comparing two words in the way they are sounded or pronounced.

Eg:

select * from SJS where soundex(Location)=soundex('Philpscomplex')

Date and Time Functions:

1 GetDate () - Returns the current system date and time in the Microsoft SQL Server standard internal format for date time values.

Eg: select getdate()

Sql server default format is mm-dd-yy.

2 GETUTCDATE() - Returns the current UTC date and time based on the system's clock and time zone setting. UTC (Universal Time Coordination) is the same as Greenwich Mean Time.

Eg: select GETUTCDATE()

3 DAY (date) - Returns the day of the month as an integer.

Eg: select day(getdate())

4 MONTH (date) - Returns the month as an integer.

Eg: select month(getdate())

5 YEAR (date) - Returns the 4-digit year as an integer.

Eg: select year(getdate())

6 DATENAME (datepart, date) - Returns an integer representing the specified date part of the specified date.

** It is a generic function this function is used for picking the any part of the date from the given date.

Eg: select datename(month,getdate())

select datename(year,getdate())

select datename(day,getdate())

7 DATEPART (datepart, date)

Is the parameter that specifies the part of the date to return? The table lists date parts and abbreviations recognized by Microsoft SQL Server.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Examples of DatePart:

select datename(dw,getdate())

select datename(qq,getdate())

select datename(mm,getdate())

select datename(wk,getdate())

select datename(hh,getdate())

select datename(ss,getdate())

select datename(ms,getdate())

8 DATEADD (datepart, number, date) - Returns the date that results from adding the specified number of datepart units to the date.

Eg: select dateadd(dd,10,getdate())

select dateadd(mm,3,getdate())

9 DATEDIFF (datepart, startdate, enddate) - Returns the number of datepart units between the specified start date and end date.

Eg: select datediff(yy,10,getdate())

10 ISDATE (expression) - Returns a value of 1(true) if the expression is a valid date/time value; returns a value of 0(false) otherwise

Eg: select Isdate(2010-11-26)

Conversion functions:

These functions are used for converting expressions from one data type to another data type.

Convertion functions are two types.

Cast(expression)

Convert(type,expression)

Eg: select convert(varchar(50),getdate())

select cast(getdate() as varchar(50)

System functions: to be used with SELECT

Function	Determinism
APP_NAME	Nondeterministic
CASE expression	Deterministic
CAST and CONVERT	Deterministic unless used with date time, smalldatetime, or sql_variant.
COALESCE	Deterministic
COLLATIONPROPERTY	Nondeterministic
CURRENT_TIMESTAMP	Nondeterministic
CURRENT_USER	Nondeterministic
DATALENGTH	Deterministic
@@ERROR	Nondeterministic
fn_helpcollations	Deterministic
fn_servershareddrives	Nondeterministic
fn_virtualfilestats	Nondeterministic
FORMATMESSAGE	Nondeterministic
GETANSINULL	Nondeterministic
HOST_ID	Nondeterministic
HOST_NAME	Nondeterministic
IDENT_CURRENT	Nondeterministic
IDENT_INCR	Nondeterministic
IDENT_SEED	Nondeterministic
@@IDENTITY	Nondeterministic
IDENTITY (Function)	Nondeterministic
ISDATE	Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and the style parameter is not equal to 0, 100, 9, or 109. Styles 0 and 100 use the default format mon dd yyyy hh:miAM (or PM). Styles 9 and 109 use the default format plus milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM).
ISNULL	Deterministic
ISNUMERIC	Deterministic
NEWID	Nondeterministic
NULLIF	Deterministic
PARSENAME	Deterministic
PERMISSIONS	Nondeterministic
@@ROWCOUNT	Nondeterministic
ROWCOUNT_BIG	Nondeterministic
SCOPE_IDENTITY	Nondeterministic
SERVERPROPERTY	Nondeterministic
SESSIONPROPERTY	Nondeterministic
SESSION_USER	Nondeterministic
STATS_DATE	Nondeterministic
SYSTEM_USER	Nondeterministic
@@TRANCOUNT	Nondeterministic
USER_NAME	Nondeterministic

GROUP Functions:

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

Eg:

App_Name (): This function shows the name of the application connected to the data base server.

Eg: select app_name()

select count(*) from SJS

SQL DISTINCT (): This function is used to select the distinct rows

Eg:

SELECT DISTINCT SJS FROM BranchName

SQL MAX (): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an employee, the query would be:

Eg: select Max(salary)from sjs

SQL MIN (): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an employee, he query would be:

Eg: select min(salary) from sjs

SQL AVG (): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

Eg: select avg(salary)from sjs

SQL SUM (): This function is used to get the sum of a numeric column

To get the total salary given out to the employees,

Eg: select sum(salary)from sjs

OPERATORS IN SQL

SQL Operators

There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected.

Comparison Operators: Comparison operators are used to compare the column data with specific values in a condition. Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

The below table describes each comparison operator.

Comparison Operators	Description
=	equal to
<>, !=	is not equal to
<	less than
>	greater than
>=	greater than or equal to
<=	less than or equal to

SQL Logical Operators: There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Logical Operators	Description
OR	For the row to be selected at least one of the conditions must be true.
AND	For a row to be selected all the specified conditions must be true.
NOT	For a row to be selected the specified condition must be false.

"OR" Logical Operator:

If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.

Example:

select BranchName,Location from sjs where BranchID=1 or BranchName='Khammam'

AND" Logical Operator:

If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.

Example:

select * from SJS where BranchID=1 and PhoneNumber=645474

"NOT" Logical Operator:

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

Example:

select * from SJS where BranchID!=1

select * from SJS where not BranchID=1

Arithemtic operators:

Using these operators we can perfom the arithemetic operations. Arithemetic operators are

Addtion[+],subtraction[-],Division[%],Multiplilcation[*].

Example:

select BranchID,BranchName,salary,Sal*12 as annualsalary from sjs

select BranchID,BranchName,Salary,Sal+1000 as Bonnus from sjs

select BranchID,BranchName,Salary,Sal-1000 as Bonnus from sjs

select BranchID,BranchName,Salary,Sal/1000 as pf from sjs

Synonyms:

Synonyms are intorduced in sqlserver2005

Synonyms are nothing but aliases of any object.

syntax: synonym name for object name

Adventages:

Whenever object names are lengthy using the aliase and create a short cut for particular object.

Hiding the actuval object name and giving the permisions to others.

Example:

create synonym cs for colleges

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.

Advantage of joins: Using of joins we can retrieve the data from multiple tables at a time.

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.

Example:

Non_Ansi format:

select cs.CollegeID,cs.CollegeName,cs.Location,s.studentID,s.StudentName from Colleges cs,Students s where cs.CollegeID=s.CollegeID

ANSI Format:

select cs.CollegeID,cs.CollegeName,cs.Location,s.studentID,s.StudentName from Colleges cs inner join Students s on cs.CollegeID=s.CollegeID

2) SQL Non equi joins

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

It is also called as teta join or range oriented join

Eg: select s.StudentID,s.StudentName,cs.CollegeName,cs.Location

from Students s,Colleges cs

where cs.CollegeID!=s.StudentID

3) Cartesian join:

Multiple tables are combined with each other without any condition called as Cartesian join.

A Cartesian join will get you a Cartesian product.
A Cartesian join is when you join every row of one table to every row of another table.
You can also get one by joining every row of a table to every row of itself.

Eg: select * from Colleges cs,Students s

4)Outer joins:

Outer joins also same as Equi join.

Equi join returns the only matching data in the tables used in the condtion.

But

Outer joins retrive the matching data and unmatched data present lefthandside or righthandside of a table.

Outer joins are mainly divided into two types

Those are:

Left outer Join

Right outer join

Left outer join:

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Symbol of left outer join: *=

Eg: select

s.studentID,s.StudentName,cs.CollegeName,cs.Location

from Students s,Colleges cs

where cs.CollegeID*=s.studentID

Right Outer join:

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Symbol of right outer join=*

Eg:

Select

s.studentID,s.StudentName,cs.CollegeName,cs.Location

from Students s,Colleges cs

where cs.CollegeID=*s.studentID

Left outer join, right outer join, and the full outer join. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

If u apply these joins in sql change the compatabilty mode of our database.

How can we change the compatabilty mode in sql?

Using the sp_dbcmptlevet ‘databasename’,80

Eg: sp_dbcmptlevel 'sjs.db',80

Full Outer join:

Use this when you want to join all rows, even if there are no matching rows in the right table.

Eg:

select

s.studentID,s.StudentName,cs.CollegeName,cs.Location

from Students s full outer join Colleges cs

where cs.CollegeID = s.CollegeID

Sql having set of clauses. Using the Clauses we can show only wanted data in tables according to requirements.

Clauses are used for filter the records in a Table.

Those are:

· Where clause

· Order By clause

· Having clause

· Group By clause.

· Where Clause: The WHERE clause is used to extract only those records that fulfill a specified criterion.

It shows only matching data which satisfy the given condition.

Syntax:

SELECT column name(s)
FROM table name
WHERE column name operator value

Eg: select * from Students where StudentID=3

ORDER BY: The ORDER BY is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.
Syntax: order by column name desc
Eg:
select * from SJS order by BranchName desc

Having Clause: Having clause is used in different scenarios.

** Where clause is used for restricting the data before grouping.

But

Having is used for restricting the data after grouping.

Where condition restrict only rows. Having restricted the groups.

Having clause is mainly used in aggregate Functions.

Eg:

select count(*)from SJS Where BranchName='Khammam'

In this query fist count the total no. of branches.

The total no. of Branches store in a buffer.

Next the where condition query is executed

And finally the result set is displayed.

Buffer: A temporary storage device.

Group by: The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Eg:

select Max(StudentName) from Students group by StudentID

Text Box:
VIEWS

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.

Once you have defined a view, you can reference it like any other table in a database.

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

The restrictions imposed on views are as follows:

· A view can be created only in the current database.

· The name of a view must follow the rules for identifiers and must not be the same as that of the base table.

· A view can be created only if there is a SELECT permission on its base table.

· A SELECT INTO statement cannot be used in view declaration statement.

· A trigger or an index cannot be defined on a view.

· The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Syntax:

CREATE VIEW view_name
[(column_name[,column_name]….)]
AS select_statement [WITH CHECK OPTION]

Eg:

create view stud as Select * from Students. // Whole table

//single record view creation//

create view stud1 as select * from Students where StudentID=1

//with check option//

Alter view jobschool as

select BranchName,Location,PhoneNumber

from SJS where BranchID=1

with check option

How to drop the View?

If you want to drop the view then you can use the following statement. When you drop the table underlying view will not be deleted. But if you run that view it will thrown an error.

Syntax: DROP VIEW VIEW_NAME

Eg: Drop view jobschool

How to alter the view?

If you want to do changes in the created views then you can alter the view whatever you want to view the same view name.

Syntax:

ALTER VIEW VIEW_NAME

SELECT [Columns List]....

Eg: Alter view jobschool as select BranchName,Location,PhoneNumber

from SJS where BranchID=1 with check option

SCHEMABINDING:

Binds the view to the schema of the underlying table or tables.

When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema. object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Restrictions:

*** Select statement should not contain * in it. Explicitly specifying column names.

*** Must and should specify the owner name before the table name.

Eg: create view ss with schemabinding

select StudentID,StudentName

from dbo.Students

Indexes

An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes; they are just used to speed up searches/queries.

Syntax:

CREATE UNIQUE INDEX index_name
ON table name (column name)

Eg:
create unique index I1 on Books (BookID)

sp_help: Using this command we can see the existing table structure.

sp_help Books: u can see the index of a Books table.

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

Syntax: DROP INDEX table_name.index_name

Eg: Drop index Books.I1

Stored Procedures:

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

A stored procedure is an already written SQL statement that is saved in the database

Advantages:

Easy of use.

Business validations also performed.

Faster Execution

To create a procedure:

In the Object Explorer, expand the database for which you want to create the procedure, expand its Programmability node, right-click Stored Procedures, and click New Stored Procedure... A query window with a skeleton syntax would be displayed.
Open an empty query window associated with the database for which you want to create the stored procedure and display the Templates Explorer. In the Templates Explorer, expand the Store Procedure node. Drag Create Stored Procedure and drop it in the query window
Open an empty query window associated with the database for which you want to create the stored procedure and enter the necessary code

Syntax: Create Procedure Procedure Name

(

@param1 data type size,

@param2 data type size

Begin statement

End

)

Eg: create procedure Usp_Insert_Books

(

@BookName nvarchar(50),

@Rack nvarchar(50)

)

begin

insert into Usp_Insert_Books(BookName,Rack)

values(@BookName,@Rack)

end

Executing a Procedure:

Syntax: Exec Procedure Name

Eg: exec Usp_Insert_Books 'c#.net',4

Deleting a Procedure:

Syntax: DROP PROCEDURE ProcedureName

Eg: Drop Procedure Usp_Insert_Books

Functions

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

To create a Function:

In the Object Explorer, expand the database for which you want to create the Functions, expand its Programmability node, select Table valued or scalar functions, right-click Functions, and click New-Inline-Table valued function
A query window with a skeleton syntax would be displayed.
Open an empty query window associated with the database for which you want to create the Function and enter the necessary code

Syntax:

CREATE [OR REPLACE] FUNCTION function name [parameters]

RETURN return_datatype;

Declaration_section

BEGIN

Execution_section

Return return_variable;

EXCEPTION

exception section

Return return_variable;

END;

** Return Type: The header section defines the return type of the function. The return data type can be any of the oracle data type like varchar, number etc.

** The execution and exception section both should return a value which is of the data type defined in the header section.

Functions are divided into two types:

Scalar functions: SQL scalar functions return a single value, based on the input value

Scalar functions:

· UCASE() - Converts a field to upper case

· LCASE() - Converts a field to lower case

· MID() - Extract characters from a text field

· LEN() - Returns the length of a text field

· ROUND() - Rounds a numeric field to the number of decimals specified

Table valued functions: A function That is capable of retriving a table as an output is known as Table valued Function

Eg:

create function fn_std

(@CollegeID int)

returns table

Return(select S.StudentName,s.CollegeID,c.CollegeName,c.Location

from Students s Inner join Colleges c

on s.CollegeID=c.CollegeID

where

s.CollegeID = @CollegeID)

Executing a Function:

Syntax: select * from function name (particular id)

Eg:

select * from fn_std(4)

TRIGGERS

Creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement.

EG: only upper case letters:

create trigger Book_trg_insert

Books After insert

begin

declare

@BookID int,

@BookName nvarchar(50),

@RackNo int

select

@BookID=BookID,

@BookName=BookName,

@RackNo=RackNo

from inserted

update Books set BookName= upper(@BookName)

where BookID=@BookID

end

Example of above Trigger Execution:

insert into Books values(1,'asp.net',1)

SQL Quick Reference:

SQL Statement	Syntax
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND\|OR condition
ALTER TABLE	ALTER TABLE table_name ADD column_name datatype or ALTER TABLE table_name DROP COLUMN column_name
AS (alias)	SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... )
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE	DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name (Note: Deletes the entire table!!) DELETE * FROM table_name (Note: Deletes the entire table!!)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL)
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
HAVING	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)
INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC\|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO	SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP	SELECT TOP number\|percent column_name(s) FROM table_name
TRUNCATE TABLE	TRUNCATE TABLE table_name
UNION	SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL	SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE	UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value

Referencess:

Ø www.c-sharpcorner.com

Ø www.beginner-sql-tutorial.com

Ø www.1keydata.com

Ø www.w3schools.com

Copy Rights Reserved : ? www.thejobschool.com

INDEX:

SerialNumber	TopicName		PageNumbers
1	Intorduction To DataBase Ø DBMS Ø RDBMS		1
2	Intorduction To Sql Server 2005		2-4
3	DataTypes		5-7
4	SqlCommands		8-10
5	Constraints Ø PRIMARY KEY Ø UNIQUE Ø NOTNULL Ø FOREIGN KEY Ø CHECK Ø DEFAULT		11-14
6	Bulit in Functions Ø Single_Row_Functions Ø Group_Functions		15-24
7	Sql-Operators		25-28
8	Joins Ø Equi_Join Ø Non_ Equi_Join Ø Cartesian_Join Ø Outer_Join		29-31
9	Clauses Whereclause,OrderBy,Having,grooup	32-34
10	Views		34-36
11	Indexes		37
12	StoredProcedures		38-39
13	Pl/Sql Functions Ø Scalar_Functions Ø Table_Valued_Functions		40-41
14	Triggers		42
15	Sql_Quick_Reference		43-45

yerrus

Thursday, March 1, 2012

database material for .net

SQL Operators

"OR" Logical Operator:

AND" Logical Operator:

"NOT" Logical Operator:

SQL Joins

DROP INDEX Statement

No comments:

Post a Comment

images

Search This Blog