Question

All the question should be solved using IF statement
Q1) List the Full Name, the city, and the state of all members, and order them by their first name in descending order (Z-A) if they are from 'KY' or by their last name in ascending order (A-Z) when they are from 'TN' state.

Q2) List the total number of movies for each genre (FAMILY, ACTION, DRAMA, COMEDY).

- Comment the following two lines if creating database in Mimir or Bluenose
create schema if not exists DVD_vidrental;
use DVD_vidrental;

DROP TABLE IF EXISTS `detailrental`;
DROP TABLE IF EXISTS `rental`;
DROP TABLE IF EXISTS `video`;
DROP TABLE IF EXISTS `movie`;
DROP TABLE IF EXISTS `price`;
DROP TABLE IF EXISTS `membership`;

CREATE TABLE `membership` (
`MEM_NUM` decimal(8,0) NOT NULL,
`MEM_FNAME` varchar(30) NOT NULL,
`MEM_LNAME` varchar(30) NOT NULL,
`MEM_STREET` varchar(120) DEFAULT NULL,
`MEM_CITY` varchar(50) DEFAULT NULL,
`MEM_STATE` char(2) DEFAULT NULL,
`MEM_ZIP` char(5) DEFAULT NULL,
`MEM_BALANCE` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`MEM_NUM`)
);

CREATE TABLE `price` (
`PRICE_CODE` decimal(2,0) NOT NULL,
`PRICE_DESCRIPTION` varchar(20) NOT NULL,
`PRICE_RENTFEE` decimal(5,2) DEFAULT NULL,
`PRICE_DAILYLATEFEE` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`PRICE_CODE`)
);

CREATE TABLE `movie` (
`MOVIE_NUM` decimal(8,0) NOT NULL,
`MOVIE_TITLE` varchar(75) NOT NULL,
`MOVIE_YEAR` decimal(4,0) DEFAULT NULL,
`MOVIE_COST` decimal(5,2) DEFAULT NULL,
`MOVIE_GENRE` varchar(50) DEFAULT NULL,
`PRICE_CODE` decimal(2,0) DEFAULT NULL,
PRIMARY KEY (`MOVIE_NUM`),
KEY `PRICE_CODE` (`PRICE_CODE`),
CONSTRAINT `movie_ibfk_1` FOREIGN KEY (`PRICE_CODE`) REFERENCES `price` (`PRICE_CODE`)
);

CREATE TABLE `video` (
`VID_NUM` decimal(8,0) NOT NULL,
`VID_INDATE` date DEFAULT NULL,
`MOVIE_NUM` decimal(8,0) DEFAULT NULL,
PRIMARY KEY (`VID_NUM`),
KEY `MOVIE_NUM` (`MOVIE_NUM`),
CONSTRAINT `video_ibfk_1` FOREIGN KEY (`MOVIE_NUM`) REFERENCES `movie` (`MOVIE_NUM`)
);

CREATE TABLE `rental` (
`RENT_NUM` decimal(8,0) NOT NULL,
`RENT_DATE` date DEFAULT NULL,
`MEM_NUM` decimal(8,0) DEFAULT NULL,
PRIMARY KEY (`RENT_NUM`),
KEY `MEM_NUM` (`MEM_NUM`),
CONSTRAINT `rental_ibfk_1` FOREIGN KEY (`MEM_NUM`) REFERENCES `membership` (`MEM_NUM`)
);

CREATE TABLE `detailrental` (
`RENT_NUM` decimal(8,0) NOT NULL,
`VID_NUM` decimal(8,0) NOT NULL,
`DETAIL_FEE` decimal(5,2) DEFAULT NULL,
`DETAIL_DUEDATE` date DEFAULT NULL,
`DETAIL_RETURNDATE` date DEFAULT NULL,
`DETAIL_DAILYLATEFEE` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`RENT_NUM`,`VID_NUM`),
KEY `VID_NUM` (`VID_NUM`),
CONSTRAINT `detailrental_ibfk_1` FOREIGN KEY (`RENT_NUM`) REFERENCES `rental` (`RENT_NUM`),
CONSTRAINT `detailrental_ibfk_2` FOREIGN KEY (`VID_NUM`) REFERENCES `video` (`VID_NUM`)
);

START TRANSACTION;

INSERT INTO `membership` VALUES (102,'TAMI','DAWSON','2632 TAKLI CIRCLE','NORENE','TN','37136',11.00),
(103,'CURT','KNIGHT','4025 CORNELL COURT','FLATGAP','KY','41219',6.00),
(104,'JAMAL','MELENDEZ','788 EAST 145TH AVENUE','QUEBECK','TN','38579',0.00),
(105,'IVA','MCCLAIN','6045 MUSKET BALL CIRCLE','SUMMIT','KY','42783',15.00),
(106,'MIRANDA','PARKS','4469 MAXWELL PLACE','GERMANTOWN','TN','38183',0.00),
(107,'ROSARIO','ELLIOTT','7578 DANNER AVENUE','COLUMBIA','TN','38402',5.00),
(108,'MATTIE','GUY','4390 EVERGREEN STREET','LILY','KY','40740',0.00),
(109,'CLINT','OCHOA','1711 ELM STREET','GREENEVILLE','TN','37745',10.00),
(110,'LEWIS','ROSALES','4524 SOUTHWIND CIRCLE','COUNCE','TN','38326',0.00),
(111,'STACY','MANN','2789 EAST COOK AVENUE','MURFREESBORO','TN','37132',8.00),
(112,'LUIS','TRUJILLO','7267 MELVIN AVENUE','HEISKELL','TN','37754',3.00),
(113,'MINNIE','GONZALES','6430 VASILI DRIVE','WILLISTON','TN','38076',0.00);

