PRACTICAL 16

#Queries Set 1 (Database Fetching records) [TABLE-MOVIE]

#FORMATION OF TABLE
mysql> /*Queries Set 1 (Database Fetching records) [TABLE-MOVIE]*/
mysql> CREATE DATABASE PRACTICALS;
Query OK, 1 row affected (0.10 sec)
mysql> USE PRACTICALS;
Database changed
mysql> CREATE TABLE MOVIE
-> (
-> MOVIEID CHAR(3) PRIMARY KEY,
-> MOVIENAME VARCHAR(20),
-> CATEGORY VARCHAR(15),
-> RELEASEDATE DATE,
-> PRODUCTIONCOST INT,
-> BUSINESSCOST INT);
Query OK, 0 rows affected (0.11 sec)
mysql> DESC MOVIE;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| MOVIEID | char(3) | NO | PRI | NULL | |
| MOVIENAME | varchar(20) | YES | | NULL | |
| CATEGORY | varchar(15) | YES | | NULL | |
| RELEASEDATE | date | YES | | NULL | |
| PRODUCTIONCOST | int | YES | | NULL | |
| BUSINESSCOST | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.07 sec)
mysql> INSERT INTO MOVIE VALUES
-> ('001','HINDI_MOVIE','MUSICAL','2018-04-23',124500,130000),
-> ('002','TAMIL_MOVIE','ACTION','2016-05-17',112000,118000),
-> ('003','ENGLISH_MOVIE','HORROR','2017-08-06',245000,360000),
-> ('004','BENGALI_MOVIE','ADVENTURE','2017-01-04',72000,100000),
-> ('005','TELUGU_MOVIE','ACTION',NULL,100000,NULL),
-> ('006','PUNJABI_MOVIE','COMEDY',NULL,30500,NULL);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0

a) Display all the information from the Movie table.

mysql> SELECT * FROM MOVIE;

+---------+---------------+-----------+-------------+----------------+--------------+

| MOVIEID | MOVIENAME | CATEGORY | RELEASEDATE | PRODUCTIONCOST | BUSINESSCOST |

+---------+---------------+-----------+-------------+----------------+--------------+

| 001 | HINDI_MOVIE | MUSICAL | 2018-04-23 | 124500 | 130000 |

| 002 | TAMIL_MOVIE | ACTION | 2016-05-17 | 112000 | 118000 |

| 003 | ENGLISH_MOVIE | HORROR | 2017-08-06 | 245000 | 360000 |

| 004 | BENGALI_MOVIE | ADVENTURE | 2017-01-04 | 72000 | 100000 |

| 005 | TELUGU_MOVIE | ACTION | NULL | 100000 | NULL |

| 006 | PUNJABI_MOVIE | COMEDY | NULL | 30500 | NULL |

+---------+---------------+-----------+-------------+----------------+--------------+

6 rows in set (0.04 sec)

b) List business done by the movies showing only MovieID, MovieName and Total_Earning. Total_ Earning to be
calculated as the sum of ProductionCost and BusinessCost
mysql> SELECT MOVIEID,MOVIENAME,PRODUCTIONCOST+IFNULL(BUSINESSCOST,0) AS
TOTAL_EARNING FROM MOVIE;
+---------+---------------+---------------+
| MOVIEID | MOVIENAME | TOTAL_EARNING |
+---------+---------------+---------------+
| 001 | HINDI_MOVIE | 254500 |
| 002 | TAMIL_MOVIE | 230000 |
| 003 | ENGLISH_MOVIE | 605000 |
| 004 | BENGALI_MOVIE | 172000 |
| 005 | TELUGU_MOVIE | 100000 |
| 006 | PUNJABI_MOVIE | 30500 |
+---------+---------------+---------------+
6 rows in set (0.00 sec)
(c )List the different categories of movies.
mysql> SELECT DISTINCT CATEGORY FROM MOVIE;
+-----------+
| CATEGORY |
+-----------+
| MUSICAL |
| ACTION |
| HORROR |
| ADVENTURE |
| COMEDY |
+-----------+
5 rows in set (0.04 sec)

d) Find the net profit of each movie showing its MovieID, MovieName and NetProfit. Net Profit is to be calculated
as the difference between Business Cost and Production Cost.
mysql> SELECT MOVIEID,MOVIENAME,BUSINESSCOST-PRODUCTIONCOST AS NET_PROFIT FROM MOVIE;

+---------+---------------+------------+

| MOVIEID | MOVIENAME | NET_PROFIT |

+---------+---------------+------------+

| 001 | HINDI_MOVIE | 5500 |

| 002 | TAMIL_MOVIE | 6000 |

| 003 | ENGLISH_MOVIE | 115000 |

| 004 | BENGALI_MOVIE | 28000 |

| 005 | TELUGU_MOVIE | NULL |

