-----------------
1. Little preface:
----------------
In 1975,  SQUARE appeared. SEQUEL2 had been derived from this 
first version during the 1970s. In 1982, IBM  proposed a new version. 
Now, We deal with Access of MS and SQL from Oracle.

A database is a set of tables. In a relational database, the tables 
are linked by a field (column). Extracting information from this set 
of tables ( database) is done by queries under command lines on the 
prompt.The language of a database is SQL (ronounced es-kew-el or sequel 
(sikwel). It comes from Oracle company. SQL is the standard "language"used 
to investigate databases. It stands for Structured Query Language.The well 
known databases are Access (database Microsoft), MySQL (database SQL), and 
Oracle, all of them use SQL sever. 
(The orders below witout examples have to be tested.)


---------------------
2. The main commands: 
--------------------

ORDER 0: CONNECT
--------
SQL> connect
Enter user-name: System
Enter password:
Connected.


ORDER 1: CREATE TABLE:
----------------------
SQL> CREATE TABLE table name
(column name column type [attribute constraint]
{, column name column type [attribute constraints] }
[table constraint {,table constraint}]);
or:
SQL> CREATE TABLE Table-Name(
Col-Name1 Col-Type1 NOT NULL,
Col-Name2 Col-Type2 NOT NULL,
... );
Where :
Table-Name is the name of the table to create.
Col-Namei is the name of the " i "column in the table, and
Col-Typei its type (for instance numbre, varchar, ...)
NOT NULL mentions that that the cell will not be null.
We have parenthesis after Table-Name until the end. 
Do comma after every column creation.
end the commande with semi-colon ";". 
Example:
SQL>CREATE TABLE Students(
StudentID NUMBER(5,0) NOT NULL ,
CourseNumber VARCHAR2(15) NOT NULL ,
CourseName VARCHAR2(25) ,
Semester VARCHAR2(10) ,
Year NUMBER(4,0),
Grade VARCHAR2(2));

ORDER 2: INSERT:
----------------
SQL> INSERT INTO table name [( column name{, column name} ) ]
(VALUES ( constant value , { constant value} ){,(constant value{,constant value})}
| select statement);

or:
SQL> INSERT INTO
TABLE(FIELD1, FIELD2, FIELD3, ..., FIELDi, ...)
VALUES(VALUE1, VALUE2, VALUE3, ..., VALUEi, ...);
Where :
TABLE is the name of the table where to insert values.
FIELDi is the field i used in the creation of the table. That is the Col_Namei 
(the name of the column i)
VALUEi is the value to give to the field i.
Example:
SQL>INSERT INTO Students VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+'); 
INSERT INTO Students VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-'); 
INSERT INTO Students VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A'); 
INSERT INTO Students VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-'); 
INSERT INTO Students VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B'); 
INSERT INTO StudentsVALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+'); 
INSERT INTO Students VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A'); 
INSERT INTO Students VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A'); 
INSERT INTO StudentsVALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');


ORDER 3: SELECT:
----------------
SQL> SELECT attribute list FROM table name;
Example:
SQL> select * from Students;

 STUDENTID COURSENUMBER    COURSENAME                SEMESTER         YEAR GR
---------- --------------- ------------------------- ---------- ---------- --
       101 CIS3100         OOP I                     SPRING           1999 A-
       101 MKT3000         Marketing                 FALL             1997 A
       102 CIS3400         DBMS I                    SPRING           1997 A-
       102 CIS3500         Network I                 SUMMER           1997 B
       103 MKT3100         Advertizing               SPRING           1998 A
       103 MKT3000         Marketing                 FALL             1997 A
       101 CIS3400         DBMS I                    FALL             1997 B+
       101 CIS3100         OOP I                     SPRING           1999 A-
       101 MKT3000         Marketing                 FALL             1997 A
       102 CIS3400         DBMS I                    SPRING           1997 A-
       102 CIS3500         Network I                 SUMMER           1997 B

 STUDENTID COURSENUMBER    COURSENAME                SEMESTER         YEAR GR
---------- --------------- ------------------------- ---------- ---------- --
       103 MKT3100         Advertizing               SPRING           1998 A
       103 MKT3000         Marketing                 FALL             1997 A
13 rows selected.

                                                                                  top
ORDER 4: DESCRIBE:
-----------------
describe (or desc) TABLE;
-------------------------
Example:
SQL> describe Students;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 STUDENTID                                 NOT NULL NUMBER(5)
 COURSENUMBER                              NOT NULL VARCHAR2(15)
 COURSENAME                                         VARCHAR2(25)
 SEMESTER                                           VARCHAR2(10)
 YEAR                                               NUMBER(4)
 GRADE                                              VARCHAR2(2)

ORDER 5 : DROP TABLE:
----------------------
SQL> DROP TABLE table name;
Example:
SQL> create table PROJECT (
  2  PNO number(3),
  3  PNAME varchar2(60) unique,
  4  PMGR number(4) not null,
  5  PERSONS number(5),
  6  BUDGET number(8,2) not null,
  7  PSTART date,
  8  PEND date);

Table created.
SQL> drop table PROJECT;
Table dropped.


ORDER 6 : ALTER and ADD COLUMN:
-------------------------------
SQL> ALTER TABLE table name ADD column name column type;


ORDER 7: DISTINCT:
------------------
SQL> SELECT [DISTINCT] attribute list
FROM (table name { } | joined table) {, (table name { } | joined table) }
[WHERE ]
[GROUP BY grouping attributes [HAVING group selection condition ] ]
[ORDER BY column name [order] {, column name [order] } ];

The difference between ALL and DISTINCT is in shown in this example:
SQL> SELECT ALL STUDENTID FROM STUDENTS;

 STUDENTID
----------
       101
       101
       102
       102
       103
       103
       101
       101
       101
       102
       102

 STUDENTID
----------
       103
       103
13 rows selected.

Every row is selected from the column "STUDENTID", No matter the repetition.
                                                                                  top
SQL> SELECT DISTINCT STUDENTID FROM STUDENTS;
 STUDENTID
----------
       102
       101
       103
SQL>
In this case, we have no repetition.



ORDER 8: DELETE:
---------------
SQL> DELETE FROM table name
[WHERE selection condition];


ORDER 9: UPDATE:
----------------
It offen happens to change values in cells of tables, to add rows, 
to delete rows, that is to update tables. The command to update a table 
is the folowing:
SQL> UPDATE table name
SET column name=value expression { , column name=value expression }
[WHERE selection condition];
or:
SQL> UPDATE Table_Name
SET Col-Namei = NEW_VALUE
WHERE Col_Namej = ITS_VALUE;

Where :
Col-Namei is the column i ( or field i)
Col-Namej is the column j ( or field j)
VALUEi is the value to give to the field i.
NEW_VALUE is the new value to set in the cell.
ITS_VALUE is the value in the Col_Namej and in the row where 
to find the cell to change.
Note that the second clause whith "WHERE" plays just a role of 
referee.
end the commande with semi-colon ";".
Example:

SQL> WHERE COURSENAME = 'Advertizing';
SP2-0734: unknown command beginning "WHERE COUR..." - rest of line ignored.
SQL> update Students
  2  set STUDENTID = 104
  3  WHERE COURSENAME = 'Advertizing';

2 rows updated.
To verify:
SQL> select * from Students;
 STUDENTID COURSENUMBER    COURSENAME                SEMESTER         YEAR GR
---------- --------------- ------------------------- ---------- ---------- --
       101 CIS3100         OOP I                     SPRING           1999 A-
       101 MKT3000         Marketing                 FALL             1997 A
       102 CIS3400         DBMS I                    SPRING           1997 A-
       102 CIS3500         Network I                 SUMMER           1997 B
       104 MKT3100         Advertizing               SPRING           1998 A
       103 MKT3000         Marketing                 FALL             1997 A
       101 CIS3400         DBMS I                    FALL             1997 B+
       101 CIS3100         OOP I                     SPRING           1999 A-
       101 MKT3000         Marketing                 FALL             1997 A
       102 CIS3400         DBMS I                    SPRING           1997 A-
       102 CIS3500         Network I                 SUMMER           1997 B

 STUDENTID COURSENUMBER    COURSENAME                SEMESTER         YEAR GR
---------- --------------- ------------------------- ---------- ---------- --
       104 MKT3100         Advertizing               SPRING           1998 A
       103 MKT3000         Marketing                 FALL             1997 A

13 rows selected.
SQL>
                                                                                  top

ORDER 10:
---------
Using GROUP BY and HAVING

Example 1:
Count the number of the professors and print one of them for each level.
For each = Having
SQL> select column_i, count(*) from Table group by column_i;
Will print the table of Students grouped by column_i and the number of 
students for each column_i 
Example:
SQL> select SEMESTER, count(*) from Students group by SEMESTER;
SEMESTER     COUNT(*)
---------- ----------
SPRING              6
SUMMER              2
FALL                5
SQL>

SQL> select COURSENAME, count(*) from Students group by 
COURSENAME having COURSENAME = 'OOP I';
COURSENAME                  COUNT(*)
------------------------- ----------
OOP I                              2
SQL>


Example 2:

SQL> select STUDENTID, count(*) from Students group by STUDENTID having 
STUDENTID between 101 and 103;

 STUDENTID   COUNT(*)
---------- ----------
       102          4
       101          5
       103          4
SQL>


	  
	 
ORDER 11: CREATE [UNIQUE] INDEX:
--------------------------------
SQL> CREATE [UNIQUE] INDEX index name
ON table name ( column name [ order ] { , column name [ order ] } );


ORDER 12: DROP INDEX:
----------------------
SQL> DROP INDEX index name;


ORDER 13: CREATE VIEW:
----------------------
SQL> CREATE VIEW view name [ ( column name { , column name } ) ]
AS select statement;

ORDER 14: DROP VIEW:
--------------------
SQL> DROP VIEW view name;

                                                                                  top
ORDER 15: DISTROY A ROW:
-----------------------
SQL> DELETE FROM table name
WHERE [ condition ];


---------------
3. Some different types: 
--------------

CHARACTER( n), CHAR( n) CHAR( n)
CHARACTER VARYING( n), CHAR VARYING( n) VARCHAR( n)
NUMERIC( p, s), DECIMAL( p, s), DEC( p, s)[ 1] NUMBER( p, s)
INTEGER, INT, SMALLINT NUMBER( 38)
FLOAT( b)[ 2], DOUBLE PRECISION[ 3], REAL[ 4] NUMBER

We can create types of domains and drop like this:
- Create:
CREATE TYPE books CHAR (6) DEFAULT ’The Problems of Philosophy’
CONSTRAINT book_value
CHECK (VALUE IN ’The ousider’, ’How do I see the world’, ’The Problems of Philosophy’);
- Drop:
DROP TYPE domaine [RESTRICT | CASCADE]
DROP TYPE book_valide
DROP TYPE book_valide CASCADE



----------------------------------
4. Different sorts of constraints: 
----------------------------------
– NOT NULL
– UNIQUE ou PRIMARY KEY
– FOREIGN KEY
– REFERENCES
– CHECK



--------------
5. About keys: 
--------------
	5.1. Primary key:
How to establish a primary key?
A primary key is the key that is unique inside each row of 
its field : no repetition. It's the main attribute that we can use 
outside its table to refer to this table. A primary key is not null; 
but a unique key could be null.
Example:
Table1: 
Primary key = String1

Create table Table1
(String1 char(30) not null,
Number1 integer,
Number11 number(digit),
constraint pk_Table1 primary key (String1));

The field String1 must be not null in order to 
become a primary key.

                                                                                  top
	5.2. Foreign key:
A foreign key inside a table is specified as such because the same 
key is already set as a primary key outside this table.
Example:
Table2: 
Primary key = Number2, 
Foreign key = String1 = Primary key in Table1

Create table Table2
(String2 char(30) ,
String1 char(30) not null,
Number2 integer not null,
Number22 number(digit) ,
constraint pk_Table2 primary key (Number2));
constraint fk_Table2 foreign key (String1) references Table1 (String1));

