SECD2523 DATABASE

SQL 4 | DML 3

[Link]
 LECTURE LEARNING OUTCOME
By the end of this lecture, students should be able to:

Construct SQL statements for data manipulations with multiple tables

01 Subqueries

02 Simple Join

03 Equijoin: NATIONAL JOIN; JOIN … ON; INNER JOIN ON;

[Link]
04 Outer Join: LEFT OUTER JOIN; RIGHT OUTER JOIN; FULL OUTER JOIN;

2
01 Subqueries

02 Simple Join

03 Equijoin

04 Outer Join

[Link]
3
Before we continue this DML 3,

We need to prepare our tables for all required records…

Here, our SQL statements to prepare…

[Link]
AzqJxf1/edit?usp=sharing&ouid=112935562328060013817&rtpof=true
&sd=true

[Link]
4
Create a database to create tables for employees, departments, and locations
CREATE DATABASE IF NOT EXISTS db_dml3_dept_emp_loc;
use db_dml3_dept_emp_loc;

CREATE TABLE departments (department_id INT (11) NOT NULL, department_name VARCHAR(30) NOT NULL, manager_id INT (11), location_id INT (11), PRIMARY
KEY (department_id));

CREATE TABLE employees (employee_id INT (11) NOT NULL, first_name VARCHAR(20), last_name VARCHAR(25) NOT NULL, email VARCHAR(25) NOT NULL,
phone_number VARCHAR(20), hire_date DATE NOT NULL, job_id VARCHAR(10) NOT NULL, salary DECIMAL(8, 2) NOT NULL, commission_pct DECIMAL(2, 2),
manager_id INT (11), department_id INT (11), PRIMARY KEY (employee_id));

CREATE TABLE locations (location_id INT (11) NOT NULL, street_address VARCHAR(40), postal_code VARCHAR(12), city VARCHAR(30) NOT NULL, state_province
VARCHAR(25), country_id CHAR(2) NOT NULL, PRIMARY KEY (location_id));

