Introduction to SQL

Overview of SQL Query Language

SQL (Structured Query Language) is the most influential commercially marketed relational query language.
Other query languages include XQuery.
SQL functionalities:
- Defining the structure of data.
- Modifying data.
- Specifying security constraints.

A Brief History of SQL

Early 1970s:
- IBM San Jose Research Lab developed the IBM Sequel language as part of the System R project.
- The language was later renamed Structured Query Language (SQL).
Standardization:
- SQL was standardized by ANSI and ISO.
- Standardization is essential for ensuring portability and compatibility across different database systems.
- SQL standards evolved over time: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008.

Parts of SQL Language

Data Definition Language (DDL):
- Used to define relation schemas.
- Modify relation schemas.
- Delete relations.
Data Manipulation Language (DML):
- Used for querying data.
- Inserting, deleting, and updating tuples.
- Defining views.
Integrity Constraints:
- Enforce primary-key constraints.
- Enforce foreign-key constraints.
Transaction Control:
- Specifies the beginning and ending of transactions to ensure data consistency.
Embedded SQL and Dynamic SQL:
- Describes how SQL can be embedded within programming languages like C, C++, Java, etc.
Authorization:
- Grants rights to relations and views to control access.

How to create Database using SQL

Usage of Data Definition Language (DDL)

DDL is used to create a database, which involves:
- Specifying the set of relations.
- Defining the schema for each relation.
  - Including the data type of attributes.
- Defining integrity constraints to maintain data quality.
- Setting up indices for efficient data retrieval.
- Configuring security and authorization settings.
- Determining the physical storage structure on disk.

Data Types of Attributes in SQL

char(n) or character(n):
- Fixed-length string with a user-specified length n.
varchar(n) or character varying(n):
- Variable-length string with a user-specified maximum length n.
Example:
- name is an attribute of type char(10).
- vname is an attribute of type varchar(10).
Storage Comparison:
- If we store “SAM” in name, it will store 10 characters, padding the string with 7 spaces.
- If we store “SAM” in vname, it will store only 3 characters.
Comparison Issue:
- Comparing name == vname may return unexpected results due to the padded spaces in char.
- Recommendation: Use varchar to avoid such issues.

Other Data Types

int or integer
smallint
real
double
float
- float (n)
numeric(p, d):
- Fixed-point number with user-specified precision.
  - p = maximum total number of digits that can be stored.
  - d = maximum number of digits stored to the right of the decimal point.
MySQL Data Types: For a comprehensive list of data types in MySQL, refer to the official documentation.

Creating a University Database in SQL

Editor:
- MySQL Workbench is a popular tool for database development.
- It operates on a client-server model.
Sample Online Editors:
- Online editors like paiza.io and extendsclass.com provide convenient environments for executing SQL queries.

SQL Commands for Database Creation

create database command to create a database:
```
create database university;
```
use command to select a database:
```
use university;
```

create table command to create a table:

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

General Form of `create table` Command

r: relation name.
Ai: attribute name.
Di: Domain of Ai.
Integrity Constraints (Optional):
- Primary key.
- Foreign key.
- Not null.

Examples of `create table` with Integrity Constraints

Creating the department table:

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

Creating the course table:

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

Creating the instructor table:

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

Create Instructor Table (SQL Standard)

Example 1:

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

Example 2:

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

Create Instructor Table (MySQL)

Example 1:

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

Example 2:

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

Create Takes Table (SQL Standard)

create table takes (
    ID varchar(5),
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4,0),
    grade varchar(2),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (course_id, sec_id, semester, year) references section,
    foreign key (ID) references student
);

Since the primary key consists of more than one attribute (here 4 attributes), we can’t specify primary key here. Rather, it should be at the end.

Takes Table (MySQL)

create table takes (
    ID varchar(5),
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4,0),
    grade varchar(2),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year),
    foreign key (ID) references student (ID)
);

Since the primary key consists of more than one attribute (here 4 attributes), we can’t specify primary key here. Rather, it should be at the end.

Inserting Data into a Table

select * command to view table content:
```
select * from department;
```

insert command to insert data into a table:

insert into department values('Comp. Sci.', 'Taylor', 100000);
insert into department values ('Biology','Watson',90000);

Trying to insert data with potential issues:

insert into department values ('Biology','Watson',95000); -- May cause an error if primary key is violated
insert into department values (null,'Watson',90000);      -- May cause an error if primary key is violated

