Good SQL questions

1. An SQL statement interview question about group
Table content:
Victory
Victory
Negative
Negative
Victory
Negative
Negative

How do I write an SQL statement to generate the following results?

Win-win
2 2
1 2
------------------------------------------
Create Table # TMP (RQ varchar (10), shengfu nchar (1 ))

Insert into # TMP values ('2017-2005 ', 'sheng ')
Insert into # TMP values ('2017-2005 ', 'sheng ')
Insert into # TMP values ('2017-2005 ', 'negative ')
Insert into # TMP values ('2017-2005 ', 'negative ')
Insert into # TMP values ('2017-05-10 ', 'sheng ')
Insert into # TMP values ('2017-05-10 ', 'negative ')
Insert into # TMP values ('2017-05-10 ', 'negative ')

1) Select RQ, sum (case when shengfu = 'WINS 'then 1 else 0 end) 'wins', sum (case when shengfu = 'ned' then 1 else 0 end) 'Negative 'from # TMP group by RQ
2) Select N. RQ, N. Rows, M. rows from (
Select RQ, distinct = count (*) from # TMP where shengfu = 'sheng' group by rq) n inner join
(Select RQ, distinct = count (*) from # TMP where shengfu = 'negative 'group by rq) m on N. RQ = M. RQ
3) Select a. col001, A. A1 wins, B. B1 is negative from
(Select col001, count (col001) A1 from temp1 where col002 = 'sheng' group by col001),
(Select col001, count (col001) B1 from temp1 where col002 = 'negative 'group by col001) B
Where a. col001 = B. col001

2. Ask an SQL statement query question during the interview.
The table has three columns a B c, which are implemented using SQL statements: When Column A is greater than Column B, select column A; otherwise, select Column B, if column B is greater than column C, column B is selected; otherwise, column C is selected.
------------------------------------------
Select (case when A> B then a else B End ),
(Case when B> C then B esle C end)
From table_name

3. interview question: an SQL statement for date judgment?
Please retrieve all records whose date (sendtime field) is the current day In the tb_send table? (The sendtime field is of the datetime type, including the date and time)
------------------------------------------
Select * from TB where datediff (DD, sendtime, getdate () = 0

4. There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics, and 58 points in English, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):
If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
------------------------------------------
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table

5. In sqlserver2000, please use SQL to create a user temporary table and a system temporary table, which contains two fields: ID and idvalues. The types are both int type. What are the differences between them?
------------------------------------------
User temporary table: Create Table # XX (ID int, idvalues INT)
Temporary system table: Create Table # XX (ID int, idvalues INT)

Differences:
The user temporary table is only visible to the user's session who created the table and invisible to other processes.
The temporary table is automatically deleted when the process for creating it disappears.

The global temporary table is visible to the entire SQL server instance, but it is automatically deleted when all sessions accessing it disappear.

6. sqlserver2000 is a large database. Its storage capacity is limited only by the storage media. How does it implement this unlimited capacity mechanism.
------------------------------------------
All its data is stored in the data file (*. DBF), so as long as the file is large enough, the storage capacity of SQL Server can be expanded.

The SQL Server 2000 database has three types of files:

Main data files
The main data file is the starting point of the database and points to other parts of the file in the database. Each database has a primary data file. The recommended file extension for main data files is. MDF.

Secondary data file
The secondary data file contains all data files except the primary data file. Some databases may have no secondary data files, while some databases have multiple secondary data files. The recommended file extension for secondary data files is. NDF.

Log Files
The log file contains all the log information required to restore the database. Each database must have at least one log file, but more than one. The recommended file extension for log files is. LDF.

7. Use an SQL statement to obtain the result.
Retrieve the data in the format listed in table3 from Table1 and Table2. Note that the provided data and results are not accurate, but you can ask for advice as a format.
You can also use the stored procedure.

Table1

Month mon Department Dep performance YJ
-------------------------------
February 01 10
February 02 10
March January
March 02 8
March February
March

Table 2

Department Dep Department name dname
--------------------------------
01 domestic business 1
02 domestic business department 2
03 domestic business department 3
04 International Business Department

Table3 (result)

Department DEP, January, January
--------------------------------------
01 10 null
02 10 8 null
03 null 5 8
04 null 9

------------------------------------------
1)
Select a. Department name dname, B. Performance YJ as 'August 1', C. Performance YJ as 'August 1', and D. Performance YJ as 'August'
From Table1 A, Table2 B, Table2 C, Table2 d
Where a. Department Dep = B. Department Dep and B. Month MON = 'August 1' and
A. Department Dep = C. Department Dep and C. Month MON = 'August 1' and
A. Department Dep = D. Department Dep and D. Month MON = 'August 1' and
2)
Select a. Dep,
Sum (case when B. Mon = 1 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 2 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 3 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 4 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. MON = 5 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 6 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 7 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. MON = 8 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 9 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 10 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 11 then B. YJ else 0 end) as 'August 1 ',
Sum (case when B. Mon = 12 then B. YJ else 0 end) as 'August 1 ',
From Table2 a left join Table1 B on A. Dep = B. Dep

8. Interview Questions from Huawei
The ID in a table has multiple records. All the records of this ID are displayed, and the total number of records is displayed.
------------------------------------------
Select ID, count (*) from TB group by ID having count (*)> 1
Select * from (select count (ID) as count from Table group by ID) t where T. Count> 1