Database Management System 10CS54 VTU unit 4

The Eduladder is a community of students, teachers, and programmers just interested to make you pass any exams. So we solve previous year question papers for you.
In eduladder you can Ask,Answer,Listen,Earn and Download Questions and Question papers.
Watch related videos of your favorite subject.
Connect with students from different parts of the world.
Apply or Post Jobs, Courses ,Internships and Volunteering opportunity. For FREE
See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions


You are here:Open notes-->VTU-->Database-Management-System-10CS54-VTU-unit-4

Database Management System 10CS54 VTU unit-4

UNIT 4 SQL The Relational Database Standard


4.1 Data Definition, Constraints, and Schema Changes in SQL2 
 Structured Query Language (SQL) was designed and implemented at IBM Research. 
 Created in late 70’s, under the name of SEQUEL 
 A standard version of SQL (ANSI 1986), is called SQL86 or SQL1. 
 A revised version of standard SQL, called SQL2 (or SQL92). 
 SQL are going to be extended with objectoriented and other recent database concepts.
 Consists of 
 A Data Definition Language (DDL) for declaring database schemas 
 Data Manipulation Language (DML) for modifying and querying database 
instances 
 In SQL, relation, tuple, and attribute are called table, row, and columns respectively. 
 The SQL commands for data definition are CREATE, ALTER, and DROP. 
 The CREATE TABLE Command is used to specify a new table by giving it a name and 
specifying its attributes (columns) and constraints. 
 Data types available for attributes are: 
o Numeric integer, real (formated, such as DECIMAL(10,2)) 
o CharacterString fixedlength and varyinglength 
o BitString fixedlength, varyinglength 
o Date in the form YYYYMMDD 
o Time in the form HH:MM:SS 
o Timestamp includes both the DATE and TIME fields 
o Interval to increase/decrease the value of date, time, or timestamp

4.2 Basic Queries in SQL
 SQL allows a table (relation) to have two or more tuples that are identical in all their 
attributes values. Hence, an SQL table is not a set of tuple, because a set does not allow 
two identical members; rather it is a multiset of tuples. 
 A basic query statement in SQL is the SELECT statement. 
 The SELECT statement used in SQL has no relationship to the SELECT operation of 
relational algebra. 
The SELECT Statement
The syntax of this command is:
SELECT <attribute list> 
FROM <table list> 
WHERE <Condition>;
Query 0: Retrieve the birthday and address of the employee(s) whose name is ‘John B. Smith’ 
 Q0: SELECT BDATE, ADDRESS 
 FROM EMPLOYEE 
 WHERE FNAME = ‘John’ AND MINIT =‘B’ AND LNAME = ‘SMITH’ 
Query 1: Retrieve the name and address of all employee who work for the ‘Research’ Dept.
 Q1: SELECT FNAME, LNAME, ADDRESS
 FROM EMPLOYEE, DEPARTMENT 
 WHERE DNAME = ‘Research’ AND DNUMBER = DNO
Query 2: For every project located in ‘Stafford’, list the project number, the controlling 
department number, and the department manager’s last name, address, and birthdate.
Q2: SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
 FROM PROJECT, DEPARTMENT, EMPLOYEE 
 WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION = 
‘Stafford’
Dealing with Ambiguous Attribute Names and Renaming (Aliening)
Ambiguity in the case where attributes are same name need to qualify the attribute using DOT 
separator
e.g., WHERE DEPARTMENT.DNUMBER=EMPLOYEE.DNUMBER
More Ambiguity in the case of queries that refer to the same relation twice
 Query 8: For each employee, retrieve the employee’s first and last name and the first and last 
name of his or her immediate supervisor
Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
 FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN
Unspecified WHEREClause and Use of Asterisk (*)
A missing WHEREclause indicates no conditions, which means all tuples are selected
In case of two or more table, then all possible tuple combinations are selected
Example: Q10: Select all EMPLOYEE SSNs , and all combinations of EMPLOYEE SSN and 
DEPARTMENT DNAME
SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT
More
 To retrieve all the attributes, use * in SELECT clause
Retrieve all employees working for Dept. 5
 SELECT *
 FROM EMPLOYEE
 WHERE DNO=5
Substring Comparisons, Arithmetic Operations, and Ordering
 like, binary operator for comparing strings 
 %, wild card for strings 
 _, wild card for characters 
 ||, concatenate operation for strings 
