Database - Discussions
San77
BasicSQL.pdf
Basic SQL
Dr. Buleje
Slide 6- 1
Outline
SQL Data Definition and Data Types
Specifying Constraints in SQL
Basic Retrieval Queries in SQL
INSERT, DELETE, and UPDATE Statements in
SQL
Slide 6- 3
Basic SQL
SQL language
SQL Origin
SQL Actually comes from the word “SEQUEL” which was the original term used in the paper: “SEQUEL TO SQUARE”
Slide 6- 4
SQL Data Definition, Data Types,
Standards
Terminology:
Table, row, and column
CREATE statement
Main SQL command for data definition
Slide 6- 5
SQL Standards
SQL-86 or SQL 1.A.
SQL-92 is referred to as SQL-2.
Later standards (from SQL-1999) are divided into
core specification and specialized extensions.
SQL-2006 added XML features
SQL-3
Slide 6- 6
Schema and Catalog Concepts in
SQL
We cover the basic standard SQL syntax
SQL schema
Schema elements include
Tables, constraints, views, domains, and other
constructs
Slide 6- 6
Schema and Catalog Concepts in
SQL (cont’d.)
CREATE SCHEMA statement
CREATE SCHEMA COMPANY AUTHORIZATION
‘Jsmith’;
Catalog
Slide 6- 8
The CREATE TABLE Command in
SQL
Specifying a new relation
Can optionally specify schema:
CREATE TABLE COMPANY.EMPLOYEE ...
or
CREATE TABLE EMPLOYEE ...
Slide 6- 9
The CREATE TABLE Command in
SQL (cont’d.)
Base tables (base relations)
Virtual relations (views)
Slide 6- 10
COMPANY relational database schema
Slide 6- 11
One possible database state for the COMPANY relational database schema
Slide 6- 12
One possible database state for the COMPANY relational database schema – continued
Slide 6- 13
SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure in Slide 10
continued on next slide
Slide 6- 14
SQL CREATE TABLE data definition statements for defining the COMPANY
schema from Figure in Slide 10 -continued
Slide 6- 15
Attribute Data Types and Domains in
SQL
Basic data types
Numeric data types
Integer numbers: INTEGER, INT, and SMALLINT
Floating-point (real) numbers: FLOAT or REAL, and
DOUBLE PRECISION
Character-string data types
Fixed length: CHAR(n), CHARACTER(n)
Varying length: VARCHAR(n), CHAR
VARYING(n), CHARACTER VARYING(n)
Slide 6- 17
Attribute Data Types and Domains in
SQL (cont’d.)
Bit-string data types
Fixed length: BIT(n)
Varying length: BIT VARYING(n)
Boolean data type
Values of TRUE or FALSE or NULL
DATE data type
Ten positions
Components are YEAR, MONTH, and DAY in the
form YYYY-MM-DD
Multiple mapping functions available in RDBMSs to
change date formats
Slide 6- 18
Attribute Data Types and Domains in
SQL (cont’d.)
Additional data types
Timestamp data type
Includes the DATE and TIME fields
Plus a minimum of six positions for decimal
fractions of seconds
Optional WITH TIME ZONE qualifier
INTERVAL data type
Specifies a relative value that can be used to
increment or decrement an absolute value of a date,
time, or timestamp
Slide 6- 19
Attribute Data Types and Domains in
SQL (cont’d.)
Domain
Example:
CREATE DOMAIN SSN_TYPE AS CHAR(9);
TYPE
User Defined Types (UDTs) are supported for
object-oriented applications.
Uses the command: CREATE TYPE
Slide 6- 18
Specifying Constraints in SQL
Basic constraints:
Relational Model has 3 basic constraint types that
are supported in SQL:
Key constraint
Entity Integrity Constraint
Referential integrity constraints
Slide 6- 19
Basic Retrieval Queries in SQL
SELECT statement
SQL allows a table to have two or more tuples
that are identical
Slide 6- 20
The SELECT-FROM-WHERE
Structure of Basic SQL Queries
Basic form of the SELECT statement:
Slide 6- 21
Basic Retrieval Queries
Slide 6- 31
Basic Retrieval Queries (Contd.)
Slide 6- 32
Tables as Sets in SQL
SQL does not automatically eliminate duplicate tuples in
query results
For aggregate operations duplicates must be accounted
for
Use the keyword DISTINCT in the SELECT clause
Only distinct tuples should remain in the result
Slide 6- 38
Tables as Sets in SQL (cont’d.)
Set operations
UNION, EXCEPT (difference), INTERSECT
Corresponding multiset operations: UNION ALL,
EXCEPT ALL, INTERSECT ALL)
Slide 6- 39
Substring Pattern Matching and
Arithmetic Operators
LIKE comparison operator
Used for string pattern matching
% replaces an arbitrary number of zero or more
characters
underscore (_) replaces a single character
Examples: WHERE Address LIKE ‘%Houston,TX%’;
WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;
BETWEEN comparison operator
WHERE(Salary BETWEEN 30000 AND 40000)
AND Dno = 5;
Slide 6- 40
Arithmetic Operations
Standard arithmetic operators:
Addition (+), subtraction (–), multiplication (*), and
division (/) may be included as a part of SELECT
Slide 6- 27
Ordering of Query Results
Use ORDER BY clause
Keyword DESC
Keyword ASC
ORDER BY D.Dname DESC, E.Lname ASC,
E.Fname ASC
Slide 6- 28
INSERT, DELETE, and UPDATE
Statements in SQL
Three commands used to modify the database:
INSERT, DELETE, and UPDATE
INSERT
UPDATE
DELETE
Slide 6- 44
The INSERT Command (examples)
Specify the relation name and a list of values for
the tuple. All values including nulls are supplied.
The variation below inserts multiple tuples where
a new table is loaded values from the result of a
query.
Slide 6- 46
The DELETE Command (examples)
Removes tuples from a relation
Includes a WHERE clause to select the tuples to be
deleted. The number of tuples deleted will vary.
Slide 6- 49
UPDATE (example)
Example: Change the location and controlling
department number of project number 10 to
'Bellaire' and 5, respectively
U5: UPDATE PROJECT
SET PLOCATION = 'Bellaire',
DNUM = 5
WHERE PNUMBER=10
Slide 6- 51
Summary
SQL
A Comprehensive language for relational database
management
Data definition, queries, updates, constraint
specification, and view definition
Covered :
Data definition commands for creating tables
Commands for constraint specification
Simple retrieval queries
Database update commands
Slide 6- 55
