Recap on Some SQL and Joins
Bernie Lydon
Bernie.lydon@dbs.ie
What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard.
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL
language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT,
UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Data Manipulation Language (DML)
Data Manipulation Language (DML) is the language that
allows you to use core SQL statements to manipulate data.
SELECT: retrieves rows from the database and enables the
selection of onen or many rows or columns from one/many
tables.
INSERT: adds one or more new rows to a table.
UPDATE: Changes existing data in one or more columns in a
table.
DELETE: Removes rows from a table.
MERGE: Performs insert, update or delete operations on a
target table.
Data Definition Language (DDL)
Data Definition Language (DDL) statements are used to
define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including
all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
Joins
The SQL Joins clause is used to combine records from
two or more tables in a database.
A JOIN is a means for combining fields from two tables
by using values common to each.
Create a New Database called: Joins.accdb
Then create the following Table: Customers
Create a 2nd table called: Orders
Inner Join
The most frequently used and important of the joins is the
INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining
column values of two tables (table1 and table2) based upon
the join-predicate.
The query compares each row of table1 with each row of
table2 to find all pairs of rows which satisfy the join-
predicate.
When the join-predicate is satisfied, column values for each
matched pair of rows of A and B are combined into a result
row. B7IT010 Database Design
INNER JOIN
The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field
Now let us join these two tables using INNER JOIN as follows:
B7IT010 Database Design
Result: Inner Join
This would produce following result:
B7IT010 Database Design
LEFT JOIN
The SQL LEFT JOIN returns all rows from the left table,
even if there are no matches in the right table. This means
that if the ON clause matches 0 (zero) records in right
table, the join will still return a row in the result,but with
NULL in each column from right table.
This means that a left join returns all the values from the
left table, plus matched values from the right table or
NULL in case of no matching join predicate.
B7IT010 Database Design
LEFT JOIN
The basic syntax of LEFT JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_filed = table2.common_field;
Now let us join these two tables using LEFT JOIN as follows:
B7IT010 Database Design
LEFT JOIN
This would produce following result:
B7IT010 Database Design
RIGHT JOIN
The SQL RIGHT JOIN returns all rows from the right
table, even if there are no matches in the left table. This
means that if the ON clause matches 0 (zero) records in
left table, the join will still return a row in the result but
with NULL in each column from left table.
This means that a right join returns all the values from the
right table, plus matched values from the left table or
NULL in case of no matching join predicate.
B7IT010 Database Design
RIGHT JOIN
The basic syntax of RIGHT JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_filed = table2.common_field;
Now let us join these two tables using RIGHT JOIN as follows:
B7IT010 Database Design
RIGHT JOIN
This would produce following result:
B7IT010 Database Design
Recap - Joins
http://w3schools.com/sql/sql_join.asp
INNER JOIN: Returns all rows when there is at least one
match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the
matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and
the matched rows from the left table
B8IT101 Database Design
CREATE Statement (DDL)
Used to create a new table in a database.
*note – you must specify the new tables name, the names of each
column and the datatype for each column.
CREATE TABLE Supplier_tbl
(
SupplierID autoincrement primary key,
Sname char(55) NOT NULL,
Address1 char(60),
Address2 char(60),
Address3 char(60),
Telephone char(25),
Email char(100)
);
B7IT010 Database Design
INSERT INTO Statement (DML)
Data can be entered into tables in 3 ways, one of which is
via SQL.
*pay attention to the order in which you add data – it
should be in the same order as the fields/columns in the
table.
B7IT010 Database Design
UPDATE Statement (DML)
Used to update existing records in a table.
*notice the WHERE clause – it specifies which record(s)
should be updated – without the WHERE clause every
record in a table will be updated!!!!!!!!
UPDATE Supplier_tbl
SET address1=‘Bray Business Park’, address2=‘Bray’
WHERE SupplierID=1;
B7IT010 Database Design
DELETE STATEMENT
The DELETE statement is used to delete rows in a table.
Syntax
DELETE FROM table_name
WHERE column_name = some_value
B7IT010 Database Design
