IS6146 Databases for Management Information Systems Lecture 2: SQL II

1
IS6146 Databases for Management Information
SystemsLecture 2 SQL II Joins, Updates,
Insertions, and Deletions

• Rob Gleasure
• R.Gleasure_at_ucc.ie
• robgleasure.com

2
IS6146

• Todays session
• More SQL
• Insert Into
• Update
• Delete
• Joins
• Exercise

3
More on the SQL DML

• So far weve looked at getting data from specific
  tables, but there are still two parts of the Data
  Manipulation Language (DML) we havent covered
• The DML has four main elements
• Select
• Insert Into
• Update
• Delete
• Also, what if our data isnt on one table?

4
Insert Into

• We use INSERT INTO queries to add new tuples (aka
  records, rows) into a table
• The basic structure of an INSERT-INTO query is as
  follows
• INSERT INTO
• table_name1 (column_name1, column_name2, )
• VALUES
• value1, value2,

5
Insert Into

• For example, say we want to insert a new student
  in a Students table with a Student_ID of 12345678
  and a Name of Jane Smith, we might have the
  following
• INSERT INTO Students (Student_ID, Name) VALUES
  (12345678, Jane Smith)
• Note that we insert the data in the form of a new
  tuple (aka record, row) and if we do not specify
  a column, we have to provide data for each column
  in the new record
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_insert_cols

6
Update

• We use UPDATE queries to modify existing tuples
  (aka records, rows) in a table
• The basic structure of an UPDATE query is as
  follows
• UPDATE
• table_name1, table_name2,
• SET
• col_name1 value1, col_name2 value2,
• WHERE
• some_columnsome_value

7
Update

• For example, say we want to change the previously
  added student record to be Janet Smith, we
  might have the following
• UPDATE Students SET (Name Janet Smith) WHERE
  (Student_ID12345678)
• Careful with this, if you dont set a WHERE
  condition you will change every record in the
  database
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_update

8
Delete

• We use DELETE queries to remove existing tuples
  (aka records, rows) in a table
• The basic structure of a DELETE query is as
  follows
• DELETE FROM 
• table_name WHERE 
• some_columnsome_value

9
Delete

• For example, say we want to delete the previously
  added student record, we might have the following
• DELETE FROM Students WHERE (Name Janet
  Smith)
• Again be careful, if you dont set a WHERE
  condition you delete every record in the database
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_delete

10
Joins

• Joins combine tuples (aka rows, records) from
  multiple tables
• Joins come in several forms
• Inner Joins
• Left Joins
• Right Joins
• Full Joins
• Unions
• Select Into/Into Select

11
Inner Joins

• Inner Joins return the specified columns at the
  intersection of two or more tables
• Image from http//www.w3schools.com/

12
Inner Joins

• Inner Joins are the most basic (and probably most
  common) type of join
• The basic structure of an INNER JOIN query is as
  follows
• SELECT 
• column_name(s) FROM 
• table1 INNER JOIN 
• table2 ON 
• table1.column_nametable2.column_name

13
Inner Joins

• For example, say in addition to the previously
  added student record, a separate REFERENCES table
  stores student details. We could retrieve
  Student_IDs included in both tables as follows
• SELECT Students.Student_ID, Student.Name,
  References.Ref_Details
• FROM Students INNER JOIN References
• ON Students.Student_ID References.Student_ID
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_select_join_inner

14
Left Joins

• Left Joins (sometimes called left outer joins)
  return all of the specified columns from the
  first table and their intersection (where it
  exists) with two or more tables
• Image from http//www.w3schools.com/

15
Left Joins

• The big difference here is that columns from our
  first table that have no corresponding entry in
  the latter tables are still returned (with null
  signifying the missing entry)
• The basic structure of an LEFT JOIN query is as
  follows
• SELECT 
• column_name(s) FROM 
• table1 LEFT JOIN 
• table2 ON 
• table1.column_nametable2.column_name

16
Left Joins

• For example, what if some students do not have
  references and we still want to see their
  Student_ID and Name? We could retrieve these
  records as follows
• SELECT Students.Student_ID, Student.Name,
  References.Ref_Details
• FROM Students LEFTJOIN References
• ON Students.Student_ID References.Student_ID
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_select_join_left

17
Right Joins

