DAV MODEL SCHOOL, DURGAPUR
MySQL Assignment for Class XII-A2
DATABASE NAME: ABCBA
TABLE 1: EMP
ROLL ADDRESS AGE WEIGHT DOB DID
1 KOLKATA 12 78.50 2008-12-12 111
2 DURGAPUR 13 85.65 2008-06-05 111
3 KOLKATA 12 56.53 2007-12-11 222
4 KOLKATA 13 90.00 2005-09-19 333
5 ASANSOL 15 89.65 2008-05-25 111
6 DURGAPUR 12 96.23 2006-06-18 333
ROLL INT(2) ROLL PRIMARY KEY
ADDRESS VARCHAR(20) ADDRESS NOT NULL
AGE INT(2) AGE DEFAULT 12
DATA CONSTR-
WEIGHT FLOAT(5,2) WEIGHT CHECK >0
TYPE AINTS
DOB DATE DOB UNIQUE
DID INT(3) DID FOREIGN KEY
TABLE 2: DEPT
DID DNAME
111 AI
222 IT
333 CS
DID INT(3) CONSTR- DID PRIMARY KEY
DATA DNAME VARCHAR(20) AINTS DNAME UNIQUE
TYPE
A. Answer the following:
1. Write MySQL Queries for creating the two tables EMP and DEPT (as per
the above data types and constraints).
2. Write MySQL Query for adding a column named “MOB_NO” after the
column “ADDRESS” with data type “INT (10)” and constraint
“UNIQUE” in the table EMP.
3. Write MySQL Query for deleting the column “MOB_NO” from table
EMP.
4. Write MySQL Query for changing the data type “VARCHAR (20)” to
“CHAR(15)” of the table EMP.
5. Write MySQL Query for renaming the column “ADDRESS” to “ADRS”
of the table EMP.
B. Answer the following:
1. Write MySQL Query to Display “ADDRESS” in Lower Case and in
descending order of ADDRESS from the EMP Table.
2. Write MySQL Query to Display Unique “ADDRESS” Values with
ascending order of ROLL from the EMP Table.
3. Write MySQL Query to Print EMP Details Whose “ADDRESS” starts
with “K” ends with ‘A’.
4. Write MySQL Query to Print EMP Details Whose “ADDRESS” contains
exactly 7 characters.
5. Write MySQL Query to Display details of EMP Who Joined in the year
2008.
C. Answer the following:
1. Write MySQL Query to print the details of EMP whose age is in
Between 10 & 14. (Use “between” keyword)
2. Write MySQL Query to Print Employee Count in ‘KOLKATA’ Address.
3. Write MySQL Query to Print EMP Details Excluding the address
“DURGAPUR”.
4. Write MySQL Query to Print “ROLL” and “DOB” of employees who are
youngest from the table EMP.
5. Write MySQL Query to Print EMP Details where weight is more than 90
and address is “DURGAPUR”.
D. Answer the following:
1. Write MySQL Query to Show the heaviest “WEIGHT” per “AGE” group
of EMP.
2. Write MySQL Query to Show the “ADDRESS” and its no. of residents
from Table EMP.
3. Write MySQL Query to Show the average “WEIGHT” per “AGE” group
of the table EMP.
4. Write MySQL Query to Show the total AGE per “ADDRESS” and
descending order of “ADDRESS” from EMP.
5. Write MySQL Query to Show the lightest weight per “ADDRESS” of the
table EMP.
E. Answer the following:
1. Write MySQL Query to Show “ROLL” and its “DNAME ” from tables
EMP and DEPT (using cross join).
2. Write MySQL Query to Show the no. of employees of “AI” department
(using inner join).
3. Write MySQL Query to Show the details of EMP who belongs to
“KOLKATA” and works for “IT” department (using natural join).
4. Write MySQL Query to Show the minimum age from employees who
works in “CS” department (using cross join).
5. Write MySQL Query to Show maximum weight from employees who
work in “IT” department (using cross join).