Preparation for the workshop – ready, set ……
▪ connect to Flux – flux.qa and be ready to answer questions
▪ login to the Oracle database (local install, MoVE or ORDS)
▪ on campus students nominate someone as your table leader for this
Copyright By PowCoder代写 加微信 powcoder
INFORMATION TECHNOLOGY
Structured Query Language (SQL) – Part I
Workshop 2022 S1
Access tables via DRONE.tablename in the Monash Oracle database
Anatomy of an SQL SELECT Statement
SELECT drone_id, drone_pur_date, drone_flight_time FROM drone
WHERE drone_pur_price > 2000;
Note slides use tablename not drone.tablename – command as run by drone account user
Predicate / search condition
SQL SELECT Statement – Usage
What column/s to display
SELECT drone_id, drone_pur_date, drone_flight_time FROM drone
WHERE drone_pur_price > 2000;
What table(s) the data is to be drawn from
What row/s to retrieve – the RESTRICTION to place on the rows retrieved
Run this command against the Oracle Database
Flux Task coming up ….
Q1. List all the drones which cost from $3000 to $5300 to purchase (multiple answers may be selected):
A. SELECT * FROM drone where drone_pur_price BETWEEN 3000 AND 5300;
B. SELECT * FROM drone where drone_pur_price >= 3000 or drone_pur_price <= 5300;
C. SELECT * FROM drone where drone_pur_price IN (3000,5300);
D. SELECT * FROM drone where drone_pur_price >= 3000 and drone_pur_price <= 5300;
E. SELECT * FROM drone where drone_pur_price >= 3000 or <= 5300;
SQL Predicates or Search Conditions
▪ The search conditions are applied on each row, and the row is returned if the search conditions are evaluated to be TRUE for that row.
▪ Comparison
– Compare the value of one expression to the value of another expression. – Operators: =, !=,< >, <, >, <=, >=
– Example: drone_pur_price > 2000
– Test whether the value of an expression falls within a specified range of
– Operator: BETWEEN
– Example: drone_pur_price BETWEEN 3000 AND 5300 (both are inclusive)
SQL Predicates or Search Conditions
▪ Set Membership
– To test whether the value of expression equals one of a set of values. – Operator: IN
– Example : dt_code in (‘DMA2′,’DSPA’) -> which drones of this type?
▪ Pattern Match
– To test whether a string (text) matches a specified pattern. – Operator: LIKE
– Patterns:
• % character represents any sequence of zero or more character.
• _ character represents any single character. – Example:
• WHERE dt_model LIKE ‘DJI%’ -> drone type models starting with DJI • WHERE train_code LIKE ‘__I__’ -> drone types with a train_code with
an I in the middle
Flux Task coming up ….
Q2. To list the rentals which have not been returned, the SQL would be:
A. select * from rental where rent_in = null;
B. select * from rental where rent_in is null;
C. select * from rental where rent_in is not null;
D. select * from rental where rent_in is empty;
SQL Predicates or Search Conditions
– To test whether a column has a NULL (unknown) value. – Example: WHERE rent_in IS NULL.
▪ Use in subquery (to be discussed in the future) – ANY, ALL
What row will be retrieved?
▪ Predicate evaluation is done using three-valued logic. – TRUE, FALSE and UNKNOWN
▪ DBMS will evaluate the predicate against each row. ▪ Row that is evaluated to be TRUE will be retrieved. ▪ NULL is considered to be UNKNOWN.
Combining Predicates
▪ Logical operators – AND, OR, NOT
– An expression is evaluated LEFT to RIGHT
– Sub-expression in brackets are evaluated first – NOTs are evaluated before AND and OR
– ANDs are evaluated before OR
– Use of BRACKETS better alternative
Truth Table
• AND is evaluated to be TRUE if and only if both conditions are TRUE
• OR is evaluated to be TRUE if and only if at least one of the conditions
is TRUE AND
U = Unknown
Unknown = NULL in relational database
Flux Task coming up ….
Q3. Find all the training courses which are not run by the trainer with trainer_id 1 or the trainer with trainer_id 2:
A. select * from training_course where trainer_id <>1 or trainer_id <> 2;
B. select * from training_course where trainer_id <> (1 or 2);
C. select * from training_course where trainer_id <>1 and trainer_id <> 2;
D. select * from training_course where trainer_id <> (1 and 2);
Arithmetic Operations
▪ Can be performed in SQL.
▪ For example, what is the drone cost per minute:
select drone_id, drone_cost_hr/60 from drone;
Formatting?
Run this command against the Oracle Database
Oracle NVL function
▪ It is used to replace a NULL with a value (numeric OR character/string)
SELECT stu_nbr, NVL(enrol_mark,0), NVL(enrol_grade,’WH’)
FROM enrolment;
Oracle NVL function continued
select rent_no, drone_id, rent_out, nvl(rent_in,’Still out’) from rental;
Run this command against the Oracle Database
What happens, why?
Renaming Column
▪ Note column heading from drone_cost_hr/60 ▪ Use the word “AS”
– New column name in ” ” to maintain case, special characters or spacing
select drone_id, drone_cost_hr/60 as costpermin from drone;
select drone_id, drone_cost_hr/60 as “COST/MIN” from drone;
Sorting Query Result
▪ “ORDER BY” clause – tuples have no order
– Must be used if more than one row may be returned
▪ Order can be ASCending or DESCending. The default is ASCending. – NULL values can be explicitly placed first/last using “NULLS
LAST” or “NULLS FIRST” command
▪ Sorting can be done for multiple columns.
– order of the sorting is specified for each column. ▪ Example:
select drone_id, drone_flight_time from drone order by drone_flight_time desc, drone_id;
Group Task coming up ….
Q4. Write a query to satisfy the following requirements:
● Show the rental number, when the rental was taken out and when the rental was returned
○ no attribute formatting is necessary, use the table column names directly
● The output should show
○ the most recently returned rental first
○ show nulls at the end of the output
Obtain the ids of those drones which have been rented?
Run this command against the Oracle Database
Removing Duplicate Rows in the Query Result
▪ Use “DISTINCT” as part of SELECT clause
– use with care
– Which of our drones have been rented?
Run this command against the Oracle Database and note the difference
select distinct drone_id from rental
order by drone_id;
▪ For database students are required to use ANSI JOINS
– placing the join in the where clause is not acceptable and will be marked as
incorrect for all assessment purposes
• such a join is sometimes known as “implicit join notation” – effectively a cross join and then restricted by the where clause
▪ ANSI JOINS – ON
• the general form which always works, hence the syntax we tend to use
• FROM trainer JOIN training_course ON trainer.trainer_id = training_course.trainer_id – USING
• requires matching attribute/s in the two tables
• FROM trainer JOIN training_course USING (trainer_id) – NATURAL
• requires matching attribute/s in the two tables
• FROM trainer NATURAL JOIN training_course
SQL EQUI JOIN
TRAINING_COURSE
Special form of EQUI: SQL NATURAL JOIN
TRAINING_COURSE
Group Task coming up ….
Q5. Find the full name and contact number for all customers who have completed a training course run by trainer id 1
1. Identify the source tables
2. Build the JOIN table by table (here use ON),
maintain all attributes so you can see what is
3. Limit rows (where) and attributes (select list)
4. Order by customer name
Special note: the Oracle symbol to concatenate two strings is ||
Output required:
▪ SQL statement, clause, predicate. ▪ Writing SQL predicates.
– Comparison, range, set membership, pattern matching, is NULL
– Combining predicates using logic operators (AND, OR, NOT) ▪ Arithmetic operation.
– NVL function
▪ Column alias.
▪ Ordering (Sorting) result. ▪ Removing duplicate rows. ▪ JOIN-ing tables
Oracle Date Data Type Revisited
Oracle Date Datatype
▪ Dates are stored differently from the SQL standard – standard uses two different types: date and time – Oracle uses one type: DATE
• Stored in internal format contains date and time
– Julian date as number (advantage can use arithmetic)
• Output is controlled by formatting via to_char
– select to_char(sysdate,’dd-Mon-yyyy’) from dual;
» 20-Apr-2021 – select
to_char(sysdate,’dd-Mon-yyyy hh:mi:ss AM’) from dual;
» 20-Apr-2020 02:51:24 PM
• DATE data type must be formatted with TO_CHAR when selecting for display. to_char can also be used to format numbers
• Text representing date must be formatted with TO_DATE when comparing or inserting/updating.
Report drones purchased after 1st March 2020?
Returning to Oracle NVL function ▪ It is used to replace a NULL with a value.
select rent_no, drone_id, rent_out, nvl(rent_in,’Still out’) from rental;
● rent_in is date, ‘Still out’ is string (char)
select rent_no, drone_id, to_char(rent_out,’dd-Mon-yyyy’) as dateout,
nvl(to_char(rent_in,’dd-Mon-yyyy’),’Still out’) as datein
from rental;
Current Date
• Current date can be queried from the DUAL table (used to evaluate expressions/functions) by calling SYSDATE
to_char(sysdate, ‘dd-Mon-yyyy hh:mi:ss AM’) AS current_datetime
FROM dual;
• Oracle internal attributes include: – sysdate: current date/time
– systimestamp: current date/time as a timestamp – user: current logged in user
What is the current date and time according to Oracle
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com