Preparation for the workshop – ready, set ……
▪ connect to Flux – flux.qa and be ready to answer questions
▪ login to the Oracle database via SQL Developer or ORDS (you will
need to run the CISCO or Global VPN first if you are off campus), or
Copyright By PowCoder代写 加微信 powcoder
SQL Developer on MoVE (no VPN required)
▪ ORDS: https://ora-fit.ocio.monash.edu:8441/ords/sql-developer
10 – SQL Advanced
Workshop 2022S1
▪Subquery – nested, inline, correlated ▪ Views
▪Joins – self join, outer join
▪Set Operators
▪Oracle Functions
List the drone id, carry capacity and hire cost per hour for all drones
Write and run the SQL select statement against the Oracle Database
SQL CASE statement
The CASE statement (Applied Week 9 UPDATE) used in a select list enables a query to evaluate an attribute and output a particular value based on that evaluation.
Drones which can carry objects have been classified by HyFlying as:
▪ ‘No Load’ if the carrying capacity is 0 Kg,
▪ ‘Light Loads’ for carrying greater than 0 but less than 4 Kg, and
▪ ‘Heavy Loads’ for 4 Kg and greater.
For all drones display the drone id, the carrying capacity classification eg ‘No load’ and the drone cost per hour
WHEN test THEN value WHEN test THEN value
ELSE value
END AS column_name
Write and run the SQL select statement against the Oracle Database
SQL CASE statement
WHEN dt_carry_kg = 0 THEN
WHEN dt_carry_kg < 4 THEN
'Light Loads'
'Heavy Loads'
END AS carryingcapacity,
drone_cost_hr
drone.drone_type
NATURAL JOIN drone.drone
▪Subquery – nested, inline, correlated
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
For each drone find the customers (cust_id only) who rented the drone for the highest number of days
A. For each completed rental, list the number of days the drone was out B. For each drone, list the maximum number of days the drone was out
Write and run the SQL select statements against the Oracle Database
( rent_in - rent_out )
drone.rental
rent_in IS NOT NULL
SELECT drone_id,
MAX(rent_in - rent_out) FROM
drone.rental WHERE
rent_in IS NOT NULL GROUP BY
drone_id ORDER BY
Subquery (NESTED)
▪The subquery is independent of the outer query and is executed only once.
Subquery (CORRELATED)
▪the subquery is related to the outer query and is evaluated once for each row of the outer query
▪correlated subqueries can also be used within update statements – outer update occurs based on value returned from subquery
Subquery (INLINE) – Derived table
How many completed rentals have been recorded?
List, for each drone, the number of times the drone has been rented in a completed rental
Write and run the SQL select statements against the Oracle Database
▪For each drone compute the percentage of the company's rentals contributed by that drone
COUNT(*) AS times_rented
drone.rental
rent_in IS NOT NULL
COUNT(*) AS totalrentals
FROM drone.rental
rent_in IS NOT NULL;
Subquery (INLINE)
COUNT(*) AS times_rented,
to_char(COUNT(*) * 100 /(
COUNT(rent_in)
drone.rental
), '990.99') AS percent_overall
drone.rental
rent_in IS NOT NULL
percent_overall DESC;
Use of subquery in INSERT
If you need to both create and insert the data, is there a simpler way to achieve these two tasks?
Assume table created
Simpler approach (using week 7 Applied approach 7.3.4)
Note this table will need subsequent alter commands to, for example, set the PK
▪Subquery – nested, inline, correlated
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
▪A virtual table derived from one or more base tables.
▪Sometimes used as "Access Control" to the database
CREATE OR REPLACE VIEW [view_name] AS
SELECT ... ;
CREATE OR REPLACE VIEW maxdaysout_view AS
MAX(rent_in - rent_out) AS maxdays
drone.rental
rent_in IS NOT NULL
select * from maxdaysout_view
order by drone_id;
▪What objects do I own?
select * from user_objects;
Using Views
▪For each drone find the customers (cust_id only) who rented the drone for the highest number of days
Please note VIEWS MUST NOT be used for Assignment 2 and Exam
▪Subquery – nested, inline, correlated
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
▪Show the name of the manager for each employee.
SELECT empno,
empname, empinit, mgrno
FROM emp.employee;
FROM emp.employee e1 JOIN emp.employee e2 ON e1.mgrno = e2.empno;
Joined rows 1,12
Note some columns have been hidden
Why now only 13 rows?
SELECT e1.empno, e1.empname, e1.empinit, e1.mgrno, e2.empname AS MANAGER
FROM emp.employee e1 JOIN emp.employee e2 ON e1.mgrno = e2.empno
ORDER BY e1.empname;
INNER JOIN
Student Join gives no information for Chris and the student with ID 4
Select * from student s join mark m on s.id = m.id; Note that this is an EQUI JOIN (an inner join)
FULL OUTER JOIN
Student (incomplete) information of both Chris and student with ID 4
select * from
student s full outer join mark m on s.id = m.id;
LEFT OUTER JOIN
Student (incomplete) information of only Chris
select * from
student s left outer join mark m on s.id = m.id;
RIGHT OUTER JOIN
Student (incomplete) information of the student with ID 4
select * from
student s right outer join mark m on s.id = m.id;
Q1. What is the output from the following SQL
select e1.emp_name as name, e2.emp_name as manager
from employee e1 right outer join employee e2
manager_id
on e1.manager_id = e2.emp_id;
project_name
project_cost
emp_salary
Q2. Which of the following shows, for each employee, the total amount of projects they are assigned to? (e.g., Alice is assigned to Alpha with total cost 4000, Bob is assigned to Beta and Gamma with total cost 8000)
A. select e.emp_name, sum(project_cost) as total from employee e left outer join project p on e.emp_id = p.emp_id group by e.emp_name;
B. select e.emp_name, sum(project_cost) as total from employee e right outer join project p on e.emp_id = p.emp_id group by e.emp_name;
C. select e.emp_name, NVL(sum(project_cost),0) as total from employee e left outer join project p on e.emp_id = p.emp_id group by e.emp_name;
D. None of the above
Outer Join
▪ List the number of times ALL drones have been rented
Write and run the SQL select statement against the Oracle Database
Outer Join
▪ List the number of times ALL drones have been rented
▪Subquery – nested, inline, correlated
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
Relational Set Operators
▪ Using the set operators you can combine two or more sets to create new sets (relations) ▪ Union All
–All rows selected by either query, including all duplicates
– All rows selected by either query, removing duplicates (e.g,, DISTINCT on
Union All)
▪ Intersect
–All distinct rows selected by both queries
–All distinct rows selected by the first query but not by the second
▪ All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.
▪ The two sets must be UNION COMPATIBLE (i.e., same number of attributes and similar data types)
List the drone id of all drones
List the drone id of those drones which have been rented Using a set operator which drones have not been rented?
Write and run the SQL select statements against the Oracle Database
▪List the details of drones which have not been rented.
▪Using the UNION operator create a single list of all customers: –for those who have completed training show "Completed training"
–for those who have not completed training show "Not completed training"
Group Task
INTERSECTION
▪Find the trainers who have the same last name as any customer
▪Subquery – nested, inline, correlated
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
See document on Moodle
EXTRACT and DECODE
▪ LPAD allows us to Left PAD a character string - effectively moving it across the output column
– Can use any pad character eg. * or space (if character not specified space is used)
lpad(char,n,char2)/rpad(char,n,char2)
Pads char left/right to size n using char2
select lpad('Page 1', 15, '*') as "Lpad example" from dual;
select rpad('Page 1', 15, '*') as "Rpad example" from dual;
Write and run the SQL select statement against the Oracle Database
Run Script
Run Statement
percent_overall as an output string is left aligned First blank characters after to_char
● one character position for sign (all numerics have a sign)
● blanks to match the format string 990.99 Can remove all leading blanks with LTRIM
Write and run the SQL select statement against the Oracle Database
COUNT(*) AS times_rented,
to_char(COUNT(*) * 100 /(
COUNT(rent_in)
drone.rental
), '990.99') AS percent_overall
drone.rental
rent_in IS NOT NULL
percent_overall DESC;
Produce the above output for the query on the left
Write and run the SQL select statement against the Oracle Database
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com