1.

Write SQL commands for the following on the basis of

the given table STUDENT.
+------------+-------+---------+------------+--------+-------------+--------+
| STUDENT_NO | CLASS | NAME | GAME | GRADE1 | SUPW | GRADE2 |
+------------+-------+---------+------------+--------+-------------+--------+
| 10 | 7 | SAMEER | CRICKET | B | PHOTOGRAPHY | A |
| 11 | 8 | SUJIT | TENNIS | A | GARDENING | C |
| 12 | 7 | KAMAL | SWIMMING | B | PHOTOGRAPHY | B |
| 13 | 7 | VEENA | TENNIS | C | COOKING | A |
| 14 | 9 | ARCHANA | BASKETBALL | A | LITERATURE | A |
| 15 | 10 | ARPIT | CRICKET | A | GARDENING | C |
+------------+-------+---------+------------+--------+-------------+--------+

i. Display the names of the students who are getting a grade ‘C’ in
either GAME or SUPW.
SELECT NAME FROM STUDENT WHERE GRADE1="C" OR GRADE2="C";
+-------+
| NAME |
+-------+
| SUJIT |
| VEENA |
| ARPIT |
+-------+

ii. Display the different games offered in the school.

SELECT DISTINCT GAME FROM STUDENT;
+------------+
| GAME |
+------------+
| CRICKET |
| TENNIS |
| SWIMMING |
| BASKETBALL |
+------------+

iii. Display the SUPW taken up by the students whose name starts
with ‘A’.
SELECT SUPW FROM STUDENT WHERE NAME LIKE "A%";
+------------+
| SUPW |
+------------+
| LITERATURE |
| GARDENING |
+------------+
2. Write SQL commands for the following on the basis of
the given table SPORTS.
+------------+-------+---------+------------+--------+-----------+--------+
| STUDENT_NO | CLASS | NAME | GAME1 | GRADE1 | GAME2 | GRADE2 |
+------------+-------+---------+------------+--------+-----------+--------+
| 10 | 7 | SAMEER | CRICKET | B | SWIMMING | A |
| 11 | 8 | SUJIT | TENNIS | A | SKATING | C |
| 12 | 7 | KAMAL | SWIMMING | B | FOOTBALL | B |
| 13 | 7 | VEENA | TENNIS | C | TENNIS | A |
| 14 | 9 | ARCHANA | BASKETBALL | A | CRICKET | A |
| 15 | 10 | ARPIT | CRICKET | A | ATHLETICS | C |
+------------+-------+---------+------------+--------+-----------+--------+

i. Display the names of the students who have grade ‘C’ in either
Game1 or Game2.
SELECT NAME FROM SPORTS WHERE GRADE1="C" OR GRADE2="C";
+-------+
| NAME |
+-------+
| SUJIT |
| VEENA |
| ARPIT |
+-------+

ii. Display the names of the students who have same game for both
Game1 and Game2.
SELECT NAME FROM SPORTS WHERE GRADE1=GRADE2;
+---------+
| NAME |
+---------+
| KAMAL |
| ARCHANA |
+---------+

iii. Display the games taken up by the students whose name starts
with A.
SELECT NAME,GAME1,GAME2 FROM SPORTS WHERE GAME1=GAME2;
+-------+--------+--------+
| NAME | GAME1 | GAME2 |
+-------+--------+--------+
| VEENA | TENNIS | TENNIS |
+-------+--------+--------+
3. Write SQL commands for the following on the basis of
the given table CLUB.
+----------+-----------+------+------------+-------------+------+------+
| COACH_ID | COACHNAME | AGE | SPORTS | DATE_OF_APP | PAY | SEX |
+----------+-----------+------+------------+-------------+------+------+
| 1 | KUKREJA | 35 | KARATE | 1996-03-27 | 1000 | M |
| 2 | RAVINA | 34 | KARATE | 1998-01-20 | 1200 | F |
| 3 | KARAN | 34 | SQUASH | 1998-02-19 | 2000 | M |
| 4 | TARUN | 33 | BASKETBALL | 1998-01-01 | 1500 | M |
| 5 | ZUBIN | 36 | SWIMMING | 1998-01-12 | 750 | M |
| 6 | KETAKI | 36 | SWIMMING | 1998-02-24 | 800 | F |
| 7 | ANKITA | 39 | SQUASH | 1998-02-20 | 2200 | F |
| 8 | ZAREEN | 37 | KARATE | 1998-02-22 | 1100 | F |
| 9 | KUSH | 41 | SWIMMING | 1998-01-13 | 900 | M |
| 10 | SHAILYA | 37 | BASKETBALL | 1998-02-19 | 1700 | M |
+----------+-----------+------+------------+-------------+------+------+

