MySQL non member simple question set 2021-11-08

Posted by zebrax on Mon, 08 Nov 2021 07:38:30 +0100

175. Combine two tables

SELECT FirstName, LastName, City, State 
From Person LEFT OUTER JOIN Address
ON Person.PersonId = Address.PersonId;

Chapter 16 of MySQL must know and know exercises related to creating advanced connections

  1. customers table and orders table

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-IvAfEE0b-1636354669755)(C:\Users\Administrator\Desktop\MySQL\customers.jpg)]

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-fweldsp-1636354669764) (C: \ users \ administrator \ desktop \ MySQL \ orders. JPG)]

  1. Cust is used for internal connection and external connection respectively_ ID join two tables
  • inner Join 
SELECT customers.cust_id, orders.order_num 
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-9c8KEyxH-1636354669769)(C:\Users\Administrator\Desktop\MySQL\INNER.jpg)]

  • outer join 
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-8hGDOIqE-1636354669780)(C:\Users\Administrator\Desktop\MySQL\OUTER.jpg)]

Thus, the internal connection is to cust_id takes the intersection. The external connection is to cust_id union set, cust existing in the customers table_ ID does not exist in the orders table, so there is no corresponding order_ Num (because order_num comes from orders), so it is represented by NULL when merging.

Go back to 175. Merge the two tables. The title requires that the City and State should be provided regardless of whether the person has Address information. Therefore, it can be seen that some personids may not appear in the Address table, so external connection is required.

Knowledge points:

  1. INNER ON
  2. External connection outer on (sometimes LEFT or right is required)

181. Employees who exceed the manager's income

SELECT E1.Name AS Employee
FROM Employee AS E1, Employee AS E2
WHERE E1.ManagerId = E2.Id 
	AND E1.Salary > E2.Salary;

Chapter 16 of MySQL must know and be able to create advanced connection and self connection

Official solution 2:

SELECT E1.Name AS Employee
FROM Employee AS E1 JOIN Employee AS E2
	 ON E1.ManagerId = E2.Id
	 AND E1.Salary > E2.Salary;

Knowledge points:

  1. Self coupling
  2. JOIN ... ON ...

182. Find duplicate email addresses

  1. My solution
SELECT DISTINCT P1.Email 
FROM Person AS P1, Person AS P2
WHERE P1.Id != P2.ID 
    AND P1.Email = P2.Email;

Idea: because it is in a table, when the id is different and the Email is the same, it indicates that the mailbox is repeated, using self connection. Because only one duplicate Email is returned, the qualifier DISTINCT is added.

  1. Official solution 1: use GROUP BY and temporary table

    • First, calculate the number of each Email (GROUP BY is required) and store it in the temporary table. The reason for this is that Email can only appear once.

    • Then extract the Email column with num value greater than 1 from the generated temporary table.

    • Note: Every derived table must have its own alias exception will be reported if the temporary table is not named.

SELECT Email, COUNT(Eamil) AS num
FROM Person
GROUP BY Email;

SELECT Email FROM 
(SELECT Email, COUNT(Email) AS num
 FROM Person GROUP BY Email) AS temp
WHERE num > 1;
  1. Official solution 2: use GROUP BY and HAVING conditions
    • The difference between WHERE and HAVING: WHERE filters rows and HAVING filters groups.
    • Note: you need to group before you can use COUNT.
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

Reference: Chapter 13 of MySQL must know

183. Customers who never order

Note: the same name may be different ID, so CustomerId is the unique identification code.

  1. My solution
    • First find the ID of the customer with the order record
    • Then remove the customer ID with ordering record from all customer IDs
SELECT Customers.Id FROM Customers, Orders
WHERE Orders.CustomerId = Customers.Id;

SELECT Name AS Customers FROM Customers
WHERE Id NOT IN
(SELECT Customers.Id FROM Customers, Orders
WHERE Orders.CustomerId = Customers.Id);
  1. Official solution
SELECT CustomersId FROM Orders;

SELECT Name AS Customers From Customers
WHERE ID NOT IN
(SELECT CustomerId FROM Orders);

Difference: I took a detour.

196. Delete duplicate e-mail addresses

