SQLServer Application Development --- SQLSERVER2000 comprehensive data query instance

SQLSERVER2000 Data Query integrated instance 1: update user card information 1. Description: A company has printed a batch of rechargeable cards, and the card password is generated randomly. Now this problem occurs: the O and 0, I and 1 in the card are clearly invisible to the user's reflection. The company decided to change all the O in the password stored in the database to 0 and change all

SQL SERVER 2000 data query integrated instance 1: update user card information 1. Description: A company has printed a batch of rechargeable cards, and the card password is generated randomly. Now this problem occurs: card's "O" and "0", "I" and "1", the user reports cannot clearly be seen, the company decided, change all "O" in the password stored in the database to "0", and change all

SQL SERVER 2000DataQueryComprehensiveInstance
Instance1: update user card information
1. Description:
A company printed a batch of rechargeable cards and the card password was randomly generated. Now this problem occurs: "O" and "0", "I" and "1" in the card ", if the user reports clearly, the company decides to storeDataAll "O" in the password in the database is changed to "0", and all "I" are changed to "1 ".
2. Implementation:
Declare @ Card table ([password] varchar (8) -- createDataTable
Insert into @ Card
Select 'abcdefgh' union
Select 'ijklmnop' union
Select 'qrstuvwx'
Select * from @ Card
Update @ Card set [password] = replace ([password], 'O', '0'), 'I', '1 ')
Select * from @ Card
Go

