Introduction to SQL

Intermediate SQL

• SQL stands for Structured Query Language
• SQL is a domain-specific language and is not a general programming language
• SQL is specialized to handle structured data following a relational model
• Used to interact with relational databases to manage data for creating, populating, modifying, or destroying data, with the ability to manage data access.
• SQL has language specifications with sets of languages, rules, and syntax
• SQL is structural, requiring a rigid predefined schema, unlike 'noSQL
• SQL grammar and syntax are strict
• SQL has specific features like triggers and stored procedures

History of SQL

• SQL was first developed in the 1970s by two scientists at IBM
• This followed a theory of relational algebra by Edgar F. Codd, who was also an IBM scientist
• The first commercial implementation of SQL-based RDMBS was Oracle's V2
• It was first adopted by ANSI in 1986 and ISO in 1987 as a standard
• The latest version of the SQL standard is from 2016
• Despite standardization, SQL code may not seamlessly migrate between different RDBMS

Terminology

• Database: A structured collection of data stored and accessed electronically, used for data storage, analysis, and management
• Table: a collection of related data in an organized structure of rows and columns
• Column: is a set of data values of a particular type for each table
• Row: represents a single, implicitly structured data in a table
• Relation: is a set of tuples that have the same attributes and characteristics
• Primary Key: is a column or set of columns that uniquely identifies each row in a table
• Foreign Key: is a column or set of columns that link data in one table to another

Data Definition Language (DDL)

• DDL is a language used to define storage groups, structures, and database objects
• DDL statements create, modify, and remove database objects like tables and indexes
• Common DDL languages are CREATE, DROP, ALTER, comment, label on, rename, and truncate

Data Types in SQL

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

Create Table

• SQL defines relations using the create table command
• Integrity constraints can be included during table creation like not null, primary key and foreign key

Basic Query Structure

• Typical SQL query format: select A1, A2, ..., An from r1, r2, ..., rm where P
• A represents an attribute, R represents a relation
• P is a predicate, and SQL query results in a relation
• The select clause lists attributes desired in the query's result
• The distinct keyword is used to eliminate duplicate results
• The keyword all specifies that duplicates should not be removed
• SQL names are case-insensitive

The Where Clause

• The where clause provides conditions that the result must satisfy
• To find all instructors in Comp. Sci. dept you can use select name from instructor where dept_name = 'Comp. Sci.'
• Comparison results are combined using logical connectives like and, or, or not
• Comparisons can be applied via results of arithmetic expressions and is aConditional Statement >,>=, <,<=,

The From Clause

• The from clause lists the relations involved in the query
• The where clause condition joins selections in relational algebra providing conditions

String Operations

• SQL provides string-matching operators for comparisons on character strings
• The operator like uses patterns with two characters: % (matches any substring) and _ (matches any character)
• String patterns are case-sensitive
• SQL supports string operations like concatenation, case conversion, finding string length, extracting substrings, etc

Ordering Tuples

• To list instructors' names in alphabetic order: select distinct name from instructor order by name
• Specify desc for descending order or asc for ascending order while ascending order is the default
• It is possible to sort on multiple attributes

Set Operations in SQL

• SQL supports union, intersect, and except
• The above operations automatically eliminate duplicates
• Multiset versions to retain duplicates are union all, intersect all and except all
• Aggregate functions in SQL return a value

Null Values

• It's possible to have a null value where it signifies an unknown value where a value does not exist
• Any arithmetic expression involving null results in null
• The predicate is null is use to check for null values, for example: select name from instructor where salary is null

Nested Subqueries

• Nested subqueries are select select-from-where` expressions within another query
• Nesting allows subqueries to replace values, relations, or conditions within a main query
• Use exists to evaluate if there are subqueries
• Use the unique construct to test for duplicates in query results

Subqueries in the From Clause

• SQL allows subquery expressions
• If subqueries in select clause a runtime error can occur if there are more than one tuple

SQL Modifications

• SQL enables modification of the database through deletion of tuples from a relation, insertion of new tuples, and updating values in tuples
• delete from instructor where dept_name= 'Finance'; will remove all instructors from the finance department