Instructions: Write the SQL statements to create the output described in the Problems below. ALL queries should return data

Problems:

1) Write the SQL commands to produce a query that lists ALL the fields for all the records in the MODEL table (note: they do not need to be in any particular order).

2) List CUSTOMER CODE, LASTNAME, FIRSTNAME and PHONE NUMBER for Kathy Smith in the CUSTOMER table.

3) List CHARTER CODE NUMBER, CHARTER DATE and CHARTER PILOT NUMBER for those flights in the CHARTER table whose date of departure is scheduled to leave on any date from 2/6/2008 through 2/8/2008.

4) List EMPLOYEE LASTNAME, FIRSTNAME and DATE OF BIRTH for all employees, except Robert Williams, in the EMPLOYEE table whose date of birth is after 5/19/70.

5) List EMPLOYEE NUMBER, PILOT LICENSE and PILOT RATINGS for all pilots in the PILOT table whose employee number is either 101, 105 or 106.

6) You remember that a fellow employee has an unusual spelling for a name that sounds like George. List EMPLOYEE FIRSTNAME and LASTNAME for all employees whose firstname ends in rge.

7) List DATE OF BIRTH, EMPLOYEE NUMBER, TITLE, LASTNAME and FIRSTNAME for ALL employees in order by date of birth (oldest person first in the list).

8) List CUSTOMER AREACODE, BALANCE OWED, LASTNAME, FIRSTNAME and PHONE NUMBER for those customers who have a middle initial and put them in order, first by areacode, and within each areacode by balance owed (from largest to smallest) (okay, so its kind of a hokey exampledeal with it.

9) Print a TOTAL of all the CUSTOMER BALANCES owed from the CUSTOMER table. 10) Print a COUNT of all the people who owe money (positive balance) in the CUSTOMER table.

11) Produce the output shown in the figure below for aircraft 2778V. Note that this output includes data from the CHARTER and CUSTOMER tables (Hint: Uses a JOIN in this query).

12) Produce the output shown in the figure below. The output, derived from the CHARTER and MODEL tables, is limited to February 6, 2008. (Hint: The join passes through another table. Note that the connection between CHARTER and MODEL requires the existence of AIRCRAFT because the CHARTER table does not contain a foreign key to MODEL. However, CHARTER does contain AC_NUMBER, a foreign key to AIRCRAFT, which contains a foreign key to MODEL.)

SQL script:

CREATE DATABASE IF NOT EXISTS `coopsql-avia_company` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `coopsql-avia_company`;

-- MySQL dump 10.13 Distrib 5.6.19, for osx10.7 (i386)

--

-- Host: localhost Database: coopsql-avia_company

-- ------------------------------------------------------

-- Server version 5.6.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `AIRCRAFT`

--

