Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.2.3-falcon-alpha-community-nt MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database PolyCompany;
Query OK, 1 row affected (0.00 sec)
mysql> use PolyCompany;
Database changed
mysql> create table EMPLOYEE(FNAME varchar(30) not null, MINIT char(1) not null,
LNAME varchar(30) not null, SSN integer default 9 not null, BDATE date, ADDRESS
varchar(50) not null, SEX char(1) not null, SALARY integer default 5 not null,
SUPERSSN integer default 9, DNO integer default 1 not null);
Query OK, 0 rows affected (0.14 sec)
mysql> alter table EMPLOYEE add primary key(SSN);
Query OK, 8 rows affected (0.29 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> describe EMPLOYEE;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| FNAME | varchar(30) | NO | | | |
| MINIT | char(1) | NO | | | |
| LNAME | varchar(30) | NO | | | |
| SSN | int(11) | NO | PRI | 9 | |
| BDATE | date | YES | | NULL | |
| ADDRESS | varchar(50) | NO | | | |
| SEX | char(1) | NO | | | |
| SALARY | int(11) | NO | | 5 | |
| SUPERSSN | int(11) | YES | | 9 | |
| DNO | int(11) | NO | | 1 | |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0.09 sec)
mysql> insert into employee values('John','B','Smith','12345
731 Fondren,Houston,TX','M','30000','333445555','5'),('Frank
45555','1955-12-08','638 Vose,Houston,TX','M','40000','88866
,'J','Zelaya','999887777','1968-07-19','3321 Castle,Spring,T
54321','4'),('Jennifer','S','Wallace','987654321','1941-06-2
ire, TX','F','43000','888665555','4'),('Ramesh','K','Narayan
09-15','975 Fire Oak,umble, TX','M','38000','333445555','5')
sh','453453453','1972-07-31','5631 Rice, Houston, TX','F','2
'),('Ahmad','V','Jabbar','987987987','1969-03-29','980 Dalla
'25000','897654321','4'),('James','E','Borg','888665555','19
,Huoston, TX','M','55000',' ','1');
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select*from employee;
+----------+-------+---------+-----------+------------+-----
+-----+--------+-----------+-----+
| FNAME | MINIT | LNAME | SSN | BDATE | ADDR
| SEX | SALARY | SUPERSSN | DNO |
+----------+-------+---------+-----------+------------+-----
+-----+--------+-----------+-----+
| John | B | Smith | 123456789 | 1965-01-09 | 731
| M | 30000 | 333445555 | 5 |
| Frankin | T | Wong | 333445555 | 1955-12-08 | 638
| M | 40000 | 888665555 | 5 |
| Alicia | J | Zelaya | 999887777 | 1968-07-19 | 3321
| F | 25000 | 987654321 | 4 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291
| F | 43000 | 888665555 | 4 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975
| M | 38000 | 333445555 | 5 |
| Joice | A | English | 453453453 | 1972-07-31 | 5631
| F | 25000 | 333445555 | 5 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980
| M | 25000 | 897654321 | 4 |
| James | E | Borg | 888665555 | 1937-11-10 | 450
| M | 55000 | 0 | 1 |
+----------+-------+---------+-----------+------------+-----
+-----+--------+-----------+-----+
8 rows in set (0.00 sec)
mysql> create table DEPARTMENT(DNAME varchar(20) not null, D
lt 1 not null, MGRSSN integer default 9 not null, MGRSTARTDA
Query OK, 0 rows affected (0.12 sec)
mysql> alter table DEPARTMENT add primary key(DNUMBER);
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> describe DEPARTMENT;
+--------------+-------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+--------------+-------------+------+-----+---------+-------
| DNAME | varchar(20) | NO | PRI | |
| DNUMBER | int(11) | NO | | 1 |
| MGRSSN | int(11) | NO | | 9 |
| MGRSTARTDATE | date | YES | | NULL |
+--------------+-------------+------+-----+---------+-------
4 rows in set (0.01 sec)
mysql> insert into department values('Research','5','3334455
Administration','4','987654321','1955-01-01'),('Headquarters
981-06-19');
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select*from department;
+----------------+---------+-----------+--------------+
| DNAME | DNUMBER | MGRSSN | MGRSTARTDATE |
+----------------+---------+-----------+--------------+
| Research | 5 | 333445555 | 1988-05-22 |
| Administration | 4 | 987654321 | 1955-01-01 |
| Headquarters | 1 | 888665555 | 1981-06-19 |
+----------------+---------+-----------+--------------+
3 rows in set (0.00 sec)
mysql> create table project(PNAME varchar(20) not null, PNUM
ull,PLOCATION varchar(20) not null,DNUM integer default 1 no
Query OK, 0 rows affected (0.10 sec)
mysql> alter table PROJECT add primary key(PNUMBER);
Query OK, 6 rows affected (0.22 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> describe project;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| PNAME | varchar(20) | NO | | | |
| PNUMBER | int(2) | NO | PRI | | |
| PLOCATION | varchar(20) | NO | | | |
| DNUM | int(11) | NO | | 1 | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into project values('ProductX','1','Bellaire',
'Sugarland','5'),('ProductZ','3','Houston','5'),('Computeriz
d','4'),('Reorganization','20','Houston','1'),('Newbenefits'
);
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select*from project;
+-----------------+---------+-----------+------+
| PNAME | PNUMBER | PLOCATION | DNUM |
+-----------------+---------+-----------+------+
| ProductX | 1 | Bellaire | 5 |
| ProdutY | 2 | Sugarland | 5 |
| ProductZ | 3 | Houston | 5 |
| Computerization | 10 | Stafford | 4 |
| Reorganization | 20 | Houston | 1 |
| Newbenefits | 30 | Stafford | 4 |
+-----------------+---------+-----------+------+
6 rows in set (0.00 sec)
mysql> create table WORKS_ON(ESSN integer default 9 not null,PNO integer(3) not
null, HOURS varchar(5));
Query OK, 0 rows affected (0.09 sec)
mysql> describe WORKS_ON;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| ESSN | int(11) | NO | | 9 | |
| PNO | int(3) | NO | | | |
| HOURS | varchar(5) | NO | | | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> insert into WORKS_ON values('123456789','1','32.5'),('123456789','2','7.5'
),('666884444','3','40.0'),('453453453','1','20.0'),('453453453','2','20.0'),('3334
45555','2','10.0'),('333445555','3','10.0'),('333445555','10','10.0'),('333445555',
'20','10.0'),('999887777','30','30.0'),('999887777','10','10.0'),('987987987','10','
35.0'),('987987987','30','50'),('987654321','30','200'),('987654321','20','15.0'),
('888665555','20','null');
Query OK, 16 rows affected (0.04 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select*from WORKS_ON;
+-----------+-----+--------+
| ESSN | PNO | HOURS |
+-----------+-----+--------+
| 123456789 | 1 | 32.5 |
| 123456789 | 2 | 7.5 |
| 666884444 | 3 | 40.0 |
| 453453453 | 1 | 20.0 |
| 453453453 | 2 | 20.0 |
| 333445555 | 2 | 10.0 |
| 333445555 | 3 | 10 0 |
| 333445555 | 10 | 10.0 |
| 333445555 | 20 | 10.0 |
| 999887777 | 30 | 30.0 |
| 999887777 | 10 | 10.0 |
| 987987987 | 10 | 35.0 |
| 987987987 | 30 | 50.0 |
| 987654321 | 30 | 2.00 |
| 987654321 | 20 | 15.0 |
| 888665555 | 20 | null |
+-----------+-----+--------+
16 rows in set (0.00 sec)
mysql> create table DEPENDENT(ESSN integer default 9 not null, DEPENDENT_NAME ch
ar(20) not null,SEX char(1) not null,BDATE date, RELATIONSHIP varchar(10) not nu
ll);
Query OK, 0 rows affected (0.08 sec)
mysql> describe DEPENDENT;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| ESSN | int(11) | NO | | 9 | |
| DEPENDENT_NAME | char(20) | NO | | | |
| SEX | char(1) | NO | | | |
| BDATE | date | YES | | NULL | |
| RELATIONSHIP | varchar(10) | NO | | | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> insert into DEPENDENT values('333445555','Alice','F','1986-04-05','DAUGHT
ER'),('333445555','Theodore','M','1983-10-25','SON'),('333445555','Joy','F','195
8-05-03','SPOUSE'),('987654321','Abner','M','1942-02-28','SPOUSE'),('123456789',
'Michael','M','1988-01-04','SON'),('123456789','Alice','F','1988-12-30','DAUGHTE
R'),('123456789','Elizabeth','F','1967-05-05','SPOUSE');
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select*from DEPENDENT;
+-----------+----------------+-----+------------+--------------+
| ESSN | DEPENDENT_NAME | SEX | BDATE | RELATIONSHIP |
+-----------+----------------+-----+------------+--------------+
| 333445555 | Alice | F | 1986-04-05 | DAUGHTER |
| 333445555 | Theodore | M | 1983-10-25 | SON |
| 333445555 | Joy | F | 1958-05-03 | SPOUSE |
| 987654321 | Abner | M | 1942-02-28 | SPOUSE |
| 123456789 | Michael | M | 1988-01-04 | SON |
| 123456789 | Alice | F | 1988-12-30 | DAUGHTER |
| 123456789 | Elizabeth | F | 1967-05-05 | SPOUSE |
+-----------+----------------+-----+------------+--------------+
7 rows in set (0.00 sec)
mysql> insert into DEPT_LOCATIONS values('1','Houston'),('4','Stafford'),('5','B
ellaire'),('5','Sugarland'),('5','Houston');
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> create table DEPT_LOCATIONS(DNUMBER integer(1) not null, DLOCATION varcha
r(20) not null);
Query OK, 0 rows affected (0.10 sec)
mysql> describe DEPT_LOCATIONS;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DNUMBER | int(1) | NO | | | |
| DLOCATION | varchar(20) | NO | | | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select*from DEPT_LOCATIONS;
+---------+-----------+
| DNUMBER | DLOCATION |
+---------+-----------+
| 1 | Houston |
| 4 | Stafford |
| 5 | Bellaire |
| 5 | Sugarland |
| 5 | Houston |
+---------+-----------+
5 rows in set (0.00 sec)
mysql> alter table DEPENDENT add foreign key(ESSN) references EMPLOYEE(SSN) on d
elete cascade on update cascade;
Query OK, 7 rows affected (0.27 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> alter table DEPT_LOCATIONS change DNUMBER DNUMBER integer(11) default 1 n
ot null;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table WORKS_ON add foreign key(ESSN) references EMPLOYEE(SSN) on de
lete cascade on update cascade;
Query OK, 16 rows affected (0.28 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> alter table DEPARTMENT add foreign key(MGRSSN) references EMPLOYEE(SSN) o
n delete cascade on update cascade;
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table WORKS_ON add foreign key(PNO) references PROJECT(PNUMBER) on
delete cascade on update cascade;
Query OK, 16 rows affected (0.22 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> mysql> alter table EMPLOYEE add foreign key(DNO) references DEPARTMENT(DNUMBER) on
delete cascade on update cascade;
Query OK, 16 rows affected (0.25 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> alter table PROJECT add foreign key(DNUM) references DEPARTMENT(DNUMBER)
on delete cascade on update cascade;
Query OK, 6 rows affected (0.32 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> describe employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| FNAME | varchar(30) | NO | | | |
| MINIT | char(1) | NO | | | |
| LNAME | varchar(30) | NO | | | |
| SSN | int(11) | NO | PRI | 9 | |
| BDATE | date | YES | | NULL | |
| ADDRESS | varchar(50) | NO | | | |
| SEX | char(1) | NO | | | |
| SALARY | int(11) | NO | | 5 | |
| SUPERSSN | int(11) | YES | | 9 | |
| DNO | int(11) | NO | MUL | 1 | |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0.01 sec)
mysql> describe department;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| DNAME | varchar(20) | NO | | | |
| DNUMBER | int(11) | NO | PRI | 1 | |
| MGRSSN | int(11) | NO | MUL | 9 | |
| MGRSTARTDATE | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> describe project;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| PNAME | varchar(20) | NO | | | |
| PNUMBER | int(3) | NO | PRI | | |
| PLOCATION | varchar(20) | NO | | | |
| DNUM | int(11) | NO | MUL | 1 | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe works_on;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| ESSN | int(11) | NO | MUL | 9 | |
| PNO | int(3) | NO | MUL | | |
| HOURS | varchar(5) | NO | | | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> describe dependent;
+----------------+-------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------------+-------------+------+-----+---------+-------
| ESSN | int(11) | NO | MUL | 9 |
| DEPENDENT_NAME | char(20) | NO | | |
| SEX | char(1) | NO | | |
| BDATE | date | YES | | NULL |
| RELATIONSHIP | varchar(10) | NO | | |
+----------------+-------------+------+-----+---------+-------
5 rows in set (0.01 sec)
mysql> describe dept_locations;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DNUMBER | int(11) | NO | | 1 | |
| DLOCATION | varchar(20) | NO | | | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select bdate, address from employee where fname='John' and minit='B'and l
name='Smith';
+------------+------------------------+
| bdate | address |
+------------+------------------------+
| 1965-01-09 | 731 Fondren,Houston,TX |
+------------+------------------------+
1 row in set (0.06 sec)
mysql> select fname, lname, address from employee, department where dname='resea
rch' and dnumber=dno;
+---------+---------+------------------------+
| fname | lname | address |
+---------+---------+------------------------+
| John | Smith | 731 Fondren,Houston,TX |
| Frankin | Wong | 638 Vose,Houston,TX |
| Joice | English | 5631 Rice, Houston, TX |
| Ramesh | Narayan | 975 Fire Oak,umble, TX |
+---------+---------+------------------------+
4 rows in set (0.00 sec)
mysql> select pnumber, dnum, lname, bdate, address from project, department, emp
loyee where dnum=dnumber and mgrssn=ssn and plocation='Stafford';
+---------+------+---------+------------+-------------------------+
| pnumber | dnum | lname | bdate | address |
+---------+------+---------+------------+-------------------------+
| 10 | 4 | Wallace | 1941-06-20 | 291 Berry, Bellaire, TX |
| 30 | 4 | Wallace | 1941-06-20 | 291 Berry, Bellaire, TX |
+---------+------+---------+------------+-------------------------+
2 rows in set (0.04 sec)
mysql> select e.fname, e.lname, s.fname, s.lname from employee as e, employee as
s where e.superssn=s.ssn;
+----------+---------+----------+---------+
| fname | lname | fname | lname |
+----------+---------+----------+---------+
| John | Smith | Frankin | Wong |
| Frankin | Wong | James | Borg |
| Joice | English | Frankin | Wong |
| Ramesh | Narayan | Frankin | Wong |
| Jennifer | Wallace | James | Borg |
| Alicia | Zelaya | Jennifer | Wallace |
+----------+---------+----------+---------+
6 rows in set (0.03 sec)
mysql> select ssn from employee;
+-----------+
| ssn |
+-----------+
| 123456789 |
| 333445555 |
| 453453453 |
| 666884444 |
| 888665555 |
| 987654321 |
| 987987987 |
| 999887777 |
+-----------+
8 rows in set (0.00 sec)
mysql> select ssn, dname from employee, department;
+-----------+----------------+
| ssn | dname |
+-----------+----------------+
| 123456789 | Headquarters |
| 123456789 | Administration |
| 123456789 | Research |
| 333445555 | Headquarters |
| 333445555 | Administration |
| 333445555 | Research |
| 453453453 | Headquarters |
| 453453453 | Administration |
| 453453453 | Research |
| 666884444 | Headquarters |
| 666884444 | Administration |
| 666884444 | Research |
| 888665555 | Headquarters |
| 888665555 | Administration |
| 888665555 | Research |
| 987654321 | Headquarters |
| 987654321 | Administration |
| 987654321 | Research |
| 987987987 | Headquarters |
| 987987987 | Administration |
| 987987987 | Research |
| 999887777 | Headquarters |
| 999887777 | Administration |
| 999887777 | Research |
+-----------+----------------+
24 rows in set (0.00 sec)
mysql> select * from employee where dno=5;
+---------+-------+---------+-----------+------------+------------------------+-
----+--------+-----------+-----+
| FNAME | MINIT | LNAME | SSN | BDATE | ADDRESS |
SEX | SALARY | SUPERSSN | DNO |
+---------+-------+---------+-----------+------------+------------------------+-
----+--------+-----------+-----+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren,Houston,TX |
M | 30000 | 333445555 | 5 |
| Frankin | T | Wong | 333445555 | 1955-12-08 | 638 Vose,Houston,TX |
M | 40000 | 888665555 | 5 |
| Joice | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX |
F | 25000 | 333445555 | 5 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak,umble, TX |
M | 38000 | 333445555 | 5 |
+---------+-------+---------+-----------+------------+------------------------+-
----+--------+-----------+-----+
4 rows in set (0.00 sec)
mysql> select * from employee, department where dname='research' and dno=dnumber
;
+---------+-------+---------+-----------+------------+------------------------+-
----+--------+-----------+-----+----------+---------+-----------+--------------+
| FNAME | MINIT | LNAME | SSN | BDATE | ADDRESS |
SEX | SALARY | SUPERSSN | DNO | DNAME | DNUMBER | MGRSSN | MGRSTARTDATE |
+---------+-------+---------+-----------+------------+------------------------+-
----+--------+-----------+-----+----------+---------+-----------+--------------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren,Houston,TX |
M | 30000 | 333445555 | 5 | Research | 5 | 333445555 | 1988-05-22 |
| Frankin | T | Wong | 333445555 | 1955-12-08 | 638 Vose,Houston,TX |
M | 40000 | 888665555 | 5 | Research | 5 | 333445555 | 1988-05-22 |
| Joice | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX |
F | 25000 | 333445555 | 5 | Research | 5 | 333445555 | 1988-05-22 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak,umble, TX |
M | 38000 | 333445555 | 5 | Research | 5 | 333445555 | 1988-05-22 |
+---------+-------+---------+-----------+------------+------------------------+-
----+--------+-----------+-----+----------+---------+-----------+--------------+
4 rows in set (0.00 sec)
mysql> select salary from employee;
+--------+
| salary |
+--------+
| 30000 |
| 40000 |
| 25000 |
| 38000 |
| 55000 |
| 43000 |
| 25000 |
| 25000 |
+--------+
8 rows in set (0.00 sec)
mysql> select distinct salary from employee;
+--------+
| salary |
+--------+
| 30000 |
| 40000 |
| 25000 |
| 38000 |
| 55000 |
| 43000 |
+--------+
6 rows in set (0.04 sec)
mysql> (select pname from project, department, employee where dnum=dnumber and m
grssn=ssn and lname='Smith') union (select pname from project, works_on, employe
e where pnumber=pno and essn=ssn and lname='Smith');
+----------+
| pname |
+----------+
| ProductX |
| ProdutY |
+----------+
2 rows in set (0.07 sec)
mysql> select fname, lname, address from employee where dno in (select dnumber f
rom department where dname='research');
+---------+---------+------------------------+
| fname | lname | address |
+---------+---------+------------------------+
| John | Smith | 731 Fondren,Houston,TX |
| Frankin | Wong | 638 Vose,Houston,TX |
| Joice | English | 5631 Rice, Houston, TX |
| Ramesh | Narayan | 975 Fire Oak,umble, TX |
+---------+---------+------------------------+
4 rows in set (0.07 sec)
mysql> select fname, lname from employee where not exists (select * from depende
nt where ssn=essn);
+--------+---------+
| fname | lname |
+--------+---------+
| Joice | English |
| Ramesh | Narayan |
| James | Borg |
| Ahmad | Jabbar |
| Alicia | Zelaya |
+--------+---------+
5 rows in set (0.00 sec)
mysql> select distinct essn from works_on where pno in (1,2,3);
+-----------+
| essn |
+-----------+
| 123456789 |
| 333445555 |
| 453453453 |
| 666884444 |
+-----------+
4 rows in set (0.00 sec)
mysql> select fname, lname from employee where superssn is null;
Empty set (0.00 sec)
mysql> select e.fname, e.lname, s.fname, s.lname from (employee e left outer jo
n employee s on e.superssn=s.ssn);
+----------+---------+----------+---------+
| fname | lname | fname | lname |
+----------+---------+----------+---------+
| John | Smith | Frankin | Wong |
| Frankin | Wong | James | Borg |
| Joice | English | Frankin | Wong |
| Ramesh | Narayan | Frankin | Wong |
| James | Borg | NULL | NULL |
| Jennifer | Wallace | James | Borg |
| Ahmad | Jabbar | NULL | NULL |
| Alicia | Zelaya | Jennifer | Wallace |
+----------+---------+----------+---------+
8 rows in set (0.00 sec)
mysql> select fname, lname, address from employee, department where dname='rese
rch' and dnumber=dno;
+---------+---------+------------------------+
| fname | lname | address |
+---------+---------+------------------------+
| John | Smith | 731 Fondren,Houston,TX |
| Frankin | Wong | 638 Vose,Houston,TX |
| Joice | English | 5631 Rice, Houston, TX |
| Ramesh | Narayan | 975 Fire Oak,umble, TX |
+---------+---------+------------------------+
4 rows in set (0.00 sec)
mysql> select max(salary), min(salary), avg(salary) from employee;
+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+-------------+
| 55000 | 25000 | 35125.0000 |
+-------------+-------------+-------------+
1 row in set (0.05 sec)
mysql> select max(salary), min(salary), avg(salary) from employee, department w
ere dno=dnumber and dname='research';
+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+-------------+
| 40000 | 25000 | 33250.0000 |
+-------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from employee, department where dno=dnumber and dname='r
search';
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select dno, count(*), avg(salary) from employee group by dno;
+-----+----------+-------------+
| dno | count(*) | avg(salary) |
+-----+----------+-------------+
| 1 | 1 | 55000.0000 |
| 4 | 3 | 31000.0000 |
| 5 | 4 | 33250.0000 |
+-----+----------+-------------+
3 rows in set (0.04 sec)
mysql> select pnumber, pname, count(*) from project, works_on where pnumber=pn
group by pnumber, pname;
+---------+-----------------+----------+
| pnumber | pname | count(*) |
+---------+-----------------+----------+
| 1 | ProductX | 2 |
| 2 | ProdutY | 3 |
| 3 | ProductZ | 2 |
| 10 | Computerization | 3 |
| 20 | Reorganization | 3 |
| 30 | Newbenefits | 3 |
+---------+-----------------+----------+
6 rows in set (0.00 sec)
mysql> select pnumber, pname, count(*) from project, works_on where pnumber=pn
group by pnumber, pname having count(*)>2;
+---------+-----------------+----------+
| pnumber | pname | count(*) |
+---------+-----------------+----------+
| 2 | ProdutY | 3 |
| 10 | Computerization | 3 |
| 20 | Reorganization | 3 |
| 30 | Newbenefits | 3 |
+---------+-----------------+----------+
4 rows in set (0.00 sec)
mysql> select fname, lname from employee where address like '%Houston, TX%';
+-------+---------+
| fname | lname |
+-------+---------+
| Joice | English |
| Ahmad | Jabbar |
+-------+---------+
2 rows in set (0.04 sec)
mysql> select fname, lname from employee where bdate like '_______5_';
Empty set (0.00 sec)
mysql> select fname, lname, 1.1*salary from employee, works_on, project where s
n=essn and pno=pnumber and pname='ProductX';
+-------+---------+------------+
| fname | lname | 1.1*salary |
+-------+---------+------------+
| John | Smith | 33000.0 |
| Joice | English | 27500.0 |
+-------+---------+------------+
2 rows in set (0.06 sec)
mysql> select dname, lname, fname, pname from department, employee, works_on, p
ojec where dnumber=dno and ssn=essn and pno=pnumber order by dname, lname;
ERROR 1146 (42S02): Table 'polycompany.projec' doesn't exist
mysql> select dname, lname, fname, pname from department, employee, works_on, p
oject where dnumber=dno and ssn=essn and pno=pnumber order by dname, lname;
+----------------+---------+----------+-----------------+
| dname | lname | fname | pname |
+----------------+---------+----------+-----------------+
| Administration | Jabbar | Ahmad | Computerization |
| Administration | Jabbar | Ahmad | Newbenefits |
| Administration | Wallace | Jennifer | Reorganization |
| Administration | Wallace | Jennifer | Newbenefits |
| Administration | Zelaya | Alicia | Newbenefits |
| Administration | Zelaya | Alicia | Computerization |
| Headquarters | Borg | James | Reorganization |
| Research | English | Joice | ProdutY |
| Research | English | Joice | ProductX |
| Research | Narayan | Ramesh | ProductZ |
| Research | Smith | John | ProductX |
| Research | Smith | John | ProdutY |
| Research | Wong | Frankin | ProductZ |
| Research | Wong | Frankin | Computerization |
| Research | Wong | Frankin | ProdutY |
| Research | Wong | Frankin | Reorganization |
+----------------+---------+----------+-----------------+
16 rows in set (0.00 sec)
mysql> create table depts_info (dept_name varchar(10),no_of_emps integer,total_
al integer);
Query OK, 0 rows affected (0.08 sec)
mysql> describe DEPT_INFO;
ERROR 1146 (42S02): Table 'polycompany.dept_info' doesn't exist
mysql> describe DEPTS_INFO;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| dept_name | varchar(10) | YES | | NULL | |
| no_of_emps | int(11) | YES | | NULL | |
| total_sal | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> delete from employee where lname='Brown';
Query OK, 0 rows affected (0.03 sec)
mysql> select*from employee;
+----------+-------+---------+-----------+------------+------------------------
+-----+--------+-----------+-----+
| FNAME | MINIT | LNAME | SSN | BDATE | ADDRESS
| SEX | SALARY | SUPERSSN | DNO |
+----------+-------+---------+-----------+------------+------------------------
+-----+--------+-----------+-----+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren,Houston,TX
| M | 30000 | 333445555 | 5 |
| Frankin | T | Wong | 333445555 | 1955-12-08 | 638 Vose,Houston,TX
| M | 40000 | 888665555 | 5 |
| Joice | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000 | 333445555 | 5 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak,umble, TX
| M | 38000 | 333445555 | 5 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone,Huoston, TX
| M | 55000 | 0 | 1 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000 | 888665555 | 4 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000 | 897654321 | 4 |
| Alicia | J | Zelaya | 999887777 | 1968-07-19 | 3321 Castle,Spring,TX
| F | 25000 | 987654321 | 4 |
+----------+-------+---------+-----------+------------+------------------------
+-----+--------+-----------+-----+
8 rows in set (0.00 sec)
mysql> update project set plocation='Bellaire', dnum=5 where pnumber=10;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from project;
+-----------------+---------+-----------+------+
| PNAME | PNUMBER | PLOCATION | DNUM |
+-----------------+---------+-----------+------+
| ProductX | 1 | Bellaire | 5 |
| ProdutY | 2 | Sugarland | 5 |
| ProductZ | 3 | Houston | 5 |
| Computerization | 10 | Bellaire | 5 |
| Reorganization | 20 | Houston | 1 |
| Newbenefits | 30 | Stafford | 4 |
+-----------------+---------+-----------+------+
6 rows in set (0.00 sec)
mysql> update employee set salary=salary*1.1 where dno in (select dnumber from
epartment where dname='research');
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select *from employee;
+----------+-------+---------+-----------+------------+------------------------
+-----+--------+-----------+-----+
| FNAME | MINIT | LNAME | SSN | BDATE | ADDRESS
| SEX | SALARY | SUPERSSN | DNO |
+----------+-------+---------+-----------+------------+------------------------
+-----+--------+-----------+-----+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren,Houston,TX
| M | 33000 | 333445555 | 5 |
| Frankin | T | Wong | 333445555 | 1955-12-08 | 638 Vose,Houston,TX
| M | 44000 | 888665555 | 5 |
| Joice | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 27500 | 333445555 | 5 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak,umble, TX
| M | 41800 | 333445555 | 5 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone,Huoston, TX
| M | 55000 | 0 | 1 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000 | 888665555 | 4 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000 | 897654321 | 4 |
| Alicia | J | Zelaya | 999887777 | 1968-07-19 | 3321 Castle,Spring,TX
| F | 25000 | 987654321 | 4 |
+----------+-------+---------+-----------+------------+------------------------
+-----+--------+-----------+-----+
8 rows in set (0.00 sec)
mysql> quit;
Tidak ada komentar:
Posting Komentar