Introduction to SQL

Last modified: 2026-03-03 18:58:29 +0900 View History

Overview of the SQL Query Language

SQL Data Definition

Basic Types

Basic Schema Definition

create table r
    (A_1D_1, A_2D_2, ..., A_nD_n, 
        (integrity-constraint_1), 
        ...,
        (integrity-constraint_k));

create table instructor
    (ID char(5),
     name   varchar(20),
     dept_name  varchar(20),
     salary numeric(8, 2));

Integrity Constraints in Create Table

create table instructor (
    ID char(5),
    name   varchar(20) not null,
    dept_name  varchar(20),
    salary numeric(8, 2),
    primary key(ID),
    foreign key (dept_name) references department);

More Examples of Relation Definitions

create table department (
    dept_name   varchar(20),
    building    varchar(15),
    budget  numeric(12,2),
    primary key (dept_name));

create table course (
    course_id   varchar(7),
    title   varchar(50),
    dept_name   varchar(20),
    credits numeric(2, 0),
    primary key (course_id),
    foreign key (dept_name) references department);

create table teaches (
    ID  varchar(5),
    course_id   varchar(8),
    sec_id  varchar(8),
    semester    varchar(6),
    year    numeric(4, 0),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (course_id, sec_id, semester, year) references sections,
    foreign key (ID) references instructor);

Updates to tables

insert into instructor values ('10211', 'Smith', 'Biology', 66000);

delete from student

drop table r;

alter table r add A D;

alter table r drop A;

Basic Structure of SQL Queries

select A_1, A_2, ... , A_n
from r_1, r_2, ..., r_m
where P

The Select Clause

--find the names of all instructors
select name
from instructor;

--find the department names of all instructors, and remove duplicates
select distinct dept_name
from instructor;

--find dept_name of all instructors
select all dept_name
from instructor;

--find all attributes of all instructors
select *
from instructor;

--Result is a table with one column and a single row with value "437"
select '437';

--Can give the column a name
select '437' as FOO;

select 'A'
from instructor;

select ID, name, salary / 12
from instructor;

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

The Where Clause

--to find all instructors in Comp. Sci. dept
select name
from instructor
where dept_name = 'Comp. Sci.';

--to find all instructors in Comp. Sci. dept with salary > 70000
select name
from instructor
where dept_name = 'Comp. Sci' and salary > 70000

The From Clause

--find the Cartesian product instructor X teaches
select *
from instructor, teaches;

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

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

Queries on Multiple Relations

--retrieve the names of all instructors, along with their department names and department building name
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;

select A_1, A_2, ..., A_n
from r_1, r_2, ..., r_m
where P;

for each tuple t_1 in relation r_1
    for each tuple t_2 in relation r_2
        ...
        for each tuple t_m in relation r_m
            Concatenate t_2, t_2, ..., t_m into a single tuple t
            Add t into the result relation
Example
The Cartesian product of the instructor relation with the teaches relation

Additional Basic Operations

The Rename Operation

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.';

select name as instructor_name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

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

String Operations

--Find the names of all instructors whose name includes the substring “dar”.
select name
from instructor
where name like '%dar%';

--matches all strings beginning with "ab%cd"
like 'ab\%cd%' escape '\';

--matches all strings beginning with "ab\cd"
like 'ab\\cd%' escape '\';

Ordering the Display of Tuples

--To list in alphabetic order all instructors in the Physics department
select name
from instructor
where dept_name = 'Physics'
order by name;

/*
We wish to list the entire instructor relation in desc order of salary.
If several instructors have the same salary, we order them in asc order by name.
*/
select *
from instructor
order by salary desc, name asc;

Where Clause Predicates

select name
from instructor
where salary between 90000 and 100000;

-- same query
select name
from instructor
where salary <= 100000 and salary >= 90000;

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and dept_name = 'Biology';

--same query
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

Set Operations

The Union Operation

--find the set of all courses taught either in Fall 2017 or in Spring 2018, or both
(select course_id
 from section
 where semester = 'Fall' and year = 2017)
union
(select course_id
 from section
 where semester = 'Spring' and year = 2018);

The Intersect Operation

--find the set of all courses taught in both the Fall 2017 and Spring 2018
(select course_id
 from section
 where semester = 'Fall' and year = 2017)
intersect
(select course_id
 from section
 where semester = 'Spring' and year = 2018);

The Except Operation

--find the all courses taught in the Fall 2017 but not in the Spring 2018
(select course_id
 from section
 where semester = 'Fall' and year = 2017)
except
(select course_id
 from section
 where semester = 'Spring' and year = 2018);

Null Value

--Find all instructors whose salary is null
select name
from instructor
where salary is null;

select name
from instructor
where salary > 10000 is unknown;

Aggregate Functions

Basic Aggregation

--find the average salary of instructors in the Comp. Sci.
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';

--find the total number of instructors who teaches a course in the Spring 2018
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

--number of tuples in the course relation
select count(*)
from course;

Aggregation with Grouping

Example
--find the average salary in each department
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
Example
result
select avg (salary)
from instructor;

--find the number of instructors in each department who teach a course in the Spring 2018
select dept_name, count (distinct ID) as instr_count
from instructor, teaches
where instructor.ID = teaches.ID and
    semester = 'Spring' and year = 2018
group by dept_name;

--erroneous query: because of attribute "ID" in select clause
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

The Having Clause

--find departments where the average salary of the instructors is more than $42,000
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