Inserting data:

insert into department values('Elec. Eng.', 'Taylor', 85000);
insert into department values('Music', 'Packard', 80000);
insert into department values('Finance', 'Painter', 120000);
insert into department values('History', 'Painter', 50000);
insert into department values('Physics', 'Watson', 70000);

Example: Instructor Table

insert into instructor values('10101', 'Srinivasan', 'Comp. Sci.', 65000);
insert into instructor values('12121', 'Wu', 'Finance', 90000);
insert into instructor values('15151', 'Mozart', 'Music', 40000);
insert into instructor values('22222', 'Einstein', 'Physics', 95000);
insert into instructor values('32343', 'El Said', 'History', 60000);
insert into instructor values('33456', 'Gold', 'Physics', 87000);
insert into instructor values('45565', 'Katz', 'Comp. Sci.', 75000);
insert into instructor values('58583', 'Califieri', 'History', 62000);
insert into instructor values('76543', 'Singh', 'Finance', 80000);
insert into instructor values('76766', 'Crick', 'Biology', 72000);
insert into instructor values('83821', 'Brandt', 'Comp. Sci.', 92000);
insert into instructor values('98345', 'Kim', 'Elec. Eng.', 80000);

Alter Table

Adding an attribute to an existing relation:
```
alter table r add A D;
```
Dropping an attribute from a relation:
```
alter table r drop A;
```
- r: relation name
- A: attribute name to be added to/deleted from r
- D: Domain of A

Examples with Alter Table

Before adding

ID     name       dept_name   salary
10101  Srinivasan Comp. Sci.  100000.00
12121  Wu         Finance     120000.00

Adding room_no attribute:

alter table instructor add room_no varchar(4);

After adding

ID     name       dept_name   salary      room_no
10101  Srinivasan Comp. Sci.  100000.00  null
12121  Wu         Finance     120000.00  null

Dropping room_no attribute:
```
alter table instructor drop room_no;
```

After dropping

ID     name       dept_name   salary
10101  Srinivasan Comp. Sci.  100000.00
12121  Wu         Finance     120000.00

How to write queries using SQL

Basic Structure of SQL Queries

Three main clauses:
- select
- from
- where
SQL is based on relational algebra.
How SQL queries work:
- Takes relations as input specified in the from clause.
- Operates on relations based on conditions in the where clause.
- Selects attributes specified in the select clause.
- Produces a relation as the result.
Correspondence to Relational Algebra:
- where corresponds to selection (σ).
- select corresponds to projection (Π).
- from corresponds to Cartesian product (×) or natural join (⋈).

Query on a Single Relation

Query: “Find the names of all instructors”
```
select name from instructor;
```
Relational Algebra Equivalent:
```
Π name (instructor)
```

Another Example

Query: “Find the department names of all instructors”
```
select dept_name from instructor;
```
Relational Algebra Equivalent:
```
Π dept_ame (instructor)
```

Duplicate Tuples

Relations are sets, so duplicates are not allowed.
However, SQL allows duplicates in relations and results of SQL expressions because duplicate elimination can be time-consuming.

Removing duplicates in SQL:

select distinct dept_name from instructor;

Specifying not to remove duplicates:
```
select all dept_name from instructor;
```

Select *

select * denotes all attributes of a relation.
```
select * from instructor;
```

More on `select` Clause

May contain arithmetic expressions.

select ID, name, salary*1.1 from instructor;

This does not change the original relation but presents the result with calculated values.

Where Clause

Specifies conditions that the result must satisfy.
Example: “Find names of all instructors in the Comp. Sci. dept who have a salary greater than $80,000”
```
select name from instructor where dept_name = ‘Comp. Sci.’ and salary > 80000;
```

Relational Algebra Equivalent:

Π name (σ dept_name=‘Comp. Sci.’ ^ salary > 80000 (instructor))

Where Clause Allows

Logical operators: AND, OR, NOT
Comparison operators: <, <=, >, >=, =, <>
Can compare strings, arithmetic expressions, dates.

From Clause

Lists relations involved in the query.
Corresponds to the Cartesian product in relational algebra.
Example: Find the Cartesian product of instructor and teaches:
```
select * from instructor, teaches;
```

Queries on Multiple Relations

Query: “Find names of all instructors, along with their department names and department building name”

The required information cannot be obtained from the instructor relation alone.

select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;

General Case of SQL Queries

select A1, A2, ..., An
from r1, r2, ..., rm
where P

