## Problem Instructions

1. Move the file `p2_grades.sql` to the `htdocs\hs7\p2` folder. This batch file when imported into the database server will create the tables you are to work with in this assignment.

1. Import the batch file `p2_grades.sql` using the `mysql` client or `phpMyAdmin`. If using the client terminal, make sure you log into the database server from the `p2` folder. *__Note:__* You must create the database `p2_grades` first if it does not exist, before performing the import.

The questions you are to answer are prefixed with a **`Q:`** label. Your answers will be typed directly into the raw version of this document inside an answer block identified as:

**`A:`**

```

Type your answer here

```

*__Note:__* Use uppercase when typing SQL commands and type each part of the SQL statement on a separate line. For example:

```

SELECT *

FROM table

ORDER BY col

```

It is advisable that you use either the `mysql` client monitor or `phpMyAdmin` to perform the following steps and actually create the database and tables. That way you can test out your answers and gain experience interacting directly with the database server.

## Questions

**`Q1:`** Type a `WHERE` join to list the `term` and `year` in which the course `CS140` was taken by students.

**`Q2:`** Type a `WHERE` join to list the semester as the concatenated col (`termyear`) and the `course$id` of each course taken. Order by `year` and `term`.

**`Q3:`** Type a `WHERE` join to list the semester as the concatenated col (`termyear`) and the `number` of each course taken. Order by `year` and `term`.

**`Q4:`** Type a `WHERE` join to list the `student$id`, grade, course `number`, `term` and `year` for every course that received a grade other than 'A', 'B', or 'C'.

**`Q5:`** Type a `WHERE` join to list the course `number`, `student$id`, `year`, and `term`. Order the results by `number`, `year`, and `term`.

_______________________________________________________________________

grades.sql

-- MySQL dump 10.13 Distrib 5.7.30, for osx10.12 (x86_64)

--

-- Host: localhost Database: p3_grades

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

-- Server version 5.7.30

/*!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 `course`

--

DROP TABLE IF EXISTS `course`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `course` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`number` char(6) NOT NULL,

`title` varchar(50) NOT NULL,

`hours` double(2,1) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

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

--

-- Dumping data for table `course`

--

LOCK TABLES `course` WRITE;

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

INSERT INTO `course` VALUES (1,'CS111','Computer Concepts',3.0),(2,'ENG101','English Composition',3.0),(3,'MA150','Calculus I',5.0),(4,'CS140','Intro to Computing I',4.0),(5,'CS150','Intro to Computing II',3.0),(6,'CS240','Intro to Computing III',3.0),(7,'MA125','College Algebra',3.0);

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

UNLOCK TABLES;

--

-- Table structure for table `student`

--

DROP TABLE IF EXISTS `student`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `student` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`eid` int(11) NOT NULL,

`first` varchar(20) NOT NULL,

`last` varchar(30) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

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

--

-- Dumping data for table `student`

--

LOCK TABLES `student` WRITE;

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

INSERT INTO `student` VALUES (1,800,'Allison','Auburn'),(2,801,'Barry','Brown'),(3,802,'Cathy','Crimson'),(4,803,'Frank','Fucia'),(5,804,'Herald','Hibiscus');

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

UNLOCK TABLES;

--

-- Table structure for table `transcript`

--

DROP TABLE IF EXISTS `transcript`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `transcript` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`student$id` int(10) unsigned NOT NULL,

`course$id` int(10) unsigned NOT NULL,

`term` enum('SP','SU','FA') NOT NULL,

`year` int(4) NOT NULL,

`grade` char(2) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `student$id` (`student$id`),

KEY `course$id` (`course$id`),

CONSTRAINT `transcript_ibfk_1` FOREIGN KEY (`student$id`) REFERENCES `student` (`id`),

CONSTRAINT `transcript_ibfk_2` FOREIGN KEY (`course$id`) REFERENCES `course` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

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

--

-- Dumping data for table `transcript`

--

LOCK TABLES `transcript` WRITE;

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

INSERT INTO `transcript` VALUES (1,1,1,'SP',2018,'A'),(2,1,2,'SP',2018,'B'),(3,1,3,'SU',2018,'A'),(4,1,4,'FA',2018,'B'),(5,1,5,'FA',2018,'C'),(6,2,1,'FA',2019,'A'),(7,2,3,'FA',2019,'WP'),(8,2,4,'FA',2019,'B'),(9,2,7,'FA',2019,'C'),(10,3,1,'SU',2020,'A'),(11,4,2,'FA',2019,'B'),(12,4,3,'FA',2019,'C'),(13,4,1,'SP',2020,'A'),(14,4,2,'SP',2020,'B'),(15,4,3,'SP',2020,'A');

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

UNLOCK TABLES;

/*!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 */;