mysql> create database d1;

Query OK, 1 row affected (0.06 sec)

mysql> create table DOCTOR(DID CHAR(3) PRIMARY KEY,DNAME VARCHAR(30) NOT NULL,DEPT
CHAR(5),CHARGES INTEGER,VISITDAYS CHAR(4) NOT NULL);
ERROR 1046 (3D000): No database selected
mysql> USE D1;
Database changed
mysql> create table DOCTOR(DID CHAR(3) PRIMARY KEY,DNAME VARCHAR(30) NOT NULL,DEPT
CHAR(5),CHARGES INTEGER,VISITDAYS CHAR(4) NOT NULL);
Query OK, 0 rows affected (0.09 sec)

mysql> DESC DOCTOR;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DID | char(3) | NO | PRI | NULL | |
| DNAME | varchar(30) | NO | | NULL | |
| DEPT | char(5) | YES | | NULL | |
| CHARGES | int(11) | YES | | NULL | |
| VISITDAYS | char(4) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

mysql> INSERT INTO DOCTOR VALUES('D01','R.SHARMA','ENT',1000,'MON');

Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO DOCTOR VALUES('D02','D.BASAK','ENT',1500,'WED');

Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO DOCTOR VALUES('D03','M.AGARWAL','PAED',6000,'SAT');

Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO DOCTOR VALUES('D04','E.JOSEPH','ORTHO',1200,'SUN');

Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO DOCTOR VALUES('D05','M.FERNANDEZ','ORTHO',4000,'THU');

Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+---------+-----------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS |
+-----+-------------+-------+---------+-----------+
| D01 | R.SHARMA | ENT | 1000 | MON |
| D02 | D.BASAK | ENT | 1500 | WED |
| D03 | M.AGARWAL | PAED | 6000 | SAT |
| D04 | E.JOSEPH | ORTHO | 1200 | SUN |
| D05 | M.FERNANDEZ | ORTHO | 4000 | THU |
+-----+-------------+-------+---------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT MAX(CHARGE) FROM DOCTOR WHERE DEPT LIKE 'ORTHO';
ERROR 1054 (42S22): Unknown column 'CHARGE' in 'field list'
mysql> SELECT MAX(CHARGES) FROM DOCTOR WHERE DEPT LIKE 'ORTHO';
+--------------+
| MAX(CHARGES) |
+--------------+
| 4000 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(LCASE(DNAME),DEPT) FROM DOCTOR;

+---------------------------+
| CONCAT(LCASE(DNAME),DEPT) |
+---------------------------+
| r.sharmaENT |
| d.basakENT |
| m.agarwalPAED |
| e.josephORTHO |
| m.fernandezORTHO |
+---------------------------+
5 rows in set (0.00 sec)

mysql> SELECT DEPT,COUNT(DEPT) FROM DOCTOR GROUP BY DEPT;

+-------+-------------+
| DEPT | COUNT(DEPT) |
+-------+-------------+
| ENT | 2|
| PAED | 1|
| ORTHO | 2|
+-------+-------------+
3 rows in set (0.01 sec)

mysql> SELECT DEPT,AVG(CHARGES) FROM DOCTOR GROUP BY DEPT;

+-------+--------------+
| DEPT | AVG(CHARGES) |
+-------+--------------+
| ENT | 1250.0000 |
| PAED | 6000.0000 |
| ORTHO | 2600.0000 |
+-------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT DEPT,ROUND(AVG(CHARGES)) FROM DOCTOR GROUP BY DEPT;

+-------+---------------------+
| DEPT | ROUND(AVG(CHARGES)) |
+-------+---------------------+
| ENT | 1250 |
| PAED | 6000 |
| ORTHO | 2600 |
+-------+---------------------+
3 rows in set (0.01 sec)
mysql> SELECT DEPT,ROUND(AVG(CHARGES),2) FROM DOCTOR GROUP BY DEPT;
+-------+-----------------------+
| DEPT | ROUND(AVG(CHARGES),2) |
+-------+-----------------------+
| ENT | 1250.00 |
| PAED | 6000.00 |
| ORTHO | 2600.00 |
+-------+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DEPT='ENT';

+-----+----------+------+---------+-----------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS |
+-----+----------+------+---------+-----------+
| D01 | R.SHARMA | ENT | 1000 | MON |
| D02 | D.BASAK | ENT | 1500 | WED |
+-----+----------+------+---------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT DISTINCT(DEPT) FROM DOCTOR;

+-------+
| DEPT |
+-------+
| ENT |
| PAED |
| ORTHO |
+-------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT DEPT) FROM DOCTOR;

+----------------------+
| COUNT(DISTINCT DEPT) |
+----------------------+
| 3|
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT DEPT) "DISTINCT DEPT" FROM DOCTOR;

+---------------+
| DISTINCT DEPT |
+---------------+
| 3|
+---------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE CHARGES BETWEEN 2000 AND 3500;
Empty set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE CHARGES BETWEEN 2000 AND 4000;
+-----+-------------+-------+---------+-----------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS |
+-----+-------------+-------+---------+-----------+
| D05 | M.FERNANDEZ | ORTHO | 4000 | THU |
+-----+-------------+-------+---------+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DOCTOR ORDER BY DNAME;

+-----+-------------+-------+---------+-----------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS |
+-----+-------------+-------+---------+-----------+
| D02 | D.BASAK | ENT | 1500 | WED |
| D04 | E.JOSEPH | ORTHO | 1200 | SUN |
| D03 | M.AGARWAL | PAED | 6000 | SAT |
| D05 | M.FERNANDEZ | ORTHO | 4000 | THU |
| D01 | R.SHARMA | ENT | 1000 | MON |
+-----+-------------+-------+---------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR ORDER BY DNAME DESC;

+-----+-------------+-------+---------+-----------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS |
+-----+-------------+-------+---------+-----------+
| D01 | R.SHARMA | ENT | 1000 | MON |
| D05 | M.FERNANDEZ | ORTHO | 4000 | THU |
| D03 | M.AGARWAL | PAED | 6000 | SAT |
| D04 | E.JOSEPH | ORTHO | 1200 | SUN |
| D02 | D.BASAK | ENT | 1500 | WED |
+-----+-------------+-------+---------+-----------+
5 rows in set (0.00 sec)

mysql> UPDATE DOCTOR SET CHARGES=CHARGES+CHARGES*0.05 WHERE DEPT LIKE 'ORTHO';