i. To show all information about the swimming coaches in the club.

SELECT *FROM CLUB WHERE SPORTS="SWIMMING";
+----------+-----------+------+----------+-------------+------+------+
| COACH_ID | COACHNAME | AGE | SPORTS | DATE_OF_APP | PAY | SEX |
+----------+-----------+------+----------+-------------+------+------+
| 5 | ZUBIN | 36 | SWIMMING | 1998-01-12 | 750 | M |
| 6 | KETAKI | 36 | SWIMMING | 1998-02-24 | 800 | F |
| 9 | KUSH | 41 | SWIMMING | 1998-01-13 | 900 | M |
+----------+-----------+------+----------+-------------+------+------+

ii. To list names of all coaches with their date of appointment in

descending order.
SELECT COACHNAME,DATE_OF_APP FROM CLUB ORDER BY DATE_OF_APP
DESC;
+-----------+-------------+
| COACHNAME | DATE_OF_APP |
+-----------+-------------+
| KETAKI | 1998-02-24 |
| ZAREEN | 1998-02-22 |
| ANKITA | 1998-02-20 |
| KARAN | 1998-02-19 |
| SHAILYA | 1998-02-19 |
| RAVINA | 1998-01-20 |
| KUSH | 1998-01-13 |
| ZUBIN | 1998-01-12 |
| TARUN | 1998-01-01 |
| KUKREJA | 1996-03-27 |
+-----------+-------------+

iii. To display a report, showing coachname, pay, age and bonus

(15% of pay) for all the coaches.
SELECT COACHNAME,PAY,AGE,0.15*PAY AS BONUS FROM CLUB;
+-----------+------+------+--------+
| COACHNAME | PAY | AGE | BONUS |
+-----------+------+------+--------+
| KUKREJA | 1000 | 35 | 150.00 |
| RAVINA | 1200 | 34 | 180.00 |
| KARAN | 2000 | 34 | 300.00 |
| TARUN | 1500 | 33 | 225.00 |
| ZUBIN | 750 | 36 | 112.50 |
| KETAKI | 800 | 36 | 120.00 |
| ANKITA | 2200 | 39 | 330.00 |
| ZAREEN | 1100 | 37 | 165.00 |
| KUSH | 900 | 41 | 135.00 |
| SHAILYA | 1700 | 37 | 255.00 |
+-----------+------+------+--------+
4. Write SQL commands for the following on the basis of
the given table STUDENT1.
+------+---------+---------+------------+----------+-------+-------+
| NO | NAME | STIPEND | STREAM | AVG_MARK | GRADE | CLASS |
+------+---------+---------+------------+----------+-------+-------+
| 1 | KARAN | 400 | MEDICAL | 78.5 | B | 12B |
| 2 | DIVAKAR | 450 | COMMERCE | 89.2 | A | 11C |
| 3 | DIVYA | 300 | COMMERCE | 68.6 | C | 12C |
| 4 | ARUN | 350 | HUMANITIES | 73.1 | B | 12C |
| 5 | SABINA | 500 | NONMEDICAL | 90.6 | A | 11A |
| 6 | JOHN | 400 | MEDICAL | 75.4 | B | 12B |
| 7 | ROBERT | 250 | HUMANITIES | 64.4 | C | 11A |
| 8 | RUBINA | 450 | NONMEDICAL | 88.5 | A | 12A |
| 9 | VIKAS | 500 | NONMEDICAL | 92.0 | A | 12A |
| 10 | MOHAN | 300 | COMMERCE | 67.5 | C | 12C |
+------+---------+---------+------------+----------+-------+-------+

i. Select all the non medical stream students from STUDENT1.

SELECT NAME FROM STUDENT1 WHERE STREAM="NONMEDICAL";
+--------+
| NAME |
+--------+
| SABINA |
| RUBINA |
| VIKAS |
+--------+

