SQL1S20 (2).pptx - Structured Query Language Brief History \u2022 Developed in early 1970 for relational data model \u2013 Structured English Query

SQL1S20 (2).pptx - Structured Query Language Brief History...

This preview shows page 1 - 11 out of 39 pages.

Structured Query Language
Image of page 1
Brief History Developed in early 1970 for relational data model: Structured English Query Language (SEQUEL) Implemented with IBM System R 1987 first ISO standard version 1992 SQL 2 1999 SQL 3 Object-relational model 2003:SQL 2003 SQL/XML 2016: SQL 2016 JSON
Image of page 2
Language Overview Three major components: Data definition language, DDL Create Schema Create Table Alter Table Data manipulation language, DML Updating database: Insert, Delete, Update Query database: Select Data control language (DCL) Help DBA control the database: Grant/revoke privileges to access the database, creating procedures, etc.
Image of page 3
Extensions to Standard SQL SQL Procedural language To create stored procedures, user-defined functions, triggers, etc. Example Oracle: PL/SQL, MySQL SQL/PSM (SQL/Persistent Stored Modules) Microsoft SQL Server: Transact-SQL (T-SQL) IBM DB2: SQL Procedural Language
Image of page 4
Interface with database Using a visual tool: MySQL Workbench Entering command at the DBMS command prompt. MySQL Command Line Client Embedded in a procedural language.
Image of page 5
Data Definition Language SQL Identifiers Character set: A-Z, a-z, 0-9, _ <= 128 characters Start with a letter Cannot contain spaces
Image of page 6
SQL Data Types Boolean True, False, Unknown (for Null) Character Fixed length: CHARACTER(n), CHAR(n), CHAR – default to 1 character Varying length: VARCHAR(n): Maximum length is n Numeric DECIMAL(i,j) INTEGER, INT, SMALLINT – up to 32767 FLOAT, REAL, DOUBLE PRECISION Date: DATETIME TIMESTAMP Large objects: CHARACTER LARGE OBJECT BINARY LARGE OBJECT JSON, Set
Image of page 7
Create Schema CREATE SCHEMA schemaName;
Image of page 8
CREATE TABLE CREATE TABLE tableName(fields and data type separated by commas); Example: CREATE TABLE employee( eid CHAR(5), ename VARCHAR(40), sex CHAR(1), salary DECIMAL(9,2), hire_Date DATETIME);
Image of page 9
Integrity Enhancement Feature Required data: NOT NULL eid CHAR(5) NOT NULL, Default value:DEFAULT sex CHAR DEFAULT ‘M’, Field domain: CHECK(condition) salary NUMERIC(9,2) CHECK (salary >= 100 AND salary <=10000), sex CHAR DEFAULT ‘M’ CHECK (sex in (‘M’,’F’)), PRIMARY KEY
Image of page 10
Image of page 11

You've reached the end of your free preview.

Want to read all 39 pages?

  • Spring '14
  • B MAK
  • ALTER

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes