MYSQL PRACTICALS FOR CLASS XII - SECOND TERM -2022

MYSQL PRACTICALS

SECOND TERM - 2022

CLASS - XII

======================================

EXPERIMENT - 11

Objectives: Consider the following DEPT and WORKER tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii):

Table: DEPT

Table: WORKER

Note: DOJ refers to date of Joining and DOB refers to date of Birth of workers.
Questions:

(i) To display Wno, Name, Gender from the table Worker in descending order of Wno.

(ii) To display the Name of all the Female workers from the table Worker.

(iii) To display the Wno and Name of those workers from the table Worker who are born between ‘1987-01-01’ and ‘1991-12-01’.

(iv) To count and display Male workers who have joined after ‘1986-01-01’.

(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVEING COUNT(*) > 1 ;

(vi) SELECT DISTINCT DEPARTMENT FROM DEPT ;

(vii) SELECT NAME, DEPARTMENT, LOCATION FROM WORKER W, DEPT D WHERE W.DCODE = D.DCODE AND WNO < 1003 ;

(viii) SELECT MAX(DOJ), MIN(DOB) FROM WORKER ;

Solution:

(i) SELECT WNO, NAME, GENDER FORM WORKER ORDER BY WNO DESC ;

(ii) SELECT NAME FROM WORKER WHERE GENDER = 'FEMALE' ;

(iii) SELECT WHO, NAME FROM WORKER WHERE DOB BETWEEN '1987-01-01' AND '1991-12-01' ;

OR

SELECT WNO, NAME FROM WORKER WHERE DOB>='1987-01-01' AND DOB <= '1991-12-01' ;

(iv) SELECT COUNT(*) FROM WORKER WHERE GENDER = 'MALE' AND DOJ > '1986-01-01' ;

(v) 

(vi)

(vii)

(viii)

EXPERIMENT - 12

Objectives: Consider the following ACTIVITY and COACH. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii):

Table: ACTIVITY

Table: COACH

Questions
(i) To display the names of all activities with their Acodes in descending order.

(ii) To display sum of PrizeMoney for the Activities played in each of the Stadium Separately.

(iii) To display the coach's name and Acodes in ascending order of Acode from the table COACH.

(iv) To display the content of all activities for which ScheduleDate is earlier than 01-01-2004 in ascending order of participantsNum.

(v) SELECT COUNT(DISTINCT PARTICIPANTSNUM) FROM ACTIVITY ;

(vi) SELECT MAX(SCHEDULEDATE), MIN(SCHEDULEDATE) FROM ACTIVITY ;

(vii) SELECT NAME, ACTIVITYNAME FROM ACTIVITY A, COACH C WHERE A.ACODE=C.A.CODE AND A.PARTICIPANTSNUM = 10 ;

(viii) SELECT DISTINCT PARTICIPANTSNUM FROM ACTIVITY ;

Solution:

(i) SELECT ACODE, ACTIVITYNAME FROM ACTIVITY ORDER BY ACODE DESC ;

(ii) SELECT STADIUM, SUM(PRIZEMONEY) FROM ACTIVITY GROUP BY STADIUM ;

(iii) SELECT NAME, ACODE FROM COACH ORDER BY ACODE ;

(iv) SELECT * FROM ACTIVITY WHERE SCHEDULEDATE < '2004-01-01' ORDER BY PARTICIPANTSNUM ;

(v) 

(vi)

(vii)

(viii)

EXPERIMENT - 13

Objectives: Consider the following  table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv) and output for (v).

Table: FITNESS

Questions

(i) To display the names of all the products with price more than 20000.

(ii) To display the names of all products by the manufacturer 'Aone'.

(iii) To change the price data of all the products by applying 25% discount reduction.

(iv) To add new row for product with the details:

             'P7', 'Vibro Exerciser', 28000, 'Aone'.

(v) SELECT * FROM FITNESS WHERE MANUFACTURER LIKE '%E' ;

Solution

(i) SELECT PNAME FORM FITNESS WHERE PRICE > 20000 ;

(ii) SELECT PNAME FROM FITNESS WHERE MANUFACTURER = 'AONE' ;

(iii) UPDATE FITNESS SET PRICE = PRICE - PRICE * 0.25 ;

(iv) INSERT INTO FITNESS VALUES ('P7', 'VIBRO EXERCISER' 28000, 'AONE') ;

(v) 

EXPERIMENT - 14

Objectives: Consider the  table INTERIORS. Write commands in MySQL for (i) to (iii) and output for (iv) to (vi).

Table: INTERIORS

Questions:

(i) To list the ItemName whose Type is Office Table.

(ii) To display a report listing ItemName, Type and Price in descending order of Price.

(iii) To count the number of ItemName.

(iv) SELECT ITEMNAME, TYPE FROM INTERIORS WHERE ITEMNAME LIKE 'R%' ;

(v) SELECT COUNT(DISTINCT TYPE) FROM INTERIORS ;

(vi) SELECT ITEMNAME, TYPE FROM INTERIORS WHERE DATEOFSTOCK > '2010/11/15' ;

Solutions

(i) SELECT ITEM NAME FROM INTERIORS WHERE TYPE = 'OFFICE TABLE' ;

(ii) SELECT ITEMNAME, TYPE, PRICE FROM INTERIORS ORDER BY PRICE DESC ;

(iii) SELECT COUNT(*) FROM INTERIORS ;

(iv) 

(v)

(vi)

EXPERIMENT - 15

Objectives: Write SQL commands for the following queries based on the relation Teacher given below:

Table: TEACHER

Questions

(i) To show all information about the teacher of Computer department.

(ii) To list the names of female teachers who are in Maths department.

(iii) To list the names of all teachers with their date of joining in ascending order.

(iv) To display teacher's name, salary, age for male teachers only.

(v) To count the number of teacher with Age > 23.

Solution

(i) SELECT * FROM TEACHER WHERE DEPARTMENT = 'COMPUTER' ;

(ii) SELECT NAME FROM TEACHER WHERE DEPARTMENT = 'MATH' AND GENDER = 'F' ;

(iii) SELECT NAME FROM TEACHER ORDER BY DATE_OF_JOIN ;

(iv) SELECT NAME, SALARY, AGE FROM TEACHER WHERE GENDER = 'M' ;

(v) SELECT COUNT(*) FROM TEACHER WHERE AGE > 23 ;

=============================