ii. List the names of those students who are in class 12 sorted by
Stipend.
SELECT NAME FROM STUDENT1 WHERE CLASS LIKE "12%" ORDER BY
STIPEND;
+--------+
| NAME |
+--------+
| DIVYA |
| MOHAN |
| ARUN |
| KARAN |
| JOHN |
| RUBINA |
| VIKAS |
+--------+

iii. List all students sorted by Average marks in descending order.

SELECT *FROM STUDENT1 ORDER BY AVG_MARK DESC;
+------+---------+---------+------------+----------+-------+-------+
| NO | NAME | STIPEND | STREAM | AVG_MARK | GRADE | CLASS |
+------+---------+---------+------------+----------+-------+-------+
| 9 | VIKAS | 500 | NONMEDICAL | 92.0 | A | 12A |
| 5 | SABINA | 500 | NONMEDICAL | 90.6 | A | 11A |
| 2 | DIVAKAR | 450 | COMMERCE | 89.2 | A | 11C |
| 8 | RUBINA | 450 | NONMEDICAL | 88.5 | A | 12A |
| 1 | KARAN | 400 | MEDICAL | 78.5 | B | 12B |
| 6 | JOHN | 400 | MEDICAL | 75.4 | B | 12B |
| 4 | ARUN | 350 | HUMANITIES | 73.1 | B | 12C |
| 3 | DIVYA | 300 | COMMERCE | 68.6 | C | 12C |
| 10 | MOHAN | 300 | COMMERCE | 67.5 | C | 12C |
| 7 | ROBERT | 250 | HUMANITIES | 64.4 | C | 11A |
+------+---------+---------+------------+----------+-------+-------+

iv. Display a report listing Name, Stipend, Stream and amount of

stipend paid in a year, assuming that the stipend is paid every
month.
SELECT NAME,STIPEND,STREAM,STIPEND*12 AS ANNUAL_STIPEND FROM
STUDENT1;
+---------+---------+------------+----------------+
| NAME | STIPEND | STREAM | ANNUAL_STIPEND |
+---------+---------+------------+----------------+
| KARAN | 400 | MEDICAL | 4800 |
| DIVAKAR | 450 | COMMERCE | 5400 |
| DIVYA | 300 | COMMERCE | 3600 |
| ARUN | 350 | HUMANITIES | 4200 |
| SABINA | 500 | NONMEDICAL | 6000 |
| JOHN | 400 | MEDICAL | 4800 |
| ROBERT | 250 | HUMANITIES | 3000 |
| RUBINA | 450 | NONMEDICAL | 5400 |
| VIKAS | 500 | NONMEDICAL | 6000 |
| MOHAN | 300 | COMMERCE | 3600 |
+---------+---------+------------+----------------+

v. SELECT TRUNCATE(AVG_MARK,1) FROM STUDENT1 WHERE

AVG_MARK<75;
+----------------------+
| TRUNCATE(AVG_MARK,1) |
+----------------------+
| 68.6 |
| 73.1 |
| 64.4 |
| 67.5 |
+----------------------+

vi. SELECT ROUND(AVG_MARK) FROM STUDENT1 WHERE

GRADE=”B”;
+-----------------+
| ROUND(AVG_MARK) |
+-----------------+
| 79 |
| 73 |
| 75 |
+-----------------+
vii. SELECT CONCAT(NAME,STREAM) FROM STUDENT1 WHERE
CLASS=”12A”;
+---------------------+
| CONCAT(NAME,STREAM) |
+---------------------+
| RUBINANONMEDICAL |
| VIKASNONMEDICAL |
+---------------------+

viii. SELECT RIGHT(STREAM,2) FROM STUDENT1;

