# --------------------------------------------------------------#

# List-Program No : L6-P1
# Developed By : Muanhlunsang Guite
# Date : 01-October-2024
# --------------------------------------------------------------#

mysql> CREATE TABLE TEACHER (TCODE VARCHAR(3) PRIMARY KEY, TEACHERNAME

VARCHAR(25), STREAM VARCHAR(15), BASIC FLOAT, DATEOFJOINING DATE);
Query OK, 0 rows affected (0.01 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P2
# Developed By : Muanhlunsang Guite
# Date : 01-October-2024
# --------------------------------------------------------------#

mysql> INSERT INTO TEACHER VALUES (101, 'ANANYA MURTY', 'SCIENCE',

18000, '1990-01-23');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEACHER VALUES (202, 'KIRTI KHANEJA', 'HUMANITIES',

28000, '1978-12-12');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEACHER VALUES (103, 'ADIL MEHRA', 'SCIENCE', 8000,
'2001-02-14');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEACHER VALUES (305, 'NISHIYA GOEL', 'COMMERCE',

12000, '1997-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEACHER VALUES (203, 'SHUBH PANDIT', 'HUMANITIES',

22000, '1985-03-19');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEACHER VALUES (109, 'NAINA', 'SCIENCE', 20000,

'1994-07-17');
Query OK, 1 row affected (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P3
# Developed By : Muanhlunsang Guite
# Date : 02-October-2024
# --------------------------------------------------------------#

mysql> CREATE TABLE STUDENT (SCODE INT, NAME VARCHAR(20), TCODE

VARCHAR(3), AGG FLOAT);
Query OK, 0 rows affected (0.01 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P4
# Developed By : Muanhlunsang Guite
# Date : 02-October-2024
# --------------------------------------------------------------#

mysql> INSERT INTO STUDENT VALUES (2, 'NABI AHMAD', 101, 91);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO STUDENT VALUES (1, 'RAVI SAHAI', 305, 84);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO STUDENT VALUES (5, 'VIBHUTI', 203, 67);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO STUDENT VALUES (4, 'NAZNEEN', 103, 89);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO STUDENT VALUES (3, 'ARYANA', 202, 37);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO STUDENT VALUES (6, 'JONATHAN', 305, 45);

Query OK, 1 row affected (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P5
# Developed By : Muanhlunsang Guite
# Date : 02-October-2024
# --------------------------------------------------------------#

mysql> SELECT * FROM TEACHER;

+-------+---------------+------------+-------+---------------+
| TCODE | TEACHERNAME | STREAM | BASIC | DATEOFJOINING |
+-------+---------------+------------+-------+---------------+
| 101 | ANANYA MURTY | SCIENCE | 18000 | 1990-01-23 |
| 103 | ADIL MEHRA | SCIENCE | 8000 | 2001-02-14 |
| 109 | NAINA | SCIENCE | 20000 | 1994-07-17 |
| 202 | KIRTI KHANEJA | HUMANITIES | 28000 | 1978-12-12 |
| 203 | SHUBH PANDIT | HUMANITIES | 22000 | 1985-03-19 |
| 305 | NISHIYA GOEL | COMMERCE | 12000 | 1997-01-01 |
+-------+---------------+------------+-------+---------------+
6 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P6
# Developed By : Muanhlunsang Guite
# Date : 02-October-2024
# --------------------------------------------------------------#

mysql> SELECT * FROM TEACHER WHERE DATEOFJOINING BETWEEN '1978-01-01'

AND '1992-11-30';
+-------+---------------+------------+-------+---------------+
| TCODE | TEACHERNAME | STREAM | BASIC | DATEOFJOINING |
+-------+---------------+------------+-------+---------------+
| 101 | ANANYA MURTY | SCIENCE | 18000 | 1990-01-23 |
| 202 | KIRTI KHANEJA | HUMANITIES | 28000 | 1978-12-12 |
| 203 | SHUBH PANDIT | HUMANITIES | 22000 | 1985-03-19 |
+-------+---------------+------------+-------+---------------+
3 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P7
# Developed By : Muanhlunsang Guite
# Date : 02-October-2024
# --------------------------------------------------------------#

mysql> SELECT * FROM STUDENT ORDER BY AGG DESC;

+-------+------------+-------+------+
| SCODE | NAME | TCODE | AGG |
+-------+------------+-------+------+
| 2 | NABI AHMAD | 101 | 91 |
| 4 | NAZNEEN | 103 | 89 |
| 1 | RAVI SAHAI | 305 | 84 |
| 5 | VIBHUTI | 203 | 67 |
| 6 | JONATHAN | 305 | 45 |
| 3 | ARYANA | 202 | 37 |
+-------+------------+-------+------+
6 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P8
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> SELECT NAME,SCODE FROM STUDENT WHERE TCODE IN (101,203);

+------------+-------+
| NAME | SCODE |
+------------+-------+
| NABI AHMAD | 2 |
| VIBHUTI | 5 |
+------------+-------+
2 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P9
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> SELECT BASIC * 0.25 "INCOME TAX AMOUNT" FROM TEACHER;

+-------------------+
| INCOME TAX AMOUNT |
+-------------------+
| 4500 |
| 2000 |
| 5000 |
| 7000 |
| 5500 |
| 3000 |
+-------------------+
6 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P10
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> UPDATE STUDENT SET AGG=82 WHERE NAME="RAVI SAHAI";

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P11
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> DELETE FROM STUDENT WHERE AGG<40;

Query OK, 1 row affected (0.06 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P12
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> UPDATE TEACHER SET STREAM="HUMANITIES",TCODE=210 WHERE

TEACHERNAME="ANANYA MURTY";
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P13
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> ALTER TABLE STUDENT ADD COLUMN (GRADE VARCHAR(1));

Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P14
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> UPDATE STUDENT SET GRADE='A' WHERE AGG>=70;

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P15
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> UPDATE STUDENT SET GRADE='B' WHERE AGG>=40 AND AGG<70;

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P16
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> UPDATE STUDENT SET GRADE='C' WHERE AGG<40;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P17
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> SELECT TCODE,TEACHERNAME FROM TEACHER WHERE STREAM='HUMANITIES'

ORDER BY TCODE;
+-------+---------------+
| TCODE | TEACHERNAME |
+-------+---------------+
| 202 | KIRTI KHANEJA |
| 203 | SHUBH PANDIT |
| 210 | ANANYA MURTY |
+-------+---------------+
3 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P18
# Developed By : Muanhlunsang Guite
# Date : 10-October-2024
# --------------------------------------------------------------#

mysql> SELECT TEACHERNAME,STREAM,DATEOFJOINING FROM TEACHER ORDER BY

STREAM,DATEOFJOINING DESC;
+---------------+------------+---------------+
| TEACHERNAME | STREAM | DATEOFJOINING |
+---------------+------------+---------------+
| NISHIYA GOEL | COMMERCE | 1997-01-01 |
| ANANYA MURTY | HUMANITIES | 1990-01-23 |
| SHUBH PANDIT | HUMANITIES | 1985-03-19 |
| KIRTI KHANEJA | HUMANITIES | 1978-12-12 |
| ADIL MEHRA | SCIENCE | 2001-02-14 |
| NAINA | SCIENCE | 1994-07-17 |
+---------------+------------+---------------+
6 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P19
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT MAX(BASIC) FROM TEACHER WHERE STREAM='SCIENCE';

+------------+
| MAX(BASIC) |
+------------+
| 20000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(BASIC) FROM TEACHER WHERE STREAM='HUMANITIES';

+------------+
| MAX(BASIC) |
+------------+
| 28000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(BASIC) FROM TEACHER WHERE STREAM='COMMERCE';

+------------+
| MAX(BASIC) |
+------------+
| 12000 |
+------------+
1 row in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P20
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT SUM(BASIC) FROM TEACHER;

+------------+
| SUM(BASIC) |
+------------+
| 108000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT AVG(BASIC) FROM TEACHER;

+------------+
| AVG(BASIC) |
+------------+
| 18000 |
+------------+
1 row in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P21
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT MIN(AGG) FROM STUDENT WHERE TCODE>200;

+----------+
| MIN(AGG) |
+----------+
| 45 |
+----------+
1 row in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P22
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT COUNT(*) FROM TEACHER;

+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P23
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT AVG(AGG) FROM STUDENT WHERE TCODE=101;

+----------+
| AVG(AGG) |
+----------+
| 91 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(AGG) FROM STUDENT WHERE TCODE=305;

+----------+
| AVG(AGG) |
+----------+
| 63.5 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(AGG) FROM STUDENT WHERE TCODE=203;

+----------+
| AVG(AGG) |
+----------+
| 67 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(AGG) FROM STUDENT WHERE TCODE=103;

+----------+
| AVG(AGG) |
+----------+
| 89 |
+----------+
1 row in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P24
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT COUNT(*) FROM TEACHER WHERE STREAM='SCIENCE';

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

mysql> SELECT COUNT(*) FROM TEACHER WHERE STREAM='HUMANITIES';

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

mysql> SELECT COUNT(*) FROM TEACHER WHERE STREAM='COMMERCE';

+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P25
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT DISTINCT STREAM FROM TEACHER;

+------------+
| STREAM |
+------------+
| SCIENCE |
| HUMANITIES |
| COMMERCE |
+------------+
3 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P26
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> UPDATE TEACHER SET BASIC=BASIC+1000 WHERE

DATEOFJOINING<'1995-02-01';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
# --------------------------------------------------------------#
# List-Program No : L6-P27
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT MIN(BASIC) FROM TEACHER GROUP BY STREAM HAVING

COUNT(*)>=2;
+------------+
| MIN(BASIC) |
+------------+
| 8000 |
| 19000 |
+------------+
2 rows in set (0.00 sec)
# --------------------------------------------------------------#
# List-Program No : L6-P28
# Developed By : Muanhlunsang Guite
# Date : 14-October-2024
# --------------------------------------------------------------#

mysql> SELECT NAME,TEACHERNAME,TEACHER.TCODE FROM STUDENT,TEACHER

WHERE STUDENT.TCODE=TEACHER.TCODE;
+------------+--------------+-------+
| NAME | TEACHERNAME | TCODE |
+------------+--------------+-------+
| RAVI SAHAI | NISHIYA GOEL | 305 |
| VIBHUTI | SHUBH PANDIT | 203 |
| NAZNEEN | ADIL MEHRA | 103 |
| JONATHAN | NISHIYA GOEL | 305 |
+------------+--------------+-------+
4 rows in set (0.00 sec)