MySQL DML操作強化練習問題
44428 ワード
MySQL DML操作強化練習問題
タイトル1
Empデータ情報
答え
タイトル2
2つのテーブルデータ情報
答え
タイトル1
1. emp[ ]， ： 、 、 、 、
、 、 、 ； ：
A.
B. , ,
C.
D. 18 20 ,
E. , ,
F. 、 、 ,
G. , ,
H. , 15 25
I.
J.
K.
L. ，
M.
N.
Empデータ情報
drop table if exists emp;
create table emp
(
id varchar(12),
name varchar(12),
sex char(2),
age int,
phone varchar(11),
address varchar(20),
times timestamp,
email varchar(20)
);
insert into emp values('A100101',' ',' ',58,'13111223344',' ','2000-1-1','[email protected]');
insert into emp values('A100102',' ',' ',38,'13111223344',' ','2000-1-1','[email protected]');
insert into emp values('A100103',' ',' ',28,'13111223344',' ','2001-1-1','[email protected]');
insert into emp values('A100104',' ',' ',27,'13111223344',' ','2002-1-1','[email protected]');
insert into emp values('A100105',' ',' ',18,'13111223344',' ','2010-1-1','[email protected]');
insert into emp values('A100106',' ',' ',19,'13111223344',' ','2011-1-1','[email protected]');
insert into emp values('A100107',' ',' ',20,'13111223344',' ','2015-1-1','[email protected]');
insert into emp values('A100106',' ',' ',19,'13111223344',' ','2011-1-1','[email protected]');
select * from emp;
答え
#
#A.
select * from emp;
#B. , ,
SELECT NAME,PHONE,EMAIL FROM EMP;
#C.
SELECT * FROM EMP WHERE SEX=' ';
#D. 18 20 ,
SELECT name,sex FROM emp WHERE age BETWEEN 18 AND 20;
#E. , ,
SELECT name,phone,address FROM emp WHERE sex=' ' AND address=' ';
#F. 、 、 ,
SELECT name,phone,address FROM emp WHERE name=' ' or name=' ' or name=' ';
#G. , ,
SELECT name,sex,age,address FROM emp WHERE address=' ' or address=' ';
#H. , 15 25
SELECT name FROM emp WHERE address=' ' AND sex=' ' AND age BETWEEN 15 AND 25;
#I.
SELECT * FROM emp WHERE email IS NULL;
#J.
SELECT * FROM emp WHERE TIMESTAMPDIFF(YEAR,times,now())>2;
#K.
SELECT COUNT(SEX) FROM emp WHERE sex=' ';
SELECT COUNT(SEX) FROM emp WHERE sex=' ';
#L. ，
SELECT SUM(age) FROM emp WHERE sex=' ';
SELECT SUM(age) FROM emp WHERE sex=' ';
#M.
SELECT MIN(age) FROM emp WHERE address=' ';
#N.
SELECT SUM(age) FROM emp;
タイトル2
2. dept emps[ ]， ：
A.
B. 12
C.
D. 6
E. "R"
F.
G. ， "a" "A"
H. 10
I. ，
J. ， ， 。
K. 、 ， ，
L. ， ，
M. 30 ，
N. （ ）2
O. ，
P. "A"
Q. 、
2つのテーブルデータ情報
DROP TABLE IF EXISTS EMPS;
DROP TABLE IF EXISTS DEPT;
CREATE TABLE DEPT
(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
CREATE TABLE EMPS
(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT,
HIREDATE TIMESTAMP,
SAL FLOAT,
COMM FLOAT,
DEPTNO INT,
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);
INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');
INSERT INTO EMPS VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMPS VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMPS VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMPS VALUES(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO EMPS VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMPS VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO EMPS VALUES(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO EMPS VALUES(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);
INSERT INTO EMPS VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMPS VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO EMPS VALUES(7876,'ADAMS','CLERK',7788,'1987-5-23',1100,NULL,20);
INSERT INTO EMPS VALUES(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO EMPS VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO EMPS VALUES(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
SELECT * FROM DEPT;
SELECT * FROM EMPS;
答え
#
#A.
SELECT * FROM EMPS WHERE HIREDATE=LAST_DAY(HIREDATE);
#B. 12
SELECT * FROM EMPS WHERE TIMESTAMPDIFF(YEAR,HIREDATE,NOW())>12;
#C.
#regexp
#binary MySQL
# ^
# $
# \ [A-Z]
SELECT ENAME FROM EMPS WHERE ENAME REGEXP BINARY '^\[A-Z]';
#D. 6
SELECT ENAME FROM EMPS WHERE LENGTH(ENAME)=6;
#E. "R"
SELECT ENAME FROM EMPS WHERE ENAME NOT LIKE '%R%';
#F.
SELECT SUBSTR(ENAME FROM 1 FOR 3) FROM EMPS;
#G. ， "a" "A"
SELECT REPLACE(ENAME,'A','a') FROM EMPS;
#H. 10
SELECT ENAME,ADDDATE(HIREDATE,INTERVAL 10 YEAR) FROM EMPS;
#I. ，
SELECT * FROM EMPS ORDER BY ENAME;
# ， ， 。
SELECT ENAME,HIREDATE FROM EMPS ORDER BY DATEDIFF(NOW(),HIREDATE) DESC;
#K. 、 ， ，
SELECT ENAME,JOB,SAL FROM EMPS ORDER BY JOB DESC,SAL ASC;
#L. ， ，
SELECT ENAME,HIREDATE FROM EMPS ORDER BY DATE_FORMAT(HIREDATE,'%Y') ASC,DATE_FORMAT(HIREDATE,'%m') ASC;
#M. 30 ，
SELECT ENAME,round(sal/30,0) AS daysal FROM EMPS;
#N. （ ）2
SELECT * FROM EMPS WHERE DATE_FORMAT(HIREDATE,'%m')=2;
#O. ，
SELECT ENAME,TIMESTAMPDIFF(DAY,HIREDATE,NOW()) AS JOBDAY FROM EMPS;
#P. "A"
SELECT ENAME FROM EMPS WHERE ENAME LIKE '%A%';
#Q. 、
SELECT ENAME,HIREDATE,TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) AS ' ',TIMESTAMPDIFF(MONTH,HIREDATE,NOW()) AS ' ',TIMESTAMPDIFF(DAY,HIREDATE,NOW()) AS ' ' FROM EMPS;