Introduction to Database Management Systems
Database Management
Systems and SQL
Session 1
What is a DBMS?
•
Collection of interrelated data – manual or
computerized or online
•
Set of programs to access the data
•
DBMS provides an environment that is both
convenient
and
efficient
to use.
2
Applications Areas of
DBMS?
Banking: all transactions
Airlines: reservations, schedules
Universities: registration, grades
Sales: customers, products, purchases
Manufacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax
deductions
Databases touch all aspects of our lives
3
•
To avoid data redundancy and inconsistency
Multiple file formats, duplication of information in
different files
•
To avoid difficulty in accessing data
Need to write a new program to carry out each
new task
•
To deal with data isolation — multiple files and
formats
•
To deal with integrity problems
Integrity constraints (e.g. account balance > 0)
become part of program code
Easy to add new constraints or change existing
ones
Why do we use DBMS
4
1.
Atomicity of updates
•
Failures may leave database in an inconsistent state with partial
updates carried out
•
E.g. transfer of funds from one account to another should either
complete or not happen at all
2.
Concurrent access by multiple users
•
Concurrent accessed needed for performance
•
Uncontrolled concurrent accesses can lead to inconsistencies
o
E.g. two people reading a balance and updating it at the same
time
3.
Security problems
Why do we use DBMS (contd..)
5
Relational Model
Example of tabular data in the relational model
customer-
name
c
ustomer-id
customer-
street
customer-
city
account-
number
Johnson
Smith
Johnson
Jones
Smith
192-83-7465
019-28-3746
192-83-7465
321-12-3123
019-28-3746
Alma
North
Alma
Main
North
Palo Alto
Rye
Palo Alto
Harrison
Rye
A-101
A-215
A-201
A-217
A-201
Attributes
6
A Logically Related Database
7
Database Users
Users are differentiated by the way they expect to interact with the
system
•
Application programmers
– interact with system through DML
calls
•
Sophisticated users
– form requests in a database query
language
•
Specialized users
– write specialized database applications that
do not fit into the traditional data processing framework
•
Naïve users
– invoke one of the permanent application
programs that have been written previously
E.g. people accessing database over the web, bank tellers, clerical staff
8
Database Administrator
•
Coordinates all the activities of the database
system
•
Has a good understanding of the enterprise’s
information resources and needs.
•
Database administrator’s responsibilities include:
Schema definition
Storage structure and access method definition
Schema and physical organization modification
Granting user authority to access the database
Specifying integrity constraints
Acting as liaison with users
Monitoring performance and responding to changes in
requirements
9
Transaction Management
•
A
transaction
is a collection of operations that
performs a single logical function in a database
application
•
Transaction-management component ensures that the
database remains in a consistent (correct) state
despite system failures (e.g., power failures and
operating system crashes) and transaction failures.
•
Concurrency-control manager controls the interaction
among the concurrent transactions, to ensure the
consistency of the database.
10
Storage Management
•
Storage manager is a program module that
provides the interface between the low-level
data stored in the database and the
application programs and queries submitted to
the system.
•
The storage manager is responsible to the
following tasks:
interaction with the file manager
efficient storing, retrieving and updating of data
11
Overall
System
Structure
12
Application Architectures
T
w
o
-
t
i
e
r
a
r
c
h
i
t
e
c
t
u
r
e
:
E
.
g
.
c
l
i
e
n
t
p
r
o
g
r
a
m
s
u
s
i
n
g
O
D
B
C
/
J
D
B
C
t
o
c
o
m
m
u
n
i
c
a
t
e
w
i
t
h
a
d
a
t
a
b
a
s
e
T
h
r
e
e
-
t
i
e
r
a
r
c
h
i
t
e
c
t
u
r
e
:
E
.
g
.
w
e
b
-
b
a
s
e
d
a
p
p
l
i
c
a
t
i
o
n
s
,
a
n
d
a
p
p
l
i
c
a
t
i
o
n
s
b
u
i
l
t
u
s
i
n
g
“
m
i
d
d
l
e
w
a
r
e
”
13
DBMS:
Allows to Create, Manipulate &
Access the Data
14
SQL
Structured Query Language
The Language of DBMS
Standard language for
querying
and
manipulating
data.
Very widely used.
1.
Data Definition Language (DDL)
–
Create/alter/delete tables and their attributes
2.
Data Manipulation Language (DML)
–
Insert/delete/modify tuples in tables
SQL
•
SQL: widely used non-procedural language
–
E
.
g
.
f
i
n
d
t
h
e
n
a
m
e
o
f
t
h
e
c
u
s
t
o
m
e
r
w
i
t
h
c
u
s
t
o
m
e
r
-
i
d
1
9
2
-
8
3
-
7
4
6
5
s
e
l
e
c
t
c
u
s
t
o
m
e
r
.
c
u
s
t
o
m
e
r
-
n
a
m
e
f
r
o
m
c
u
s
t
o
m
e
r
w
h
e
r
e
c
u
s
t
o
m
e
r
.
c
u
s
t
o
m
e
r
-
i
d
=
‘
1
9
2
-
8
3
-
7
4
6
5
’
–
E
.
g
.
f
i
n
d
t
h
e
b
a
l
a
n
c
e
s
o
f
a
l
l
a
c
c
o
u
n
t
s
h
e
l
d
b
y
t
h
e
c
u
s
t
o
m
e
r
w
i
t
h
c
u
s
t
o
m
e
r
-
i
d
1
9
2
-
8
3
-
7
4
6
5
s
e
l
e
c
t
a
c
c
o
u
n
t
.
b
a
l
a
n
c
e
f
r
o
m
d
e
p
o
s
i
t
o
r
,
a
c
c
o
u
n
t
w
h
e
r
e
d
e
p
o
s
i
t
o
r
.
c
u
s
t
o
m
e
r
-
i
d
=
‘
1
9
2
-
8
3
-
7
4
6
5
’
a
n
d
d
e
p
o
s
i
t
o
r
.
a
c
c
o
u
n
t
-
n
u
m
b
e
r
=
a
c
c
o
u
n
t
.
a
c
c
o
u
n
t
-
n
u
m
b
e
r
•
Application programs generally access databases through one of
–
Language extensions to allow embedded SQL
–
Application program interface (e.g. ODBC/JDBC) which allow SQL
queries to be sent to a database
16
Tables in RDBMS
Product
Attribute names
Table name
Tuples or rows
17
Steps to Define the Schema
Product
S
t
e
p
1
:
D
e
f
i
n
e
t
a
b
l
e
n
a
m
e
a
n
d
i
t
s
a
t
t
r
i
b
u
t
e
s
Product(PName, Price, Category, Manufacturer)
18
B
a
s
i
c
d
a
t
a
t
y
p
e
s
–
N
u
m
e
r
i
c
•
Integer numbers: INTEGER, INT, and SMALLINT
•
Floating-point (real) numbers: FLOAT or REAL, and
DOUBLE PRECISION
–
C
h
a
r
a
c
t
e
r
-
s
t
r
i
n
g
•
Fixed length: CHAR(
n
), CHARACTER(
n
)
•
Varying length: VARCHAR(
n
), CHAR VARYING(
n
),
CHARACTER VARYING(
n
)
Data Types and Domain of Attributes
Product(
PName
, Price, Category, Manfacturer)
19
Data Types and Domain of Attributes
–
B
o
o
l
e
a
n
•
Values of TRUE or FALSE or NULL
–
D
A
T
E
•
Ten positions
•
Components are YEAR, MONTH, and DAY in the
form YYYY-MM-DD
–
T
i
m
e
s
t
a
m
p
•
Includes the DATE and TIME fields
•
Plus a minimum of six positions for decimal
fractions of seconds
•
Optional WITH TIME ZONE qualifier
20
S
t
e
p
2
:
D
e
f
i
n
e
D
a
t
a
T
y
p
e
s
a
n
d
D
o
m
a
i
n
o
f
A
t
t
r
i
b
u
t
e
s
.
Product(
PName
, Price, Category, Manfacturer)
Pname : Varchar
,
Price: Float,
Category: Varchar
Manfacturer: Varchar
Steps to Define the Schema
21
Constraints: Restrictions on values of
Attribute.
S
t
e
p
3
:
S
p
e
c
i
f
y
i
n
g
C
o
n
s
t
r
a
i
n
t
s
.
Product(
PName
, Price, Category, Manfacturer)
•
Specifying Key and Referential Integrity
Constraints
•
Specifying Attribute and Domain Constraints
•
Specifying Key Constraints
22
Specifying Attribute and Domain Constraints
•
N
O
T
N
U
L
L
NULL is not permitted for a particular attribute
•
D
e
f
a
u
l
t
v
a
l
u
e
DEFAULT <value>
•
C
H
E
C
K
c
l
a
u
s
e
Dnumber > 0 AND Dnumber < 21;
•
U
N
I
Q
U
E
c
l
a
u
s
e
Specifies attributes that have unique values
23
Specifying Key Constraints
•
P
R
I
M
A
R
Y
K
E
Y
c
l
a
u
s
e
Specifies one or more attributes that make up the
primary key of a relation
It is an attribute or a combination of attributes that
that uniquely identifies the records./tuples
e.g. roll_no, account_no, Id etc.
24
P
R
I
M
A
R
Y
K
E
Y
=
N
O
T
N
U
L
L
+
U
N
I
Q
U
E
Schema of Table Product
P
r
o
d
u
c
t
(
P
n
a
m
e
v
a
r
c
h
a
r
P
r
i
m
a
r
y
K
e
y
,
Price float Not Null,
Category varchar, check(Gadget, Photoraphy,
Household
Manufacturer varchar )
25
LET’S CODE
TOGETHER!!
26
Creating a Database
Step 1. Create a Database Company
CREATE DATABASE
<DATABSE NAME>;
Create database company;
Step 2. USE Database
USE
<DATABSE NAME>;
use company;
27
Step 2. SHOW TABLES
show tables;
Step 1. Create a TABLE
CREATE TABLE
<TABLE NAME> (
<ATTRIBUTE LIST> <DATA TYPE> <CONSTRAINT>,
<ATTR2> <DATA TYPE>,<CONSTRAINT>);
Creating a Table
28
Creating a Table
VIPS: Oct - Dec 2019
29
create table product(Pname varchar(20) primary key,
price float NOT NULL,category varchar(20)
CHECK(category
in("Gadget","Photography","Household")),manufacturer varchar(20));
Show tables;
Desc <tablename>;
Show Existing Tables
Describe structure of a Existing Table
30
Desc product;
INSERT INTO
R(A1,…., An)
VALUES
(v1,…., vn)
Insert records in Table
31
insert into product(Pname,price,category,manufacturer)
values("Gizmo",19.99, "Gadgets", "GizmoWorks");or
insert into product values("Gizmo",19.99, "Gadgets", "GizmoWorks");insert into product values("Powergizmo",29.99, "Gadgets", "GizmoWorks");insert into product values("SingleTouch",149.99, "Photography", "Canon");insert into product values("MultiTouch",203.99, "Household", "Hitachi");Select Query
SELECT
*
FROM
product;
Product
“selection”
SELECT
<attributes>
FROM
<one or more relations>
WHERE
<conditions>
32
SELECT
Pname, Price
FROM
Product
Product
“projection”
Select Query using WHERE
33
SELECT
*
FROM
Product
WHERE
category=‘Gadgets’;
Product
“selection” with
where
Select Query using WHERE
34
SELECT
PName, Price, Manufacturer
FROM
Product
WHERE
Price > 100;
Product
“selection” and
“projection” with
where
Select Query using WHERE
35
SELECT
PName, Price, Manufacturer
FROM
Product
WHERE
Price > 100 and manufacturer =“Canon”;
Product
Combine two or more
conditions Using
and
Select Query using WHERE
36
SELECT
PName, Price, Manufacturer
FROM
Product
WHERE
manufacturer =“Hitachi” or
manufacturer = “Canon”;
Product
Combine two or more
conditions Using
or
Select Query using WHERE
37
SELECT
PName, Price, Manufacturer
FROM
Product
WHERE
manufacturer IN(“Hitachi”,“Canon”);
Product
Replace OR with In
conditions Using
IN
Select Query using WHERE
38
Note That
•
Case insensitive:
–
Same: SELECT Select select
–
Same: Product product
–
Different: ‘Seattle’ ‘seattle’
•
Constants:
–
‘abc’ - yes
–
“abc” - no
39
The LIKE operator
P
a
t
t
e
r
n
:
p
a
t
t
e
r
n
m
a
t
c
h
i
n
g
o
n
s
t
r
i
n
g
s
.
I
t
c
o
n
t
a
i
n
s
t
w
o
s
p
e
c
i
a
l
s
y
m
b
o
l
s
:
% = any sequence of characters
_ = any single character
S
E
L
E
C
T
*
F
R
O
M
P
r
o
d
u
c
t
s
W
H
E
R
E
P
N
a
m
e
L
I
K
E
<
p
a
t
t
e
r
n
>
40
Like Operator with %
SELECT
*
FROM
Product
WHERE
Pname like ‘p%’;
Product
Product name that starts with P
41
Like Operator with %
SELECT
*
FROM
Product
WHERE
Pname like ‘%Touch’;
Product
Product name that ends with Touch
42
Like Operator with %
SELECT
*
FROM
Product
WHERE
Pname like ‘%e%’;
Product
Product name that contains e anywhere in the name
43
Like Operator with _ &%
SELECT
*
FROM
Product
WHERE
Pname like ‘_o%’;
Product
Product name with second letter ‘o’
44
Like Operator with %
SELECT
*
FROM
Product
WHERE
Pname like ‘%c_’;
Product
Product name with second last character ‘c’
45
Eliminating Duplicates
SELECT
DISTINCT
category
FROM
Product;
Compare to:
SELECT
category
FROM
Product;
46
Aggregate Functions
Except count, all aggregations apply to a single attribute
SQL supports several aggregation operations:
Sum
Max
Min
Avg
Count
47
SELECT
sum(price)
FROM
Product;
Product
Sum of Price of all Products
403.96
Aggregate Functions – SUM
48
SELECT
max(price)
FROM
Product;
Product
Max of Price of all Products
Aggregate Functions – MAX
203.96
49
SELECT
min(price)
FROM
Product;
Product
Min of Price of all Products
Aggregate Functions – MIN
19.99
50
SELECT
avg(price)
FROM
Product;
Product
Avg of Price of all Products
Aggregate Functions – AVG
100.99
51
SELECT
count(price)
FROM
Product;
Product
Total number of Products
Aggregate Functions – COUNT
4
52
SELECT
count(*)
FROM
Product;
More Examples
53
WRITE
THE
QUERY
54
Ordering the Results
SELECT
pname, price, manufacturer
FROM
Product
WHERE
manufacturer=‘GizmoWorks’ AND price > 50
ORDER BY
price, pname;
•
Ties are broken by the second attribute on the ORDER
BY list, etc.
•
Also works without Where
•
Ordering is ascending, unless you specify the DESC
keyword.
55
SELECT
pname, price, manufacturer
FROM
Product
ORDER BY
price DESC;
SELECT
Category
FROM
Product
ORDER BY
PName
?
SELECT
DISTINCT
category
FROM
Product
ORDER BY
category
SELECT
DISTINCT
category
FROM
Product
ORDER BY
PName
?
?
FIND
THE
RESULT
56
Practice Exercise
57
Create a new table in your current database
‘COMPANY’ with the following schema
58
Create a new table named ‘COMPDTLS’ in your
current database with the following schema
COMPDTLS(
CompName
varchar Primary Key,
RegDate
Date Not Null,
StockPrice
Float
Country
varchar )
59
Insert the following Records in
COMPDTLS
60
1.
List the details of all companies
2.
List the registration date of all companies
3.
Show the details of all companies of Japan
4.
List the company name whose stock price is 65
5.
List the companies of Japan or India
6.
Show the maximum stock price.
7.
Show the average stock price.
8.
Show the distinct countries
9.
Show the total no of countries
10.
Show the company name whose country name ends with ‘a’.
Write SQL Queries for:
61
Fundamentals and applications of Database Management Systems (DBMS) and SQL. Learn how DBMS helps in organizing and accessing data efficiently to avoid redundancy, inconsistency, and security issues. Understand the relational model and its attributes, and discover the importance of using DBMS for various sectors like banking, airlines, universities, sales, manufacturing, and human resources.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Database Management Database Management Systems and SQL Systems and SQL Session 1 Session 1
What is a DBMS? Collection computerized or online of interrelated data manual or Set of programs to access the data DBMS provides an environment that is both convenient and efficient to use. 2
Applications Areas of DBMS? Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions Databases touch all aspects of our lives Databases touch all aspects of our lives 3
Why do we use DBMS To avoid data redundancy and inconsistency Multiple file formats, duplication of information in different files To avoid difficulty in accessing data Need to write a new program to carry out each new task To deal with data isolation multiple files and formats To deal with integrity problems Integrity constraints (e.g. account balance > 0) become part of program code Easy to add new constraints or change existing ones 4
Why do we use DBMS (contd..) 1. Atomicity of updates Failures may leave database in an inconsistent state with partial updates carried out E.g. transfer of funds from one account to another should either complete or not happen at all 2. Concurrent access by multiple users Concurrent accessed needed for performance Uncontrolled concurrent accesses can lead to inconsistencies o E.g. two people reading a balance and updating it at the same time 3. Security problems 5
Relational Model Attributes Example of tabular data in the relational model customer- name customer- street customer- city account- number customer-id Johnson 192-83-7465 Alma A-101 Palo Alto Smith 019-28-3746 North A-215 Rye Johnson 192-83-7465 Alma A-201 Palo Alto Jones 321-12-3123 Main A-217 Harrison Smith 019-28-3746 North A-201 Rye 6
Database Users Users are differentiated by the way they expect to interact with the system Application programmers interact with system through DML calls Sophisticated users form requests in a database query language Specialized users write specialized database applications that do not fit into the traditional data processing framework Na ve users invoke one of the permanent application programs that have been written previously E.g. people accessing database over the web, bank tellers, clerical staff 8
Database Administrator Coordinates all the activities of the database system Has a good understanding of the enterprise s information resources and needs. Database administrator s responsibilities include: Schema definition Storage structure and access method definition Schema and physical organization modification Granting user authority to access the database Specifying integrity constraints Acting as liaison with users Monitoring performance and responding to changes in requirements 9
Transaction Management A transaction is a collection of operations that performs a single logical function in a database application Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures. Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. 10
Storage Management Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible to the following tasks: interaction with the file manager efficient storing, retrieving and updating of data 11
Overall System Structure 12
Application Architectures Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a database Three-tier architecture: E.g. web-based applications, and applications built using middleware 13
DBMS: Allows to Create, Manipulate & Access the Data 14
The Language of DBMS SQL Structured Query Language Standard language for querying data. Very widely used. querying and manipulating Very widely used. manipulating 1. Data Definition Language (DDL) Create/alter/delete tables and their attributes 2. Data Manipulation Language (DML) Insert/delete/modify tuples in tables
SQL SQL: widely used non-procedural language E.g. find the name of the customer with customer-id 192-83-7465 selectcustomer.customer-name fromcustomer wherecustomer.customer-id= 192-83-7465 E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465 selectaccount.balance fromdepositor, account wheredepositor.customer-id= 192-83-7465 and depositor.account-number = account.account-number Application programs generally access databases through one of Language extensions to allow embedded SQL Application program interface (e.g. ODBC/JDBC) which allow SQL queries to be sent to a database 16
Table name Attribute names Tables in RDBMS Product PName Price Category Manufacturer Gizmo 19.99 Gadgets GizmoWorks Powergizmo 29.99 Gadgets GizmoWorks SingleTouch 149.99 Photography Canon MultiTouch 203.99 Household Hitachi Tuples or rows 17
Steps to Define the Schema Step 1: Define table name and its attributes Product(PName, Price, Category, Manufacturer) Product PName Price Category Manufacturer Gizmo 19.99 Gadgets GizmoWorks Powergizmo 29.99 Gadgets GizmoWorks SingleTouch 149.99 Photography Canon MultiTouch 203.99 Household Hitachi 18
Data Types and Domain of Attributes Product(PName, Price, Category, Manfacturer) Basic data types Numeric Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION Character-string Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) 19
Data Types and Domain of Attributes Boolean Values of TRUE or FALSE or NULL DATE Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD Timestamp Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds Optional WITH TIME ZONE qualifier 20
Steps to Define the Schema Step 2: Define Data Types and Domain of Attributes. Product(PName, Price, Category, Manfacturer) Pname : Varchar, Price: Float, Category: Varchar Manfacturer: Varchar 21
Step 3: Specifying Constraints. Product(PName, Price, Category, Manfacturer) Constraints: Restrictions on values Constraints: Restrictions on values of of Attribute Attribute. . Specifying Attribute and Domain Constraints Specifying Key Constraints Specifying Key and Referential Integrity Constraints 22
Specifying Attribute and Domain Constraints NOT NULL NULL is not permitted for a particular attribute Default value DEFAULT <value> CHECK clause Dnumber > 0 AND Dnumber < 21; UNIQUE clause Specifies attributes that have unique values 23
Specifying Key Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation It is an attribute or a combination of attributes that that uniquely identifies the records./tuples e.g. roll_no, account_no, Id etc. PRIMARY KEY = NOT NULL+ UNIQUE 24
Schema of Table Product Product(Pname varchar Primary Key, Price float Not Null, Category varchar, check(Gadget, Photoraphy, Household Manufacturer varchar ) Attribute Data Type Constraints Pname Varchar Primary Key Price Float Not Null Category Varchar Gadget, Photography, Household Manufacturer Varchar 25
LETS CODE LET S CODE TOGETHER!! TOGETHER!! 26
Creating a Database Step 1. Create a Database Company CREATE DATABASE <DATABSE NAME>; Create database company; Step 2. USE Database USE <DATABSE NAME>; use company; Step 2. SHOW TABLES show tables; 27
Creating a Table Step 1. Create a TABLE CREATE TABLE <TABLE NAME> ( <ATTRIBUTE LIST> <DATA TYPE> <CONSTRAINT>, <ATTR2> <DATA TYPE>,<CONSTRAINT>); Attribute Pname Price Category Data Type Varchar Float Varchar Constraints Primary Key Not Null Gadget, Photography, Household Manufacturer Varchar 28
Creating a Table create table product(Pname varchar(20) primary key, price float NOT NULL,category varchar(20) CHECK(category in("Gadget","Photography","Household")), manufacturer varchar(20)); Attribute Pname Price Category Data Type Varchar Float Varchar Constraints Primary Key Not Null Gadget, Photography, Household Manufacturer VIPS: Oct - Dec 2019 Varchar 29
Show Existing Tables Show tables; Describe structure of a Existing Table Desc <tablename>; Desc product; 30
Insert records in Table INSERT INTO R(A1, ., An) VALUES (v1, ., vn) insert into product(Pname,price,category,manufacturer) values("Gizmo",19.99, "Gadgets", "GizmoWorks"); or insert into product values("Gizmo",19.99, "Gadgets", "GizmoWorks"); insert into product values("Powergizmo",29.99, "Gadgets", "GizmoWorks"); insert into product values("SingleTouch",149.99, "Photography", "Canon"); insert into product values("MultiTouch",203.99, "Household", "Hitachi"); PName Price Category Manufacturer Gizmo 19.99 Gadgets GizmoWorks Powergizmo 29.99 Gadgets GizmoWorks SingleTouch 149.99 Photography Canon MultiTouch 203.99 Household Hitachi 31
Select Query SELECT <attributes> FROM <one or more relations> WHERE <conditions> Product SELECT * FROM product; PName Price Category Manufacturer selection Gizmo 19.99 Gadgets GizmoWorks Powergizmo 29.99 Gadgets GizmoWorks SingleTouch 149.99 Photography Canon MultiTouch 203.99 Household Hitachi 32
Select Query using WHERE PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product SELECT Pname, Price FROM Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 projection 33
Select Query using WHERE Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT * FROM Product WHERE category= Gadgets ; PName Gizmo Powergizmo Price 19.99 29.99 Category Gadgets Gadgets Manufacturer GizmoWorks GizmoWorks selection with where 34
Select Query using WHERE Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100; PName SingleTouch MultiTouch Price 149.99 203.99 Manufacturer Canon Hitachi selection and projection with where 35
Select Query using WHERE Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100 and manufacturer = Canon ; Combine two or more conditions Using and PName SingleTouch Price 149.99 Manufacturer Canon 36
Select Query using WHERE PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product SELECT PName, Price, Manufacturer FROM Product WHERE manufacturer = Hitachi or manufacturer = Canon ; PName SingleTouch MultiTouch Price 149.99 203.99 Manufacturer Canon Hitachi Combine two or more conditions Using or 37
Select Query using WHERE PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product SELECT PName, Price, Manufacturer FROM Product WHERE manufacturer IN( Hitachi , Canon ); PName SingleTouch MultiTouch Price 149.99 203.99 Manufacturer Canon Hitachi Replace OR with In conditions Using IN 38
Note That Case insensitive: Same: SELECT Select select Same: Product product Different: Seattle seattle Constants: abc - yes abc - no 39
The LIKE operator SELECT * FROM Products WHERE PName LIKE <pattern> Pattern : pattern matching on strings. It contains two special symbols: % = any sequence of characters _ = any single character 40
Like Operator with % Product name that starts with P Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT * FROM Product WHERE Pname like p% ; PName Price Category Manufacturer Powergizmo 29.99 Gadgets GizmoWorks 41
Like Operator with % Product name that ends with Touch Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT * FROM Product WHERE Pname like %Touch ; PName SingleTouch MultiTouch Price 149.99 203.99 Category Photography Household Manufacturer Canon Hitachi 42
Like Operator with % Product name that contains e anywhere in the name Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT * FROM Product WHERE Pname like %e% ; PName Powergizmo SingleTouch Price 29.99 149.99 Category Gadgets Photography Manufacturer GizmoWorks Canon 43
Like Operator with _ &% Product name with second letter o Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT * FROM Product WHERE Pname like _o% ; PName Price Category Manufacturer Powergizmo 29.99 Gadgets GizmoWorks 44
Like Operator with % Product name with second last character c Product PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi SELECT * FROM Product WHERE Pname like %c_ ; PName SingleTouch MultiTouch Price 149.99 203.99 Category Photography Household Manufacturer Canon Hitachi 45
Eliminating Duplicates Category Gadgets Photography Household SELECT DISTINCT category FROM Product; Compare to: Category Gadgets Gadgets Photography Household SELECT category FROM Product; 46
Aggregate Functions SQL supports several aggregation operations: Sum Max Min Avg Count Except count, all aggregations apply to a single attribute 47
Aggregate Functions SUM Sum of Price of all Products PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product SELECT sum(price) FROM Product; 403.96 48
Aggregate Functions MAX Max of Price of all Products PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product SELECT max(price) FROM Product; 203.96 49
Aggregate Functions MIN Min of Price of all Products PName Gizmo Powergizmo SingleTouch MultiTouch Price 19.99 29.99 149.99 203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product SELECT min(price) FROM Product; 19.99 50
