MySql - SELECT statement Hello! I need to make a script to retrieve data in MySq

Question

MySql - SELECT statement

Hello! I need to make a script to retrieve data in MySql using the select statement. The only required parts are the write the query to get the data (using select) The two scripts for creating and dropping the tables will be given at the bottom. Thank you!

For this assignment you will be using the classicmodels database on our unix system. Test your queries, then put them in a script named myScript.sql with comments. Run the script in MySQL putting the output into a file named myOutput.txt.

1.How many tables are there and what are their names?

2.What are the column names and domains for each table?

3.a.How many customers are there? b.How many customers have orders?

4.a.How many products are there? b.List all the details for the first 10 products. (Use limit)

5.What is the total payment amount for each customer who has made a payment, list only the first 15.

6.What are the names of the cities where there are offices, list them in alphabetic order?

7.a.How many employees are there? b.How many employees work in each office? List the count and the office code.

8.a.How many orders are there? b.How many orders are there for each customer who has placed an order? List only those with more than 5 orders. c.How many orders have shipped? d.What possible status can an order have?

9.List all the employee names in the format last, first (for example Green, Joe). List them in reverse alphabetic order of last name. (Use the concat function)

10.List all of the employees who work in London. You cannot use more than one table in a single select statement.

Given script files:

create.txt:

-- This is a script to add the space doctors tables from class
CREATE TABLE Person(people_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), birthdate DATE);
CREATE TABLE Planet(planet_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), loc VARCHAR(255));
CREATE TABLE Disease(name VARCHAR(255) PRIMARY KEY, descr VARCHAR(255));
CREATE TABLE Treatment(treatment_id INT PRIMARY KEY AUTO_INCREMENT, descr VARCHAR(255));
CREATE TABLE Doctor(people_id INT NOT NULL, degree VARCHAR(32) not null, school varchar(32) not null, degree_date DATE not null, specialty VARCHAR(20), PRIMARY KEY(people_id), FOREIGN KEY (people_id) REFERENCES Person(people_id));
CREATE TABLE CrewMember(people_id INT NOT NULL, position VARCHAR(20) Not NULL, salary DECIMAL(11,2) NOT NULL, PRIMARY KEY(people_id), FOREIGN KEY (people_id) REFERENCES Person(people_id));
CREATE TABLE TreatmentHistory(date_contracted DATETIME, crew_id INT NOT NULL, doctor INT NOT NULL, disease_id VARCHAR(255), treatment_id INT NOT NULL, PRIMARY KEY(date_contracted, crew_id), FOREIGN KEY (crew_id) REFERENCES CrewMember(people_id), FOREIGN KEY (doctor) REFERENCES Doctor(people_id), FOREIGN KEY (disease_id) REFERENCES Disease(name), FOREIGN KEY (treatment_id) REFERENCES Treatment(treatment_id));
CREATE TABLE Symptom(disease_name VARCHAR(255) NOT NULL, symptom_num INT NOT NULL, descr VARCHAR(255), PRIMARY KEY(disease_name, symptom_num), FOREIGN KEY (disease_name) REFERENCES Disease(name));
CREATE TABLE SideEffect(treatment_id INT NOT NULL, se_num INT NOT NULL, descr VARCHAR(255), PRIMARY KEY(treatment_id, se_num), FOREIGN KEY (treatment_id) REFERENCES Treatment(treatment_id));
CREATE TABLE TreatmentForDisease(treatment_id INT, disease_name VARCHAR(255), recommendation_priority INT, PRIMARY KEY (treatment_id, disease_name), FOREIGN KEY(treatment_id) REFERENCES Treatment(treatment_id), FOREIGN KEY(disease_name) REFERENCES Disease(name));
CREATE TABLE DiseaseOnPlanet(disease_name VARCHAR(255), planet_id INT, date_discovered DATE, discoverer_name VARCHAR(255), PRIMARY KEY(disease_name, planet_id), FOREIGN KEY (disease_name) REFERENCES Disease(name), FOREIGN KEY(planet_id) REFERENCES Planet(planet_id));
CREATE TABLE CrewVisitsPlanet(crew_id INT, planet_id INT, visitdate DATETIME, PRIMARY KEY(crew_id, planet_id, visitdate), FOREIGN KEY(crew_id) REFERENCES CrewMember(people_id), FOREIGN KEY(planet_id) REFERENCES Planet(planet_id));

drop.txt:

-- This is a script to drop the space doctors tables from class
-- The following line prevents foreign keys from preventing the drop
SET foreign_key_checks = 0;

DROP TABLE Person;
DROP TABLE Planet;
DROP TABLE Disease;
DROP TABLE Treatment;
DROP TABLE Doctor;
DROP TABLE CrewMember;
DROP TABLE TreatmentHistory;
DROP TABLE Symptom;
DROP TABLE SideEffect;
DROP TABLE TreatmentForDisease;
DROP TABLE DiseaseOnPlanet;
DROP TABLE CrewVisitsPlanet;

-- This turns foreign key checks back on after done
SET foreign_key_checks = 1;

Explanation / Answer

1. Login to Mysql and type following script: (to display all the tables)

use classicmodels;show tables;

2. Get all the column names:

3.a select count(people_id) as customer_count from Person;

3.b select count(people_id) as customer_with_order_count from CrewMember;

4.a select count(treatment_id) as product_count from Treatment;

4.b select * from Treatment limit 10;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.