(name like ’%a_’) is true for all names having ‘a’ as second letter from the end. 
 Partial strings are specified by using ' 
 SELECT FNAME, LNAME 
 FROM EMPLOYEE
 WHERE FNAME LIKE '%Mc%';
 In order to list all employee who were born during 1960s we have the followings: 
 SELECT FNAME, LNAME
. FROM EMPLOYEE
 WHERE BDATE LIKE '6_______';
 SQL also supports addition, subtraction, multiplication and division (denoted by +, , *, 
and /, respectively) on numeric values or attributes with numeric domains. 
Examples: Show the resulting salaries if every employee working on the 'ProductX' project is 
given a 10 percent raise. 
 SELECT FNAME, LNAME, 1.1*SALARY 
 FROM EMPLOYEE, WORKS_ON, PROJECT
 WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX';
Retrieve all employees in department number 5 whose salary between $30000 and $40000. 
 SELECT *
 FROM EMPLOYEE 
 WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO=5;
It is possible to order the tuples in the result of a query. 
 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, but user can specify 
 ORDER BY DNAME DESC, LNAME ASC, FNAME, ASC;

Tables as Sets in SQL

SQL treats table as a multiset, which means duplicate tuples are OK 
SQL does not delete duplicate because Duplicate elimination is an expensive operation (sort and
delete) user may be interested in the result of a query in case of aggregate function, we do not 
want to eliminate duplicates 
 To eliminate duplicate, use DISTINCT
examples 
 Q11: Retrieve the salary of every employee , and (Q!2) all distinct salary values
 Q11: SELECT ALL SALARY 
 FROM EMPLOYEE 
 Q12: SELECT DISTINCT SALARY
FROM EMPLOYEE

4.3 More Complex SQL Queries

 Complex SQL queries can be formulated by composing nested SELECT/FROM/WHERE 
