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