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

http://cyreath.blogspot.co.uk/2013/09/tools-tech-install-sql-server-2012.html
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

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

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

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

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]

Read More

Tools & Tech - Add data to a SQL table, set a constraint

[WATCH, RATE, SUBSCRIBE]

Before we carry on our SQL server training and then onto SQL proper, then start adding data to our tables, there's one last item to set-up. 

In the previous post, Add / Delete Tables from a Database, we set up a Primary Key for our tables. However, it can prove useful to know about the Foreign Key constraint too, used to help ensure data integrity.

What is a Foreign Key?

A Foreign Key is a reference to a Primary Key in another table. It is required all the time but it allows us to relate data in one table to data in another. In our example tables, Grade in the SalesTeam table references data in the GradeID column of the EmpGrade table. The idea being that all employees have a grade, this then allows a cross check to the SalaryMax associated with that grade.

The use of the Foreign Key constraint ensures whatever Grade we enter in the SalesTeam table, matches what are valid grades as given in the EmpGrade table. If not, we'd have no idea what the SalaryMax for an employee should be.

Setting a Foreign Key Constraint (via UI)

To add a Foreign Key via the UI is a bit convoluted to say the least;

• Right-click on the table name in the Object Explorer window, SalesTeam in this example
• In the context menu that opens click 'Design', this opens the table designer in the right-hand pane
• Right-click next to the column name and in the menu that opens select 'Relationships'

In the Foreign Key Relationships window that open click the [Add] button.

You'll see an auto generated ID under 'Selected Relationships' that will need editing in the next step. In the right hand panel click on [...] next to 'Tables and Columns Specification'.

The Primary Key we want to reference is in the EmpGrade table and the column is GradeID, select those in the 'Tables and Columns' window. Ensure SalesTeam is selected as the Foreign Key Table and Grade as the column.

Ensure the 'Relationship name' is set to something meaningful, click [OK] to save and exit the dialogue boxes. If you now expand 'SalesTeam > Columns' in the Object Explorer window, you will see Grade is now marked as 'FK' 

Setting a Foreign Key Constraint (via SQL)

To set the Foreign Key via a SQL statement is much more straightforward, we simply use the 'alter' keyword.

Open a new Query window and enter the following:

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


If we read this out-loud we're saying; Alter the table called SalesTeam and add a constraint called FK_SalesTeam_Grade of type foreign key on the column Grade, which references the EmpGrade tables's GradeID column.

Hit F5 to run the statement and make the change.

Adding Data to a Table (via UI)

To add data to our table using the UI;

• Right-click on the table name in the Object Explorer
• Select 'Edit Top 200 Rows
• Enter data into the designer that opens in the right hand panel

To check if your data has saved, open a new query window and execute:

   select * from EmpGrade

Adding Data to a Table (via SQL)

To add data via an SQL statement we need to use the insert keyword and specify what data we want in which columns.

   insert into EmpGrade
   (GradeID, SalaryMax)
   values (1, 10000)


For our SalesTeam table we would need to specify the full list of columns and data, just the same as above.

   insert into SalesTeam
   (EmpID, FirstName, LastName, Location, Grade)
   values (1, 'Andrew', 'Johns','Manchester', 3)


To enter multiple data sets into a table at once, simply repeat the data values:

   insert into SalesTeam
   (EmpID, FirstName, LastName, Location, Grade)

   values (1, 'Andrew', 'Johns','Manchester', 3),
   (2, 'Steven', 'Carre','Baker', 4),
   (3, 'Daniel', 'McCormick','Edinburgh', 2)


We'll leave deleting and altering data to the next post / video, when we look at what useful SQL keywords and statements will be good to know from the tester perspective.



To see the above being run, watch the video below - don't forget to subscribe to the channel!

Read More

Tools & Tech - Add / Delete a Table from a database

[WATCH, RATE, SUBSCRIBE]

Now that we have a SQL database up and running, we need to add Tables. As with adding the Database, this can either be done via the user interface or by running a set of SQL queries. As before, we'll do both.

Adding a Table to a Database (via UI)

If you're not already; Log-into your SQL server via SSMS, open the Object Explorer window (‘View > Object Explorer’ or hit F8), then expand 'Databases' so you can see your test database.

•         Expand your test database, e.g. ‘TestDB001’ then
•     Right-click on Tables and select ‘New Table…’ to open the table designer window

