PowerPoint Presentation
SQL LANGUAGE and Relational Data Model TUTORIAL
Prof: Dr. Shu-Ching Chen
TA: Sheng Guan
Outline
Relational Data Model
Step1 Define the purpose
Step2 Gather data, define constraints
Step3 Create Relationship
SQL Language
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
Relational Data Model (1)
Relation : Organizing data into collections of two-dimensional table called “Relations”
ERD Relational Data Model Database
Relational Data Model (2)
Fields (Attributes, Columns)
Tuples
(Records
/Rows )
S_id name Email Age gpa
5000 Dave Dave@cs 19 3.3
53666 Jones Jone@cs 18 3.4
53888 Smith Smith@cs 18 3.2
RD- Step1 Define the purpose
Gather the requirements and define the objective of your database
Drafting out the sample input forms, queries and reports, often helps.
*
RD Step2 Gather data, define constraints
Primary Key
Unique and not Null
Simple
Should not change
Often uses Integer
Other constraints
Not null
Positive values
RD – Step3 Create Relationship (1)
Identify the relationships among tables:
One-to-Many
Many-to-Many
One-to-One
RD – Step3 Create Relationship (2)
One-to-Many
The column teacherID in the child table Classes is known as the foreign key. A foreign key of a child table is a primary key of a parent table, used to reference the parent table.
Take note that for every value in the parent table, there could be zero, one, or more rows in the child table. For every value in the child table, there is one and only one row in the parent table.
*
RD – Step3 Create Relationship (3)
Many-to-Many
The many-to-many relationship is, in fact, implemented as two one-to-many relationships, with the introduction of the junction table.
– An order has many items in OrderDetails. An OrderDetails item belongs to one particular order.
– A product may appears in many OrderDetails. Each OrderDetails item specified one product.
*
RD – Step3 Create Relationship (4)
One-to-One
*
Basic Syntax of SQL Language
SELECT attribute name(s)
FROM table name
WHERE comparison predicate (Boolean expression)
GROUP BY attribute name(s)
HAVING comparison predicate
ORDER BY attribute name(s)
SQL (Structured Query Language) is a computer language aimed to store, manipulate, and query data stored in relational databases
HAVING is used when you are using an aggregate such as GROUP BY. It is used to check conditions after the aggregation takes place.
WHERE is used before the aggregation takes place.
*
Create Tables
Note that all data types use rather obvious input formats. Constants that are not simple numeric values must usually be surrounded by single quotes (‘), as in the example. The date column is actually quite flexible in what it accepts, but for this tutorial we will stick to the format shown here.
The syntax used so far requires you to remember the order of the columns. An alternative syntax allows you to list the columns explicitly:
You can list the columns in a different order if you wish or you can even omit some columns, for example, if the precipitation is unknown:
Many developers consider explicitly listing the columns better style than relying on the order implicitly.
*
SELECT ALL records
SQL is not case sensitive. SELECT is the same as select.
Tip: The asterisk (*) is a quick way of selecting all columns!
*
Formula
Like & DISTINCT
The LIKE operator is used to search for a specified pattern in a column.
The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
It is also possible to select the city that does NOT contain the pattern “Sa” from the “weather” table, by using the NOT keyword.
SELECT * FROM Persons
WHERE City NOT LIKE ‘%tav%‘
% => A substitute for zero or more characters
_ => A substitute for exactly one character
*
SELECT specific records with conditions
SQL ORDER BY
The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
SELECT * FROM Persons
ORDER BY LastName DESC
*
SQL UPDATE
UPDATE table name
SET column1 = value, column2 = value2,…
WHERE comparison predicate (Boolean expression)
AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
SQL IN OPERATOR
The IN operator allows you to specify multiple values in a WHERE clause
The MAX() function returns the largest value of the selected column
SQL has many built-in functions for performing calculations on data
*
SQL Alias
This can be a good thing to do if you have very long or complex table names or column names
An alias name could be anything, but usually it is short.
As you’ll see from the two SELECT statements above; aliases can make queries easier to both write and to read.
*
SQL Joins
INNER JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
SELECT column_name(s)
FROM table_name1 JOIN_TYPES table_name2
ON table_name1.column_name = table_name2.column_name
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table
*
SQL INNER JOIN
SQL LEFT JOIN
SQL RIGHT JOIN
SQL GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers
*
SQL HAVING Clause
PosgreSQL Arrays (1)
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported
As shown, an array data type is named by appending square brackets ([]) to the data type name of the array elements. The above command will create a table named sal_emp with a column of type text (name), a one-dimensional array of type integer(pay_by_quarter), which represents the employee’s salary by quarter, and a two-dimensional array of text (schedule), which represents the employee’s weekly schedule
*
PosgreSQL Arrays (2)
The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n]
We can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing lower-bound:upper-bound for one or more array dimensions. For example, this query retrieves the first item on Bill’s schedule for the first two days of the week:
If any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example, [2] is treated as [1:2]
*
PosgreSQL Arrays (3)
An array value can be replaced completely:
or using the ARRAY expression syntax:
An array can also be updated at a single element
or updated in a slice:
*
PosgreSQL Composite Types