Database) Chapter3. Introduction to SQL

Chapter 3: Introduction to SQL

SQL features

Data Definition Language - Data Manipulation Language

Data Definition Language (DDL) allows the specification of information about relations, including:

The schema for each realation

The domain of values associated with each attribute

Integrity constrraints

Domain type in SQL

char(n) - fixed n length character string

varchar(n) - max n length character string

int numeric(p,d) ->precision p, scale d --> 123.46 p=5 d=2

smallint real float(n)-> at least n digits

SQL relation is defined using the create table

create table r(A1 D1, A2 D2, ... , An Dn,

(integrity-constraint1),

...

(integrity-constraintk))

=> r is the name of the relation

=> each Ai is an attribute name in the schema of relation r

=> Di is the data type of values in the domain of attribute Ai

Integrity Constraints (IC) in Create Table

not null => It is not valid for null value

primary key(A1, A2, ..., An)

foreign key(A1, A2, ... , An) references r

Drop and Alter Table Constructs

drop table student -> Deletes the table and its contents

delete from students -> Delete all contents of table but retains table

alter table -> table의 attribute를 관리함

alter table r add A D => Domain D를 갖는 Attribute A를 relation r에 추가

alter table r drop A => Attribute A를 drop시킴

SQL as DML Data Manipulation Language

A typical SQL query has the form:

select A1, A2, ... , An

from r1, r2, ... , rm

where P

Ai represents an attribute, Ri represents a relation, P is a predicate

SQL query의 결과는 하나의 relation으로 출력 -> r1, r2, ... ,rn까지 cartesian product에서 A1, A2, .. , An만 뽑아 출력

Select Clause list the attributes desired in the result of a query

-> Corresponds to the projection operation of the relational algebra

distinct (remove duplicates) - all (duplicates not be removed)

(Ex) select distinct ID, select all ID

* (all attributes) select *

select clause can contain arithmetic expressions involving the operation, +, -, *, and /.

where clause specifies conditions that the result must satisfy

-logical connectives and, or and not

-between comparison operator: where salary between 90000 and 100000

from clause lists the relations involved in the query

corresponds to the Cartesian product operation of the relational algebra

-> Select * from insturctor, teaches => Cartesian Product: instructor X teaches

Joins : (Ex) 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

(ex) select * / from instructor natural join teaches

List the names of instructors along with the titles of courses that they teach

=> select name, title / from instructor natural join / where teaches.course_id = course.course_id;

or => select name, title / from (instructor natural join teaches) / join course using(course_id);

The Rename operation -> as

(ex) old-name as new_name

(Ex) select ID, name, salary/12 as monthly_salary

from instructor

(ex2) select distinct T.name

from instructor as T, instructor as S

where T.salary > S.salary and S.dept_name = ‘Comp. Sci’

=> Computer Scienct department의 someone의 salary보다 높은 사람의 instructor name 출력 -> Comp. Sci.의 최소 salary 의미함

String Operations -> % or _

percent (%) -> The % character matches any substring.

underscore (_) -> The _ character matches any character -> 글자수 제한

(Ex) Find the names of all instructores whose name includes the substring “dar”

-> select name / from instructor / where name like ‘%dar%’

=> 만약 %나 _가 들어가있는 string을 매칭시키고 싶다면 앞에 \ 붙임

ex => where name like ‘% 100 \% %’

Ordering the display of tuples

-> List in alphabetic order (auto asc)

select distinct name/ from instructor / order by name (desc)

desc 안붙이면 자동으로 asc.

Set operations

-Union, intersect and except

select course_id from section where sem = ‘Fall’ and year = 2009

union or intersect or except

select course_id from section where sem = ‘Spring’ and year = 2010

Null values -> 5 + null => null

​

With​ -> 임시의 View를 만들어줌 (잘 쓰면 개꿀)

  

Aggregate Functions

-avg -min -max -sum -count

group by => find the values applied aggregate function by selected group.

(ex) select dept_name, avg (salary) / from instructor/ group by dept_name;

Having Cluase -> group화가 된 다음에 predicate를 적용함 where같은 경우에는 group화를 적용하기 전

(ex) select dept_name, avg (salary) / from instructor / group by dept_name / habing avg (salary) > 42000;

Nested Subqueries – set membership (set operation을 다르게 풀이한 것, 의미는 set operation과 똑같음)

(ex) 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);

(ex) select count (distinct ID) from takes where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year) from teaches where teaches.ID = 10101);

(ex) select name from instructors where salary > some (select salary from instructor where dept_name = ‘Biology’);

Some Clause and All Clause

Some => if one element of the group satisfy the condition, it would return true

All => if all element of the group satisfy the condition, it would return true

(ex) select name from instructor where salary > all (select salary from instruct where dept_name = ‘Biology);

Unique construct -> tests whether a subquery has any duplicate tuples in ite result

