mysql> CREATE DATABASE COMPANY;

Query OK, 1 row affected (0.00 sec)

mysql> USE COMPANY;

Database changed
mysql> CREATE TABLE DEPARTMENT(DNO INT,DNAME VARCHAR(10), MGRSTARTDATE DATE,PRIMARY
KEY(DNO));
Query OK, 0 rows affected (0.01 sec)

mysql> DESC DEPARTMENT;

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| DNO | int | NO | PRI | NULL | |
| DNAME | varchar(10) | YES | | NULL | |
| MGRSTARTDATE | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> CREATE TABLE EMPLOYEE(SSN VARCHAR(10) PRIMARY KEY,NAME VARCHAR(10),ADDRESS
VARCHAR(10),SEX CHAR(1),SALARY DECIMAL(10,2),SUPERSSN VARCHAR(10),FOREIGN
KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE CASCADE, DNO INT, FOREIGN KEY(DNO)
REFERENCES DEPARTMENT (DNO));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC EMPLOYEE;

+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| SSN | varchar(10) | NO | PRI | NULL | |
| NAME | varchar(10) | YES | | NULL | |
| ADDRESS | varchar(10) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
| SUPERSSN | varchar(10) | YES | MUL | NULL | |
| DNO | int | YES | MUL | NULL | |
+----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE DEPARTMENT ADD (MGRSSN VARCHAR(10), FOREIGN KEY(MGRSSN)

REFERENCES EMPLOYEE(SSN) ON DELETE CASCADE);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC DEPARTMENT;

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| DNO | int | NO | PRI | NULL | |
| DNAME | varchar(10) | YES | | NULL | |
| MGRSTARTDATE | date | YES | | NULL | |
| MGRSSN | varchar(10) | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE DELOCATION(DNO INT, DLOC VARCHAR(10),PRIMARY

KEY(DNO,DLOC),FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO) ON DELETE CASCADE);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC DELOCATION;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| DNO | int | NO | PRI | NULL | |
| DLOC | varchar(10) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE PROJECT(PNO INT PRIMARY KEY,PNAME VARCHAR(10),PLOCATION

VARCHAR(10),DNO INT,FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO) ON DELETE CASCADE);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC PROJECT;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| PNO | int | NO | PRI | NULL | |
| PNAME | varchar(10) | YES | | NULL | |
| PLOCATION | varchar(10) | YES | | NULL | |
| DNO | int | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE WORKS_ON(SSN VARCHAR(10), PNO INT,HOURS INT,PRIMARY

KEY(SSN,PNO), FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN) ON DELETE CASCADE,FOREIGN
KEY(PNO) REFERENCES PROJECT(PNO) ON DELETE CASCADE);
Query OK, 0 rows affected (0.03 sec)

mysql> DESC WORKS_ON;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| SSN | varchar(10) | NO | PRI | NULL | |
| PNO | int | NO | PRI | NULL | |
| HOURS | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001'INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL2mysql>
LOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21INSERT INTO E mysql>
INSERT INTO EMININSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001',ABHINAV','KERALA'
,'M', EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001',INSERT
INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001',ABHINAV','KERALA'
,'M', DDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001',ABHINAV','KERALA','M',
ARY,SUPERSSN)V 21AI001',ABHINAV','KERALA','M',
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SA
mysql> E(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001',ABHINAV','K
'M',
mysql>
INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001',ABHINAV','KERALAm
ysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001','ABHINAV
SCOTT','KERALA','M',50000,'4AL21AI006');
ERROR 1406 (22001): Data too long for column 'NAME' at row 1
mysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001','ABHINAV','KERALA
','M',50000,'4AL21AI006');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`company`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`SUPERSSN`)
REFERENCES `employee` (`SSN`) ON DELETE CASCADE)
mysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001','ABHINAV
ST','KERALA','M',50000,'4AL21AI006');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`company`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`SUPERSSN`)
REFERENCES `employee` (`SSN`) ON DELETE CASCADE)
mysql> INSERT INTO EMPLOYEE VALUES('4AL21AI001','ABHINAV
','KERALA','M',50000,'4AL21AI006');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> DESC EMPLOYEE;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| SSN | varchar(10) | NO | PRI | NULL | |
| NAME | varchar(10) | YES | | NULL | |
| ADDRESS | varchar(10) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
| SUPERSSN | varchar(10) | YES | MUL | NULL | |
| DNO | int | YES | MUL | NULL | |
+----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> INSERT INTO EMPLOYEE VALUES('4AL21AI001','ABHINAV

','KERALA','M',50000,'4AL21AI006');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001','ABHINAV
ST','KERALA','M',50000,'4AL21AI006');
mysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI001','ABHINAV
ST','KERALA','M',50000,'4AL21AI001');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI002','ABHI','MOODBIDRI
','M',50000,'4AL21AI002');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI003','GUNGI','MOODBIDR
I','M',50000,'4AL21AI003');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI004','BARATH','MANGLOR
E','M',50000,'4AL21AI004');;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI005','BHAVISH','MIJAR'
,'M',50001,'4AL21AI005');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO
EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI006','CHANDAN','MIJAR'
,'M',99999,'4AL21AI006');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI007','DARSHAN','MIJAR'
,'M',50000,'4AL21AI007');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI008','DIKSHITHA','MIJA
R','F',50000,'4AL21AI008');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO

EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY,SUPERSSN)VALUES('4AL21AI009','DANUSH','MIJAR',
'M',50000,'4AL21AI009');
mysql> DESC EMPLOYEE;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| SSN | varchar(10) | NO | PRI | NULL | |
| NAME | varchar(10) | YES | | NULL | |
| ADDRESS | varchar(10) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
| SUPERSSN | varchar(10) | YES | MUL | NULL | |
| DNO | int | YES | MUL | NULL | |
+----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FRPM EMPLOYEE

-> ^C
mysql> SELECT * FRPM EMPLOYEE;
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 'FRPM
EMPLOYEE' at line 1
mysql> SELECT * FROM EMPLOYEE;
+------------+------------+-----------+------+----------+------------+------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
+------------+------------+-----------+------+----------+------------+------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
| 4AL21AI002 | ABHI | MOODBIDRI | M | 50000.00 | 4AL21AI002 | NULL |
| 4AL21AI003 | GUNGI | MOODBIDRI | M | 50000.00 | 4AL21AI003 | NULL |
| 4AL21AI004 | BARATH | MANGLORE | M | 50000.00 | 4AL21AI004 | NULL |
| 4AL21AI005 | BHAVISH | MIJAR | M | 50001.00 | 4AL21AI005 | NULL |
| 4AL21AI006 | CHANDAN | MIJAR | M | 99999.00 | 4AL21AI006 | NULL |
| 4AL21AI007 | DARSHAN | MIJAR | M | 50000.00 | 4AL21AI007 | NULL |
| 4AL21AI008 | DIKSHITHA | MIJAR | F | 50000.00 | 4AL21AI008 | NULL |
| 4AL21AI009 | DANUSH | MIJAR | M | 50000.00 | 4AL21AI009 | NULL |
+------------+------------+-----------+------+----------+------------+------+
9 rows in set (0.00 sec)

mysql> INSERT INTO DEPARTMENT VALUES(1,'IOT','2017-01-01','4AL21AI001');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DEPARTMENT VALUES(2,'CLOUD COMP','2017-01-01','4AL21AI002');

Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DEPARTMENT VALUES(3,'DBMS','2017-01-03','4AL21AI003');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DEPARTMENT VALUES(4,'PAI','2017-02-03','4AL21AI004');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DEPARTMENT VALUES(5,'AIML','2017-04-03','4AL21AI005');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM DEPARTMENT;

