250 likes | 371 Views
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
E N D
Seminar:Introduction to relational databases Introduction to the database field: SQL: Structured Query Language FEN 2012-08-14
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
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
Example: MiniBank Table definitions: Constraint FEN 2012-08-14
Example: MiniBank Sample queries: • Retrieve information about customer number 3: FEN 2012-08-14
Example: MiniBank Sample queries: • Retrieve account number, balance and customer number for accounts with a balance between 1000 and 2000: FEN 2012-08-14
Example: MiniBank Sample queries: • Retrieve information about customer Tommy and his accounts: FEN 2012-08-14
Example: Company - Schema FEN 2012-08-14
Example: Company - Sample Data FEN 2012-08-14
Example: Company - Foreign Key Constraints FEN 2012-08-14
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
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
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
SQL: Data Manipulation Language • SELECT • UPDATE • INSERT • DELETE • Work on tables FEN 2012-08-14
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
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
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
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
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
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
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
Updates i SQL: Updates: • Inserting rows: INSERT • Deleting rows: DELETE • Updating row values: UPDATE As SELECT they work on tables. FEN 2012-08-14
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
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
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