Useful Questions For Mysql and useful questions for Mysql

Useful Questions For Mysql and useful questions for Mysql

A. 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 ')
========================================================== ======================================
SELECT rq, sum (shengfu = 'sheng') as 'sheng', sum (shengfu = 'negative ') as 'negative'
FROM '# tmp'
Group by '# tmp'. rq
**************************************** *************************

B. 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 taname
**************************************** ***********

C. 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)
Bytes -----------------------------------------------------------------------------------------------------------------------
Select * from time where datediff (SendTime, CURDATE () = 0

CURDATE () Get the current date
DATEDIFF () returns the number of days between the start time expr and the end time expr2. Expr and expr2 are date or date-and-time expressions. Only the date portion of these values is used in the calculation.

TIMEDIFF (expr, expr2)
TIMEDIFF () returns the time between the start time expr and the end time expr2. Expr and expr2 are time or date-and-time expressions. The two types must be the same.
**************************************** **************************************** **************************************** **

D. 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
----------------------------------------------------------------------
1. there must be three categories: Chinese, mathematics, and English.
2. Determine the queried score
3. First, find a field to display
SELECT
CASE WHEN
Yuwen> = '80' then' excellent 'when yuwen> = '60' then' pass 'else' fail 'end yuwen,
CASE WHEN
Shuxue> = '80' then' excellent 'when shuxue> = '60' then' pass 'else' fail 'end shuxue,
CASE WHEN
Yingyu> = '80' then' excellent 'when yingyu> = '60' then' pass 'else' fail 'end yingyu
FROM
Kecheng

E. 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
------------------------------------------

Select a. dname, 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'
From table2 a left join table1 B on a. dep = B. dep
GROUP
A. dep

 

 

F. One interview question
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

G. The table format is as follows:
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
Obtain the following query results:
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
How to Write SQL statements?
****************************************
SELECT a. year, SUM (B. salary) AS sala
FROM table AS a, table AS B
WHERE B. salary <= a. salary
Group by a. salary
**************************************** ***********
H. Use an SQL statement to query the names of students whose scores are greater than 80 in each course.
Name kecheng fenshu
Zhang San Language 81
James math 75
Li Si language 76
Li Si mathematics 90
Wang Wu language 81
Wang Wu, mathematics 100
Wang Wu English 90

**************************************** ************************************
Selet name from tablename where name in (select name from tablename where fenshu> 80)
Select distinct name from table where name not in (select distinct name from table where fenshu <= 80)

 

**************************************** **********
I. The student table is as follows:
Automatic student ID name course number Course name score
1 2005001 Zhang San 0001 mathematics 69
2 2005002 Li Si 0001 mathematics 89
3 2005001 Zhang San 0001 mathematics 69
Delete redundant student information that is identical except for automatic numbers
**************************************** ***************
Delete tablename where automatic number not in () can run
Select min (automatic number) from tablename group by student ID, name, course number, course name, score can run
Delete tablename where automatic number not in (select min (automatic number) from tablename group by student ID, name, course number, course name, score)

Cannot run. An error like You can't specify target table 'tb' for update in FROM clause will be reported.
Bytes ----------------------------------------------------------------------------------------------------------------------------
J. a table Named department has only one field name and a total of four records, which are a, B, c, and d, corresponding to four ball pairs. Now, four ball pairs are playing, use an SQL statement to display all possible competition combinations.
Let's start with your own ideas. Is there a simple result for me?
--------------------------------------------------
A: select a. name, B. name
From team a, team B
Where a. name <B. name


**************************************** **************************************** **************************************** ********
K. Use an SQL statement to query the tables in the TestDB table that show the subjects with a higher number of months than those in the corresponding month of the 101 subject. Note: There are many subjects in TestDB, all of which have the amount of 1-12 months.
AccID: Subject code, Occmonth: month of occurrence, DebitOccur: occurrence.
Database Name: JcyAudit, Dataset: Select * from TestDB

A: select .*
From TestDB
, (Select Occmonth, max (DebitOccur) Debit101ccur from TestDB where AccID = '000000' group by Occmonth) B
Where a. Occmonth = B. Occmonth and a. DebitOccur> B. Debit101ccur

 

**************************************** **************************************** ****
L. interview questions: 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
Query such a result
Year m 1 M2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

**************************************** **************
Answer 1,
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, lead (year) over
(Partition by name order by year) b2, lead (m, 2) over (partition by name order by year) b3, rank () over (
Partition by name order by year) rk from t) where rk = 1;