1. CREATE DATABASE namadtbase;
2. CREATE TABLE namatable
EMPLOYEE
INSERT INTO EMPLOYEE
VALUES ('...','..','..')..
INSERT INTO EMPLOYEE(Fname,Lname,SSn) Valuer('...','..','..')
UPDATE TABLE EMPLOYEE
SET Dno=5
WHERE SSn= '....';
create database x;
use x;
create table employee
mysql> describe dept_locations;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Dnumber | int(11) | NO | PRI | NULL | |
| Dlocation | varchar(20) | NO | PRI | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> alter table project MODIFY Dnum INT,ADD FOREIGN KEY(Dnum) REFERENCES depa
rtment(Dnumber);
Query OK, 0 rows affected (3.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe project;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Pname | varchar(16) | NO | | NULL | |
| Pnumber | int(11) | NO | PRI | NULL | |
| Plocation | varchar(20) | YES | | NULL | |
| Dnum | int(11) | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> alter table works_on MODIFY Pno INT NOT NULL,ADD FOREIGN KEY(Pno) REFEREN
CES project(Pnumber);
Query OK, 0 rows affected (3.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe works_on;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Essn | varchar(9) | NO | PRI | NULL | |
| Pno | int(11) | NO | PRI | NULL | |
| Hours | time | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> alter table works_on MODIFY Essn VARCHAR(9) NOT NULL,ADD FOREIGN KEY(Essn
) REFERENCES employee(SSn);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe works_on;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Essn | varchar(9) | NO | PRI | NULL | |
| Pno | int(11) | NO | PRI | NULL | |
| Hours | time | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table dependent MODIFY Essn VARCHAR(9) NOT NULL,ADD FOREIGN KEY(Ess
n) REFERENCES employee(SSn);
Query OK, 0 rows affected (3.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desribe dependent;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'desri
be dependent' at line 1
mysql> describe dependent;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| Essn | varchar(9) | NO | PRI | NULL | |
| Dependent_name | varchar(15) | NO | PRI | NULL | |
| Sex | char(1) | YES | | NULL | |
| Bdate | date | YES | | NULL | |
| Relationship | varchar(8) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql> INSERT INTO EMPLOYEE VALUES('John','B','Smith','123456789','09-01-1965','
731 Fondren, Houston, TX','M','30000','333445555','5'),('Franklin','T','Wong','3
33445555','08-12-1955','638 Voss, Houston, TX','M','40000','888665555','5'),('Al
icia','J','Zelaya','999887777','19-01-1968','3321 Castle, Spring, TX','F','25000
','987654321','4'),('Jennifer','S','Wallace','987654321','20-06-1941','291 Berry
, Bellaire, TX','F','43000','888665555','4'),('Ramesh','K','Narayan','666884444'
,'15-09-1962','975 Fire Oak, Humble, TX','M','38000','333445555','5'),('Joyce','
A','English','453453453','31-07-1972','5631 Rice, Houston, TX','F','25000','3334
45555','5'),('Ahmad','V','Jabbar','987987987','29-03-1969','980 Dallas, Houston,
TX','M','25000','987654321','4'),('James','E','Borg','888665555','10-11-1937','
450 Stone, Houston, TX','M','55000','NULL','1');
ERROR 1292 (22007): Incorrect date value: '09-01-1965' for column 'Bdate' at row
1
mysql> INSERT INTO EMPLOYEE VALUES('John','B','Smith','123456789','1965-01-09','
731 Fondren, Houston, TX','M','30000','333445555','5'),('Franklin','T','Wong','3
33445555','1955-12-08','638 Voss, Houston, TX','M','40000','888665555','5'),('Al
icia','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring, TX','F','25000
','987654321','4'),('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry
, Bellaire, TX','F','43000','888665555','4'),('Ramesh','K','Narayan','666884444'
,'1962-09-15','975 Fire Oak, Humble, TX','M','38000','333445555','5'),('Joyce','
A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F','25000','3334
45555','5'),('Ahmad','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston,
TX','M','25000','987654321','4'),('James','E','Borg','888665555','1937-11-10','
450 Stone, Houston, TX','M','55000','NULL','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`erna`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Super_SSn`)
REFERENCES `employee` (`SSn`))
mysql> INSERT INTO EMPLOYEE VALUES('John','B','Smith','123456789','1965-01-09','
731 Fondren, Houston, TX','M','30000','333445555','5');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`erna`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Super_SSn`)
REFERENCES `employee` (`SSn`))
mysql> INSERT INTO EMPLOYEE VALUES('John','B','Smith','123456789','1965-01-09','
731 Fondren, Houston, TX','M','30000'),('Franklin','T','Wong','333445555','1955-
12-08','638 Voss, Houston, TX','M','40000'),('Alicia','J','Zelaya','999887777','
1968-01-19','3321 Castle, Spring, TX','F','25000'),('Jennifer','S','Wallace','98
7654321','1941-06-20','291 Berry, Bellaire, TX','F','43000'),('Ramesh','K','Nara
yan','666884444','1962-09-15','975 Fire Oak, Humble, TX','M','38000'),('Joyce','
A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F','25000'),('Ah
mad','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M','25000'
),('James','E','Borg','888665555','1937-11-10','450 Stone, Houston, TX','M','550
00');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO EMPLOYEE (Fname,Minit,Lname,SSn,Bdate,Adress,Sex,Salary) VALU
ES('John','B','Smith','123456789','1965-01-09','731 Fondren, Houston, TX','M','3
0000'),('Franklin','T','Wong','333445555','1955-12-08','638 Voss, Houston, TX','
M','40000'),('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring
, TX','F','25000'),('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry
, Bellaire, TX','F','43000'),('Ramesh','K','Narayan','666884444','1962-09-15','9
75 Fire Oak, Humble, TX','M','38000'),('Joyce','A','English','453453453','1972-0
7-31','5631 Rice, Houston, TX','F','25000'),('Ahmad','V','Jabbar','987987987','1
969-03-29','980 Dallas, Houston, TX','M','25000'),('James','E','Borg','888665555
','1937-11-10','450 Stone, Houston, TX','M','55000');
ERROR 1054 (42S22): Unknown column 'Adress' in 'field list'
mysql> INSERT INTO EMPLOYEE (Fname,Minit,Lname,SSn,Bdate,Address,Sex,Salary) VAL
UES('John','B','Smith','123456789','1965-01-09','731 Fondren, Houston, TX','M','
30000'),('Franklin','T','Wong','333445555','1955-12-08','638 Voss, Houston, TX',
'M','40000'),('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle, Sprin
g, TX','F','25000'),('Jennifer','S','Wallace','987654321','1941-06-20','291 Berr
y, Bellaire, TX','F','43000'),('Ramesh','K','Narayan','666884444','1962-09-15','
975 Fire Oak, Humble, TX','M','38000'),('Joyce','A','English','453453453','1972-
07-31','5631 Rice, Houston, TX','F','25000'),('Ahmad','V','Jabbar','987987987','
1969-03-29','980 Dallas, Houston, TX','M','25000'),('James','E','Borg','88866555
5','1937-11-10','450 Stone, Houston, TX','M','55000');
Query OK, 8 rows affected (3.46 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select*from employee;
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| Fname | Minit | Lname | SSn | Bdate | Address
| Sex | Salary | Super_SSn | Dno |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX
| M | 30000.00 | NULL | NULL |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX
| M | 40000.00 | NULL | NULL |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000.00 | NULL | NULL |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX
| M | 38000.00 | NULL | NULL |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX
| M | 55000.00 | NULL | NULL |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000.00 | NULL | NULL |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000.00 | NULL | NULL |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX
| F | 25000.00 | NULL | NULL |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
8 rows in set (0.00 sec)
mysql> UPDATE employee
-> SET Super_SSn='333445555'
-> WHERE SSn='123456789';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from employee;
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| Fname | Minit | Lname | SSn | Bdate | Address
| Sex | Salary | Super_SSn | Dno |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX
| M | 30000.00 | 333445555 | NULL |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX
| M | 40000.00 | NULL | NULL |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000.00 | NULL | NULL |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX
| M | 38000.00 | NULL | NULL |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX
| M | 55000.00 | NULL | NULL |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000.00 | NULL | NULL |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000.00 | NULL | NULL |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX
| F | 25000.00 | NULL | NULL |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
8 rows in set (0.00 sec)
mysql> UPDATE employee
-> SET Super_SSn='888665555'
-> WHERE SSn='333445555';
Query OK, 1 row affected (3.38 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE employee
-> SET Super_SSn='987654321'
-> WHERE SSn='999887777';
Query OK, 1 row affected (3.43 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE employee
-> SET Super_SSn='888665555'
-> WHERE SSn='987654321';
Query OK, 1 row affected (3.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE employee
-> SET Super_SSn='333445555'
-> WHERE SSn='666884444';
Query OK, 1 row affected (3.42 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE employee
-> SET Super_SSn='333445555'
-> WHERE SSn='453453453';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE employee
-> SET Super_SSn='987654321'
-> WHERE SSn='987987987';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE employee
-> SET Super_SSn='NULL'
-> WHERE SSn='888665555';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`erna`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Super_SSn`)
REFERENCES `employee` (`SSn`))
mysql> UPDATE employee
-> SET Super_SSn=' '
-> WHERE SSn='888665555';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`erna`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Super_SSn`)
REFERENCES `employee` (`SSn`))
mysql> UPDATE employee
-> SET Super_SSn=
-> WHERE SSn='888665555';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHERE
SSn='888665555'' at line 3
mysql> select*from employee;
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| Fname | Minit | Lname | SSn | Bdate | Address
| Sex | Salary | Super_SSn | Dno |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX
| M | 30000.00 | 333445555 | NULL |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX
| M | 40000.00 | 888665555 | NULL |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000.00 | 333445555 | NULL |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX
| M | 38000.00 | 333445555 | NULL |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX
| M | 55000.00 | NULL | NULL |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000.00 | 888665555 | NULL |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000.00 | 987654321 | NULL |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX
| F | 25000.00 | 987654321 | NULL |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
8 rows in set (0.00 sec)
mysql> UPDATE employee
-> SET Dno='5'
-> WHERE SSn='123456789';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`erna`.`employee`, CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`Dno`) REFERE
NCES `department` (`Dnumber`))
mysql> UPDATE employee
-> SET Dno=5
-> WHERE SSn='123456789';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`erna`.`employee`, CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`Dno`) REFERE
NCES `department` (`Dnumber`))
mysql>
mysql> select*FROM EMPLOYEE;
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| Fname | Minit | Lname | SSn | Bdate | Address
| Sex | Salary | Super_SSn | Dno |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX
| M | 30000.00 | 333445555 | NULL |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX
| M | 40000.00 | 888665555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000.00 | 333445555 | 5 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX
| M | 38000.00 | 333445555 | 5 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX
| M | 55000.00 | NULL | 1 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000.00 | 888665555 | 4 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000.00 | 987654321 | 4 |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX
| F | 25000.00 | 987654321 | 4 |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
8 rows in set (0.00 sec)
mysql> UPDATE employee
-> WHERE SSN='333445555';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHERE
SSN='333445555'' at line 2
mysql>
mysql> UPDATE employee
-> set DNO = 5
-> WHERE SSN='333445555';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select*FROM EMPLOYEE;
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| Fname | Minit | Lname | SSn | Bdate | Address
| Sex | Salary | Super_SSn | Dno |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX
| M | 30000.00 | 333445555 | NULL |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX
| M | 40000.00 | 888665555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000.00 | 333445555 | 5 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX
| M | 38000.00 | 333445555 | 5 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX
| M | 55000.00 | NULL | 1 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000.00 | 888665555 | 4 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000.00 | 987654321 | 4 |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX
| F | 25000.00 | 987654321 | 4 |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
8 rows in set (0.00 sec)
mysql> UPDATE employee
-> set DNO = 5
-> WHERE SSN='123456789';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*FROM EMPLOYEE;
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| Fname | Minit | Lname | SSn | Bdate | Address
| Sex | Salary | Super_SSn | Dno |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX
| M | 30000.00 | 333445555 | 5 |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX
| M | 40000.00 | 888665555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX
| F | 25000.00 | 333445555 | 5 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX
| M | 38000.00 | 333445555 | 5 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX
| M | 55000.00 | NULL | 1 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX
| F | 43000.00 | 888665555 | 4 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX
| M | 25000.00 | 987654321 | 4 |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX
| F | 25000.00 | 987654321 | 4 |
+----------+-------+---------+-----------+------------+-------------------------
-+------+----------+-----------+------+
8 rows in set (0.00 sec)
mysql> INSERT INTO DEPT_LOCATIONS(Dlocation) VALUES('Houston','Stafford','Bellai
re','Sugarland','Houston');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> describe dept_location;
ERROR 1146 (42S02): Table 'erna.dept_location' doesn't exist
mysql> describe dept_locations;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Dnumber | int(11) | NO | PRI | NULL | |
| Dlocation | varchar(20) | NO | PRI | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> INSERT INTO DEPT_LOCATIONS(Dnumber,Dlocation) VALUES('1','Houston'),('4',
'Stafford'),('5','Bellaire'),('5','Sugarland'),('5','Houston');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from dept_locations
-> ;
+---------+-----------+
| Dnumber | Dlocation |
+---------+-----------+
| 1 | Houston |
| 4 | Stafford |
| 5 | Bellaire |
| 5 | Houston |
| 5 | Sugarland |
+---------+-----------+
5 rows in set (0.04 sec)
mysql> describe project;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Pname | varchar(16) | NO | | NULL | |
| Pnumber | int(11) | NO | PRI | NULL | |
| Plocation | varchar(20) | YES | | NULL | |
| Dnum | int(11) | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (1.43 sec)
mysql> INSERT INTO PROJECT(Pname,Pnumber,Plocation,dnum) VALUES('ProductX','1','
Bellaire','5'),('ProductY','2','Sugarland','5'),('ProductZ','3','Houston','5'),(
'Computerization','10','stafford','4'),('Reorganization','20','Houston','1'),('N
ewbenefits','30','Stafford','4');
Query OK, 6 rows affected (3.40 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select*from project;
+-----------------+---------+-----------+------+
| Pname | Pnumber | Plocation | Dnum |
+-----------------+---------+-----------+------+
| ProductX | 1 | Bellaire | 5 |
| ProductY | 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> describe works_on;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Essn | varchar(9) | NO | PRI | NULL | |
| Pno | int(11) | NO | PRI | NULL | |
| Hours | time | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.93 sec)
mysql> INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789','1','32.5'),('123
456789','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','5.0'),('987654321','30','20
.0'),('987654321','20','15.0'),('888665555','20');
ERROR 1136 (21S01): Column count doesn't match value count at row 16
mysql> INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789','1','32.5'),('123
456789','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','5.0'),('987654321','30','20
.0'),('987654321','20','15.0'),('888665555','20',' ');
ERROR 1292 (22007): Incorrect time value: ' ' for column 'Hours' at row 16
mysql> INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789','1','32.5'),('123
456789','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','5.0'),('987654321','30','20
.0'),('987654321','20','15.0'),('888665555','20','');
ERROR 1292 (22007): Incorrect time value: '' for column 'Hours' at row 16
mysql> INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789','1','32.5'),('123
456789','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','5.0'),('987654321','30','20
.0'),('987654321','20','15.0');
Query OK, 15 rows affected (0.36 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> UPDATE WORKS_ON
-> set Essn='888665555';
ERROR 1062 (23000): Duplicate entry '888665555-2' for key 'PRIMARY'
mysql> UPDATE WORKS_ON
-> set Essn='888665555'
-> where SSn='888665555';
ERROR 1054 (42S22): Unknown column 'SSn' in 'where clause'
mysql> select*fromworks_on;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'fromw
orks_on' at line 1
mysql> select*from works_on;
+-----------+-----+----------+
| Essn | Pno | Hours |
+-----------+-----+----------+
| 123456789 | 1 | 00:00:32 |
| 123456789 | 2 | 00:00:07 |
| 333445555 | 2 | 00:00:10 |
| 333445555 | 3 | 00:00:10 |
| 333445555 | 10 | 00:00:10 |
| 333445555 | 20 | 00:00:10 |
| 453453453 | 1 | 00:00:20 |
| 453453453 | 2 | 00:00:20 |
| 666884444 | 3 | 00:00:40 |
| 987654321 | 20 | 00:00:15 |
| 987654321 | 30 | 00:00:20 |
| 987987987 | 10 | 00:00:35 |
| 987987987 | 30 | 00:00:05 |
| 999887777 | 10 | 00:00:10 |
| 999887777 | 30 | 00:00:30 |
+-----------+-----+----------+
15 rows in set (0.00 sec)
mysql> INSERT INTO WORKS_ON(Essn,Pno,Hours) VALUES('123456789','1','32.5'),('123
456789','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','5.0'),('987654321','30','20
.0'),('987654321','20','15.0'),('888665555','20','00.0');
ERROR 1062 (23000): Duplicate entry '123456789-1' for key 'PRIMARY'
mysql> select*from works_on;
+-----------+-----+----------+
| Essn | Pno | Hours |
+-----------+-----+----------+
| 123456789 | 1 | 00:00:32 |
| 123456789 | 2 | 00:00:07 |
| 333445555 | 2 | 00:00:10 |
| 333445555 | 3 | 00:00:10 |
| 333445555 | 10 | 00:00:10 |
| 333445555 | 20 | 00:00:10 |
| 453453453 | 1 | 00:00:20 |
| 453453453 | 2 | 00:00:20 |
| 666884444 | 3 | 00:00:40 |
| 987654321 | 20 | 00:00:15 |
| 987654321 | 30 | 00:00:20 |
| 987987987 | 10 | 00:00:35 |
| 987987987 | 30 | 00:00:05 |
| 999887777 | 10 | 00:00:10 |
| 999887777 | 30 | 00:00:30 |
+-----------+-----+----------+
15 rows in set (0.00 sec)
mysql> describe dependent;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| Essn | varchar(9) | NO | PRI | NULL | |
| Dependent_name | varchar(15) | NO | PRI | NULL | |
| Sex | char(1) | YES | | NULL | |
| Bdate | date | YES | | NULL | |
| Relationship | varchar(8) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (3.54 sec)
mysql> INSERT INTO DEPENDENT(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES(
'333445555','Alice','F','1986-04-05','Daughter'),('333445555','Theodore','M','19
83-10-25','Son'),('333445555','Joy','F','1958-05-03','Spouse'),('987654321','Abn
er','M','1942-02-28','Spouse'),('123456789','Michael','M','1988-01-04','Son'),('
123456789','Alice','F','1988-12-30','Daughter'),('123456789','Elizabeth','F','19
67-05-05','Spouse');
Query OK, 7 rows affected (3.32 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select*from dependent;
+-----------+----------------+------+------------+--------------+
| Essn | Dependent_name | Sex | Bdate | Relationship |
+-----------+----------------+------+------------+--------------+
| 123456789 | Alice | F | 1988-12-30 | Daughter |
| 123456789 | Elizabeth | F | 1967-05-05 | Spouse |
| 123456789 | Michael | M | 1988-01-04 | Son |
| 333445555 | Alice | F | 1986-04-05 | Daughter |
| 333445555 | Joy | F | 1958-05-03 | Spouse |
| 333445555 | Theodore | M | 1983-10-25 | Son |
| 987654321 | Abner | M | 1942-02-28 | Spouse |
+-----------+----------------+------+------------+--------------+
7 rows in set (0.00 sec)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT SSn,Lname,Dname,Pname from employee,department,project where Dno=D
number and Dnum=Dnumber;
ERROR 1046 (3D000): No database selected
mysql> use erna;
Database changed
mysql> SELECT SSn,Lname,Dname,Pname from employee,department,project where Dno=D
number and Dnum=Dnumber;
+-----------+---------+----------------+-----------------+
| SSn | Lname | Dname | Pname |
+-----------+---------+----------------+-----------------+
| 888665555 | Borg | Headquarters | Reorganization |
| 987654321 | Wallace | Administration | Computerization |
| 987987987 | Jabbar | Administration | Computerization |
| 999887777 | Zelaya | Administration | Computerization |
| 987654321 | Wallace | Administration | Newbenefits |
| 987987987 | Jabbar | Administration | Newbenefits |
| 999887777 | Zelaya | Administration | Newbenefits |
| 123456789 | Smith | Research | ProductX |
| 333445555 | Wong | Research | ProductX |
| 453453453 | English | Research | ProductX |
| 666884444 | Narayan | Research | ProductX |
| 123456789 | Smith | Research | ProductY |
| 333445555 | Wong | Research | ProductY |
| 453453453 | English | Research | ProductY |
| 666884444 | Narayan | Research | ProductY |
| 123456789 | Smith | Research | ProductZ |
| 333445555 | Wong | Research | ProductZ |
| 453453453 | English | Research | ProductZ |
| 666884444 | Narayan | Research | ProductZ |
+-----------+---------+----------------+-----------------+
19 rows in set (0.15 sec)
mysql> SELECT SSn,Lname,Dname,Dnumber,Dlocation from employee,department,dept_lo
cations where Dno=Dnumber and Dnumber=Dnumber;
ERROR 1052 (23000): Column 'Dnumber' in field list is ambiguous
mysql> SELECT SSn,Lname,Dname,L.Dnumber,Dlocation from employee,department D,dep
t_locations L where Dno=Dnumber and D.Dnumber=L.Dnumber;
ERROR 1052 (23000): Column 'Dnumber' in where clause is ambiguous
mysql> SELECT SSn,Lname,Dname,L.Dnumber,Dlocation from employee,department D,dep
t_locations L where D.Dnumber=Dno and D.Dnumber=L.Dnumber;
+-----------+---------+----------------+---------+-----------+
| SSn | Lname | Dname | Dnumber | Dlocation |
+-----------+---------+----------------+---------+-----------+
| 888665555 | Borg | Headquarters | 1 | Houston |
| 987654321 | Wallace | Administration | 4 | Stafford |
| 987987987 | Jabbar | Administration | 4 | Stafford |
| 999887777 | Zelaya | Administration | 4 | Stafford |
| 123456789 | Smith | Research | 5 | Bellaire |
| 333445555 | Wong | Research | 5 | Bellaire |
| 453453453 | English | Research | 5 | Bellaire |
| 666884444 | Narayan | Research | 5 | Bellaire |
| 123456789 | Smith | Research | 5 | Houston |
| 333445555 | Wong | Research | 5 | Houston |
| 453453453 | English | Research | 5 | Houston |
| 666884444 | Narayan | Research | 5 | Houston |
| 123456789 | Smith | Research | 5 | Sugarland |
| 333445555 | Wong | Research | 5 | Sugarland |
| 453453453 | English | Research | 5 | Sugarland |
| 666884444 | Narayan | Research | 5 | Sugarland |
+-----------+---------+----------------+---------+-----------+
16 rows in set (0.00 sec)
mysql> select max(salary),min(salary),avg(salary) from employee;
+-------------+-------------+--------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+--------------+
| 55000.00 | 25000.00 | 35125.000000 |
+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> select salary fromemployee;
ERROR 1054 (42S22): Unknown column 'salary' in 'field list'
mysql> select salary from employee;
+----------+
| salary |
+----------+
| 30000.00 |
| 40000.00 |
| 25000.00 |
| 38000.00 |
| 55000.00 |
| 43000.00 |
| 25000.00 |
| 25000.00 |
+----------+
8 rows in set (0.00 sec)
mysql> select distinct salary from employee;
+----------+
| salary |
+----------+
| 30000.00 |
| 40000.00 |
| 25000.00 |
| 38000.00 |
| 55000.00 |
| 43000.00 |
+----------+
6 rows in set (0.00 sec)
mysql> select Dno, count(*), avg(salary) from employee group by Dno;
+------+----------+--------------+
| Dno | count(*) | avg(salary) |
+------+----------+--------------+
| 1 | 1 | 55000.000000 |
| 4 | 3 | 31000.000000 |
| 5 | 4 | 33250.000000 |
+------+----------+--------------+
3 rows in set (0.00 sec)
mysql> select Lname,Pname from employee,project where Pname=ProductX and Dno=Dnu
m;
ERROR 1054 (42S22): Unknown column 'ProductX' in 'where clause'
mysql> select Lname,Pname from employee,project where Dno=Dnum;
+---------+-----------------+
| Lname | Pname |
+---------+-----------------+
| Smith | ProductX |
| Wong | ProductX |
| English | ProductX |
| Narayan | ProductX |
| Smith | ProductY |
| Wong | ProductY |
| English | ProductY |
| Narayan | ProductY |
| Smith | ProductZ |
| Wong | ProductZ |
| English | ProductZ |
| Narayan | ProductZ |
| Wallace | Computerization |
| Jabbar | Computerization |
| Zelaya | Computerization |
| Borg | Reorganization |
| Wallace | Newbenefits |
| Jabbar | Newbenefits |
| Zelaya | Newbenefits |
+---------+-----------------+
19 rows in set (0.00 sec)
mysql> select Lname,Pname from employee,project,department where Dno=Dnumber and
Dnum=Dnumber and Dno<2;
+-------+----------------+
| Lname | Pname |
+-------+----------------+
| Borg | Reorganization |
+-------+----------------+
1 row in set (0.00 sec)
mysql> select Lname,Pname from employee,project,department where Dno=Dnumber and
Dnum=Dnumber and Dno<5;
+---------+-----------------+
| Lname | Pname |
+---------+-----------------+
| Borg | Reorganization |
| Wallace | Computerization |
| Wallace | Newbenefits |
| Jabbar | Computerization |
| Jabbar | Newbenefits |
| Zelaya | Computerization |
| Zelaya | Newbenefits |
+---------+-----------------+
7 rows in set (0.00 sec)
mysql> select Lname,Pname from employee,project,department where Dno=Dnumber and
Dnum=Dnumber and Dno<1;
Empty set (0.00 sec)
mysql> select Lname,Pname from employee,project,department where Pnumber=1 and S
Sn=mgr_ssn and Dnumber=dnum;
+-------+----------+
| Lname | Pname |
+-------+----------+
| Wong | ProductX |
+-------+----------+
1 row in set (0.00 sec)
mysql> select Lname,Dependent_name,Relationship from employee D,dependent L wher
e D.Sex=F and SSn=Essn;
ERROR 1054 (42S22): Unknown column 'F' in 'where clause'
mysql> select Lname,Dependent_name,Relationship from employee D,dependent L wher
e D.Sex=L.Sex and SSn=Essn;
+-------+----------------+--------------+
| Lname | Dependent_name | Relationship |
+-------+----------------+--------------+
| Smith | Michael | Son |
| Wong | Theodore | Son |
+-------+----------------+--------------+
2 rows in set (0.00 sec)
mysql> select Lname,Dependent_name from employee,dependent where relationship='d
aughter' and SSn=Essn;
+-------+----------------+
| Lname | Dependent_name |
+-------+----------------+
| Smith | Alice |
| Wong | Alice |
+-------+----------------+
2 rows in set (0.00 sec)
mysql> select Lname,Dlocation,Plocation from employee,dept_locations L,project,d
epartment D where L.Dnumber=4 and SSn=mgr_ssn and D.Dnumber=Dnum and Dno=D.Dnumb
er and L.Dnumber=D.Dnumber;
+---------+-----------+-----------+
| Lname | Dlocation | Plocation |
+---------+-----------+-----------+
| Wallace | Stafford | stafford |
| Wallace | Stafford | Stafford |
+---------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> select distinct Lname from employee;
+---------+
| Lname |
+---------+
| Smith |
| Wong |
| English |
| Narayan |
| Borg |
| Wallace |
| Jabbar |
| Zelaya |
+---------+
8 rows in set (0.00 sec)
0 komentar:
Posting Komentar