What Is SQL?

SQL (Structured Query Language)

Can be used to Create, Drop, and Alter Tables (schema):

• We will study that next week

Can be also be used to:

• Retrieve data from tables – get slices of Data

• Insert new data into tables

• Update (modify) existing data in tables

• We will study how to do that this lecture

Note: We must have the correct permissions to do these actions on the database. We will discuss in later class sessions

SQL as Data Definition Language

• Can be used to create relations as well as additional information about relations, such as:

• The schema of the relation

• The data types of the values to be stored in the relation

• The integrity constraints on the relation

• The set of indices on the relation

• Information about access privileges and security

• The physical storage structure of the relation on the disk

SQL :: Creating a Table

SQL :: Inserting Records

SQL :: Querying & Retrieving Records

SQL :: Keywords & Symbols

• To force elimination of duplicates, insert distinct after select

select distinct instructor_name from course

• An asterisk (*) in the select clause denotes all attributes:

select * from instructor where salary > 50000

• String-matching operator like for comparisons using patterns % character

select * from course where instructor_name like '%Mickey%'

• List records in alphabetic order (ascending or descending)

select * from instructor order by name

select * from instructor order by name desc

select * from instructor order by name asc

SQL :: Aggregations

SQL supports the following aggregation functions:

• avg: average value

• min: minimum value

• max: maximum value

• sum: sum of values

• count:number of value

Example:

select max(salary) from instructor

select sum(salary) from instructor

select count(*) from instructor where salary >50000	

SQL Keywords - Aggregate Functions and Group By

• Find the average salary of instructors in each department

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

Output:

Restriction on Aggregation with Group By

• Attributes in select clause other than aggregate functions must appear in group by list

/* erroneous query */
selectdept_name, ID, avg (salary)
from instructor
group by dept_name;

Note: A very common mistake. But note that having ID in the SELECT clause in this query does not make sense since ID is not an attribute of departments (unlike average salary).

Restriction on Aggregation with Group By and with Having Clause

• Find the names and average salaries of all departments whose average salary is greater than 50,000

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

Note: predicates in the having clause are filters on groups whereas predicates in the where clause are filters on the tuples that go into groups.