MySQL Practical For Class 12 - Part 2

This is Part - 2 of the previous post, In this post we are going to make a new table named Games. We make a new table in the previous Database Practical. So don't confuse.

Firstly, we use our Database Practical

USE PRACTICAL;

Now, we make a table named Games

CREATE TABLE GAMES(GCODE INT(65),
    -> GNAME VARCHAR(255),
    -> NO_OF_PLAYERS INT(65),
    -> PRIZE_MONEY INT(65),
    -> SCHEDULE_DATE DATE);
Query OK, 0 rows affected, 3 warnings (1.43 sec)

Now, we check the description of our table Games.

DESC GAMES;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| GCODE         | int          | YES  |     | NULL    |       |
| GNAME         | varchar(255) | YES  |     | NULL    |       |
| NO_OF_PLAYERS | int          | YES  |     | NULL    |       |
| PRIZE_MONEY   | int          | YES  |     | NULL    |       |
| SCHEDULE_DATE | date         | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.19 sec)

Now, we insert the values in our table Games

INSERT INTO GAMES VALUE(101,"Corrom Board",2,5000,"2004-01-23");
Query OK, 1 row affected (0.09 sec)

INSERT INTO GAMES VALUE(102,"Badminton",2,12000,"2011-04-12");
Query OK, 1 row affected (0.13 sec)

INSERT INTO GAMES VALUE(103,"Table Tennis",4,8000,"2011-02-14");
Query OK, 1 row affected (0.12 sec)

INSERT INTO GAMES VALUE(104,"Chess",4,9000,"2011-01-11");
Query OK, 1 row affected (0.19 sec)

INSERT INTO GAMES VALUE(105,"Lawn Tennis",4,25000,"2011-05-19");
Query OK, 1 row affected (0.19 sec)

Now, we have a look at our table.

SELECT * FROM GAMES;
+-------+--------------+---------------+-------------+---------------+
| GCODE | GNAME        | NO_OF_PLAYERS | PRIZE_MONEY | SCHEDULE_DATE |
+-------+--------------+---------------+-------------+---------------+
|   101 | CarRom Board |             2 |        5000 | 2004-01-23    |
|   102 | Badminton    |             2 |       12000 | 2011-04-12    |
|   103 | Table Tennis |             4 |        8000 | 2011-02-14    |
|   104 | Chess        |             2 |        9000 | 2011-01-11    |
|   105 | Lawn Tennis  |             4 |       25000 | 2011-05-19    |
+-------+--------------+---------------+-------------+---------------+
5 rows in set (0.00 sec)

Q1. To Display Names Of Those Games Whose, Prize Money Is In The Range Of 10,000 To 30,000.

SELECT GNAME FROM GAMES WHERE PRIZE_MONEY BETWEEN 11000 AND 13000;
+-----------+
| GNAME     |
+-----------+
| Badminton |
+-----------+
  1 row in set (0.00 sec)

Q2. To Display The Details Of Games Whose Game Name Starts "c" In Ascending Order Of Scheduled Day.

SELECT GNAME FROM GAMES WHERE GNAME LIKE "C%" ORDER BY SCHEDULE_DATE;
+--------------+
| GNAME        |
+--------------+
| Corrom Board |
| Chess        |
+--------------+
    2 rows in set (0.02 sec)

Q3. Display The First 4 Character Of Each Game Name.

Q4. To Find The Minimum Prize Money

SELECT MIN(PRIZE_MONEY) FROM GAMES;
+------------------+
| MIN(PRIZE_MONEY) |
+------------------+
|             5000 |
+------------------+
      1 row in set (0.07 sec)

Q5. SELECT YEAR(SCHEDULE_DATE) FROM GAMES;

SELECT YEAR(SCHEDULE_DATE) FROM GAMES;
+---------------------+
| YEAR(SCHEDULE_DATE) |
+---------------------+
|                2004 |
|                2011 |
|                2011 |
|                2011 |
|                2011 |
+---------------------+
        5 rows in set (0.00 sec)

Q6. SELECT DAYNAME(SCHEDULE_DATE) FROM GAMES WHERE GNAME LIKE "%S";

SELECT DAYNAME(SCHEDULE_DATE) FROM GAMES WHERE GNAME LIKE "%S";
+------------------------+
| DAYNAME(SCHEDULE_DATE) |
+------------------------+
| Monday                 |
| Tuesday                |
| Thursday               |
+------------------------+
          3 rows in set (0.07 sec)

Q7. SELECT MONTH(SCHEDULE_DATE) FROM GAMES;

SELECT MONTH(SCHEDULE_DATE) FROM GAMES;
+----------------------+
| MONTH(SCHEDULE_DATE) |
+----------------------+
|                    1 |
|                    4 |
|                    2 |
|                    1 |
|                    5 |
+----------------------+
            5 rows in set (0.07 sec)