■１－１２
SELECT last_name, salary,
commission_pct,
salary * 3,
salary + commission_pct,
(salary + commission_pct) * 12
FROM employees;

■１－１５
SELECT last_name AS name
,commission_pct comm
FROM employees;

SELECT last_name "Name",
salary*12 "Annual Salary"
FROM employees;

■１－１６
SELECT last_name||job_id AS "Employees"
FROM employees;

■１－１８
SELECT last_name|| ' is a ' ||job_id
AS "Employees Details"
FROM employees;

■１－２０
SELECT department_name ||
q'[, it's assigned Manager ID: ]'
|| manager_id
AS "Department nd Manager"
FROM departments;

■１－２１
SELETC department_id
FROM employees;

SELECT DISTINCT department_id
FROM employees;

Ｂ１０
SELECT employee_id "Emp #",last_name "Employee",job_id "Job",hire_date "Hire Date"
From employees;

Ｂ１１
SELECT last_name|| q'[,]' ||job_id
AS "Employee and Title"
FROM employees;

Ｂ１２
DESCRIBE employees

SELECT employee_id||','||first_name||','||last_name||','||email||','||phone_number||','||job_id||','||salary||','||commission_pct||','||manager_id||','||department_id
AS "THE_OUTPUT"
FROM employees;

■２－５
SELECT employee_id,
last_name,
job_id,
department_id
FROM employees
WHERE department_id = 90;

■２－６
SELECT last_name,
job_id,
department_id
FROM employees
WHERE last_name='Whalen';

■２－１０
SELECT employee_id,
last_id,
salary,
manager_id
FROM employees
WHERE manager_id IN(100,101,201);

Ｂ４
SELECT last_name,job_id,hire_date
FROM employees
WHERE last_name='Matos' OR last_name='Taylor';

Ｂ５
SELECT last_name,department_id
FROM employees
WHERE department_id=20 OR department_id=50;

■３－９
SELECT last_name
LOWER(last_name),
UPPER(last_name)
FROM employees;

■３－３６
SELECT TO_NUMBER('$1,234','$9,999')
FROM dual;

SELECT TO_NUMBER('1234','999,999')
FROM dual;

■３－４６
SELECT last_name,
UPPER(CONCAT(SUBSTR (last_name,1,8),'US'))
FROM employees
WHERE department_id=60;

■３－５４
SELECT last_name,job_id,salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

■Ｂ１６
SELECT first_name,LENGTH(first_name)
FROM employees
WHERE first_name LIKE 'A%' OR first_name LIKE 'M%' OR first_name LIKE 'J%'
ORDER BY first_name;

■Ｂ１７
SELECT last_name,
ROUND(MONTHS_BETWEEN(SYSDATE,hire_date),0)
AS "MONTHS_WORKED",
ROUND((SYSDATE-hire_date)/30,0)
AS "MONTHS_WORKED2"
FROM employees
ORDER BY hire_date DESC;

■Ｂ２５ ９
SELECT
DISTINCT manager_id,MIN(DISTINCT salary)
FROM
employees
WHERE manager_id IS NOT NULL
GROUP BY
manager_id
ORDER BY
MIN(DISTINCT salary);


■B26特別問題（解けず）
SELECT COUNT(hire_date),COUNT(hire_date)

FROM employees
WHERE TO_CHAR(hire_date,'YYYY')='1995'
OR TO_CHAR(hire_date,'YYYY')='1996'
OR TO_CHAR(hire_date,'YYYY')='1997'
OR TO_CHAR(hire_date,'YYYY')='1998'
;


■B28
SELECT location_id,street_address,city,state_province,country_name
FROM locations
NATURAL JOIN countries;

■B28
SELECT last_name,department_id,department_name
FROM employees
JOIN departments

■B29
SELECT
last_name,
job_id,
department_name
FROM
employees e

JOIN departments d
ON e.department_id=d.department_id
JOIN locations l
ON d.location_id=l.location_id
AND l.city='Toronto';

■B29（解ききれなかった）
SELECT
e.department_id,
e.last_name,
m.last_name

FROM employees e JOIN employees m
ON (e.department_id=m.department_id)
ORDER BY department_id

USING (department_id);


■６－９
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=
(SELECT job_id
FROM employees
WHERE employee_id=141)
AND salary>
(SELECT salary
FROM employees
WHERE employee_id=143);

■B34－２
SELECT last_name,hire_date
FROM employees
WHERE department_id=
(SELECT department_id
FROM employees
WHERE last_name='&&enter')
AND last_name'&enter';

■B34-２
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>
(SELECT AVG(salary)
FROM employees
)
ORDER BY salary;

■３４－３
SELECT employee_id,last_name
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE first_name LIKE '%u%');

■３５－５
SELECT last_name,salary
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL AND manager_id=100);


B44-9-1表の作成

CREATE TABLE dept2(

id NUMBER(7)
CONSTRAINT dep2_id_pk PRIMARY KEY,
name VARCHAR2(25)
);

B44-9-2
INSERT INTO dept2(id,name)
SELECT department_id,department_name
FROM departments;

B44-9-3
CREATE TABLE emp(
id NUMBER(7),
last_name VARCHAR2(25),
first_name VARCHAR2(25),
dept_id NUMBER(7)
CONSTRAINT emp_dept_id_fk
REFERENCES departments(department_id)

);

B44-9-4
CREATE TABLE employees2(

id NUMBER(6),

first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
salary NUMBER(8,2) NOT NULL,
dept_id NUMBER(4),

CONSTRAINT emp2_id_pk PRIMARY KEY (id)
);

追加した文
INSERT INTO employees2(id,first_name,last_name,salary,dept_id)

SELECT employee_id,first_name,last_name,salary,department_id
FROM employees;

B44-9-5
DROP TABLE employees2 PURGE;

B48-10-1から3
CREATE VIEW dept50
AS SELECT employee_id EMPNO,
last_name EMPLOYEE,
department_id DEPTNO
FROM employees
WHERE department_id=50
WITH CHECK OPTION CONSTRAINT dept50_ck;

↓

UPDATE dept50
SET deptno=80
WHERE employee='Matos';


”0行が更新されました。”

ってでる。