Query OK, 2 rows affected (0.08 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+---------+-----------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS |
+-----+-------------+-------+---------+-----------+
| D01 | R.SHARMA | ENT | 1000 | MON |
| D02 | D.BASAK | ENT | 1500 | WED |
| D03 | M.AGARWAL | PAED | 6000 | SAT |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU |
+-----+-------------+-------+---------+-----------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE DOCTOR ADD DOJ DATE;

Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+---------+-----------+------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS | DOJ |
+-----+-------------+-------+---------+-----------+------+
| D01 | R.SHARMA | ENT | 1000 | MON | NULL |
| D02 | D.BASAK | ENT | 1500 | WED | NULL |
| D03 | M.AGARWAL | PAED | 6000 | SAT | NULL |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | NULL |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | NULL |
+-----+-------------+-------+---------+-----------+------+
5 rows in set (0.00 sec)

mysql> UPDATE DOCTOR SET DOJ='2007-09-11' WHERE DID='D01';

Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE DOCTOR SET DOJ='2007-04-21' WHERE DID='D02';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE DOCTOR SET DOJ='2009-12-25' WHERE DID='D03';

Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE DOCTOR SET DOJ='2011-10-08' WHERE DID='D04';

Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+---------+-----------+------------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS | DOJ |
+-----+-------------+-------+---------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | NULL |
+-----+-------------+-------+---------+-----------+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DOJ IS NULL;

+-----+-------------+-------+---------+-----------+------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS | DOJ |
+-----+-------------+-------+---------+-----------+------+
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | NULL |
+-----+-------------+-------+---------+-----------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DOJ IS NOT NULL;

+-----+-----------+-------+---------+-----------+------------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS | DOJ |
+-----+-----------+-------+---------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
+-----+-----------+-------+---------+-----------+------------+
4 rows in set (0.00 sec)

mysql> UPDATE DOCTOR SET DOJ='2011-06-18' WHERE DID='D05';

Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+---------+-----------+------------+
| DID | DNAME | DEPT | CHARGES | VISITDAYS | DOJ |
+-----+-------------+-------+---------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
+-----+-------------+-------+---------+-----------+------------+
5 rows in set (0.00 sec)

mysql> DESC DOCTOR;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DID | char(3) | NO | PRI | NULL | |
| DNAME | varchar(30) | NO | | NULL | |
| DEPT | char(5) | YES | | NULL | |
| CHARGES | int(11) | YES | | NULL | |
| VISITDAYS | char(4) | NO | | NULL | |
| DOJ | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE DOCTOR DROP PRIMARY KEY;

Query OK, 5 rows affected (0.12 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> DESC DOCTOR;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DID | char(3) | NO | | NULL | |
| DNAME | varchar(30) | NO | | NULL | |
| DEPT | char(5) | YES | | NULL | |
| CHARGES | int(11) | YES | | NULL | |
| VISITDAYS | char(4) | NO | | NULL | |
| DOJ | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE DOCTOR ADD PRIMARY KEY(DID);

Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC DOCTOR;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DID | char(3) | NO | PRI | NULL | |
| DNAME | varchar(30) | NO | | NULL | |
| DEPT | char(5) | YES | | NULL | |
| CHARGES | int(11) | YES | | NULL | |
| VISITDAYS | char(4) | NO | | NULL | |
| DOJ | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE DOCTOR MODIFY DNAME VARCHAR(25);

Query OK, 5 rows affected (0.15 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> DESC DOCTOR;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DID | char(3) | NO | PRI | NULL | |
| DNAME | varchar(25) | YES | | NULL | |
| DEPT | char(5) | YES | | NULL | |
| CHARGES | int(11) | YES | | NULL | |
| VISITDAYS | char(4) | NO | | NULL | |
| DOJ | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE DOCTOR CHANGE CHARGES CHARGE INT;

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC DOCTOR;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| DID | char(3) | NO | PRI | NULL | |
| DNAME | varchar(25) | YES | | NULL | |
| DEPT | char(5) | YES | | NULL | |
| CHARGE | int(11) | YES | | NULL | |
| VISITDAYS | char(4) | NO | | NULL | |
| DOJ | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

-----------------------------------------------------------------------------------------------------------------------------------
mysql> SELECT * FROM DOCTOR;
+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
+-----+-------------+-------+--------+-----------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO DOCTOR VALUES('DO6','A.SHARMA','PAED',3000,'MON','2005-09-13');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO DOCTOR VALUES('DO7','K.SHARMA','ENT',1000,'TUE','2006-05-04');

Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO DOCTOR VALUES('DO8','M.ROY','ENT',2000,'MON','2006-08-14');

Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO DOCTOR VALUES('DO9','M.ROY','PAED',2000,'MON','2011-07-11');

Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT DEPT,COUNT(VISITDAYS) FROM DOCTOR GROUP BY VISITDAYS;

+-------+------------------+
| DEPT | COUNT(VISITDAYS) |
+-------+------------------+
| ENT | 4|
| ENT | 1|
| PAED | 1|
| ORTHO | 1|
| ORTHO | 1|
| ENT | 1|
+-------+------------------+
6 rows in set (0.00 sec)

mysql> SELECT DEPT,COUNT(VISITDAYS) FROM DOCTOR GROUP BY DEPT;

+-------+------------------+
| DEPT | COUNT(VISITDAYS) |
+-------+------------------+
| ENT | 4|
| PAED | 3|
| ORTHO | 2|
+-------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT DEPT,COUNT(VISITDAYS) FROM DOCTOR GROUP BY DEPT HAVING COUNT(*)>2;

+------+------------------+
| DEPT | COUNT(VISITDAYS) |
+------+------------------+
| ENT | 4|
| PAED | 3|
+------+------------------+
2 rows in set (0.00 sec)

mysql> UPDATE DOCTOR SET DNAME='K.SEN' WHERE DID='D09';

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT
-> * FROM DOCTOR WHERE DNAME LIKE '%SHARMA%';
+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
+-----+----------+------+--------+-----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DNAME LIKE '%A';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
+-----+----------+------+--------+-----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DNAME LIKE 'A%';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
+-----+----------+------+--------+-----------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DNAME LIKE 'A%A';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
+-----+----------+------+--------+-----------+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(DNAME) FROM DOCTOR WHERE DNAME LIKE 'A%A';

+--------------+
| COUNT(DNAME) |
+--------------+
| 1|
+--------------+
1 row in set (0.00 sec)

mysql> SELECT DEPT FROM DOCTOR WHERE DNAME LIKE '___';

Empty set (0.00 sec)

mysql> SELECT DEPT FROM DOCTOR WHERE DEPT LIKE '___';

+------+
| DEPT |
+------+
| ENT |
| ENT |
| ENT |
| ENT |
+------+
4 rows in set (0.00 sec)

mysql> SELECT DEPT FROM DOCTOR WHERE DEPT LIKE '__T__';

+-------+
| DEPT |
+-------+
| ORTHO |
| ORTHO |
+-------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(DEPT) FROM DOCTOR WHERE DEPT LIKE '__T__';

+-------------+
| COUNT(DEPT) |
+-------------+
| 2|
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE MONTHNAME(DOJ)=09;

Empty set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE MONTHNAME(DOJ)='September';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
+-----+----------+------+--------+-----------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE MONTHNAME(DOJ) like '%-09-%';

Empty set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DOJ like '%-09-%';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
+-----+----------+------+--------+-----------+------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE YEAR(DOJ)=2011;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE YEAR(DOJ) BETWEEN 2006 AND 2008;
+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
+-----+----------+------+--------+-----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE YEAR(DOJ) BETWEEN 2006 AND 2008 ORDER BY VISITDAYS
DESC;
+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
+-----+----------+------+--------+-----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DAYNAME(DOJ)='TUESDAY';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
+-----+----------+------+--------+-----------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR WHERE DAYNAME(DOJ) LIKE 'TUESDAY';

+-----+----------+------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+----------+------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
+-----+----------+------+--------+-----------+------------+
2 rows in set (0.00 sec)

mysql> SELECT DNAME,DID,DEPT FROM DOCTOR WHERE DAYNAME(DOJ) LIKE 'TUESDAY';

+----------+-----+------+
| DNAME | DID | DEPT |
+----------+-----+------+
| R.SHARMA | D01 | ENT |
| A.SHARMA | DO6 | PAED |
+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT DNAME,DID,DEPT,DOJ FROM DOCTOR WHERE DAYNAME(DOJ) LIKE 'TUESDAY';

+----------+-----+------+------------+
| DNAME | DID | DEPT | DOJ |
+----------+-----+------+------------+
| R.SHARMA | D01 | ENT | 2007-09-11 |
| A.SHARMA | DO6 | PAED | 2005-09-13 |
+----------+-----+------+------------+
2 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------
------------------
05/03/2024
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| d1 |
| employee |
| information_schema |
| mysql |
| performance_schema |
| student |
+--------------------+
6 rows in set (0.03 sec)

mysql> use d1;

Database changed
mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| doctor |
+--------------+
1 row in set (0.02 sec)

mysql> select * from doctor;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.02 sec)

mysql> SELECT LOWER(DEPT) FROM DOCTOR;

+-------------+
| LOWER(DEPT) |
+-------------+
| ent |
| ent |
| paed |
| ortho |
| ortho |
| paed |
| ent |
| ent |
| paed |
+-------------+
9 rows in set (0.01 sec)
mysql> SELECT LCASE(DEPT) FROM DOCTOR;
+-------------+
| LCASE(DEPT) |
+-------------+
| ent |
| ent |
| paed |
| ortho |
| ortho |
| paed |
| ent |
| ent |
| paed |
+-------------+
9 rows in set (0.00 sec)

mysql> SELECT UCASE(DEPT) FROM DOCTOR;

+-------------+
| UCASE(DEPT) |
+-------------+
| ENT |
| ENT |
| PAED |
| ORTHO |
| ORTHO |
| PAED |
| ENT |
| ENT |
| PAED |
+-------------+
9 rows in set (0.00 sec)

mysql> SELECT UPPER(DEPT) FROM DOCTOR;

+-------------+
| UPPER(DEPT) |
+-------------+
| ENT |
| ENT |
| PAED |
| ORTHO |
| ORTHO |
| PAED |
| ENT |
| ENT |
| PAED |
+-------------+
9 rows in set (0.00 sec)

mysql> SELECT LEFT(DEPT,2) FROM DOCTOR;

+--------------+
| LEFT(DEPT,2) |
+--------------+
| EN |
| EN |
| PA |
| OR |
| OR |
| PA |
| EN |
| EN |
| PA |
+--------------+
9 rows in set (0.00 sec)

mysql> SELECT RIGHT(DEPT,2) FROM DOCTOR;

+---------------+
| RIGHT(DEPT,2) |
+---------------+
| NT |
| NT |
| ED |
| HO |
| HO |
| ED |
| NT |
| NT |
| ED |
+---------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT MID(DNAME,3,4) FROM DOCTOR WHERE DEPT LIKE 'ORTHO';

+----------------+
| MID(DNAME,3,4) |
+----------------+
| JOSE |
| FERN |
+----------------+
2 rows in set (0.00 sec)

mysql> SELECT SUBSTR(DNAME,3,4) FROM DOCTOR WHERE DEPT LIKE 'ORTHO';

+-------------------+
| SUBSTR(DNAME,3,4) |
+-------------------+
| JOSE |
| FERN |
+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT MID(DNAME,3,4) FROM DOCTOR WHERE DEPT LIKE 'ORTHO';

+----------------+
| MID(DNAME,3,4) |
+----------------+
| JOSE |
| FERN |
+----------------+
2 rows in set (0.00 sec)

mysql> SELECT SUBSTRING(DNAME,3,4) FROM DOCTOR WHERE DEPT LIKE 'ORTHO';

+----------------------+
| SUBSTRING(DNAME,3,4) |
+----------------------+
| JOSE |
| FERN |
+----------------------+
2 rows in set (0.00 sec)

mysql> SELECT CONCAT(DEPT,VISITDAYS) FROM DOCTOR;

+------------------------+
| CONCAT(DEPT,VISITDAYS) |
+------------------------+
| ENTMON |
| ENTWED |
| PAEDSAT |
| ORTHOSUN |
| ORTHOTHU |
| PAEDMON |
| ENTTUE |
| ENTMON |
| PAEDMON |
+------------------------+
9 rows in set (0.00 sec)
mysql> SELECT TRIM(LEADING '*' FROM "****WELCOME****");
+------------------------------------------+
| TRIM(LEADING '*' FROM "****WELCOME****") |
+------------------------------------------+
| WELCOME**** |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING '*' FROM "****WELCOME****");

+-------------------------------------------+
| TRIM(TRAILING '*' FROM "****WELCOME****") |
+-------------------------------------------+
| ****WELCOME |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH '*' FROM "****WELCOME****");