(Ex) select T.course_id / from instructor as T / where unique (select S.course_id / from instructor as S/ where T.course_id = S.course.id and S.year = 2009);

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

Modification of the Database

delete: all values in attributes, but not attributes => delete from instructor/ where dept_name = ‘Finanace’;

delete from instructor where salary < (select avg (salary) from instructor);

insert into: insert tuple to the relation

insert into course values(‘CS-437’, ‘DB’, ‘Comp. Sci.’, 4);

select을 이용하여 insertion을 진행할 수도 있다.

insert into student / select ID, name, dept_name, 0 / from instructor

update : update the attribute

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

update instructor / set salary = case / when salary <= 100000 then salary*1.05/ else salary *1.03/ end

​

-------------------------------------------------------------------------------------------------------------------

DB 예제

a)
select title
from course
where dept_name = 'Comp.Sci' and credits = 3

b)
select distinct studet.ID
from (student join takes using(ID)) join (instructor join teaches using (ID))
      using(course_id,sec_id,semester,year)
where instructor.name = 'Einstein'

c)
select max(salary)
from instructor

d)
select *
from instructor as T
where T.salary = (select max(salary) from instructor)

​

e)
select course_id, sec_id, count(ID)
from takes natural join section
where semester = 'Autumn' and year = 2009
group by course_id, sec_id

​

f)
with sec_enrollment as (

select course_id, sec_id, T 
from (select course_id, sec_id, count(ID) as T
      from takes natural join section
      where semester = 'Autumn' and year = 2009
      group by course_id, sec_id)

)
select course_id, sec_id, T
from sec_enrollment
where T = ( select max(T) from sec_enrollment)

3.2

select ID, sum(credits * points)/sum(credits)
from (student natural join takes) join grade_points using (grade)
group by ID

​

select sum(credits * points) / sum(credits) as GPA

with avg_grade as (
 select ID, sum(credits * points) / sum(credits) as GPA

3.3

update instructor
set salary = salary * 1.1
where dept_name = 'Comp.Sci.'

​

delete from courses
where  course_id not in (select course_id from section)

​

insert into instructor
(select ID,name,dept_name,10000
from student
where tot_cred > 100)

​

select name
from customer 
where street = (select street from customer where name = 'Smith')
and city = (select city from customer where name = 'Smith')

select distinct branch_name
from account natural join depositor natural join customer
where customer_city = 'Harrison'

​

-----------------------------------------------------------------------------------------​

branch(branch_name, branch_city, assets)
customer(customer_name, customer_street, customer_city)
loan(loan_number, branch_name, amount)
borrower(cutomer_name, loan_number)
account(account_number, branch_name, balance)
depositor(customer_name, account_number)

------------------------------------------------------------------------

(select customer_name from depositor)
except (select customor_name from borrower)

​

select distinct name
from customer T join customer S using (customer_street, customer_city)
where S.customer_name = 'Smith'

​

select branch_name
from (customer natural join branch) join account using (branch_name)
where customer_city = 'Harrison'

​

-------------------------------------------------------------------------------------​

employee( employee_name, street, city)
works(employee_name, company_name, salary)
company(company_name, city)
manages(employee_name, manager_name)  

-------------------------------------------------------------------

select employee_name, city
from employee natural join works
where company_name = 'First Bank Corporation'

select name, street, city
from employee natural join works
where salary > 10000 and company_name = 'First Bank Corporation'

​

select *
from employee
except ( select *
         from employee
         where company_name = 'First Bank Corporation'

​

select *
from employee
where salary > all (select salary
                    from employee
                    where company_name = 'Small Bank Corporation'

​

with T as (select company_name, count(distinct) employee_name as employee) 
           from works
    group by company_name)

select company_name
from T
where T.employee = (select max(employee)
                    from T)

​

with avg_salary as (select company_name, avg(salary) as T
                    from works
                    group by company_name)

select company_name
from avg_salary
where T > (select T
           from avg_salary
           where company_name = 'First Bank Corporation');

​

​

update employee
set city = 'Newton'
where person_name = 'Jones'

​

update works
set salary = salary * 1.03
where employee_name in (select employee_name
                       from manages) and salary * 1.1 > 100000 and company_name = 'First Bank Corporation'
​

update works
set salary = salary * 1.1
where works.employee_name in (select manages.employee_name
                       from manages) and salary * 1.1 <= 100000 and company_name = 'First Bank Corporation'

3.11
a)
select distinct name
from (student join takes using (ID)) join course using (course_id)
where course.dept_name = 'Comp. Sci.'

​

b)
select ID, name
from student natural join takes
except
​

select ID, name
from student natural join takes
where year <= 2008

​

with T as (select ID, name, min(year) as min_year
           from student natural join takes
           group by ID, name)
select ID, name
from T
where min_year >= 2009

select dept_name, max(salary)
from instructor
group by dept_name