CS代写 - PowCoder代写

DISTINCT FUNCTIONS

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values

SELECT DISTINCT column_name FROM table_name; The query returns only distinct values in the specified column. In other words, it removes the duplicate values in the column from the result set.

Slide 7- *

The following SQL statement selects ALL (including the duplicates) values from the “Country” column in the “Customers” table:
SELECT Country FROM Customers;

The following SQL statement selects only the DISTINCT values from the “Country” column in the “Customers” table:
SELECT DISTINCT Country FROM Customers;

Slide 7- *

Nested Queries, Tuples,
and Set/Multiset Comparisons
Nested queries
Complete select-from-where blocks within WHERE clause of another query
Outer query and nested subqueries
Comparison operator IN
Compares value v with a set (or multiset) of values V
Evaluates to TRUE if v is one of the elements in V

Slide 7- 8

Nested Queries (cont’d.)
Slide 7- 9

Nested Queries (cont’d.)
Use tuples of values in comparisons
Place them within parentheses

Slide 7- 10

Use other comparison operators to compare a single value v
= ANY (or = SOME) operator
Returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN
Other operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>
ALL: value must exceed all values from nested query

Nested Queries (cont’d.)
Slide 7- 11

Specifying Joined Tables in the FROM Clause of SQL
Joined table
Permits users to specify a table resulting from a join operation in the FROM clause of a query

Slide 7- 19

Aggregate Functions in SQL
Used to summarize information from multiple tuples into a single-tuple summary
Built-in aggregate functions
COUNT, SUM, MAX, MIN, and AVG
Create subgroups of tuples before summarizing
To select entire groups, HAVING clause is used
Aggregate functions can be used in the SELECT clause or in a HAVING clause

Slide 7- *

Renaming Results of Aggregation
Following query returns a single row of computed values from EMPLOYEE table:

Q19: SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE;
The result can be presented with new names:

Q19A: SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal
FROM EMPLOYEE;

Slide 7- *

Aggregate Functions in SQL (cont’d.)
NULL values are discarded when aggregate functions are applied to a particular column

Slide 7- *

Grouping: The GROUP BY Clause
Partition relation into subsets of tuples
Based on grouping attribute(s)
Apply function to each such group independently
GROUP BY clause
Specifies grouping attributes
COUNT (*) counts the number of rows in the group

Slide 7- *

Examples of GROUP BY
The grouping attribute must appear in the SELECT clause:

Q24: SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
GROUP BY may be applied to the result of a JOIN:

Q25: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname;

Slide 7- *

Grouping: The GROUP BY and HAVING Clauses (cont’d.)
HAVING clause
Provides a condition to select or reject an entire group:
Query 26. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.

Q26: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
Slide 7- 31

Combining the WHERE and the HAVING Clause
Consider the query: we want to count the total number of employees whose salaries exceed $40,000 in each department, but only for departments where more than five employees work.

INCORRECT QUERY:

SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000
GROUP BY Dno
HAVING COUNT (*) > 5;
Slide 7- *

Use of CASE
SQL also has a CASE construct
Used when a value can be different based on certain conditions.
Can be used in any part of an SQL query where a value is expected
Applicable when querying, inserting or updating tuples

Slide 7- *

EXAMPLE of use of CASE
The following example shows that employees are receiving different raises in different departments (A variation of the update U6)

U6’: UPDATE EMPLOYEE

SET Salary =
CASE WHEN Dno = 5 THEN Salary + 2000
WHEN Dno = 4 THEN Salary + 1500
WHEN Dno = 1 THEN Salary + 3000
Slide 7- *

View Update
Update on a view defined on a single table without any aggregate functions
Can be mapped to an update on underlying base table- possible if the primary key is preserved in the view
Update not permitted on aggregate views. E.g.,

UV2: UPDATE DEPT_INFO
SET Total_sal=100000
WHERE Dname=‘Research’;
cannot be processed because Total_sal is a computed value in the view definition
Slide 7- 51

The DROP Command
DROP command
Used to drop named schema elements, such as tables, domains, or constraint
Drop behavior options:
CASCADE and RESTRICT
DROP SCHEMA COMPANY CASCADE;
This removes the schema and all its elements including tables, views, constraints, etc. If the RESTRICT option is chosen, the schema is dropped only if it has no elements in it; otherwise, the DROP command will not be executed.

Slide 7- 55

The ALTER table command
Alter table actions include:
Adding or dropping a column (attribute)
Changing a column definition
Adding or dropping table constraints
ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);

Slide 7- 56

Dropping Columns, Default Values
To drop a column
Choose either CASCADE or RESTRICT
CASCADE would drop the column from views etc. RESTRICT is possible if no views refer to it.

ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE;
Default values can be dropped and altered :

ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT ‘333445555’;

Slide 7- *

Table 7.2 Summary of SQL Syntax
continued on next slide
Slide 7- 59

Table 7.2 (continued) Summary of SQL Syntax
Slide 7- 60

程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts