Database Design

SQL Exercises #2 - Using CoopSQL-avia_company database (MySQL)

6/6/2017

For the following exercises, open the CoopSQL-avia_company database (instructor provided in MySQL), and write the SQL statements to create the output described below. ALL queries should return data (extra credit for finding any issues). Start an MSWord document with cover page. On the document list the question number with copied SQL syntax and a screen shot of the output for each question in MySQL. Create the working query in MySQL first. Afterwards check to see if commands work in MSAccess.

You are encouraged to work in groups, but you are responsible for your own learning.

1. Create an SQL script to add a table named Airline to the Cooperative_SQL_Avia file with the following data. Attempt to connect the Airline table to the Employee table and Enforce Referential Integrity. Store the dashes for the phone number in the database.

2. Write an SQL command that will find the incorrectly stored EMP_NUM_CONTACT of 111 and replace it with the correct contact 101. Again, attempt to connect the Airline table to the Employee table and Enforce Referential Integrity.

3. List the Charter Trip, Charter Date, and the Charter Wait Time for those Charters that have a value in the Charter_Wait_Time field. Order them from highest to lowest wait time.

4. List the same fields from Question #3 for the Charter record(s) that had the longest Wait Time.

5. List the Pilots Employee Number, License Number, Lastname, and Firstname for the Charter records in Question #3.

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

Airline ID Airline Contact Lastname Airline Contact Firstname Airline Contact Phone Emp Num Contact Boein Mauer Ken 888-555-1212 100 Delta Johnson Brad 888-555-1233 Southwest Airline Lester Jon 888-555-0101 Sun Count Range Courtne 888-555-4321 104 Alaska Airlines Wilson Brian 888-555-9876 109 Hawaiian Airlines rout Mike 888-555-1234