牛客网在线编程网址:https://www.nowcoder.com/activity/oj
(默认使用SQLite)
题目1:
存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据?
CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));
select * from emp_v where emp_no in (select emp_no from employees);
或
SELECT * FROM employees INTERSECT SELECT * FROM emp_v
题目2:
将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus( emp_no int not null, recevied datetime not null, btype smallint not null);
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
我理解的题意是要更新他们的薪水记录,这样应该先修改原来薪水的到期时间再插入新的薪水记录。不过可以通过的代码好像不是这样的:
update salaries set salary = salary*1.1 where emp_no in (select emp_no from emp_bonus where btype > 0) AND to_date = '9999-01-01';
用mysql实现自己的想法是这样的(可以的话应该制定规则再比较奖金时间和上次薪水调整时间的关系,我没有做这一步):
update salaries set to_date = CURDATE() /*先更新原来薪水的记录时间到当前*/ where emp_no IN ( select emp_no from emp_bonus where btype > 0 ) AND salaries.to_date = '9999-01-01'; insert into salaries /*再插入新的薪水,from_date为当前*/ select emp_no, round(salary*1.1) salary, CURDATE() from_date, '9999-01-01' to_date from salaries where emp_no in (select emp_no from emp_bonus where btype > 0) and salaries.to_date = CURDATE() ;
题目3:
针对库中的所有表生成select count(*)对应的SQL语句:employees, emp_bonus, dept_emp, dept_manager, salaries;
select "select count(*) from "||name||";" cnts from sqlite_master where type = 'table';
题目4:
将employees表中的所有员工的last_name和first_name通过(')连接起来。
select last_name||"'"||first_name from employees;
题目5:
查找字符串'10,A,B' 中逗号','出现的次数cnt。
select (length("10,A,B") - length(replace("10,A,B", ",", "")))/length(",") as cnt;
题目6:
获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
select first_name from employees order by substr(first_name, length(first_name)-1) asc;
或
select first_name from employees order by substr(first_name, -2) asc;
Mysql里用法相似的是substring()函数,也可以用left(string, num),right(string, num)实现字符截取功能。