31271 Lecture Notes - Lecture 7: Aggregate Function, Programming Productivity, Virtual Method Table
Lecture 7: SQL I
Connecting to PostgreSQL in Labs
• In terminal, type psql –h pgsqllab.it.uts.edu.au –U username username
• To add a database file, type \i dbfile.txt
SQL Overview
• SQL – Structured Query Language, is the standard for relational database
management systems (RDBMS).
• RDBMS is a database management system that manages data as a collection of
tables in which relationships are represented by common values in related
tables.
• The purpose of SQL is to:
o Specify syntax/semantics for data definition and manipulation
o Define data structures and basic operations
o Enable portability of database definition and application modules
o Specify minimal (level 1) and complete (level 2) standards
o Allows for later growth/enhancement to standard (referential integrity,
transaction management, user-defined functions, extended join
operations, national character sets)
• The benefits of have a standardised relational language are reduced training
costs, productivity, application portability, application longevity, reduced
dependence on a single vendor and cross-system communication.
• Within an SQL environment:
o Catalogue – a set of schemas that constitute the description of a database
o Schema – the structure that contains descriptions of objects created by a
user (base tables, views, constraints)
o Data Definition Language (DDL) – commands that define a database,
including creating, altering, dropping tables and establishing constraints.
o Data Manipulation Language (DML) – commands that maintain and
query a database
o Data Control Language (DCL) – Commands that control a database,
including administering privileges and committing data.
SQL Data Types
find more resources at oneclass.com
find more resources at oneclass.com
Tables
• Steps in table creation:
1. Identify data types for the attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate keys)
4. Identify primary key and foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table and associated indexes
• When creating multiple tables with foreign keys, you need to make sure you
create the tables that are relied on prior to those that rely on other tables.
• The ALTER TABLE statement allows you to change column specifications
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
In a where containing one or more ands all specified conditions must be true. In a where containing one or more ors, at least one of the conditions must be true. If you mix ands or ors, the ands have precedence: the like operator allows you to compare strings. Add a % to the string to specify if it begins or ends with the string: an (cid:498)is not null(cid:499) key word can be used in a where clause to return data that is not null. Aggregate function: aggregate functions include avg, sum, min, max and count. Rules: the first one (or more) columns nominated in the select statement must also be nominated in the group by, the remaining columns nominated in the select must be aggregate functions. Views: table are used to store data physically in a database, corresponding to relations in a logical database design, using queries, it is possible to create virtual table or dynamic views.
