MySQL
SDS_19
这个作者很懒,什么都没留下…
展开
-
196. Delete Duplicate Emails
Problem:Write a SQL query todeleteall duplicate email entries in a table namedPerson, keeping only unique emails based on itssmallestId.+----+------------------+| Id | Email |+----+------------------+| 1 | john@example.com || 2 | ...原创 2021-07-24 10:03:00 · 154 阅读 · 0 评论 -
596. Classes More Than 5 Students
Poblem:Please list out all classes which have more than or equal to 5 students.+---------+------------+ should output: +---------+| student | class | | class |+---------+------------+ +---------+| A .原创 2021-07-22 15:54:01 · 56 阅读 · 0 评论 -
197. Rising Temperature
Problem:Write an SQL query to find all dates'idwith higher temperature compared to its previous dates (yesterday).Return the result table inany order.The query result format is in the following example:Weather+----+------------+-------------+|...原创 2021-07-18 10:52:13 · 122 阅读 · 0 评论 -
595. Big Countries
Problem:There is a table World.+-----------------+------------+------------+--------------+---------------+| name | continent | area | population | gdp |+-----------------+------------+------------+--------------+--------原创 2021-07-17 10:36:40 · 142 阅读 · 0 评论 -
Subquery 子查询
即SELECT语句中嵌套SELECT语句WHERE语句中嵌套子查询 FROM语句中嵌套子查询 SELECT语句中嵌套子查询1. WHEREProblem: 找出高于平均薪资的员工信息。mysql> SELECT avg(sal) FROM emp;+-------------+| avg(sal) |+-------------+| 2073.214286 |+-------------+1 row in set (0.00 sec)mysql> SEL原创 2021-07-17 10:15:38 · 231 阅读 · 0 评论 -
183. Customers Who Never Order
Problem: Suppose that a website contains two tables, theCustomerstable and theOrderstable. Write a SQL query to find all customers who never order anything.SELECT CustomerId FROM Orders;+------------+| CustomerId |+------------+| 3 ||...原创 2021-07-17 08:36:45 · 71 阅读 · 0 评论 -
182. Duplicate Emails
Problem: Write a SQL query to find all duplicate emails in a table named Person.For example, your query should return the following for the above table:+----+---------+ +---------+| Id | Email | | Email |+----+---------+ +---------+| 1 .原创 2021-07-16 20:57:39 · 50 阅读 · 0 评论 -
Group function + GROUP BY + HAVING
分组函数分组函数又被称为“多行处理函数”,即输入多行,输出结果是一行。COUNT( ) 计数 SUM( ) 求和 AVG( ) 求平均值 MAX( ) 求最大值 MIN( ) 求最小值Problem: 找出员工的工资总和。mysql> select sum(sal) from emp;+----------+| sum(sal) |+----------+| 29025.00 |+----------+1 row in set (0.00 sec)Probl原创 2021-07-16 20:15:44 · 175 阅读 · 0 评论 -
JOIN Clause 连接查询
笛卡尔积现象查询前先从笛卡尔积现象开始考虑内连接(1) 等值连接:表连接条件是等量关系SQL99 Syntax:... A (inner) join B on 表连接条件 where ...SQL99语法优于SQL92语法,因为SQL99将表连接条件(join)与数据过滤条件(where)分离,SQL99语法结构更清晰Problem:查询每个员工的部门名称,显示员工名和部门名。mysql> select ename,deptno from emp; mysq.原创 2021-07-16 15:53:08 · 204 阅读 · 0 评论 -
181. Employees Earning More Than Their Managers
Problem: Given the Employee table, write a SQL query that finds out employees who earn more than their managers.Employee+----+-------+--------+-----------+| Id | Name | Salary | ManagerId |+----+-------+--------+-----------+| 1 | Joe | 70000 |.原创 2021-07-15 17:20:31 · 107 阅读 · 0 评论 -
175. Combine Two Tables
单行处理函数:IFNULL(可能为NULL的数据,替代值)原创 2021-07-15 18:45:44 · 48 阅读 · 0 评论 -
176. Second Highest Salary
Problem: Write a SQL query to get the second highest salary from the Employee table. If there is no second highest salary, then the query should return null.+----+--------+| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |原创 2021-07-13 21:08:03 · 88 阅读 · 0 评论