DATA MANIPULATION LANGUAGE (DML)

Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%

update instructor
set salary = salary*1.03
where salary > 100000

update instructor
set salary = salary*1.05
where salary <= 100000

Case Statements: Conditional Updates

• Case statements work like if-else statements in SQL.

case
    when <predicate1> then <result1>,
    when <predicate2> then <result2>,
    ....
    else <resultn>
end

• case marks the beginning of the block and end keyword marks the end.
• Inside the block, when and then keywords are used to convey that if predicate1 turns out to be true, then the query will return result1, and so on.
• else keyword is used to return a result when none of the above predicates evaluate to true.

Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%

update instructor
set salary = case

when salary <= 100000
then salary * 1.05
else salary * 1.03
end

• In the above query, the salary of the instructor is replaced by 1.05 times the salary if salary<=>100000, else it is replaced by 1.03 times the salary.

SQL QUERY SYNTAX:

select A1, A2, . . . , An,
from r1,r2, ...,rm
where P

where,
Ai = Attribute name.
ri = relation name.
P = Predicate.

• SELECT CLAUSE - Lists the attributes required in the result. (Projection operation in relational algebra)
• FROM CLAUSE - Specifies conditions that the result must satisfy. (Selection predicate in relational algebra)
• WHERE CLAUSE - Lists the relations involved in the query. (Cartesian product in relational algebra)

Find the names of all instructors?

select "name"
from instructor

select all "name"
from instructor

• Both the above queries are equivalent and return all the values in name column from the instructor relation.
• The keyword all allows duplicates and is the default output if not specified.
• SQL allows duplicates.

select distinct dept_name
from instructor

• The keyword distinct removes duplicate values from the column dept_name of instructor table.

select *
from instructor

• * means all the attributes.
• Returns the entire table instructor with all attributes.

select '437'

• Attribute is a literal without a from clause.
• Results a table with 1 column and a single row with value '437'.

select '437' as blah

• The above returned column can be named as 'blah'.

select 'a'
from instructor

• Attribute is a literal with a from clause.
• Returns a table with one column with a value 'A' in all the rows.
• Number of rows returned = Number of rows in the table instructor.

select ID, name, salary/12
from instructor

• Can use arithmetic expressions (- +,-,*,/) in Select clause.
• Returns the ID, name and salary from instructor with all the values of the salary column divided by 12.
• The salary/12 column has no name.

select ID, name, salary/12 as monthly_salary
from instructor

• Here the salary/12 column has been renamed as monthly_salary using the 'as' keyword.

select name
from instructor
where dept_name = 'Comp. Sci.'

• Returns a table with 1 column - name, containing the names of the instructors from the department 'Comp. Sci.' from instructor.

select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 80000

• Logical connectives - AND,OR and NOT can be used in the where clause.
• Returns all instructors in Comp. Sci. dept with salary > 80000.

Basic Operations:

Cartesian Product

select *
from instructor, teaches

• Returns all attributes in the cartesian product of instructor and teaches.
• mn rows returned where m= Number of rows in instructor, n= Number of rows in teaches.

Examples:

Find the names of all instructors who have taught some course and the course id.

select "name", course_id
from instructor,teaches
where instructor.ID = teaches.ID

• Returns a relation with 2 columns name (names of the instructors) and course_id (ID of the course they taught).

Find the names of all instructors in the Art department who have taught some course and the course id.

select "name", course_id
from instructor,teaches
where instructor.ID = teaches.ID and instructor.dept_name = 'Art'

Note: In the above queries, the attribute name has been written in double quotes because it is a built-in type in postgresql. Although the query gives the same result without the double quotes, enclosing the keywords in quotes is a good practice to distinguish them from the attribute names of the relation.

AS Operator

Syntax:

old_name as new_name

Find the names of all instructors who have a higher salary than some instructor in 'Comp. Sci'.

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci'

• Here the 'AS' Operator is used to rename the relations. In order to perform a self cartesian product, we renamed the instructor table as T and the other as S. This enables us to use the attributes of the 2 instructor tables in a specific way in the where clause.

• 'AS' Keyword is optional and can be omitted:

select distinct T.name
from instructor T, instructor S
where T.salary > S.salary and S.dept_name = 'Comp. Sci'

String Operators

like operator

Find the names of all instructors whose name includes the substring "dar".

select name
from instructor
where name like '%dar%'

• Here, the like operator compares the strings:
  percent (%) - matches any substring.
  underscore (_) - matches any character.

