go
if exists (select * from dbo.sysobjects where id = object_id (N '[emp]')
and OBJECTPROPERTY (id, N'IsUserTable ') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar (50),
[Last name] varchar (50), gender char (1))
go
insert into Emp (id, [First name], [Last name], gender)
values (1, 'John', 'Smith', 'm')
insert into Emp (id, [First name], [Last name], gender)
values (2, 'James', 'Bond', 'm')
insert into Emp (id, [First name], [Last name], gender)
values (3, 'Alexa', 'Mantena', 'f')
insert into Emp (id, [First name], [Last name], gender)
values (4, 'Shui', 'Qui', 'f')
insert into Emp (id, [First name], [Last name], gender)
values (5, 'William', 'Hsu', 'm')
insert into Emp (id, [First name], [Last name], gender)
values (6, 'Danielle', 'Stewart', 'F')
insert into Emp (id, [First name], [Last name], gender)
values (7, 'Martha', 'Mcgrath', 'F')
insert into Emp (id, [First name], [Last name], gender)
values (8, 'Henry', 'Fayol', 'm')
insert into Emp (id, [First name], [Last name], gender)
values (9, 'Dick', 'Watson', 'm')
insert into Emp (id, [First name], [Last name], gender)
values (10, 'Helen', 'Foster', 'F')
go
Now, create a [Full name] column to determine which prefix should be 'Mr.' or 'Ms.', based on the value in the column Gender.
Select [id],[Full Name] = case Gender
when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
when 'f' then 'Ms. '+[First name]+ ' '+[Last name]
end
from Emp
This procedure returns the result as shown below:
id Full Name
----------- ----------------
1 Mr. John Smith
2 Mr. James Bond
3 Ms. Alexa Mantena
4 Ms. Shui Qui
5 Mr. William Hsu
6 Ms. Danielle Stewart
7 Ms. Martha Mcgrath
8 Mr. Henry Fayol
9 Mr. Dick Watson
10 Ms. Helen Foster
If you add a row with the value NULL to the gender column, you will not see any names returned in the result set.
Insert the following row into the emp table:
use tempdb go insert into Emp (id,[First name],[Last name], gender ) values (11,'Tom','Gabe',NULL) go
Now create a [Full name] column to determine the prefix prefix each name is 'Mr.' or 'Ms.', based on the value in the column Gender:
Select [id],[Full Name] = case Gender when 'm' then 'Mr. '+[First name]+ ' '+[Last name] when 'f' then 'Ms. '+[First name]+ ' '+[Last name] end from Emp
The result returned is as follows:
id Full Name
----- -----------------------
1 Mr. John Smith
2 Mr. James Bond
3 Ms. Alexa Mantena
4 Ms. Shui Qui
5 Mr. William Hsu
6 Ms. Danielle Stewart
7 Ms. Martha Mcgrath
8 Mr. Henry Fayol
9 Mr. Dick Watson
10 Ms. Helen Foster
11 NULL
However, we need to display the Full Name of the employee, even if there is no value on Gender.
To do this, you need to use the CASE function with the ELSE clause. Execute the query as follows:
Select [id],[Full Name] = case Gender
when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
when 'f' then 'Mz. '+[First name]+ ' '+[Last name]
else [First name]+ ' '+[Last name]
end
from Emp
The result returned is as follows:
id Full Name
------ ---------------------
1 Mr. John Smith
2 Mr. James Bond
3 Mz. Alexa Mantena
4 Mz. Shui Qui
5 Mr. William Hsu
6 Mz. Danielle Stewart
7 Mz. Martha Mcgrath
8 Mr. Henry Fayol
9 Mr. Dick Watson
10 Mz. Helen Foster
11 Tom Gabe
In the above two examples, you find that the conditions are either Male, Female, or None. However, in many cases you will need to use multiple conditions, multiple operators at the same time to return a value.
Add the [Marital Status] column to the table and update the value as below:
use tempdb
go
alter table Emp add [Marital Status] char(1) -- S-Single M-Married
go
Update Emp set [Marital Status]='S' where id in (1,5,8)
Update Emp set [Marital Status]='M' where [Marital Status] is NULL
Go
Suppose we want to display the employee name with the preceding prefix that makes it easy to determine their marital status. Execute the query as follows:
Select [id],[Full Name] = case
when Gender ='m' and [marital status] ='S' then 'MR. '+[First name]+ ' '+[Last name]
when Gender ='m' and [marital status] ='M' then 'Mr. '+[First name]+ ' '+[Last name]
when Gender ='f' and [marital status] ='S' then 'Ms. '+[First name]+ ' '+[Last name]
when Gender ='f' and [marital status] ='M' then 'Mrs. '+[First name]+ ' '+[Last name]
else [First name]+ ' '+[Last name]
end
from Emp
The result returned is:
id Full Name
----------- --------------------
1 MR. John Smith
2 Mr. James Bond
3 Mrs. Alexa Mantena
4 Mrs. Shui Qui
5 MR. William Hsu
6 Mrs. Danielle Stewart
7 Ms. Martha Mcgrath
8 MR. Henry Fayol
9 Mr. Dick Watson
10 Mrs. Helen Foster
11 Tom Gabe
Suppose we have the following table
use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money) go insert into Emp (id, [First name], [Last name], salary) values (1, 'John', 'Smith ', 120000) insert into Emp (id, [First name], [Last name], salary) values (2,' James', 'Bond', 95000) insert into Emp (id, [First name], [Last name ], salary) values (3, 'Alexa', 'Mantena', 200000) insert into Emp (id, [First name], [Last name], salary) values (4, 'Shui', 'Qui', 36000) insert into Emp (id, [First name], [Last name], salary) values (5, 'William', 'Hsu', 39000) insert into Emp (id, [First name], [Last name], salary) values (6, 'Danielle', 'Stewart', 50000) insert into Emp (id, [First name], [Last name], salary) values (7, 'Martha', 'Mcgrath', 400000) insert into Emp ( id, [First name], [Last name], salary) values (8, 'Henry', 'Fayol', 75000) insert into Emp (id, [First name], [Last name], salary) values (9, 'Dick', 'Watson', 91000) insert into Emp (id, [First name], [Last name], salary) values (10, 'Helen', 'Foster', 124000) go
And now I want to create a Tax column based on the following salary
Select [id], [Full Name] = [First name] + [Last name], Salary, Tax = case When salary between 0 and 36000 then Salary * .24 When salary between 36000 and 450000 then Salary * .28 When salary between 45000 and 75000 then Salary * .30 When salary between 75000 and 150000 then Salary * .32 else Salary * .40 end from Emp
This function will produce the result:
id Full Name Salary Tax ----------- ----------------- ----------- ------ -------- 1 JohnSmith 120000.00 95000.00 jamesbond 26600.000000 33600.000000 2 3 4 ShuiQui AlexaMantena 56000.000000 36000.00 200000.00 8640.000000 5 WilliamHsu 39000.00 50000.00 DanielleStewart 14000.000000 10920.000000 6 7 8 HenryFayol 112000.000000 MarthaMcgrath 400000.00 75000.00 91000.00 DickWatson 25480.000000 21000.000000 9 10 HelenFoster 124000.00 34720.000000
Suppose we have the table below in Books:
use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[Books]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [Books] GO create table Books (Bookid int, Title varchar (100), Authorname varchar (100), state char (2)) go into Books (Bookid, Title, Authorname, state) values (1, 'The Third Eye', 'Lobsang Rampa', 'CA') insert into Books (Bookid, Title, Authorname, state) values (2, 'Service Oriented Architecture For Dummies',' Judith Hurwitz ',' NJ ') insert into Books (Bookid, Title, Authorname, state) values (3,' Business Reference for Students and Professionals', 'Ray Myers',' NY ') insert into Books (Bookid, Title, Authorname, state) values (4,' More Java Gems', 'Dwight Deugo', 'FL') insert into Books (Bookid, Title, Authorname, state) values (5, 'Six Sigma Workbook For Dummies',' Craig Gygi ',' FL ') insert into Books (Bookid, Title, Authorname, state) values (6,' Performance Appraisals : How to Achieve Top Results', 'Priscilla A. Glidden', 'NC') insert into Book s (Bookid, Title, Authorname, state) values (7, 'Talent Management: From Competencies to Organizational Performance', 'John Smith', 'FL') insert into Books (Bookid, Title, Authorname, state) values (8, 'Using Unix', 'Howard Johnson', 'CT') insert into Books (Bookid, Title, Authorname, state) values (9, 'Mastering Oracle', 'Erina Zolotrova', 'CT') insert into Books (Bookid, Title, Authorname, state) values (10, 'How to become CEO', 'Olga Zohaskov', 'NY') goes
To query all the values in the table we use the query function below:
Select * from Books
This function will produce the result as shown below
Suppose we want to display the entire number of books in the order of states: first is NY, then CA, NJ, CT and FL.
You can do this using the CASE function as shown below:
select Title, Authorname, state from Books order by case when state = 'NY' then 1 when state = 'CA' then 2 when state = 'NJ' then 3 when state = 'CT' then 4 when state = 'FL' then 5 else 6 end
This function will produce the following result:
Title Authorname state ----------------------------------------------- ------------- --------------------- ----- Business Reference for Students and Professionals Ray Myers NY How to become Third Officer Olga Zohaskov NY The Third Eye Lobsang Rampa CA Depties Service Oriented Architecture For Judith Hurwitz NJ Using Unix Howard Johnson CTO Eolotrova CT More Java Gems Dwight Deugo FL Six Sigma Workbook For Dummies Craig FL Talent Management: From Competencies to Organizational Per John Smith FL Performance Appraisals: How to Achieve Top Results Priscilla A. Glidden NC
Suppose we have the following table
set quoted_identifier off go to use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money, state char (2)) go insert into Emp (id, [First name], [Last name], salary, State) values (1, 'John', 'Smith', 120000, 'WA') insert into Emp (id, [First name], [Last name], salary, State) values (2, 'James',' Bond ', 95000,' OR ') insert into Emp (id, [First name], [Last name], salary, State) values (3,' Alexa ',' Mantena ', 200000,' WY ') insert into Emp ( id, [First name], [Last name], salary, State) values (4, 'Shui', 'Qui', 36000, 'CO') insert into Emp (id, [First name], [Last name], salary, State) values (5, 'William', 'Hsu', 39000, 'NE') insert into Emp (id, [First name], [Last name], salary, State) values (6, 'Danielle', 'Stewart', 50000, 'TX') insert into Emp (id, [First name], [Last name], salary, State) values (7, 'Martha', 'Mcgrath', 400000, 'PA') inser t into Emp (id, [First name], [Last name], salary, State) values (8, 'Henry', 'Fayol', 75000, 'NJ') insert into Emp (id, [First name], [ Last name], salary, State) values (9, 'Dick', 'Watson', 91000, 'NY') insert into Emp (id, [First name], [Last name], salary, State) values (10, 'Helen', 'Foster', 124000, 'AK') go
And now want to create more TimeZone columns based on State (US state)
select id, [First name], [Last name], salary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'W' ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'MN' ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then 'Alaskan' when state in ('HA') then 'Hawaii' end from emp
The above function will produce the following result
id First name Last name salary Timezone ------------------------------------------- --- 1 John Smith 120000.00 Pacific 2 James Bond 95000.00 Pacific 3 Alea Mantena 200000.00 Mountain 4 Shui Qui 36000.00 Pacific 600.00 Central 6 Martha Mcgrath 400000.00 Eastern 8 Henry Fayol 75000.00 Eastern 9 Dick Watson 91000.00 Eastern 10 Helen Foster 124000.00 Alaskan
Now we want to see all the information in the lines with Eastern and Mountain Timezone
select * from (select id, [First name], [Last name], salary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ( 'NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA' ',' WV ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ', 'WY', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX' ',' MN ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in ( 'AK') then 'Hawaii' end from emp) as mytype where TimeZone in ('Mountain', 'eastern')
The result of this function is as follows
id First name Last name salary Timezone 3 Alexa Mantena 200000.00 Mountain 7 Martha Mcgrath 400000.00 Eastern 8 Henry Fayol 75000.00 Eastern 9 Dick Watson 91000.00 Eastern
Now we have the table above and you want to display the average value of the salary based on the time zone (Timezone).
select avg (salary) as AverageSalary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT' , 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', 'MI', ' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ',' CO ' , 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'IA', ' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in ('HA') then 'Hawaii' end from emp group by case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ' , 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', ' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ' , 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', ' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in (' HA ') then' Hawaii 'end
This function will produce the result as shown below
AverageSalary TimeZone --------------------------- 124000.00 Alaskan 50000.00 Central 188666.6666 Eastern 200000.00 Mountain 72500.00 Pacific
Now you just want to see the Eastern and Alaskan time zones from the above results. We can use the HAVING clause as shown below
select avg (salary) as AverageSalary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT' , 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', 'MI', ' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ',' CO ' , 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'IA', ' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in ('HA') then 'Hawaii' end from emp group by case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ' , 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', ' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ' , 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', ' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in (' HA ') then' Hawaii 'end having case when state in (' WA ',' OR ',' NE ', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE' ',' MD ',' DC ',' VA ',' WV ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern ' when state in ('MT', 'ID', 'WY', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE' ',' KS ',' OK ',' TX ',' MN ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ', 'AL') then 'Central' when state in ('AK') then 'Alaskan' when state in ('HA') then 'Hawaii' end in ('Eastern', 'Alaskan')
The result of the function is shown in the following figure:
AverageSalary TimeZone
-----------------------------
124000.00 Alaskan
188666.6666 Eastern
Suppose we have the following table
set quoted_identifier off go to use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money, state char (2)) go insert into Emp (id, [First name], [Last name], salary, State) values (1, 'John', 'Smith', 120000, 'WA') insert into Emp (id, [First name], [Last name], salary, State) values (2, 'James',' Bond ', 95000,' OR ') insert into Emp (id, [First name], [Last name], salary, State) values (3,' Alexa ',' Mantena ', 200000,' WY ') insert into Emp ( id, [First name], [Last name], salary, State) values (4, 'Shui', 'Qui', 36000, 'CO') insert into Emp (id, [First name], [Last name], salary, State) values (5, 'William', 'Hsu', 39000, 'NE') insert into Emp (id, [First name], [Last name], salary, State) values (6, 'Danielle', 'Stewart', 50000, 'TX') insert into Emp (id, [First name], [Last name], salary, State) values (7, 'Martha', 'Mcgrath', 400000, 'PA') inser t into Emp (id, [First name], [Last name], salary, State) values (8, 'Henry', 'Fayol', 75000, 'NJ') insert into Emp (id, [First name], [ Last name], salary, State) values (9, 'Dick', 'Watson', 91000, 'NY') insert into Emp (id, [First name], [Last name], salary, State) values (10, 'Helen', 'Foster', 124000, 'AK') go
And now we need to have 6 tables to store employee IDs of different time zones as shown below
if exists (select * from dbo.sysobjects where id = object_id (N '[eastern]') and objectproperty (id, N'isusertable ') = 1) drop table [eastern] go create table eastern (id int) if exists ( select * from dbo.sysobjects where id = object_id (N '[mountain]') and objectproperty (id, N'isusertable ') = 1) drop table [mountain] go create table mountain (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[hawaii]') and objectproperty (id, N'isusertable ') = 1) drop table [hawaii] go create table hawaii (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[central]') and objectproperty (id, N'isusertable ') = 1) [central] drop table created table central (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[alaskan]') and objectproperty (id, N'isusertable ') = 1) drop table [alaskan] go to create alaskan table (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[pacific]') and objectproperty (id, N'isusertable ') = 1) drop table [paci fic] go create table Pacific (id int) go insert into pacific (id) values (1) insert into pacific (id) values (2) insert into mountain (id) values (3) insert into mountain (id) values (4) ) insert into central (id) values (5) insert into central (id) values (6) insert into eastern (id) values (7) insert into eastern (id) values (8) insert into eastern (id) values (9 ) insert into alaskan (id) values (10) go
If you want to know the entire Eastern time zone employee, you will definitely have to execute a simple query statement as shown below.
select e.id, [First Name], [Last name], Salary, State
from emp e join eastern ee on e.id = ee.id
The above query statement will return the following result
id First name Last name salary Timezone
------------------------------------------------
7 Martha Mcgrath 400000.00 PA
8 Henry Fayol 75000.00 NJ
9 Dick Watson 91000.00 NY
So now suppose we need to create a script that allows us to put a time zone into a variable and display the result based on the value of that variable. This can be done using clauses and CASE functions as follows:
declare @group varchar (10)
set @ group = 'Pacific'
select ee.id, ee. [First Name], ee. [Last Name], Salary, State, @group as TimeZone from emp ee
left join mountain m on m. [id] = ee. [id]
kết nối không phải alaskan a trên a. [id] = ee. [id]
hăm bên trái h trong h. [id] = ee. [id]
trái bên ngoài c trên c. [id] = ee. [id]
pacific left kết nối p trên p. [id] = ee. [id]
trái bên ngoài e vào e. [id] = ee. [id]
where ee.id in (case @group
when 'Then' Eastern e.id
when 'Mountain' Then m.id
when 'Pacific' then p.id
when 'Alaskan' then a.id
when 'Hawaii' Then h.id
khi 'Central' Then c.id
end)
The above scenario will have the following results:
id First name Last name salary state TimZone ------------------------------------------ ------------- 1 John Smith 120000.00 WA Pacific 2 James Bond 95000.00 OR Pacific
The above script can be written in the following procedure:
create procedure emp_timezone @Zone varchar (10)
as
select ee.id, ee. [First Name], ee. [Last Name], Salary, State, @Zone as TimeZone from emp ee
left join mountain m on m. [id] = ee. [id]
kết nối không phải alaskan a trên a. [id] = ee. [id]
hăm bên trái h trong h. [id] = ee. [id]
trái bên ngoài c trên c. [id] = ee. [id]
pacific left kết nối p trên p. [id] = ee. [id]
trái bên ngoài e vào e. [id] = ee. [id]
where ee.id in (case @Zone
when 'Then' Eastern e.id
when 'Mountain' Then m.id
when 'Pacific' then p.id
when 'Alaskan' then a.id
when 'Hawaii' Then h.id
khi 'Central' Then c.id
end)
And execute the above created function with the command:
exec emp_timezone 'Eastern'
The function will produce the result:
id First name Last name salary state TimZone
-------------------------------------------------- ---
7 Martha Mcgrath 400000.00 PA Eastern
8 Henry Fayol 75000.00 NJ Eastern
9 Dick Watson 91000.00 NY Eastern
See more: