Over the last few posts about SQL and SQL server training, I wrote up how to:
- Install SQL Server Management Studio (SSMS)
- Set-up a database on the server installed on the machine with SSMS
- Add a table either using the UI or SQL statements
- 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
http://cyreath.blogspot.co.uk/2013/09/tools-tech-install-sql-server-2012.html
http://www.youtube.com/watch?v=ty8kVWeHubU
http://www.youtube.com/watch?v=ty8kVWeHubU
Add / Delete a Database
http://cyreath.blogspot.co.uk/2013/09/tools-tech-add-delete-sql-database.html
http://www.youtube.com/watch?v=TdFNKQq_FPg
http://www.youtube.com/watch?v=TdFNKQq_FPg
Add / Delete a Table
http://cyreath.blogspot.co.uk/2013/09/tools-tech-add-delete-table-from.html
http://www.youtube.com/watch?v=uxp302kCDKg
http://www.youtube.com/watch?v=uxp302kCDKg
Add Data, set a constraint
http://cyreath.blogspot.co.uk/2013/09/tools-tech-add-data-to-sql-table-set.html
http://www.youtube.com/watch?v=2NG1OVAZGrI
http://www.youtube.com/watch?v=2NG1OVAZGrI
-------------------
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]
-----------------------------------------------------
YouTube Channel: [WATCH, RATE, SUBSCRIBE]