+---------------------------------------+
| TRIM(BOTH '*' FROM "****WELCOME****") |
+---------------------------------------+
| WELCOME |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LTRIM(" WELCOME ");

+-----------------------------+
| LTRIM(" WELCOME ") |
+-----------------------------+
| WELCOME |
+-----------------------------+
1 row in set (0.01 sec)

mysql> SELECT RTRIM(" WELCOME ");

+-----------------------------+
| RTRIM(" WELCOME ") |
+-----------------------------+
| WELCOME |
+-----------------------------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------
mysql> SELECT LENGTH("WELCOME");
+-------------------+
| LENGTH("WELCOME") |
+-------------------+
| 7|
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT POW(LENGTH("WELCOME"),2);

+--------------------------+
| POW(LENGTH("WELCOME"),2) |
+--------------------------+
| 49 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(DEPT) FROM DOCTOR WHERE VISITDAYS LIKE 'MON';

+--------------+
| LENGTH(DEPT) |
+--------------+
| 3|
| 4|
| 3|
| 4|
+--------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT MOD(14,5);

+-----------+
| MOD(14,5) |
+-----------+
| 4|
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MOD(14,0);

+-----------+
| MOD(14,0) |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MOD(YEAR(CURDATE()),10);

+-------------------------+
| MOD(YEAR(CURDATE()),10) |
+-------------------------+
| 4|
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT MOD(100,LENGTH(VISITDAYS)) WHERE VISITDAYS LIKE 'SUN';

