1 / 14

SQL: Structured Query Language – Part 1

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

lucia
Télécharger la présentation

SQL: Structured Query Language – Part 1

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. SQL: Structured Query Language – Part 1 Data Definition: CREATE TABLE, ALTER TABLE Data Manipulation: INSERT, UPDATE, DELETE Queries: SELECT FEN 2014-02-06

  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 2014-02-06

  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 2014-02-06

  4. Example: MiniBank Table definitions: Constraint FEN 2014-02-06

  5. 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

  6. 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

  7. 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

  8. Company: Exercise • Do Exercise 2, phase 1 and 2 onCompanyExercise.pdf FEN 2014-02-06

  9. Company: Relations (PK – FK) FEN 2014-02-06

  10. 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

  11. Company: Exercise • Do exercise 2, phase 3 of CompanyExercise.pdf FEN 2014-02-06

  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 2014-02-06

  13. 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

  14. VW: new database • Look at this database:..\lektion03 (SQL2)\vwDatabase.pdf • Create the database. These scripts may be helpful. FEN 2014-02-06

More Related