Docsity
Docsity
Prepara i tuoi esami
Prepara i tuoi esami

Studia grazie alle numerose risorse presenti su Docsity

Ottieni i punti per scaricare
Ottieni i punti per scaricare

Guadagna punti aiutando altri studenti oppure acquistali con un piano Premium

Guide e consigli
Guide e consigli
Vendi su Docsity
Vendi su Docsity
Docsity AI
Docsity AI
AccediRegistrati

Prepara i tuoi esami

Studia grazie alle numerose risorse presenti su Docsity

Cerca documenti

Prepara i tuoi esami con i documenti condivisi da studenti come te su Docsity

Cerca la tua università

Trova i documenti specifici per gli esami della tua università

Video Corsi

Preparati con lezioni e prove svolte basate sui programmi universitari!

Quiz

Rispondi a reali domande d’esame e scopri la tua preparazione

Docsity AINEW

Riassumi i tuoi documenti, fagli domande, convertili in quiz e mappe concettuali

Maturità 2026

Studia con prove svolte, tesine e consigli utili

Esplora domande

Togliti ogni dubbio leggendo le risposte alle domande fatte da altri studenti come te

Argomenti di studio

Esplora i documenti più scaricati per gli argomenti di studio più popolari

Ottieni i punti per scaricare

Guadagna punti aiutando altri studenti oppure acquistali con un piano Premium

Condividi documenti
download point icon20 Punti

Per ogni documento caricato

Rispondi alle domande
download point icon5 Punti

per ogni risposta data (max 1 al giorno)

Tutti i modi per ottenere punti gratis
Ottieni punti subito

Scegli un piano Premium con tutti i punti di cui hai bisogno

Opportunità di studio

Scegli il tuo prossimo programma di studio

Entra in contatto con le migliori università del mondo e scegli il tuo percorso di studi

Classifica delle migliori università

Scopri le migliori università italiane secondo gli studenti

Community

Chiedi alla community

Chiedi aiuto alla community e sciogli i tuoi dubbi legati allo studio

Guide Gratuite

I nostri eBook salva studente

Scarica gratuitamente le nostre guide sulle tecniche di studio, metodi per gestire l'ansia, dritte per la tesi realizzati da tutor Docsity

SQL and Data Management, Sintesi del corso di Architettura Tecnica

Politecnico di Milano (POLIMI)Architettura Tecnica
Prof. Marco Brambilla

cuybersecurtiry corso eicta polimi

Tipologia: Sintesi del corso

2024/2025

Caricato il 24/02/2026

flavia-cinquepalmi
flavia-cinquepalmi 🇮🇹

4 documenti

1 / 4

Toggle sidebar

Questa pagina non è visibile nell’anteprima

Non perderti parti importanti!

bg1
4) SQL and Data Management
TOPIC OF THE CLASS:
Introduction to SQL
SQL Components
Tables and Schemas
Basic SQL Queries
Joins
Subqueries
Aggregation & Grouping
… Oct 2024
Introduction to SQL
Standard language for querying and manipulating data
Structured Query Language
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system
(RDBMS), or for stream processing in a relational data stream management system (RDSMS).
A query is a request to retrieve specific information from one or more database tables.
Characteristics
Data stored in columns and tables
Relationships represented by data
Data Manipulation Language
Data Definition Language
Abstraction from physical layer
Transactions
Big Fail or Big Win?
SQL – Big Fail? SQL – Big Win?
Users must specify how to retrieve data
(procedural) rather than just what
Declarative: users only specify
what they want
Declarativity
Declarativity: "What, not How"
No need to write the whole code because the system is automatically choosing the best option available.
Versions
SQL 1999 - Turing Complete
4 SQL and Data Management
1
SQL is not a programming language, it is a declarative language (not procedural). In SQL you just declare your needs, you do not write
the procedure to find it. It describes what, not how, which will be solved by the database. The program finds the query optimization.
pf3
pf4

Anteprima parziale del testo

Scarica SQL and Data Management e più Sintesi del corso in PDF di Architettura Tecnica solo su Docsity!

4 ) SQL and Data Management

TOPIC OF THE CLASS :

Introduction to SQL

SQL Components

Tables and Schemas

Basic SQL Queries

Joins

Subqueries

Aggregation & Grouping

… Oct 2024

Introduction to SQL

Standard language for querying and manipulating data

S tructured Q uery L anguage

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system

(RDBMS), or for stream processing in a relational data stream management system (RDSMS).

A query is a request to retrieve specific information from one or more database tables.

Characteristics

Data stored in columns and tables

Relationships represented by data

Data Manipulation Language