Names like Sardar , Darwin, Sardarwin ,etc. will be matched.

Match the string "100%"

like '100%' escape '\'

select name
from instructor
where name like '___'

• Here, 3 underscores(_) have been used which will match any string of exactly three characters.

select name
from instructor
where name like '___%'

• The above query matches any string of at least three characters.

String Concatenation:

select 'str1' || 'str2' 

• Output = One single column named 'str1str2'.

select 'str1' || 'something ' || 'str2' 

• Ouput = One single column names 'str1somethingstr2'.

select 'str1' || NULL AS res

• Concatenating a string with NULL value returns NULL.

select concat('str1','str2')

• The concat() function takes 2 strings and concatenates them in the same order.
• Output = One single column named 'str1str2'.

select lower(name) from instructor

• lower() function takes the name column from the instructor and converts all the values in the column in lower case.

select upper(name) from instructor

• upper() function takes the name column from the instructor and converts all the values in the column in upper case.

SELECT name,length(name) as "Length of Name" 
FROM instructor 
WHERE length(name)>7;

• length() function takes the name column from the instructor and calculates the length of all the values in the column and renames the column as 'Length of Name' and filters out only those names having length greater than 7 as per the predicate in the where clause.

select id,name, substring(name,1,4) "1st 4 characters of name"
from instructor

• substring(str,start_index,end_index) function takes 3 arguments- string, starting index, ending index of the substring to be sliced from the string.

• The above query outputs a relation with 3 columns: id,name,1st 4 characters of name. The third column contains the substrings corresponding to the first 4 characters of the names.

Ordering the tuples:

select distinct name
from instructor
order by name

• Output: List in alphabetic order the names of all instructors. (Ascending order by default.)
• asc for ascending and desc for descneding can be specified explicitly.

select distinct name
from instructor
order by name desc

Sorting by multiple attributes:

select name , dept_name
from instructor
order by dept_name desc, name desc

• Here the query first sorts on dept_name attribute in descending order and then sorts name column in descending order on the tuples with same dept_name.

Selecting number of tuples in the output

select top 10 distinct name
from instructor

• Returns only top 10 tuples.
• Not all database systems support the SELECT TOP clause.

ORACLE uses:

select distinct name
from instructor
order by name
fetch first 10 rows only

Where clause predicates:

Between operator

Find the names of all instructors with salary between $90, 000 and $100, 000 (that is, ≥ $90, 000 and ≤ $100, 000)

select name
from instructor
where salary between 90000 and 100000

Tuple Comparison

select name, course id
from instructor,teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

• Can compare a tuple with another in the where clause.
• Ouput = A relation where instructor.ID matches with teaches.ID and the dept_name is 'Biology'.

In operator

select name
from instructor
where dept_name in ('Comp. Sci.', 'Biology')

• Used to specify multiple values in a where clause.
• Used as a shorthand for multiple or conditions.
• Outputs= All the names of the instructors having 'Comp. Sci.' or 'Biology' as the dept_name.

Set Operations

Union

• Combines the result of two or more select statements.
  Find courses that ran in Fall 2009 or in Spring 2010

(select course_id from section where semester = 'Fall' and year = 2009)
union
(select course_id from section where semester = 'Spring' and year = 2010)

Intersection

• Returns the common tuples from 2 or more select statements.
  Find courses that ran in Fall 2009 and in Spring 2010.

(select course_id from section where semester = 'Fall' and year = 2009)
intersect
(select course_id from section where semester = 'Spring' and year = 2010)

Set Difference

• Returns all the rows from the first select statement that are not returned by the second select statement.
  Find courses that ran in Fall 2009 but not in Spring 2010

(select course_id from section where semester = 'Fall' and year = 2009)
except
(select course_id from section where semester = 'Spring' and year = 2010)

Note: To retain all duplicates, use the corresponding multiset versions union all, intersect all, and except all.

Corresponding multi-set versions:

(select course_id from section where semester = 'Fall' and year = 2009)
union all
(select course_id from section where semester = 'Spring' and year = 2010)

(select course_id from section where semester = 'Fall' and year = 2009)
intersect all
(select course_id from section where semester = 'Spring' and year = 2010)

(select course_id from section where semester = 'Fall' and year = 2009)
except all
(select course_id from section where semester = 'Spring' and year = 2010)

EXAMPLE: Find the salaries of all instructors that are less than the largest salary

