MySQL Assignment – Solved (SQL Style)
Base Tables
CUSTOMERS Table
mysql> CREATE TABLE CUSTOMERS (
CNO INT PRIMARY KEY,
CNAME VARCHAR(50),
ADDRESS VARCHAR(50)
);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO CUSTOMERS VALUES
(101, 'Richa Jain', 'Delhi'),
(102, 'Surbhi Sinha', 'Chennai'),
(103, 'Lisa Thomas', 'Bangalore'),
(104, 'Imran Ali', 'Delhi'),
(105, 'Roshan Singh', 'Chennai');
Query OK, 5 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMERS;
+-----+--------------+-----------+
| CNO | CNAME | ADDRESS |
+-----+--------------+-----------+
| 101 | Richa Jain | Delhi |
| 102 | Surbhi Sinha | Chennai |
| 103 | Lisa Thomas | Bangalore |
| 104 | Imran Ali | Delhi |
| 105 | Roshan Singh | Chennai |
+-----+--------------+-----------+
5 rows in set (0.00 sec)
TRANSACTION Table
mysql> CREATE TABLE TRANSACTION (
TRNO VARCHAR(10),
CNO INT,
AMOUNT INT,
TYPE VARCHAR(10),
DOT DATE
);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO TRANSACTION VALUES
('T001', 101, 1500, 'Credit', '2017-11-23'),
('T002', 103, 2000, 'Debit', '2017-05-12'),
('T003', 102, 3000, 'Credit', '2017-06-10'),
('T004', 103, 12000, 'Credit', '2017-09-12'),
('T004', 101, 1000, 'Debit', '2017-09-05');
Query OK, 5 rows affected (0.00 sec)
mysql> SELECT * FROM TRANSACTION;
+------+-----+--------+--------+------------+
| TRNO | CNO | AMOUNT | TYPE | DOT |
+------+-----+--------+--------+------------+
| T002 | 103 | 2000 | Debit | 2017-05-12 |
| T003 | 102 | 3000 | Credit | 2017-06-10 |
| T004 | 101 | 1000 | Debit | 2017-09-05 |
| T004 | 103 | 12000 | Credit | 2017-09-12 |
| T001 | 101 | 1500 | Credit | 2017-11-23 |
+------+-----+--------+--------+------------+
5 rows in set (0.00 sec)
TRAINER Table
mysql> CREATE TABLE TRAINER (
TID INT,
TNAME VARCHAR(50),
CITY VARCHAR(50),
HIREDATE DATE,
SALARY INT
);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO TRAINER VALUES
(101, 'Sunaina', 'Mumbai', '1998-10-15', 90000),
(102, 'Anamika', 'Delhi', '1994-12-24', 80000),
(103, 'Deepti', 'Chandigarh', '2001-12-21', 82000),
(104, 'Meenakshi', 'Delhi', '2002-12-25', 78000),
(105, 'Richa', 'Mumbai', '1996-01-12', 95000),
(106, 'Maniprabha', 'Chennai', '2001-12-12', 69000);
Query OK, 6 rows affected (0.00 sec)
mysql> SELECT * FROM TRAINER;
+-----+------------+------------+------------+--------+
| TID | TNAME | CITY | HIREDATE | SALARY |
+-----+------------+------------+------------+--------+
| 101 | Sunaina | Mumbai | 1998-10-15 | 90000 |
| 102 | Anamika | Delhi | 1994-12-24 | 80000 |
| 103 | Deepti | Chandigarh | 2001-12-21 | 82000 |
| 104 | Meenakshi | Delhi | 2002-12-25 | 78000 |
| 105 | Richa | Mumbai | 1996-01-12 | 95000 |
| 106 | Maniprabha | Chennai | 2001-12-12 | 69000 |
+-----+------------+------------+------------+--------+
6 rows in set (0.00 sec)
COURSE Table
mysql> CREATE TABLE COURSE (
CID VARCHAR(10),
CNAME VARCHAR(50),
FEES INT,
STARTDATE DATE,
TID INT
);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO COURSE VALUES
('C201', 'AGDCA', 12000, '2018-07-02', 101),
('C202', 'ADCA', 15000, '2018-07-15', 103),
('C203', 'DCA', 10000, '2018-10-01', 102),
('C203', 'DDTP', 9000, '2018-09-15', 104),
('C205', 'DHN', 20000, '2018-08-01', 101),
('C206', 'O LEVEL', 18000, '2018-07-25', 105);
Query OK, 6 rows affected (0.00 sec)
mysql> SELECT * FROM COURSE;
+------+---------+-------+------------+-----+
| CID | CNAME | FEES | STARTDATE | TID |
+------+---------+-------+------------+-----+
| C201 | AGDCA | 12000 | 2018-07-02 | 101 |
| C202 | ADCA | 15000 | 2018-07-15 | 103 |
| C203 | DCA | 10000 | 2018-10-01 | 102 |
| C203 | DDTP | 9000 | 2018-09-15 | 104 |
| C205 | DHN | 20000 | 2018-08-01 | 101 |
| C206 | O LEVEL | 18000 | 2018-07-25 | 105 |
+------+---------+-------+------------+-----+
6 rows in set (0.00 sec)