Yr9muzns

1FirstName    LastName   Password    Email
2	
3DROP TABLE Users
4
5CREATE TABLE Users(
6    FirstName nvarchar(10) ,
7    LastName nvarchar(10),
8    Password nvarchar(128),
9    Email nvarchar(30),
10    )
11
12INSERT INTO Users (FirstName, LastName) VALUES ('Adams','Armstrong'),
13                                            ('John','Barnes'),
14                                            ('Mark','Barnetty'),
15                                            ('Mike','Ra'),
16                                            ('Harry','Radley'),
17                                            ('Nick','Kade'),
18                                            ('Brian','Kael'),
19                                            ('Lea','Fahim'),
20                                            ('Julia','Dacey'),
21                                            ('Anna','Octavio'),
22                                            ('Kate','Yale')
23
24INSERT INTO Users (FirstName, LastName, Password, Email) 
25SELECT FirstName, LastName, Password, Email =
26    CASE 
27    WHEN Email = 0 THEN
28    LOWER(LEFT(FirstName,1) + LastName) + '@hotmail.com'
29    WHEN Email = 1 THEN
30    LOWER(LEFT(FirstName,1) + LastName) + '@gmail.com'
31    WHEN Email = 2 THEN
32    LOWER(LEFT(FirstName,1) + LastName) + '@mail.ru'
33    END
34    FROM (SELECT  FirstName, LastName, REPLACE(LOWER(CONVERT(NVARCHAR(128), NEWID())), '-', '') as Password,
35    CAST(RAND(CHECKSUM(NEWID())) * 3 as INT) as Email from Users) as random
36
37DELETE FROM Users WHERE Password IS NULL and  Email IS NULL
38
39SELECT * FROM Users