[Link]
5
Insert into locations with the following values (23 records)
INSERT INTO locations VALUES (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT');
INSERT INTO locations VALUES (1100,'93091 Calle della Testa','10934','Venice',NULL,'IT');
INSERT INTO locations VALUES (1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP');
INSERT INTO locations VALUES (1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP');
INSERT INTO locations VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
INSERT INTO locations VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
INSERT INTO locations VALUES (1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US');
INSERT INTO locations VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
INSERT INTO locations VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
INSERT INTO locations VALUES (1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA');
INSERT INTO locations VALUES (2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN');
INSERT INTO locations VALUES (2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN');
INSERT INTO locations VALUES (2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU');
INSERT INTO locations VALUES (2300,'198 Clementi North','540198','Singapore',NULL,'SG');
INSERT INTO locations VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK');
INSERT INTO locations VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
INSERT INTO locations VALUES (2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK');

[Link]
INSERT INTO locations VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
INSERT INTO locations VALUES (2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR');
INSERT INTO locations VALUES (2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH');
INSERT INTO locations VALUES (3000,'Murtenstrasse 921','3095','Bern','BE','CH');
INSERT INTO locations VALUES (3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL');
6
INSERT INTO locations VALUES (3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
Insert into departments with the following values (27 records)
INSERT INTO departments VALUES (10,'Administration',200,1700);
INSERT INTO departments VALUES (20,'Marketing',201,1800);
INSERT INTO departments VALUES (30,'Purchasing',114,1700);
INSERT INTO departments VALUES (40,'Human Resources',203,2400);
INSERT INTO departments VALUES (50,'Shipping',121,1500);
INSERT INTO departments VALUES (60,'IT',103,1400);
INSERT INTO departments VALUES (70,'Public Relations',204,2700);
INSERT INTO departments VALUES (80,'Sales',145,2500);
INSERT INTO departments VALUES (90,'Executive',100,1700);
INSERT INTO departments VALUES (100,'Finance',108,1700);
INSERT INTO departments VALUES (110,'Accounting',205,1700);
INSERT INTO departments VALUES (120,'Treasury',NULL,1700);
INSERT INTO departments VALUES (130,'Corporate Tax',NULL,1700);
INSERT INTO departments VALUES (140,'Control And Credit',NULL,1700);
INSERT INTO departments VALUES (150,'Shareholder Services',NULL,1700);
INSERT INTO departments VALUES (160,'Benefits',NULL,1700);
INSERT INTO departments VALUES (170,'Manufacturing',NULL,1700);
INSERT INTO departments VALUES (180,'Construction',NULL,1700);
INSERT INTO departments VALUES (190,'Contracting',NULL,1700);
INSERT INTO departments VALUES (200,'Operations',NULL,1700);

[Link]
INSERT INTO departments VALUES (210,'IT Support',NULL,1700);
INSERT INTO departments VALUES (220,'NOC',NULL,1700);
INSERT INTO departments VALUES (230,'IT Helpdesk',NULL,1700);
INSERT INTO departments VALUES (240,'Government Sales',NULL,1700);
INSERT INTO departments VALUES (250,'Retail Sales',NULL,1700);
INSERT INTO departments VALUES (260,'Recruiting',NULL,1700); 7
INSERT INTO departments VALUES (270,'Payroll',NULL,1700);
Insert into employees with the following values (107 records)
INSERT INTO employees VALUES (100,'Steven','King','SKING','515.123.4567',STR_TO_DATE('17-JUN-1987', '%d-%M- INSERT INTO employees VALUES (136,'Hazel','Philtanker','HPHILTAN','650.127.1634',STR_TO_DATE('06-FEB-2000', '%d-%M- INSERT INTO employees VALUES (172,'Elizabeth','Bates','EBATES','011.44.1343.529268',STR_TO_DATE('24-MAR-1999', '%d-%M-
%Y'),'AD_PRES',24000,NULL,NULL,90); %Y'),'ST_CLERK',2200,NULL,122,50); %Y'),'SA_REP',7300,.15,148,80);
INSERT INTO employees VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',STR_TO_DATE('21-SEP-1989', '%d-%M- INSERT INTO employees VALUES (137,'Renske','Ladwig','RLADWIG','650.121.1234',STR_TO_DATE('14-JUL-1995', '%d-%M- INSERT INTO employees VALUES (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268',STR_TO_DATE('21-APR-2000', '%d-%M-
%Y'),'AD_VP',17000,NULL,100,90); %Y'),'ST_CLERK',3600,NULL,123,50); %Y'),'SA_REP',6100,.10,148,80);
INSERT INTO employees VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569',STR_TO_DATE('13-JAN-1993', '%d-%M- INSERT INTO employees VALUES (138,'Stephen','Stiles','SSTILES','650.121.2034',STR_TO_DATE('26-OCT-1997', '%d-%M- INSERT INTO employees VALUES (174,'Ellen','Abel','EABEL','011.44.1644.429267',STR_TO_DATE('11-MAY-1996', '%d-%M-
%Y'),'AD_VP',17000,NULL,100,90); %Y'),'ST_CLERK',3200,NULL,123,50); %Y'),'SA_REP',11000,.30,149,80);
INSERT INTO employees VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567',STR_TO_DATE('03-JAN-1990', '%d-%M- INSERT INTO employees VALUES (139,'John','Seo','JSEO','650.121.2019',STR_TO_DATE('12-FEB-1998', '%d-%M-%Y'),'ST_CLERK',2700,NULL,123,50); INSERT INTO employees VALUES (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266',STR_TO_DATE('19-MAR-1997', '%d-%M-
%Y'),'IT_PROG',9000,NULL,102,60); %Y'),'SA_REP',8800,.25,149,80);
INSERT INTO employees VALUES (140,'Joshua','Patel','JPATEL','650.121.1834',STR_TO_DATE('06-APR-1998', '%d-%M-
INSERT INTO employees VALUES (104,'Bruce','Ernst','BERNST','590.423.4568',STR_TO_DATE('21-MAY-1991', '%d-%M-%Y'),'IT_PROG',6000,NULL,103,60); %Y'),'ST_CLERK',2500,NULL,123,50); INSERT INTO employees VALUES (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',STR_TO_DATE('24-MAR-1998', '%d-%M-
%Y'),'SA_REP',8600,.20,149,80);
INSERT INTO employees VALUES (105,'David','Austin','DAUSTIN','590.423.4569',STR_TO_DATE('25-JUN-1997', '%d-%M- INSERT INTO employees VALUES (141,'Trenna','Rajs','TRAJS','650.121.8009',STR_TO_DATE('17-OCT-1995', '%d-%M-%Y'),'ST_CLERK',3500,NULL,124,50);
%Y'),'IT_PROG',4800,NULL,103,60); INSERT INTO employees VALUES (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',STR_TO_DATE('23-APR-1998', '%d-%M-
INSERT INTO employees VALUES (142,'Curtis','Davies','CDAVIES','650.121.2994',STR_TO_DATE('29-JAN-1997', '%d-%M-
INSERT INTO employees VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560',STR_TO_DATE('05-FEB-1998', '%d-%M- %Y'),'ST_CLERK',3100,NULL,124,50); %Y'),'SA_REP',8400,.20,149,80);
%Y'),'IT_PROG',4800,NULL,103,60); INSERT INTO employees VALUES (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',STR_TO_DATE('24-MAY-1999', '%d-%M-
INSERT INTO employees VALUES (143,'Randall','Matos','RMATOS','650.121.2874',STR_TO_DATE('15-MAR-1998', '%d-%M-
INSERT INTO employees VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567',STR_TO_DATE('07-FEB-1999', '%d-%M- %Y'),'ST_CLERK',2600,NULL,124,50); %Y'),'SA_REP',7000,.15,149,NULL);
%Y'),'IT_PROG',4200,NULL,103,60); INSERT INTO employees VALUES (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',STR_TO_DATE('04-JAN-2000', '%d-%M-
INSERT INTO employees VALUES (144,'Peter','Vargas','PVARGAS','650.121.2004',STR_TO_DATE('09-JUL-1998', '%d-%M-
INSERT INTO employees VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569',STR_TO_DATE('17-AUG-1994', '%d-%M- %Y'),'ST_CLERK',2500,NULL,124,50); %Y'),'SA_REP',6200,.10,149,80);
%Y'),'FI_MGR',12000,NULL,101,100); INSERT INTO employees VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876',STR_TO_DATE('24-JAN-1998', '%d-%M-
INSERT INTO employees VALUES (145,'John','Russell','JRUSSEL','011.44.1344.429268',STR_TO_DATE('01-OCT-1996', '%d-%M-
INSERT INTO employees VALUES (109,'Daniel','Faviet','DFAVIET','515.124.4169',STR_TO_DATE('16-AUG-1994', '%d-%M- %Y'),'SA_MAN',14000,.4,100,80); %Y'),'SH_CLERK',3200,NULL,120,50);
%Y'),'FI_ACCOUNT',9000,NULL,108,100); INSERT INTO employees VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877',STR_TO_DATE('23-FEB-1998', '%d-%M-
INSERT INTO employees VALUES (146,'Karen','Partners','KPARTNER','011.44.1344.467268',STR_TO_DATE('05-JAN-1997', '%d-%M-
INSERT INTO employees VALUES (110,'John','Chen','JCHEN','515.124.4269',STR_TO_DATE('28-SEP-1997', '%d-%M- %Y'),'SA_MAN',13500,.3,100,80); %Y'),'SH_CLERK',3100,NULL,120,50);
%Y'),'FI_ACCOUNT',8200,NULL,108,100); INSERT INTO employees VALUES (182,'Martha','Sullivan','MSULLIVA','650.507.9878',STR_TO_DATE('21-JUN-1999', '%d-%M-
INSERT INTO employees VALUES (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278',STR_TO_DATE('10-MAR-1997', '%d-%M-
INSERT INTO employees VALUES (111,'Ismael','Sciarra','ISCIARRA','515.124.4369',STR_TO_DATE('30-SEP-1997', '%d-%M- %Y'),'SA_MAN',12000,.3,100,80); %Y'),'SH_CLERK',2500,NULL,120,50);
%Y'),'FI_ACCOUNT',7700,NULL,108,100);
INSERT INTO employees VALUES (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268',STR_TO_DATE('15-OCT-1999', '%d-%M- INSERT INTO employees VALUES (183,'Girard','Geoni','GGEONI','650.507.9879',STR_TO_DATE('03-FEB-2000', '%d-%M-
INSERT INTO employees VALUES (112,'Jose Manuel','Urman','JMURMAN','515.124.4469',STR_TO_DATE('07-MAR-1998', '%d-%M- %Y'),'SA_MAN',11000,.3,100,80); %Y'),'SH_CLERK',2800,NULL,120,50);
%Y'),'FI_ACCOUNT',7800,NULL,108,100);
INSERT INTO employees VALUES (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',STR_TO_DATE('29-JAN-2000', '%d-%M- INSERT INTO employees VALUES (184,'Nandita','Sarchand','NSARCHAN','650.509.1876',STR_TO_DATE('27-JAN-1996', '%d-%M-
INSERT INTO employees VALUES (113,'Luis','Popp','LPOPP','515.124.4567',STR_TO_DATE('07-DEC-1999', '%d-%M- %Y'),'SA_MAN',10500,.2,100,80); %Y'),'SH_CLERK',4200,NULL,121,50);
%Y'),'FI_ACCOUNT',6900,NULL,108,100);
INSERT INTO employees VALUES (150,'Peter','Tucker','PTUCKER','011.44.1344.129268',STR_TO_DATE('30-JAN-1997', '%d-%M- INSERT INTO employees VALUES (185,'Alexis','Bull','ABULL','650.509.2876',STR_TO_DATE('20-FEB-1997', '%d-%M-
INSERT INTO employees VALUES (114,'Den','Raphaely','DRAPHEAL','515.127.4561',STR_TO_DATE('07-DEC-1994', '%d-%M- %Y'),'SA_REP',10000,.3,145,80); %Y'),'SH_CLERK',4100,NULL,121,50);
%Y'),'PU_MAN',11000,NULL,100,30);
INSERT INTO employees VALUES (151,'David','Bernstein','DBERNSTE','011.44.1344.345268',STR_TO_DATE('24-MAR-1997', '%d-%M- INSERT INTO employees VALUES (186,'Julia','Dellinger','JDELLING','650.509.3876',STR_TO_DATE('24-JUN-1998', '%d-%M-
INSERT INTO employees VALUES (115,'Alexander','Khoo','AKHOO','515.127.4562',STR_TO_DATE('18-MAY-1995', '%d-%M- %Y'),'SA_REP',9500,.25,145,80); %Y'),'SH_CLERK',3400,NULL,121,50);
%Y'),'PU_CLERK',3100,NULL,114,30);
INSERT INTO employees VALUES (152,'Peter','Hall','PHALL','011.44.1344.478968',STR_TO_DATE('20-AUG-1997', '%d-%M- INSERT INTO employees VALUES (187,'Anthony','Cabrio','ACABRIO','650.509.4876',STR_TO_DATE('07-FEB-1999', '%d-%M-
INSERT INTO employees VALUES (116,'Shelli','Baida','SBAIDA','515.127.4563',STR_TO_DATE('24-DEC-1997', '%d-%M- %Y'),'SA_REP',9000,.25,145,80); %Y'),'SH_CLERK',3000,NULL,121,50);
%Y'),'PU_CLERK',2900,NULL,114,30);
INSERT INTO employees VALUES (153,'Christopher','Olsen','COLSEN','011.44.1344.498718',STR_TO_DATE('30-MAR-1998', '%d-%M- INSERT INTO employees VALUES (188,'Kelly','Chung','KCHUNG','650.505.1876',STR_TO_DATE('14-JUN-1997', '%d-%M-
INSERT INTO employees VALUES (117,'Sigal','Tobias','STOBIAS','515.127.4564',STR_TO_DATE('24-JUL-1997', '%d-%M- %Y'),'SA_REP',8000,.2,145,80); %Y'),'SH_CLERK',3800,NULL,122,50);
%Y'),'PU_CLERK',2800,NULL,114,30);
INSERT INTO employees VALUES (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',STR_TO_DATE('09-DEC-1998', '%d-%M- INSERT INTO employees VALUES (189,'Jennifer','Dilly','JDILLY','650.505.2876',STR_TO_DATE('13-AUG-1997', '%d-%M-
INSERT INTO employees VALUES (118,'Guy','Himuro','GHIMURO','515.127.4565',STR_TO_DATE('15-NOV-1998', '%d-%M- %Y'),'SA_REP',7500,.2,145,80); %Y'),'SH_CLERK',3600,NULL,122,50);
%Y'),'PU_CLERK',2600,NULL,114,30);
INSERT INTO employees VALUES (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508',STR_TO_DATE('23-NOV-1999', '%d-%M- INSERT INTO employees VALUES (190,'Timothy','Gates','TGATES','650.505.3876',STR_TO_DATE('11-JUL-1998', '%d-%M-
INSERT INTO employees VALUES (119,'Karen','Colmenares','KCOLMENA','515.127.4566',STR_TO_DATE('10-AUG-1999', '%d-%M- %Y'),'SA_REP',7000,.15,145,80); %Y'),'SH_CLERK',2900,NULL,122,50);
%Y'),'PU_CLERK',2500,NULL,114,30);
INSERT INTO employees VALUES (156,'Janette','King','JKING','011.44.1345.429268',STR_TO_DATE('30-JAN-1996', '%d-%M- INSERT INTO employees VALUES (191,'Randall','Perkins','RPERKINS','650.505.4876',STR_TO_DATE('19-DEC-1999', '%d-%M-
INSERT INTO employees VALUES (120,'Matthew','Weiss','MWEISS','650.123.1234',STR_TO_DATE('18-JUL-1996', '%d-%M- %Y'),'SA_REP',10000,.35,146,80); %Y'),'SH_CLERK',2500,NULL,122,50);
%Y'),'ST_MAN',8000,NULL,100,50);
INSERT INTO employees VALUES (157,'Patrick','Sully','PSULLY','011.44.1345.929268',STR_TO_DATE('04-MAR-1996', '%d-%M- INSERT INTO employees VALUES (192,'Sarah','Bell','SBELL','650.501.1876',STR_TO_DATE('04-FEB-1996', '%d-%M-%Y'),'SH_CLERK',4000,NULL,123,50);
INSERT INTO employees VALUES (121,'Adam','Fripp','AFRIPP','650.123.2234',STR_TO_DATE('10-APR-1997', '%d-%M-%Y'),'ST_MAN',8200,NULL,100,50); %Y'),'SA_REP',9500,.35,146,80);
INSERT INTO employees VALUES (193,'Britney','Everett','BEVERETT','650.501.2876',STR_TO_DATE('03-MAR-1997', '%d-%M-
INSERT INTO employees VALUES (122,'Payam','Kaufling','PKAUFLIN','650.123.3234',STR_TO_DATE('01-MAY-1995', '%d-%M- INSERT INTO employees VALUES (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268',STR_TO_DATE('01-AUG-1996', '%d-%M- %Y'),'SH_CLERK',3900,NULL,123,50);
%Y'),'ST_MAN',7900,NULL,100,50); %Y'),'SA_REP',9000,.35,146,80);
INSERT INTO employees VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876',STR_TO_DATE('01-JUL-1998', '%d-%M-
INSERT INTO employees VALUES (123,'Shanta','Vollman','SVOLLMAN','650.123.4234',STR_TO_DATE('10-OCT-1997', '%d-%M- INSERT INTO employees VALUES (159,'Lindsey','Smith','LSMITH','011.44.1345.729268',STR_TO_DATE('10-MAR-1997', '%d-%M- %Y'),'SH_CLERK',3200,NULL,123,50);
%Y'),'ST_MAN',6500,NULL,100,50); %Y'),'SA_REP',8000,.3,146,80);
INSERT INTO employees VALUES (195,'Vance','Jones','VJONES','650.501.4876',STR_TO_DATE('17-MAR-1999', '%d-%M-
INSERT INTO employees VALUES (124,'Kevin','Mourgos','KMOURGOS','650.123.5234',STR_TO_DATE('16-NOV-1999', '%d-%M- INSERT INTO employees VALUES (160,'Louise','Doran','LDORAN','011.44.1345.629268',STR_TO_DATE('15-DEC-1997', '%d-%M- %Y'),'SH_CLERK',2800,NULL,123,50);
%Y'),'ST_MAN',5800,NULL,100,50); %Y'),'SA_REP',7500,.3,146,80);
INSERT INTO employees VALUES (196,'Alana','Walsh','AWALSH','650.507.9811',STR_TO_DATE('24-APR-1998', '%d-%M-
INSERT INTO employees VALUES (125,'Julia','Nayer','JNAYER','650.124.1214',STR_TO_DATE('16-JUL-1997', '%d-%M-%Y'),'ST_CLERK',3200,NULL,120,50); INSERT INTO employees VALUES (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268',STR_TO_DATE('03-NOV-1998', '%d-%M- %Y'),'SH_CLERK',3100,NULL,124,50);
%Y'),'SA_REP',7000,.25,146,80);
INSERT INTO employees VALUES (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224',STR_TO_DATE('28-SEP-1998', '%d-%M- INSERT INTO employees VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822',STR_TO_DATE('23-MAY-1998', '%d-%M-

[Link]
%Y'),'ST_CLERK',2700,NULL,120,50); INSERT INTO employees VALUES (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268',STR_TO_DATE('11-NOV-1997', '%d-%M- %Y'),'SH_CLERK',3000,NULL,124,50);
%Y'),'SA_REP',10500,.25,147,80);
INSERT INTO employees VALUES (127,'James','Landry','JLANDRY','650.124.1334',STR_TO_DATE('14-JAN-1999', '%d-%M- INSERT INTO employees VALUES (198,'Donald','OConnell','DOCONNEL','650.507.9833',STR_TO_DATE('21-JUN-1999', '%d-%M-
%Y'),'ST_CLERK',2400,NULL,120,50); INSERT INTO employees VALUES (163,'Danielle','Greene','DGREENE','011.44.1346.229268',STR_TO_DATE('19-MAR-1999', '%d-%M- %Y'),'SH_CLERK',2600,NULL,124,50);
%Y'),'SA_REP',9500,.15,147,80);
INSERT INTO employees VALUES (128,'Steven','Markle','SMARKLE','650.124.1434',STR_TO_DATE('08-MAR-2000', '%d-%M-
INSERT INTO employees VALUES (199,'Douglas','Grant','DGRANT','650.507.9844',STR_TO_DATE('13-JAN-2000', '%d-%M-
%Y'),'ST_CLERK',2200,NULL,120,50); INSERT INTO employees VALUES (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268',STR_TO_DATE('24-JAN-2000', '%d-%M-
%Y'),'SH_CLERK',2600,NULL,124,50);
%Y'),'SA_REP',7200,.10,147,80);
INSERT INTO employees VALUES (129,'Laura','Bissot','LBISSOT','650.124.5234',STR_TO_DATE('20-AUG-1997', '%d-%M-
INSERT INTO employees VALUES (200,'Jennifer','Whalen','JWHALEN','515.123.4444',STR_TO_DATE('17-SEP-1987', '%d-%M-
%Y'),'ST_CLERK',3300,NULL,121,50); INSERT INTO employees VALUES (165,'David','Lee','DLEE','011.44.1346.529268',STR_TO_DATE('23-FEB-2000', '%d-%M-%Y'),'SA_REP',6800,.1,147,80);
%Y'),'AD_ASST',4400,NULL,101,10);
INSERT INTO employees VALUES (130,'Mozhe','Atkinson','MATKINSO','650.124.6234',STR_TO_DATE('30-OCT-1997', '%d-%M- INSERT INTO employees VALUES (166,'Sundar','Ande','SANDE','011.44.1346.629268',STR_TO_DATE('24-MAR-2000', '%d-%M-
%Y'),'ST_CLERK',2800,NULL,121,50); %Y'),'SA_REP',6400,.10,147,80); INSERT INTO employees VALUES (201,'Michael','Hartstein','MHARTSTE','515.123.5555',STR_TO_DATE('17-FEB-1996', '%d-%M-
%Y'),'MK_MAN',13000,NULL,100,20);
INSERT INTO employees VALUES (131,'James','Marlow','JAMRLOW','650.124.7234',STR_TO_DATE('16-FEB-1997', '%d-%M- INSERT INTO employees VALUES (167,'Amit','Banda','ABANDA','011.44.1346.729268',STR_TO_DATE('21-APR-2000', '%d-%M-
%Y'),'ST_CLERK',2500,NULL,121,50); %Y'),'SA_REP',6200,.10,147,80); INSERT INTO employees VALUES (202,'Pat','Fay','PFAY','603.123.6666',STR_TO_DATE('17-AUG-1997', '%d-%M-%Y'),'MK_REP',6000,NULL,201,20);

INSERT INTO employees VALUES (132,'TJ','Olson','TJOLSON','650.124.8234',STR_TO_DATE('10-APR-1999', '%d-%M-%Y'),'ST_CLERK',2100,NULL,121,50); INSERT INTO employees VALUES (168,'Lisa','Ozer','LOZER','011.44.1343.929268',STR_TO_DATE('11-MAR-1997', '%d-%M- INSERT INTO employees VALUES (203,'Susan','Mavris','SMAVRIS','515.123.7777',STR_TO_DATE('07-JUN-1994', '%d-%M-
%Y'),'SA_REP',11500,.25,148,80); %Y'),'HR_REP',6500,NULL,101,40);
INSERT INTO employees VALUES (133,'Jason','Mallin','JMALLIN','650.127.1934',STR_TO_DATE('14-JUN-1996', '%d-%M-
%Y'),'ST_CLERK',3300,NULL,122,50); INSERT INTO employees VALUES (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268',STR_TO_DATE('23-MAR-1998', '%d-%M- INSERT INTO employees VALUES (204,'Hermann','Baer','HBAER','515.123.8888',STR_TO_DATE('07-JUN-1994', '%d-%M-

INSERT INTO employees VALUES (134,'Michael','Rogers','MROGERS','650.127.1834',STR_TO_DATE('26-AUG-1998', '%d-%M-

%Y'),'ST_CLERK',2900,NULL,122,50);
%Y'),'SA_REP',10000,.20,148,80);
INSERT INTO employees VALUES (170,'Tayler','Fox','TFOX','011.44.1343.729268',STR_TO_DATE('24-JAN-1998', '%d-%M-%Y'),'SA_REP',9600,.20,148,80);
%Y'),'PR_REP',10000,NULL,101,70);
INSERT INTO employees VALUES (205,'Shelley','Higgins','SHIGGINS','515.123.8080',STR_TO_DATE('07-JUN-1994', '%d-%M-
8
%Y'),'AC_MGR',12000,NULL,101,110);
INSERT INTO employees VALUES (135,'Ki','Gee','KGEE','650.127.1734',STR_TO_DATE('12-DEC-1999', '%d-%M-%Y'),'ST_CLERK',2400,NULL,122,50); INSERT INTO employees VALUES (171,'William','Smith','WSMITH','011.44.1343.629268',STR_TO_DATE('23-FEB-1999', '%d-%M-
%Y'),'SA_REP',7400,.15,148,80); INSERT INTO employees VALUES (206,'William','Gietz','WGIETZ','51hr5.123.8181',STR_TO_DATE('07-JUN-1994', '%d-%M-
Now, we need to add foreign keys
to tables departments and employees
ALTER TABLE departments ADD FOREIGN KEY (location_id) REFERENCES
locations(location_id);
ALTER TABLE employees ADD FOREIGN KEY (department_id)
REFERENCES departments(department_id);
ALTER TABLE employees ADD FOREIGN KEY (manager_id) REFERENCES
employees(employee_id);
ALTER TABLE departments ADD FOREIGN KEY (manager_id) REFERENCES

[Link]
employees (employee_id);

9
Here, our tables with records
select * from locations;

[Link]
10
Here, our tables with records
select * from departments;

[Link]
11
Here, our tables with records
select * from employees;

[Link]
12
Subqueries

[Link]
13
 Subqueries
• Nested query
• Contains outer & inner SELECT statements.
• The result of inner SELECT (subquery) is used by the outer query to determine
the final output.
• Subqueries written at:
• WHERE or HAVING clause
• SELECT or FROM clause
• When subquery written at SELECT clause, the subquery output is a single
value.

[Link]
14
 Subqueries
• Example 1:
• List employee id and salary of all
employees who are working in the
sales department.
SELECT employee_id, salary
FROM employees
WHERE department_id =
( SELECT department_id
FROM departments
WHERE department_name =
'Sales' );

[Link]
15
 Subqueries
• What if we want also to display the department name in the result?
How should the statement be written?
SELECT employee_id, salary, department_name
FROM employees
WHERE department_id =
( SELECT department_id
FROM departments
WHERE department_name = 'Sales') ;

[Link]
• Will this query execute correctly???

16
 Subqueries
• What if we want also to display the department name in the result?
How should the statement be written?
SELECT employee_id, salary, department_name
FROM employees
WHERE department_id =
( SELECT department_id
FROM departments
WHERE department_name = 'Sales') ;

[Link]
• Will this query execute correctly??? No

17
 Join

18

[Link]
 Obtaining Data From Multiple Tables
• Sometimes you need to use and display data from more than one
table.
• Employee IDs exist in the EMPLOYEES table.
• Department IDs exist in both the EMPLOYEES and DEPARTMENTS tables.
• Department names exist in the DEPARTMENTS table.
• To produce the report, you need to join the EMPLOYEES and
DEPARTMENTS tables, and access data from both of them.

[Link]
19
Obtaining Data From Multiple Tables

[Link]
20
 Simple Join / Traditional Method
Query 1:
• List the employee first name, last name and employee salary for the
SALES department.
SELECT first_name, last_name, salary
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND departments.department_name = 'Sales';

This is a simple join

*without the use of

[Link]
any special syntax in
SQL

21
 Simple Join / Traditional Method
Query 2:
• List all employees id, first name, last name and department name.
SELECT employee_id, first_name, last_name,
department_name
FROM employees, departments
WHERE employees.department_id =
departments.department_id;

[Link]
22
 Simple Join / Traditional Method
Query 3:
• List all employee id, first name, last name, department id and
department name.
SELECT employee_id, first_name, last_name, department_name,
department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

[Link]
Why error occur?
How to correct the error?

23
 Simple Join / Traditional Method
Query 3:
• List all employee id, first name, last name, department id and
department name.
SELECT employee_id, first_name, last_name, department_name,
employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

To solve the ambiguous column issue,

[Link]
use Table prefix to correct the error

24
Joining Tables Using SQL:1999 syntax
• Use a join to query data from more than one table:

[ ] = optional

[Link]
25
 Equijoins
• That is where a column (or multiple columns) in two or more tables
match
• Can write using:
• Simple join:
• SELECT…FROM…WHERE
• ANSI syntax:
• NATURAL JOIN
• JOIN…USING
• JOIN…ON

[Link]
• INNER JOIN…ON

26
Natural Join

[Link]
27
 Using NATURAL JOIN
• The NATURAL JOIN clause is based on all columns in the two tables
that have the same name.
• It selects rows from the two tables that have equal values in all
matched columns.
• If the columns having the same names have different data types, an
error is returned.

[Link]
28
 Using NATURAL JOIN
Query 2:
• List all employees id, first name, last name and department name.
SELECT employee_id, first_name, last_name,
department_name
FROM employees
NATURAL JOIN departments;

[Link]
29
 Using NATURAL JOIN
Query 3:
• List all employee id, first name, last name, department id and
department name.
SELECT employee_id, first_name, last_name, department_name,
department_id
FROM employees
NATURAL JOIN departments;

[Link]
30
 Using NATURAL JOIN
Query 4:
• List all employee id, first name, last name, department name and
department location id.
SELECT employee_id, first_name, last_name, department_name, location_id
FROM employees
NATURAL JOIN departments;

[Link]
31
 Using NATURAL JOIN
Query 4b:
• List all employee id, first name, last name,
department name and city.
SELECT employee_id, first_name, last_name, department_name, city
FROM employees
NATURAL JOIN departments
NATURAL JOIN locations;

Natural join with

multiple tables

[Link]
32
Join … Using

Join … On

[Link]
33
 Using JOIN…USING clause
• If several columns have the same names but the data types do not
match, use the USING clause to specify the columns for the equijoin.
• Use the USING clause to match only one column when more than one
column matches.

[Link]
34
 Using JOIN…USING clause
Query 3:
• List all employee id, first name, last name, department id and
department name.
SELECT employee_id, first_name, last_name, department_id,
department_name
FROM employees
JOIN departments
USING (department_id);

Join based on the

department_id
attribute

[Link]
35
 Using JOIN…ON clause
• The JOIN condition for the NATURAL JOIN is basically an equijoin of
all columns with the same name.
• Use the ON clause to specify arbitrary conditions or specify columns
to join.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand.

[Link]
36
 Using JOIN…ON clause
Query 3:
• List all employee id, first name, last name, department id and
department name.
SELECT employee_id, first_name, last_name, employees.department_id,
department_name
FROM employees
JOIN departments
ON (employees.department_id = departments.department_id);

[Link]
37
 Using JOIN…ON clause
Query 4:
• List all employee id, first name, last name, department name and
department location id.
SELECT employee_id, first_name, last_name, department_name, location_id
FROM employees
JOIN departments
ON (employees.department_id = departments.department_id);

[Link]
38
 Using JOIN…ON clause
Query 4b:
• List all employee id, first name, last name, department name and city.
SELECT employee_id, first_name, last_name, department_name, city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

Using JOIN…ON
with multiple
tables

[Link]
39
 Applying Additional Conditions to a Join
• Use the AND clause or the WHERE clause to apply additional
conditions:

[Link]
40
 Self Join
• A self join is a special form of equijoin or INNER JOIN where a table is
joined against itself.
• This means that the table must exists two times in the FROM clause
of the SQL query.
• Note that when joining a table to itself an alias must be used for each
of the tables in the FROM clause and then also used in the select list
and WHERE clause

[Link]
41
Inner Join … On

[Link]
42
 Self join using INNER JOIN…ON
Query 5:
• List all employee id, first name, last name and their manager’s id and
first name, last name.
SELECT e.employee_id, e.first_name, e.last_name, m.employee_id MANAGER_ID, m.first_name
MANAGER_FNAME, m.last_name MANAGER_LNAME
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

[Link]
43
 Self join using INNER JOIN…ON
Query 5:
• List all employee id, first name, last name and their manager’s id and
first name, last name.
SELECT e.employee_id, e.first_name, e.last_name, m.employee_id MANAGER_ID, m.first_name MANAGER_FNAME,
m.last_name MANAGER_LNAME
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

JOIN and INNER JOIN

are functionally

[Link]
equivalent

44
 Self join using INNER JOIN…ON
Query 6:
• List all employee who are the manager.
SELECT DISTINCT e.manager_id, m.first_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

[Link]
45
 Using INNER JOIN…ON
Query 3:
• List all employee id, first name, last name, department id and
department name.
SELECT employee_id, first_name, last_name, employees.department_id,
department_name
FROM employees
INNER JOIN departments
ON (employees.department_id = departments.department_id);

[Link]
46
 Using INNER JOIN…ON
Query 4:
• List all employee id, first name, last name, department name and
department location id.
SELECT employee_id, first_name, last_name, department_name, location_id
FROM employees
INNER JOIN departments
ON (employees.department_id = departments.department_id);

[Link]
47
Outer Join

[Link]
48
 OUTER JOIN
• Often times we need to return rows from one table even if there are
no matching rows that are produced through a join condition. For this
situation, we use outer joins.
• Outer joins:
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

[Link]
49
 LEFT OUTER JOIN
• A left outer join is where the table, on the left of a FROM clause is
required to return all of its rows regardless of having matching rows
from the table it is being joined on.

[Link]
50
 Using LEFT OUTER JOIN
Query 7:
• List all department with all its employees, including all department
without employee.
SELECT department_name, first_name
FROM departments
LEFT OUTER JOIN employees
ON (departments.department_id = employees.department_id);

[Link]
51
 RIGHT OUTER JOIN
• A right outer join is just the opposite of a left outer join. It states that
you would like all rows from the right table in the FROM clause to be
returned regardless of having a true match defined in the WHERE
clause against the left side table in the FROM clause.

[Link]
52
 Using RIGHT OUTER JOIN
Query 8:
• List all employees with their departments, including all employees
without department.
SELECT department_name, first_name
FROM departments
RIGHT OUTER JOIN employees
ON (departments.department_id = employees.department_id);

[Link]
53
 FULL OUTER JOIN
• Return both left and right sides of a query regardless of having a
match.

Note:
MySQL does not directly support a FULL OUTER JOIN.
However, you can achieve the same result
by combining a LEFT JOIN and a RIGHT JOIN with
a UNION operator.

[Link]
54
 Using FULL OUTER JOIN
• How can we get the records for all employees AND all
departments whether they are missing data or not?
ORACLE:
SELECT departments.department_name, employees.first_name
FROM departments
FULL OUTER JOIN employees
ON (departments.department_id = employees.department_id);

MySQL:
SELECT departments.department_name, employees.first_name
FROM departments
LEFT JOIN employees ON departments.department_id = employees.department_id
UNION
SELECT departments.department_name, employees.first_name

[Link]
FROM departments
RIGHT JOIN employees ON departments.department_id = employees.department_id;

55
56