Copyright 2016 and Shamkant B. Navathe
DISTINCT FUNCTIONS
Copyright By Assignmentchef assignmentchef
The SELECTDISTINCTstatement is used to return onlydistinct(different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values
SELECT DISTINCTcolumn_name FROM table_name; Thequeryreturns onlydistinctvalues in the specified column. In other words, it removes the duplicate values in the column from the result set.
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
The following SQL statement selects ALL (including the duplicates) values from the Country column in the Customers table:
SELECTCountryFROMCustomers;
The following SQL statement selects only the DISTINCT values from the Country column in the Customers table:
SELECTDISTINCTCountryFROMCustomers;
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
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
Copyright 2016 and Shamkant B. Navathe
Nested Queries (contd.)
Slide 7- 9
Copyright 2016 and Shamkant B. Navathe
Nested Queries (contd.)
Use tuples of values in comparisons
Place them within parentheses
Slide 7- 10
Copyright 2016 and Shamkant B. Navathe
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 (contd.)
Slide 7- 11
Copyright 2016 and Shamkant B. Navathe
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
Copyright 2016 and Shamkant B. Navathe
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- *
Copyright 2016 and Shamkant B. Navathe
Renaming Results of Aggregation
Following query returns a single row of computed values from EMPLOYEE table:
Q19: SELECTSUM (Salary), MAX (Salary), MIN (Salary), AVG(Salary)
FROMEMPLOYEE;
The result can be presented with new names:
Q19A:SELECTSUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal
FROMEMPLOYEE;
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
Aggregate Functions in SQL (contd.)
NULL values are discarded when aggregate functions are applied to a particular column
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
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- *
Copyright 2016 and Shamkant B. Navathe
Examples of GROUP BY
The grouping attribute must appear in the SELECT clause:
Q24:SELECTDno, COUNT (*), AVG (Salary)
FROMEMPLOYEE
GROUP BYDno;
GROUP BY may be applied to the result of a JOIN:
Q25:SELECTPnumber, Pname, COUNT (*)
FROMPROJECT, WORKS_ON
WHEREPnumber=Pno
GROUP BYPnumber, Pname;
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
Grouping: The GROUP BY and HAVING Clauses (contd.)
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:SELECTPnumber, Pname, COUNT (*)
FROMPROJECT, WORKS_ON
WHEREPnumber=Pno
GROUP BYPnumber, Pname
HAVINGCOUNT (*) > 2;
Slide 7- 31
Copyright 2016 and Shamkant B. Navathe
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:
SELECTDno, COUNT (*)
FROMEMPLOYEE
WHERESalary>40000
GROUP BYDno
HAVINGCOUNT (*) > 5;
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
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- *
Copyright 2016 and Shamkant B. Navathe
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:UPDATEEMPLOYEE
SETSalary =
CASEWHENDno = 5THENSalary + 2000
WHENDno = 4THENSalary + 1500
WHENDno = 1THENSalary + 3000
Slide 7- *
Copyright 2016 and Shamkant B. Navathe
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:UPDATEDEPT_INFO
SETTotal_sal=100000
WHEREDname=Research;
cannot be processed because Total_sal is a computed value in the view definition
Slide 7- 51
Copyright 2016 and Shamkant B. Navathe
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
Copyright 2016 and Shamkant B. Navathe
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
Copyright 2016 and Shamkant B. Navathe
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- *
Copyright 2016 and Shamkant B. Navathe
Table 7.2 Summary of SQL Syntax
continued on next slide
Slide 7- 59
Copyright 2016 and Shamkant B. Navathe
Table 7.2 (continued) Summary of SQL Syntax
Slide 7- 60
CS: assignmentchef QQ: 1823890830 Email: [email protected]
Reviews
There are no reviews yet.