Many To Many Relationship Table Script

I am creating a database for tracking the Cricketers and their types. By types I mean Batsman, Bowler, Wicket Keeper, etc.
The issue is a Cricketer might belong to different type. For Example Kapil Dev is both a Batsman and a Bowler.
Inorder to solve this I should create a third table referred to as Junction Table apart from Cricketer and Type tables which is having reference to both Crickter and Type tables and a Composite Key. Here I name it Cricketer Type.

Create database Cricket

use Cricket

Create table Cricketer (CricketerId int identity(1,1) primary key,Cricketer varchar(40))

Create table Type (TypeId int identity(1,1) primary key,Type varchar(40))

insert into Cricketer values ('Sumesh')
insert into Cricketer values ('Nithin')
insert into Cricketer values ('Shalvin')

insert into Cricketer values ('Mongia')
insert into Type values ('Batsman')
insert into Type values ('Bowler')
insert into Type values ('Wicket Keeper')

create table CricketerType(primary key (CricketerId, TypeId),CricketerId int references Cricketer(CricketerId),TypeId int references Type(TypeId) )

insert into CricketerType values (1, 1)
insert into CricketerType values (1, 3)
insert into CricketerType values (2, 2)
insert into CricketerType values (3, 3)
insert into CricketerType values (4, 3)

select * from Cricketer
select * from Type
select * from CricketerType

select C.CricketerId, C.Cricketer, T.Type from Cricketer C, Type T,CricketerType CT where CT.CricketerId = C.CricketerId and Ct.TypeId = T.TypeId

Sql Server Stored Procedures

Stored Procedures and block of code which does one or more actions on a database. 

CREATE PROCEDURE spHello as
 PRINT 'Hello welcome to T-Sql'
GO

Executing a stored Procedure

EXEC spHello

Stored Procedure with varchar parameter and string concatenation

create procedure spHelloName (@Name varchar(20))
as 
 print 'Hello ' + @Name
go

Procedure with integer Parameter and addition
Memory variables must preceded with @ symbol

create procedure spAdd(@p1 int, @p2 int)
as
 print @p1 + @p2
go

If

create procedure spIf (@pCity varchar(40))
as
if @pCity = 'Kochi'
 print 'Industrial capital  of Kerala'
go

While Loop

create or alter procedure spWhile
as
DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Hello '
   SET @cnt = @cnt + 1;
END;
GO

While Loop with Cast

create or alter procedure spWhileCounter
as
DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Hello ' + cast(@cnt as varchar(3))
   SET @cnt = @cnt + 1;
END;
GO 

Stored Procedure for Selecting values from a Table

CREATE PROCEDURE spAllCategories
AS
 select * from Categories
GO

spAllCategories

Stored Procedure with Joins
create procedure spAllProductsCategory
as
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId
go

Stored Procedure with Where Clause
create procedure spProductsCategorySearch(@pCategoryId int)
as
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId and C.CategoryId = @pCategoryId
go
spProductsCategorySearch 1
spProductsCategorySearch 2

Stored Procedure for Inserting values into a Table
create procedure spInsertCategories (@pCategoryName varchar(40), @pDescription varchar(60))
as
insert into Categories (CategoryName, Description) values (@pCategoryName, @pDescription)
GO

EXEC spInsertCategories @pCategoryName = 'Condiments', @pDescription = 'Sweets and savory sauces, relishes, spreads, and seasonings'spInsertCategories 'Confections', 'Desserts, candies, and sweet breads'


User Managament Scripts
create database UserManagement

use UserManagement

create table Roles(RoleId int identity(1,1) primary key, Role varchar(40))

create table Users (UserId int identity(1,1) primary key,
RoleId int References Roles(RoleId), UserName varchar(40),
Password varchar(40))

create procedure InsertRole(@pRoleName varchar(40))
as
insert into Roles values (@pRoleName)
go

insertRole 'Admin'

create procedure spAllRoles as
select * from Roles
go

create procedure spInsertUser(@pGroupId int,
@pUserName varchar(40), @pPassword varchar(40))
as
insert into Users values (@pGroupId, @pUserName, @pPassword )
go

spInsertUser 1, 'Greorge', 'george'
spAllRoles

create procedure spAllUsers
as
select * from Users
go

spAllUsers

