可爱多hh 2018-10-27 17:14 采纳率: 100%
浏览 1170
已采纳

创建一个数据表,只需要create table就可以

图片说明
只需要CREATE TABLE 就可以,不用INSERT
Relational Schema

Production(Title,BasicTicketPrice)
Performance(PerfDate,PerfTime,Title)
Zone(Name,PriceMultiplier)
Seat(RowNumber,Zone)
Booking(Email,PerfDate,PerfTime,RowNumber)

Constraints

Not Null:

Production: BasicTicketPrice
Booking: Email
Zone: PriceMultiplier
Data

We have provided table creation and insert statements for Zone and Seat. Use these to create and populate the two tables.
Zone
Seat

You will need to define your own table creation and insert commands for the following data: Production

Title BasicTicketPrice
Cats 15.00
Fame 15.00
Tosca 30.00

Performance
PerfDate PerfTime Title
2017-11-01 19:00:00 Cats
2017-11-02 19:00:00 Cats
2017-11-03 19:00:00 Cats
2017-11-03 13:00:00 Cats
2017-11-04 19:00:00 Fame
2017-11-05 13:00:00 Fame
2017-11-05 19:00:00 Tosca
2017-11-06 13:00:00 Tosca
2017-11-06 19:00:00 Tosca

Booking
Email PerfDate PerfTime RowNumber
ZP@email.com 2017-11-01 19:00:00 Z18
ZP@email.com 2017-11-01 19:00:00 Z19
Jane.Dot@live.com 2017-11-01 19:00:00 Z16
Jane.Dot@live.com 2017-11-05 13:00:00 U20
Jane.Dot@live.com 2017-11-05 13:00:00 U19
Mike.Stand@email.com 2017-11-05 13:00:00 X13
Mike.Stand@email.com 2017-11-05 13:00:00 X14
qvf3@live.com 2017-11-05 13:00:00 Z19
What you have to do

Write mySQL CREATE TABLE commands for Production, Performance and Booking, ensuring that they contain the correct columns. The commands must include primary key definitions. Pick sensible data types for the columns. You must include foreign keys and ensure that other constraints are enforced. These constraints include those explicitly listed as well as those implied by the models.
Write mySQL insertion commands to populate the tables with the data given above. Only the given data should be inserted.
Create the following mySQL queries.
All performances of Cats in the order they occur. Output all columns of the Performance table.
Booking and performance details where the person booking has a email with domain "@email.com". For each such booking, output the Email, PerfDate, PerfTime and Title.
The prices paid for of all booked seats. Output the RowNumber, Email of person booking and calculated price.
All the free seats for the performance at 2017-11-01, 19:00:00. Output the RowNumber only.
Count of all the seats in each zone for zones where the PriceMultiplier is greater than 2.3. Output the zone Name, PriceMultiplier and the count.

  • 写回答

1条回答 默认 最新

  • threenewbee 2018-10-28 06:09
    关注
     CREATE DATABASE  IF NOT EXISTS `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */;
    USE `mydb`;
    -- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)
    --
    -- Host: localhost    Database: mydb
    -- ------------------------------------------------------
    -- Server version   5.1.72-community
    
    /*!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 `booking`
    --
    
    DROP TABLE IF EXISTS `booking`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `booking` (
      `id` int(11) NOT NULL,
      `Email` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `SeatID_idx` (`id`),
      KEY `PerID_idx` (`id`),
      CONSTRAINT `PerID` FOREIGN KEY (`id`) REFERENCES `performance` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `SeatID` FOREIGN KEY (`id`) REFERENCES `seat` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='<double-click to overwrite multiple objects>';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `booking`
    --
    
    LOCK TABLES `booking` WRITE;
    /*!40000 ALTER TABLE `booking` DISABLE KEYS */;
    /*!40000 ALTER TABLE `booking` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Table structure for table `performance`
    --
    
    DROP TABLE IF EXISTS `performance`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `performance` (
      `id` int(11) NOT NULL,
      `PerDate` date DEFAULT NULL,
      `PerTime` time DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `ProdID_idx` (`id`),
      CONSTRAINT `ProdID` FOREIGN KEY (`id`) REFERENCES `production` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='<double-click to overwrite multiple objects>';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `performance`
    --
    
    LOCK TABLES `performance` WRITE;
    /*!40000 ALTER TABLE `performance` DISABLE KEYS */;
    /*!40000 ALTER TABLE `performance` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Table structure for table `production`
    --
    
    DROP TABLE IF EXISTS `production`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `production` (
      `id` int(11) NOT NULL,
      `Ttile` varchar(45) DEFAULT NULL,
      `BasicTicketPrice` float DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='<double-click to overwrite multiple objects>';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `production`
    --
    
    LOCK TABLES `production` WRITE;
    /*!40000 ALTER TABLE `production` DISABLE KEYS */;
    /*!40000 ALTER TABLE `production` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Table structure for table `seat`
    --
    
    DROP TABLE IF EXISTS `seat`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `seat` (
      `id` int(11) NOT NULL,
      `RpwNumber` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `ZoneID_idx` (`id`),
      CONSTRAINT `ZoneID` FOREIGN KEY (`id`) REFERENCES `zone` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='<double-click to overwrite multiple objects>';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `seat`
    --
    
    LOCK TABLES `seat` WRITE;
    /*!40000 ALTER TABLE `seat` DISABLE KEYS */;
    /*!40000 ALTER TABLE `seat` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Table structure for table `zone`
    --
    
    DROP TABLE IF EXISTS `zone`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `zone` (
      `id` int(11) NOT NULL,
      `Name` varchar(45) DEFAULT NULL,
      `PriceMultiplier` float DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='<double-click to overwrite multiple objects>';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `zone`
    --
    
    LOCK TABLES `zone` WRITE;
    /*!40000 ALTER TABLE `zone` DISABLE KEYS */;
    /*!40000 ALTER TABLE `zone` 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 2018-10-28 14:08:22
    
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 Torch not compiled with CUDA enabled
  • ¥15 三种节点编号优化算法比较
  • ¥15 比特币ord程序wallet_constructor.rs文件支持一次性铸造1000个代币,并将它们分配到40个UTXO上(每个UTXO上分配25个代币),并设置找零地址
  • ¥115 用Java解决探地雷达dzt文件的解析过程
  • ¥20 有关神经网络这道(b)determine the parameters of neural network
  • ¥25 annaconda jvpyter
  • ¥20 来一个会抓包app支付接口的
  • ¥30 vivado2017.4的sdk打不开
  • ¥50 matlab,文献复现
  • ¥20 寻找Flink CDC指导,有偿咨询