MySQL Practical For Class 12 - Part 1

 

In this post, we are going to make a MySQL Database which was coming in Class 12 Practical Exam of Information Practices conducted by CBSE.

Firstly, we are going to Create a Database in MySQL

  mysql> CREATE DATABASE PRACTICAL;
  Query OK, 1 row affected (0.72 sec)

Secondly, we use our Database Practical

    USE PRACTICAL;
    Database changed

Now, we create our table Employee

    CREATE TABLE EMPLOYEE(EMPLOYEE_ID VARCHAR(255),
    -> NAME VARCHAR(255),
    -> SALE INT(65),
    -> JOB_ID INT(65));
Query OK, 0 rows affected, 2 warnings (2.31 sec)

Now, we check our table description

DESC EMPLOYEE;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID | varchar(255) | YES  |     | NULL    |       |
| NAME        | varchar(255) | YES  |     | NULL    |       |
| SALE        | int          | YES  |     | NULL    |       |
| JOB_ID      | int          | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.65 sec)

Now, we fill our data in the table Employee

  INSERT INTO EMPLOYEE VALUE("E1","SUMIT SINHA",1100000,102);
Query OK, 1 row affected (0.26 sec)

mysql> INSERT INTO EMPLOYEE VALUE("E2","VIJAY SINGH TOMAR",1300000,101);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO EMPLOYEE VALUE("E3","AJAY RAJPAL",1400000,103);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO EMPLOYEE VALUE("E4","MOHIT RAMNA",1250000,102);
Query OK, 1 row affected (0.21 sec)

mysql> INSERT INTO EMPLOYEE VALUE("E5","SHAILJA SINGH",1450000,103);
  Query OK, 1 row affected (0.15 sec)

Now, we see how our table Employee look.

  SELECT * FROM EMPLOYEE;
+-------------+-------------------+---------+--------+
| EMPLOYEE_ID | NAME              | SALE    | JOB_ID |
+-------------+-------------------+---------+--------+
| E1          | SUMIT SINHA       | 1100000 |    102 |
| E2          | VIJAY SINGH TOMAR | 1300000 |    101 |
| E3          | AJAY RAJPAL       | 1400000 |    103 |
| E4          | MOHIT RAMNA       | 1250000 |    102 |
| E5          | SHAILJA SINGH     | 1450000 |    103 |
+-------------+-------------------+---------+--------+
5 rows in set (0.07 sec)

Q1. To Display EMPLOYEE_ID,NAME Of Employees In Descending Order Sale.

SELECT EMPLOYEE_ID,NAME,SALE FROM EMPLOYEE ORDER BY SALE DESC;
+-------------+-------------------+---------+
| EMPLOYEE_ID | NAME              | SALE    |
+-------------+-------------------+---------+
| E5          | SHAILJA SINGH     | 1450000 |
| E3          | AJAY RAJPAL       | 1400000 |
| E2          | VIJAY SINGH TOMAR | 1300000 |
| E4          | MOHIT RAMNA       | 1250000 |
| E1          | SUMIT SINHA       | 1100000 |
+-------------+-------------------+---------+
5 rows in set (0.00 sec)

Q2. To Display The Name Of Employees, Who Have Achived Sale More Than 13,00,000.

SELECT NAME,SALE FROM EMPLOYEE WHERE SALE>1300000;
+---------------+---------+
| NAME          | SALE    |
+---------------+---------+
| AJAY RAJPAL   | 1400000 |
| SHAILJA SINGH | 1450000 |
+---------------+---------+
2 rows in set (0.07 sec)

Q3. To Display The Names And Corresponding JOB_ID Of Those Employees Who Have Singh Anywhere In Name.

SELECT NAME,JOB_ID FROM EMPLOYEE WHERE NAME LIKE"%SINGH%";
+-------------------+--------+
| NAME              | JOB_ID |
+-------------------+--------+
| VIJAY SINGH TOMAR |    101 |
| SHAILJA SINGH     |    103 |
+-------------------+--------+
2 rows in set (0.13 sec)

Q4. Write SQL Command To Change JOB_ID To 104 Of The Employee With ID As E4 In Table Employee.

UPDATE EMPLOYEE SET JOB_ID = 104 WHERE EMPLOYEE_ID = 'E4';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Q5. Select MOD(SALE,3) FROM EMPLOYEE;

  Select MOD(SALE,3) FROM EMPLOYEE;
+-------------+
| MOD(SALE,3) |
+-------------+
|           2 |
|           1 |
|           2 |
|           2 |
|           1 |
+-------------+
  5 rows in set (0.06 sec)

Q6. Select Length(Name) From Employee Where Sale<1300000;

  Select Length(Name) From Employee Where Sale<1300000;
+--------------+
| Length(Name) |
+--------------+
|           11 |
|           11 |
+--------------+
2 rows in set (0.00 sec)

Q7. Select Lcase(NAME) From Employee Where JOB_ID="103";

  Select Lcase(NAME) From Employee Where JOB_ID="103";
+---------------+
| Lcase(NAME)   |
+---------------+
| ajay rajpal   |
| shailja singh |
+---------------+
  2 rows in set (0.08 sec)

Hope You Learn A Lot From This. Please Share With Your Friends And Also Follow On Social Media. 

PART - 2 WILL BE PUBLISHED ON 1 - APRIL - 2022, AT SHARP 12 PM. (It may take a long time due to heavy traffic.)