introduction to sql n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction to SQL PowerPoint Presentation
Download Presentation
Introduction to SQL

Loading in 2 Seconds...

play fullscreen
1 / 58

Introduction to SQL - PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on

Introduction to SQL . Presented by : Satrio Agung Wicaksono. Definition. Structured Query Language (SQL) is a high-level language that allows users to manipulate relational data

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Introduction to SQL' - dee


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
introduction to sql

Introduction to SQL

Presented by :

SatrioAgungWicaksono

definition
Definition
  • Structured Query Language (SQL) is a high-level language that allows users to manipulate relational data
  • One of the strengths of SQL is that users need only specify the information they need without having to know how to retrieve it
three sql categories
Three SQL Categories
  • DDL – Data definition language used to define, change, or drop database objects
  • DML – Data manipulation language used to read and modify data
  • DCL – Data control language used to grant and revoke authorizations
history of sql
History of SQL
  • Don Chamberlin and Ray Boyce from IBM Corporation developed the SQL language in the 1970's as part of the System R project; a project established to provide a practical implementation to Codd's relational model
  • Originally, the language was termed “Structured English Query Language” or SEQUEL, but it was later changed to SQL as SEQUEL was a registered trademark of a UK based company
  • SQL was adopted as a standard language in 1986 by the American National Standards Institute (ANSI) and by the International Standards Organization (ISO) in 1987.
defining a relational database schema in sql
Defining a relational database schema in SQL
  • Data Types
    • Like any programming language, databases also support a limited set of data types, which can be used to define the types of data a column can store.
    • Basic data types include integer, float, decimal, char, date, time, blob, and so on
dates and times data types
Dates and Times Data Types
  • All databases support various date and time specific data types and functions
  • DB2 has the following data types for date and time. :
    • Date (YYYY-MM-DD)
    • Time (HH:MM:SS)
    • Timestamp (YYYY-MM-DD-HH:MM:SS:ssssss)
  • The following, is a partial set of functions specialized for date and time:
    • Year
    • Month
    • Day
    • Dayname
    • Hour
    • Minute
    • Second
    • Microsecond
creating a table
Creating a table
  • A table is a data set, organized and stored in rows and columns.
  • A table holds data for like items, for example students, professors, subjects, books etc
  • Entities in a data model generally map to tables when implemented in databases. Attributes of entities map to columns of the table
  • example:

create table myTable (col1 integer)

default values
Default Values
  • When data is inserted into a table, you may want to automatically generate default values for a few columns
  • Using default value :
  • To define a column that will generate a department number as an incremented value of the last department number
null values
NULL values
  • A NULL represents an unknown state
    • For example, a table that stores the course marks of students can allow for NULL values. This could mean to the teacher that the student did not submit an assignment, or did not take an exam. It is different from a mark of zero, where a student did take the exam, but failed on all the questions
  • when you don't want a NULL to be allowed. For example, if the country field is required for your application, ensure you prevent NULL values as follows

create table myTable (name varchar(30), country varchar(20) NOT NULL)

constraints
Constraints
  • Constraints allow you to define rules for the data in your table. There are different types of constraints:
    • A UNIQUE constraint prevents duplicate values in a table. This is implemented using unique indexes and is specified in the CREATE TABLE statement using the keyword UNIQUE. A NULL is part of the UNIQUE data values domain.
    • A PRIMARY KEY constraint is similar to a UNIQUE constraint, however it excludes NULL as valid data. Primary keys always have an index associated with it.
    • A REFERENTIAL constraint is used to support referential integrity which allows you to manage relationships between tables. This is discussed in more detail in the next section.
    • A CHECK constraint ensures the values you enter into a column are within the rules specified in the constraint.
constraints cont d
Constraints, Cont’d…
  • The following example shows a table definition with several CHECK constraints and a PRIMARY KEY defined:
referential integrity
Referential integrity
  • Referential integrity establishes relationships between tables.
  • Using a combination of primary keys and foreign keys, it can enforce the validity of your data.
  • Referential integrity reduces application code complexity by eliminating the need to place data level referential validation at the application level.
  • Only tables that have columns defined as UNIQUE or PRIMARY KEY can be referenced in other tables as foreign keys for referential integrity
referential integrity cont d
Referential integrity, Cont’d…
  • Referential integrity can be defined during table definition or after the table has been created as shown in the example below where three different syntaxes are illustrated:
  • Sintax 1 :
    • CREATE TABLE DEPENDANT_TABLE

(ID INTEGER REFERENCES BASE_TABLE(UNIQUE_OR_PRIMARY_KEY),

NAME VARCHAR(9),

:

:

:

);

  • Sintax 2 :
    • CREATE TABLE DEPENDANT_TABLE

(ID INTEGER,

NAME VARCHAR(9),

:

:

:,

CONSTRAINT constraint_name FOREIGN KEY (ID)

REFERENCES BASE_TABLE(UNIQUE_OR_PRIMARY_KEY)

);

referential integrity cont d1
Referential integrity, Cont’d…
  • Sintak 3:
    • CREATE TABLE DEPENDANT_TABLE

(ID INTEGER,

NAME VARCHAR(9),

:

:

:

);

    • ALTER TABLE DEPENDANT_TABLE

ADD CONSTRAINT constraint_name FOREIGN KEY (ID)

REFERENCES BASE_TABLE(UNIQUE_OR_PRIMARY_KEY);

