Unit 9 database
Unit 9.1 What is a database?
What is a database
Unit 9 database 1
A database is an organized collection of data, which allows users to obtain and
process information according to their requirements.
Includes All types of data
a. numbers
b. text
c. picture
Two primary ways of storing information
1. single table databases(flat file/ unstructured)
2. relational Database ( 关系性的)
Advantages of database
Prevent problems occurring because:
1. any changes or additions are made it only has to be done once - data is
consistent
2. the same data is used by everyone
3. data is only once stored in relational databases, which means no data
duplication.
Fields and records
1. field: column in the table
2. Records: row in the table
Primary Keys
a unique field that can be used to identify a record in a table
Foreign Keys
a field in a table that refers to the primary key in another table. Used to link tables
and create relationships.
Unit 9 database 2
DATA type
1. Jeff, Jane, Patty(Name): TEXT
2. 04/04/1993: Date/Time
3. char
4. Boolean
5. real
6. integer
NULL Value
a null value in a table is a value in a field that appears to be blank.
Validation check: checks the if information fits the
requirement
Type Description
Length check
Format Check
Range Check
Presence Check
Type check
Unit 9.2 theoretical part
DBMS: database management system
DBMS uses DDL(data definition language) and DML(data manipulation
language) to create.
DBMS uses SQL for both data definition.
4 relationships of a database
1. 1 to many
Unit 9 database 3
2. many to 1
3. many to. many
4. 1 to 1
5.
Unit 9.2 SQL: structured query language
SQL (structured query language): is a computer language for storing,
manipulating, and retrieving data stored in a relational database.
DDL:
Unit 9 database 4
SELECT FIELD,FIELD
FROM DATABASE
WHERE condition
SELECT<FieldName>, <FieldName2>
FROM<TableName>
WHERE<Condition>
AND/OR another_condition ;
SELECT
SELECT COLUMN(FIELD)
SELECT DISTINCT COLUMN
select all column without duplication
SELECT *
select all
SELECT COUNT(DISTINCT COLUMN)
select number of distinct column
Unit 9 database 5
WHERE
ORDER BY
order column alphabetically
ORDER BY COLUMN_1 DESC, COLUMN_2 ASC
column_1 in descending order
column_2 in ascending order
CONDITION AND or OR
WHERE CONDITION_1 AND (CONDITION_2 OR CONDITION_3)
NOT
1. Select column
FROM Table_Name
WHERE NOT Column = Value
2. WHERE NOT LIKE/BETWEEN/IN Condition
INSERT
INSERT INTO Table_name (Column1 , Column2 , Column3 , Column4)
VALUES
(VALUE, VALUE, VALUE , VALUE)
NULL
means the value is blank
WHERE COLUMN IS (NOT) NULL
UPDATE
Unit 9 database 6
UPDATE Field
SET Column1 = Value1, Column2 = Value2
WHERE Condition
Operator Condition SQL example
standard numerical
=, =!, < , <=, > , >=
operator
Case insensitive
<FieldName> LIKE
LIKE and NOT LIKE exact string
“string”
comparison
BETWEEN … AND number is not within <FieldName>
… range of two values BETWEEN 1 AND 10
NOT BETWEEN … number is not within <FieldName> NOT
AND … range of two values BETWEEN 1 AND 10
number exists in a
IN (…) <FieldName> IN (2,4,6)
list
NOT IN (…) number doesn’t exist
used in select and
DISTINT
means no
MIN() and MAX()
used in SELECT
SELECT MIN/MAX/(Field_name)
SELECT COUNT(*/Field_name)
JOIN (INNER JOIN)
Unit 9 database 7
SELECT TA.Field_1, TA.Field_2(things
you wanna show)
FROM TA(ON the left)
JOIN TB(ON the right)
ON TA.Field_P = TB.Field_N
GROUP BY
1. SELECT subject,
AVG(points)
FROM exam_results
GROUP BY subject
Unit 9 database 8
2. SELECT subject,
taken_on, AVG(points)
FROM exam_results
GROUP BY subject,
taken_on
niger white
1 2
1 2
1 2
1 1
1 1
2 1
SELECT niger, AVG(white)
FROM SB
GROUP BY niger
niger AVG(white) Count(white) SUM(white)
1 1.6 5 8
2 1 1 1
Unit 9 database 9
CREATE DATABASE or table and drop
database
CREATE database/table NAME
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
....
;
DROP TABLE NAME
ALTER TABLE table_name
ADD column_name datatype;
Unit 9 database 10