INSERT INTO `price` VALUES (1,'Standard',3.00,1.00),
(2,'New Release',4.50,3.00),(3,'Discount',2.50,1.00),
(4,'Weekly Special',2.00,0.50);
INSERT INTO `movie` VALUES (1234,'The Cesar Family Christmas',2014,39.95,'FAMILY',2),
(1235,'Smokey Mountain Wildlife',2011,59.95,'ACTION',3),(1236,'Richard Goodhope',2015,59.95,'DRAMA',2),
(1237,'Beatnik Fever',2014,29.95,'COMEDY',2),(1238,'Constant Companion',2015,89.95,'DRAMA',NULL),
(1239,'Where Hope Dies',2005,25.49,'DRAMA',3),(1245,'Time to Burn',2015,45.49,'ACTION',3),
(1246,'What He Doesn\'t Know',2013,58.29,'COMEDY',1);
INSERT INTO `video` VALUES (34341,'2014-01-22',1235),(34342,'2014-01-22',1235),
(34366,'2016-03-02',1236),(34367,'2016-03-02',1236),(34368,'2016-03-02',1236),
(34369,'2016-03-02',1236),(44392,'2015-10-21',1237),(44397,'2015-10-21',1237),
(54321,'2015-06-18',1234),(54324,'2015-06-18',1234),(54325,'2015-06-18',1234),
(59237,'2016-02-14',1237),(61353,'2013-01-28',1245),(61354,'2013-01-28',1245),
(61367,'2015-07-30',1246),(61369,'2015-07-30',1246),(61388,'2014-01-25',1239);
INSERT INTO `rental` VALUES (1001,'2016-03-01',103),(1002,'2016-03-01',105),
(1003,'2016-03-02',102),(1004,'2016-03-02',110),(1005,'2016-03-02',111),
(1006,'2016-03-02',107),(1007,'2016-03-02',104),(1008,'2016-03-03',105),(1009,'2016-03-03',111);
INSERT INTO `detailrental` VALUES (1001,34342,2.00,'2016-03-04','2016-03-02',NULL),
(1001,34366,3.50,'2016-03-04','2016-03-02',3.00),(1001,61353,2.00,'2016-03-04','2016-03-03',1.00),
(1002,59237,3.50,'2016-03-04','2016-03-04',3.00),(1003,54325,3.50,'2016-03-04','2016-03-09',3.00),
(1003,61369,2.00,'2016-03-06','2016-03-09',1.00),(1003,61388,0.00,'2016-03-06','2016-03-09',1.00),
(1004,34341,2.00,'2016-03-07','2016-03-07',1.00),(1004,34367,3.50,'2016-03-05','2016-03-07',3.00),
(1004,44392,3.50,'2016-03-05','2016-03-07',3.00),(1005,34342,2.00,'2016-03-07','2016-03-05',1.00),
(1005,44397,3.50,'2016-03-05','2016-03-05',3.00),(1006,34366,3.50,'2016-03-05','2016-03-04',3.00),
(1006,61367,2.00,'2016-03-07',NULL,1.00),(1007,34368,3.50,'2016-03-05',NULL,3.00),
(1008,34369,3.50,'2016-03-05','2016-03-05',3.00),
(1009,54324,3.50,'2016-03-05',NULL,3.00);

COMMIT;

Answer 1

Query 1:

select CONCAT(mem_fname,' ',mem_lname) as FULL_NAME,mem_state,mem_city from membership order by if(mem_state ='KY',mem_fname ,mem_lname) DESC;

OUTPUT

+-----------------+-----------+--------------+
| FULL_NAME       | mem_state | mem_city     |
+-----------------+-----------+--------------+
| LUIS TRUJILLO   | TN        | HEISKELL     |
| LEWIS ROSALES   | TN        | COUNCE       |
| MIRANDA PARKS   | TN        | GERMANTOWN   |
| CLINT OCHOA     | TN        | GREENEVILLE  |
| JAMAL MELENDEZ  | TN        | QUEBECK      |
| MATTIE GUY      | KY        | LILY         |
| STACY MANN      | TN        | MURFREESBORO |
| IVA MCCLAIN     | KY        | SUMMIT       |
| MINNIE GONZALES | TN        | WILLISTON    |
| ROSARIO ELLIOTT | TN        | COLUMBIA     |
| TAMI DAWSON     | TN        | NORENE       |
| CURT KNIGHT     | KY        | FLATGAP      |
+-----------------+-----------+--------------+

Query 2:

Select COUNT(CASE when movie_genre='FAMILY' THEN 1 end) as family,COUNT(CASE when movie_genre='ACTION' THEN 1 end)as action,COUNT(CASE when movie_genre='DRAMA' THEN 1 end) as drama,COUNT(CASE when movie_genre='COMEDY' THEN 1 end) as comedy from movie;

OUTPUT

+--------+--------+-------+--------+
| family | action | drama | comedy |
+--------+--------+-------+--------+
|      1 |      2 |     3 |      2 |
+--------+--------+-------+--------+
1 row in set (0.001 sec)

SCREENSHOT