Part 1. SQL DDL (36 points) You are given a following schema: PublicChauffeurs (
ID: 3750475 • Letter: P
Question
Part 1. SQL DDL (36 points)
You are given a following schema:
PublicChauffeurs(License Number, Renewed, Status, Status Date, Driver Type, License Type, Original Issue Date, Name, Sex, Chauffer City, Chauffer State, Record Number)
The table is based on a real data set original taken from City of Chicago data portal (located here: https://data.cityofchicago.org/Community-Economic-Development/Public-Chauffeurs/97wa-y6ff
Write SQL DDL to create the table. Make sure to look at the data to decide reasonable domains.
Declare primary and foreign keys as necessary in your SQL code.
Create at least three reasonable additional attribute-level, tuple-level, NOT NULL, or UNIQUE constraints in your SQL code.
Part 2. SQL DML (64 points)
You were hired to do some data analysis for a local zoo. Below is the data table, including the necessary constraints and all the insert statements to populate the database.
-- Drop all the tables to clean up
DROP TABLE Animal;
-- ACategory: Animal category 'common', 'rare', 'exotic'. May be NULL
-- TimeToFeed: Time it takes to feed the animal (hours)
CREATE TABLE Animal(
AID NUMBER(3, 0),
AName VARCHAR2(30) NOT NULL,
ACategory VARCHAR2(18),
TimeToFeed NUMBER(4,2),
CONSTRAINT Animal_PK PRIMARY KEY(AID)
);
INSERT INTO Animal VALUES(1, 'Galapagos Penguin', 'exotic', 0.5);
INSERT INTO Animal VALUES(2, 'Emperor Penguin', 'rare', 0.75);
INSERT INTO Animal VALUES(3, 'Sri Lankan sloth bear', 'exotic', 2.5);
INSERT INTO Animal VALUES(4, 'Grizzly bear', 'common', 3.0);
INSERT INTO Animal VALUES(5, 'Giant Panda bear', 'exotic', 1.5);
INSERT INTO Animal VALUES(6, 'Florida black bear', 'rare', 1.75);
INSERT INTO Animal VALUES(7, 'Siberian tiger', 'rare', 3.5);
INSERT INTO Animal VALUES(8, 'Bengal tiger', 'common', 2.75);
INSERT INTO Animal VALUES(9, 'South China tiger', 'exotic', 2.25);
INSERT INTO Animal VALUES(10, 'Alpaca', 'common', 0.25);
INSERT INTO Animal VALUES(11, 'Llama', NULL, 3.5);
Since none of the managers in the zoo know SQL, it is up to you to write the queries to answer the following list of questions.
Find all the animals (their names) that take less than 2 hours to feed.
Find all the rare animals and sort the query output by feeding time (any direction)
Find the animal names and categories for the animals that are related to a bear (hint: remember the LIKE operator)
Return the listings for all animals whose rarity is not available in the database
Find the rarity rating of all animals that require between 1 and 2.2 hours to be fed
Find the names of the animals that are related to the tiger and are not common
Find the minimum and maximum feeding time amongst all the animals in the zoo. Use only one query.
Find the average feeding time for the rare animals
I am also including two more challenging queries that you may attempt to test your knowledge of SQL. They aren’t mandatory – you will get 100% credit for the homework if you correctly answer queries A-H.
EC1. Find the listing of the animal that requires the longest feeding time. Do not hardcode any feeding time values.
EC2. Find the names of the animals that can be fed in less than the average overall time + 20% (i.e. in less than 1.2 * average feeding time). Do not hardcode any feeding time values.
Explanation / Answer
PART 1
QUERY TO CREATE THE TABLE :
CREATE TABLE PublicChauffeurs(
LicenseNumber NUMBER(20) NOT NULL UNIQUE,
Renewed VARCHAR2(12),
Status VARCHAR2(30),
StatusDate Date,
drivertype varchar2(30) check (DriverType in ('Taxi', 'Livery Only','Pedicab','Ambulance Only','Horse-Drawn Only')),
LicenseType VARCHAR2(30),
OriginalIssueDate Date,
Name VARCHAR2(30) NOT NULL,
Sex VARCHAR2(10),
ChaufferCity VARCHAR2(30),
ChaufferState VARCHAR2(30),
RecordNumber VARCHAR2(30),
PRIMARY KEY (LicenseNumber)
);
/// Create Table command is used to create a table in the database.
General Syntax is
Create table "tablename" (
col_name1 Col_Data_Type,
col_name2 Col_Data_Type,
)
Data Type simply means what kind of data can be present inside the column:
Number : Numeric data
Varchar2 : Charactes Or Strings
Date : Data is Date
Primary Key is used when we know that the data in the column is unique.
Not null means col cannnot contain null values
check makes sure the data only consists of predefined values
Unique also takes care that the data is unique in col.
PART 2
select aname from animal
where timetofeed < 2;
/// Select statement is used to retrieve data from the database and where is used to specify a condition. Here, aname is the Column name to be retrieved and the condition is added after the where.
General Syntax is : Select ''Col name" where "Condition";
2 select aname from animal
where acategory = 'rare'
order by timetofeed;
/// Here, we also needed to sort a column. Order by is used to sort a Column. The default pattern is ascending order.
3 select aname,acategory
from animal
where aname like '%bear%';
// Like is used whenever we want the result to be contained a specific word i.e 'bear'
% means before and after 'bear' there could be many characters or not.
4 select aname from animal
where acategory is NULL;
// NULL is used to check if the data is present for a column or not.
5 select aname,acategory from animal
where timetofeed between 1 and 2.2;
/// between is used with where whenever we want the data to be present in some range.
6 select aname from animal
where aname like '%tiger%' AND acategory != 'common';
/// Like is used to check for "tiger" in data and the "!=" operator works as not equal to.
we can always have multiple conditions within "where". We just have to seperate them with "AND"
7 select MIN(timetofeed) "Minimum Feeding Time" , MAX(timetofeed) "Maximum Feeding Time"
from animal;
// MIN AND MAX are used when we need to find the maximum and the minimum data from a column. We can also specify the column name for data retrieved. Here we have used "Minimum Feeding Time" and "Maximum Feeding Time".
8 select AVG(timetofeed) "Average time" from animal
where acategory = 'rare';
/// AVG is used to find the average data from a column in the take. Where is used to add the extra condition.
* EC1
select aname from animal
where timetofeed = ( select max(timetofeed) from animal );
/// Max is used to find the longest feeding time.
* EC2
select aname from animal
where timetofeed < 1.2 * ( select avg(timetofeed) from animal );
// Avg is used to find the average time