| 006 | PUNJABI_MOVIE | NULL |

+---------+---------------+------------+

6 rows in set (0.04 sec)

e) List MovieID, MovieName and Cost for all movies with ProductionCost greater than 10,000 and less than
1,00,000
mysql> SELECT MOVIEID,MOVIENAME,PRODUCTIONCOST FROM MOVIE WHERE PRODUCTIONCOST>10000
AND PRODUCTIONCOST<100000;

+---------+---------------+----------------+

| MOVIEID | MOVIENAME | PRODUCTIONCOST |

+---------+---------------+----------------+

| 004 | BENGALI_MOVIE | 72000 |

| 006 | PUNJABI_MOVIE | 30500 |

+---------+---------------+----------------+

2 rows in set (0.00 sec)

f) List details of all movies which fall in the category of comedy or action.
mysql> SELECT * FROM MOVIE WHERE CATEGORY IN('COMEDY','ACTION');

+---------+---------------+----------+-------------+----------------+--------------+

| MOVIEID | MOVIENAME | CATEGORY | RELEASEDATE | PRODUCTIONCOST | BUSINESSCOST |

+---------+---------------+----------+-------------+----------------+--------------+

| 002 | TAMIL_MOVIE | ACTION | 2016-05-17 | 112000 | 118000 |

| 005 | TELUGU_MOVIE | ACTION | NULL | 100000 | NULL |

| 006 | PUNJABI_MOVIE | COMEDY | NULL | 30500 | NULL |

+---------+---------------+----------+-------------+----------------+--------------+

3 rows in set (0.00 sec)

g) List details of all movies which have not been released yet
mysql> SELECT * FROM MOVIE WHERE RELEASEDATE IS NULL;

+---------+---------------+----------+-------------+----------------+--------------+

| MOVIEID | MOVIENAME | CATEGORY | RELEASEDATE | PRODUCTIONCOST | BUSINESSCOST |

+---------+---------------+----------+-------------+----------------+--------------+

| 005 | TELUGU_MOVIE | ACTION | NULL | 100000 | NULL |

| 006 | PUNJABI_MOVIE | COMEDY | NULL | 30500 | NULL |

+---------+---------------+----------+-------------+----------------+--------------+

2 rows in set (0.00 sec)

#PRACTICAL 17

#Queries Set 2 (Based on Aggregate Functions)[ TABLEPRODUCT] [(g) to (k)

#CREATION OF TABLE AND INPUT OF DATA
mysql> USE PRACTICALS;
Database changed
mysql> CREATE TABLE PRODUCT
-> (
-> PCODE CHAR(3) PRIMARY KEY,
-> PNAME VARCHAR(20),
-> UPRICE INT(3),
-> MANUFACTURER VARCHAR(20));
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> INSERT INTO PRODUCT VALUES
-> ('P01','WASHING POWDER',120,'SURF'),
-> ('P02','TOOTHPASTE',54,'COLGATE'),
-> ('P03','SOAP',25,'LUX'),
-> ('P04','TOOTHPASTE',65,'PEPSODENT'),
-> ('P05','SOAP',38,'DOVE'),
-> ('P06','SHAMPOO',245,'DOVE');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
#SHOWING TABLE DETAILS
mysql> SELECT * FROM PRODUCT;
+-------+----------------+--------+--------------+
| PCODE | PNAME | UPRICE | MANUFACTURER |
+-------+----------------+--------+--------------+
| P01 | WASHING POWDER | 120 | SURF |
| P02 | TOOTHPASTE | 54 | COLGATE |
| P03 | SOAP | 25 | LUX |
| P04 | TOOTHPASTE | 65 | PEPSODENT |
| P05 | SOAP | 38 | DOVE |
| P06 | SHAMPOO | 245 | DOVE |
+-------+----------------+--------+--------------+
6 rows in set (0.00 sec)
e) Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where
the UPrice is more than 100, otherwise the discount will be 0.
mysql> UPDATE PRODUCT SET DISCOUNT=UPRICE*0.1 WHERE UPRICE>100;

Query OK, 2 rows affected (0.01 sec)

Rows matched: 2 Changed: 2 Warnings: 0

#TABLE AFTER UPDATING VALUES

mysql> SELECT * FROM PRODUCT;

+-------+----------------+--------+--------------+----------+

| PCODE | PNAME | UPRICE | MANUFACTURER | DISCOUNT |

+-------+----------------+--------+--------------+----------+

| P01 | WASHING POWDER | 120 | SURF | 12.0 |

| P02 | TOOTHPASTE | 54 | COLGATE | NULL |

| P03 | SOAP | 25 | LUX | NULL |

| P04 | TOOTHPASTE | 65 | PEPSODENT | NULL |

| P05 | SOAP | 38 | DOVE | NULL |

| P06 | SHAMPOO | 245 | DOVE | 24.5 |