+-----+------------+--------------+------------+
| DNO | DNAME | MGRSTARTDATE | MGRSSN |
+-----+------------+--------------+------------+
| 1 | IOT | 2017-01-01 | 4AL21AI001 |
| 2 | CLOUD COMP | 2017-01-01 | 4AL21AI002 |
| 3 | DBMS | 2017-01-03 | 4AL21AI003 |
| 4 | PAI | 2017-02-03 | 4AL21AI004 |
| 5 | AIML | 2017-04-03 | 4AL21AI005 |
+-----+------------+--------------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO DLOCATION VALUES(1,'FIRST FL');

ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> INSERT INTO DLOCATION VALUES(1,'FIRST FL');
ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> INSERT INTO DLOCATION VALUES(1,'FIRST FLORE');
ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> SELECT * FROM DEPARTMENT;
+-----+------------+--------------+------------+
| DNO | DNAME | MGRSTARTDATE | MGRSSN |
+-----+------------+--------------+------------+
| 1 | IOT | 2017-01-01 | 4AL21AI001 |
| 2 | CLOUD COMP | 2017-01-01 | 4AL21AI002 |
| 3 | DBMS | 2017-01-03 | 4AL21AI003 |
| 4 | PAI | 2017-02-03 | 4AL21AI004 |
| 5 | AIML | 2017-04-03 | 4AL21AI005 |
+-----+------------+--------------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO DLOCATION VALUES(1,'FIRST FL');

ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> DESC DLOCATION;
ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> DESC DELOCATION;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| DNO | int | NO | PRI | NULL | |
| DLOC | varchar(10) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO DLOCATION VALUES(1,'FIRST FL');

ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> INSERT INTO DELOCATION VALUES(1,'FIRST FL');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DELOCATION VALUES(2,'SECOND FL');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DELOCATION VALUES(3,'THIRD FL');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DELOCATION VALUES(4,'FOURTH FL');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO DELOCATION VALUES(5,'FIFTH FL');

Query OK, 1 row affected (0.00 sec)

mysql> DESC DELOCATION;

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| DNO | int | NO | PRI | NULL | |
| DLOC | varchar(10) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM DELOCATION;

+-----+-----------+
| DNO | DLOC |
+-----+-----------+
| 1 | FIRST FL |
| 2 | SECOND FL |
| 3 | THIRD FL |
| 4 | FOURTH FL |
| 5 | FIFTH FL |
+-----+-----------+
5 rows in set (0.00 sec)

mysql> INSERT INTO PROJECT VALUES(1,'IOT','KARWAR',1);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PROJECT VALUES(1,'AIML','KARWAR',2);

ERROR 1062 (23000): Duplicate entry '1' for key 'project.PRIMARY'
mysql> INSERT INTO PROJECT VALUES(2,'AIML','KARWAR',2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PROJECT VALUES(3,'DL','KARWAR',3);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PROJECT VALUES(3,'CLOUD COMP','DELHI',3);

ERROR 1062 (23000): Duplicate entry '3' for key 'project.PRIMARY'
mysql> INSERT INTO PROJECT VALUES(4,'CLOUD COMP','DELHI',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PROJECT VALUES(5,'CLOUD COMP','MUMBAI',5);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM RMPLOYEE, WORKS_ON W, WHERE E.SSN=W.SSN AND NAME LIKE
"%SCOTT";
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
E.SSN=W.SSN AND NAME LIKE "%SCOTT"' at line 1
mysql> INSERT INTO WORKS_ON VALUES('4AL21AI001',1,24);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO WORKS_ON VALUES('4AL21AI002',1,24);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI003',1,24);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI004',1,24);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI005',1,24);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI006',2,24);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI007',3,24);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI008',4,24);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO WORKS_ON VALUES('4AL21AI009',5,24);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM WORKS_ON;

+------------+-----+-------+
| SSN | PNO | HOURS |
+------------+-----+-------+
| 4AL21AI001 | 1 | 24 |
| 4AL21AI002 | 1 | 24 |
| 4AL21AI003 | 1 | 24 |
| 4AL21AI004 | 1 | 24 |
| 4AL21AI005 | 1 | 24 |
| 4AL21AI006 | 2 | 24 |
| 4AL21AI007 | 3 | 24 |
| 4AL21AI008 | 4 | 24 |
| 4AL21AI009 | 5 | 24 |
+------------+-----+-------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE E,WORKS_ON W WHERE E.SSN=E.SSN AND NAME LIKE"%SCOTT";
Empty set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE E,WORKS_ON W WHERE E.SSN=E.SSN AND NAME LIKE"%ST";
+------------+------------+---------+------+----------+------------+------
+------------+-----+-------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO | SSN
| PNO | HOURS |
+------------+------------+---------+------+----------+------------+------
+------------+-----+-------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI001 | 1 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI002 | 1 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI003 | 1 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI004 | 1 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI005 | 1 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI006 | 2 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI007 | 3 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI008 | 4 | 24 |
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
4AL21AI009 | 5 | 24 |
+------------+------------+---------+------+----------+------------+------
+------------+-----+-------+
9 rows in set (0.00 sec)

