Transcript - Sundara Ram MattA Jan 12th, 2014

Day 21
- Sundara Ram Matta
[email protected]
Apr 01st, 2015
1
IMPORTANT ANNOUNCEMENTS !!!
• HW #4 due by this Friday – 4/3/2015
• HW #5 has been posted and due date is
next Friday – 4/10/2015.
• Also MyITLab Lesson D – due by Monday
4/6/2015.
2
INTRODUCTION
• SQL is short for Structured Query Language.
• SQL is pronounced “sequel”.
• It is a widely used database language,
providing means of data manipulation (store,
retrieve, update, delete) and database
creation.
3
INTRODUCTION
• Almost all modern RDMS use SQL as standard
database language.
– MS SQL Server
–
–
–
–
Microsoft Access
Oracle
Sybase
Informix
• Although all those RDBMS use SQL, they use
different SQL dialects.
4
SQL DATABASE TABLE
• The foundation of every RDMS is a database
object called TABLE.
• The database table columns (called also table
fields) have their own unique names and have
a pre-defined data types.
• While table columns describe the data types,
the table rows contain the actual data for the
columns.
5
SQL DATABASE TABLE
CustomerID CustomerName
ContactName
Address
City
Obere Str. 57
Berlin
1
Alfreds Futterkiste
Maria Anders
2
Ana Trujillo
Emparedados y
helados
Ana Trujillo
3
Antonio Moreno
Taquería
Antonio Moreno
4
Around the Horn
Thomas Hardy
120 Hanover Sq.
5
Berglunds
snabbköp
Christina Berglund
Berguvsvägen 8
PostalCode Country
12209
Germany
Avda. de la
México D.F.
Constitución 2222
5021
Mexico
Mataderos 2312 México D.F.
5023
Mexico
London
WA1 1DP
UK
Luleå
S-958 22
Sweden
6
SQL INSTRUCTIONS
Keep in Mind That...
• SQL is NOT case sensitive: select is the
same as SELECT
Semicolon after SQL Statements?
• Semicolon is the standard way to separate each
SQL statement in database systesm.
7
MOST IMPORTANT SQL COMMANDS
•
•
•
•
•
•
•
•
•
•
•
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
8
SQL SELECT STATEMENT
• The SELECT statement is used to select data
from a database.
• The result is stored in a result table, called the
result-set.
• SELECT column_name,column_name
FROM table_name;
• SELECT * FROM table_name;
9
SQL SELECT ‘DISTINCT’ STATEMENT
• In a table, a column may contain many duplicate
values; and sometimes you only want to list the
different (distinct) values.
• The DISTINCT keyword can be used to return
only distinct (different) values.
• SELECT DISTINCT column_name,column_name
FROM table_name;
10
SQL WHERE CLAUSE
• The WHERE clause is used to extract only those
records that fulfil a specified criterion.
• SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
11
SQL WHERE CLAUSE
Operator
Description
=
Equal
<>
Not equal. Note: In some versions of SQL this operator may be
written as !=
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
BETWEEN
LIKE
IN
Between an inclusive range
Search for a pattern
To specify multiple possible values for a column
12
SQL AND & OR OPERATORS
• The AND & OR operators are used to filter
records based on more than one condition.
• The AND operator displays a record if both the
first condition AND the second condition are true.
• The OR operator displays a record if either the
first condition OR the second condition is true.
13
SQL UPDATE STATEMENT
• The UPDATE statement is used to update
records in a table.
• UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
14
SQL UPDATE STATEMENT
• Be careful when updating records. If we had
omitted the WHERE clause, in the example
above, like this:
• UPDATE Customers
SET ContactName='Alfred Schmidt',
City='Hamburg';
• Entire ContactName and City Column will get
replaced.
15
SQL DELETE STATEMENT
• The DELETE statement is used to delete records in a
table.
• DELETE FROM table_name
WHERE some_column=some_value;
• Notice the WHERE clause in the SQL DELETE
statement!
• Be very careful when deleting records. You cannot
undo this statement!
16
SQL ORDER BY KEYWORD
• The ORDER BY keyword is used to sort the
result-set by one or more columns.
• The ORDER BY keyword sorts the records in
ascending order by default.
• SELECT column_name, column_name
FROM table_name
ORDER
BY column_name DESC, column_name DESC;
17
SQL ’JOIN’ KEYWORD
• An SQL JOIN clause is used to combine rows
from two or more tables, based on a common
field between them.
• The most common type of join is: SQL INNER
JOIN (simple join).
• An SQL INNER JOIN return all rows when there
is at least one match in BOTH the tables.
18
NEXT WEEK…
• Study the Reports – just like forms, Cover Misc.
topics and we will wrap up MS - Access.
• We will have an Exam review session on
Wednesday – April 08th.
• Exam #2: Comprehensive on MS Access
Monday – April 13th (Section 3).
Wednesday – April 15th (Section 5).
19
- Sundara Ram Matta
[email protected]
Apr 01st, 2015
20