delete statement • update statement • insert statement...
TRANSCRIPT
• Data Definition and Manipulation Languages• Structured Query Language (SQL)• DDL Example: CREATE TABLE• SELECT statement• INSERT statement• UPDATE statement• DELETE statement
• End-users interact with the data and database schema using a database language.
• Structured Query Language (SQL)—ISO standard—meets these requirements.
• Non-procedural.• Uses easy to understand English words.• Utilised by various organisational users with different
information needs.• It is based on an internationally recorgnised standard.
• SQL statements comprise of reserved words and user-defined words.
• SQL statements are normally terminated by semi-colons: ‘;’
• CREATE TABLE [...]Fields require data types corresponding to types of data stored in the column.
• Additional statements.• CREATE [TABLE|VIEW]• DROP [TABLE|VIEW]• ALTER TABLE
• SELECT statements.• INSERT statements.• UPDATE statements.• DELETE statements.
• SELECT statements have the following form.
SELECT A1, A2 [...]FROM r1, r2 [...]WHERE P
• A represents attributes.• r represents relations.• P represents a predicate.
• The SELECT clause lists all specified attributes in the final results.
• FROM specifies the tables to be used.• WHERE indicates a condition for filtering results.
• GROUP BY arranges output in associated groupings.• HAVING provides a mechanism of filtering grouping.• ORDER BY specifies how the output should be arranged.
• GROUP BY and HAVING are useful for aggregate functions.
• SUM• COUNT• MIN• MAX• AVG
• ORDER BY specifies how the output should be arranged.
• Subqueries involve the use of embedded inner SELECT queries that result results that are used by the outer SELECT statement.
• Demonstrations in class
• Multitable queries combine output from multiple tables by joining common fields.
• INSERT statements enable end users to add new records or entries to the database.
• INSERT statements have the following form.
INSERT INTO TableName (columnList) VALUES (dataList) WHERE P
• columnList specifies columns to slot data in.
• dataList specifies data.
• UPDATE statements enable end users to update existing records or entries in the database.
• UPDATE statements have the following form.
UPDATE TableName SET columnName = dataValue WHERE P
• columnName and value specifies aspects to change.
• P is the condition.
• DELETE statements facilitate the purging of information from the database.
• DELETE statements have the following form.
DELETE FROM TableName WHERE P
• P is the condition.