acytoo

Database Introduction to Sql

#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

See Also: