CS代写 INFS1603/COMM1822

INFS1603/COMM1822
Demo Script

Table of Content

Copyright By PowCoder代写 加微信 powcoder

Introduction 2
Week 3A SQL Basics – Table Creation and Select Statements 3
Week 4 SQL – Restricting Rows, Single-row Functions 8
Week 5 Joining Table 17
Week 7 Aggregate Functions (GROUP BY) 23
Week 8 Data Manipulation Language (DML) 26
Week 9 Subqueries and Merge Statements 32
Week 10 Views 37

You can “ + click” on the week name (e.g., Week 3B SQL Basics) to access the demo scripts.

Introduction

In the lab, students will need to get familiar with a range of personal computer software, including SQLDeveloper. The labs are available for students to do this on a self-taught basis, using the recommended workbooks or equivalent alternatives. Please see Course Outline to see the lab information (e.g., location and time).
This document provides lab demos.
Last update in January 2022.

Run the following first

—————————————————————
ALTER SESSION SET nls_date_format = ‘DD-MON-RR’;
–Comment and will be by ignored by Oracle SQL Developer
Week 3A SQL Basics – Table Creation and Select Statements
—————————————————————-
— SELECT Statement —
—————————————————————-

— [1] Select all data from a table, in this case, the employees table:
SELECT * FROM employees;

— [2] Retrieve column names / metadata from a table:
DESCRIBE employees;

— [3] Select one column only:
SELECT last_name
FROM employees;

— [4] Select more than one column:
SELECT employee_id, last_name, first_name
FROM employees;

— [5] Four common heading alias methods
— Note: (a) Use of double quotes (“) for heading alias
— (b) Do not use single quotes (‘) for alias
SELECT employee_id AS Employee,
employee_id AS “Employee Id”,
last_name Surname,
first_name “First Name”
FROM employees;

—————————————————————-
— SELECT DISTINCT Statement —
—————————————————————-

— [6] Without DISTINCT, you will retrieve 107 records:
SELECT job_id FROM employees;

— [7] With DISTINCT, you only retrieve distinct/unique jobs
— in our example, 19 distinct jobs:
SELECT DISTINCT job_id FROM employees;

— [8] UNIQUE will give you the same result
SELECT UNIQUE job_id FROM employees;

— [9] Select distinct department_id
— it returns 12 records:
SELECT DISTINCT department_id FROM employees;

— [10] Select distinct the manager_id
— it returns 19 records:
SELECT DISTINCT manager_id FROM employees;

— [11] Select distinct combinations of department_id and manager_id
— it returns 28 records:
SELECT DISTINCT department_id,
manager_id
FROM employees;

—————————————————————-
— Concatenation —
—————————————————————-

— [12] Use single quotes to display a string/literal,
— Use double bars (||) to concatenate strings.
SELECT first_name ||
last_name ||
‘ is a(n) ‘ ||
job_id AS “Employee’s Job”
FROM employees;

— [13] Use q’< and >‘ as delimiters to
— to include a quotation mark in the literal:

SELECT first_name ||
last_name ||
q’<'s job id is >‘ ||
job_id AS “Employee’s Job”
FROM employees;

—————————————————————-
— Arithmetic operations —
—————————————————————-

— [14] Use multiplications and additions in the query:

SELECT last_name,
salary * 0.10 AS “Increase”,
salary * 1.10 AS ” “,
100 * 12 AS “Expenses”,
(salary * 1.10) + (100 * 12) AS ” w/Exp”
FROM employees;

—————————————————————-
— Week 3 Part B
— Table Creation, Management, Constraints
—————————————————————-

—————————————————————-
— Assume you know:
— (a) What is a Primary Key
— (b) What is a Foreign Key
— (c) What is a Constraint
—————————————————————-

—————————————————————-
— Delete Tables: —
— You can only delete a table if it does not violate —
— any constraints. For instance, you can only delete —
— a table if no column from any other tables references —
— this table. Thus, the construction or deletion of —
— constraints will determine the order of creating or —
— removing tables. —
—————————————————————-
— Tidy Up tables if they exists in the database
— You do not have to worry if you see error message or similar
— such as:
— SQL Error: ORA-00942: table or view does not exist
— 00942. 00000 – “table or view does not exist”

DROP TABLE departments_history;
DROP TABLE employees_history;
DROP TABLE employees_hist_2011;
DROP TABLE locations_temp;
DROP TABLE global_region_temp;

—————————————————————-
— Create a : —
— (a) Without Data —
— (b) With Data —
—————————————————————-

— [1] Create a table without data!
— Create a table called departments_history.
— Initialise the table with default values.
CREATE TABLE departments_history
department_id NUMBER(4),
department_name VARCHAR2(30),
location_id NUMBER(4),
approved_employee_id NUMBER(6),
active_flag CHARACTER(1) DEFAULT ‘Y’,
modify_date DATE DEFAULT SYSDATE

— [2###] Returns the properties of the table which you just created
DESCRIBE departments_history;

— [3] No data is added
SELECT * FROM departments_history;

— [4] List all the tables in your database.
SELECT table_name
FROM user_tables
ORDER BY table_name;

—————————————————————-

— [5]Create a Table With Data:
— Create employees_history table data from employees table data
— Note: not all the constraints are not created
CREATE TABLE employees_history
AS (SELECT * FROM employees);

DESCRIBE employees_history;

— [7]Data is now added!
SELECT * FROM employees_history;

—————————————————————-
— Adding, modifying and removing a column from a table —
—————————————————————-
— [8]Add a new column
ALTER TABLE employees_history
ADD (modify_date DATE);

DESCRIBE employees_history;

— [10] Remove a column
ALTER TABLE employees_history
DROP COLUMN phone_number;

— [11] Modify a column
— Change email from VARCHAR2(25) to VARCHAR2(30)
— Remember: varchar = letters/numbers
ALTER TABLE employees_history
MODIFY (email VARCHAR2(30));

— [12] It will also reflect in your table descriptions
DESCRIBE employees_history;

—————————————————————-
— Making a column to be UNUSED —
—————————————————————-

— [13] Instead of removing a column immediately, which requires
— excusive locking* of a table, you might want to make
— the column unavailable to the users by defining the column
— as UNUSED.
— *) we will cover locking in a later course
ALTER TABLE employees_history
SET UNUSED (email);

— [14] Notice that you don’t see the email column anymore
DESCRIBE employees_history;

— [15] You can remove all the unused columns at a later stage
ALTER TABLE employees_history
DROP UNUSED COLUMNS;

