stanford open course db-class: "Table variables and set operators"

table-vars-and-set-operators.sql

-- To get started, create the database (load sql below, stop after dump completion)
--
-- command to dump db: jfi@mac:~$ mysqldump -u root -p dbclass

--
-- Table structure for table `apply`
--

DROP TABLE IF EXISTS `apply`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apply` (
  `sID` int(11) NOT NULL,
  `cName` varchar(50) NOT NULL,
  `major` varchar(50) NOT NULL,
  `decision` varchar(1) DEFAULT NULL,
  UNIQUE KEY `sID` (`sID`,`cName`,`major`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `apply`
--

LOCK TABLES `apply` WRITE;
/*!40000 ALTER TABLE `apply` DISABLE KEYS */;
INSERT INTO `apply` VALUES (123,'Stanford','CS','Y'),(123,'Stanford','EE','N'),(123,'Berkeley','CS','Y'),(123,'Cornell','EE','Y'),(234,'Berkeley','biology','N'),(345,'MIT','bioengineering','Y'),(345,'Cornell','bioengineering','N'),(345,'Cornell','CS','Y'),(345,'Cornell','EE','N'),(678,'Stanford','history','Y'),(987,'Stanford','CS','Y'),(987,'Berkeley','CS','Y'),(876,'Stanford','CS','N'),(876,'MIT','biology','Y'),(876,'MIT','marine biology','N'),(765,'Stanford','history','Y'),(765,'Cornell','history','N'),(765,'Cornell','psychology','Y'),(543,'MIT','CS','N');
/*!40000 ALTER TABLE `apply` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `college`
--

DROP TABLE IF EXISTS `college`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `college` (
  `cName` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `enrollment` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`cName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `college`
--

LOCK TABLES `college` WRITE;
/*!40000 ALTER TABLE `college` DISABLE KEYS */;
INSERT INTO `college` VALUES ('Stanford','CA',15000),('Berkeley','CA',36000),('MIT','MA',10000),('Cornell','NY',21000);
/*!40000 ALTER TABLE `college` 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` (
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  `sName` varchar(50) DEFAULT NULL,
  `GPA` double DEFAULT NULL,
  `sizeHS` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`sID`)
) ENGINE=MyISAM AUTO_INCREMENT=988 DEFAULT CHARSET=latin1;
/*!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 (123,'Amy',3.9,1000),(234,'Bob',3.6,1500),(345,'Craig',3.5,500),(456,'Doris',3.9,1000),(567,'Edward',2.9,2000),(678,'Fay',3.8,200),(789,'Gary',3.4,800),(987,'Helen',3.7,800),(876,'Irene',3.9,400),(765,'Jay',2.9,1500),(654,'Amy',3.9,1000),(543,'Craig',3.4,2000);
/*!40000 ALTER TABLE `student` 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 */;

-- Dump completed on 2011-10-23 18:58:24

-- MySQL does not have intersect, sad pig :(
-- Work around:
select distinct a1.sid from apply a1 inner join apply a2 on a1.sid = a2.sid where ((a1.major = 'CS' or a2.major = 'CS') and (a1.major = 'EE' or a2.major = 'EE'));

-- or this (shown in the video):
select distinct a1.sid from apply a1, apply a2 where a1.sid = a2.sid and a1.major = 'CS' and a2.major = 'EE';

-- no except (difference operator). Work around using subqueries
select sid, sname from student where sid in (select sid from apply where major = 'CS') and sid not in (select sid from apply where major = 'EE');

Thank you!