ERROR 1054 (42S22): Unknown column 'VISITDAYS' in 'field list'
mysql> SELECT MOD(100,LENGTH(VISITDAYS)) FROM DOCTOR WHERE VISITDAYS LIKE 'SUN';
+----------------------------+
| MOD(100,LENGTH(VISITDAYS)) |
+----------------------------+
| 1|
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(4523.6748,2);

+--------------------+
| ROUND(4523.6748,2) |
+--------------------+
| 4523.67 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(4523.6748,3);

+--------------------+
| ROUND(4523.6748,3) |
+--------------------+
| 4523.675 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(4523.6748,-3);

+---------------------+
| ROUND(4523.6748,-3) |
+---------------------+
| 5000 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT ROUND(4523.6748,-4);

+---------------------+
| ROUND(4523.6748,-4) |
+---------------------+
| 0|
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(5523.6748,-4);

+---------------------+
| ROUND(5523.6748,-4) |
+---------------------+
| 10000 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(6523.6748,-4);

+---------------------+
| ROUND(6523.6748,-4) |
+---------------------+
| 10000 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(7523.6748,-4);

+---------------------+
| ROUND(7523.6748,-4) |
+---------------------+
| 10000 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(7523.6748);

+------------------+
| ROUND(7523.6748) |
+------------------+
| 7524 |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT ROUND(7523.3748);

