Introduction to SQL

Introduction to SQL

• SQL is a query language used to interact with databases.
• SQL can be used for data definition, basic querying, and modification of databases.
• SQL also supports set operations, null values, aggregate functions, and nested subqueries.

History of SQL

• IBM developed Sequel as part of the System R project at the IBM San Jose Research Laboratory
• Sequel was eventually renamed to Structured Query Language (SQL)
• SQL-86 was the first ANSI and ISO standard SQL
• Other versions: SQL-89, SQL-92, SQL:1999 (Y2K compliant), SQL:2003, and SQL:2008.
• Commercial systems generally offer SQL-92 features, with varying feature sets from later standards and proprietary additions
• https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html is a MySQL installation link

DDL (Data Definition Language)

• SQL's DDL allows specification of information about relations, including schema, attribute domain values, integrity constraints, and indices.

Domain Types in SQL

• char(n): Fixed-length character string where n specifies the length.
• varchar(n): Variable-length character string where n is the maximum length.
• int: Integer (machine-dependent).
• smallint: Small integer (machine-dependent subset of the integer domain).
• numeric(p,d): Fixed-point number with user-specified precision of p digits, where d digits are to the right of the decimal point
• real, double precision: Floating point numbers with machine-dependent precision.
• float(n): Floating point number with user-specified precision of at least n digits.

Create Table Command

• The create table command defines an SQL relation.
• create table r (A1 D1, A2 D2,..., An Dn, (integrity-constraint1),...,(integrity-constraintk))
• r is the name of the relation.
• Ai is an attribute name in the schema of relation r.
• Di is the data type of values in the domain of attribute Ai.
• create table instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2)) is an example of a table creation

Integrity Constraints in Create Table

• not null: Ensures that an attribute cannot have a null value.
• primary key (A1,..., An): Specifies the primary key of the table.
• foreign key (Am,..., An) references r: Specifies a foreign key referencing another relation r.
• 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) is an example of using integrity constraints
• A primary key declaration automatically ensures not null.

More Relation Definitions

• create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID), foreign key (dept_name) references department(dept_name)) creates a student table

Additional Relation Definitions

• 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 (ID) references student(ID), foreign key (course_id, sec_id, semester, year) references section((course_id, sec_id, semester, year))): defines a takes table
• sec_id can be dropped from the primary key to prevent a student from registering for two sections of the same course in the same semester.
• create table course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department(dept_name)): creates a course table.

Updates to Tables

• insert into instructor values ('10211', 'Smith', 'Biology', 66000): Inserts a new tuple into the instructor table.
• delete from student: Removes all tuples from the student relation.
• drop table r: Deletes the table r.
• alter table r add A D: Adds an attribute named A with domain D to relation r; existing tuples are assigned null for the new attribute.
• alter table r drop A: Removes the attribute named A from relation r (not supported by all databases).

Basic Query Structure

• select A1, A2,..., An from r1, r2,..., rm where P is a typical SQL query form.
• Ai represents an attribute.
• Ri represents a relation.
• P is a predicate
• The result of an SQL query is itself a relation.

The Select Clause

• The select clause lists desired attributes in the query result.
• Example: select name from instructor finds the names of all instructors.
• SQL names are case-insensitive.
• SQL allows duplicates in relations and query results.
• distinct keyword eliminates duplicates like in: select distinct dept_name from instructor.
• The all keyword specifies duplicates should not be removed like in: select all dept_name from instructor.
• select * from instructor denotes all attributes.
• Arithmetic expressions using +, -, *, / are allowed
• select ID, name, salary/12 from instructor would return a relation with the salary attribute divided by 12.
• select ID, name, salary/12 as monthly_salary uses as to rename an attribute
• The where clause specifies conditions for the result.
• select name from instructor where dept_name = 'Comp. Sci.' to find all instructors in Comp. Sci.
• Comparison results are combined using logical connectives and, or, and not.
• select name from instructor where dept_name = 'Comp. Sci.' and salary > 80000 finds instructors in 'Comp. Sci.' with salary > 80000.

Cartesian Product

• select * from instructor, teaches: Generates every possible instructor-teaches pair with all attributes from both relations.

Examples with Joins

• select i.name, t.course_id from instructor i, teaches t where instructor.ID = teaches.ID: finds names of all instructors who have taught a course and the course IDs.

More Query Examples

• select i.name, t.course_id from instructor i, teaches t where i.ID = t.ID and i.dept_name = 'Art': Finds the names of all instructors in the Art department who have taught some course and the course ID.

The Rename Operation

• Allows renaming relations and attributes using the as clause with the format old-name as new-name

Rename Operation Examples

• select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Comp. Sci.': finds names of instructors with a higher salary than someone in 'Comp. Sci'

Natural Join

• Considers pairs of tuples with the same value on attributes that appear in both relations' schemas.
• select name, course_id from instructor natural join teaches: Returns instructor names and course IDs from a natural join of instructor and teaches, which is equivalent to instructor.ID = teaches.ID.
• select name, title from instructor natural join teaches, course where teaches.course_id = course.course_id: finds instructor names and course titles using natural join.
• select name, title from (instructor natural join teaches) join course using (course_id): specifies the course_id attribute

