SQL TEST 2

1. SELECT SUM(AMOUNT) FROM CHALLAN WHERE OFFENCECODE='A111';
2. SELECT VEHICLE.REGNO, REGDATE, CH_DATE, AMOUNT FROM VEHICLE, CHALLAN WHERE VEHICLE.REGNO = CHALLAN.REGNO;
3. SELECT COUNT(*), REGDATE FROM VEHICLE WHERE REGDATE LIKE '2011%' ORDER BY REGDATE;
4. SELECT SUM(AMOUNT), OFFENCECODE FROM CHALLAN GROUP BY OFFENCECODE;
5. SELECT COUNT(*), REGNO FROM CHALLAN GROUP BY REGNO HAVING COUNT(*) > 1;
6. SELECT * FROM CHALLAN NATURAL JOIN VEHICLE;
7. SELECT COUNT(*), CH_DATE FROM CHALLAN GROUP BY CH_DATE HAVING COUNT(*) > 2;
8. SELECT COUNT(*), YEAR(CH_DATE) FROM CHALLAN GROUP BY CH_DATE HAVING YEAR(CH_DATE) = 2012;
9. SELECT * FROM CHALLAN ORDER BY CH_DATE DESC;
10. SELECT MIN(REGDATE), MAX(REGDATE) FROM VEHICLE;
11. SELECT COUNT(*), CH_DATE FROM CHALLAN GROUP BY CH_DATE;
12. SELECT CHALLAN.REGNO, OWNER, ADDRESS, CHALLNO, OFFENCECODE FROM CHALLAN, VEHICLE WHERE CHALLAN.REGNO = VEHICLE.REGNO;
13. SELECT COUNT(*), OFFENCECODE FROM CHALLAN GROUP BY OFFENCECODE;
14. SELECT COUNT(*), REGNO FROM VEHICLE GROUP BY REGNO HAVING REGNO LIKE '__09%';
15. ALTER TABLE VEHICLE ADD COLUMN LOCATION CHAR(20);ALTER TABLE VEHICLE ADD COLUMN LOCATION CHAR(20);
16. SELECT REGNO, OWNER FROM VEHICLE WHERE OWNER LIKE '%A%';
17. SELECT COUNT(*), CH_DATE FROM CHALLAN GROUP BY CH_DATE HAVING CH_DATE IS NULL;
18. SELECT * FROM CHALLAN WHERE AMOUNT > 1000;
19. MariaDB [2022xiia]> DESC CHALLAN;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| CHALLNO     | int(11)  | NO   | PRI | NULL    |       |
| CH_DATE     | date     | YES  |     | NULL    |       |
| REGNO       | char(10) | YES  | MUL | NULL    |       |
| OFFENCECODE | char(5)  | NO   |     | NULL    |       |
| AMOUNT      | int(11)  | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.005 sec)

MariaDB [2022xiia]> DESC VEHICLE;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| REGNO    | char(10)    | NO   | PRI | NULL    |       |
| REGDATE  | date        | YES  |     | NULL    |       |
| OWNER    | varchar(30) | NO   |     | NULL    |       |
| ADDRESS  | varchar(30) | NO   |     | NULL    |       |
| LOCATION | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.004 sec)