Tuesday, 10 September 2013

SQL Scripts for setting up the Test Tables


Over the last few posts about SQL and SQL server training, I wrote up how to:

  1. Install SQL Server Management Studio (SSMS)
  2. Set-up a database on the server installed on the machine with SSMS
  3. Add a table either using the UI or SQL statements
  4. Add data to the tables, again either using the UI or via SQL statements
-------------------

As a reminder, here's the full set of posts and links to the videos;

Install SQL Server Management Studio 2012 Express

Add / Delete a Database

Add / Delete a Table

Add Data, set a constraint

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

As it can be a bit hard to see what's being written when watching the videos, here's the two scripts to do steps 3 and 4 above. You can either copy these into a text file and save them as .sql files or paste it into a New Query window in SSMS, then save them.

Run Script 1, then Script 2.

Script 1: Set-up EmpGrade Table


-- Make sure the database context is correct --

Use TestDB001
Go

-- Create the table --

create table EmpGrade
(
GradeID int NOT NULL Primary Key,
SalaryMax int NOT NULL
);

-- Add data to the table --

insert into EmpGrade
(GradeID, SalaryMax)
values
(1, 10000),
(2, 20000),
(3, 30000),
(4, 40000),
(5, 50000),
(6, 60000),
(7, 70000),
(8, 80000);

-- Check the data is stored --

select * from EmpGrade



Script 2: Set-up SalesTeam Table, set the Foreign Key

-- Make sure the database context is correct --

Use TestDB001
Go

-- Create the table --

create table SalesTeam
(
EmpID int NOT NULL Primary Key,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Location varchar(50) NOT NULL,
Grade int NOT NULL,
);

-- Add data to the table --

insert into SalesTeam
(EmpID, FirstName, LastName, Location, Grade)
values
(1,'David','Green','London',1),
(2,'Claire','White','Hull',1),
(3,'Andrea','Thompson','Warwick',2),
(4,'Lucy','Smith','Bristol',2),
(5,'Andrew','Zaleski','Edinburgh',3),
(6,'Corrie','Sime','Basingstoke',4),
(7,'Ahmed','Nawaz','London',2),
(8,'Danny','Webb','Dublin',8),
(9,'David','Lawson','Bakewell',3),
(10,'Fred','Webb','Uxbridge',3);


-- Make the Foreign Key reference

alter table SalesTeam add constraint FK_SalesTeam_Grade
foreign key (Grade) references EmpGrade (GradeID);

-- Check the data is stored

select * from SalesTeam


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


YouTube Channel: [WATCH, RATE, SUBSCRIBE]





0 comments: