SQL> select bdate,address 2 from employee 3 where fname='John' and minit='B' and lname='Smith';

BDATE ADDRESS
-------- ------------------------------
65/01/09 731 Fondren, Houston, TX

SQL> select fname,lname,address 2 from employee,department 3 where dname='Research' and 4 dnumber=dno;

FNAME LNAME ADDRESS
------------------------------ ------------------------------ ------------------------------
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houton, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyco English 5631 Rice, Houston, TX

SQL> select pnumber,dnum,lname,address,bdate 2 from project,department,employee 3 where dnum=dnumber and Mgr_ssn=ssn and plocation='Stafford';

PNUMBER DNUM LNAME ADDRESS BDATE
---------- ---------- ------------------------------ ------------------------------ -------- 10 4 Wallace 291 Berry, Bellaire, TX 41/06/20 30 4 Wallace 291 Berry, Bellaire, TX 41/06/20

SQL> select fname, employee.lname, address 2 from employee, department 3 where department.dname='research' and department.dnumber=employee.dno;

선택된 레코드가 없습니다.

SQL> SELECT EMPLOYEE.Fname, EMPLOYEE.Lname, EMPLOYEE.Address 2 FROM EMPLOYEE, DEPARTMENT 3 WHERE DEPARTMENT.Dname='Research' AND DEPARTMENT.Dnumber=EMPLOYEE.Dno;

FNAME LNAME ADDRESS
------------------------------ ------------------------------ ------------------------------
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houton, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyco English 5631 Rice, Houston, TX

SQL> select e.fname, e.lname, s.fname, s.lname 2 from employee e, employee s 3 where e.super_ssn = s.ssn;

FNAME LNAME FNAME LNAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
Ramesh Narayan Franklin Wong
John Smith Franklin Wong
Joyco English Franklin Wong
Franklin Wong James Borg
Jennifer Wallace James Borg
Alicia Zelaya Jennifer Wallace
Ahmad Jabbar Jennifer Wallace

7 개의 행이 선택되었습니다.

SQL> SELECT E.Fname, E.LName, E.Address 2 FROM EMPLOYEE E, DEPARTMENT D 3 WHERE D.DName='Research' AND D.Dnumber=E.Dno;

FNAME LNAME ADDRESS
------------------------------ ------------------------------ ------------------------------
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houton, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyco English 5631 Rice, Houston, TX

SQL> select ssn 2 from employee;

SSN
------------------
123456789
333445555
453453453
666884444
888665555
987654321
987987987
999887777

8 개의 행이 선택되었습니다.

SQL> select ssn,dname 2 from employee,department;

SSN DNAME
------------------ ------------------------------
123456789 Administration
333445555 Administration
453453453 Administration
666884444 Administration
888665555 Administration
987654321 Administration
987987987 Administration
999887777 Administration
123456789 Headquarters
333445555 Headquarters
453453453 Headquarters
666884444 Headquarters
888665555 Headquarters
987654321 Headquarters
987987987 Headquarters
999887777 Headquarters
123456789 Research
333445555 Research
453453453 Research
666884444 Research
888665555 Research
987654321 Research
987987987 Research
999887777 Research

24 개의 행이 선택되었습니다.

SQL> SELECT * FROM EMPLOYEE WHERE Dno=5;

FNAME MI LNAME SSN BDATE ADDRESS SE SALARY SUPER_SSN DNO
------------------------------ -- ------------------------------ ------------------ -------- ------------------------------------------------------------ -- ---------- ------------------ ----------
John B Smith 123456789 65/01/09 731 Fondren, Houston, TX M 30000 333445555 5
Franklin T Wong 333445555 55/12/08 638 Voss, Houton, TX M 40000 888665555 5
Ramesh K Narayan 666884444 62/09/15 975 Fire Oak, Humble, TX M 38000 333445555 5
Joyco A English 453453453 72/07/31 5631 Rice, Houston, TX F 25000 333445555 5

SQL> SELECT * 2 FROM EMPLOYEE, DEPARTMENT 3 WHERE Dname='Research' AND Dno=Dnumber;

FNAME MI LNAME SSN BDATE ADDRESS SE SALARY SUPER_SSN DNO DNAME DNUMBER MGR_SSN MGR_STAR
------------------------------ -- ------------------------------ ------------------ -------- ------------------------------------------------------------ -- ---------- ------------------ ---------- ------------------------------ ---------- ------------------ --------
John B Smith 123456789 65/01/09 731 Fondren, Houston, TX M 30000 333445555 5 Research 5 333445555 88/05/22
Franklin T Wong 333445555 55/12/08 638 Voss, Houton, TX M 40000 888665555 5 Research 5 333445555 88/05/22
Ramesh K Narayan 666884444 62/09/15 975 Fire Oak, Humble, TX M 38000 333445555 5 Research 5 333445555 88/05/22
Joyco A English 453453453 72/07/31 5631 Rice, Houston, TX F 25000 333445555 5 Research 5 333445555 88/05/22

SQL> SELECT * 2 FROM EMPLOYEE, DEPARTMENT;

FNAME MI LNAME SSN BDATE ADDRESS SE SALARY SUPER_SSN DNO DNAME DNUMBER MGR_SSN MGR_STAR
------------------------------ -- ------------------------------ ------------------ -------- ------------------------------------------------------------ -- ---------- ------------------ ---------- ------------------------------ ---------- ------------------ --------
John B Smith 123456789 65/01/09 731 Fondren, Houston, TX M 30000 333445555 5 Research 5 333445555 88/05/22
Franklin T Wong 333445555 55/12/08 638 Voss, Houton, TX M 40000 888665555 5 Research 5 333445555 88/05/22
Alicia J Zelaya 999887777 68/01/19 3321 Castle, Spring, TX F 25000 987654321 4 Research 5 333445555 88/05/22
Jennifer S Wallace 987654321 41/06/20 291 Berry, Bellaire, TX F 43000 888665555 4 Research 5 333445555 88/05/22
Ramesh K Narayan 666884444 62/09/15 975 Fire Oak, Humble, TX M 38000 333445555 5 Research 5 333445555 88/05/22
Joyco A English 453453453 72/07/31 5631 Rice, Houston, TX F 25000 333445555 5 Research 5 333445555 88/05/22
Ahmad V Jabbar 987987987 69/03/29 980 Dallas, Houston, TX M 25000 987654321 4 Research 5 333445555 88/05/22
James E Borg 888665555 37/11/10 450 Stone, Houston, TX M 55000 1 Research 5 333445555 88/05/22
John B Smith 123456789 65/01/09 731 Fondren, Houston, TX M 30000 333445555 5 Administration 4 987654321 95/01/01
Franklin T Wong 333445555 55/12/08 638 Voss, Houton, TX M 40000 888665555 5 Administration 4 987654321 95/01/01
Alicia J Zelaya 999887777 68/01/19 3321 Castle, Spring, TX F 25000 987654321 4 Administration 4 987654321 95/01/01
Jennifer S Wallace 987654321 41/06/20 291 Berry, Bellaire, TX F 43000 888665555 4 Administration 4 987654321 95/01/01
Ramesh K Narayan 666884444 62/09/15 975 Fire Oak, Humble, TX M 38000 333445555 5 Administration 4 987654321 95/01/01
Joyco A English 453453453 72/07/31 5631 Rice, Houston, TX F 25000 333445555 5 Administration 4 987654321 95/01/01
Ahmad V Jabbar 987987987 69/03/29 980 Dallas, Houston, TX M 25000 987654321 4 Administration 4 987654321 95/01/01
James E Borg 888665555 37/11/10 450 Stone, Houston, TX M 55000 1 Administration 4 987654321 95/01/01
John B Smith 123456789 65/01/09 731 Fondren, Houston, TX M 30000 333445555 5 Headquarters 1 888665555 81/06/19
Franklin T Wong 333445555 55/12/08 638 Voss, Houton, TX M 40000 888665555 5 Headquarters 1 888665555 81/06/19
Alicia J Zelaya 999887777 68/01/19 3321 Castle, Spring, TX F 25000 987654321 4 Headquarters 1 888665555 81/06/19
Jennifer S Wallace 987654321 41/06/20 291 Berry, Bellaire, TX F 43000 888665555 4 Headquarters 1 888665555 81/06/19
Ramesh K Narayan 666884444 62/09/15 975 Fire Oak, Humble, TX M 38000 333445555 5 Headquarters 1 888665555 81/06/19
Joyco A English 453453453 72/07/31 5631 Rice, Houston, TX F 25000 333445555 5 Headquarters 1 888665555 81/06/19
Ahmad V Jabbar 987987987 69/03/29 980 Dallas, Houston, TX M 25000 987654321 4 Headquarters 1 888665555 81/06/19
James E Borg 888665555 37/11/10 450 Stone, Houston, TX M 55000 1 Headquarters 1 888665555 81/06/19

24 개의 행이 선택되었습니다.

SQL> select all salary 2 from employee;

SALARY
----------
30000 40000 25000 43000 38000 25000 25000 55000

8 개의 행이 선택되었습니다.

SQL> SELECT DISTINCT Salary 2 FROM EMPLOYEE;

SALARY
----------
38000 43000 55000 30000 40000 25000

6 개의 행이 선택되었습니다.

SQL> (select distinct pnumber 2 from project,department,employee 3 where dnum=dnumber and mgr_ssn=ssn and lname='Smith') 4 union 5 (select pnumber 6 from project,works_on,employee 7 where pnumber=pno and essn=ssn and lname='Smith');