— [16###]
DESCRIBE employees_history;

—————————————————————-
— Renaming a table, and recoving a deleted table —
—————————————————————-

— [17] Renaming a table
RENAME employees_history TO employees_hist_2011;

— [18###]
DESCRIBE employees_hist_2011;

— [19] Notice the table name changed
SELECT table_name FROM USER_TABLES;

— [20] Deleting a table
DROP TABLE employees_hist_2011;

— [21]’Oh No – delete the wrong table’
— Recovering a table
— Find the deleted table in the recycle bin like in Windows
SELECT object_name, original_name
FROM recyclebin;

— [22] Recovering from the recycle bin
FLASHBACK TABLE employees_hist_2011 TO BEFORE DROP;

— [23###]
DESCRIBE employees_hist_2011;

— [24] To delete a table permanently by referencing
— the exact object name, copy and paste the object name
— from Query 19
PURGE TABLE “BIN$VxWQy4n/0l/gUKuVSZ5rEA==$0”;

— [25] Alternatively, you can empty a recycle bin like in Windows
PURGE recyclebin;

— [26]Your recycle bin should be empty
SELECT object_name, original_name
FROM recyclebin;

—————————————————————-
— End of Oracle Lab Demo Week 8 —
—————————————————————-

Week 4 SQL – Restricting Rows, Single-row Functions
—————————————————————-
— The WHERE clause —
—————————————————————-

— [1] Select a records/rows based on a condition
— using a specific numeric value:
SELECT employee_id,
last_name,
first_name,
Manager_id
FROM employees
WHERE manager_id = 102;

— [2] Select records/rows based on a condition
— using a specific character string:
SELECT employee_id,
last_name,
First_name
FROM employees
WHERE last_name = ‘King’;

— [3] Select records/rows based on a condition
— using a specific date:
SELECT employee_id,
last_name,
first_name,
FROM employees
WHERE hire_date = ’08-MAR-08′;

— Hint: Not working? Date must be in the format DD-MON-RR!
— This can be set up via > Tools > Preferences > Database > NLS
— Or via the command ALTER SESSION SET nls_date_format = ‘DD-MON-RR’

—————————————————————-
— Comparing values —
—————————————————————-

— [4] Condition based on values being above a numeric value (not inclusive)
SELECT employee_id,
last_name,
first_name,
FROM employees
WHERE salary > 10000;

— [5] Condition based on a range of numeric values (not inclusive)
SELECT employee_id,
last_name,
first_name,
FROM employees
WHERE salary > 10000 AND salary < 11000; -- [6] Condition based on a range of numeric values (inclusive) SELECT employee_id, last_name, first_name, FROM employees WHERE salary >= 10000 AND salary <= 11000; -- [7] Condition based on NOT selecting a value SELECT department_id, department_name, location_id FROM departments WHERE location_id <> 1700;

— [8] Alternatively, you can use BETWEEN … AND … operator.
— Border values are included in result:
SELECT employee_id,
last_name,
first_name,
FROM employees
WHERE salary BETWEEN 10000 AND 11000;

— [9] Selecting a range of records based on a character
SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name > ‘T’;

— [10] Comparing alphanumeric values, i.e. values contain numbers,
— alphabets and space; it is case sensitive:

SELECT location_id, postal_code, country_id
FROM locations
WHERE postal_code = ‘YSW 9T2’;

— Hint: How does it work? See http://www.asciitable.com/ (for example)

— [10] Can also use >, <, >= and <= for characters/string -- Has the first characteran Ascii value of higher than '9'? SELECT location_id, postal_code, country_id FROM locations WHERE postal_code > ‘9’;

— [10] Condition selecting a range of strings
SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name >= ‘King’ AND last_name <= 'Lee'; -- [11] Alternatively, you can use the BETWEEN ... AND ... operator -- but you must specify the lower limit first: SELECT employee_id, last_name, first_name FROM employees WHERE last_name BETWEEN 'King' AND 'Lee'; -- [12] If you specify the upper limit first, no record will be retrieved: SELECT employee_id, last_name, first_name FROM employees WHERE last_name BETWEEN 'Lee' AND 'King'; -- [13] Remember value inside the quotes is case sensitive: SELECT employee_id, last_name, first_name FROM employees WHERE last_name = 'King'; -- [14] you will not retrieve any records if 'KING' is entered: SELECT employee_id, last_name, first_name FROM employees WHERE last_name = 'KING'; -- [15] Selecting a range of values based on a date SELECT employee_id, last_name, first_name, FROM employees WHERE hire_date >= ’08-MAR-08′;

— [16] Both Mar and MAR work here
— (because it is a date, not a string)
SELECT employee_id,
last_name,
first_name,
FROM employees
WHERE hire_date >= ’08-Mar-08′;

—————————————————————-
— IN, NOT, NULL, and LIKE —
—————————————————————-

— [17] Find employees working in the department of
–Human Resources (id = 40) or IT (60) – 6 rows
SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE department_id IN (40, 60);

— [18] Find employees NOT working in the
— department of HR(deparment id = 40) or IT (60) – 100 rows
SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE department_id NOT IN (40, 60);

— Hint: There are 107 employees but the two above SELECT statements
— only retrieved 106 employees. Why is one employee is missing?
— One employee s/he does not have a department_id.
— You can only find the missing employee by checking for NULL.

— [19] Checking for NLULL values in department_id:
SELECT employee_id, department_id
FROM employees
WHERE department_id IS NULL;

— [20] Comparisons including ‘=’ do not work with NULL
SELECT employee_id, department_id
FROM employees
WHERE department_id = NULL;
— should use WHERE department_id is NULL;

—————————————————————-
— Wildcards —
—————————————————————-

— [21] ‘%’ (percentage): match any characters
— Find all employees with surname starts with ‘S’:
SELECT employee_id,
last_name,
First_name
FROM employees
WHERE last_name LIKE ‘S%’;

— [22] Find all employees with surname ends with double ‘l’s – 6 rows
SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name LIKE ‘%ll’;

— [23] Find all employees with surname contains double ‘l’s – 13 rows
SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name LIKE ‘%ll%’;

— [23] ‘_’ (underscore): match one character
— Find employee id – match patterns starts with 1,
— any number in the middle, and ends with 9,
— and start working in the year 2008.
SELECT employee_id, last_name, first_name, hire_date
FROM employees
WHERE employee_id LIKE ‘1_9’ AND hire_date LIKE ‘%-08’;

—————————————————————-
— ORDER BY —
—————————————————————-

— [24] Hint: for rules of precedence
SELECT employee_id, last_name, first_name
FROM employees
ORDER BY last_name;

— [25] Sort by column number (here second column) in the SELECT list
— Not recommended! But you will see it is commonly used in
— system/auto-generated codes:
SELECT employee_id, last_name, first_name
FROM employees
ORDER BY 2;

— [26] Sort by both manager_id and then hire_date:
SELECT employee_id,
last_name,
manager_id,
FROM employees
ORDER BY manager_id, hire_date;

— [27] Descending ordering (instead of the default ascending ordering)
— Same query as above, sorted by both manager_id and hire_date
— but now hire_date is sorted in descending order:
SELECT employee_id,
last_name,
manager_id,
FROM employees
ORDER BY manager_id, hire_date DESC;

—————————————————————-
— Section 2 – Single-Row Functions —
—————————————————————-

— [1] Case Conversion Functions
— Examples of using UPPER(), LOWER() and INITCAP():
SELECT last_name,
UPPER(last_name),
LOWER(last_name),
INITCAP(email)
FROM employees
WHERE UPPER(last_name) = ‘KING’;

— [2] Character Manipulation Functions
— String starts from one (1) and not zero (0)
— Padding related to the desired length of the result, not the
— number of characters your want to insert:
SELECT first_name,
SUBSTR(first_name,1,3),
LENGTH(first_name),
LPAD(salary, 10, ‘*’),
RPAD(salary, 10, ‘*’)
FROM employees
WHERE UPPER(last_name) = ‘KING’;

— [3] Number Functions
— The round function needs the position of the rounding,
— e.g., 0 is rounding to the decimal place
— 1 is rounding to one digit behind the decimal place
— -1 is rounding to one digit before the decimal place
— 2 5 . 0 0
— (-2) (-1) (0) (1) (2)
— Example of the ROUND Function:
SELECT commission_pct,
commission_pct * 100,
ROUND(commission_pct * 100,-1),
commission_pct * 10,
ROUND(commission_pct * 10,0),
ROUND(commission_pct * 10,1)
FROM employees
WHERE department_id = 80 AND employee_id BETWEEN 151 AND 154;

— [4] The Truncation function cuts later numbers off (does not round)
— It doesn’t reduce the length of the string though
— Example of the TRUNC Function:
SELECT commission_pct,
commission_pct * 100,
TRUNC(commission_pct * 100,-1),
commission_pct * 10,
TRUNC(commission_pct * 10,0),
TRUNC(commission_pct * 10,1)
FROM employees
WHERE department_id = 80 AND employee_id BETWEEN 151 AND 154;

— [5] How many years here? – No rounding, no truncation:
SELECT last_name,
hire_date,
MONTHS_BETWEEN(sysdate,hire_date)/12 “Approx.(years)”
FROM employees
ORDER BY hire_date DESC;

— [6] How many years here? – With rounding: (5 places after the decimal point)
SELECT last_name,
hire_date,
ROUND(MONTHS_BETWEEN(sysdate,hire_date)/12,5) “Approx.(years)”
FROM employees
ORDER BY hire_date DESC;

— [7] How many years here? – With truncating: (5 places after the decimal point)
SELECT last_name,
hire_date,
TRUNC(MONTHS_BETWEEN(sysdate,hire_date)/12,5) “Approx.(years)”
FROM employees
ORDER BY hire_date DESC;

—————————————————————-
— Dual Table – A “dummy table” —
—————————————————————-

— Hint: Purpose is for calculations and system functions.
— This dummy table is used when you’re not actually interested in
— the data, but instead just wan to execute calculation/functioms:

— [8] Get the system date:
SELECT sysdate FROM dual;

— [9] What is actually in the table: (one column, one row, one value)
SELECT * FROM dual;

— [10] ABS is mathematical function that returns the absolute (positive)
— value of the specified numeric expression:
SELECT -12.5, ABS(-12.5)
FROM dual;

— [11] Demonstrate the use of POWER function:
SELECT 2*2*2, POWER(2,3)
FROM dual;

— [12] An example of using DATE functions to calculate how many
— years (approx.) have the employees work for the company
— Here you use the months_between function, to calculate the time
— between the current date and the hire date
— this is divided by 12 to show years. It is then truncated to 1
— decimal place:
SELECT last_name,
hire_date,
TRUNC(MONTHS_BETWEEN(sysdate,hire_date)/12,0) “Approx. (years)”
FROM employees

程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com