SQL (Structured Query Language): definition, divisions, and common operators

Data and Computers

SQL (Structured Query Language) Overview

· A standard language has been designed to operate on a relational database: SQL (Structured Query Language). · It is a language of interrogation and manipulation of the database and the information it contains. · It's not an imperative/procedural language, it's a declarative language. · As a declarative language, SQL does not require the writing of sequences of operations (such as Imperative Languages), but rather specifies the logical properties of the information sought.

1Database Management II. Data and Computers

SQL Language Divisions

· It is divided into: Data Definition Language (DDL) - Allows to create and delete databases or modify their structure Data Manipulation Language (DML) - Allows to query and update the database Data Control Language (DCL) - Allows to manage users and permissions Device Media Control Language (DMCL) - It allows to control the media (mass storage) where the data is stored.

2Database Management II. Data and Computers

DDL (Data Definition Language) Introduction

. It is the SQL instruction set for defining the structure of the database. . DDL commands define the structure of the database and therefore the data it contains, but they do not provide the tools to modify the data itself for which the DML is used. . The user must have the necessary permissions to act on the database structure, and these permissions are assigned via the Data Control Language (DCL).

3II. Data and Computers

DDL Main Instructions

. There are three main instructions of the DDL: v create: for creating databases, tables, indexes, views, etc. alter: for modifying the structure of a table or other objects within a database drop: for deleting a table, an entire database, or other objects. Examples: Creating a Database: create database students Deleting a Database: drop database students

4II. Data and Computers

DDL Example: Creating a Table

Example of SQL commands for creating a table CREATE TABLE Demographic ( PatNo Integer NOT NULL default 1, Name char(40), Sex char(1), BirthDate date, Address char(50), City char(30), PhoneN Integer(10) );

5II. Data and Computers

DDL Intra-relational Constraints

Intra-relational constraints primary key Elects the specified attribute(s) as the primary key for the relationship not null Forces the attribute to always have a defined value unique Dictates that no two tuples of the relationship exist with the same value on the attribute(s). Attributes that are unique are also superkeys to the relationship primary key implies not null and unique on the affected attributes

6II. Data and Computers

Primary Key Constraint Example

Create table Department ( Name char (20) primay key Address char (50), primary key (Name) )

7II. Data and Computers

DDL Interrelational Constraints

Interrelational Constraints references after the attribute expresses a referential integrity constraint foreign key ... references For attribute sets (at the end of the Create Table) … foreign key (Name, Surname) references Anagraphic(Name, Surname) ) Referential Integrity Constraints create table Employee ( CF char(16) references Person(CF) )

8II. Data and Computers

DCL (Data Control Language)

. It is the set of SQL statements for defining access permissions on databases and tables and for managing user accounts. . There are two main instructions in the DCL: v grant: to assign a certain permission to a user V revoke: revoke a certain permission from a user Granting Permits: grant Privileges on resource to Users with grant option Example: grant select on students to Rossi

9Database Management II. Data and Computers

DML (Data Manipulation Language) Overview

· Provides commands for inserting, modifying, and deleting data within tables in a database, and for querying. . The structure of this data must already have been defined via the DDL. In addition, permission to access such data must be previously assigned to the user via the DCL. . All database queries are performed directly or indirectly using the selection command specified by the operation SELECT.

10II. Data and Computers

DML Main Instructions

. There are four main instructions in the DML: v insert: To insert data into a table update: To change the data in a table based on a certain selection criterion delete: To delete records in a table that match a certain condition select: To select data in one or more tables based on certain criteria or conditions PRIVILEGES!

11II. Data and Computers

DML Insert Statement Syntax

