SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE department( deptno SMALLINT NOT NULL PRIMARY KEY, deptname VARCHAR(15), code VARCHAR(30)); INSERT INTO department VALUES (1,'IT', 'asa_123456'); INSERT INTO department VALUES (2,'Economy','s''dfg*234'); INSERT INTO department VALUES (3,'Research','a_ss*8888'); INSERT INTO department VALUES (4,'Marketing','a%'); CREATE TABLE employee( empno CHAR (4) PRIMARY KEY, fname VARCHAR (10), lname VARCHAR (10), city VARCHAR (10), education CHAR (8), salary DECIMAL(7, 2), tax_rate DECIMAL(3,1), start_date DATEtime, deptno SMALLINT); INSERT INTO employee VALUES (2134,'Peter','Stream' ,'LONDON','Ba',2800 ,22 ,'2020-03-02',3); INSERT INTO employee VALUES (2234,'Mike','Wood' ,'LONDON','PhD',3100 ,33 ,'2009-10-15',1); INSERT INTO employee VALUES (2245,'Rachel','Brooke' ,'HELSINKI','MA',3100 ,31 ,'2014-09-24',4); INSERT INTO employee VALUES (2345,'Leon','Lake' ,'LONDON',NULL,2800 ,24.5,'2018-01-01', 3); INSERT INTO employee VALUES (2884,'Peter','Taylor' ,'HELSINKI','MA',2960 ,31 ,'2009-05-12',NULL); INSERT INTO employee VALUES (3546,'Laura','Brown' ,'SYDNEY','Ba',2650 ,22 ,'2017-09-15',1); INSERT INTO employee VALUES (3547,'Lilian','River' ,'SYDNEY','DIP',2800 ,37 ,'2009-05-12',3); CREATE TABLE project ( projno CHAR(4) NOT NULL PRIMARY KEY, project_name VARCHAR(15), priority SMALLINT, location VARCHAR(15) ); create table kkk ( s1 char(1)); INSERT INTO project VALUES ('P1','BOOKKEEPING',2,'LONDON'); INSERT INTO project VALUES ('P2','BILLING',1,'HELSINKI'); INSERT INTO project VALUES('P3','WAREHOUSING',3,'HELSINKI'); INSERT INTO project VALUES('P4','ACCOUNTING',2,'LONDON'); INSERT INTO project VALUES('P5','CUSTOMERS',3,'SINGAPORE'); INSERT INTO project VALUES('P6','STATISTICS', NULL, NULL); CREATE TABLE proj_emp (projno CHAR (4) NOT NULL, empno CHAR (4) NOT NULL, hours_act SMALLINT, hours_est SMALLINT, CONSTRAINT proj_emp_pk PRIMARY KEY (projno, empno)); INSERT INTO proj_emp VALUES ('P1','2134',300,300); INSERT INTO proj_emp VALUES ('P1','2245',200,300); INSERT INTO proj_emp VALUES ('P1','3546',400,500); INSERT INTO proj_emp VALUES ('P1','2884',100,200); INSERT INTO proj_emp VALUES ('P1','2234',200,NULL); INSERT INTO proj_emp VALUES ('P1','2345',100,100); INSERT INTO proj_emp VALUES ('P1','3547',300,200); INSERT INTO proj_emp VALUES ('P2','2134',300,NULL); INSERT INTO proj_emp VALUES ('P2','2245',400,500); INSERT INTO proj_emp VALUES ('P3','2245',900,100); INSERT INTO proj_emp VALUES ('P4','2245',200,200); INSERT INTO proj_emp VALUES ('P4','2234',300,400); INSERT INTO proj_emp VALUES ('P4','2884',400,600); SELECT emp.empno, emp.lname, pr.project_name, pe.hours_act FROM employee emp JOIN proj_emp pe ON (emp.empno = pe.empno) JOIN project pr ON (pr.projno = pe.projno) ORDER BY emp.lname, pr.project_name