+-----------------+
| RIGHT(STREAM,2) |
+-----------------+
| AL |
| CE |
| CE |
| ES |
| AL |
| AL |
| ES |
| AL |
| AL |
| CE |
+-----------------+
5. Write SQL commands for the following on the basis of
the given table STUDENT.
+------+---------+---------+------------+----------+-------+-------+
| NO | NAME | STIPEND | STREAM | AVG_MARK | GRADE | CLASS |
+------+---------+---------+------------+----------+-------+-------+
| 1 | KARAN | 400.00 | MEDICAL | 78.5 | B | 12B |
| 2 | DIVAKAR | 450.00 | COMMERCE | 89.2 | A | 11C |
| 3 | DIVYA | 300.00 | COMMERCE | 68.6 | C | 12C |
| 4 | ARUN | 350.00 | HUMANITIES | 73.1 | B | 12C |
| 5 | SABINA | 500.00 | NONMEDICAL | 90.6 | A | 11A |
| 6 | JOHN | 400.00 | MEDICAL | 75.4 | B | 12B |
| 7 | ROBERT | 250.00 | HUMANITIES | 64.4 | C | 11A |
| 8 | RUBINA | 450.00 | NONMEDICAL | 88.5 | A | 12A |
| 9 | VIKAS | 500.00 | NONMEDICAL | 92.0 | A | 12A |
| 10 | MOHAN | 300.00 | COMMERCE | 67.5 | C | 12C |
+------+---------+---------+------------+----------+-------+-------+

i. SELECT MIN(AVG_MARK) FROM STUDENT WHERE AVG_MARK<75;

+---------------+
| MIN(AVG_MARK) |
+---------------+
| 64.4 |
+---------------+

ii. SELECT SUM(STIPEND) FROM STUDENT WHERE GRADE=”B”;

+--------------+
| SUM(STIPEND) |
+--------------+
| 1150.00 |
+--------------+

iii. SELECT AVG(STIPEND) FROM STUDENT WHERE CLASS=”12A”;

+--------------+
| AVG(STIPEND) |
+--------------+
| 475.000000 |
+--------------+

iv. SELECT COUNT(DISTINCT STREAM) FROM STUDENT;

+------------------------+
| COUNT(DISTINCT STREAM) |
+------------------------+
| 4 |
+------------------------+
6. Write SQL commands for the following on the basis of
the given table MOV.
+------+--------------------+--------+--------+---------+------+-------+
| NO | TITLE | TYPE | RATING | STARS | QTY | PRICE |
+------+--------------------+--------+--------+---------+------+-------+
| 1 | Gone with the Wind | Drama | G | Gable | 4 | 39.95 |
| 2 | Friday the 13th | Horror | R | Jason | 2 | 69.95 |
| 3 | Top Gun | Drama | PG | Cruise | 7 | 49.95 |
| 4 | Splash | Comedy | PG13 | Hanks | 3 | 29.95 |
| 5 | Independence Day | Drama | R | Turner | 3 | 19.95 |
| 6 | Risky Business | Comedy | R | Cruise | 2 | 44.95 |
| 7 | Cocoon | Scifi | PG | Ameche | 2 | 31.95 |
| 8 | Crocodile Dundee | Comedy | PG13 | Harris | 2 | 69.95 |
| 9 | 101 Dalmantians | Comedy | G | | 3 | 59.95 |
| 10 | Tootsie | Comedy | PG | Hoffman | 1 | 29.95 |
+------+--------------------+--------+--------+---------+------+-------+

i. Display a list of all movies with price over 20 and sorted by price.
SELECT *FROM MOV WHERE PRICE>20 ORDER BY PRICE;
+------+--------------------+--------+--------+---------+------+-------+
| NO | TITLE | TYPE | RATING | STARS | QTY | PRICE |
+------+--------------------+--------+--------+---------+------+-------+
| 4 | Splash | Comedy | PG13 | Hanks | 3 | 29.95 |
| 10 | Tootsie | Comedy | PG | Hoffman | 1 | 29.95 |
| 7 | Cocoon | Scifi | PG | Ameche | 2 | 31.95 |
| 1 | Gone with the Wind | Drama | G | Gable | 4 | 39.95 |
| 6 | Risky Business | Comedy | R | Cruise | 2 | 44.95 |
| 3 | Top Gun | Drama | PG | Cruise | 7 | 49.95 |
| 9 | 101 Dalmantians | Comedy | G | | 3 | 59.95 |
| 2 | Friday the 13th | Horror | R | Jason | 2 | 69.95 |
| 8 | Crocodile Dundee | Comedy | PG13 | Harris | 2 | 69.95 |
+------+--------------------+--------+--------+---------+------+-------+