Enter the column names you want to use and select the appropriate data type. There is a full list of data types given here http://technet.microsoft.com/en-us/library/ms187752.aspx.

We're going to set up two tables, one called EmpGrade to store data about the Employee Grade, the other called SalesTeam which has the list of Sales Team members.

Add the following columns into the Table Designer, selecting the correct data type and select if the column can have null data or if it must be populated with data. For all the following I've set them to NOT NULL, except SalaryMax.

EmpGrade:

• GradeID (int)
• SalaryMax (int)

SalesTeam:

• EmpID (int)
• FirstName (varchar(50)
• LastName (varchar(50))
• Location (varchar(50))
• Grade (int)

Make GradeID and EmpID the Primary Key of each table by right-clicking on the tab to the left and selecting 'Set Primary Key'. We'll come back to setting a Foreign Key in a later post.

For each, click 'File > Save Table1...' and save your tables.

Deleting a Table in a Database (via UI)

Deleting a table via the UI is straight forward. Simply select your table and right-click. In the context menu that appears, click on Delete.

Delete your temporary test tables and we'll re-create them using SQL statements. If you want to keep them you can just make some additional tables.

Adding a Table to a Database (via SQL)

Doing all this via the UI is fine, but this isn't a SQL Server training tutorial, it's about SQL statements hands-on, so let's look at that. 

To add a new table using a SQL statement is as straight forward as the UI. We're also able to set parameters such as the Primary Key directly.

On the menu bar, select 'New Query' and enter the following:

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

Hit F5 to execute the statement.

We've now created a new table using SQL and set GradeID as the Primary Key. If you right-click on 'Tables' in the Object Explorer window then select 'Refresh', you will see your new table is there.

To add the second table from our example, run the following query:

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,
)

Hit F5 to execute the statement and refresh your tables list.

Deleting a Table in a Database (via SQL)

Deleting a table requires just a short SQL statement.

drop table SalesTeam

Then hit F5 to execute the statement and you'll see your table is deleted. Remember you might have to 'Refresh' the tables list to see the change.


Have a watch of the video on YouTube that covers all these points - linked to above

YouTube Channel: [WATCH, RATE, SUBSCRIBE]

Read More

Tools & Tech – Add / Delete a SQL database

[WATCH, RATE, SUBSCRIBE]

Let's carry on our SQL Server training. Now that we have SQL Server and SQL Server Management Studio (SSMS) installed on our system, we need to add a database and some tables to work with. That can either be done via the user interface or by running a set of SQL queries. Though the aim of this exercise is to practice writing SQL queries for our testing, we’ll look at both ways as it will be quicker to get set up.

Adding a Database

Log-into your SQL server via SSMS and open the Object Explorer window (‘View > Object Explorer’ or hit F8). Here’s the steps to follow:

•       Right click on ‘Databases’ then
•     Select ‘New Database…’ to open the New Database window
•     In ‘Database name’ enter the name of your database, something like TestDB001
•      Click [OK]

That's it, as we’re not changing any settings for the DB. If you now expand ‘Databases‘ in  the Object Explorer window you’ll see your new DB has been created.

To do this using a SQL statement is even easier. On the menu bar hit ‘New Query’ to open the Query window. In the query window type:

create database TestDB002 

and hit F5 to run the query.

If you don’t see your new database, right click on ‘Databases’ in the Object Explorer window and in the menu that appears click ‘Refresh’.

Deleting a Database

If you want to remove the database you just created it’s straight forward via the GUI. Just right click on the name of your database in the Object Explorer window and in the menu that appears, click ‘Delete’. This opens a Delete Object window that will have your database listed. Simply click [OK] and your database will be deleted.

To do this via a SQL query, go to the SQL Query window we opened earlier and type the following:

drop database TestDB002

That will then remove the database named TestDB002.

For a full walkthrough have a watch of the short video - linked to above!

YouTube Channel: [WATCH, RATE, SUBSCRIBE]

Read More

Tools & Tech - Install SQL Server 2012 Express

Often we’ll be testing with systems that contain data that we’re interested in. We’re interested in the data because it will inform us about how the system is operating, what functions, methods, classes, etc. are doing in the background with data we enter or that is received by the system in some way. Much of the data the system works with is hidden, that is not exposed back in the UI, so our ability to get access to it wherever it’s stored is critical.