mysql> SELECT PNO FROM EMPLOYEE E,WORKS_ON W WHERE E.SSN=E.SSN AND NAME LIKE"%ST";
+-----+
| PNO |
+-----+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-----+
9 rows in set (0.00 sec)

mysql>
IKE "%ST";
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 ',DNO AND
NAME LIKE "%ST"' at line 1
mysql> SELECT PNO FROM PROJECT P, DEPARTMENT D, EMPLOYEE WHERE P.PNO=D,DNO AND
NAME LIKE "%ST";
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 ',DNO AND
NAME LIKE "%ST"' at line 1
mysql> SELECT * FROM PROJECT E,WORKS_ON W WHERE E.SSN=W.SSN AND SSN=MGRSSN AND
DEPARTMENT SELECT * FROM PROJECT E,WORKS_ON W WHERE E.SSN=W.SSN AND SSN=MGRSSN AND
DEPARTMENT D, EMPLOYEE WHERE P.PNO=D,DNO AND NAME LIKE "%ST";
mysql> USE COMPANY;
Database changed
mysql> SELECT * FROM EMPLOYEE;
+------------+------------+-----------+------+----------+------------+------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
+------------+------------+-----------+------+----------+------------+------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
| 4AL21AI002 | ABHI | MOODBIDRI | M | 50000.00 | 4AL21AI002 | NULL |
| 4AL21AI003 | GUNGI | MOODBIDRI | M | 50000.00 | 4AL21AI003 | NULL |
| 4AL21AI004 | BARATH | MANGLORE | M | 50000.00 | 4AL21AI004 | NULL |
| 4AL21AI005 | BHAVISH | MIJAR | M | 50001.00 | 4AL21AI005 | NULL |
| 4AL21AI006 | CHANDAN | MIJAR | M | 99999.00 | 4AL21AI006 | NULL |
| 4AL21AI007 | DARSHAN | MIJAR | M | 50000.00 | 4AL21AI007 | NULL |
| 4AL21AI008 | DIKSHITHA | MIJAR | F | 50000.00 | 4AL21AI008 | NULL |
| 4AL21AI009 | DANUSH | MIJAR | M | 50000.00 | 4AL21AI009 | NULL |
+------------+------------+-----------+------+----------+------------+------+
9 rows in set (0.01 sec)
mysql> SELECT * FROM DEPARTMENT;
+-----+------------+--------------+------------+
| DNO | DNAME | MGRSTARTDATE | MGRSSN |
+-----+------------+--------------+------------+
| 1 | IOT | 2017-01-01 | 4AL21AI001 |
| 2 | CLOUD COMP | 2017-01-01 | 4AL21AI002 |
| 3 | DBMS | 2017-01-03 | 4AL21AI003 |
| 4 | PAI | 2017-02-03 | 4AL21AI004 |
| 5 | AIML | 2017-04-03 | 4AL21AI005 |
+-----+------------+--------------+------------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM DLOCATION;

ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> DESC DLOCATION
-> ^C
mysql> DESC DLOCATION;
ERROR 1146 (42S02): Table 'company.dlocation' doesn't exist
mysql> SELECT * FROM PROJECT;
+-----+------------+-----------+------+
| PNO | PNAME | PLOCATION | DNO |
+-----+------------+-----------+------+
| 1 | IOT | KARWAR | 1 |
| 2 | AIML | KARWAR | 2 |
| 3 | DL | KARWAR | 3 |
| 4 | CLOUD COMP | DELHI | 4 |
| 5 | CLOUD COMP | MUMBAI | 5 |
+-----+------------+-----------+------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM WORKS_ON;

+------------+-----+-------+
| SSN | PNO | HOURS |
+------------+-----+-------+
| 4AL21AI001 | 1 | 24 |
| 4AL21AI002 | 1 | 24 |
| 4AL21AI003 | 1 | 24 |
| 4AL21AI004 | 1 | 24 |
| 4AL21AI005 | 1 | 24 |
| 4AL21AI006 | 2 | 24 |
| 4AL21AI007 | 3 | 24 |
| 4AL21AI008 | 4 | 24 |
| 4AL21AI009 | 5 | 24 |
+------------+-----+-------+
9 rows in set (0.01 sec)

mysql> SHOW TABLES;

