“Mastering the SQL Language: The Key to Optimizing Your Data Processing Capabilities”

Directory

foreword

1. Combining two tables (left join)

2. Employees who earn more than their managers

3. Find Duplicate Emails

Summarize

Foreword

In today’s data age, SQL has become an indispensable data processing language. When performing tasks such as data analysis, data mining, and business development, we often need to use SQL to obtain the required data from the database. Understanding SQL algorithms is an essential step in order to improve your skills and deal with difficulties encountered in your work.

This blog mainly introduces the relevant knowledge of SQL algorithm questions, including what are SQL algorithm questions, their application scenarios, and how to improve your SQL skills through learning, so that you can use SQL more proficiently to solve practical problems. I believe that through reading this article, you will have a deeper understanding and acquire better SQL programming skills.

1. Combine two tables (left join)

175. Combine two tables

Difficulty easy

Table: Person

 + ------------- + --------- +
| column name | type |
 + ------------- + ---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
 + ------------- + ---------+
addressId is the primary key column of this table.
Each row of this table contains city and state information for a person with ID = PersonId.

Write a SQL query to report the last name, first name, city, and state of each person in the Person table. If the personId has an address that is not in the Address table, report empty null .

Return the table of results in arbitrary order.

The query result format is as follows.

Example 1:

<strong>Enter:</strong>
Person table:
 + ---------- + ---------- + ----------- +
| personId | lastName | firstName |
 + ---------- + ---------- + ----------- +
| 1 | Wang | Allen |
| 2 | Alice | Bob |
 + ---------- + ---------- + ----------- +
Address table:
 + ----------- + ---------- + --------------- + ---------- -- +
| addressId | personId | city | state |
 + ----------- + ---------- + --------------- + ---------- -- +
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
 + ----------- + ---------- + --------------- + ---------- -- +
<strong>Output:</strong>
 + ----------- + ---------- + --------------- + ---------- +
| firstName | lastName | city | state |
 + ----------- + ---------- + --------------- + ---------- +
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
 + ----------- + ---------- + --------------- + ---------- +
<strong>Explanation:</strong>
There are no addresses with personId = 1 in the address table, so their city and state returns null.
addressId = 1 contains the address information of personId = 2

--create table
Create table if not Exists Person(
personId int,
fristName varchar(255),
lastName varchar(255))
--Table II
Create table if not exists Address(
addressId int, personId INT
, city varchar(255), state varchar(255))
Truncate table Person;--clear the table
--Insert data
insert into Person(personId, fristName, lastName)
values('1','Wang','Allen')
insert into Person(personId, fristName, lastName)
values('2','Alice','Bob')
Truncate table Address
insert into Address(addressId, personId, city, state)
values('1','2','New York City','New York')
insert into Address(addressId, personId, city, state)
values('2','3','Leetcode','California')

Join two tables by left join

select fristName,lastName,city,state
from Person left join Address
on Person.personId = Address.personId;

left join — perform a left join operation

on Query the data in the right table according to the content in on, and return if found, otherwise it is null

Truncate table Person

–Truncate empty

Clear the data in the table, use with caution, cannot roll back

2. Employees earning more than their managers

181. Employees earning more than managers

Table: Employee

 + ------------- + --------- +
| Column Name | Type |
 + ------------- + ---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
 + ------------- + ---------+
Id is the primary key of this table.
Each row of this table represents an employee's ID, name, salary, and manager's ID.

Write a SQL query to find employees who earn more than their managers.

Return the table of results in arbitrary order.

The query result format is as follows.

Example 1:

<strong>Enter:</strong>
Employee table:
 + ---- + ------- + -------- + ----------- +
| id | name | salary | managerId |
 + ---- + ------- + -------- + ----------- +
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
 + ---- + ------- + -------- + ----------- +
<strong>Output:</strong>
 + ---------- +
| Employee |
 + ---------- +
| Joe |
 + ---------- +
<strong>Explanation:</strong> Joe is the only employee who earns more than the manager.

Solutions:

Create the table first:

Create table If Not Exists Employee(
id int,
name varchar(255),
salary int,
managerId int)
Truncate table Employee
insert into Employee(id,name,salary,managerId)
values('1','Joe','70000','3')
insert into Employee(id,name,salary,managerId)
values('2','Henry','80000','4')
insert into Employee(id,name,salary,managerId)
values('3','Sam','60000','100')
insert into Employee(id,name,salary,managerId)
values('4','Max','90000','100')

--Id is the primary key of the table.
--Each row of this table represents the employee's ID, name, salary, and manager's ID.

Table structure:

First of all, we can think of

Select * from
Employee AS a,
Employee As b
--As to alias the two tables
--Use select query in two tables, this way of writing will produce Cartesian product

Since we only want employees who earn more than the manager, use Where to do a filter

Select a.Name AS 'Employee'
from Employee AS a,Employee As b
where
   a.ManagerId = b.Id
       And a.salary>b.salary
--Since only one line of name is needed, change select * to select a.Name AS 'Employee'

Solution two:

Select a.Name AS 'Employee'
from Employee AS a Join Employee As b
     On a.ManagerId=b.Id
       And a.salary>b.salary
--Use Join to connect two tables, use on to do a conditional query

Three, find duplicate emails

182. Find Duplicate Emails

Table: Person

Write a SQL query to report all duplicate emails. Note that the email field is guaranteed not to be NULL.

Returns a table of results in arbitrary order.

The query result format is as follows.

Example 1:

<strong>Enter:</strong>
Person table:
 + ---- + --------- +
| id | email |
 + ---- + --------- +
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
 + ---- + --------- +
<strong>Output:</strong>
 + ---------+
| Email |
 + ---------+
| [email protected] |
 + ---------+
<strong>Explanation:</strong> [email protected] appears twice.

First create a table:

Create table If Not Exists Person1(
id int, email varchar(255))
Truncate table Person
insert into Person1 (id, email) values ('1', '[email protected]')
insert into Person1 (id, email) values ('2', '[email protected]')
insert into Person1 (id, email) values ('3', '[email protected]')

First, we use group by to do a grouping to get the number of emails every time, and use it as a temporary table to do a query

select email, count(email) as num
from person1
GROUP BY email;

Summary

SQL is a language used to manage and process relational databases, and its proficient application is particularly important for data processing capabilities. If you want to master the SQL language and use it to improve data processing capabilities, you need to pay attention to the following points:

1. Learning basic grammar: SQL language includes various statements such as query, insert, update, and delete. Mastering these basic grammars is the first step in learning SQL. It is recommended to continue to ponder and practice in practice to deepen understanding.

2. Learn to use functions and operators: Functions and operators in SQL can make queries and data processing more efficient and convenient, so it is very necessary to master them. For example, aggregate functions can quickly calculate averages, maximums, minimums, and so on, while wildcards can quickly match characters.

3. Optimize query performance: Executing queries in large data sets usually takes a long time, so query optimization in SQL is particularly important. In order to improve query performance, related technologies such as indexes can be used.

4. Know how to use multi-table query: Compared with single-table query, more complexity needs to be considered in multi-table query. Therefore, in-depth study of the syntax and skills of multi-table query is helpful to improve the application level of SQL.

In short, mastering the SQL language and being good at using it can not only improve the efficiency of data analysis and processing, but also gain more challenges and opportunities in data work.