Transcript Lecture 5

IS2803
Developing Multimedia
Applications for Business (Part 2)
Lecture 5: SQL I
Rob Gleasure
[email protected]
robgleasure.com
IS2803

Today's lecture
 The SQL DML
Introduction to SQL


So once we have our data models in place and our DBMS set up,
how do we get started using our data?
 The SQL (pronounced like sequel) query language
SQL (Structured Query Language) was introduced in the 70’s and
has evolved since to add features such as
 Compatibility with object-oriented programming
 Compatibility with XML
 Compatibility with XQuery
Accessing a DBMS with SQL
The Structure of SQL

SQL can be divided into two parts:
 The Data Manipulation Language (DML)
 Used to create, read, update and delete tuples (CRUD
operations)
 Mostly used by application programmers and
sophisticated/specialised users
 The Data Definition Language (DDL)
 Used to define database structure (relation schemas) and data
access control, as well as integrity constraints and views
 Mostly used by database administrator
Basic SQL query

The basic syntax of SQL queries are as follows:
COMMAND
column_name1, column_name2, …
FROM/SET/VALUES
table_name1, table_name2, …
WHERE
column_name comparison_operator value;

All statements in a semi-colon
The SQL Select statement


The SELECT statement is used to retrieve data from a database into
a result table, or result-set
You can use an asterisk (*) instead of a column name if you wish to
select all columns satisfying the criteria
e.g.
SELECT * FROM my_table;
The SQL Select statement

Let’s open up an online example from W3Schools

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
The SQL Select statement

Sometimes non-primary key columns may contain duplicate values you can also use SELECT DISTINCT when you want to avoid
duplicates in your result-set
e.g.
SELECT DISTINCT * FROM my_table;
The SQL Where clause


A number of comparison operators
are possible with the WHERE
clause
Examples


http://www.w3schools.com/sql/trysql.as
p?filename=trysql_select_between_in
http://www.w3schools.com/sql/trysql.as
p?filename=trysql_select_in
Operator
Description
=
Equal
<>, !=
Not equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
BETWEEN
Between an range of
numbers
LIKE
Searches for patterns
IN
When a specific value
is sought
The ‘LIKE’ condition and Wildcards

Sometimes we want to identify records based on slices of the data
within a cell - we do this using wildcards and the LIKE condition
Wildcard
Description
%
A substitute for zero or more characters
_
A substitute for a single character
[charlist]
Sets and ranges of characters to match
[^charlist]
Matches only a character NOT specified within the
or [!charlist] brackets

Examples



http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_percent
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_underscore
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_charlist&ss=-1
The SQL Where clause (continued)



Numerous clauses can be combined with the keywords AND & OR
e.g.
SELECT * FROM my_table
WHERE val1=‘true’ AND val2=‘false’;
Complex clause can be creates by nesting clauses in parentheses
e.g.
SELECT * FROM my_table
WHERE val1=‘true’ AND (val2=‘false’ OR val2=‘null’);
Example

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_where_and_or
The SQL Order By keyword

The ORDER BY keyword is used to sort the result-set
e.g.
SELECT * FROM my_table ORDER BY NAME;

You may also specify whether you want the returned result-set to
be sorted in ascending or descending order by using the keywords
ASC or DESC
e.g.
SELECT * FROM my_table ORDER BY NAME DESC;

Example

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_orderby_desc
More on the SQL DML



So far we’ve looked at getting data from specific tables, but there
are still two parts of the Data Manipulation Language (DML) we
haven’t covered
The DML has four main elements
 Select
 Insert Into
 Update
 Delete
Also, what if our data isn’t on one table?…
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, …;
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?filename=trysql_insert_cols
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_column=some_value;
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_ID=12345678) ;


Careful with this, if you don’t set a WHERE condition you will
change every record in the database
Example
http://www.w3schools.com/sql/trysql.asp?filename=trysql_update
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_column=some_value;
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 don’t set a WHERE condition you delete
every record in the database
Example
http://www.w3schools.com/sql/trysql.asp?filename=trysql_delete
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
Inner Joins

Inner Joins return the specified columns at the intersection of two or
more tables
Image from http://www.w3schools.com/
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_name=table2.column_name;
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?filename=trysql_select_join_inner
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/
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_name=table2.column_name;
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?filename=trysql_select_join_left
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/
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_name=table2.column_name;
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?filename=trysql_select_join_right&
ss=-1
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/
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_name=table2.column_name;
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;
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
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
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?filename=trysql_select_union_all2
Exercise

Consider the following problems related to the Customers database,
what queries best solve them?
1.
We want to retrieve all customer addresses in Mexico?
2.
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‘?
3.
We want to update that customer’s contact name to ‘Anna Ramos’?
4.
We want to delete the same customer?
Exercise
5.
We want to retrieve all cities mentioned in customer records and
supplier records using a full outer join
6.
We want to retrieve all cities mentioned in customer records and
supplier records using a union
7.
We want to add all cities listed in the Suppliers table into the
Customers table
Want to read more?

Links and references
 Tutorials on the PHP filestream
 http://www.w3schools.com/php/php_ref_filesystem.asp
 http://www.tizag.com/phpT/files.php