Note: the title outputs the Person table, so you need to delete the duplicate e-mail in the original table.

  • Official solution: use DELETE and WHERE clauses
    • First, SELECT the lines with the same Email but large Id with self association
    • Rewrite the SELECT statement to the DELETE statement
SELECT P1.* FROM Person AS P1, Person AS P2
WHERE P1.Id > P2.Id 
	AND P1.Email = P2.Email; 

DELETE P1 FROM Person AS P1, Person AS P2
WHERE P1.Id > P2.Id
	AND P1.Email = P2.Email;

This statement means to delete those P1 rows that meet the conditions from the Person table.

This problem is not done by yourself. It needs to be reviewed!!!

197. Rising temperature

Note: the date does not necessarily increase with the increase of Id, so you cannot start from the Id column.

Self connection is still used. When the difference between days is one and the temperature meets the conditions, SELECT.

SELECT W1.id FROM Weather W1, Weather W2
WHERE W1.Temperature > W2.Temperature
	AND DATEDIFF(W1.recordDate, W2.recordDate) = 1;

Note: the DATEDIFF date function is used here to return the difference between two dates.

596. Classes with more than five students

  • First, calculate the number of candidates for each course and generate a temporary table
SELECT class, COUNT(class) AS num
FROM courses GROUP BY class
  • Then select the courses with num > 5 from the temporary table
SELECT class FROM 
(SELECT class, COUNT(class) AS num
FROM courses GROUP BY class) AS temp
WHERE num >= 5;

The aggregation in the official problem solution is COUNT(DISTINCT student)

620. Interesting movies

SELECT * FROM cinema
WHERE description != 'boring' 
	AND id % 2 = 1
ORDER BY -rating;

Two knowledge points:

  1. Odd numbers can be judged by mod(id, 2) = 1
  2. ORDER BY... DESC can be used for descending order

627. Change of gender

  • The title is required to be modified in the original table and updated
  • The usage of new judgment sentences
UPDATE Salary 
SET sex = IF(sex='m', 'f', 'm')

UPDATE Salary
SET sex = CASE sex
		    WHEN 'm' THEN 'f'
		    ELSE 'm'
          END;
  • MySQL CASE... WHEN usage
CASE sex
	 WHEN '1' THEN 'M'
	 WHEN '2' THEN 'F'
	 ELSE 'MF'
	 END;

1179. Reformat department table

  • Why use SUM?
  • When there are multiple data in a cell, CASE WHEN will only extract the first data.
  • GROUP BY id enables you to GROUP BY id and generate a virtual table (an imaginary table). In the virtual table, all revenue or month data with id=1 are written in the same cell. For example, 8000, 7000 and 6000 are written in the same cell. Real tables cannot be written like this, so this writing method only exists in virtual tables to help us understand.

Author: xxiao053
Link: https://leetcode-cn.com/problems/reformat-department-table/solution/guan-yu-group-byyu-sumde-pei-he-by-xxiao053/
Source: LeetCode

SELECT id, 
    SUM(CASE month WHEN 'Jan' THEN Revenue END) AS 'Jan_Revenue',
    SUM(CASE month WHEN 'Feb' THEN Revenue END) AS 'Feb_Revenue',
    SUM(CASE month WHEN 'Mar' THEN Revenue END) AS 'Mar_Revenue',
    SUM(CASE month WHEN 'Apr' THEN Revenue END) AS 'Apr_Revenue',
    SUM(CASE month WHEN 'May' THEN Revenue END) AS 'May_Revenue',
    SUM(CASE month WHEN 'Jun' THEN Revenue END) AS 'Jun_Revenue',
    SUM(CASE month WHEN 'Jul' THEN Revenue END) AS 'Jul_Revenue',
    SUM(CASE month WHEN 'Aug' THEN Revenue END) AS 'Aug_Revenue',
    SUM(CASE month WHEN 'Sep' THEN Revenue END) AS 'Sep_Revenue',
    SUM(CASE month WHEN 'Oct' THEN Revenue END) AS 'Oct_Revenue',
    SUM(CASE month WHEN 'Nov' THEN Revenue END) AS 'Nov_Revenue',
    SUM(CASE month WHEN 'Dec' THEN Revenue END) AS 'Dec_Revenue'
FROM Department
GROUP BY id

Topics: Database MySQL leetcode

Hot Topics

©2024 Programmer Think