1 / 25

Seminar: Introduction to relational databases

Seminar: Introduction to relational databases. Introduction to the database field: SQL: Structured Query Language. SQL. SQL is a realisation of the relational model. SQL is much more than merely queries – it includes: DDL Data Definition Language DML Data Manipulation Language DCL

ayame
Télécharger la présentation

Seminar: Introduction to relational databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Seminar:Introduction to relational databases Introduction to the database field: SQL: Structured Query Language FEN 2012-08-14

  2. SQL • SQL is a realisation of the relational model. • SQL is much more than merely queries – it includes: • DDL • Data Definition Language • DML • Data Manipulation Language • DCL • Data Control Language FEN 2012-08-14

  3. SQL-Versions • For instance: • Oracle • MySQL • MS SQL Server • PostgreSQL • SQL has been implemented by many different DBMS-manufactures • SQL is to a large extend the same for most DBMSs – close to a de facto standard • Standards:SQL86 (SQL1), SQL89 (SQL1½), SQL92 (SQL2), SQL3 (SQL9x/SQL2000? - eventually SQL-99) • SQL2 is still the most common standard. • SQL-99 (Huge - released in 2002) • Now SQL:2003 (partly supported by MS SQL Server 2008,revisions SQL:2008, SQL:2011) • Most manufactures have their own extensions (and omissions) to the standard ??? If you are confused – it’s for a good reason. But in practice SQL2 is still most used, the rest is mostly extensions. FEN 2012-08-14

  4. Example: MiniBank Table definitions: Constraint FEN 2012-08-14

  5. Example: MiniBank Sample queries: • Retrieve information about customer number 3: FEN 2012-08-14

  6. Example: MiniBank Sample queries: • Retrieve account number, balance and customer number for accounts with a balance between 1000 and 2000: FEN 2012-08-14

  7. Example: MiniBank Sample queries: • Retrieve information about customer Tommy and his accounts: FEN 2012-08-14

  8. Example: Company - Schema FEN 2012-08-14

  9. Example: Company - Sample Data FEN 2012-08-14

  10. Example: Company - Foreign Key Constraints FEN 2012-08-14

  11. Company on SQL Server • Let’s see it work: • MS SQL Server • Did you note the order of table creation? • Did you note the order of inserting sample data? FEN 2012-08-14

  12. Company on SQL Server • Do we miss a foreign key constraint here: • Let’s try to make an error: change mgrssn to a not existing ssn. • Why didn’t we add a constraint when the table was created? • Solution: ALTER TABLE – let’s try. FEN 2012-08-14

  13. SQL Data Definition Language - Alter Table • DROP SCHEMA • DROP TABLE • ALTER TABLE • ADD (column) • DROP COLUMN • ALTER TABLE • DROP CONSTRAINT • ADD CONSTRAINT FEN 2012-08-14

  14. SQL: Data Manipulation Language • SELECT • UPDATE • INSERT • DELETE • Work on tables FEN 2012-08-14

  15. Queries: SELECT • Syntax: SELECT <attribute-list> FROM <tables> [WHERE <condition>] [GROUP BY <attribute-list>] [HAVING <condition>] [ORDER BY <attribute-list>] [...]: WHERE, GROUP BY, HAVING and ORDER BY may be omitted. FEN 2012-08-14

  16. Examples: Company (Q0): Row and column selection: SELECT Bdate, Address FROM Employee WHERE Fname= ’John’ AND Minit = ’B’ AND Lname = ’Smith’ All attributes: SELECT * --- FEN 2012-08-14

  17. Examples: Company (Q1): JOIN: SELECT Fname, Lname, Address FROM Employee, Department WHERE Dname= ’Research’ AND Dno= Dnumber • Last term in the WHERE-clause is the join-condition. If omitted the result will be the Cartesian product. • Alternative syntax is possible. FEN 2012-08-14

  18. Examples: Company (Q2): JOIN several tables: SELECTPnumber, Dnum, Lname, Address FROMProject, Employee, Department WHEREPlocation= ’Stafford’ AND Dnum= Dnumber AND Ssn= Mgrssn Note: Two join-conditions in the WHERE-clause. FEN 2012-08-14

  19. Examples: Company (Q8): Ambiguous attribute names and aliases: SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM Employee E, Employee S WHERE E.Superssn= S.Ssn Employee is joined with itself using the aliases E and S. ’.’ (”dot”)-notation may also be used to resolve ambiguous attribute Names (remember Minibank?). FEN 2012-08-14

  20. Examples: Company SQL-tables are NOT sets (in the math sense of the word set): (Q11):SELECT Salary FROM Employee (Q11A): SELECT DISTINCT Salary FROM Employee FEN 2012-08-14

  21. Examples: Company SQL-tables are NOT sets, but in set operations (UNION, INTERSECT and EXCEPT) they are: (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE LNAME = ’Smith’ AND DNUM = DNUMBER AND MGRSSN = SSN) UNION (SELECT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE WHERE LNAME = ’Smith’ AND PNO = PNUMBER AND ESSN = SSN) FEN 2012-08-14

  22. Updates i SQL: Updates: • Inserting rows: INSERT • Deleting rows: DELETE • Updating row values: UPDATE As SELECT they work on tables. FEN 2012-08-14

  23. Examples: Company Inserting a single row: INSERT INTO EMPLOYEE VALUES (’Richard’,’K’,’Marini’,’653298653’, ’30-DEC-52’,’98 Oak Forest, Katy, ’TX’,’M’,37000,’987654321’,4) Inserting a single row, selected attributes: INSERT INTO EMPLOYEE(FNAME,LNAME,SSN) VALUES (’Richard’,’Marini’,’653298653’) Is rejected if any of the other attributes is defined NOT NULL and doesn’t have defined a default value. FEN 2012-08-14

  24. Examples: Company Deleting rows: DELETE FROM EMPLOYEE WHERE LNAME =’Brown’ DELETE FROM EMPLOYEE WHERE SSN = ’123456789’ DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = ’Research’) DELETE FROM EMPLOYEE (Not equivalent to: ´DROP TABLE EMPLOYEE’. Why not?) FEN 2012-08-14

  25. Examples: Company Updating rows: UPDATE PROJECT SET PLOCATION = ’Bellaire’, DNUM = 5 WHERE PNUMBER = 10 UPDATE EMPLOYEE SET SALARY = SALARY*1.1 WHERE DNO IN (SELECT DNUMBER FROMDEPARTMENT WHERE DNAME = ’Research’) Note, that it is only possible to affect one table in one UPDATE statement. FEN 2012-08-14

More Related