(select distinct T.salary
from instructor as T, instructor as S
where T.salary < S.salary) 
except
(select distinct salary
from instructor
)

• The first query returns salaries of all instructors that are less than the largest salary. The second query returns the salaries of all instructors. The overall output is the largest salary of all instructors.

IS NULL predicate

• Can be used to check for null values.
• Null values cannot be checked via comparison operators, such as =, <, or <>. Find all instructors whose salary is null.

select name
from instructor
where salary is null

Aggregate Functions

• Operate on the multiset of values of a column of a relation, and return a value.
• avg: average value
• sql select avg(salary) from instructor where dept_name = 'Comp. Sci'
• Returns the average salary of instructors in the Computer Science department.
• count: number of values
• sql select count(distinct ID) from teaches where semester = 'Spring' and year = 2010

• Returns the total number of instructors who teach a course in the Spring 2010 semester.

• sql select count(*) from courses;

• Returns the number of tuples in the course relation.
• min: minimum value
• sql select min(salary) from instructor where dept_name = 'Comp. Sci'
• Returns the minimum salary of all instructors in the Computer Science department.
• max: maximum value
• sql select max(salary) from instructor where dept_name = 'Comp. Sci'
• Returns the maximum salary of all instructors in the Computer Science department.
• sum: sum of values
• sql select sum(salary) from instructor where dept_name = 'Comp. Sci'
• Returns the Sum of the salary of all instructors in the Computer Science department.

Group By clause:

• Groups the attributes which have Identical values on some other attribute by passing them in some function.

select dept name, avg(salary) as avg_salary
from instructor
group by dept_name;

• Note: Attributes in select clause outside of aggregate functions must appear in group by list.

/* erroneous query */
select dept name, ID, avg(salary)
from instructor
group by dept name;

Having Clause:

• Used as to apply a predicate after the formation of the groups (predicate on the groups).

Find the names and average salaries of all departments whose average salary is greater than 42000.

select dept name, avg(salary)
from instructor
group by dept_name
having avg(salary) > 42000;

Null values

• All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes.

select sum(salary)
from instructor;

• Returns null if no non-null salary.
• Ignores the null values.

select count(salary)
from instructor;

• Returns 0 if all are null values in the salary column.
• Does not ignore the null values.

Nested Subqueries:

• select-from-where expression nested within another query. SYNTAX

select A1, A2, . . . , An
from r1,r2, . . . ,rm
where P

where * Ai : a subquery that generates a single value. * ri : any valid subquery. * P :an expression of the form: B \ (subquery) where B is an attribute and \ is

Subqueries in the Where Clause:

Set Membership

1. Find courses offered in Fall 2009 and in Spring 2010. (intersect example).

select distinct course_id
from section
where semester ='Fall' and year = 2009 and
course_id in (select course_id
              from section
              where semester ='Spring' and year = 2010)

• The subquery returns the course_id from section table with Spring semester and 2010 as the year.

• Here the in operator is used to check whether the course_id exists in the result of the subquery specified in the where clause.

• Find courses offered in Fall 2009 but not in Spring 2010. (except example)

select distinct course_id
from section
where semester ='Fall' and year = 2009 and
course_id not in (select course_id
                  from section
                  where semester ='Spring' and year = 2010)

• This query returns a result opposite to the above query due to the not in operator.
• Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 4233.

select count(distinct id)
from takes
where (course_id, sec_id, semester, takes.year) in
      (select course_id, sec_id, semester, teaches.year
       from teaches
       where teaches.id like '4233')

Set Comparison

• some clause refers to existential quantification.

Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.

select name
from instructor
where salary > some(select salary
                     from instructor
                     where dept_name = 'Biology')

• The above query checks if the salary of the instructor is greater than any result returned by the subquery returning the salary of the instructor in Biology department.
• all clause refers to universal quantification.

Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.

select name
from instructor
where salary > all(select salary
                  from instructor
                  where dept_name = 'Biology')

• The all clause checks if the salary of the instructor is greater than all the salaries returned by the subquery in the where clause.

Exists clause: testing empty relations.

• The exists construct returns the value true if the argument subquery is nonempty, else false.

Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester.

select course_id
from section as S
where semester = 'Fall' and year = 2009 and
      exists (select *
              from section as T
              where semester = 'Spring' and year = 2010
              and S.course_id = T.course_id)

