create table `employees` (
`emp_no` int(11) not null,
`birth_data` 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`));
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`));
create table `dept_emp` (
`emp_no` int(11) not null,
`dept_no` char(4) not null,
`from_date` date not null,
`to_date` date not null,
primary key (`emp_no`,`dept_no`));
create table `dept_manager` (
`dept_no` char(4) not null,
`emp_no` int(11) not null,
`to_date` date not null,
primary key (`emp_no`,`dept_no`));
create table `titles` (
`emp_no` int(11) not null,
`title` varchar(50) not null,
`from_date` date not null,
`to_date` date default null);
select * from employees
order by hire_date desc
limit 1
select * from employees
order by hire_date desc
limit 2,1
select s.*, d.dept_no
from salaries as s
join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date = '9999-01-01' and d.to_date='9999-01-01'
select e.last_name, e.first_name, d.dept_no
from dept_emp as d
inner join employees as e
on e.emp_no = d.emp_no
select e.last_name, e.first_name, d.dept_no
from empoyees e
left join dept_emp d
on e.emp_no = d.emp_no
select e.emp_no, s.salary
from employees as e, salaries as s
where e.emp_no = s.emp_no and e.hire_date = s.from.date
order by e.emp_no desc
select emp_no, count(emp_no) as t
from salaries
group by emp_no
having t > 15
select distinct salary
from salaries
where to_date = '9999-01-01'
order by salary desc
select d.dept_no, d.emp_no, s.salary
from salaries as s
inner join dept_maneger as d
on d.emp_no = s.emp_no
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
select emp_no
from employees
where emp_no not in (
select emp_no from dept_manager)
select e.emp_no, m.emp_no as manager_no
from dept_emp as e
inner join dept_manager as m
on e.dept_no = m.dept_no
where m.todate = '9999-01-01'
and e.to_date = '9999-01-01'
and e.emp_to <> m.emp_no
select d.dept_no, s.emp_no, max(s.salary) as salary
from salaries as s
inner join dept_emp as d
on d.emp_no = s.emp_no
where d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
group by d.dept_no
select title, count(title) as t
from titles
group by title
having t >= 2
select title, count(distinct emp_no) as t
from titles
group by title
having t >= 2
select * from employees
where last_name not like 'Mary'
and emp_no % 2 = 1
order by hire_date desc
select t.title, avg(s.salary)
from salaries as s
inner join titles as t
on s.emp_no = t.emp_no
and s.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
group by title
select emp_no, salary
from salaries
where salary = (
select salary from salaries
group by salary
order by salary desc limit 1, 1)
and to_date = '9999-01-01'
select e.emp_no, max(s.salary) as salary, e.last_name, e.first_name
from employees as e
inner join salaries as s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and s.salary not in (
select max(salary) from salaries)
select em.last_name, em.first_name, dp.dept_name
from (employees as em left join dept_emp as de on em.emp_no = de.emp_no)
left join departments as dp
on de.dept_no = dp.dept_no
select (
(select salary from salaries where emp_no = 10001 order by to_date desc limit 1) -
(select salary from salaries where emp_no = 10001 order by to_date asc limit 1)
) as growth
select a.emp_no, (b.salary - c.salary) as growth
from employees as a
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc
select de.dept_no, dp.dept_name, count(s.salary) as sum
from (dept_emp as de inner join salaries as s on de.emp_no = s.emp_no)
inner join departments as dp
on de.dept_no = dp.dept_no
group by de.dept_no
select s1.emp_no, s1.salary, count(distinct s2.salary) as rank
from salaries as s1, salaries as s2
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
and s1.salary <= s2.salary
group by s1.emp_no
order by s1.salary desc, s1.emp_no asc
select de.dept_no, s.emp_no, s.salary
from (employees as e inner join salaries as s on s.emp_no = e.emp_no and s.to_date = '9999-01-01')
inner join dept_emp as de
on e.emp_no = de.emp_no
where de.emp_no not in (select emp_no from dept_manager)
select e.emp_no, m.emp_no as manager_no, e.salary as emp_salary, m.salary as manager_salary
from
(select s.salary, s.emp_no, de.dept_no from salariees s
inner join dept_emp de
on s.emp_no = de.emp_no and s.to_date = '9999-01-01') as e,
(select s.salary, s.emp_no, dm.dept_no from salaries s
inner join dept_manager dm
on s.emp_no = dm.emp_no and s.to_date = '9999-01-01') as m
where e.dept_no = m.dept_no
and e.salary > m.salary
select de.dept_no, de.dept_name, t.title, count(t.title) as count
from titles as t
inner join dept_emp as de
on t.emp_no = de.emp_no
and de.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
inner join departments as dp
on de.dept_no = dp.dept_no
group by de.dept_no, t.title
select s2.emp_no, s2.from_date, (s2.salary - s1.salary) as salary_growth
from salaries as s1, salaries as s2
where s1.emp_no = s2.emp_no
and salary_growth > 5000
and (strftime("%Y", s2.to_date) - strftime("%Y", s1.to_date) = 1
or strftime("%Y," s2.from_dat2) - strftime("%Y", s1.from_date) = 1)
order by salary_growth desc
select last_name||" "||first_name as Name
from employees
select * from salaries
indexed by idx_emp_no
where emp_no = 10005
create table employees_test(
ID int primary key not null,
name text not null,
age int not null,
address char(50),
salary real);
create table audit(
EMP_no int not null,
NAME text not null);
create trigger audit_log after insert on employees_test
BEGIN
INSERT INTO audit VALUES (NEW.ID, NEW.NAME);
END;
delete from title_test where id not in
(select MIN(id) from title_test group by emp_no);
update title_test
set to_date = NULL, from_date = '2001-01-01'
where to_date = '9999-01-01';
create table if not exists titles_test(
id int(11) not null primary key,
emp_no int(11) not null,
title varchar(50)not null,
from_date date not null,
to_date date default null);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
replace into title_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
alter table titles_test rename to titles_2017;
drop table audit;
create table audit(
emp_no int not null,
create_date datetime not null,
foreign key(emp_no) references employees_test(id));
create table emp_bonus(
emp_no int not null,
btype smallint not null);
update salaries set salary = salary * 1.1
where emp_no in (select emp_no in emp_bonus)
and to_date = '9999-01-01';
select "select count(*) from " || name || ";"
from sqlite_master
where type = 'table'
select last_name || "'" || first_name from employees
select first_name from employees
order by substr(first_name, length(first_name) - 1)
select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no
select avg(salary) as avg_salary
from salaries
where to_date = '9999-01-01'
and salary not in(select max(salary) from salaries where to_date = '9999-01-01')
and salary not in(select min(salary) from salaries where to_date = '9999-01-01');
select *
from employees
limit 5, 5;
select de.emp_no, de.dept_no, eb.btype, eb.received
from dept_emp as de
left join emp_bonus as eb
on de.emp_no = eb_emp_no
select * from employees where not exists
(select emp_ no from dept_emp where emp_no = employees.emp_no)
select e.emp_no, e.first_name, e.last_name, b.btype, s.salary
(case b.btype
when 1 then s.salary * 0.1
when 2 then s.salary * 0.2
else s.salary * 0.3
end) as bonus
from employees as e
inner join emp_bonus as b
on e.emp_no = b.emp_no
inner join salaries as s
on e.emp_no = s.emp_no
and s.to_date = '9999-01-01'
select e1.first_name
from
(select e2.first_name, (select count(*) from employees as e3 where e3.first_name <= e2.first_name) as rowid
from employees as e2) as e1
where e1.rowid % 2 = 1
create table if not exists film(
film_id smallint(5) not null default '0',
title varchar(255) not null,
description text,
primary key(film_id));
create table category(
category_id tinyint(3) not null,
name varchar(25) not null,
last_update timestamp,
primary key(category_id));
create table film_category(
film_id smallint(5) not null,
category_id tinyint(3) not null,
last_update timestamp);
select c.name, count(fc.film_id)
from
(select category_id, count(film_id) as category_num from
film_category
group by category_id
having count(film_id) >=5) as cc,
film as f, category as c, film_category as fc
where f.description like '%robot%'
and f.film_id = fc.film_id and fc.category_id = c.category_id
and c.category = cc.category_id
select f.film_id, f.title
from film f
left join film_category fc
on f.film_id = fc.film_id
where fc.category_id is null;
select f.title, f.description
from film as f
where f.film_id in
(select fc.film_id from film_category as fc where fc.category_id in
(select c.category_id from category as c where c.name = 'Action'));
create table actor(
actor_id smallint(5) not null primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp not null default (datetime('now', 'localtime')));
insert into actor values
(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');
insert or ignore into actor values
(3, 'ED', 'CHASE', '2006-02-15 12:34:33');
create table actor_name as
select first_name, last_name from actor;
create unique index uniq_idx_firstname on actor(firs_tname);
create index idx_lastname on actor(last_name);
create view actor_name_view as
select first_name as first_name_v, last_name as last_name_v
from actor
alter table actor
add `create_date` datetime not null default '0000-00-00 00:00:00'
|
