Name: [SOLVED] CS
Brand: Assignment Chef
SKU: 6184487665
Price: 25 USD
Availability: InStock
Rating: 5 (1 reviews)

5/5 - (1 vote)

DISTINCT FUNCTIONS

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- *

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- *

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 (contd.)
Slide 7- 9

Nested Queries (contd.)
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 (contd.)
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: 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- *

Aggregate Functions in SQL (contd.)
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: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- *

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

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- *

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:UPDATEEMPLOYEE

SETSalary =
CASEWHENDno = 5THENSalary + 2000
WHENDno = 4THENSalary + 1500
WHENDno = 1THENSalary + 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:UPDATEDEPT_INFO
SETTotal_sal=100000
WHEREDname=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: assignmentchef QQ: 1823890830 Email: [email protected]

Reviews

There are no reviews yet.

Only logged in customers who have purchased this product may leave a review.

Whatsapp Us

[SOLVED] CS

Reviews

Related products

[Solved] Indel

[Solved] Python program that plays a guess-the-number game with magic lists

[Solved] Modularized Body Mass Index (BMI) Program in Python

[Solved] Program that reads in the file climate_data_2017_numeric.csv

[Solved] Car.py

[SOLVED] Naughty Receiver – Reliable Data Transfer