Ai: Attribute
ri: relation
P: A predicate

Basic operations done by SQL

Joins

“For all instructors who have taught some course, find their names and the course ID of the courses they taught”
```
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;
```

Write SQL Query

“For all instructors of Comp. Sci. who have taught some course, find their names and the course ID of the courses they taught”
```
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and dept name = ’Comp. Sci.’;
```

From Book (Exercise # 3.21)

Find the names of members who have borrowed any book published by “McGraw-Hill”.

select name
from member, book, borrowed
where member.memb_no = borrowed.memb_no
  and borrowed.isbn = book.isbn
  and publisher = ’McGraw-Hill’;

Natural Join

Operates on two relations where values are the same on all attributes common to both relations.

Example:

select name, course_id
from instructor
natural join teaches;

Rename Operation

SQL allows renaming relations and attributes using the as clause.
Syntax: old-name as new-name

Example:

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

Rename Operation: Example Query

“Find the names of all instructors whose salary is greater than at least one instructor in the Finance department”
```
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Finance';
```

String Operations

like is the string-matching operator with two special characters:
- %: Matches any substring.
- _: Matches any character.
Example: Find department names where the building name includes the substring ‘Wat’.
```
select dept_name from department where building like ’%Wat%’;
```

String Operations Examples

'Intro%' matches any string beginning with “Intro”.
'%Comp%' matches any string containing “Comp” as a substring.
'______' matches any string of exactly six characters.
'______%' matches any string of at least six characters.

Other String Operations

Convert upper to lower case (Lower) and vice versa (Upper).
Find string length (Length).
Extract substrings (Substring).

Set Operations: Union

Find courses taught in Fall 2009 or in Spring 2010

(select course_id from section where semester = ‘Fall’ and year = 2009)
union
(select course_id from section where semester = ‘Spring’ and year = 2010);

Alternative:

select course_id
from section
where (semester = ‘Fall’ and year = 2009) or (semester = ‘Spring’ and year = 2010);

Set Operations: Intersection

Find courses taught in Fall 2009 and in Spring 2010

(select course_id from section where semester = ‘Fall’ and year = 2009)
intersect
(select course_id from section where semester = ‘Spring’ and year = 2010);

Set Operations: Except

Find courses taught in Fall 2009 but not in Spring 2010

(select course_id from section where semester = ‘Fall’ and year = 2009)
except
(select course_id from section where semester = ‘Spring’ and year = 2010);

Set Operations

union, intersect, except automatically eliminate duplicates.
To retain duplicates, use union all, intersect all, except all.

Ordering the Display of Tuples

desc for descending order.

asc for ascending order (default).

select distinct name from instructor order by name desc;
select distinct name from instructor order by name;
select distinct name from instructor order by name asc;

Sorting on multiple attributes:

order by dept_name, name
order by dept_name desc, name asc

Some other important features supported by SQL

Null Values

Represents an unknown value or a value that does not exist.

Use is null or function ISNULL to check for null values.

select name, salary from instructor where salary is null;
select name, salary from instructor where ISNULL (salary);

True or False

Comparison with null requires a third logical value: unknown.

Aggregate Functions

Input: A collection of values
Output: A single value
Built-in SQL aggregate functions:
- Average: avg
- Minimum: min
- Maximum: max
- Total: sum
- Count: count

Aggregate Functions: Examples

Find the number of rows in the course relation

select count(*) from course;
select count(*) as total_rows from course;

Find the average salary of instructors in the Computer Science department
```
select avg(salary) from instructor where dept_name = ’Comp. Sci.’;
```
Find the total number of instructors, no duplicates, who teach a course in the Spring 2010 semester
```
select count(distinct ID) from teaches where semester = ’Spring’ and year = 2010;
```

Group By

Used to group rows that have the same values in one or more columns into a summary row
```
select dept_name, avg(salary) AS avg_salary
from instructor
group by dept_name;
```

Group by with Having Clause

The having clause is used to filter the results of a group by query.
For each course section offered in 2009, find course information (courseid, secid, semester, year) and the average total credits (tot_cred) of all students enrolled in the section, if the section had at least 2 students.
```
select course_id, sec_id, semester, year, avg (tot_cred)
from takes natural join student
where year = 2009
group by course_id, sec_id, semester, year
having count (ID) >= 2;
```

Sequence of Operations

The sequence of operations in an SQL query is as follows:
1. from
2. where
3. group by
4. having
5. select

Null Values and Aggregates

Aggregate functions ignore null values.
count(*) counts all rows, including those with null values.
count(attribute) counts only rows where the attribute is not null.

Nested Subqueries

A query within another query.
Used for testing:
1. Set membership
2. Set comparisons
3. Set existence

Nested Subquery: Set Membership

Find courses that taught in Fall 2009 and in Spring 2010

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

Nested Subquery: Set Comparison

Find the names of all instructors whose salary is greater than at least one instructor in the Finance department
```
select name
from instructor
where salary >  (select min(salary) from instructor where dept_name='Finance');
```

Nested Subquery: Set Existence

“Find all courses taught in both Fall 2009 and in Spring 2010”

select distinct course_id
from section as S
where semester = ’Fall’ and year= 2009
  and exists (
    select course_id
    from section as T
    where semester = ’Spring’
      and year= 2010
      and S.course_id = T.course_id
);

Not Exists

Find all students (ID, name) who have taken all courses offered in the Biology department.

select ID, name
from student
where not exists (
    (select course_id from course where dept_name = ’Biology’)
    except
    (select course_id from takes where student.ID = takes.ID)
);

How to modify Database using SQL

Database Modification

Deletion of tuples
Insertion of new tuples
Updating values in some tuples

Delete and Drop

delete from instructor;
- Deletes table contents, but retains table
drop table instructor;
- Deletes both table and its contents

Deletion

Delete all instructors
```
delete from instructor;
```

Delete all instructors in the Finance department

delete from instructor where dept_name= ’Finance’;

Delete all courses that have never been offered

delete from course
where course_id not in (select course_id from section);

Insertion

Insert a new course

insert into course
values (’CS-330’, ’Database Systems’, ’Comp. Sci.’, 4);

insert into course (course_id, title, dept_name, credits)
values (’CS-330’, ’Database Systems’, ’Comp. Sci.’, 4);

Updates

Change a value in a tuple without changing all values in the tuple.
Give a 15 percent salary raise to all instructors
```
update instructor
set salary= salary * 1.15;
```

Give a 15 percent salary raise to instructors whose salary is less than average

update instructor
set salary = salary * 1.15
where salary < (select avg (salary) from instructor);

Give a 15 percent salary raise to all instructors who are in Watson building.

update instructor
set salary= salary * 1.15
where dept_name in (
    select dept_name from department where building = 'Watson’
);

Case Statement

General form

case
    when condition 1 then result 1
    when condition 2 then result 2
    ..............
    when condition N then result N
    else result (optional)
end

Example:

update instructor
set salary =  (
    case
        when salary <= 100000 then salary * 1.05
        else salary * 1.03
    end
);

Introduction to SQL

Overview of SQL Query Language

A Brief History of SQL

Parts of SQL Language

How to create Database using SQL

Usage of Data Definition Language (DDL)

Data Types of Attributes in SQL

Other Data Types

Creating a University Database in SQL

SQL Commands for Database Creation

General Form of create table Command

Examples of create table with Integrity Constraints

Create Instructor Table (SQL Standard)

Create Instructor Table (MySQL)

Create Takes Table (SQL Standard)

Takes Table (MySQL)

Inserting Data into a Table

Example: Instructor Table

Alter Table

Examples with Alter Table

How to write queries using SQL

Basic Structure of SQL Queries

Query on a Single Relation

Another Example

Duplicate Tuples

Select *

More on select Clause

Where Clause

Where Clause Allows

From Clause

Queries on Multiple Relations

General Case of SQL Queries

Basic operations done by SQL

Joins

Write SQL Query

From Book (Exercise # 3.21)

Natural Join

Rename Operation

Rename Operation: Example Query

String Operations

String Operations Examples

Other String Operations

Set Operations: Union

Set Operations: Intersection

Set Operations: Except

Set Operations

Ordering the Display of Tuples

Some other important features supported by SQL

Null Values

True or False

Aggregate Functions

Aggregate Functions: Examples

Group By

Group by with Having Clause

Sequence of Operations

Null Values and Aggregates

Nested Subqueries

Nested Subquery: Set Membership

Nested Subquery: Set Comparison

Nested Subquery: Set Existence

Not Exists

How to modify Database using SQL

Database Modification

Delete and Drop

Deletion

Insertion

Updates

Case Statement

General Form of `create table` Command

Examples of `create table` with Integrity Constraints

More on `select` Clause