clauses within the WHEREclause of another query
Example: Q4: Make a list of Project numbers for projects that involve an employee whose last 
name is ‘Smith’, either as a worker or as a manger of the department that controls the project
Q4 SELECT DISTINCT PNUMBER
 FROM PROJECT 
 WHERE PNUMBER IN (SELECT PNUMBER 
 FROM PROJECT, DEPARTMENT, EMPLOYEE 
 WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’
OR PNUMBER IN (SELECT PNO 
 FROM WORKS_ON, EMPLOYEE 
 WHERE ESSN=SSN AND LNAME=‘Smith’)
IN operator and set of unioncompatible tuples
 Example:
 
 SELECT DISTINCT ESSN
 FROM WORKS_ON 
 WHERE (PNO, HOURS) IN (SELECT PNO, HOURS
 FROM WORKS_ON 
 WHERE SSN=‘123456789’
 ANY, SOME and >, <=,<>,etc.

The keyword ALL

In addition to the IN operator, a number of other comparison operators can be used to compare a 
single value v to a set of multiset V.
ALL V returns TRUE if v is greater than all the value in the set 
Select the name of employees whose salary is greater than the salary of all the employees in 
department 5
 SELECT LNAME, FNAME
 FROM EMPLOYEE
 WHERE SALARY > ALL (SELECT SALARY
 FROM EMPLOYEE
 WHERE DNO=5);
Ambiguity in nested query
 SELECT E.FNAME, E.LNAME 
 FROM EMPLOYEE AS E
 WHERE E.SSN IN (SELECT ESSN
 FROM DEPENDENT
 WHERE ESSN=E.SSN AND E.FNAM=DEPENDENT_NAME AND 
SEX=E.SEX
Correlated Nested Query
Whenever a condition in the WHEREclause of a nested query references some attributes 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.
In general, any nested query involving the = or comparison operator IN can always be 
rewritten as a single block query 
 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
Query 12: Retrieve the name of each employee who has a dependent with the same first name as 
the employee.
Q12: SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN (SELECT ESSN
FROM DEPENDENT
WHEREESSN=E.SSN AND
E.FNAME=DEPENDENT_NAME)
In Q12, the nested query has a different result for each tuple in the outer query.
The original SQL as specified for SYSTEM R also had a CONTAINS comparison operator, 
which is used in conjunction with nested correlated queries This operator was dropped from the 
language, possibly because of the difficulty in implementing it efficiently Most implementations 
of SQL do not have this operator The CONTAINS operator compares two sets of values , and 
returns TRUE if one set contains all values in the other set (reminiscent of the division operation 
of algebra).
Query 3: Retrieve the name of each employee who works on all the projects controlled by 
department number 5.
Q3: SELECT FNAME, LNAME
FROM EMPLOYEE WHERE ( (SELECT PNO FROM WORKS_ON WHERE SSN=ESSN)
CONTAINS (SELECT PNUMBER FROM PROJECT WHERE DNUM=5) )
In Q3, the second nested query, which is not correlated with the outer query, retrieves the project 
numbers of all projects controlled by department 5.
The first nested query, which is correlated, retrieves the project numbers on which the employee 
works, which is different for each employee tuple because of the correlation.

THE EXISTS AND UNIQUE FUNCTIONS IN SQL

EXISTS is used to check whether the result of a correlated nested query is empty (contains no 
tuples) or not We can formulate Query 12 in an alternative form that uses EXISTS as Q12B 
below.
Query 12: Retrieve the name of each employee who has a dependent with the same first name as 
the employee.
 SELECT E.FNAME, E.LNAME 
 FROM EMPLOYEE E 
 WHERE EXISTS (SELECT *
 FROM DEPENDENT
 WHERE E.SSN=ESSN AND SEX=E.SEX AND 
E.FNAME=DEPENDENT_NAME
Query 6: Retrieve the names of employees who have no dependents.
Q6: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERESSN=ESSN)
In Q6, the correlated nested query retrieves all DEPENDENT tuples related to an EMPLOYEE 
tuple. If none exist , the EMPLOYEE tuple is selected EXISTS is necessary for the expressive 
power of SQL
EXPLICIT SETS AND NULLS IN SQL
It is also possible to use an explicit (enumerated) set of values in the WHEREclause rather than 
a nested query Query 13: Retrieve the social security numbers of all employees who work on 
project number 1, 2, or 3.
 Retrieve SSNs of all employees who work on project number 1,2,3 
 SELECT DISTINCT ESSN
 FROM WORKS_ON
 WHERE PNO IN (1,2,3)
Null example
SQL allows queries that check if a value is NULL (missing or undefined or not applicable) SQL 
uses IS or IS NOT to compare NULLs because it considers each NULL value distinct from other 
NULL values, so equality comparison is not appropriate .
 Retrieve the names of all employees who do not have supervisors
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL
Note: If a join condition is specified, tuples with NULL values for the join attributes are not 
included in the result
Join Revisit
 Retrieve the name and address of every employee who works for ‘Search’ department 
 SELECT FNAME, LNAME, ADDRESS 
 FROM (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER) 
 WHERE DNAME=‘Search’ 

Aggregate Functions
Include COUNT, SUM, MAX, MIN, and AVG
Query 15: Find the sum of the salaries of all employees the ‘Research’ dept, and the max salary, 
the min salary, and average: 
 SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY) AVG(SALARY)
 FROM EMPLOYEE 
 WHERE DNO=FNUMBER AND DNAME=‘RSEARCH’
Query 16: Find the maximum salary, the minimum salary, and the average salary among 
employees who work for the 'Research' department.
Q16: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research'
Queries 17 and 18: Retrieve the total number of employees in the company (Q17), and the 
number of employees in the 'Research' department (Q18).
Q17: SELECT COUNT (*)
FROM EMPLOYEE
Q18: SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research'
Example of grouping
In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation
Each subgroup of tuples consists of the set of tuples that have the same value for the grouping 
attribute(s)
The function is applied to each subgroup independently
SQL has a GROUP BYclause for specifying the grouping attributes, which must also appear in 
the SELECTclause
 For each project, select the project number, the project name, and the number of employees 
who work on that projet
 SELECT PNUMBER, PNAME, COUNT(*) 
FROM PROJECT, WORKS_ON 
 WHERE PNUMBER=PNO 
 GROUP BY PNUMBER, PNAME
In Q20, the EMPLOYEE tuples are divided into groupseach group having the same value for the 
grouping attribute DNO
The COUNT and AVG functions are applied to each such group of tuples separately.The 
SELECTclause includes only the grouping attribute and the functions to be applied on each 
group of tuples. A join condition can be used in conjunction with grouping
Query 21: For each project, retrieve the project number, project name, and the number of 
employees who work on that project.
Q21: SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
In this case, the grouping and functions are applied after the joining of the two 
relations
THE HAVINGCLAUSE:
Sometimes we want to retrieve the values of these functions for only those groups that satisfy 
certain conditions. The HAVINGclause is used for specifying a selection condition on groups 
(rather than on individual tuples)
Query 22: For each project on which more than two employees work , retrieve the project 
number, project name, and the number of employees who work on that project.
Q22: SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2



Editors




You might like this video:Amazon Web Services
Watch more here

Watch more videos from this user Here

Learn how to upload a video and start earning here