SQLize Online / PHPize Online  /  SQLtest Online

create schema seminar_2; CREATE TABLE seminar_2.salary ( employee_nm TEXT, department_nm TEXT, salary NUMERIC ); SELECT * FROM information_schema.tables LIMIT 10; INSERT INTO seminar_2.salary VALUES ('Ken Sánchez', 'HR', 78); INSERT INTO seminar_2.salary VALUES ('TerriLee Duffy', 'HR', 95); INSERT INTO seminar_2.salary VALUES ('Roberto Tamburello', 'HR', 382); INSERT INTO seminar_2.salary VALUES ('Rob Walters', 'HR', 16); INSERT INTO seminar_2.salary VALUES ('Gail Erickson', 'HR', 1079); ALTER TABLE seminar_2.salary ADD COLUMN comment TEXT; SELECT * FROM seminar_2.salary; TRUNCATE TABLE seminar_2.salary; ALTER TABLE seminar_2.salary DROP comment; INSERT INTO seminar_2.salary VALUES ('Ken Sánchez', 'HR', 78); INSERT INTO seminar_2.salary VALUES ('TerriLee Duffy', 'HR', 95); INSERT INTO seminar_2.salary VALUES ('Roberto Tamburello', 'HR', 382); INSERT INTO seminar_2.salary VALUES ('Rob Walters', 'HR', 16); INSERT INTO seminar_2.salary VALUES ('Gail Erickson', 'HR', 1079); INSERT INTO seminar_2.salary VALUES ('Jossef Gibson', 'HR', 102); INSERT INTO seminar_2.salary VALUES ('Dylan Miller', 'HR', 486); INSERT INTO seminar_2.salary VALUES ('Diane Margheim', 'HR', 1953); INSERT INTO seminar_2.salary VALUES ('Gigi Matthew', 'SALE', 49); INSERT INTO seminar_2.salary VALUES ('Michael Raheem', 'SALE', 71); INSERT INTO seminar_2.salary VALUES ('Ovidiu Cracium', 'SALE', 94); INSERT INTO seminar_2.salary VALUES ('Thierry Hers', 'SALE', 61); INSERT INTO seminar_2.salary VALUES ('Janice Galvin', 'SALE', 972); INSERT INTO seminar_2.salary VALUES ('Michael Sullivan', 'SALE', 849); INSERT INTO seminar_2.salary VALUES ('Sharon Salavaria', 'SALE', 715); INSERT INTO seminar_2.salary VALUES ('David Michael', 'SALE', 94); INSERT INTO seminar_2.salary VALUES ('Kevin Brown', 'R&D', 891); INSERT INTO seminar_2.salary VALUES ('John Wood', 'R&D', 1486); INSERT INTO seminar_2.salary VALUES ('Mary Dempsey', 'R&D', 176); INSERT INTO seminar_2.salary VALUES ('Wanida Benshoof', 'R&D', 49); INSERT INTO seminar_2.salary VALUES ('Terry Eminhizer', 'R&D', 381); INSERT INTO seminar_2.salary VALUES ('Sariya Harnpadoungsataya', 'R&D', 946); INSERT INTO seminar_2.salary VALUES ('Mary Gibson', 'R&D', 486); INSERT INTO seminar_2.salary VALUES ('Jill Williams', 'R&D', 19); INSERT INTO seminar_2.salary VALUES ('James Hamilton', 'R&D', 46); INSERT INTO seminar_2.salary VALUES ('Peter Krebs', 'R&D', 445); INSERT INTO seminar_2.salary VALUES ('Jo Brown', 'R&D', 666); INSERT INTO seminar_2.salary VALUES ('Guy Gilbert', 'MANAGEMENT', 482); INSERT INTO seminar_2.salary VALUES ('Mark McArthur', 'MANAGEMENT', 12); INSERT INTO seminar_2.salary VALUES ('Britta Simon', 'MANAGEMENT', 194); INSERT INTO seminar_2.salary VALUES ('Margie Shoop', 'MANAGEMENT', 481); INSERT INTO seminar_2.salary VALUES ('Rebecca Laszlo', 'MANAGEMENT', 16); INSERT INTO seminar_2.salary VALUES ('Annik Stahl', 'MANAGEMENT', 134); INSERT INTO seminar_2.salary VALUES ('Suchitra Mohan', 'R&D', 87); INSERT INTO seminar_2.salary VALUES ('Brandon Heidepriem', 'R&D', 111); INSERT INTO seminar_2.salary VALUES ('Jose Lugo', 'R&D', 185); INSERT INTO seminar_2.salary VALUES ('Chris Okelberry', 'R&D', 94); INSERT INTO seminar_2.salary VALUES ('Kim Abercrombie', 'R&D', 348); INSERT INTO seminar_2.salary VALUES ('Ed Dudenhoefer', 'R&D', 68); INSERT INTO seminar_2.salary VALUES ('JoLynn Dobney', 'R&D', 346); INSERT INTO seminar_2.salary VALUES ('Bryan Baker', 'R&D', 185); INSERT INTO seminar_2.salary VALUES ('James Kramer', 'SUPPORT', 965); INSERT INTO seminar_2.salary VALUES ('Nancy Anderson', 'SUPPORT', 444); INSERT INTO seminar_2.salary VALUES ('Simon Rapier', 'SUPPORT', 133); INSERT INTO seminar_2.salary VALUES ('Thomas Michaels', 'SUPPORT', 200); INSERT INTO seminar_2.salary VALUES ('Eugene Kogan', 'SUPPORT', 144); INSERT INTO seminar_2.salary VALUES ('Andrew Hill', 'SUPPORT', 186); INSERT INTO seminar_2.salary VALUES ('Ruth Ellerbrock', 'SUPPORT', 179); INSERT INTO seminar_2.salary VALUES ('Barry Johnson', 'HEAD', 10000); INSERT INTO seminar_2.salary VALUES ('Sidney Higa', 'HEAD', 1); INSERT INTO seminar_2.salary VALUES ('Max Lanson', 'PR', 150); SELECT * FROM seminar_2.salary; SELECT employee_nm FROM seminar_2.salary WHERE salary > 500; SELECT employee_nm FROM seminar_2.salary WHERE salary < 500 AND salary > 300; SELECT employee_nm FROM seminar_2.salary WHERE salary >= 100 AND lower(employee_nm) LIKE '%a% %'; SELECT employee_nm FROM seminar_2.salary WHERE department_nm IN ('HR', 'PR'); SELECT DISTINCT department_nm FROM seminar_2.salary -- WHERE department_nm LIKE '__' WHERE length(department_nm) = 2; SELECT department_nm FROM seminar_2.salary WHERE department_nm LIKE 'S%'; SELECT employee_nm FROM seminar_2.salary WHERE department_nm not in ('R&D', 'SUPPORT'); SELECT department_nm FROM seminar_2.salary WHERE lower(department_nm) LIKE '%a%e%' OR lower(department_nm) LIKE '%e%a%'; create schema seminar_3; CREATE TABLE seminar_3.department ( department_nm TEXT, location_txt TEXT ); INSERT INTO seminar_3.department VALUES ('SUPPORT', 'Saratov'), ('MANAGEMENT', 'Samara'), ('HEAD', 'Moscow'), ('SALE', 'Moscow'), ('R&D', 'Novosibirsk'); SELECT employee_nm FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) WHERE location_txt = 'Moscow'; SELECT employee_nm FROM seminar_2.salary LEFT JOIN seminar_3.department USING(department_nm) WHERE location_txt is null; SELECT max(salary) AS max_salary, location_txt FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) GROUP BY location_txt; SELECT location_txt, count(employee_nm) AS employee_nm FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) GROUP BY location_txt; SELECT location_txt FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) GROUP BY location_txt HAVING min(salary) > 50; SELECT employee_nm, location_txt, salary FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) ORDER BY location_txt, salary; SELECT employee_nm, location_txt, salary FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) ORDER BY location_txt, salary DESC; SELECT location_txt, count(employee_nm) AS employee_count, max(salary) AS max_salary, min(salary) AS min_salary FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) GROUP BY location_txt; SELECT location_txt, count(employee_nm) AS employee_count FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) GROUP BY location_txt HAVING avg(salary) > 100; SELECT distinct department_nm FROM seminar_2.salary; INSERT INTO seminar_3.department VALUES ('random_department', 'Kaluga'); SELECT location_txt, max(salary) AS max_salary, min(salary) AS min_salary, avg(salary) AS avg_salary FROM seminar_2.salary INNER JOIN seminar_3.department USING(department_nm) GROUP BY location_txt;