SQL Classic Questions and answers

About GROUP BY
Table content:
2005-05-09 wins
2005-05-09 wins
2005-05-09 Negative
2005-05-09 Negative
2005-05-10 wins
2005-05-10 Negative
2005-05-10 Negative

How do you write SQL statements if you want to generate the following results?

Outcome
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
CREATE TABLE #tmp (RQ varchar), Shengfu nchar (1))

INSERT into #tmp values (' 2005-05-09 ', ' wins ')
INSERT into #tmp values (' 2005-05-09 ', ' wins ')
INSERT into #tmp values (' 2005-05-09 ', ' negative ')
INSERT into #tmp values (' 2005-05-09 ', ' negative ')
INSERT into #tmp values (' 2005-05-10 ', ' wins ')
INSERT into #tmp values (' 2005-05-10 ', ' negative ')
INSERT into #tmp values (' 2005-05-10 ', ' negative ')

1) Select RQ, sum (case when shengfu= ' wins ' then 1 else 0 end) ' wins ', sum (case when shengfu= ' negative ' then 1 else 0 end) ' negative ' from #tmp Gro Up by RQ
2) Select N.rq,n, M. Negative from (
Select RQ, Victory =count (*) from #tmp where shengfu= ' wins ' GROUP by RQ) N INNER JOIN
(select RQ, Negative =count (*) from #tmp where shengfu= ' negative ' GROUP by RQ) M on N.RQ=M.RQ
3) Select A.col001,a.a1 wins, B.b1 negative from
(select Col001,count (col001) A1 from Temp1 where col002= ' wins ' GROUP by col001) A,
(select Col001,count (col001) B1 from Temp1 where col002= ' negative ' GROUP by col001) b
where a.col001=b.col001

2. Ask a query question for an SQL statement encountered during an interview
The table has a B C three column, implemented with the SQL statement: When column A is greater than column B, select column B, or column B if column B is greater than column C, select column C otherwise.
------------------------------------------
Select (case if A>b then a else B end),
(case is b>c then B esle C end)
From table_name

3. Interview question: a date-judged SQL statement?
Please remove the date in the Tb_send table (sendtime field) for all records of the day? (sendtime field is a datetime type, containing the date and time)
------------------------------------------
SELECT * from TB where DATEDIFF (Dd,sendtime,getdate ()) =0

4. There is a table with 3 fields in it: Chinese, maths, English. Among them, 3 records indicate the language 70 points, mathematics 80 points, English 58 points, please use an SQL statement to query out these three records and display them according to the following conditions (and write your thoughts):
Greater than or equal to 80 means good, greater than or equal to 60 means passing, less than 60 points for failing.
Display format:
English for Chinese mathematics
Fail to pass a good grade
------------------------------------------
Select
(Case when language >=80 then ' excellent '
When language >=60 then ' pass '
Else ' fail ') as language,
(Case when Math >=80 then ' excellent ')
When math >=60 then ' pass '
Else ' fail ') as mathematics,
(Case when English >=80 then ' excellent '
When English >=60 then ' pass '
Else ' fail ') as English,
From table

5. In sqlserver2000, please use SQL to create a user temporary table and system temporary table, which contains two field IDs and Idvalues, type is int, and explain the difference between the two?
------------------------------------------
User Temp table: Create TABLE #xx (ID int, idvalues int)
System Temp Table: Create TABLE # #xx (ID int, idvalues int)

Difference:
The User temporary table is visible only to the session of the user who created the table, not to other processes.
This temporary table is automatically deleted when the process that created it disappears.

The Global temporary table is visible to the entire SQL Server instance, but it is also automatically deleted when all the sessions that access it are gone.

6.sqlserver2000 is a large-scale database whose storage capacity is limited only by storage media, and how it implements this infinite capacity mechanism.
------------------------------------------
All of its data is stored in a data file (*.dbf), so as long as the file is large enough, the storage capacity of SQL Server can be expanded.

There are three types of files in a SQL Server 2000 database:

Main data files
The primary data file is the starting point of the database, pointing to other parts of the file in the database. Each database has a primary data file. The recommended file name extension for primary data files is. mdf.

Secondary data files
The secondary data file contains all data files except the primary data file. Some databases may not have secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is. ndf.

Log file
The log file contains all the log information required to recover the database. Each database must have at least one log file, but there can be more than one. The recommended file name extension for log files is. ldf.

7. Use an SQL statement to derive the result
Remove the format data from the Table1,table2 such as TABLE3, note the data and the results are inaccurate, just as a format to ask you.
You can use stored procedures as well.

Table1

Mon Department DEP Performance YJ
-------------------------------
January 01 10
January 02 10
January 03 5
February 02 8
February 04 9
March 03 8

Table2

Department DEP department name Dname
--------------------------------
01 Domestic Business
02 Domestic Business Two Department
03 Domestic Business Three Department
04 International Business Department

Table3 (Result)

Department DEP January February March
--------------------------------------
NULL NULL
8 NULL
5 8 NULL
NULL NULL 9

------------------------------------------
1)
Select a. department name Dname,b. Performance YJ as ' January ', c. Performance YJ as ' February ', d. performance YJ as ' March '
From table1 a,table2 b,table2 c,table2 D
Where a. DEP Department = B. DEP department and b. Month Mon = ' January ' and
A. Department dep = c. DEP department and c. Month Mon = ' February ' and
A. Department dep = D. Department DEP and d. Month Mon = ' March ' and
2)
Select A.DEP,
SUM (case if B.mon=1 then B.yj else 0 end) as ' January ',
SUM (case if b.mon=2 then B.yj else 0 end) as ' February ',
SUM (case if B.mon=3 then B.yj else 0 end) as ' March ',
SUM (case if b.mon=4 then B.yj else 0 end) as ' April ',
SUM (case if B.mon=5 then B.yj else 0 end) as ' May ',
SUM (case if B.mon=6 then B.yj else 0 end) as ' June ',
SUM (case if B.mon=7 then B.yj else 0 end) as ' July ',
SUM (case if B.mon=8 then B.yj else 0 end) as ' August ',
SUM (case if b.mon=9 then B.yj else 0 end) as ' September ',
SUM (case if b.mon=10 then B.yj else 0 end) as ' October ',
SUM (case if b.mon=11 then B.yj else 0 end) as ' November ',
SUM (case if b.mon=12 then B.yj else 0 end) as ' December ',
From Table2 a LEFT join Table1 B on A.DEP=B.DEP

8. Huawei faces a question
The ID in a table has multiple records, the records of all the IDs are detected, and the total number of records is displayed.
------------------------------------------
The Select ID, COUNT (*) from the TB group by ID has the count (*) >1
SELECT * FROM (select COUNT (ID) as count from table Group by ID) T where t.count>1

SQL Classic Questions and answers