Link: www.nowcoder.com/ta/sql?page…

\color{red}{25} the salary of the employee and the manager in the same salary table, and then compare

Table structure: employees (emp_no,birth_date,first_name,last_name,gender,hire_date) salaries (emp_no,salary,from_date,to_date) departments (dept_no,dept_name) dept_emp (emp_no,dept_no,from_date,to_date) dept_manager (dept_no,emp_no,from_date,to_date) titles (emp_no,title,from_date,to_date)

  1. All the staff of the employees table of first_name and last_name pieced together, as the Name, with a space to distinguish between (note: the database system is sqllite, string concatenation for | | symbol, does not support the concat function)
select last_name||' '||first_name as name
from employees
Copy the code
  1. Create an actor table that contains information such as datetime(‘now’,’localtime’)
create table actor(
actor_id smallint(5) not null,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp not null default(datetime('now'.'localtime')),
PRIMARY KEY (actor_id));
Copy the code

34. Insert the following data into the table actor in batches (can not have 2 insert statements oh!)

CREATE TABLE IF NOT EXISTS 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'))) 
Copy the code
actor_id first_name last_name last_update
1 PENELOPE GUINESS The 2006-02-15 12:34:33
2 NICK WAHLBERG The 2006-02-15 12:34:33 
insert into actor
values (1.'PENELOPE'.'GUINESS'.'the 2006-02-15 12:34:33'),
(2.'NICK'.'WAHLBERG'.'the 2006-02-15 12:34:33')
Copy the code
  1. Insert the following data in batches into the table actor. If the data already exists, ignore it (replace operation is not supported).
actor_id first_name last_name last_update
‘3’ ‘ED’ ‘CHASE’ ‘the 2006-02-15 12:34:33’ 
insert  or ignore into actor values 
('3'.'ED'.'CHASE'.'the 2006-02-15 12:34:33')
Copy the code

36. For the actor in the following table, its corresponding data is:

actor_id first_name last_name last_update
1 PENELOPE GUINESS The 2006-02-15 12:34:33
2 NICK WAHLBERG The 2006-02-15 12:34:33

Select * from actor where first_name = ‘last_name’; select * from actor where first_name = ‘last_name’;

The list of type Whether to NULL meaning
first_name varchar(45) not null The name
last_name varchar(45) not null surnames 
create table actor_name
as select first_name,last_name
from actor
Copy the code
  1. Create indexes against TABLE Actor structures. (Note: in SQLite, the ALTER TABLE command does not support other operations than renaming tables and adding columns to existing tables.)

Create unique index uniq_idx_firstName on first_name, common index idx_lastName on last_name

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
Copy the code
  1. Create view actor_name_view for actor table and rename the columns first_name and last_name to first_name_v and last_name to last_name_v
create view actor_name_view (first_name_v,last_name_v) as
select first_name,last_name from actor
Copy the code
  1. Create index idx_EMP_no for the EMP_NO field in the salaries table. Query emp_no to 10005 and use mandatory index.
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 index idx_emp_no on salaries(emp_no);
Copy the code
select * from salaries indexed by idx_emp_no where emp_no = 10005
Copy the code
  1. Select last_update (create_date, type datetime, NOT NULL, default value ‘0000-00-00 00:00:00’);
alter table actor add column 
create_date datetime not null default('0000-00-00 00:00:00')
Copy the code