create procedure spUserCount (@pUserName varchar(40), @pPassword varchar(40), @userCount int output)
as
select @userCount = COUNT(*) from Users where UserName = @pUserName and Password =@pPassword
return
go

spUserCount 'George', 'george', ''

create procedure GetUserRole (@pUserName varchar(40))
as
select R.Role from Roles R, Users U where R.RoleId = U.RoleId and U.UserName = @pUserName
go

GetUserRole 'Shalvin'

GetUserRole 'Manu'

sqlcmd and Sql Server

sqlcmd is a command line tool for interacting with Sql Server.

Invoke the command prompt.














If your's is a low configuration machine sqlcmd is best tool since Sql Server Management studio is resource hungry.

sqlcmd -S .\sqlexpress
>create database ShalvinPDBlog
>go
>use ShalvinPDBlog
>create table Categories (
CategoryId int primary key identity(1,1), 
CategoryName varchar(20), Description varchar(60))
>go

Here -S points to the server name.



Related Blog
Transact Sql





For more information : http://msdn.microsoft.com/en-us/library/ms162773.aspx

SQL Server Authentication >sqlcmd -S .\sqlexpress -U sa -P s

Transact Sql

SQL (Structured Query Language) is the computer language meant for working with RDBMS (Relational Database Management Systems).
Transact-Sql (T Sql) is the Microsoft SQL Server's  implementation of SQL.

 

For working with SQL Server we can use SQL Server Management Studio (SSMS) or light weight options like sqlcmd command line option.

Types of Sql Commands 

DDL
Data Definition Language

Used to create and modify database object like Tables, Views, etc.
Eg : CREATE TABLE

DML Data Manipution Lanaguage

Eg : INSERT, UPDAT and DELETE

DCL
Data Control Language
Eg: GRANT and REVOKE

Creating a database

create database Shalvin

Switching to the newly created database

use Shalvin

 
Creating a new table, setting an auto increment field and primary key
IDENTY is used for creating an auto increment field. The parameters are seed and increment.

create table Categories (
CategoryId int identity(1,1) primary key, 
CategoryName varchar(40), 
Description varchar(60))

 

 
Inserting values

insert into Categories values ('Beverages', 'Tea, Coffee, soft drinks, etc')
insert into Categories values ('Condiments', 'Sweets and savory sauces')
insert into Categories values ('Confections', 'Desserts, candies and sweet breads')
insert into Categories values ('Diary', 'Cheese')

 

 

Inserting values only to specific field(s)

insert into Categories (CategoryName) 
    values ('Milk Products')

Updating a value in a Table

update Categories set Description = 'Bakery items' 
WHERE  CategoryId = 2

Selecting Records

select * from Categories

Selecting Specific record(s) using WHERE clause 

-- Here I am using the Northwind database that is coming with Sql Server 2000.

use Northwind

SELECT CategoryId, CategoryName, Description FROM Categories WHERE CategoryId = 1

select * from categories where categoryid = 1 or categoryid = 2<br />

In Clause

select * from Categories where CategoryId in(1, 2)

Creating a table for foreign key constraint

create table Products (ProductId int identity(10, 1) primary key, 
CategoryId int foreign key references Categories(CategoryId),
 ProductName varchar(40), UnitPrice numeric(6,2))

insert into Products values (1, 'Bru Coffee 100 g', 10)

insert into Products values (10, 'Tomato Sauce 200 ml', 20) 

Adding constraints to an existing column in a Table

alter table expense add constraint expense_uk unique(expensename)

Demonstrating foreign key vioation error
--Here there is no record in Categories table with CategoryId 5

insert into Products values (5, 'ASP.Net 3.5 Unleashed', 10)

 

 Following is the complete SELECT query. In the bracket I have specified the execution order.

SELECT (5)

FROM (1)

WHERE (2)

GROUP BY (3)

HAVING (4)

ORDER BY (6)

OFFSET - FETCH  (7)

1. FROM defined the data set

2. WHERE defines the ROW filter using predicates (a logical expression that evaluates to either true or false)

3. GROUP BY - Individual rows can be combined into groups based on grouping expression.

4. HAVING - Group filer

5 SELECT - Selects the record

6. ORDER BY - Presentation Order

7. OFFSET - FETCH - Paging Specification

 

SELECT WITHOUT FROM