PNUMBER
----------
1 2

SQL> select fname,lname 2 from employee 3 where address like '%Houston, TX%';

FNAME LNAME
------------------------------ ------------------------------
John Smith
Joyco English
Ahmad Jabbar
James Borg

SQL> SELECT Fname, Lname 2 FROM EMPLOYEE 3 WHERE Bdate LIKE '_ _ 5 _ _ _ _ _ _ _';

선택된 레코드가 없습니다.

SQL> select fname,lname,1.1*salary as increased_sal 2 from employee,works_on,project 3 where ssn=essn and pno=pnumber and pname='ProductX';

FNAME LNAME INCREASED_SAL
------------------------------ ------------------------------ -------------
John Smith 33000
Joyco English 27500

SQL> select * 2 from employee 3 where dno=5 and (salary between 30000 and 40000);

FNAME MI LNAME SSN BDATE ADDRESS SE SALARY SUPER_SSN DNO
------------------------------ -- ------------------------------ ------------------ -------- ------------------------------------------------------------ -- ---------- ------------------ ----------
John B Smith 123456789 65/01/09 731 Fondren, Houston, TX M 30000 333445555 5
Franklin T Wong 333445555 55/12/08 638 Voss, Houton, TX M 40000 888665555 5
Ramesh K Narayan 666884444 62/09/15 975 Fire Oak, Humble, TX M 38000 333445555 5

SQL> select dname,lname,fname,pname 2 from department,employee,works_on,project 3 where dnumber=dno and ssn=essn and pno=pnumber 4 order by dname,lname,fname;

DNAME LNAME FNAME PNAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
Administration Jabbar Ahmad Computerization
Administration Jabbar Ahmad Newbenefits
Administration Wallace Jennifer Reorganization
Administration Wallace Jennifer Newbenefits
Administration Zelaya Alicia Computerization
Administration Zelaya Alicia Newbenefits
Headquarters Borg James Reorganization
Research English Joyco ProductX
Research English Joyco ProductY
Research Narayan Ramesh ProductZ
Research Smith John ProductY
Research Smith John ProductX
Research Wong Franklin Computerization
Research Wong Franklin Reorganization
Research Wong Franklin ProductY
Research Wong Franklin ProductZ

16 개의 행이 선택되었습니다.

SQL> select fname,lname 2 from employee 3 where super_ssn is null;

FNAME LNAME
------------------------------ ------------------------------
James Borg

SQL> SELECT DISTINCT Pnumber 2 FROM PROJECT 3 WHERE Pnumber IN 4 (SELECT Pnumber 5 FROM PROJECT, DEPARTMENT, EMPLOYEE 6 WHERE Dnum=Dnumber AND 7 Mgr_ssn=Ssn AND Lname='Smith') 8 OR Pnumber IN 9 (SELECT Pno 10 FROM WORKS_ON, EMPLOYEE 11 WHERE Essn=Ssn AND Lname='Smith');

PNUMBER
----------
1 2

SQL> select e.fname,e.lname 2 from employee e 3 where e.ssn in (select essn 4 from dependent 5 where e.fname=dependent_name and e.sex=sex);

선택된 레코드가 없습니다.

SQL> SELECT E.Fname, E.Lname 2 FROM EMPLOYEE E, DEPENDENT D 3 WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name;

선택된 레코드가 없습니다.

SQL> SELECT E.Fname, E.Lname 2 FROM EMPLOYEE E 3 WHERE EXISTS (SELECT * FROM DEPENDENT D WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name);

선택된 레코드가 없습니다.

SQL> select fname,lname 2 from employee 3 where not exists (select * from dependent where ssn=essn);

FNAME LNAME
------------------------------ ------------------------------
Joyco English
Ramesh Narayan
James Borg
Ahmad Jabbar
Alicia Zelaya

SQL> select fname,lname 2 from employee 3 where exists (select * from department where ssn=mgr_ssn) and 4 exists (select * from dependent where ssn=essn);

FNAME LNAME
------------------------------ ------------------------------
Franklin Wong
Jennifer Wallace

SQL> SELECT Fname, Lname 2 FROM EMPLOYEE 3 WHERE NOT EXISTS (( 4 SELECT Pnumber 5 FROM PROJECT 6 WHERE Dnum=5) MINUS ( 7 SELECT Pno 8 FROM WORKS_ON 9 WHERE Ssn=Essn));

선택된 레코드가 없습니다.

SQL> SELECT Lname, Fname 2 FROM EMPLOYEE 3 WHERE NOT EXISTS (SELECT * 4 FROM WORKS_ON B 5 WHERE (B.Pno IN ( 6 SELECT Pnumber 7 FROM PROJECT 8 WHERE Dnum=5) AND NOT EXISTS ( 9 SELECT * 10 FROM WORKS_ON C 11 WHERE C.Essn=Ssn AND C.Pno=B.Pno)));