• The exists clause checks if something is returned by the enclosed subquery. If yes, it is set to true, else false.
• Correlation name – variable S in the outer query.
• Correlated subquery – the inner query

Similarly the not exists clause works in an opposite way:

select distinct S.ID, S.name
from student as S
where not exists ( (select course_id
                    from course
                    where dept_name = 'Biology')
                    except
                    (select T.course_id
                            from takes as T
                            where S.ID = T.ID));

• The above contains 2 nested queries. The first nested query lists all courses offered in Biology.
• The second one lists all courses a particular student took.
• The entire query returns all students who have taken all courses offered in the Biology department.

Unique construct

• True if the result of a query contains no duplicates, else false.

Find all courses that were offered at most once in 2009.

---DOUBT--

Subqueries in the FROM clause

Find the average instructors' salaries of those departments where the average salary is greater than $42,000.

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name) as foo
where avg_salary > 42000

• It is necessary to give an alias to the relation returned by the subquery in the from clause to be able to work on it.
• Here the subquery returns the average salary of the instructors in each department and names that table as 'foo'.
• The outer query then extracts the department names and average salaries of the departments where the average salary is greater than 42000.

With clause:

• Defines a temporary relation whose definition is available only to the query in which the with clause occurs.

Find all departments with the maximum budget.

with max_budget(value) as
          (select max(budget)
          from department)
select department.dept_name
from department, max_budget
where department.budget=max_budget.value

• max_budget(value) is a temporary relation defined using with clause that returns the maximum budget from the departments table and is stored in max_budget.
• This temporary relation is used to extract the department name by doing a cross product of the relation max_budget and department where the budget of the department is equal to the value of the max_budget relation.

Subqueries in Select Clause

• Used for scalar subqueries : queries which return a single value.
• Gives Runtime error if a subquery in select clause returns more than a single value.

List all departments along with the number of instructors in each department

select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department

• The above nested query returns a single value as the count of instructors in a department and aliases it as num_instructors.
• The department name and the number of instructors is then finally displayed using the outer query.

Joins:

• Cartesian product which requires that tuples in the two relations match (under some condition).

Cross Join

• Returns the Cartesian product of rows from tables in the join.

Explicit

select *
from employee cross join department;

OR
Implicit

select *
from employee, department;

Inner Join

• Returns records having same value in a common column of the tables.

select *
from course inner join prereq on course.course_id = prereq.course_id

• The above query returns all the records from the table course and instructor where the values in the common column course_id match.
• Note that the column course_id is repeated as it is contained in both the relations.

Self Join

• Sometimes we require a join with the same table in order to retrieve the required result. Self join is used for that and the table names are changed in order to distinctly identify the same relations and apply conditions on them.
  SYNTAX:

select * 
from <table_name> <aliased_name1>
natural join
<table_name> <aliased_name2>

select *
from course c1 inner join course c2
on c1.course_id = c2.course_id

• The above query returns then entire course relation with duplicate columns.

Natural Join

• It is an inner join that eliminates the repeated column.
• It is an implicit join and does not require the specification of the key. It joins on the columns with same name in the tables implicitly.

select * from course natural join prereq

Theta Join

• Merges 2 tables on some condition represented by theta.
• Theta condition includes all comparison operators.

select *
from course inner join prereq on course.course_id>prereq.course_id

• Returns the join of the course and prereq table on the join condition course.course_id>prereq.course_id.

If the comparison operator is replaced by '=' operator, we get our basic join also called 'equijoin'

Outer Join

• Computes the join and then adds tuples from one relation that does not match tuples in the other relation to the result of the join.
• Avoids loss of information.
• Null values are used to fill up the attributes of rows in which the common attribute does not match.

It is of 3 types:

Left Outer Join

• Returns all records of the first table (left table) and the records that match in both the tables according to the join condition.

select * from
course natural left outer join prereq

• Returns all the rows in which the course_id in course and prereq match, as well as the rows from the left column course which do not match.

Right Outer Join

• Returns all records of the second table (right table) and the records that match in both the tables according to the join condition.

select * from
course natural right outer join prereq

• Returns all the rows in which the course_id in course and prereq match, as well as the rows from the right column prereq which do not match.

Full Outer Join

• Returns the records from both the tables that match according to the join condition as well as those which do not match.

select * from
course natural full outer join prereq

• Returns all the rows in which the course_id in course and prereq match, as well as the rows from both the columns which do not match.

NOTE: Those records which do not match according to the join condition have null values in those attributes not common to both the tables.