#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 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 [distinct | all] from where
The select clause can contain arithmetic expressions involving the operation $+ - \times \div$ and operating on constant or attributes of tuples
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.
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.
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 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.
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;
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
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
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
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 '!'
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
select name, course_id from instructor, teaches where (instructor.ID, dept_name) = (teaches.ID, 'Biology')
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。
- 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.
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
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;
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);
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);
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;
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;
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;
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');
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);
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