선택된 레코드가 없습니다.

SQL> select distinct essn 2 from works_on 3 where pno in (1,2,3);

ESSN
------------------
123456789
333445555
453453453
666884444

SQL> select e.lname employee_name, s.lname supervisor_name 2 from employee e, employee s 3 where e.super_ssn = s.ssn;

EMPLOYEE_NAME SUPERVISOR_NAME
------------------------------ ------------------------------
Narayan Wong
Smith Wong
English Wong
Wong Borg
Wallace Borg
Zelaya Wallace
Jabbar Wallace

7 개의 행이 선택되었습니다.

SQL> select fname, lname, address 2 from (employee join department on dno=dnumber) 3 where dname='research';

선택된 레코드가 없습니다.

SQL> select fname, lname, address 2 from employee natural join 3 (select dname, dnumber as dno, mgr_ssn as Mssn, mgr_start_date as msdate 4 from department) dept 5 where dname='research';

선택된 레코드가 없습니다.

SQL> select e.lname as employee_name, s.lname as supervisor_name 2 from (employee e left outer join employee s on e.super_ssn=S.Ssn);

EMPLOYEE_NAME SUPERVISOR_NAME
------------------------------ ------------------------------
English Wong
Narayan Wong
Smith Wong
Jabbar Wallace
Zelaya Wallace
Wallace Borg
Wong Borg
Borg

8 개의 행이 선택되었습니다.

SQL> SELECT Pnumber, Dnum, Lname, Address, Bdate 2 FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber) 3 JOIN EMPLOYEE ON Mgr_ssn=Ssn) 4 WHERE Plocation='Stafford';

PNUMBER DNUM LNAME ADDRESS BDATE
---------- ---------- ------------------------------ ------------------------------------------------------------ -------- 10 4 Wallace 291 Berry, Bellaire, TX 41/06/20 30 4 Wallace 291 Berry, Bellaire, TX 41/06/20

SQL> SELECT E.Lname, S.Lname 2 FROM EMPLOYEE E, EMPLOYEE S 3 WHERE E.Super_ssn = E.Super_ssn + S.Ssn;

선택된 레코드가 없습니다.

SQL> select sum(salary),max(salary),min(salary),avg(salary) 2 from employee;

SUM(SALARY) MAX(SALARY) MIN(SALARY) AVG(SALARY)
----------- ----------- ----------- ----------- 281000 55000 25000 35125

SQL> select sum(salary),max(salary),min(salary),avg(salary) 2 from (employee JOIN department ON dno=dnumber) 3 where dname='Research';

SUM(SALARY) MAX(SALARY) MIN(SALARY) AVG(SALARY)
----------- ----------- ----------- ----------- 133000 40000 25000 33250

SQL> select count(*) 2 from employee;

COUNT(*)
----------
8

SQL> select count(*) 2 from employee,department 3 where dno=dnumber and dname='Research';

COUNT(*)
----------
4

SQL> select count(distinct salary) 2 from employee;

COUNT(DISTINCTSALARY)
---------------------
6

SQL> select lname,fname 2 from employee 3 where (select count(*) from dependent where ssn=essn) >=2;

LNAME FNAME
------------------------------ ------------------------------
Smith John
Wong Franklin

SQL> select dno,count(*),avg(salary) 2 from employee 3 group by dno;

DNO COUNT(*) AVG(SALARY)
---------- ---------- ----------- 1 1 55000 5 4 33250 4 3 31000

SQL> select pnumber,pname,count(*) 2 from project,works_on 3 where pnumber=pno 4 group by pnumber,pname;

PNUMBER PNAME COUNT(*)
---------- ------------------------------ ---------- 20 Reorganization 3 1 ProductX 2 10 Computerization 3 30 Newbenefits 3 2 ProductY 3 3 ProductZ 2

6 개의 행이 선택되었습니다.

SQL> select pnumber,pname,count(*) 2 from project,works_on 3 where pnumber=pno 4 group by pnumber,pname 5 having count(*)>2;

PNUMBER PNAME COUNT(*)
---------- ------------------------------ ---------- 20 Reorganization 3 10 Computerization 3 30 Newbenefits 3 2 ProductY 3

SQL> select pnumber,pname,count(*) 2 from project,works_on,employee 3 where pnumber=pno and ssn=essn and dno=5 4 group by pnumber,pname;

PNUMBER PNAME COUNT(*)
---------- ------------------------------ ---------- 20 Reorganization 1 1 ProductX 2 10 Computerization 1 2 ProductY 3 3 ProductZ 2

SQL> select dnumber,count(*) 2 from department,employee 3 where dnumber=dno and salary>40000 and 4 dno in (select dno from employee group by dno having count(*)>5) 5 group by dnumber;

선택된 레코드가 없습니다.