--for each course section offered in 2017, find the average total credits of all students enrolled in the section, if the section has at leat 2 students
select course_id, semester, year, sec_id, avg (total_cred)
from student, takes
where student.ID = takes.ID and year = 2017
group by course_id, semester, year, sec_id
having count (ID) >= 2;

Aggreation with Null and Boolean Values

Nested Subqueries

select A_1, A_2, ..., A_n
from r_1, r_2, ..., r_m
where P

Set Membership

--find courses offered in Fall 2017 and in Spring 2018
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
    course_id in (select course_id
                  from section
                  where semester = 'Spring' and year = 2018);

--find courses offered in Fall 2017 but not in Spring 2018
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
    course_id not in (select course_id
                      from section
                      where semester = 'Spring' and year = 2018);

--select the names of instructors whose names are neither Mozart nor Einstein
select distinct name
from instructor
where name not in ('Mozart', 'Einstein');

--Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101
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');

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';

--Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
select distinct name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name = 'Biology');

--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')

--find the departments that have the highest average salary
select dept_name
from instructor
group by dept_name
having avg (salary) >= all (select avg (salary)
                            from instructor
                            group by dept_name);

Test for Empty Relations

--Find all courses taught in both the Fall 2017 semester and in the Spring 2018 semester
select course_id
from section as S
where semester = 'Fall' and year = 2017 and
    exists (select *
            from section as T
            where semester = 'Spring' and year = 2018 and
                S.course_id = T.course_id);

--Find all students who have taken all courses offered in the Biology department
select 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));

Note: Cannot write this query using = all and its variants.

--find the total number of (distinct) students who have taken course sections taught by the instructor with ID 110011
select count (distinct ID)
from takes
where exists (select course_id, sec_id, semester, year
              from teaches
              where teaches.Id = '110011'
                and takes.course_id = teaches.course_id
                and takes.sec_id = teaches.sec_id
                and takes.semester = teaches.semester
                and takes.year- = teaches.year);

Test for the Absence of Duplicate Tuples

--Find all courses that were offered at most once in 2017
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 = 2017);

--Find all courses that were offered at most once in 2017
select T.course_id
from course as T
where 1 >= (select count(R.course_id)
            from section as R
            where T.course_id = R.course_id and
                  R.year = 2017);

select T.course_id
from course as T
where not unique (select R.course_id
                  from section as R
                  where T.course_id = R.course_id and
                        R.year = 2017);

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)
where avg_salary > 42000;

--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)
      from instructor
      group by dept_name)
      as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;

--find the maximum across all departments of the total of all instructors' salaries in each department
select max (tot_salary)
from (select dept_name, sum(salary)
      from instructor
      group by dept_name) as dept_total (dept_name, tot_salary);

--print the names of each instructor, along with their salary and the average salary in their department
select name, salary, avg_salary
from instructor I1, lateral (select avg (salary) as avg_salary
                             from instructor I2
                             where I2.dept_name = I1.dept_name);

The With Clause

--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;

--find all departments where the total salary is greater thatn the average of the total salary at all department
with dept_total (dept_name, value) as 
    (select dept_name, sum (salary)
    from instructor
    group by dept_name),
dept_total_avg (value) as
    (select avg (value)
    from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;

Scalar Subqueries

--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;

Scalar Without a From Clause

(select count (*) from teaches) / (select count (*) from instructor);

select (select count (*) from teaches) / (select count (*) from instructor)
from dual;

Modification of the Database

Deletion

delete from r
where P;

--delete all tuples from the instructor
delete from instructor;

--delete all tuples in the instructor relation pertaining to instructors in the Finance department
delete from instructor
where dept_name = 'Finance';

--delete all instructors with a salary between $13,000 and $15,000
delete from instructor
where salary between 13000 and 15000;

--delete all tuples in the instructor relation for those instructors associated with a department located in the Waston building
delete from instructor
where dept_name in (select dept_name
                    from department
                    where building = 'Waston');

--Delete all instructors whose salary is less than the average salary of instructors
delete from instructor
where salary < (select avg (salary)
                from instructor);

Insertion

--Add a new tuple to course
insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
--equivalently
insert into course (course_id, title, dept_name, credits) values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
--also equivalently
insert into course (title, course_id, credits, dept_name) values ('Database Systems', 'CS-437', 4,'Comp. Sci.');

--Add a new tuple to student  with tot_creds set to null
insert into student values ('3003', 'Green', 'Finance', null);

--Make each student in the Music department who has earned more than 144 credit hours an instructor in the Music department with a salary of $18,000.
insert into instructor
       select ID, name, dept_name, 18000
       from student
       where dept_name = 'Music' and tot_cred > 144;

--This request might insert an infinite number of tuples, if the primary key constraint on student were absent.
insert into student
       select *
       from student;

Updates

--Give  a  5% salary raise to all instructors
update instructor
set salary = salary * 1.05;

--Give  a 5% salary raise to those instructors who earn less than 70000
update instructor
set salary = salary * 1.05;
where salary < 70000;

--Give  a 5% salary raise to instructors whose salary is less than average
update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
                from instructor);

--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;

--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;

--Recompute and update tot_creds value for all students
update student
set tot_cred = (
    select sum (credits)
    from takes, course
    where student.ID = takes.ID and
            takes.course_id = course.course_id and
            takes.grade <> 'F' and
            takes.grade is not null);

select case 
        when sum (credits) is not null then sum (credits)
        else 0
        end;