Data Definition Language

Abstraction from physical layer

Transactions

Big Fail or Big Win?

SQL – Big Fail? SQL – Big Win?

Users must specify how to retrieve data

(procedural) rather than just what

Declarative: users only specify

what they want

Declarativity

Declarativity: "What, not How"

No need to write the whole code because the system is automatically choosing the best option available.

Versions

SQL 1999 - Turing Complete

Turing complete: a system (programming language) is capable of performing any computation

that can be described algorithmically,

given enough time and memory.

https://blog.jooq.org/10-sql-tricks-that-you-didnt-think-were-possible/

Physical Layer Abstraction

SQL provides abstraction between what the user writes and how the system executes it.

Query Optimization Engine The DBMS analyzes the query and chooses the most efficient execution plan automatically.

Physical Layer Can Change You can change indexes, storage, or table layout , and SQL apps still work without change.

Indexes You can create indexes to improve performance — but queries don’t need to change.

In-Memory Databases Data may be stored in RAM instead of on disk, users don’t need to know or change queries.

SQL stays the same, even if the underlying physical layer or performance techniques change.

SQL Components

DML (Data Manipulation Language) DDL (Data Definition Language)

Used to access and modify data in existing tables. Used to define and manage the structure of the database.

Affects Table rows (data) Affects Table schema (structure)

DDL (Data Definition Language)

Used to define and manage the structure of the database.

Create/alter/delete tables and their attributes

CREATE TABLE - define a new table

ALTER – change table structure

DROP – remove a table or column

Constraints: PRIMARY KEY , FOREIGN KEY

Triggers to respond to Insert, Update , & Delete (reactive databases!)

Stored Modules – reusable pieces of SQL logic

Security and Access Control – manage user permissions

DML (Data Manipulation Language)

Used to access and modify data in existing tables.

Query one or more tables: FROM , WHERE , ORDER BY

Insert/delete/modify tuples in tables

SELECT – retrieve data

INSERT – add new rows

UPDATE – change existing rows

DELETE – remove rows

Data aggregation functions : SUM , COUNT , AVG , MAX , MIN

Compound Statements : Multiple steps in one command

Functions and Procedures : For reusable logic

Explicit Transaction Control : BEGIN , COMMIT , ROLLBACK

Tables and Schemas Table

A table is a set of tuples (unordered) where a tuple = a record

Schema

The schema of a table is the table name and its attributes: TableName(Attribute 1 , Attribute 2 , ...)

Keys & Foreign Keys

A key is an attribute whose values are unique; we underline a key when writing the scheme.

Data Types

Atomic Types:

Characters: CHAR , VARCHAR ,

Numbers: INT , FLOAT ,

Join + filtering (alias) can be used in SQL

SELECT p.pname, p.price

FROM Product AS p

JOIN Company AS c ON p.manufacturer = c.cname

WHERE c.country = 'Japan' AND p.price  200;

Subqueries

In SQL, una outer query (o query esterna ) è la query principale che contiene al suo interno un'altra query — chiamata subquery o inner query.

SELECT ...

FROM ...

WHERE ... (subquery)

Subqueries returning relations

A subquery returning a relation is a SELECT statement nested inside another SQL query that returns a set of rows (a table).

Subqueries with IN , ANY , ALL , EXISTS

SELECT name

FROM Product

WHERE price  ALL 

SELECT price

FROM Purchase

WHERE maker = 'Gizmo-Works'

#This finds products more expensive than every product made by Gizmo-Works.

Complex Correlated Query

A correlated subquery is a subquery that refers to a column from the outer query.

SELECT DISTINCT x.pname, x.maker

FROM Product AS x

WHERE x.price  ALL 

SELECT y.price

FROM Product AS y

WHERE y.maker = x.maker AND y.year  1972

#This finds products that are more expensive than any older product made by the same manufacturer.

Aggregation & Grouping

Aggregation in SQL means performing a calculation on a set of values to return a single summarized result.

It groups multiple rows together and computes a value like COUNT , AVG , SUM , MIN , MAX.

Aggregation Function Apply to

AVG , SUM , MIN , MAX One specific column at time

COUNT or COUNT* Counts all rows, regardless of column values

COUNT DISTINCT column) Counts unique non-NULL values in a specific column

COUNT(category) counts rows where category is not NULL.

GROUP BY Groups rows sharing the same values in specified columns to perform aggregation on each group

HAVING Filters groups after aggregation (like WHERE but for groups)

SQL Cheat Sheet

attachment:ccf3902d-8475-4396-8b07-a6ee12abdace:E03_-_SQL.pdf