Several SQL pen questions, Interview questions summary

2017-08-01

Four or five companies were interviewed in the last one weeks, and the job interview was a Java intern. found that each company's SQL knowledge of the content of the study is not the same, and their own SQL knowledge is a weakness. For this, in the rookie tutorial reviewed the SQL knowledge, but also summed up the interview topics for the past few days.

Written test finishing

1. For a table similar to the following, require SQL to find the average of different gender scores over 80 points:

Table: Test_avg

NAME	Score	SEX	CLASS
A	66	Man	1
B	86	Woman	2
C	90	Man	1
D	82	Woman	2

Examine the Knowledge points:

1) AVG () function. the AVG function returns the average of a numeric column. NULL values are not included in the calculation.

2)GROUP by. The GROUP BY statement is used in conjunction with aggregate functions to group result sets based on one or more columns (note the difference between grouping and sorting).

SELECT Class,sex,AVGfromWHERE>aGROUP by SEX, CLASS;

2, to similar to the following two tables, find out all the names of the employee and its boss name, no assignment is "unknown":

Table: Employee

Id	Name	leader_id
1	Aaa	Null
2	Bbb	1
3	Ccc	2
4	Ddd	1

Table: Boss

Id	Name
1	A
2	B

Examine the Knowledge points:

1)ifnull () function . Ifnull (expression, fill data) function: If the data is Null, fill it with fill data.

2) left connection . For example: Table1 the left JOIN table2 The on limit condition, the Table1 data is fully checked out.

SELECT employee. ' Name ', Ifnull (boss.' ) name ',' unknown 'as from the leftJOIN on = boss.id;

3. Build a table with SQL statements:

Here is a table for me, note the engine of the table, character set encoding , etc.

CREATE TABLE IF  not EXISTS 'table1' (    'ID' INTUNSIGNED auto_increment,'title' VARCHAR( -) not NULL,    'author' VARCHAR( +) not NULL,    'submission_date'DATE,PRIMARY KEY('ID') ) ENGINE=InnoDBDEFAULTCHARSET=Utf-8;

4, how to query a table last 10 records of the first 7 records?

Idea: In sub-query with reverse by ID (because the primary key ID is definitely in order) the last 10 records, and then use the LIMIT method to query the top 7

Examine the Knowledge points:

1) Thinking can be flexible, the use of reverse query the last 10 records.

2)LIMIT paging . Syntax: SELECT * FROM table LIMIT [offset,] rows. Where offsets are offset, optionally, the base 0;rows is the number of rows queried.

SELECT  from (SELECTfrom orderbyDESC  by7

Interview Finishing

1. How to understand the database paradigm ?

• Paradigm (NF): "A set of relational patterns that conform to a level that represents the degree of rationalization of the linkages between attributes within a relationship". Pretty obscure, huh? In fact, you can roughly interpret it as a level of design criteria that conforms to the table structure of a data table. ( my brief summary: the degree of rationalization between the properties of the table )
• The database paradigm is also divided into 1nf,2nf,3nf,bcnf,4nf,5nf. a design that conforms to a high-level paradigm must conform to a low-level paradigm.
• 1NF is defined as: each attribute in a relationship that conforms to 1NF cannot be divided . (1NF is the most basic requirement for all relational databases)
• 1NF Disadvantage: Data redundancy is too large , insert exception, delete exception, modify the problem of the exception
• 2NF is based on 1NF, eliminating the partial function dependency of non-main attribute on code.
• 3NF on the basis of 2NF, eliminate the non-main property of the code transfer function Dependency .

2. How is MySQL paged? What are the drawbacks?

MySQL uses the LIMIT keyword for paging operations. The description is indicated in the 4th above.

Disadvantage: Word, is the more the next page , the LIMIT statement offset will be greater, the speed will be significantly slower . At this point, we can improve paging efficiency by sub-query , roughly as follows:

SELECT *  from WHERE >= (SELECT from articles  WHERE = 123 ORDER  by 10000 1 Ten

3. How does the MySQL index be implemented? What's the problem?

Simply understood, the index is to speed up the query . The index is stored independently, so when the amount of data is large, updating the table to update the index at the same time, causing the update speed down .

Here I have referred to two articles, the address is as follows:

http://fangjian0423.github.io/2017/07/05/mysql-index-summary/#more

1190000003072424

After several interviews, know the lack of their own SQL knowledge, now summed up and a bad supplement, feeling a lot of clear ideas. SQL knowledge is generally used functions, additions and deletions, constraints, the use of data types, to really remember in mind or to write more practical code. You can write SQL statements in the command-line window as much as possible to deepen the impression and move away from the visualization tools.

I hope my summary is helpful to you, too. :)

Several SQL pen questions, Interview questions summary