CS代写 SQL-86, it has been undergone major revisions in 1992, SQL-92, and 1999, SQ

Introduction to Databases for Business Analytics
Week 3: SQL 1
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:

Copyright By PowCoder代写 加微信 powcoder

PASS Leader:

(Tutor-in-Charge) Liam Li

• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.

Acknowledgement of Country
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f

Introduction to Structured Query Language (SQL)

Relational Languages
Relational DBMS’s query languages (e.g., SQL in Oracle) contain three components:
❑Data Definition Language (DDL): used to specify/modify the database schema.
❑Data Control Language (DCL): used to control the DB (e.g., user rights). ❑Data Manipulation Language (DML): used to retrieve/manipulate data.

❑ SQL = Structured Query Language = Sequel
❑ SQL is the first standard database language.
❑Originally developed by D. Chamberlin and R. Boyce at IBM.
❑The most common SQL standard is the ANSI/ISO SQL. Originally defined in 1988, SQL-86, it has been undergone major revisions in 1992, SQL-92, and 1999, SQL-99. The latest revision is SQL:2011.
❑Microsoft, Oracle, and other vendors have introduced deviations from ANSI SQL.
❑ As a relational language, SQL has three main components: • DataDefinitionLanguage(DDL)
• DataManipulationLanguage(DML) • DataControlLanguage(DCL)

SQL DLL (Data Definition Language) ❑ To create the database structure:
CREATE SCHEMA AUTHORIZATION Creator
Example: CREATE SCHEMAAUTHORIZATION Chris
CREATE DATABASE Database_Name
Example: CREATE DATABASE Student
❑ To create tables:
CREATE TABLE Table_Name (column_name data_type [NULL|NOT NULL] [,…])

