1 / 85

Riyadh Philanthropic Society For Science Prince Sultan College For Woman

Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries). Outline Introduction Data Definition Language CREATE TABLE

tonygarcia
Download Presentation

Riyadh Philanthropic Society For Science Prince Sultan College For Woman

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries)

  2. Outline • Introduction • Data Definition Language • CREATE TABLE • ALTER TABLE • DROP TABLE • Data Manipulation Language • SELECT Statement • INSERT Statement • DELETE Statement • UPDATE Statement Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 1

  3. Introduction • SQL: Structured Query Language. • Standard for commercial relational DBMSs, a joint effort by ANSI • & ISO. • Benefits of a standardized relational language: • Reduced training costs. • Productivity. • Application portability. • Application longevity. • Reduced dependence on a single vendor. • Cross-system communication. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 2

  4. “Declarative” Versus “Procedural” • Most DBMSs provide the user with a high-level declarative • language interface. • The user has just to specify “what” results he/she would like, • leaving the “how” to the DBMS. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 3

  5. Schema and Catalog Concepts in SQL • The concept of an SQL Schema was incorporated to group together • tables & other constructs that belong to the same database • application. • Schema: the structure that contains descriptions of objects created • by the user (tables, constraints, views, domains, character sets, • triggers, rules). • Catalog: a set of schemas that constitute the description of a • database. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 4

  6. Types of SQL Statements • Data Definition Language (DDL): • Commands that define a database. • E.g. CREATE, ALTER, DROP, ...etc. • Data manipulation language (DML): • Commands that maintain and query a database. • E.g. SELECT, INSERT, UPDATE, DELETE. • Data Control Language (DCL): • Commands that control a database, including administering • privileges and committing data. • E.g. CONNECT, GRANT, REVOKE, ...etc. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 5

  7. CREATE Statements • Major CREATE statements: • CREATE SCHEMA: • Defines a portion of the database owned by a particular user. • CREATE SCHEMA COMPANY AUTHORIZATION JSMITH. • CREATE TABLE: • Defines a table and its columns. • CREATE VIEW: • Defines a logical table from one or more views. • Other CREATE statements: DOMAIN , CHARACTER SET, • COLLATION, TRANSLATION, ASSERTION. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 6

  8. CREATE TABLE Construct • CREATE TABLE name ( • column-1 TYPE [DEFAULT value] [constraints], • column-2 TYPE [DEFAULT value] [constraints], • : • column-n TYPE [DEFAULT value] [constraints], • [table-constraints]); • Where: • name: table name. • column-1 to column-n: column names. • TYPE: data type. • [DEFAULT value]: optional default value. • [constraints]: optional constraints, can be specified at column • level or at table level. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 7

  9. SQL Data Types (From Oracle) • CHAR(n): a fixed-length string n characters long • (default 1, max 255 character). • VARCHAR2(n): a variable string up to n character long (max 2000). • LONG: a variable length character string up to 2 GB long. • NUMBER: a signed floating point number up to 38 digits in length. • NUMBER(n,d): a signed number n digits long including d decimal • places. • NUMBER(n): a signed integer n digits long (max 38 digits). • DATE: a valid date and time. • RAW: binary data up to 64K in size (image data for example). • LONG RAW: binary data up to 2 GB in size. • CLOB: character large object up to 4GB in size. • BLOB: binary large object up to 4GB in size. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 8

  10. CREATE DOMAIN Construct • A domain can be declared, and the domain name is used with the • attribute specification. • This makes it easier to change the data type for a domain that is • used by numerous attributes in a schema, and improves schema • readability. • Examples: • CREATE DOMAIN SSN_TYPE AS CHAR(9) • CREATE DOMAIN DNUM_TYPE AS NUMBER(2) CHECK • (DNUM>0 AND DNUM<21) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 9

  11. Constraints • There are essentially five types of constraints: • NOT NULL constraints. • UNIQUE value constraints. • PRIMARY KEY constraints. • FOREIGN KEY constraints. • CHECK constraints. • The syntax of these constraints varies according to whether they are • specified at column level or at table level. • Any constraints which apply to multiple columns must be defined as • table level constraints. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 10

  12. Constraints • The syntax for each of the column level constraints is: • NOT NULL • UNIQUE • PRIMARY KEY • REFERENCES table (key) • CHECK (condition) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 11

  13. Constraints • The syntax for each of the table level constraints is: • UNIQUE (column-list) • [CONSTRAINT name] PRIMARY KEY (column-list) • [CONSTRAINT name] FOREIGN KEY (column-list) • REFERENCES table (column-list) • CHECK (condition) • An additional ON DELETE clause may be applied to a foreign key • constraint: • ON DELETE CASCADE causes all referencing child records to • be deleted whenever a parent record is deleted. • ON DELETE SET NULL causes all referencing child records to • be set to NULL whenever a parent record is deleted. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 12

  14. To improve access speed • Steps in Table Creation • Identify data types for attributes. • Determine default values. • Identify columns that can and cannot be null. • Identify columns that must be unique (candidate keys). • Identify primary key - foreign key mates. • Identify constraints on columns (domain specifications). • Create the table and associated indexes. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 13

  15. CREATE TABLE Example • CREATE TABLE EMPLOYEE ( • FNAME VARCHAR2(15) NOT NULL, • MINIT CHAR, • LNAME VARCHAR2(15) NOT NULL, • SSN CHAR(9), • BDATE DATE, • ADDRESS VARCHAR2(30), • SEX CHAR CHECK (LOWER(SEX) IN ('m', 'f')), • SALARY NUMBER(10,2), • SUPERSSN CHAR(9), • DNO NUMBER(2) NOT NULL, • CONSTRAINT EMP_PKEY PRIMARY KEY (SSN), • CONSTRAINT EMP_FKEY1 FOREIGN KEY (SUPERSSN) REFERENCES • EMPLOYEE (SSN), • CONSTRAINT EMP_FKEY2 FOREIGN KEY (DNO) REFERENCES • DEPARTMENT (DNUMBER)) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 14

  16. ALTER TABLE Construct • Tables can be changed in a number of ways using the SQL • statement ALTER TABLE. • Possible actions include: • Adding or dropping a column. • Adding or dropping a constraint. • Changing a column definition. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 15

  17. ALTER TABLE Construct • To add two columns to the EMPLOYEE table: • ALTER TABLE EMPLOYEE ADD ( • TITLE CHAR(3) CHECK(LOWER(TITLE) IN ('mr', 'mrs', 'ms')), • TEL_NO CHAR(7)) • To add a constraint to the DEPARTMENT table: • ALTER TABLE DEPARTMENT ADD ( • CONSTRAINT D_FKEY FOREIGN KEY (MGRSSN) • REFERENCES EMPLOYEE (SSN) ON DELETE CASCADE) • To increase the width of LNAME column: • ALTER TABLE EMPLOYEE MODIFY ( • LNAME VARCHAR2(20)) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 16

  18. ALTER TABLE Construct • To add a NOT NULL constraint to the ADDRESS column: • ALTER TABLE EMPLOYEE MODIFY ( • ADDRESS NOT NULL) • To drop the attribute ADDRESS from the EMPLOYEE table: • ALTER TABLE EMPLOYEE DROP (ADDRESS) CASCADE • CONSTRAINTS • To drop a constraint named D_FKEY from DEPARTMENT table: • ALTER TABLE DEPARTMENT DROP CONSTRAINT D_FKEY Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 17

  19. ALTER TABLE Construct • To drop an existing default clause: • ALTER TABLE DEPARTMENT ALTER MGRSSN DROP • DEFAULT • To define a new default clause: • ALTER TABLE DEPARTMENT ALTER MGRSSN SET • DEFAULT '333445555' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 18

  20. DROP TABLE Construct • The DROP TABLE command deletes all information about the • dropped relation from the database. • Syntax: • DROP TABLE name [CASCADE CONSTRAINTS] • Where: • name: is the name of the table to be dropped. • CASCADE CONSTRAINTS: drops all referential integrity • constraints which refer to keys in the dropped table. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 19

  21. Basic Queries in SQL • SQL has one basic statement for retrieving data from a database, • the SELECT statement. • This is not the same as the SELECT operation of the relational • algebra. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 20

  22. Mandatory • The SQL SELECT Statement • It consists of the following basic clauses: • SELECTspecifies the columns which are to be retrieved • FROMspecifies the tables from which data is to be retrieved • WHEREdefines any selection criteria required • GROUP BYspecifies how output is to be grouped & summarized • HAVINGdetermines which group summaries to include in the output • ORDER BYdefines how the output will be stored Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 21

  23. The SELECT-FROM-WHERE Structure • The basic form of the SELECT statement is: • SELECT [DISTINCT] item-list • FROM table-list • WHERE condition • Where: • item-list: a comma-separated list of items to be retrieved. • (database attributes, functions, fixed text, ...etc.) • table-list: a comma-separated list of tables from which the • attributes are to be retrieved. • condition: a conditional (Boolean) expression that identifies the • tuples to be retrieved by the query. • [DISTINCT]: ensures only unique tuples will be returned. • The result of an SQL query is a relation (can be a multiset) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 22

  24. The SELECT-FROM-WHERE Structure • Query: retrieve the birthdate and address of the employee(s) • whose name is ‘John B. Smith’. • SELECT BDATE, ADDRESS • FROM EMPLOYEE • WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith' • Is it similar to a tuple relational expression? Any differences? • SELECT Statement Relational Algebra • SELECT clause project operation • FROM clause cartesian product operation • WHERE clause select condition Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 23

  25. The SELECT-FROM-WHERE Structure • Query: retrieve the name and address of all employees who • work for the ‘Research’ department. • Query: for every project located in ‘Stafford’, list the project • number, the controlling department number, and the department • manager’s last name, address, and birthdate. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 24

  26. Ambiguous Attribute Names, Aliasing, and Tuple Variables • In SQL if the same attribute name is used in more than one • relation, the attribute names must be qualified. • Assume that DNO attribute of the EMPLOYEE relation was • called DNUMBER. • Query: retrieve the name and address of all employees who • work for the ‘Research’ department. • SELECT FNAME, LNAME, ADDRESS • FROM EMPLOYEE, DEPARTMENT • WHERE DNAME='Research' AND • EMPLOYEE.DNUMBER=DEPARTMENT.DNUMBER Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 25

  27. Ambiguous Attribute Names, Aliasing, and Tuple Variables • Ambiguity also arise in the case of queries that refer to the same • relation twice, as in the following example. • Query: for each employee, retrieve the employee’s first and last • name and the first and last name of his or her immediate supervisor. • SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME • FROM EMPLOYEE E, EMPLOYEE S • WHERE E.SUPERSSN=S.SSN Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 26

  28. Unspecified WHERE Clause • Query: select all EMPLOYEE SSNs. • SELECT SSN • FROM EMPLOYEE • Query: select all combinations of EMPLOYEE SSN and • DEPARTMENT DNAME. SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 27

  29. The Use of Asterisk • Query: retrieve all the attribute values of any EMPLOYEE who • work in DEPARTMENT 5. • SELECT * • FROM EMPLOYEE • WHERE DNO =5 • Query: retrieve all the attributes of an EMPLOYEE and all the • attributes of the DEPARTMENT in which he or she works for • every employee of the ‘Research’ department. SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 28

  30. A set • Tables as Sets in SQL • Query: retrieve the salary of every employee. • SELECT SALARY • FROM EMPLOYEE • Query: retrieve all distinct salary values. • SELECT DISTINCT SALARY • FROM EMPLOYEE Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 29

  31. Union compatible • The UNION, INTERSECT, & MINUS SET OPERATORS • The relations resulting from these set operations are sets of tuples; • that is, duplicate tuples are eliminated from the result. • Query: make a list of all project numbers for projects that involve • an employee whose last name is ‘Smith’, either as a worker or as • a manager of the department that controls the project. • SELECT DISTINCT PNUMBER • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE DNUM=DNUMBER AND MGRSSN=SSN AND • LNAME='Smith' • UNION • SELECT DISTINCT PNUMBER • FROM PROJECT, WORKS_ON, EMPLOYEE • WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 30

  32. Conditional Operators • Main conditional operators include: =, <>, >, <, >=, <=. • Other conditional operators: • IN: tests if an item value appears in a specified list of values. • BETWEEN: tests if an item value lies (inclusively) between • two specified values. • IS NULL: tests if an item has a Null value. Null is neither zero • (in a numeric item), nor spaces (in a character item). • LIKE: tests if an item value matches a string containing • wildcard characters. The wildcard characters are: • %: meaning zero or more occurrences of any character. • _: meaning a single occurrence of any characters. • The keyword NOT can be used in conjunction with all the above • operators to revise the test. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 31

  33. Conditional Operators • Query: retrieve the names of all employees whose salary is not • (20000, 30000, or 40000). • Query: retrieve all employees in department 5 whose salary is • between $30,000 and $40,000. SELECT FNAME, LNAME FROM EMPLOYEE WHERE SALARY NOT IN (20000, 30000, 40000) SELECT * FROM EMPLOYEE WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5 Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 32

  34. Conditional Operators • Query: retrieve the names of all employees who do not have • supervisors. • Query: retrieve all employees whose address is in Houston, Texas. SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston, TX%' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 33

  35. Arithmetic Operations • The standard arithmetic operations (+, -, *, /) can be applied to • numeric values or attributes with numeric domains. • Query: show the resulting salaries if every employee working on the • ‘ProductX’ project is given 10 percent raise. • SELECT FNAME, LNAME, 1.1*SALARY INCREASED_SAL • FROM EMPLOYEE, WORKS_ON, PROJECT • WHERE SSN=ESSN AND PNO=PNUMBER AND • PNAME='ProductX' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 34

  36. Ordering of Query Results • SQL allows the user to order the tuples in the result of a query by • the values of one or more attributes, using the ORDER BY clause. • Query: retrieve a list of employees and the projects they are working • on, ordered by department and, within each department, ordered • alphabetically by last name, first name. • SELECT DNAME, LNAME, FNAME, PNAME • FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT • WHERE DNUMBER=DNO AND SSN=ESSN AND • PNO=PNUMBER • ORDER BY DNAME, LNAME, FNAME • The default order is in ascending order. • ORDER BY DNAME DESC, LNAME ASC, FNAME ASC Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 35

  37. The Three Valued Logic • Any comparison with Null returns unknown, for example, 5 < Null. • Three- valued logic using the truth value unknown: • OR: • (unknown OR true) = true. • (unknown OR false) = unknown. • (unknown OR unknown) = unknown. • AND: • (unknown AND true) = unknown. • (unknown AND false) = false. • (unknown AND unknown) = unknown. • NOT: • (NOT unknown) = unknown. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 36

  38. Nested Queries • Some queries require that existing values in the database be fetched • and then used in a comparison condition. • Such queries can be conveniently formulated by using nested • queries. • Nested queries: complete select-from-where blocks usually within • the WHERE clause of another query. That other query is called the • outer query. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 37

  39. Nested Queries • Query: make a list of all project numbers for projects that involve • an employee whose last name is ‘Smith’, either as a worker or as • a manager of the department that controls the project. • SELECT DISTINCT PNUMBER • FROM PROJECT • WHERE PNUMBER IN (SELECT PNUMBER • FROM PROJECT, EMPLOYEE, • DEPARTMENT • WHERE DNUM=DNUMBER • AND MGRSSN=SSN • AND LNAME='Smith') • OR • PNUMBER IN (SELECT PNO • FROM WORKS_ON, EMPLOYEE • WHERE ESSN=SSN • AND LNAME='Smith') Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 38

  40. Nested Queries • SQL allows the use of tuples of values in comparisons by placing • them within parentheses. • Example: • SELECT DISTINCT ESSN • FROM WORKS_ON • WHERE (PNO, HOURS) IN (SELECT PNO, HOURS • FROM WORKS_ON • WHERE ESSN='123456789') Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 39

  41. Nested Queries • In addition to the IN operator, a number of other comparison • operators can be used to compare a single value (attribute) to a set • or multiset. • The keywords ANY or ALL can be combined with the following • operators: >, >=, <, <=, <>. (= ANY is equivalent to IN) • Query: list the names of employees whose salary is greater than the • the salary of all the employees in department 5. SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO=5) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 40

  42. Correlated Nested Queries • Whenever a condition in the WHERE clause of a nested query • references some attribute of a relation declared in the outer query, • the two queries are said to be correlated. • The result of a correlated nested query is different for each tuple (or • combination of tuples) of the relation(s) the outer query. • Query: retrieve the name of each employee who has a dependent • with the same name and same sex as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE E.FNAME=DEPENDENT_NAME AND E.SEX=SEX) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 41

  43. Correlated Nested Queries • In general, a query with nested select-from-where blocks and using • the = or IN comparison operators can always be expressed as a • single block query. • Query: retrieve the name of each employee who has a dependent • with the same name and same sex as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN=D.ESSN AND E.SEX=D.SEX AND E.FNAME=D.DEPENDENT_NAME Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 42

  44. The EXISTS Function in SQL • This function checks whether the result of a correlated nested query • is empty (contains no tuples) or not. • Query: retrieve the name of each employee who has a dependent • with the same first name and same sex as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN=ESSN AND E.SEX=SEX AND E.FNAME=DEPENDENT_NAME) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 43

  45. The EXISTS Function in SQL • Query: retrieve the names of employees who have no dependents. SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 44

  46. The EXISTS Function in SQL • Query: list the names of managers who have at least one dependent. SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) AND EXISTS (SELECT * FROM DEPARTMENT WHERE SSN=MGRSSN) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 45

  47. The EXISTS Function in SQL • Query: retrieve the name of each employee who works on all the • projects controlled by department number 5. SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS ( (SELECT PNUMBER FROM PROJECT WHERE DNUM=5) MINUS (SELECT PNO FROM WORKS_ON WHERE SSN=ESSN) ) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 46

  48. Renaming of Attributes in SQL • It is possible to rename any attribute that appears in the result of a • query. • Alias names are specified following the selected attributes and must • be bounded by double quotes if they contain spaces. • Attribute aliases can be referenced only in the ORDER BY clause. • Example: • SELECT E.LNAME Name, S.LNAME "Supervisor name" • FROM EMPLOYEE E, EMPLOYEE S • WHERE E.SUPERSSN=S.SSN Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 47

  49. Outer Join • An outer join is specified by appending a parenthesized plus sign (+) • to the name of the foreign key in the join condition. • Example: • SELECT FNAME, LNAME, DEPENDENT_NAME • FROM EMPLOYEE, DEPENDENT • WHERE SSN=ESSN(+) • ORDER BY FNAME • Even when no dependents exist for a particular employee, that • employee’s name is retrieved. Null value will be replaced in the • dependent’s name. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 48

  50. Aggregate Function in SQL • A number of built-in functions exist: COUNT, SUM, MAX, MIN, • and AVG. • These functions can be used in the SELECT clause or in the • HAVING clause. • The functions SUM, MAX, MIN, and AVG are applied to a set or • multiset of numeric values. • The functions MAX and MIN can also be used with attributes that • have nonnumeric domains if the domain values have a total • ordering among one another. • Null values are discarded when aggregate functions are applied to a • particular attribute. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 49

More Related