Manel Rhaiem

Web Developer | Enthousiaste

TutorialSQLStatementsPART1

This tutorial helps you to learn how to write Sql Statements let me start with :

How to Create your DataBase just you need to make it in one line:

CREATE DATABASE database_name

Now when you need to create new Table on your Data Base you have to execute:

CREATE TABLE table_name(
  column_name1 data_type,
  column_name2 data_type,
  column_name3 data_type,
  ...
 )
  • The data type can be: Text, Byte, Integer, Double,Date,Char,Varchar(long),DateTime…..
  • They are 5 Types of Constraint
    • NOT NULL
    • UNIQUE
    • CHECK
    • PRIMARY KEY

    Type Data Primary Key

  • FOREIGN KEY
CONSTRAINT  <fk table column> FOREIGN KEY (col1, col2, ... coln)  REFERENCES<extended table> (col1, col2, ...  coln)

OR

<col1>CONSTRAINT <fk table column> REFERENCES <extended Table>(col)

To make some modification on Table exist on your Data Base

ALTER TABLE table_name ADD column_name datatype

OR

ALTER TABLE table_name DROP COLUMN column_name
  • Add: to add a column
  • Modify: apply some modification on column exist
  • Drop: Drop column
  • Add Constraint: to add a constraint to a Column
  • Drop Constraint: to delete constraint

Create and Modify View: a view is a virtual table based on the result-set of an SQL statement.

CREATE [OR REPLACE ] [FORCEjNOFORCE] VIEW <nom vue> [(alias [, alias], . . . )] AS SELECT [WITH CHECK OPTION    [CONSTRAINT<nom contrainte>]] [WITH READ ONLY [CONSTRAINT <nom contrainte>]] ;

Create Sequence :

CREATE SEQUENCE <nom séquence>
[INCREMENT BY <pas>]
[START WITH <valeur>]
[MAXVALUE <valeur max> |NOMAXVALUEg]
[MINVALUE <valeur min> | NOMINVALUEg]
[CYCLE j NOCYCLEg]
[CACHE <cache> |NOCACHE]
  • INCREMENT BY: define the step of incrementation
  • START WITH : the first number of incrementation
  • MAXVALUE |NOMAXVALUE : The MAX value
  • MINVALUE || NOMINVALUE: The Min Value
  • CYCLE | NOCYCLE
  • CACHE | NOCACHE: values for performance

Create INDEX: used to speed up searches/queries

CREATE [UNIQUE] INDEX <nom index> ON <nom table>(col1, col2,...)

Create and Delete Synonym:

CREATE [OR REPLACE] [PUBLIC] SYNONYM<nom synonyme> FOR [schema.]<nom objet>

Select Statement :

it is used to Select Data from data Base they different kind of Select for Example:

Select * From Table
  • With condition:

Select * From Table_name Where Column like ‘Ahmed’

  • Sometimes we can found duplicated values in a Table so we use DISTINCT :

Select DISTINCT name from EMPLOYEE

  • Aliases: give to a column a temporary name:

SELECT column_name AS alias_name FROM table_name;

  • NULL values: IS NULL or IS NOT NULL

SELECT LastName,FirstName,Address FROM EMPLYEE WHERE Address IS NULL

Functions:

Char Function

  • LOWER(String)

    Convert upper String to lower

  • UPPER(String)

    Convert lower String to upper

  • INITCAP(String)

    Convert the first caracter to Upper and the other lower

  • CONCAT(String1,String2)

    Concat the first string with the second string

  • SUBSTR(String,pos[length])

    Extract a sub String from the String in parameter, from the position given in parameter

  • LPAD(String, length, char)

    the char can be * or any other char, LPAD serve to complete the String with the char in the left

  • RPAD(String, length,char)

    the char can be * or any other char, RPAD serve to complete the String with the char in the right

  • LTRIM(String,char)

    Delete the caracteres in the left

  • RTRIM(String,char)

    Delete the caracteres in the right

  • ASCII(String)

    return the ASCII code of the first caracter

Numeric function

  • ROUND(45.926,2)

    return 45.92

  • FLOOR(45.926)

    return 45

  • CEIL(45.926)

    return 46

  • GREATEST(4,-1,7)

    return 7

LEAST(4,-1,7)

return -1

Date function

  • SYSDATE or CURRENT_DATE

    return the date of your system

  • MONTHS_BETWEEN(d1,d2)

    return the number of months between d1 and d2

  • ADD_MONTHS(d,j)

    add j months to the date d

  • NEXT_DAY(d,j)

    return the next date of the day j for example: NEXT_DAY(‘01-SEP-95’,‘FRIDAY’) => 08-SEP-95

  • ROUND(d,p)

    ROUND(to_date(‘25-07-03’,‘MONTH’)) return 01-08-03

  • TRUNC(d,p)

    TRUNC(to_date(‘25-07-03’,‘MONTH’),‘MONTH’) return 01-07-03

  • EXTRACT(p FROM d)

    EXTRACT(day from to_date(‘25-07-03’)) return 25

  • TO_CHAR(date,format)

    convert date to the format in the parameter (for more details visit this link)

  • TO_DATE(String,format)

Join

is to combine rows from two or more tables

They are many others statements you can find them here