Databases II
INF3703
Chapter 1-9 That could be important
SQL RELATED
The basic data definition commands allow you to create tables, indexes and views. Many SQL constraints can be
used with columns. The commands are CREATE TABLE, CREATEINDEX, CREATE VIEW, ALTER TABLE, DROP TABLE,
DROP VIEW and DROP INDEX.
The ANSI standard data types are supported by all RDBMS vendors in different ways. The basic data types are
NUMBER, INTEGER, CHAR, VARCHAR and DATE.
DML commands allow you to add, modify, and delete rows from tables. The basic DML commands are SELECT,
INSERT, UPDATE, DELETE, COMMIT and ROLLBACK.
The SELECT statement is the main data retrieval command in SQL. A SELECT statement has the
following syntax:
SELECT columnlist
FROM tablelist
[WHERE conditionlist ]
[GROUP BY columnlist ]
[HAVING conditionlist ]
[ORDER BY columnlist [ASC | DESC]] ;
The column list represents one or more column names separated by commas.
The WHERE clause can be used with the SELECT, UPDATE and DELETE statements to restrict the rows affected by
the DDL command. ((AND/OR/NOT),( ), (BETWEEN, IS NULL, LIKE, IN and EXISTS)
Aggregate functions (COUNT, MIN, MAX, AVG) are special functions that perform arithmetic computations over a
set of rows.
The ORDER BY clause is used to sort the output of a SELECT statement.
You can join the output of multiple tables with the SELECT statement.
SQL provides relational set operators to combine the output of two queries to generate a new relation. The UNION
and UNION ALL set operators combine the output of two (or more) queries and produce a new relation with all
,unique (UNION) or duplicate (UNION ALL) rows from both queries. The INTERSECT relational set operator selects
only the common rows. The MINUS set operator selects only the rows that are different. UNION, INTERSECT and
MINUS require union-compatible relations.
Operations that join tables can be classified as inner joins and outer joins. An inner join is the traditional join in
which only rows that meet a given criteria are selected. An outer join returns the matching rows as well as the
rows with unmatched attribute values for one table or both tables to bejoined. A natural join returns all rows with
matching values in the matching columns and eliminates duplicate columns. Joins may use keywords such as
USING and ON
Subqueries and correlated queries are used when it is necessary to process data based on other processed data.
Most subqueries are executed in a serial fashion. SQL functions are used to extract or transform data. Oracle
sequences may be used to generate values to be assigned to a record Procedural SQL (PL/SQL) can be used to
create triggers, stored procedures and PL/SQL functions.
A stored procedure is a named collection of SQL statements. When SQL statements are designed to return more
than one value inside the PL/SQL code, a cursor is needed. Embedded SQL refers to the use of SQL statements
within an application programming language such as Visual Basic, .NET, C#, Python or Java.
NORMALIZATION RELATED
A table is in 1NF when all key attributes are defined and when all remaining attributes are dependent on the
primary key. However, a table in 1NF can still contain both partial and transitive dependencies.
A table is in 2NF when it is in 1NF and contains no partial dependencies.
A table is in 3NF when it is in 2NF and contains no transitive dependencies.
,ERD
UML
CROW
HIERARCHY
, COMPOSITE VS AGGREGATION
(Imagine a book and pages. With the composite it means if the book burns so are the pages while aggregation
means only the cover is burned and pages stays)
