Indexes
- Unique index: a unique index does not allow two rows to have the same index value
- Primary key index: defining a primary key for a table will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key is unique and cannot be empty
- Clustered index: the physical order of rows in the table is the same as the logical (index) order of key values. Each table can only have one
- Non clustered index: a non clustered index specifies the logical order of the table. The data is stored in one location, the index is stored in another location, and the index contains a pointer to the data storage location. There can be more than one, less than 249
- Joint index: it is an index composed of several fields, which is called joint index.
175 combine two tables
Table 1: Person +-------------+---------+ | Listing | type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId Is the primary key of the previous table Table 2: Address +-------------+---------+ | Listing | type | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId Is the primary key of the previous table
Write an SQL query that meets the following conditions: whether a person has address information or not, the following information of a person needs to be provided based on the above two tables:
FirstName, LastName, City, State
select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId
176. The second highest salary
Write a SQL Query, get Employee The second highest salary in the table( Salary) . +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ Should return +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
Note that the second highest salary may not exist. For example, only one person's salary is saved, so you need to use two select
select( select DISTINCT Salary from Employee order by Salary desc limit 1,1) as SecondHighestSalary
select ... limit start,rows
It means to start from the start+1 line, take out the rows line, and start is calculated from 0
181. Employees who exceed the manager's income
Employee The table contains all employees, and their manager also belongs to employees. Each employee has one Id，In addition, there is a list of managers corresponding to employees Id. +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+ given Employee Table, write a SQL Query, which can get the names of employees whose income exceeds their manager. In the table above, Joe Is the only employee who earns more than his manager. +----------+ | Employee | +----------+ | Joe | +----------+
select worker.Name as 'Employee' from Employee worker,Employee man where worker.ManagerId=man.ID and worker.Salary >man.Salary
197. Rising temperature
surface Weather +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id Is the primary key of this table This table contains temperature information for a specific date Write a SQL Query to find all dates with higher temperatures than previous (yesterday's) dates id . The returned results do not require order. The query result format is as follows: Weather +----+------------+-------------+ | id | recordDate | Temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+ Result table: +----+ | id | +----+ | 2 | | 4 | +----+ 2015-01-02 The temperature is higher than the previous day (10 -> 25) 2015-01-04 The temperature is higher than the previous day (20 -> 30)
Idea: the two tables are self connected, the data with a difference of only 1 day is screened, and then the temperature is compared
select a.id from Weather as a join Weather as b on datediff(a.recordDate ,b.recordDate )=1 where a.Temperature >b.Temperature
182. Find duplicate email addresses
Write a SQL Query, find Person All duplicate email addresses in the table. Example: +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ Based on the above input, your query should return the following results: +---------+ | Email | +---------+ | a@b.com | +---------+ Note: all email addresses are lowercase letters.
select Email from Person group by Email having count(Email)>1
183. Customers who never order
A website contains two tables, Customers Table and Orders Table. Write a SQL Query to find all customers who never order anything. Customers Table: +----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Orders Table: +----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ For example, given the above table, your query should return: +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
select Customers.Name as 'Customers' from Customers left join Orders on Customers.Id=Orders.CustomerId where Orders.Id is null
196. Delete duplicate e-mail addresses
Write a SQL Query to delete Person All duplicate e-mail addresses in the table are reserved in the duplicate e-mail addresses Id The smallest one. +----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id Is the primary key of this table. For example, after running your query, the above Person The table should return the following rows: +----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
delete p1 from Person as p1,Person as p2 where p1.Email=p2.Email and p1.Id>p2.Id
595. Large countries
Here's one World surface +-----------------+------------+------------+--------------+---------------+ | name | continent | area | population | gdp | +-----------------+------------+------------+--------------+---------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000 | | Albania | Europe | 28748 | 2831741 | 12960000 | | Algeria | Africa | 2381741 | 37100000 | 188681000 | | Andorra | Europe | 468 | 78115 | 3712000 | | Angola | Africa | 1246700 | 20609294 | 100990000 | +-----------------+------------+------------+--------------+---------------+ If a country has an area of more than 3 million square kilometers or a population of more than 25 million, then the country is a big country. Write a SQL Query and output the name, population and area of all large countries in the table. For example, according to the above table, we should output: +--------------+-------------+--------------+ | name | population | area | +--------------+-------------+--------------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +--------------+-------------+--------------+
select name,population,area from World where area>3000000 or population>25000000
596. Classes with more than five students
There is one courses Watch, yes: student (student) and class (curriculum). Please list all classes with more than or equal to 5 students. For example, table: +---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+ Should output: +---------+ | class | +---------+ | Math | +---------+
Note that some students may have chosen the heavy one and have to go to the heavy one
select class from courses group by class having count(distinct student)>=5
620. Interesting movies
As the director of the Information Department of the cinema, you need to write a SQL Query to find out all movies described as non boring (Not boring) And id Is an odd number of movies. Please rank the results rating Arrange. For example, the following table cinema: +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | +---------+-----------+--------------+-----------+ For the above example, the correct output is: +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
select * from cinema where mod(id,2)=1 and description !='boring' order by rating desc
627. Change of gender
Given a salary Table, as shown below, with m = Male and f = Female values. Swap all f and m Value (for example, all f Change value to m，Vice versa). Requires only one update( Update)Statement, and there is no intermediate temporary table. Note that you must only write one Update Statement, please do not write any Select sentence. For example: | id | name | sex | salary | |----|------|-----|--------| | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | After running the update statement you wrote, you will get the following table: | id | name | sex | salary | |----|------|-----|--------| | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 |
update salary set sex=case sex when 'm' then 'f' else 'm' end
178. Score ranking
Write a SQL Query to achieve score ranking. If the two scores are the same, the two scores are ranked( Rank)Same. Please note that the next ranking after bisection should be the next consecutive integer value. In other words, there should be no "interval" between ranking. +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ For example, according to the above given Scores Table, your query should return (sorted from high to low): +-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
Sort:
1.rank() over: rank the students after finding out the specified conditions. The feature is that joining is to rank the students. Using this function, the two students with the same score are tied, and the next student is left out. The null value is the largest.
2. Deny_rank () over: the difference between deny_rank () over and ran() over is that after two students' grades are tied, the next student does not empty out the ranking. It is applicable to this problem.
3.row_number() over this function does not need to consider whether it is juxtaposed. Even if the values queried according to the conditions are the same, it will be ranked continuously
select Score,dense_Rank() over(order by Score desc) as 'Rank' from Scores