ii. Display all the movies sorted by QTY in decreasing order.

SELECT TITLE,QTY FROM MOV ORDER BY QTY DESC;
+--------------------+------+
| TITLE | QTY |
+--------------------+------+
| Top Gun | 7 |
| Gone with the Wind | 4 |
| Splash | 3 |
| Independence Day | 3 |
| 101 Dalmantians | 3 |
| Friday the 13th | 2 |
| Risky Business | 2 |
| Cocoon | 2 |
| Crocodile Dundee | 2 |
| Tootsie | 1 |
+--------------------+------+
iii. Display a report listing movie number, current value and
replacement value of each movie in the table. Calculate the
replacement value of all the movies as: QTY*Price*1.15.
SELECT NO,QTY*PRICE AS CURRENT_VALUE,QTY*PRICE*1.15 AS
REPLACEMENT_VALUE FROM MOV;
+------+---------------+-------------------+
| NO | CURRENT_VALUE | REPLACEMENT_VALUE |
+------+---------------+-------------------+
| 1 | 159.80 | 183.7700 |
| 2 | 139.90 | 160.8850 |
| 3 | 349.65 | 402.0975 |
| 4 | 89.85 | 103.3275 |
| 5 | 59.85 | 68.8275 |
| 6 | 89.90 | 103.3850 |
| 7 | 63.90 | 73.4850 |
| 8 | 139.90 | 160.8850 |
| 9 | 179.85 | 206.8275 |
| 10 | 29.95 | 34.4425 |
+------+---------------+-------------------+
7. Write SQL commands for the following on the basis of
the given table TEACHER.
+------+----------+------+------------+--------------+--------+------+
| NO | NAME | AGE | DEPARTMENT | DATE_OF_JOIN | SALARY | SEX |
+------+----------+------+------------+--------------+--------+------+
| 1 | JUGAL | 34 | COMPUTER | 1997-01-10 | 12000 | M |
| 2 | SHARMILA | 31 | HISTORY | 1998-03-24 | 20000 | F |
| 3 | SANDEEP | 32 | MATHS | 1996-12-12 | 30000 | M |
| 4 | SANGEETA | 35 | HISTORY | 1999-07-01 | 40000 | F |
| 5 | RAKESH | 42 | MATHS | 1997-09-05 | 25000 | M |
| 6 | SHYAM | 50 | HISTORY | 1998-06-27 | 30000 | M |
| 7 | SHIV OM | 44 | COMPUTER | 1997-02-25 | 21000 | M |
| 8 | SHALAKHA | 33 | MATHS | 1997-07-31 | 20000 | F |
+------+----------+------+------------+--------------+--------+------+

i. To show all the information of the teachers of the history

department.
SELECT *FROM TEACHER WHERE DEPARTMENT=”HISTORY”
+------+----------+------+------------+--------------+--------+------+
| NO | NAME | AGE | DEPARTMENT | DATE_OF_JOIN | SALARY | SEX |
+------+----------+------+------------+--------------+--------+------+
| 2 | SHARMILA | 31 | HISTORY | 1998-03-24 | 20000 | F |
| 4 | SANGEETA | 35 | HISTORY | 1999-07-01 | 40000 | F |
| 6 | SHYAM | 50 | HISTORY | 1998-06-27 | 30000 | M |
+------+----------+------+------------+--------------+--------+------+

ii. To list the names of female teachers who are in History

department.
SELECT NAME FROM TEACHER WHERE DEPARTMENT=”HISTORY” AND
SEX=”F”;
+----------+
| NAME |
+----------+
| SHARMILA |
| SANGEETA |
+----------+

iii. To list the names of all teachers with their date of joining in
ascending order.
SELECT NAME,DATE_OF_JOIN FROM TEACHER ORDER BY DATE_OF_JOIN;
+----------+--------------+
| NAME | DATE_OF_JOIN |
+----------+--------------+
| SANDEEP | 1996-12-12 |
| JUGAL | 1997-01-10 |
| SHIV OM | 1997-02-25 |
| SHALAKHA | 1997-07-31 |
| RAKESH | 1997-09-05 |
| SHARMILA | 1998-03-24 |
| SHYAM | 1998-06-27 |
| SANGEETA | 1999-07-01 |
+----------+--------------+