Preface | The main commands
| Some different types | Different sorts of constraints
| About keys | Using References (Linked tables)
-----------------
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