SELECT 123, 2*3, sqrt(3)

123    6    1.73205080756888

 

The above command will not work with Oracle. It requires a dummy table.

 

SELECT 123 as Numbers, 2*3 as Multiple, sqrt(3) as Square

The above command introduce alias so there can be column name.

SELECT * from Products

Select * from <TABLENAME> extracts all the data from table.

 

Selecting records from multiple Tables

SELECT Categories.CategoryName, Products.ProductName,
 Products.UnitPrice
 FROM Categories, Products 
WHERE Categories.CategoryId = Products.CategoryId

Selecting Records from Multiples Tables using Table Alias

SELECT C.CategoryName, P.ProductName, P.UnitPrice 
FROM Categories C , Products P 
WHERE C.CategoryId = P.CategoryId

Need for Normalization
Lets take the case of an unnormalized Table. My intention is the track the employee information and their transactions.

create table NNEmployee (TraId int identity(1,1) primary key,EmployeeId int, EmployeeName varchar(40), DOJ DateTime, Department varchar(40), Year varchar(4), Month varchar(10), NoOfDaysPresent int)

insert into NNEmployee values (1, 'Shalvin', '12-Jan-08', 'IT', '09', 'Jan', 22)
insert into NNEmployee values (1, 'Shalvin', '12-Jan-08', 'IT', '09','Feb', 21)
insert into NNEmployee values (2, 'Ajith', '1-Jan-08', 'Networking', '09','Jan', 22)
insert into NNEmployee values (2, 'Ajith', '1-Jan-08', 'Networking', '09','Feb', 20)

select * from NNEmployee

Here I am duplication information like Date of Join, Department, etc.

Instead I can create three tables holding Department, Employee Master and Transaction Information.

Create table Dept (DeptNo int identity(1,1) primary key, DName varchar(40))

insert into Dept values ('IT')
insert into Dept values ('Networking')

select * from Dept
create table Emp (EmpNo int identity(10, 1) primary key, DeptNo int references Dept(DeptNo), EName varchar(40), DOJ DateTime)

insert into Emp values (1, 'Shalvin', '12-Jan-08')
insert into Emp values (2, 'Ajith', '1-Jan-08')

select * from emp

select E.EmpNo, D.DName, E.EName, E.DOJ FROM Dept D, Emp E WHERE D.DeptNo = E.DeptNo

create table EmpTran (TranId int identity(100, 1) primary key,EmpNo int references Emp(EmpNo), Year varchar(4), Month varchar(10), NoOfDaysPresent int)

insert into EmpTran values (10, '09', 'Jan', 22)insert into EmpTran values (10, '09', 'Feb', 20)
insert into EmpTran values (11, '09', 'Jan', 22)insert into EmpTran values (11, '09', 'Feb', 21)
select * from EmpTran

 
Aggregate Functions
Aggregated Functions act on a set of values and returns a single result row.
select max(sal) from emp
select min(sal) from emp
select avg(sal) from emp
select sum(sal) from emp;
select count(*) from emp;
Aritmetic Functionsselect power(5,2)
select sqrt(25)

Date Functions
select getdate()
Result - 2009-07-22 16:00:03.590

Extracting the current year
select datepart(year, getdate())
Result - 2009

Extracting the current month
select datepart(month, getdate())
Result - 7

Extracting the current day
select datepart(day, getdate())
Result - 22

SubQuery
Subquery is a query nested inside another query.
For example, for extracting all the products from Products table of category 'Beverages' the subquery is

SELECT * FROM Products WHERE CategoryId IN (SELECT CatgoryId from Categories WHERE CategoryName = 'Beverages')
More Info

Renaming databasesp_renamedb 'Shalvin', 'ShalvinPD'

or
Alter Database Shalvin Modify Name = ShalvinPD

Listing all databases in an sql server Instancesp_helpdb


Listing all tables within a databaseSELECT * FROM sysobjects WHERE xtype = 'u'

Listing the structure of a tablesp_help Categories

Versions of Sql Server and Developer Tools
Sql Server 7
Sql Server 2000 - Query Analyser, Enterprise Manager
Sql Server 2005 - Sql Server Management Studio
Sql Server 2008

Related Blog
Sql Server Stored Procedures

sqlcmd and Sql Server 2005

Email : shalvin@gmail.com