01-11-2012, 12:36 PM
Data Manipulation Language (DML)
Data Manipulation.pdf (Size: 69.34 KB / Downloads: 24)
Arithmetic operations, sorting
• SQL provides capability to perform four basic arithmetic
operations (+, -, *, / ) that can be applied to numeric
attributes and constants only
SELECT 2 + 2;
• Sorting of the query result tuples is done by using
ORDER BY {ıattribute_nameı[(ASC | DESC)], …}
clause after the WHERE clause (ASC is default)
SELECT * FROM Grades
ORDER BY StudId ASC, CourId DESC;
Nested queries
• Some queries require comparing a tuple to a collection
of tuples (eg, students doing courses that have>100
students)
• This task can be accomplished by embedding a SQL
query into WHERE clause of another query
– The embedded query is called nested query,
– The query containing the nested query is called outer query
• The comparison is made by using IN, qANY, q SOME,
and qALL operators,
Correlated nested queries
Let s be the current tuple of the outer query
• If the nested query doesn’t refer to s:
– the nested query has the same result for each tuple s
– the outer query and the nested query are said to be
Uncorrelated
• If a condition in the WHERE clause of the nested query
refers to some attribute of a relation declared in the outer
query, the two queries are said to be correlated
– Have to compute the inner query for each tuple considered by
the outer query
– Correlated nested queries consume more computer time than
uncorrelated ones
Summary
• The relational database language has commands to
define:
– database schema, domain, table, and constraints (CREATE
SCHEMA, CREATE DOMAIN, CREATE TABLE)
– queries (SELECT… FROM… WHERE… GROUP
BY…HAVING… ORDER BY…)
– update operations (INSERT, DELETE, UPDATE)
– views (CREATE VIEW)
– additional features (ASSERTION, TRIGGER, CURSOR,
GRANT, REVOKE, COMMIT, ROLLBACK)
• Although SQL is defined by a standard, implementations
have some dialects and exceptions