+------------------+
| ROUND(7523.3748) |
+------------------+
| 7523 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT SIGN(-90);

+-----------+
| SIGN(-90) |
+-----------+
| -1 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT SIGN(90);

+----------+
| SIGN(90) |
+----------+
| 1|
+----------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
mysql> SELECT TRUNCATE(4356.7891,2);
+-----------------------+
| TRUNCATE(4356.7891,2) |
+-----------------------+
| 4356.78 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(4356.7891,-2);

+------------------------+
| TRUNCATE(4356.7891,-2) |
+------------------------+
| 4300 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(4356.7891,-2);

+---------------------+
| ROUND(4356.7891,-2) |
+---------------------+
| 4400 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(4356.7891,-4);

+---------------------+
| ROUND(4356.7891,-4) |
+---------------------+
| 0|
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(4356.7891,-4);

+------------------------+
| TRUNCATE(4356.7891,-4) |
+------------------------+
| 0|
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(4356.7891,4);

+-----------------------+
| TRUNCATE(4356.7891,4) |
+-----------------------+
| 4356.7891 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(4356.7891,3);
+-----------------------+
| TRUNCATE(4356.7891,3) |
+-----------------------+
| 4356.789 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(4356.7891,5);

+-----------------------+
| TRUNCATE(4356.7891,5) |
+-----------------------+
| 4356.78910 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DOCTOR

-> ;
+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> SELECT DISTINCT(VISITDAYS) FROM DOCTOR;

+-----------+
| VISITDAYS |
+-----------+
| MON |
| WED |
| SAT |
| SUN |
| THU |
| TUE |
+-----------+
6 rows in set (0.01 sec)

mysql> SELECT ALL(VISITDAYS) FROM DOCTOR;

+-----------+
| VISITDAYS |
+-----------+
| MON |
| WED |
| SAT |
| SUN |
| THU |
| MON |
| TUE |
| MON |
| MON |
+-----------+
9 rows in set (0.00 sec)
--------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------
TABLE JOIN
----------------------------------------------------

mysql> CREATE TABLE D2(DID CHAR(3) PRIMARY KEY,AADHAR CHAR(12),PHNO CHAR(10),GRP

CHAR(3));
Query OK, 0 rows affected (0.10 sec)

mysql> DESC D2;

+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| DID | char(3) | NO | PRI | NULL | |
| AADHAR | char(12) | YES | | NULL | |
| PHNO | char(10) | YES | | NULL | |
| GRP | char(3) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM DOCTOR;

+-----+-------------+-------+--------+-----------+------------+
| DID | DNAME | DEPT | CHARGE | VISITDAYS | DOJ |
+-----+-------------+-------+--------+-----------+------------+
| D01 | R.SHARMA | ENT | 1000 | MON | 2007-09-11 |
| D02 | D.BASAK | ENT | 1500 | WED | 2007-04-21 |
| D03 | M.AGARWAL | PAED | 6000 | SAT | 2009-12-25 |
| D04 | E.JOSEPH | ORTHO | 1260 | SUN | 2011-10-08 |
| D05 | M.FERNANDEZ | ORTHO | 4200 | THU | 2011-06-18 |
| DO6 | A.SHARMA | PAED | 3000 | MON | 2005-09-13 |
| DO7 | K.SHARMA | ENT | 1000 | TUE | 2006-05-04 |
| DO8 | M.ROY | ENT | 2000 | MON | 2006-08-14 |
| DO9 | M.ROY | PAED | 2000 | MON | 2011-07-11 |
+-----+-------------+-------+--------+-----------+------------+
9 rows in set (0.00 sec)