+-------------------+
| Tables_in_company |
+-------------------+
| delocation |
| department |
| employee |
| project |
| works_on |
+-------------------+
5 rows in set (0.01 sec)
mysql> SELECT * FROM DELOCATION;
+-----+-----------+
| DNO | DLOC |
+-----+-----------+
| 1 | FIRST FL |
| 2 | SECOND FL |
| 3 | THIRD FL |
| 4 | FOURTH FL |
| 5 | FIFTH FL |
+-----+-----------+
5 rows in set (0.01 sec)

mysql> DESC EMPLOYEE;

+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| SSN | varchar(10) | NO | PRI | NULL | |
| NAME | varchar(10) | YES | | NULL | |
| ADDRESS | varchar(10) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
| SUPERSSN | varchar(10) | YES | MUL | NULL | |
| DNO | int | YES | MUL | NULL | |
+----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE,DEPARTMENT WHERE SSN > MGRSSN AND

NAMESELECSELECSELECTSELECT * FROM ESELECT * FSELECT * FROMSESESELESELECT SELECT
*SELESELSESESSSSSSESELECT * FROM EMPLOYEE,DEPARTMENT ^C
E SSN > MGRSSN AND NASELECT * FROM EMPLOYEE,DEPARTMENT WHERE SSN MGRSSN AND NAME
LIKE '%ST';
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 'MGRSSN
AND NAME LIKE '%ST'' at line 1
mysql> SELECT * FROM EMPLOYEE,DEPARTMENT WHERE SSN MGRSSN AND NAME LIKE '%ST';
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 'MGRSSN
AND NAME LIKE '%ST'' at line 1
mysql> SELECT * FROM EMPLOYEE,DEPARTMENT WHERE SSN = MGRSSN AND NAME LIKE '%ST';
+------------+------------+---------+------+----------+------------+------+-----
+-------+--------------+------------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO | DNO |
DNAME | MGRSTARTDATE | MGRSSN |
+------------+------------+---------+------+----------+------------+------+-----
+-------+--------------+------------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL | 1 |
IOT | 2017-01-01 | 4AL21AI001 |
+------------+------------+---------+------+----------+------------+------+-----
+-------+--------------+------------+
1 row in set (0.00 sec)