DROP TABLE IF EXISTS `AIRCRAFT`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `AIRCRAFT` (

`AC_NUMBER` varchar(5) NOT NULL,

`MOD_CODE` varchar(10) DEFAULT NULL,

`AC_TTAF` double DEFAULT NULL,

`AC_TTEL` double DEFAULT NULL,

`AC_TTER` double DEFAULT NULL,

PRIMARY KEY (`AC_NUMBER`),

KEY `Reference2` (`MOD_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `AIRCRAFT`

--

LOCK TABLES `AIRCRAFT` WRITE;

/*!40000 ALTER TABLE `AIRCRAFT` DISABLE KEYS */;

INSERT INTO `AIRCRAFT` VALUES ('1484P','PA23-250',1833.1,1833.1,101.8),('2289L','C-90A',4243.8,768.9,1123.4),('2778V','PA31-350',7992.9,1513.1,789.5),('4278Y','PA31-350',2147.3,622.1,243.2);

/*!40000 ALTER TABLE `AIRCRAFT` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `CHARTER`

--

DROP TABLE IF EXISTS `CHARTER`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `CHARTER` (

`CHAR_TRIP` int(10) NOT NULL,

`CHAR_DATE` datetime DEFAULT NULL,

`CHAR_PILOT` int(10) DEFAULT NULL,

`CHAR_COPILOT` int(10) DEFAULT NULL,

`AC_NUMBER` varchar(5) DEFAULT NULL,

`CHAR_DESTINATION` varchar(3) DEFAULT NULL,

`CHAR_DISTANCE` double DEFAULT NULL,

`CHAR_HOURS_FLOWN` double DEFAULT NULL,

`CHAR_HOURS_WAIT` double DEFAULT NULL,

`CHAR_FUEL_GALLONS` double DEFAULT NULL,

`CHAR_OIL_QTS` smallint(5) DEFAULT NULL,

`CUS_CODE` int(10) DEFAULT NULL,

PRIMARY KEY (`CHAR_TRIP`),

KEY `Reference5` (`CHAR_COPILOT`),

KEY `Reference1` (`CUS_CODE`),

KEY `Reference3` (`CHAR_PILOT`),

KEY `Reference` (`AC_NUMBER`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `CHARTER`

--

LOCK TABLES `CHARTER` WRITE;

/*!40000 ALTER TABLE `CHARTER` DISABLE KEYS */;

INSERT INTO `CHARTER` VALUES (10001,'2008-02-05 00:00:00',104,NULL,'2289L','ATL',936,5.1,2.2,354.1,1,10011),(10002,'2008-02-05 00:00:00',101,NULL,'2778V','BNA',320,1.6,0,72.6,0,10016),(10003,'2008-02-05 00:00:00',105,109,'4278Y','GNV',1574,7.8,0,339.8,2,10014),(10004,'2008-02-06 00:00:00',106,NULL,'1484P','STL',472,2.9,4.9,97.2,1,10019),(10005,'2008-02-06 00:00:00',101,NULL,'2289L','ATL',1023,5.7,3.5,397.7,2,10011),(10006,'2008-02-06 00:00:00',109,NULL,'4278Y','STL',472,2.6,5.2,117.1,0,10017),(10007,'2008-02-06 00:00:00',104,105,'2778V','GNV',1574,7.9,0,348.4,2,10012),(10008,'2008-02-07 00:00:00',106,NULL,'1484P','TYS',644,4.1,0,140.6,1,10014),(10009,'2008-02-07 00:00:00',105,NULL,'2289L','GNV',1574,6.6,23.4,459.9,0,10017),(10010,'2008-02-07 00:00:00',109,NULL,'4278Y','ATL',998,6.2,3.2,279.7,0,10016),(10011,'2008-02-07 00:00:00',101,104,'1484P','BNA',352,1.9,5.3,66.4,1,10012),(10012,'2008-02-08 00:00:00',101,NULL,'2778V','MOB',884,4.8,4.2,215.1,0,10010),(10013,'2008-02-08 00:00:00',105,NULL,'4278Y','TYS',644,3.9,4.5,174.3,1,10011),(10014,'2008-02-09 00:00:00',106,NULL,'4278Y','ATL',936,6.1,2.1,302.6,0,10017),(10015,'2008-02-09 00:00:00',104,101,'2289L','GNV',1645,6.7,0,459.5,2,10016),(10016,'2008-02-09 00:00:00',109,105,'2778V','MQY',312,1.5,0,67.2,0,10011),(10017,'2008-02-10 00:00:00',101,NULL,'1484P','STL',508,3.1,0,105.5,0,10014),(10018,'2008-02-10 00:00:00',105,104,'4278Y','TYS',644,3.8,4.5,167.4,0,10017);

/*!40000 ALTER TABLE `CHARTER` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `CUSTOMER`

--

DROP TABLE IF EXISTS `CUSTOMER`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `CUSTOMER` (

`CUS_CODE` int(10) NOT NULL,

`CUS_LNAME` varchar(15) DEFAULT NULL,

`CUS_FNAME` varchar(15) DEFAULT NULL,

`CUS_INITIAL` varchar(1) DEFAULT NULL,

`CUS_AREACODE` varchar(3) DEFAULT NULL,

`CUS_PHONE` varchar(8) DEFAULT NULL,

`CUS_BALANCE` double DEFAULT NULL,

PRIMARY KEY (`CUS_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `CUSTOMER`

--

LOCK TABLES `CUSTOMER` WRITE;

/*!40000 ALTER TABLE `CUSTOMER` DISABLE KEYS */;

INSERT INTO `CUSTOMER` VALUES (10010,'Ramas','Alfred','A','615','844-2573',0),(10011,'Dunne','Leona','K','713','894-1238',0),(10012,'Smith','Kathy','W','615','894-2285',896.54),(10013,'Olowski','Paul','F','615','894-2180',1285.19),(10014,'Orlando','Myron',NULL,'615','222-1672',673.21),(10015,'O\'Brian','Amy','B','713','442-3381',1014.56),(10016,'Brown','James','G','615','297-1228',0),(10017,'Williams','George',NULL,'615','290-2556',0),(10018,'Farriss','Anne','G','713','382-7185',0),(10019,'Smith','Olette','K','615','297-3809',453.98);

/*!40000 ALTER TABLE `CUSTOMER` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `EMPLOYEE`

--

DROP TABLE IF EXISTS `EMPLOYEE`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `EMPLOYEE` (

`EMP_NUM` int(10) NOT NULL,

`EMP_TITLE` varchar(4) DEFAULT NULL,

`EMP_LNAME` varchar(15) DEFAULT NULL,

`EMP_FNAME` varchar(15) DEFAULT NULL,

`EMP_INITIAL` varchar(1) DEFAULT NULL,

`EMP_DOB` datetime DEFAULT NULL,

`EMP_HIRE_DATE` datetime DEFAULT NULL,

PRIMARY KEY (`EMP_NUM`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `EMPLOYEE`

--

LOCK TABLES `EMPLOYEE` WRITE;

/*!40000 ALTER TABLE `EMPLOYEE` DISABLE KEYS */;

INSERT INTO `EMPLOYEE` VALUES (100,'Mr.','Kolmycz','George','D','1942-06-15 00:00:00','1988-03-15 00:00:00'),(101,'Ms.','Lewis','Rhonda','G','1965-03-19 00:00:00','1986-04-25 00:00:00'),(102,'Mr.','VanDam','Rhett',NULL,'1958-11-14 00:00:00','1993-05-18 00:00:00'),(103,'Ms.','Jones','Anne','M','1974-05-11 00:00:00','1999-07-26 00:00:00'),(104,'Mr.','Lange','John','P','1971-07-12 00:00:00','1990-08-20 00:00:00'),(105,'Mr.','Williams','Robert','D','1975-03-14 00:00:00','2003-06-19 00:00:00'),(106,'Mrs.','Duzak','Jeanine','K','1968-02-12 00:00:00','1989-03-13 00:00:00'),(107,'Mr.','Diante','Jorge','D','1975-05-01 00:00:00','1997-07-02 00:00:00'),(108,'Mr.','Wiesenbach','Paul','R','1966-02-14 00:00:00','1993-06-03 00:00:00'),(109,'Ms.','Travis','Elizabeth','K','1961-06-18 00:00:00','2006-02-14 00:00:00'),(110,'Mrs.','Genkazi','Leighla','W','1970-05-19 00:00:00','1990-06-29 00:00:00');

/*!40000 ALTER TABLE `EMPLOYEE` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `MODEL`

--

DROP TABLE IF EXISTS `MODEL`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `MODEL` (

`MOD_CODE` varchar(10) NOT NULL,

`MOD_MANUFACTURER` varchar(15) DEFAULT NULL,

`MOD_NAME` varchar(20) DEFAULT NULL,

`MOD_SEATS` double DEFAULT NULL,

`MOD_CHG_MILE` double DEFAULT NULL,

PRIMARY KEY (`MOD_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `MODEL`

--

LOCK TABLES `MODEL` WRITE;

/*!40000 ALTER TABLE `MODEL` DISABLE KEYS */;

INSERT INTO `MODEL` VALUES ('C-90A','Beechcraft','KingAir',8,2.67),('PA23-250','Piper','Aztec',6,1.93),('PA31-350','Piper','Navajo Chieftain',10,2.35);

/*!40000 ALTER TABLE `MODEL` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `PILOT`

--

DROP TABLE IF EXISTS `PILOT`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `PILOT` (

`EMP_NUM` int(10) NOT NULL,

`PIL_LICENSE` varchar(25) DEFAULT NULL,

`PIL_RATINGS` varchar(30) DEFAULT NULL,

`PIL_MED_TYPE` varchar(1) DEFAULT NULL,

`PIL_MED_DATE` datetime DEFAULT NULL,

`PIL_PT135_DATE` datetime DEFAULT NULL,

PRIMARY KEY (`EMP_NUM`),

UNIQUE KEY `Reference4` (`EMP_NUM`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `PILOT`

--

LOCK TABLES `PILOT` WRITE;

/*!40000 ALTER TABLE `PILOT` DISABLE KEYS */;

INSERT INTO `PILOT` VALUES (101,'ATP','ATP/SEL/MEL/Instr/CFII','1','2008-01-20 00:00:00','2008-01-11 00:00:00'),(104,'ATP','ATP/SEL/MEL/Instr','1','2007-12-18 00:00:00','2008-01-17 00:00:00'),(105,'COM','COMM/SEL/MEL/Instr/CFI','2','2008-01-05 00:00:00','2008-01-02 00:00:00'),(106,'COM','COMM/SEL/MEL/Instr','2','2007-12-10 00:00:00','2008-02-02 00:00:00'),(109,'COM','ATP/SEL/MEL/SES/Instr/CFII','1','2008-01-22 00:00:00','2008-01-15 00:00:00');

/*!40000 ALTER TABLE `PILOT` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Dumping routines for database 'coopsql-avia_company'

--

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-11-15 7:26:44

PILOT 1 CHARTER PILOT EMP NUM EMP NUM EMPLOYEE CHAR TRIP CHAR DATE CHAR PILOT CHAR COPILOT AC NUMBER CHAR DESTINATION CHAR DISTANCE CHAR HOURS_FLOWN CHAR HOURS WAIT CHAR FUEL GALLONS CHAR OIL QTS CUS CODE PIL LICENSE PIL RATINGS PIL MED_TYPE PIL MED DATE PIL PT135 DATE EMP NUM PIL LICENSE PIL RATINGS PIL_MED_TYPE PIL MED DATE PIL PT135 DATE EMP TITLE EMP LNAME EMP FNAME EMP INITIAL EMP DOB EMP HIRE DATE AIRCRAFT CUSTOMER At NUMBER CUS CODE MODEL CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE MOD CODE AC TTAF AC TTEL AC TTER MOD-CODE MOD MANUFACTURER MOD NAME MOD SEATS MOD CHG MILE