❑ Example of table creation:
CREATE TABLE COURSE ( COURSE_CODE COURSE_NAME
PRGRAM_CODE SEMESTER PRIMARY KEY FOREIGN KEY
CHAR (8) VARCHAR (18)
CHAR (4) NOT NULL, CHAR (1),
(COURSE_CODE), (PROGRAM_CODE)
(SQL example from DBMS Microsoft Access)
Another table, may call PROGRAM, exists with PK as PROGRAM_CODE

❑ Example of table creation:
CREATE TABLE COURSE_1 ( COURSE_CODE VarCHAR (8), COURSE_NAME VarCHAR (8), CONSTRAINT COURSE_CODE_PK
(COURSE_CODE) );
PRIMARY KEY

SQL DML (Data Manipulation Language)
❑ ANSI/ISO SQL standard use the terms “tables,” “columns” and “rows” (not relations, attributes, and tuples)
❑The principal SQL DML statements are: • SELECT
• INSERT • UPDATE • DELETE
❑ Complete SQL statements consists of reserved words and user-defined words:
• Thereservedwordsarefixedpartofthelanguage.
• Theuser-definedwordsrepresentthemeaningofthedatatotheuser(e.g.,“users”, “bookings”).

SQL Query Structures
❑The SELECT statement is used to retrieve and display data from one or more tables.
❑ Relation algebra’s selection, projection and join statements can be performed with one single SELECT statement.
❑“SELECT FROM WHERE”
• SELECTclausetellswhichattributes[columns]ofthetuples[rows]matchingtheconditionare
produced as part of the answer.
• FROMclausegivesthenamesofrelation(s)[table(s)].
• WHEREclauseisaconditionthattuples[rows]mustsatisfyinordertomatchthequery.

SQL Query Structures
SELECT[DISTINCT|ALL] {|[column_expressionASnew_name][,…]} FROM table_name [alias] [, …]
[WHERE condition]
[GROUP BY column_list]
[HAVING condition] [ORDER BY column_list];
[] : indicates optional elements.
{} : indicates that the element may or may not appear. | : indicates “or.”
; : indicates the end of the statement.

SQL Query Structures
EMPLOYEE (Employee_ID, Employee_FName, Employee_LName, Employee_HireDate, Employee_Title)
CERTIFIED (Employee_ID, Skill_ID, Certified_Date) SKILL (Skill_ID, Skill_Name, Skill_Description)

SQL Query Structures
SQL allows us to use the keyword ALL to specify that all tuples are to be selected.
SELECT ALL FROM EMPLOYEE;
FROM EMPLOYEE;
* : is a “wild card.”

SQL Query Structures
❑ The SQL syntax is basically: SELECT FROM

;
❑ List all Skill Name and Skill Description: SELECT Skill_Name, Skill_Description FROM SKILL;
❑SQL supports the elimination of duplicates by using the keyword DISTINCT.
SELECT DISTINCT Employee_ID FROM CERTIFIED;

WHERE Clause Options
❑Selecting rows with conditional restrictions
• WHEREclauseisusedtoaddconditionalrestrictionstotheSELECTstatementthatlimitthe
rows returned by the query • Syntax:
SELECT FROM [WHERE [ORDER BY
columnlist tablelist conditionlist ]
columnlist [ASC | DESC] ]; ❑Using comparison operators on character attributes
• May be used to place restrictions on character-based attributes
❑Using comparison operators on dates
• Date procedures are often more software-specific than other SQL procedures

SQL Query Structures
For instance, in the previous example, we only interest in “Basic
Database Manipulation”, we can put a condition in the WHERE clause:
SELECT Skill_Name, Skill_Description
FROM SKILL
WHERE Skill_Name = “Basic Database Manipulation”;

Mathematical Operators for SQL
Mathematical operators that can be used in a WHERE clause for comparison:
= equal to
< less than <= less than or equal to > greater than
>= greater than or equal to <> not equal to

Mathematical Operators for SQL ❑Create a list of product description, product in-date and product price for
products sold by vendor that are not coded “21344”.
SELECT P_Description, P_Indate, P_Price, V_Code FROM PRODUCT
WHERE V_Code <> 21344;
❑ Create a list of product description, product on hand, product minimum, and product price for products with product code less than “1558-QWI”.
SELECT P_Description, P_Onhand, P_Min, P_Price FROM PRODUCT
WHERE P_Code < ‘1558-QWI’ ; ASCII Codes in SQL ❑ All characters/signs are assigned an ASCII (American Standard Code for Information Interchange) code by the computer. ❑ See manual or online for more information on ASCII codes. ❑ The comparisons of strings are made from left to right. This is useful when comparing names. However, it also may create problems: ▪ “2” is sorted as if greater than “11” (because “2” > “1”).
▪ “01/01/2020” is sorted before “12/31/2015” (because “0” < “1”). ▪ Recommendation: use the date/number format instead of string. ASCII Code Logical (Boolean) Operators in SQL Boolean Operators: ❑OR ❑ List products where the vendor code is ‘21344’ or ‘24288’: SELECT P_Description, P_Indate, P_Price, V_Code FROM PRODUCT WHERE V_Code = 21344 OR V_Code = 24288; ❑ List products where either the product in-date is after July 15, 2015 and the product price is less than 50.00 – or the vendor code is 24288. SELECT P_Description, P_Indate, P_Price, V_Code FROM PRODUCT WHERE (P_Price < 50 AND P_Indate > ‘07/15/15’) OR V_Code = 24288;

Special Operators in SQL
❑ BETWEEN is used to define range limits.
❑ IS NULL is used to check whether an attribute value is null.
❑ LIKE is used to check for similar character strings.
❑ IN is used to check whether an attribute value matches a value contains within a subset of listed values.
❑ EXISTS is used to check whether an attribute has a value.

Special Operators in SQL
❑ BETWEEN is used to define range limits.
❑ List the products with prices between 50 and 100.
FROM PRODUCT
WHERE P_Price BETWEEN 50.00 AND 100.00;
FROM PRODUCT
WHERE P_Price >= 50.00 AND P_Price <= 100.00; Special Operators in SQL ❑ LIKE is used to check for similar character strings. ❑ List the details of all vendors whose last name begins with “Smith”. SELECT V_Name, V_Contact, V_AreaCode, V_Phone FROM VENDOR V_Contact LIKE ‘Smith%’; % : wild card Special Operators in SQL ❑ IN is used to check whether an attribute value matches a value contains within a subset of listed values. ❑ List the contents of the product table where the product price is $ 50 or $ 100. FROM PRODUCT WHERE P_Price = 50.00 OR P_Price FROM PRODUCT WHERE P_Price IN (50.00, 100.00); Special Operators in SQL ❑ IS NULL is used to check whether an attribute value is null. ❑ EXISTS is used to check whether an attribute has a value. ❑ Listthedetailsofproductswithexisting(not-NULL)vendorcodes. FROM PRODUCT WHERE V_Code EXISTS; FROM PRODUCT WHERE NOT ISNULL (V_Code); Ordering SQL Results ❑ ORDER BY : produces a list in ascending order
❑ ORDER BY [DESC] : produces a list in descending order
❑ List the details of product table listed by product price in ascending order:
SELECT FROM
P_Description, P_Indate, P_Price, V_Code PRODUCT

Ordering SQL Results
❑ List the details of of products with an in-date before 15
September 1999 and a price less than A$ 50.
❑ Put the results in ascending order of vendor code and descending order of price.
SELECT FROM WHERE ORDER BY
P_Description, P_Indate, P_Price, V_Code PRODUCT
P_Indate < ‘9/15/99’ AND P_Price <= 50.00 V_Code, P_Price DESC; Please email your question(s) to Source: ealt.ca 程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com