- 博客(13)
- 收藏
- 关注
原创 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 182
原创 620. Not Boring Movies
Problem:Write an SQL query to report the movies with an odd-numbered ID and a description that is not"boring". Return the result tablein descending order byrating. Cinema table: +----+------------+-------------+--------+ | id | movie | descripti...
2021-07-22 18:21:56 122
原创 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 65
原创 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 132
原创 595. Big Countries
Problem:There is a table World. +-----------------+------------+------------+--------------+---------------+ | name | continent | area | population | gdp | +-----------------+------------+------------+--------------+--------
2021-07-17 10:36:40 170
原创 Subquery 子查询
即SELECT语句中嵌套SELECT语句 WHERE语句中嵌套子查询 FROM语句中嵌套子查询 SELECT语句中嵌套子查询 1. WHERE Problem: 找出高于平均薪资的员工信息。 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 251
原创 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 98
原创 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 62
原创 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 186
原创 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 239
原创 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 135
原创 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 113
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人