+-------+----------------+--------+--------------+----------+

6 rows in set (0.00 sec)

f) Increase the price by 12 per cent for all the products manufactured by Dove.
mysql> UPDATE PRODUCT SET UPRICE=UPRICE+0.12*UPRICE WHERE MANUFACTURER='DOVE';

Query OK, 2 rows affected (0.01 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> SELECT * FROM PRODUCT;

+-------+----------------+--------+--------------+----------+

| PCODE | PNAME | UPRICE | MANUFACTURER | DISCOUNT |

+-------+----------------+--------+--------------+----------+

| P01 | WASHING POWDER | 120 | SURF | 12.0 |

| P02 | TOOTHPASTE | 54 | COLGATE | NULL |

| P03 | SOAP | 25 | LUX | NULL |

| P04 | TOOTHPASTE | 65 | PEPSODENT | NULL |

| P05 | SOAP | 43 | DOVE | NULL |

| P06 | SHAMPOO | 274 | DOVE | 24.5 |

+-------+----------------+--------+--------------+----------+

6 rows in set (0.00 sec)

g) Display the total number of products manufactured by each manufacturer.

mysql> SELECT MANUFACTURER,COUNT(MANUFACTURER) FROM PRODUCT GROUP BY MANUFACTURER;

+--------------+---------------------+

| MANUFACTURER | COUNT(MANUFACTURER) |

+--------------+---------------------+

| SURF | 1 |

| COLGATE | 1 |

| LUX | 1 |

| PEPSODENT | 1 |

| DOVE | 2 |

+--------------+---------------------+

5 rows in set (0.01 sec)

#PRACTICAL 18

#Queries Set 3 (DDL Commands) [TABLE -PRODUCT] [(a) and (d)]

#CREATION OF TABLE AND INPUT OF DATA

a) Create the table Product with appropriate data types and constraints.
mysql> USE PRACTICALS;
Database changed
mysql> CREATE TABLE PRODUCT
-> (
-> PCODE CHAR(3) PRIMARY KEY,
-> PNAME VARCHAR(20),
-> UPRICE INT(3),
-> MANUFACTURER VARCHAR(20));
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> INSERT INTO PRODUCT VALUES
-> ('P01','WASHING POWDER',120,'SURF'),
-> ('P02','TOOTHPASTE',54,'COLGATE'),
-> ('P03','SOAP',25,'LUX'),
-> ('P04','TOOTHPASTE',65,'PEPSODENT'),
-> ('P05','SOAP',38,'DOVE'),
-> ('P06','SHAMPOO',245,'DOVE');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
#SHOWING TABLE DETAILS
mysql> SELECT * FROM PRODUCT;
+-------+----------------+--------+--------------+
| PCODE | PNAME | UPRICE | MANUFACTURER |
+-------+----------------+--------+--------------+
| P01 | WASHING POWDER | 120 | SURF |
| P02 | TOOTHPASTE | 54 | COLGATE |
| P03 | SOAP | 25 | LUX |
| P04 | TOOTHPASTE | 65 | PEPSODENT |
| P05 | SOAP | 38 | DOVE |
| P06 | SHAMPOO | 245 | DOVE |
+-------+----------------+--------+--------------+
6 rows in set (0.00 sec)
C) List the Product Code, Product name and price in descending order of their
product name. If PName is the same, then display the data in ascending order of
price.
mysql> SELECT PCODE,PNAME,UPRICE FROM PRODUCT ORDER BY PNAME DESC,UPRICE ASC;
+-------+----------------+--------+
| PCODE | PNAME | UPRICE |
+-------+----------------+--------+
| P01 | WASHING POWDER | 120 |
| P02 | TOOTHPASTE | 54 |
| P04 | TOOTHPASTE | 65 |
| P03 | SOAP | 25 |
| P05 | SOAP | 38 |
| P06 | SHAMPOO | 245 |
+-------+----------------+--------+
6 rows in set (0.00 sec)

d) Add a new column Discount to the table Product

mysql> ALTER TABLE PRODUCT ADD DISCOUNT FLOAT(3,1);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> SELECT * FROM PRODUCT;
#TABLE AFTER ADDING DISCOUNT COLUMN
+-------+----------------+--------+--------------+----------+
| PCODE | PNAME | UPRICE | MANUFACTURER | DISCOUNT |
+-------+----------------+--------+--------------+----------+
| P01 | WASHING POWDER | 120 | SURF | NULL |
| P02 | TOOTHPASTE | 54 | COLGATE | NULL |
| P03 | SOAP | 25 | LUX | NULL |
| P04 | TOOTHPASTE | 65 | PEPSODENT | NULL |
| P05 | SOAP | 38 | DOVE | NULL |
| P06 | SHAMPOO | 245 | DOVE | NULL |
+-------+----------------+--------+--------------+----------+
6 rows in set (0.00 sec)