The order:
SQL> alter table Table2  add constraint kf_Table2 foreign key(String1) 
references Table1(String1) on delete cascade;
means : 
Go to the table Table2 and alter it by adding that the String1 is a foreign key where 
it is a primary key in the Table1 table. The term "on delete cascade" means that the 
command has to be executed across the cascade (in all the table where The attribute 
String1 exists.)


------------------------------------
6. Using References (Linked tables): 
------------------------------------

CREATE TABLE Students
Laboratory VARCHAR (23) NOT NULL ,
ID INT ,
Course CHAR (9) DEFAULT ’Java Programming’,
Level CHAR(8) NOT NULL,
Thisdate DATE ,
CONSTRAINT ParameterO PRIMARY KEY (Office),
CONSTRAINT ParameterP UNIQUE (Profession)
CONSTRAINT ParameterL FOREIGN KEY (Level)
REFERENCES OtherTable( Course) ON DELETE CASCADE );

-- where Course is the Primary key in the OtherTable table.

Alter a table:

ALTER TABLE Table_name ADD attribute type, attribute type , ...
ALTER TABLE Table_name  ADD DATE_ CREATION DATE ;
ALTER TABLE OtherTable_name  DROP Level CASCADE ;
ALTER TABLE Table_name  DROP CONSTRAINT ParameterC CASCADE;

Drop a table:

DROP TABLE Table_name  ;
DROP TABLE Table_name  CASCADE;
DROP TABLE Table_name CASCADE CONSTRAINT;

                                                                             top