SQL Classic Questions and answers

Source: Internet
Author: User
Tags dname null null


1. A SQL statement interview question 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

Table Form is as follows:
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
Want query results in the following form
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
How do I write SQL statements?
Connection Query
SELECT b.year, SUM (a.salary) salary from Hello A, hello b WHERE a.year <= b.year GROUP by B.year

Sub-query
Select year, (select sum (Salary) from Hello as B where b.year<=a.year) from Hello as A



1. Use an SQL statement to find out the names of students who have more than 80 points per course

Name Kecheng Fenshu
Zhang San language 81
Zhang San Mathematics 75
John Doe Language 76
John Doe Mathematics 90
Harry Language 81
Harry Mathematics 100
Harry English 90

A:select distinct name from table where name is not in (select DISTINCT name from table where fenshu<=80)

2. The student table is as follows:
AutoNumber Number name Course number Course name score
1 2005001 Zhang 30,001 Math 69
2 2005002 li 40,001 Math 89
3 2005001 Zhang 30,001 Math 69
Delete the same student redundancy information except for the automatic numbering

A:delete tablename where auto number not in (select min (autonumber) from TableName Group by number, name, course number, course name, score)

A table called department, there is only one field name, a total of 4 records, respectively, is a,b,c,d, corresponding to four ball pairs, now four ball pairs to play, with an SQL statement to show all possible match combinations.
Would you like to do it your own way and see if the result is as simple as mine?

Answer: Select A.name, B.name
From Team A, Team B
where A.name < B.name



Use the SQL statement to: Query from the TESTDB data table that the occurrence of all months is higher than the corresponding month of the 101 account. Please note: There are many subjects in TestDB and there are 1-December occurrences.
Accid: Account code, Occmonth: The amount of the month, Debitoccur: The amount of the occurrence.
Database name: Jcyaudit, Data set: Select * from TestDB

Answer: Select A.*
From TestDB A
, (select Occmonth,max (debitoccur) debit101ccur from TestDB where accid= ' 101 ' GROUP by Occmonth) b
where A.occmonth=b.occmonth and A.debitoccur>b.debit101ccur

************************************************************************************

Interview question: How to put such a table
Year Month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
Chacheng such a result
Year M1 M2 M3 M4
1991 1.1 1.2) 1.3 1.4
1992 2.1 2.2) 2.3 2.4

Answer one,
Select year,
(select amount from AAA m where Month=1 and M.year=aaa.year) as M1,
(select amount from AAA m where month=2 and M.year=aaa.year) as M2,
(select amount from AAA m where month=3 and m.year=aaa.year) as M3,
(select amount from AAA m where month=4 and M.year=aaa.year) as M4
From AAA GROUP by year



This is done in Oracle:
SELECT * FROM (select name, Year B1, leads (year) over
(Partition by name order by year) B2, leads (m,2) over (partition by name order by year) B3,rank () over (
Partition by name, order by year, RK from T) where rk=1;

SQL Classic Questions and answers

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.