mysql> SELECT D.DNO FROM EMPLOYEE,DEPARTMENT D WHERE SSN = MGRSSN AND NAME LIKE
'%ST';
+-----+
| DNO |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> SELECT PNO FROM PROJECT WHERE DNO IN (SELECT D.DNO FROM EMPLOYEE ,
DEPARTMENT D WHERE SSN = MGRSSN AND NAME LIKE '%ST';
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 '' at
line 1
mysql> SELECT PNO FROM PROJECT WHERE DNO IN (SELECT D.DNO FROM EMPLOYEE ,
DEPARTMENT D WHERE SSN = MGRSSN AND NAME LIKE '%ST');
+-----+
| PNO |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE;

+------------+------------+-----------+------+----------+------------+------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
+------------+------------+-----------+------+----------+------------+------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 50000.00 | 4AL21AI001 | NULL |
| 4AL21AI002 | ABHI | MOODBIDRI | M | 50000.00 | 4AL21AI002 | NULL |
| 4AL21AI003 | GUNGI | MOODBIDRI | M | 50000.00 | 4AL21AI003 | NULL |
| 4AL21AI004 | BARATH | MANGLORE | M | 50000.00 | 4AL21AI004 | NULL |
| 4AL21AI005 | BHAVISH | MIJAR | M | 50001.00 | 4AL21AI005 | NULL |
| 4AL21AI006 | CHANDAN | MIJAR | M | 99999.00 | 4AL21AI006 | NULL |
| 4AL21AI007 | DARSHAN | MIJAR | M | 50000.00 | 4AL21AI007 | NULL |
| 4AL21AI008 | DIKSHITHA | MIJAR | F | 50000.00 | 4AL21AI008 | NULL |
| 4AL21AI009 | DANUSH | MIJAR | M | 50000.00 | 4AL21AI009 | NULL |
+------------+------------+-----------+------+----------+------------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM PROJECT WHERE PNAME = IOT;

ERROR 1054 (42S22): Unknown column 'IOT' in 'where clause'
mysql> SELECT * FROM PROJECT WHERE PNAME = 'IOT';
+-----+-------+-----------+------+
| PNO | PNAME | PLOCATION | DNO |
+-----+-------+-----------+------+
| 1 | IOT | KARWAR | 1 |
+-----+-------+-----------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM WORKS_ON WHERE PNO IN(SELECT PNO FROM PROJECT WHERE PNAME
='IOT';
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 '' at
line 1
mysql> SELECT * FROM WORKS_ON WHERE PNO IN(SELECT PNO FROM PROJECT WHERE PNAME
='IOT');
+------------+-----+-------+
| SSN | PNO | HOURS |
+------------+-----+-------+
| 4AL21AI001 | 1 | 24 |
| 4AL21AI002 | 1 | 24 |
| 4AL21AI003 | 1 | 24 |
| 4AL21AI004 | 1 | 24 |
| 4AL21AI005 | 1 | 24 |
+------------+-----+-------+
5 rows in set (0.00 sec)
mysql> UPDATE EMPLOYEE SET SALARY = SALARY + SALARY*0.10 WHERE SSN IN(SELECT SSN
FROM WORKS_ON WHERE PNO IN(SELECT PNO FROM PROJECT WHERE PNAME = 'IOT'));
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> SELECT * FROM EMPLOYEE;

+------------+------------+-----------+------+----------+------------+------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
+------------+------------+-----------+------+----------+------------+------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 55000.00 | 4AL21AI001 | NULL |
| 4AL21AI002 | ABHI | MOODBIDRI | M | 55000.00 | 4AL21AI002 | NULL |
| 4AL21AI003 | GUNGI | MOODBIDRI | M | 55000.00 | 4AL21AI003 | NULL |
| 4AL21AI004 | BARATH | MANGLORE | M | 55000.00 | 4AL21AI004 | NULL |
| 4AL21AI005 | BHAVISH | MIJAR | M | 55001.10 | 4AL21AI005 | NULL |
| 4AL21AI006 | CHANDAN | MIJAR | M | 99999.00 | 4AL21AI006 | NULL |
| 4AL21AI007 | DARSHAN | MIJAR | M | 50000.00 | 4AL21AI007 | NULL |
| 4AL21AI008 | DIKSHITHA | MIJAR | F | 50000.00 | 4AL21AI008 | NULL |
| 4AL21AI009 | DANUSH | MIJAR | M | 50000.00 | 4AL21AI009 | NULL |
+------------+------------+-----------+------+----------+------------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM DEPARTMENT;

+-----+------------+--------------+------------+
| DNO | DNAME | MGRSTARTDATE | MGRSSN |
+-----+------------+--------------+------------+
| 1 | IOT | 2017-01-01 | 4AL21AI001 |
| 2 | CLOUD COMP | 2017-01-01 | 4AL21AI002 |
| 3 | DBMS | 2017-01-03 | 4AL21AI003 |
| 4 | PAI | 2017-02-03 | 4AL21AI004 |
| 5 | AIML | 2017-04-03 | 4AL21AI005 |
+-----+------------+--------------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO DEPARTMENT VALUES(1,'ACCOUNTS','2017-01-01','4AL21AI006');

ERROR 1062 (23000): Duplicate entry '1' for key 'department.PRIMARY'
mysql> INSERT INTO DEPARTMENT VALUES(6,'ACCOUNTS','2017-01-01','4AL21AI006');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM DEPARTMENT;

+-----+------------+--------------+------------+
| DNO | DNAME | MGRSTARTDATE | MGRSSN |
+-----+------------+--------------+------------+
| 1 | IOT | 2017-01-01 | 4AL21AI001 |
| 2 | CLOUD COMP | 2017-01-01 | 4AL21AI002 |
| 3 | DBMS | 2017-01-03 | 4AL21AI003 |
| 4 | PAI | 2017-02-03 | 4AL21AI004 |
| 5 | AIML | 2017-04-03 | 4AL21AI005 |
| 6 | ACCOUNTS | 2017-01-01 | 4AL21AI006 |
+-----+------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM DEPARTMENT WHERE DNAME = 'ACCOONTS';

Empty set (0.00 sec)

mysql> SELECT * FROM DEPARTMENT WHERE DNAME = 'ACCOUNTS';

+-----+----------+--------------+------------+
| DNO | DNAME | MGRSTARTDATE | MGRSSN |
+-----+----------+--------------+------------+
| 6 | ACCOUNTS | 2017-01-01 | 4AL21AI006 |
+-----+----------+--------------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE DNO IN(SELECTDNO FROM DEPARTMENT WHERE DNAME
='ACCOUNTS';
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 'FROM
DEPARTMENT WHERE DNAME ='ACCOUNTS'' at line 1
mysql> SELECT * FROM EMPLOYEE WHERE DNO IN(SELECTDNO FROM DEPARTMENT WHERE DNAME
='ACCOUNTS');
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 'FROM
DEPARTMENT WHERE DNAME ='ACCOUNTS')' at line 1
mysql> SELECT * FROM EMPLOYEE WHERE DNO IN(SELECT DNO FROM DEPARTMENT WHERE DNAME
='ACCOUNTS');
Empty set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE ORDER BY DNO;

+------------+------------+-----------+------+----------+------------+------+
| SSN | NAME | ADDRESS | SEX | SALARY | SUPERSSN | DNO |
+------------+------------+-----------+------+----------+------------+------+
| 4AL21AI001 | ABHINAV ST | KERALA | M | 55000.00 | 4AL21AI001 | NULL |
| 4AL21AI002 | ABHI | MOODBIDRI | M | 55000.00 | 4AL21AI002 | NULL |
| 4AL21AI003 | GUNGI | MOODBIDRI | M | 55000.00 | 4AL21AI003 | NULL |
| 4AL21AI004 | BARATH | MANGLORE | M | 55000.00 | 4AL21AI004 | NULL |
| 4AL21AI005 | BHAVISH | MIJAR | M | 55001.10 | 4AL21AI005 | NULL |
| 4AL21AI006 | CHANDAN | MIJAR | M | 99999.00 | 4AL21AI006 | NULL |
| 4AL21AI007 | DARSHAN | MIJAR | M | 50000.00 | 4AL21AI007 | NULL |
| 4AL21AI008 | DIKSHITHA | MIJAR | F | 50000.00 | 4AL21AI008 | NULL |
| 4AL21AI009 | DANUSH | MIJAR | M | 50000.00 | 4AL21AI009 | NULL |
+------------+------------+-----------+------+----------+------------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE GROUP BY DNO;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'company.EMPLOYEE.SSN' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
mysql> SELECT COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
+------------+
| COUNT(SSN) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(SSN) FROM EMPLOYEE GROUP BY DNO,COUN5T(SSN);

ERROR 1305 (42000): FUNCTION company.COUN5T does not exist
mysql> SELECT COUNT(SSN) FROM EMPLOYEE GROUP BY DNO,COUN5T(SSN);
ERROR 1305 (42000): FUNCTION company.COUN5T does not exist
mysql> SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
+------+------------+
| DNO | COUNT(SSN) |
+------+------------+
| NULL | 9 |
+------+------------+
1 row in set (0.00 sec)

mysql> SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;

+------+------------+
| DNO | COUNT(SSN) |
+------+------------+
| NULL | 9 |
+------+------------+
1 row in set (0.00 sec)

mysql> SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;

+------+------------+
| DNO | COUNT(SSN) |
+------+------------+
| NULL | 9 |
+------+------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> ^C
mysql> mysql>
-> mysql>
-> mysql> ^C
-> ^C
mysql> SELECT DNO,COUNT(SSN) FROM EMPLOYEE GROUP BY DNO HAVING COUNT(*)>5;
+------+------------+
| DNO | COUNT(SSN) |
+------+------------+
| NULL | 9 |
+------+------------+
1 row in set (0.00 sec)

mysql> mysql>
-> mysql>
-> mysql>
-> SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
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 'mysql>
mysql>
mysql>
SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO' at line 1
mysql> SELECT DNO, COUNT(SSN) FROM EMPLOYEE GROUP BY DNO;
+------+------------+
| DNO | COUNT(SSN) |
+------+------------+
| NULL | 9 |
+------+------------+
1 row in set (0.00 sec)

m
mysql> SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(*)>5;
+------+
| DNO |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT DNO , COUNT(SSN) FROM EMPLOYEE WHERE SALARY > 600000 AND DNO
IN(SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(*)>5);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT
list contains nonaggregated column 'company.EMPLOYEE.DNO'; this is incompatible
with sql_mode=only_full_group_by
mysql> SELECT DNO , COUNT(SSN) FROM EMPLOYEE WHERE SALARY > 600000 AND DNO
IN(SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(*)>5) GROUP BY DNO;
Empty set (0.00 sec)