Understanding SQL Features: Set Ops, Inner Join, Agg Functions, Operators, DDL, Slides of Database Management Systems (DBMS)

Download Understanding SQL Features: Set Ops, Inner Join, Agg Functions, Operators, DDL and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL

(DQL, DDL, DML)

Structured Query Language features

• DQL (Data Query Language)

SELECT

Used to get data from the database and impose ordering upon it.

• DML (Data Manipulation Language)

DELETE, INSERT, UPDATE

Used to change database data.

• DDL (Data Definition Language)

DROP, TRUNCATE, CREATE, ALTER

Used to manipulate database structures and definitions.

• RIGHTS

REVOKE, GRANT

Used to give and take access rights to database objects.

Set Operations [union all] ∪

• The UNION ALL operator merges the result sets of two component queries.
• This operation returns rows retrieved by either of the component queries.

(select custNo , Name from Customer where ID=5 )

union all

(select custNo , Name from Borrower )

• This will retain duplicated tuples. Num of duplication is equals to num of duplicates that appears in both tables

Set Operations [union] ∪

• The UNION operator returns all distinct rows retrieved

by two component queries.

• The UNION operation eliminates duplicates while

merging rows retrieved by either of the component

queries.

(select custNo , Name from Customer where

ID=5 )

union

(select custNo , Name from Borrower )

Set Operations [intersect] ∩

• Returns only those rows that are returned by each of two

SELECT statements.

(select distinct cust_name from Customer )

intersect

(select distinct cust_name from Borrower )

• INTERSECT returns only the rows retrieved by both

component queries.

• Intersect all also allowed, and it will return duplicates

Set Operations [except] –

• Takes the result set of one SELECT statement, and

removes those rows that are also returned by a

second SELECT statement.

• Automatically eliminates duplicates

(select cust_name from Customer )

except

(select cust_name from Borrower )

• If duplicates are needed, use “ except all ”

Inner Join

• REFERENTIAL INTEGRITY constraint - designates a column or combination of columns as a foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. In this relationship, the table containing the foreign key is called the child table and the table containing the referenced key is called the parent table. Note these caveats:
• The child and parent tables must be on the same database. They cannot be on different nodes of a distributed database.
• The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.
• To satisfy a referential integrity constraint, each row of the child table must meet one of these conditions:
• The value of the row's foreign key must appear as a referenced key value in one of the parent table's rows. The row in the child table is said to depend on the referenced key in the parent table.
• The value of one of the columns that makes up the foreign key must be null.
• A referential integrity constraint is defined in the child table. A referential integrity constraint definition can include any of these keywords: - FOREIGN KEY - this keyword when you define a foreign key with a table constraint clause. identifies the column or combination of columns in the child table that makes up of the foreign key. Only use - REFERENCES - referenced key columns must be of the same number and datatypes as the foreign key columns. identifies the parent table and the column or combination of columns that make up the referenced key. The - ON DELETE CASCADE - table and causes ORACLE to automatically delete dependent rows from the child table to maintain referential integrity. If allows deletion of referenced key values in the parent table that have dependent rows in the child you omit this option, ORACLE forbids deletion of referenced key values in the parent table that have dependent rows in thechild table. SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID

LastName Smith DepartmentID 34 DepartmentName Clerical Jones Robinson 3334 EngineeringClerical Steinberg Rafferty 3331 EngineeringSales

Left outer join

• A left outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned—but with NULL values for each column from B.
• For example, this allows us to find the employee's departments, but still show the employee even when their department is NULL or does not exist. The example above would have ignored employees in non-existent departments.

SELECT *

FROM employee

LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID

LastName Smith DepartmentID 34 DepartmentName Clerical Jones Robinson 3334 EngineeringClerical Jasper Steinberg 3633 NULL Engineering Rafferty 31 Sales

Full outer join

• Full outer joins are the combination of left and right outer joins.

These joins will show records from both tables, and fill in NULLs for missing matches on either side.

• Some database systems do not offer this functionality, but it can be

emulated through the use of left outer joins and unions.

SELECT *

FROM employee

FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID

LastName Smith DepartmentID 34 DepartmentName Clerical Jones Robinson 3334 EngineeringClerical Jasper Steinberg 3633 NULL Engineering Rafferty NULL 3135 SalesMarketing

null values

• If a row lacks a value for a particular column, that column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints.
• Any arithmetic expression containing a null always evaluates to null.
• All Scalar functions (except NVL and TRANSLATE) return null when given a null argument. The NVL function can be used to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.
• To test for nulls, only use the comparison operators IS NULL and IS NOT NULL.
• Most aggregate functions eliminate null values in calculations; one exception is the COUNT function. When using the COUNT function against a column containing null values, the null values will be eliminated from the calculation. However, if the COUNT function uses an asterisk, it will calculate all rows regardless of null values being present

Group function (cont.)

AVG([DISTINCT|ALL] n) (^) Returns average value of n.

COUNT({ | [DISTINCT|ALL] expr} )*

Returns the number of rows in the query. If you specify expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. MAX([DISTINCT|ALL] expr) (^) Returns maximum value of expr. MIN([DISTINCT|ALL] expr) (^) Returns minimum value of expr. STDDEV([DISTINCT|ALL] x)

Returns the standard deviation of x, a number. ORACLE calculates the standard deviation as the square root of the variance defined for the VARIANCE group function. SUM([DISTINCT|ALL] n) (^) Returns sum of values of n. VARIANCE([DISTINCT|ALL] x)

Returns variance of x, a number. For the variance formula see page 3- 4 8 of ``ORACLE7 Server SQL Language Reference Manual''.

select bName, avg( balance ) from group by bName having avg( balance ) > 1200

select count() from* customer

select nName ,count (distinct cName ) from depositor, account where depositor.acctNo = account.acctNo group by bName

Predicates

Predicate (^) Description BETWEEN ... AND (^) Compares a value to a range formed by two values. IN (^) Determines whether a value exists in a list of values or a table. LIKE (^) Compares, in part or in whole, one value with another. JOIN (^) Joins two tables.

Predicate (Transaction SQL) is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, and the join conditions of FROM clauses.

in keyword

• In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the WHERE clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns. The syntax for using the IN keyword is as follows:
• SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)

Store_Information store_name Sales Date Los Angeles $1500 Jan- 05 - 1999 San Diego $250 Jan- 07 - 1999 San Francisco $300 Jan- 08 - 1999 Boston $700 Jan- 08 - 1999 SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San D store_name Sales Date Los Angeles $1500 Jan- 05 - 1999 San Diego $250 Jan- 07 - 1999

between keyword

• Whereas the IN keyword help people to limit the selection criteria to one or more discrete values, the BETWEEN keyword allows for selecting a range. The syntax for the BETWEEN clause is as follows:
• SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'
• This will select all rows whose column has a value between 'value1' and 'value2'.

Table Store_Information store_name Sales Date Los Angeles $1500 Jan- 05 - 1999 San Diego $250 Jan- 07 - 1999 San Francisco $300 Jan- 08 - 1999 Boston $700 Jan- 08 - 1999 we key in, SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan- 06 - 1999' AND 'Jan- 10 - 1999 Note that date may be stored in different formats in diff Result: store_name Sales Date San Diego $250 Jan- 07 - 1999 San Francisco $300 Jan- 08 - 1999 Boston $700 Jan- 08 - 1999