Item 3: Lessons for more than 5 students
Create courses tables as follows: student and class.
For example, table:
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
+---------+------------+
Write an SQL query that lists all classes with more than or equal to five students.
Should output:
+---------+
| class |
+---------+
| Math |
+---------+
Note:
Students should not be recalculated in each class.
--Establish courses surface
CREATE TABLE courses(
student VARCHAR(255),
class VARCHAR(255)
);
--insert data
INSERT INTO courses(student,class)
VALUES('A','Math'),('B','English'),('C','Math'),('D','Biology'),
('E','Math'),('F','Computer'),('G','Math'),('H','Math'),
('I','Math'),('A','Math');
--List all classes for more than or equal to five students,Students should not be recalculated in each class.
select class from courses group by class having count(distinct student) >= 5;
--The following sentence has one more column of records than the following count
select class,count(distinct student) as count from courses group by class having count(distinct student)>=5;
Item 4: Exchange of wages
Create a salary table with m = male and f = female values, as shown below.
For example:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
Exchange all f and m values (for example, change all f values to m, and vice versa). An update query is required and there is no intermediate temporary table.
After running the query statement you have written, 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 |
Item 5: Interesting Movies
A new cinema opened in a city, which attracted many people to come to see the film. The cinema pays special attention to the user experience and has a special LED display board for movie recommendation, which publishes film reviews and related movie descriptions.
As the director of the Information Department of the cinema, you need to write an SQL query to find out all the movies described as non-boring (not boring) and odd-numbered with id. The results should be ranked by rating.
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 |
+---------+-----------+--------------+-----------+
--Establish cinema surface
CREATE TABLE cinema(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
movie VARCHAR(255),
description VARCHAR(255),
rating FLOAT
); --AUTO_INCREMENT Definitions are classified as self-increasing attributes, usually used for primary keys, and the value is automatically added by 1.
--insert data
INSERT INTO cinema(movie,description,rating)
VALUES('War','great 3D','8.9'),('Science','fiction','8.5'),
('irish','boring','6.2'),('Ice Song','Fantacy','8.2'),
('House card','interesting','9.1');
--Find out all the movies described as non- boring (Not bored) And id For odd-numbered films, the results should be graded rating Arrangement.
select * from cinema where description != 'boring' and id%2 = 1 ORDER BY rating DESC;
Item 6: Combination of two tables
Create Tables 1 and 2 in the database and insert three rows of data each (self-made)
Table 1: Person
+-------------+---------+
| column name | type|
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key of the upper table
Table 2: Address
+-------------+---------+
| column name | type|
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key of the table above
Write an SQL query to satisfy the requirement that whether or not the person has address information, the following information of the person should be provided based on the above two tables: FirstName, LastName, City, State.
Item 7: Delete duplicate mailboxes
Write an SQL query to delete all duplicate e-mails in the email table. Only the one with the smallest Id is retained in the duplicate e-mails.
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
Id is the primary key of this table.
For example, after running your query statement, the Person table above should return the following rows:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | a@b.com |
| 2 | c@d.com |
+----+------------------+
--Establish email form
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255)
);
--insert data
INSERT INTO email(ID, Email)
VALUES ('1','a@b.com'),
('2','c@d.com'),
('3','a@b.com');
--lookup Email All duplicate e-mails in the table.
select Email,count(*) as count from email group by Email having count>1;
--Delete duplicate mailboxes and keep only duplicate mailboxes Id The smallest one.
DELETE e1 FROM email e1,email e2 WHERE e1.Email = e2.Email AND e1.ID > e2.IdD
delete from email where ID not in (select minid from (select min(ID) as minid from email group by Email) b);
-
Item 8: Customers who never order
A website contains two tables, Customers table and Orders table. Write an SQL query to find out all customers who never ordered 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 |
+-----------+
CREATE DATABASE IF NOT EXISTS data3 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
use data3;
--Establish Customers form
CREATE TABLE Customers (
ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255)
);
INSERT INTO Customers(Name)
VALUES('Joe'),('Henry'),('Sam'),('Max');
--Establish Orders form
CREATE TABLE Orders (
ID INT NOT NULL PRIMARY KEY,
CustomerId INT
);
INSERT INTO Orders(ID,CustomerId)
VALUES('1','3'),('2','1');
--Find out all the customers who never ordered anything.
SELECT Name from Customers left join Orders ON Orders.CustomerId = Customers.ID where Orders.ID is Null;
Item 9: Employees Over Manager's Income
The Employee table contains all employees, and their managers belong to employees. Each employee has an Id, in addition to a list of managers'Ids corresponding to the employee.
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
Given the Employee table, write an SQL query that gets the names of employees whose incomes exceed their managers. In the table above, Joe is the only employee whose income exceeds that of his manager.
+----------+
| Employee |
+----------+
| Joe |
+----------+
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)