SQL Interview Questions

/*
Navicat MySQL Data Transfer


Source Server         : localhost
Source Server Version : 50024
Source Host           : localhost:3306
Source Database       : stu


Target Server Type    : MYSQL
Target Server Version : 50024
File Encoding         : 65001


Date: 2016-03-22 01:23:27
*/


SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `applycard`
-- ----------------------------
DROP TABLE IF EXISTS `applycard`;
CREATE TABLE `applycard` (
  `id` int(11) default NULL,
  `applydate` datetime default NULL,
  `applyremark` varchar(100) default NULL,
  `applynumber` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of applycard
-- ----------------------------
INSERT INTO `applycard` VALUES ('1', '2016-03-21 23:45:15', 'good', '100');
INSERT INTO `applycard` VALUES ('2', '2016-03-22 23:45:33', 'better', '200');
INSERT INTO `applycard` VALUES ('3', '2016-03-23 23:46:02', 'best', '300');


-- ----------------------------
-- Table structure for `applycarddetail`
-- ----------------------------
DROP TABLE IF EXISTS `applycarddetail`;
CREATE TABLE `applycarddetail` (
  `id` int(11) default NULL,
  `applyname` varchar(100) default NULL,
  `applycardid` varchar(100) default NULL,
  `applyaddress` varchar(100) default NULL,
  `applynumber` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of applycarddetail
-- ----------------------------
INSERT INTO `applycarddetail` VALUES ('1', '小明', '11111111111111111', '深圳市', '100');
INSERT INTO `applycarddetail` VALUES ('2', '小强', '22222222222222222', '耒阳市', '200');
INSERT INTO `applycarddetail` VALUES ('4', '小阳', '2222222', '2222222', '300');
INSERT INTO `applycarddetail` VALUES ('5', '小华', '66666666666666666', '小华市', '400');
INSERT INTO `applycarddetail` VALUES ('6', '小花', '66666666666666666', '校花市', '500');
INSERT INTO `applycarddetail` VALUES ('2', '小强', '22222222222222222', '耒阳市', '600');

--查询身份证号码为'11111111111111111的申请日期

#子查询 
select applydate from applycard where id=(select id from applycarddetail where applycardid='11111111111111111');
#左连接
select applydate from applycard  a left outer join applycarddetail  b on (a.id=b.id) where applycardid='11111111111111111';


--删除带“阳”字的记录
delete from applycarddetail where applyname like '阳%';


--删除姓"李"的记录
delete from applycarddetail where applyname like '李%';


--查询身份证出现两条以上的重复记录，并且统计出现次数
select *,count(0) AS 重复次数 from applycarddetail group by applycardid having count(applycardid) >= 2
#select [字段],count(0) AS 重复次数 from[表名] group by [字段] having count([字段]) > 2


--查询无重复记录，根据单个字段查询
select distinct * from applycarddetail order by id desc
#select distinct [字段] from [表名] order by [字段] desc


--同时更新两张表中的申请数量applynumber



--删除表中多余的重复记录，重复记录是根据单个字段来判断