Most of the time we can expect data to be stored in a database (DB), as opposed to a set of flat files perhaps. That means we need to know how to access the DB and then access the data that’s stored on it. To access the data, we’re going to have to know some of the basics of the Structured Query Language (SQL) that is used to work with data in a DB.

To practice using SQL and start playing with the data on a DB, we need a DB to play with. Most often we don’t have free access to a DB in our workplace, so setting up our own would be very handy. To do that isn’t as hard as it might seem. We can use a free product from Microsoft called SQL Server 2012 Express and install it on a Windows system.

YouTube Channel: [WATCH, RATE, SUBSCRIBE]

Watch the video for a walkthrough of the download and installation process. You can download SQL Server 2012 Express from here: www.microsoft.com/express/sql/ to help with your SQL Server training.

In the follow up video, we’ll build out some tables, populate it with some test data as if we have a freshly installed build, then run some queries that give an idea of what we’ll often be 

doing.

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

Tools & Tech - SQL Server 2012 Express

Read More

Project ‘Coding Oledium’ – Part 7

In the last part of Coding Oledium we got down to building out the main methods file and add a Loop to create a flow for the game over the main player actions. However, there was a slight issue in that we had to kill the player in order to quit, assuming the player didn't just quit cleanly using the Quit command.

In this version the Loop is fixed so that the game will end if the player Quits or dies. We haven't yet implemented the Attacking, swipe for swipe by the player and the monster, but we can pretend to have had a fight at this point which is fine. One technique when coding is of course to implement something at a simple level, then go back and elaborate it. A little bit like building something to a substantial degree, then polishing and prettifying it afterwards. We know the code we add won't break the main body of the application / game later - or at least shouldn't do so, but that's another issue related to 'scope', which we can cover at some other point.

The main piece of work in Part 7 is to get the ad_hero_generator.vbs file progressed enough to start writing to an external file, the character-sheet.txt file to be specific. This is what we want producing when the player first starts the game and at the end when they quit. Later on we can look at reading the file contents in so the user doesn't have to start from scratch every time. For now though we just need to create and write the file.

YouTube Channel: [WATCH, RATE, SUBSCRIBE]

In the main ad_index.vbs file we create a new File System Object, which we use to read in the contents of the other game files. It's the same thing we need to do for creating the Character Sheet and writing to it.

If you remember from this link about the OpenTextFile method (http://msdn.microsoft.com/en-us/library/314cz14s(v=vs.84).aspx) we had a choice to set whether we want to read, write or append to the end. The value of 2 is what we want for writing. Here's two key lines we'll add in the CharacterSheetGeneration function:

oOutputFile = "./Character-Sheet.txt"

and also;

Set sTextOutput = oFSO1.OpenTextFile(oOutputFile, 2, True)

The use of True is to indicate that the file should be created if it doesn't already exist. This is then followed by lines to add text to the Character Sheet, for example:

 sTextOutput.writeline sHeroName

With this we're then able to call the ad_hero_generator.vbs file and call the function at the start of the game. Have a look at the video to see the extra elements around this approach.

Going ahead we still need to create Arrays and Classes. However, Classes in VBScript seem to be a little bit 'interesting' and a somewhat new addition from what I've read. I may do a separate study of them just to be sure they're understood.

Read More

Project ‘Coding Oledium’ – Part 6

In part 6 the main thing worked on was the Case Statement that will provide the checks on what sPlayerAction has been received from the player. These mostly match our Mind Map that we created in Part 1, with a few additions thought up on the way.

The next item is to think about how to Loop and get the player to drop out of the game if a) they die or b) they Quit the game. In the previous post it seemed it was an If statement that would be simplest. Instead of that however, VBScript provides an even simpler way, using the Do Until... Loop construct.

Given we want to cover both the events of the player getting 0 Hit Points (aka becoming dead) and Quitting - we also need to use Or in the statement.

Using SciTe colouring it looks like this:

     Do Until iPlayerHp <= 0 OR sPlayerAction = "Quit"
     ... case statements
     Loop

YouTube Channel: [WATCH, RATE, SUBSCRIBE]

In this version there's also a few style issues that need sorting out. For example the sMsgBoxTitle string that we want as the title for all Message Boxes doesn't work in all instances that it's called - not figured out why yet...

Read More