• Right Joins (sometimes called right outer joins)
  return all of the specified columns from the
  latter tables and their intersection (where it
  exists) with the first table
• Image from http//www.w3schools.com/

18
Right Joins

• Here, columns from our latter tables that have no
  corresponding entry in the first tables are still
  returned (with null signifying the missing entry
  in the first table)
• The basic structure of an RIGHT JOIN query is as
  follows
• SELECT 
• column_name(s) FROM 
• table1 RIGHT JOIN 
• table2 ON 
• table1.column_nametable2.column_name

19
Right Joins

• For example, what if some references have been
  received before the corresponding student records
  have been created and we still want to see them?
  We could retrieve these records as follows
• SELECT Students.Student_ID, Student.Name,
  References.Ref_Details
• FROM Students RIGHT JOIN References
• ON Students.Student_ID References.Student_ID
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_select_join_rightss-1

20
Full Outer Joins

• Full Outer Joins return all of the specified
  columns from the first and latter tables,
  including entries in either table with no
  corresponding table in the other(s)
• Image from http//www.w3schools.com/

21
Full Outer Joins

• Here, columns from our any table that have no
  corresponding entry in other tables are still
  returned (with null signifying the missing entry)
• The basic structure of an FULL OUTER JOIN query
  is as follows
• SELECT 
• column_name(s) FROM 
• table1 FULL OUTER JOIN 
• table2 ON 
• table1.column_nametable2.column_name

22
Full Outer Joins

• For example, what if we want to see the full set
  of records to determine which student records we
  have not yet created and which references are
  still outstanding? We could retrieve these
  records as follows
• SELECT Students.Student_ID, Student.Name,
  References.Ref_Details
• FROM Students FULL OUTER JOIN References
• ON Students.Student_ID References.Student_ID

23
Unions

• Unions are a bit different, they tend to be used
  for retrieving comprehensive sets of similar
  records
• Unions combine two or more SELECT queries,
  provided the following conditions are met
• Each SELECT query must have the same number of
  columns
• Each merged column must share data types
• Columns in each SELECT query must be in the same
  order

24
Unions

• The basic structure of a UNION query is as
  follows
• SELECT
• column_name(s)
• FROM
• table1
• UNION
• SELECT
• column_name(s)
• FROM
• table2
• Note that this will automatically return only
  distinct records, though we can used UNION ALL if
  we want to include duplicates

25
Unions

• For example, what if our students table only
  stores enrolled students, whereas another
  Provisional_Students table stores students in
  provisional places awaiting confirmation? We
  could retrieve these records as follows
• SELECT Name FROM Students
• UNION
• SELECT Name FROM Provisional_Students
• ORDER BY Name
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_select_union_all2

26
Select Into

• Sometimes we want to copy an entire schema into a
  new table. We can do this using SELECT INTO as
  follows
• SELECT 
• column_name(s)INTO 
• newtableFROM 
• table1
• This often ends up as a way of backing things up,
  e.g.
• SELECT INTO Students_Backup FROM Students

27
Insert Into Select

• Alternatively, sometimes we want to add data from
  one table to an existing table. We can do this
  using INSERT INTO SELECT as follows
• INSERT INTO 
• table2 (column_name(s))SELECT 
• column_name(s)FROM 
• table1
• Example
• http//www.w3schools.com/sql/trysql.asp?filenamet
  rysql_insert_into_select

28
Exercise

• Consider the following problems related to the
  Customers database, what queries best solve them?
• We want to retrieve all customers with addresses
  in Mexico?
• We want to add a new Customer called 'Juan Garcia
  Ramos', with contact name 'Juan Ramos', address
  of 'Tribulete 4356', in the city 'México D.F',
  with a post code of '05029', in the country of
  'Mexico?
• We want to update that customers contact name to
  Anna Ramos?
• We want to delete the same customer?

29
Exercise

1. We want to select ProductName and CategoryID from
   the Products table, along with the SupplierName
   from the Suppliers table using a left join. Hint
   use the SupplierID column in the Products table
   as a foreign key
2. We want to retrieve all cities mentioned in
   customer records and supplier records using a
   union
3. We want create e new Customer_Cities_Backup table
   storing all cities listed in the Customers table
4. We want to add all cities listed in the Suppliers
   table into the Customers table