140 likes | 276 Views
SQL: Structured Query Language – Part 1. Data Definition: CREATE TABLE, ALTER TABLE Data Manipulation: INSERT, UPDATE, DELETE Queries: SELECT. SQL. SQL is a realisation of the relational model. SQL is much more than merely queries – it includes: DDL Data Definition Language DML
E N D
SQL: Structured Query Language – Part 1 Data Definition: CREATE TABLE, ALTER TABLE Data Manipulation: INSERT, UPDATE, DELETE Queries: SELECT FEN 2014-02-06
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 2014-02-06
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 2014-02-06
Example: MiniBank Table definitions: Constraint FEN 2014-02-06
Example: MiniBank Sample queries: • Retrieve information about customer number 3: Note: The result is a table (with only one row, not a tuple). FEN 2014-02-06
Example: MiniBank Sample queries: • Retrieve account number, balance and customer number for accounts with a balance between 1000 and 2000: Note: The result is a table. FEN 2014-02-06
Example: MiniBank Sample queries: • Retrieve information about customer Tommy and his accounts: Note: The result is a table (with only one row, not a tuple). FEN 2014-02-06
Company: Exercise • Do Exercise 2, phase 1 and 2 onCompanyExercise.pdf FEN 2014-02-06
Company: Relations (PK – FK) FEN 2014-02-06
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 2014-02-06
Company: Exercise • Do exercise 2, phase 3 of CompanyExercise.pdf FEN 2014-02-06
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 2014-02-06
SQL Data Definition Language - Alter Table • DROP SCHEMA • DROP TABLE • ALTER TABLE • ADD (column) • DROP COLUMN • ALTER TABLE • DROP CONSTRAINT • ADD CONSTRAINT FEN 2014-02-06
VW: new database • Look at this database:..\lektion03 (SQL2)\vwDatabase.pdf • Create the database. These scripts may be helpful. FEN 2014-02-06