MySql - SELECT statement - Queries

For this assignment you will be using the classicmodels database on our unix system. Test your queries so that they would retrieve data although there is not data being stored.

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;