SQL Lecture 10: Structured Query Language - Database Management and Commands, Study notes of Calculus

Download SQL Lecture 10: Structured Query Language - Database Management and Commands and more Study notes Calculus in PDF only on Docsity! KTH ROYAL INSTITUTE OF TECHNOLOGY Lecture 10 Structured Query Language Summary from previous lecture 1. Entity-relation diagrams: entity, relation, attributes. 2. Database structure: • Tables are known as ”Relations” • Rows are ”Tuples” • Columns are ”Attributes” 3. Normalisation of database. SQL coding Most DBMS allow SQL to be used in two distinct ways: • Interactive SQL. SQL commands can be typed at the command line directly. The DBMS interprets and processes the SQL commands immediately, and the results are displayed. • Programmatic SQL. SQL statements are embedded in a host language such as Java, C, Python etc. The host language provides the necessary looping and branching structures and the interface with the user, while SQL provides the statements to communicate with the DBMS. Datatypes in SQL 1. String (text): • CHAR datatype (fixed-length character data): CHAR (n) • VARCHAR datatype (variable-length character string): VARCHAR (n) 2. Numbers: • INTEGER datatype (default 10 digits): INTEGER(p) • FLOAT datatype (default mantissa 16): FLOAT(p) 3. DATE Datatype SQL commands SQL commands can be classified into three types: 1. Data Definition Language commands (DDL) 2. Data Manipulation Language commands (DML) 3. Data Control Language commands (DCL) Contents • SQL description: • SQL definition • SQL datatypes • SQL Syntax • Relation Calculus • SQL commands Tuple Relational Calculus List of main operations used to manipulate Relations: • INSERT • DELETE • UPDATE • SELECT • JOIN • UNION INSERT command INSERT is a unary operation – it operates on a single Relation and adds a Tuple to a Relation. ID Name Grade 1 Jill D 2 Bob B 3 Steve C ID Name Grade 1 Jill D 2 Bob B 3 Steve C 4 Lars A INSERT INTO Relation VALUES (‘&attribute1’,…) SELECT command SELECT is a unary operation – it operates on a single Relation. The SELECT operation creates a new relation R2 from relation R1. The Tuples in R1 is a subset of R2 ID Name Grade 1 Jill D 2 Bob B 4 Lars E ID Name Grade 2 Bob B 4 Lars E SELECT &attribute1, &attribute2 FROM tablename; SELECT command Extension to SELECT command: • SELECT * FROM R1 WHERE a1=6; • SELECT * FROM R1 GROUP BY a1; • SELECT * FROM R1 ORDER BY a1 (ASC, DESC); • SELECT * FROM R1 HAVING a2>3; • SELECT a1,a3 FROM R3 WHERE a2 IN (value1,value2); JOIN command JOIN is a binary operation – it operates two Relations. The JOIN operation creates a new relation R3 from relations R1 & R2 based on common attributes (keys). X Not Normalised?? Course Professor EH2745 Nordström EH2751 Nordström EJ2301 Soulard EG2200 Amelin Professor Office Nordström Osquldas väg 10, floor 7 Amelin Teknikringen 33, floor 2 Soulard Teknikringen 33, floor 1 Course Professor Office EH2745 Nordström Osquldas väg 10, floor 7 EH2751 Nordström Osquldas väg 10, floor 7 EJ2301 Soulard Teknikringen 33, floor 1 EG2200 Amelin Reknikringen 35, floor 2 SELECT R1.a1, R1.a2, R2.a2 FROM R1 JOIN R2 ON R1.A2=R2.A1