referential integrity cont d2
Referential integrity, Cont’d…
  • There are different rules to handle deletes and updates and the behavior depends on the following constructs used when defining the tables:
    • CASCADE
      • As the name suggests, with the cascade option the operation is cascaded to all rows in the dependant tables that are referencing the row or value to be modified or deleted in the base table.
    • SET NULL
      • With this option all the referring cells in dependant tables are set to NULL
    • NO ACTION
      • With this option no action is performed as long as referential integrity is maintained before and after the statement execution.
    • RESTRICT
      • With this option, the update or delete of rows having references to dependant tables are not allowed to continue
  • The statement below shows where the delete and update rules are specified:
    • ALTER TABLE DEPENDANT_TABLE

ADD CONSTRAINT constraint_name

FOREIGN KEY column_name

ON DELETE <delete_action_type>

ON UPDATE <update_action_type> ;

  • A delete action type can be a CASCADE, SET NULL, NO ACTION, or RESTRICT. An update action type can be a NO ACT ON, or RESTRICT
creating a schema
Creating a schema
  • Just in the same way we store and manage data files on a computer in directories or folders and keep related or similar files together;
  • a schema in DB2 is a database object that allows you to group related database objects together.
  • In DB2, every object has two parts, a schema name, and the name of the object
creating a schema cont d
Creating a schema , Cont’d…
  • To create a schema, use this statement:
    • create schema mySchema
  • To create a table with the above schema, explicitly include it in the CREATE TABLE statement as follows:
    • create table mySchema.myTable (col1 integer)
  • When the schema is not specified, DB2 uses an implicit schema, which is typically the user ID used to connect to the database. You can also change the implicit schema for your current session with the SET CURRENT SCHEMA command as follows:
    • set current schema mySchema
modifying database objects
Modifying database objects
  • Once a database object is created, it may be necessary to change its properties to suit changing business requirements.
  • Dropping and recreating the object is one way to achieve this modification; however, dropping the object has severe side effects.
  • A better way to modify database objects is to use the ALTER SQL statement. For example, assuming you would like to change a table definition so that NULLs are not allowed for a given column, you can try this SQL statement:
    • alter table myTable alter column col1 set not null
renaming database objects
Renaming database objects
  • Once database objects are created, they can be renamed using the SQL statement, RENAME. To rename any database object use the following SQL syntax:
    • RENAME <object type> <object name> to <new name>
  • Where the object type can be for example, a table, table space, or index. Not all database objects can be renamed after they are created.
  • To rename a column, the ALTER TABLE SQL statement should be used in conjunction with RENAME. For example:
    • ALTER TABLE <table name> RENAME COLUMN <column name> TO <new name>
droping database table
Droping database table
  • DROP TABLE statement allows you to remove tables from your schema:
    • DROP TABLE SCHEMA.TABLE_NAME
    • Ex : DROP TABLE DB2ADMIN.EMPLOYEE
data manipulation with sql
Data manipulation with SQL
  • Selecting Data
  • Inserting Data
  • Deleting Data
  • Updating Data
structure of an sql query
Structure of an SQL Query

SELECT - Defines result columns

Column names

Arithmetic expressions

Literals (text or numeric)

Scalar functions

Column functions

Concatenation

FROM - Table or view names

WHERE - Conditions (qualifies rows)

ORDER BY - Sorts result rows

alternate order by specifications
Alternate ORDER BY Specifications
  • SELECT LASTNAME, FIRSTNME, WORKDEPT, JOB, SEX FROM EMPLOYEE ORDER BY WORKDEPT DESC, JOB, LASTNAME, SEX DESC

Equivalent ORDER BY clauses:

  • ORDER BY WORKDEPT DESC, JOB ASC, LASTNAME ASC, SEX DESC
  • ORDER BY 3 DESC, 4, 1, 5 DESC
  • ORDER BY 3 DESC, 4 ASC, 1 ASC, 5 DESC
  • ORDER BY 3 DESC, JOB, LASTNAME, 5 DESC
  • ORDER BY WORKDEPT DESC, 4 ASC, 1 ASC, SEX DESC
comparison operators
Comparison Operators

SELECT *

FROM EMPLOYEE

WHERE SALARY = 20000 -- equal to

OR SALARY <> 20000 -- not equal to

OR SALARY > 20000 -- greater than

OR SALARY >= 20000 -- greater than or equal to

OR SALARY < 20000 -- less than

OR SALARY <= 20000 -- less than or equal to

maintaining data
Maintaining Data
  • TESTEMP Table
  • The visual shows the contents of the TESTEMP table.
  • This table will be the basis for the exercises of this unit
ddl create table testemp
DDL Create Table TESTEMP

CREATE TABLE TESTEMP

(

EMPNO CHAR(6) NOT NULL,

LASTNAME VARCHAR(15) NOT NULL, WORKDEPT CHAR(3),

HIREDATE DATE,

SALARY DECIMAL(9,2),

BONUS DECIMAL(9,2)

)

reminders
Reminders
  • You must be authorized to execute CREATE/DROP table statements
  • You must be authorized to execute statements which change table content
  • Dropping a table drops dependent objects
  • Search condition in the WHERE clause qualifies rows to be updated or deleted
  • Inserting a row into a view actually inserts the row into the table on which the view is based
  • Updating a row of a view updates the row of the table on which the view is based
  • Omitting the WHERE clause of an UPDATE or DELETE
  • statement causes all rows of the table or view to be updated or deleted