Syntax of the insert statement: insert into table (field1, . fieldk) values (value1 , ... , valuek) Example: insert into course (abbreviation, credits, name, professor) values ( INFMED' 6, 'Medical Informatics' , 'Micaela Morettini' )

12II. Data and Computers

DML Update Statement Syntax

Syntax of the update statement: update table set field =value1, .. , fieldk=valuek where (condition) Example: · update student set name= 'Rossi' · where universityid=12345 Warning: An update statement without a condition updates all rows in the table!

13II. Data and Computers

DML Delete Statement Syntax

Syntax of the delete statement: delete from table where (condition) Example: delete from student where name like 'Ro%' or universityid=213243 Warning: A delete statement without a condition deletes all records in the table!

14II. Data and Computers

DML Select Statement Syntax

Syntax of the select statement: select (table1 . field1, ... , tablek . fieldk) from table1, ... , tablek where (condition) The three parts are called: target list From clause Where clause

15II. Data and Computers

DML Select Statement Details

. The simplest form of the selection command is one in which the selection list consists of one or more column names from the same table . If you need to select all the columns in a table, you can use the syntax: SELECT FROM table . The WHERE clause allows you to specify conditions in the selection. . The conditions of the WHERE clause are specified using comparison operators, logical connectors, and BETWEEN, IN, LIKE, IS NULL operators.

16II. Data and Computers

DML WHERE Clause Operators

· WHERE clause = < > >= <= <> Comparison Operators AND, OR, NOT Logical Connectors

17Database Management II. Data and Computers

DML WHERE Clause Advanced Operators

The BETWEEN operator checks to see if an argument is within a range of values; It uses the following syntax: BETWEEN AND ; The IN operator checks whether an operand is contained in a sequence of values. The LIKE operator checks whether a string of characters matches a particular format. The following wildcards can be used to define the format: indicates any single character; _ % indicates any sequence of characters. WHERE ID LIKE '0 5 '

18II. Data and Computers

DML IS NULL Operator

The IS NULL operator checks to see if the contents of an operand are NULL. The latter is a state that indicates that the table item in the specific column does not contain any values. SELECT * FROM Anagraphic WHERE PhoneN IS NULL

19II. Data and Computers

DML Join Operations

. You can perform selection operations involving multiple tables because of the correlations established between them through primary keys and foreign keys Join in the FROM clause you must indicate the two related tables on which the join is to be made in the WHERE clause the connection between the two tables must be expressed, by means of a special condition called the join condition

20II. Data and Computers

DML Column Qualification

In the WHERE clause and in the selection list, you can specify the names of the columns by qualifying them by the name of the table to which they belong, used as a prefix in the form: table_name.column_name

21II. Dati e computer

DML Employee Table Example

Employee Name Surname Department Office Salary Mario Rossi Administration 10 45 Carlo Bianchi Production 20 36 Giuseppe Verdi Administration 20 40 Franco Neri Logistic 16 45 Carlo Rossi Direction 14 80

22II. Data and Computers

DML Select Examples

Identify the salary of all employees with surname'Rossi'. select Salary from Employee where Surname = 'Rossi' Retrieve all employee information by surname 'Rossi' select from Employee where Surname = 'Rossi'

23II. Data and Computers

DML Monthly Salary Calculation

Finding 'Bianchi' monthly salary select Salary/12 as MonthlySalary from Employee where Surname = 'Bianchi'

24II. Dati e computer

DML Employee and Department Tables

Employee Name Surname Department Office Salary Mario Rossi Administration 10 45 Carlo Bianchi Production 20 36 Giuseppe Verdi Administration 20 40 Franco Neri Logistic 16 45 Carlo Rossi Direction 14 80 Department Name Address City Administration Via Tito Livio, 27 Milano Production Pl. Lavater, 3 Torino R&D Via Morone, 6 Milano Logistic Via Segre, 9 Roma Direction Via Tito Livio, 27 Milano

25II. Data and Computers

DML Join Example: Employee City

Find the first and last names of employees and the cities in which they work select Employee.Name, Employee.Surname, Department.City from Employee, Department where Employee. Department = Department. Name Alternative Way: Select E.Name, E.Surname, D.City from Employee E join Department D On E.Department=D.Name Allows to specify the type of join !! INNER (theta join), LEFT OUTER, RIGHT OUTER, FULL OUTER

26II. Data and Computers

DML Join Example with Aliases

Employee Name Surname Department Office Salary Mario Rossi Administration 10 45 Administration Via Tito Livio, 27 Milano Carlo Bianchi Production 20 36 Production Pl. Lavater, 3 Torino Giuseppe Verdi Administration 20 40 R&D Via Morone, 6 Milano Franco Neri Logistic 16 45 Carlo Rossi Direction 14 80 Logistic Via Segre, 9 Roma Direction Via Tito Livio, 27 Milano select E.Name as Name, Surname, D.Name as DepName from Employee E, Department D Where E.Department=D.Name Department Name Address City alias = allows to refer to tables without having to specify their full name

27