Instance2: Special sorting
1. Description:
InDataThe database table contains the following characters:DataSuch: 13-1, 13-2, 13-3, 13-4, 13-100, 13-108, 13-18, 13-11, 13-15, 14-1, 14-2, now you want to sort data by SQL statements, and sort the data by the first half of the number, and then sort the data by the second half. The output is arranged as follows: 13-1, 13-2, 13-3, 13-10, 13-11, 13-15, 13-18, 13-100, 13-108, 14-1, 14-2
2. Implementation:
Declare @ SellRecord table (listNumber varchar (10) -- createDataTable
Insert into @ SellRecord
Select '13-1' union
Select '13-2' union
Select '13-3' union
Select '13-4' union
Select '13-100 'union
Select '13-108 'union
Select '13-18 'union
Select '13-11' union
Select '13-15' union
Select '14-1' union
Select '14-2'
Select * from @ SellRecord
Select * from @ SellRecord order by convert (int, left (listNumber, charindex ('-', listNumber)-1), convert (int, stuff (listNumber, 1, charindex ('-', listNumber ),''))

Instance3:QueryOdd and even rows in a table
1. Description:
In a unitDataAnd then obtain a value based on the sum.
2. Implementation:
-- CreateDataTable
Use pubs
Go
If exists (select * from sysobjects where name = 'tbl') drop table tbl
Go
Create table tbl
(
IdKey int identity (1, 1) not null,
A int
)
Go
Insert into tbl (a) values (1)
Insert into tbl (a) values (2)
Insert into tbl (a) values (3)
Insert into tbl (a) values (4)
Insert into tbl (a) values (5)
Delete from tbl where idKey = 2
Go
Select * from tbl
Go
-- ProceedQuery
Select identity (int, 1, 1) as [id],
Into tempTbl
From tbl
Go
Select * from tempTbl
Select sum (a) from tempTbl where [id] % 2! = 0
Select sum (a) from tempTbl where [id] % 2 = 0
Go

Instance4: bank card recovery
1. Description: A Bank issued a new credit card and launched it well at the beginning. However, the amount of waste cards is gradually increasing, and the balance on the card is less than 2 yuan, and the user does not use the card for a long time. Therefore, the Bank backed up the user information of the cards less than 2 yuan in MarchDataThe database table is deleted, but the problem is coming soon. The user finds that his card can no longer be used for splitting, so he can only recover the cards.
2. Implementation:
Use pubs
Go
If exists (select * from sysobjects where name ='s ') drop table S
Go
If exists (select * from sysobjects where name = 'M') drop table M
Go
Create table M
(
CardID int primary key not null,
UserName varchar (20) not null
)
Go
Create table S
(
CountID int identity (1, 1) primary key, -- account ID
CardID int foreign key references M (CardID), -- card number
Score float -- balance
)
Go
Insert into M (CardID, UserName) values (16, 'zhang san ')
Insert into M (CardID, UserName) values (23, 'Lee 4 ')
Insert into M (CardID, UserName) values (25, 'wang wu ')
Insert into M (CardID, UserName) values (29, 'Liu liu ')
Insert into M (CardID, UserName) values (30, 'yang 7 ')
Insert into S (CardID, Score) values (16, 34.5)
Insert into S (CardID, Score) values (25,300)
Insert into S (CardID, Score) values (29, 1.5)
Go
Select * from M
Select * from S
Go
-- Restore
Insert into S (CardID, Score) select M. CardID, 2 from M left join S on M. CardID = S. CardID where S. CardID is null
Go
Select * from S
Go

Instance5:
1. Description:
There are two tables:DataMerge and group by student ID to obtain the total score and average score.
2. Implementation:
-- CreateDataTable
Use pubs
Go
If exists (select * from sysobjects where name = 'A') drop table
Go
If exists (select * from sysobjects where name = 'B') drop table B
Go
Create table A -- Mathematical Example table
(
[Id] int primary key,
Score int
)
Go
Create table B -- language sequence table
(
[Id] int primary key,
Score int
)
Go
Insert into A values (16, 66)
Insert into A values (23, 56)
Insert into A values (25, 67)
Insert into A values (29,45)
Insert into B values (23, 80)
Insert into B values (25, 90)
Insert into B values (29,59)
Insert into B values (30, 84)
Go
Select * from
Select * from B
Go
-- Create a temporary table andDataMerge and perform related operations
If exists (select * from sysobjects where name = 'C') drop table C
Go
Create table C -- mathematical tables
(
[Id] int,
Score int
)
Go
Insert into C (id, score)
Select A. id, A. score from A union
Select B. id, B. score from B
Go
Select id as student id, sum (score) as total score, avg (score) as average score from C group by id
Go

Instance6:
1. Description:
The table ABC contains fields A, B, and C, all of which are characters.DataColumn A stores A single letter from A to Z,QueryOutput all characters in column A between A and P.DataLine
2. Implementation:
-- CreateDataTable
Use pubs
Go
If exists (select * from sysobjects where name = 'abc') drop table ABC
Go
Create table ABC (id varchar (1 ))
Go
-- Take 5 Characters As An Example
Insert into ABC values ('A ')
Insert into ABC values ('B ')
Insert into ABC values ('C ')
Insert into ABC values ('D ')
Insert into ABC values ('E ')
Go
Select * from ABC where id between 'a 'and 'C'
Go

Instance7:
1. Description:
There are student renewal tables,DataAs follows,QueryName of the student whose score is greater than 80 for each course
2. Implementation:
-- CreateDataTable
Use pubs
Go
If exists (select * from sysobjects where name = 'tb') drop table tb
Go
Create table tb (stuName varchar (20), course varchar (20), score int)
Go
Insert into tb values ('zhangqi', 'China', 80)
Insert into tb values ('zhang qi', 'mat', 77)
Insert into tb values ('Li wan', 'China', 66)
Insert into tb values ('Li wan', 'mat', 91)
Insert into tb values ('wang yi', 'China', 84)
Insert into tb values ('wang yi', 'mat', 100)
Insert into tb values ('wang yi', 'English ', 90)
Insert into tb values ('yang Zhao ', 'English', 86)
Insert into tb values ('yang Zhao ', 'mat', 93)
Go
Select * from tb
Go
--QueryData
Select stuName from tb group by stuName having min (score)> = 80
Go

Instance8:
1. Description:
Merge user tables with three GameWOW, GameDiablo, and GameStarCraft tables in the following structure:DataMerged to the new table Game. The structure of the new table is as follows. For records that exist in the new table but do not exist in the source table, use NULL to indicate
2. Implementation:
Use pubs
Go
If exists (select * from sysobjects where name = 'gamewow') drop table GameWOW
Go
If exists (select * from sysobjects where name = 'giamediablo') drop table GameDiablo
Go
If exists (select * from sysobjects where name = 'gamestarcraft ') drop table GameStarCraft
Go
If exists (select * from sysobjects where name = 'game') drop table Game
Go
Create table GameWOW
(
SName varchar (10 ),
SPassWord varchar (10 ),
SBirthday smalldatetime,
SAddress varchar (10 ),
SEmail varchar (10)
)
Go
Insert into GameWOW values ('wow', 'wow', getdate (), 'wow', 'wow ')
Go
Create table GameDiablo
(
SName varchar (10 ),
SPassWord varchar (10 ),
SBirthday smalldatetime,
SSex bit,
SCardNumber varchar (10)
)
Go
Insert into GameDiablo values ('diablo', 'diablo', getdate (), 1, 'diablo ')
Go
Create table GameStarCraft
(
SName varchar (10 ),
SPassWord varchar (10 ),
SBirthday smalldatetime,
SArea varchar (10 ),
SCode int
)
Go
Insert into GameStarCraft values ('starcraft ', 'starcraft', getdate (), 'starcraft ', 1)
Go
Create table Game
(
SName varchar (10 ),
SPassWord varchar (10 ),
SBirthday smalldatetime,
SAddress varchar (10 ),
SEmail varchar (10 ),
SSex bit,
SCardNumber varchar (10 ),
SArea varchar (10 ),
SCode int
)
Go
-- Merge
Insert into Game (SName, SPassWord, SBirthday, SAddress, SEmail, SSex, SCardNumber, SArea, SCode)
Select SName, SPassWord, SBirthday, SAddress, SEmail, null
From GameWOW union
Select SName, SPassWord, SBirthday, null, null, SSex, SCardNumber, null, null
From GameDiablo union
Select SName, SPassWord, SBirthday, null, SArea, SCode
From GameStarCraft
Go
Select * from Game

Instance9:
1. Description:
The Forum uses a certain format as the primary post number, in the format of: Forum number _ current date _ four random numbers
2. Implementation:
Select primary sticker No. = 'Forum No. _ '+ convert (varchar (4), datepart (yyyy, getdate () +
Convert (varchar (2), datepart (mm, datepart (mm, getdate () +
Convert (varchar (2), datepart (dd, datepart (dd, getdate () +
Convert (varchar (4), right (rand (datepart (MS, getdate () * 1000), 4 ))