-
Notifications
You must be signed in to change notification settings - Fork 1
/
Hands-on Pet Rescue
99 lines (78 loc) · 4.24 KB
/
Hands-on Pet Rescue
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
# Hands-on, Practice Aggregate, Scalar, String, Date and Time Functions
-------------------------------------------------------------
-- Create Table
-------------------------------------------------------------
-- Drop the PETRESCUE table in case it exists
drop table PETRESCUE;
-- Create the PETRESCUE table
create table PETRESCUE (
ID INTEGER PRIMARY KEY NOT NULL,
ANIMAL VARCHAR(20),
QUANTITY INTEGER,
COST DECIMAL(6,2),
RESCUEDATE DATE
);
-------------------------------------------------------------
-- Load Data
-------------------------------------------------------------
-- Insert sample data into PETRESCUE table
insert into PETRESCUE values
(1,'Cat',9,450.09,'2018-05-29'),
(2,'Dog',3,666.66,'2018-06-01'),
(3,'Dog',1,100.00,'2018-06-04'),
(4,'Parrot',2,50.00,'2018-06-04'),
(5,'Dog',1,75.75,'2018-06-10'),
(6,'Hamster',6,60.60,'2018-06-11'),
(7,'Cat',1,44.44,'2018-06-11'),
(8,'Goldfish',24,48.48,'2018-06-14'),
(9,'Dog',2,222.22,'2018-06-15')
;
-------------------------------------------------------------
-- AGGREGATE FUNCTIONS, Exercise 02
-------------------------------------------------------------
-- Query A1: Enter a function that calculates the total cost of all animal rescues in the PETRESCUE table.
select SUM(COST) from PETRESCUE;
-------------------------------------------------------------
-- Query A2: Enter a function that displays the total cost of all animal rescues in the PETRESCUE table in a column called SUM_OF_COST.
select SUM(COST) AS SUM_OF_COST from PETRESCUE;
-------------------------------------------------------------
-- Query A3: Enter a function that displays the maximum quantity of animals rescued.
select MAX(QUANTITY) from PETRESCUE;
-------------------------------------------------------------
-- Query A4: Enter a function that displays the average cost of animals rescued.
select AVG(COST) from PETRESCUE;
-------------------------------------------------------------
-- Query A5: Enter a function that displays the average cost of rescuing a dog.
select AVG( COST / QUANTITY ) from PETRESCUE where ANIMAL = 'Dog';
-------------------------------------------------------------
-- SCALAR AND STRING FUNCTIONS, Exercise 01
-------------------------------------------------------------
--Query B1: Enter a function that displays the rounded cost of each rescue.
select ROUND(COST) from PETRESCUE;
-------------------------------------------------------------
--Query B2: Enter a function that displays the length of each animal name.
select LENGTH(ANIMAL) from PETRESCUE;
-------------------------------------------------------------
-- Query B3: Enter a function that displays the animal name in each rescue in uppercase.
select UCASE(ANIMAL) from PETRESCUE;
-------------------------------------------------------------
-- Query B4: Enter a function that displays the animal name in each rescue in uppercase without duplications.
select DISTINCT(UCASE(ANIMAL)) from PETRESCUE;
-------------------------------------------------------------
-- Query B5: Enter a query that displays all the columns from the PETRESCUE table, where the animal(s) rescued are cats. Use cat in lower case in the query.
select * from PETRESCUE where LCASE(ANIMAL) = 'cat';
-------------------------------------------------------------
-------------------------------------------------------------
-- DATA & TIME, Exercise 04
-------------------------------------------------------------
-- Query C1: Enter a function that displays the day of the month when cats have been rescued.
select DAY(RESCUEDATE) from PETRESCUE where ANIMAL = 'Cat';
-------------------------------------------------------------
--Query C2: Enter a function that displays the number of rescues on the 5th day of the month.
select COUNT(*) from PETRESCUE where MONTH(RESCUEDATE)='05';
-------------------------------------------------------------
--Query C3: Animals rescued should see the vet within three days of arrivals. Enter a function that displays the third day from each rescue.
select (RESCUEDATE + 3 DAYS) from PETRESCUE;
-------------------------------------------------------------
--Query C4: Enter a function that displays the length of time the animals have been rescued; the difference between today’s date and the recue date.
select (CURRENT DATE - RESCUEDATE) from PETRESCUE;