mysql> INSERT INTO D2 VALUES('D01','445678903214','9000341678','B+');

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO D2 VALUES('D03','323245456788','8769077801','AB+');

Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO D2 VALUES('D04','223344667788','6294566789','AB+');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO D2 VALUES('D07','434356567676','987650098','A+');

Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM D2;

+-----+--------------+------------+------+
| DID | AADHAR | PHNO | GRP |
+-----+--------------+------------+------+
| D01 | 445678903214 | 9000341678 | B+ |
| D03 | 323245456788 | 8769077801 | AB+ |
| D04 | 223344667788 | 6294566789 | AB+ |
| D07 | 434356567676 | 987650098 | A+ |
+-----+--------------+------------+------+
4 rows in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

DOCTOR.DID=D2.DID;
+-----+-----------+-------+------------+------+
| DID | DNAME | DEPT | PHNO | GRP |
+-----+-----------+-------+------------+------+
| D01 | R.SHARMA | ENT | 9000341678 | B+ |
| D03 | M.AGARWAL | PAED | 8769077801 | AB+ |
| D04 | E.JOSEPH | ORTHO | 6294566789 | AB+ |
+-----+-----------+-------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID;
+-----+-----------+-------+------------+------+
| DID | DNAME | DEPT | PHNO | GRP |
+-----+-----------+-------+------------+------+
| D01 | R.SHARMA | ENT | 9000341678 | B+ |
| D03 | M.AGARWAL | PAED | 8769077801 | AB+ |
| D04 | E.JOSEPH | ORTHO | 6294566789 | AB+ |
+-----+-----------+-------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID AND DEPT='ORTHO';
+-----+----------+-------+------------+------+
| DID | DNAME | DEPT | PHNO | GRP |
+-----+----------+-------+------------+------+
| D04 | E.JOSEPH | ORTHO | 6294566789 | AB+ |
+-----+----------+-------+------------+------+
1 row in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID ORDER BY DNAME;
+-----+-----------+-------+------------+------+
| DID | DNAME | DEPT | PHNO | GRP |
+-----+-----------+-------+------------+------+
| D04 | E.JOSEPH | ORTHO | 6294566789 | AB+ |
| D03 | M.AGARWAL | PAED | 8769077801 | AB+ |
| D01 | R.SHARMA | ENT | 9000341678 | B+ |
+-----+-----------+-------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID ORDER BY DNAME DESC;
+-----+-----------+-------+------------+------+
| DID | DNAME | DEPT | PHNO | GRP |
+-----+-----------+-------+------------+------+
| D01 | R.SHARMA | ENT | 9000341678 | B+ |
| D03 | M.AGARWAL | PAED | 8769077801 | AB+ |
| D04 | E.JOSEPH | ORTHO | 6294566789 | AB+ |
+-----+-----------+-------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,LENGTH(DEPT),PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID ORDER BY DNAME DESC;
+-----+-----------+--------------+------------+------+
| DID | DNAME | LENGTH(DEPT) | PHNO | GRP |
+-----+-----------+--------------+------------+------+
| D01 | R.SHARMA | 3 | 9000341678 | B+ |
| D03 | M.AGARWAL | 4 | 8769077801 | AB+ |
| D04 | E.JOSEPH | 5 | 6294566789 | AB+ |
+-----+-----------+--------------+------------+------+
3 rows in set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID AND DNAME LIKE '%AR' ORDER BY DNAME DESC;
Empty set (0.00 sec)

mysql> SELECT DOCTOR.DID,DNAME,DEPT,PHNO,GRP FROM DOCTOR,D2 WHERE

D2.DID=DOCTOR.DID AND DNAME LIKE '%AR%' ORDER BY DNAME DESC;
+-----+-----------+------+------------+------+
| DID | DNAME | DEPT | PHNO | GRP |
+-----+-----------+------+------------+------+
| D01 | R.SHARMA | ENT | 9000341678 | B+ |
| D03 | M.AGARWAL | PAED | 8769077801 | AB+ |
+-----+-----------+------+------------+------+
2 rows in set (0.00 sec)