项目三:
1),创建courses 表 ,包含属性 student,class:
create table courses(
-> student char(1) not null,
-> class varchar(225) not null);
2),插入数据:
insert into courses values('A', 'Math');
select * from courses;
3), SQL 查询,列出所有超过或等于5名学生的课:
select distinct class from courses
group by class having count(student) > 5;
项目四:
1),创建一个 salary 表:
create table salary(
-> id int not null primary key,
-> name varchar(6) not null,
-> sex char(1) not null,
-> salary int not null);
2),插入数据:
insert into salary values('1','A','m','2500');
select * from salary;
3),交换所有的 f 和 m 值:
update salary set sex = case sex when 'f' then 'm' else 'f' end;
select * from salary;
项目五:
1),在数据库中创建表Person,Address,并各插入三行数据,
如下:
2),SQL 查询,满足条件:
无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State。
select Person.First_name, Person.Last_name,
Address.City, Address.State
from Person left join Address
on Person.Person_id = Address.Person_id
order by Person.First_name;
项目六:
SQL 查询,来删除 email 表中所有重复的电子邮箱,
重复的邮箱里只保留 Id 最小 的那个
1),利用临时表:
create temporary table T1 as select min(id), Email from email group by Email;
truncate table email;
insert into email select * from T1;
select * from email;
2),直接在原表上面操作:
delete from email where id not
in (select min_id from (select min(id)
as min_id from email group by Email));
本文分享自 MiningAlgorithms 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!