String Operations

• SQL includes a string-matching operator, like, which uses % to match any substring and _ to match any character.
• select name from instructor where name like '%dar%': finds the names of instructors whose names include the substring "dar".
• like '100 \\%' matches the string "100%" using backslash \ as the escape character.
• SQL supports concatenation (using "||"), converting case, finding string length, and extracting substrings.
• Patterns used in like are case-sensitive.
• 'Intro%': Matches any string beginning with "Intro"
• '%Comp%': Matches any string containing "Comp" in it
• '___': Matches any string of exactly three characters
• '___%': Matches any string of at least three characters

Ordering the Display of Tuples

• select distinct name from instructor order by name: Lists names of all instructors in alphabetical order.
• desc specifies descending order, and asc for ascending, with ascending order being the default.
• sort on multiple attributes such as order by dept_name, name

Where Clause Predicates

• SQL includes between comparison
• select name from instructor where salary between 90000 and 100000: find names that have a salary between 90000 and 10000

Set Operations

• Set operations includes union, intersect, and except and works with queries
• union automatically eliminates duplicates; use union all operator to include them.
• (select course_id from section where semester = 'Fall' and year = 2009) union (select course_id from section where semester = 'Spring' and year = 2010) is a SQL query for returning the union using union

Null Values

• Tuples can have a null value to represent missing information
• null signifies an unknown or non-existent value.
• Arithmetic expressions involving null evaluate to null, such as 5 + null returning null.
• The predicate is null checks for null values in select queries, e.g. select name from instructor where salary is null.
• Comparisons with null return unknown results
• Used in a three-valued logic with values including true, false, and unknown
• Returns based on the truthiness of the values, example
• (unknown or true) = true
• (unknown or false) = unknown
• (unknown or unknown) = unknown
• where clause predicate is treated as false if it evaluates to unknown

Aggregate Functions

• These functions operate on a value within a column of a relation, and return a single value
• avg: average value
• min: minimum value
• max: maximum value
• sum: sum of the values
• count: number of values

Aggregate Functions Examples

• select avg (salary) from instructor where dept_name= 'Comp. Sci.': computes the average salary of instructors in the Computer Science department.
• select count (distinct ID) from teaches where semester = 'Spring' and year = 2010: finds the number of instructors who teach a course in the Spring 2010 semester.
• select dept_name, avg (salary) as avg_salary from instructor group by dept_name: calculates average salary of instructors in each department.

Aggregate Functions & Grouping

• Attributes in the select clause outside of aggregate functions must appear in the group by list.
• select dept_name, avg (salary) from instructor group by dept_name: correct query for group by usage
• predicates in the having clause are applied after forming groups whereas predicates in the where clause are applied before forming groups.
• select dept_name, avg (salary) from instructor group by dept_name having avg (salary) > 42000: Find the names and average salaries of all departments whose average salary is greater than 42000

Null Values and Aggregates

• select sum (salary ) from instructor ignores null salary amounts.
• All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes.
• If a collection has only null values, count returns 0 and all other aggregates return null

Nested Subqueries

• Allows nesting of subqueries

• Nested subqueries follow the basic structure: select A1, A2,..., An from r1, r2,..., rm where P

• Ai can be replaced by a subquery that generates a single value.

• ri can be replaced by any valid subquery

• P can be replaced with B (subquery) where B is an attribute.

Subqueries in the Where Clause

• Used for set membership, set comparisons, set cardinality.

Set Membership

• 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): Finds courses offered in Fall 2009 and in Spring 2010.
• select distinct course_id from section where semester = 'Fall' and year= 2009 and course_id not in (select course_id from section where semester = 'Spring' and year= 2010): Finds course offered in fall 2009 but not in spring 2010

Set Comparison

• Uses the some clause for comparison
• Given by select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept name = 'Biology'
• Query using > some clause given by: select name from instructor where salary > some (select salary from instructor where dept name = 'Biology')
• Uses the all clause for comparision
  • 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');

Empty Relations Tests

• The exists construct returns true if the argument subquery is nonempty
• The logic for testing relations includes this syntax 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 is used to retrieve things such as employee name that has no dependents

Absence of Duplicate Tuples

• The unique construct tests whether a subquery has any duplicate tuples in its result.
• The unique construct evaluates to “true” if a given subquery contains no duplicates.

Subqueries in the From Clause

• SQL allows a subquery expression to be used in the from clause
• 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.
• Resulting subquery can be stored in a relation and the attributes in the result can be given new names

With Clause

• The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.

Database Modification

• The database can be modified through deleting, insertion and updating
• This can be done in a variety of ways, for example, deleting from the instructor table and stating parameters such department delete from instructor where dept_name= ’Finance’
• Insertions insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4) And
• Updating update instructor set salary = salary * 1.03 where salary > 100000