Stepik

-- create
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

-- fetch
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';

-- create
CREATE TABLE PERSONALDATA (
pdId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
phone TEXT NOT NULL,
salary INTEGER NOT NULL
);
-- insert
INSERT INTO PERSONALDATA VALUES (0001, 'Clark', '250-50-50', '50000');
INSERT INTO PERSONALDATA VALUES (0002, 'Dave', '8(999)999-99-99' , '70000');
INSERT INTO PERSONALDATA VALUES (0003, 'Ava', '233-33-33', '30000');

SELECT EMPLOYEE.name, PERSONALDATA.phone, PERSONALDATA.salary FROM EMPLOYEE
JOIN PERSONALDATA ON EMPLOYEE.empId = PERSONALDATA.pdId;

INSERT INTO EMPLOYEE VALUES (0004, 'Rick','Sales');
INSERT INTO PERSONALDATA VALUES (0005, 'Ivan', '8(777)777-77-77','75000');

SELECT EMPLOYEE.name, PERSONALDATA.phone, PERSONALDATA.salary FROM EMPLOYEE
LEFT JOIN PERSONALDATA ON EMPLOYEE.empId = PERSONALDATA.pdId;

SELECT EMPLOYEE.name, PERSONALDATA.phone, PERSONALDATA.salary FROM EMPLOYEE
RIGHT JOIN PERSONALDATA ON EMPLOYEE.empId = PERSONALDATA.pdId;

SELECT MIN(salary) from PERSONALDATA;
SELECT SUM(salary), COUNT(name) from PERSONALDATA;

SELECT salary from PERSONALDATA where name = 'Dave' and phone = '8(999)999-99-99';