#Domain Types in SQL
- char(n): Fixed length character string, with user-specific length n.
- varchar(n): Variable length character strings, with user-specific maximum length n.
- int: Integer
- smallint: Small integer
- numeric(p, s): Fixed point number, with user-specific precision of p digits, with s digits to the right of decimal point.
- real, double precision: Floating point and double-precision floating point numbers
- float(n): Floating point number, with user-specific precision of at least n digits.
#Create table
create table alluser (
id char(5),
name varchar(10) not null
)
#Drop and alter
drop table alluser
alter table alluser add email varchar(20)
Dropping of attributes not supported by many databases
alter table alluser drop name
#Select
select [distinct | all]
from
where
The select clause can contain arithmetic expressions involving the operation $+ - \times \div$ and operating on constant or attributes of tuples
example:
select id, name, salary/12
from instructor
The where clause specifies conditions that result must satisfy correspond to the selection predicate of the relational algebra.
example:
select name
from instructor
where dept = 'phy' and salary > 80000
Comparison results can be combined using the logical connectives and, or and not. Comparison can be applied to results of arithmetics expressions.
The from clause lists the relation involved in the query, corresponding to the Cartesian product operation of the relational algebra.
#Join
For all instructors who have taught courses, find their names and the course ID of the courses they taught.
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID
#Natural join
Natural join matches tuples with the same values for all common attributes, and retains only one copy of each common column.
Danger in natural join: beware of unrelated attributes with same name which get equated incorrectly.
Examples:
List the names of instructors along with the the titles of courses that they teach
Incorrect version (equates course.dept_name with instructor.dept_name)
select name, title
from instructor natural join teaches natural join course;
Correct version
select name, title
from instructor natural join teaches, course
where teaches.course_id= course.course_id;
Another correct version
select name, title
from (instructor natural join teaches) join course using(course_id);
#Rename operation: as
The SQL allows renaming relations and attributes using the as clause:
old-name as new-name
Example:
select id, name, salary/12 as monthly-salary
from instructor
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.deptname = 'comp.sci'
Keyword as is optional and can be omitted
instructor as t = instructor t
#String operation
SQL includes a string-match operator for comparisons on character strings, The operator like uses pattern that are described using two special characters:
- percent (%) the % character matches any substring
- underscore (_) the _ character matches any character
Example:
Find the names of all instructors whose names includes the substring 'dar'
select name
from instructor
where name like '%dar%'
Escape: The default escape character is backslash ()
If you want to match '100%':
like '100\%' escape '\'
or you can use something else
SELECT
feedback_id,
comment
FROM
sales.feedbacks
WHERE
comment LIKE '%30!%%' ESCAPE '!'
in general:
column | expression NOT LIKE pattern [ESCAPE escape_character]
#Ordering the display of tuples
select distinct name
from instructor
order by name [desc | asc]
#Where clause predicates
SQL includes a between comparison operator
Example: Find the names of all instructors with salary between 90,000 and 100,000 (that is $\geq$ 90,000 and $\leq$ 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')
#Duplicates
In relations with duplicates, SQL can define how many copies of tuples appear in the result.
Multiset versions of some of the relational algebra operators - given multiset relation r1 and r2。
#Set operation
Examples:
- Find courses that ran in Fall 2009 or in Spring 2010
(select course_id from section where sem = ‘Fall’ and year = 2009)
union
(select course_id from section where sem = ‘Spring’ and year = 2010)
- Find courses that ran in Fall 2009 and in Spring 2010
(select course_id from section where sem = ‘Fall’ and year = 2009)
intersect
(select course_id from section where sem = ‘Spring’ and year = 2010)
- Find courses that ran in Fall 2009 but not in Spring 2010
(select course_id from section where sem = ‘Fall’ and year = 2009)
except
(select course_id from section where sem = ‘Spring’ and year = 2010)
Each of the above operations automatically eliminates duplicates
To retain all duplicates use the corresponding multiset versions union all, intersect all and except all.
#null values
It is possible for tuples to have a null value, denoted by null, for some of their attributes
null signifies an unknown value or that a value does not exist
The result of any arithmetic expression involving null is null: 5 + null is null
Example
select name
from instructor
where salary is null
#Usually should not do this
Output the names of all instructors who are in department with budget > 100000
select name
from instructor
where dept_name in (select dept_name
from department
where budget > 100000);
This is correct syntax, but a very complicated way to do a simple join
select name
from instructor join department
where budget > 100000;
#Set comparison
Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology’;
Same as using some
select name
from instructor
where salary > some (select salary
from instructor
where dept_name = 'Biology');
#Test for empty relations
The exists construct returns the value true if the argument subquery is nonempty
Yet another way of specifying the query “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);
#Not exists
Find all students who have taken all courses offered in the Biology department.
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));
#Test for absence of duplicate tuples
The unique construct tests whether a subquery has any duplicate tuples in its result
Find all courses that were offered at most once in 2009
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id= R.course_id
and R.year = 2009);
#Derived relations
SQL allows a subquery expression to be used 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)
where avg_salary > 42000;
Or,
select dept_name, avg_salary
from (select dept_name, avg (salary)
from instructor
group by dept_name) as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;
#With clause
The with clause provides a way of defining a temporary view 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 budget
from department, max_budget
where department.budget = max_budget.value;
#Modification
Delete all instructors
delete from instructor
Delete all instructors from the Finance department
delete from instructor
where dept_name= 'Finance';
Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building.
delete from instructor
where dept_name in (select dept_name
from department
where building = 'Watson');
#Insertion
insert into course
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
insert into course (course_id, title, dept_name, credits)
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
insert into student
values (’3003’, ’Green’, ’Finance’, null);
#Updates
Increase salaries of instructors whose salary is over $100,000 by 3%, and all others receive a 5% raise
update instructor
set salary = salary * 1.03
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;
The order is important,or using following
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end