Chapter Two
The basic object at the relational DBMS is?
The tables.
[Link]
[Link]
live-sql/
[Link]
Ex: Which one of the following is a correct Oracle name and why?
1xrt
X%23a
E_error
T##_$
Var23@
OYT#_#
&R5567W
#exam
1xrt No (start with number)
X%23a No (Contains the character %)
E_error Yes
T##_$ Yes
Var23@ No (Contains the character @)
OYT#_# Yes
&R5567W No (Not start by letter and contains the character &)
#exam No (start with #)
Ex: is the following is an accepted oracle object name or not?
FFF12$^# No (Contains the character ^)
DBA (Data Base Administrator): He/she is the person that control
and manage the data base management system.
CREATE USER command is a DDL command or DML command?
Ex: Write a SQL command to create a user 201916030 and the
password is the same name.
CREATE USER 201916030 IDENTIFIED BY 201916030;
Ex: Write a SQL command to create a user Mylab and the
password is “eerr12345”.
CREATE USER Mylab IDENTIFIED BY eerr12345;
Ex: Write a SQL command to grant the user Mylab the following
privileges.
CREATE SESSION
CREATE TABLE
DROP TABLE
UNLIMITED TABLESPACE
GRANT CREATE SESSION, CREATE TABLE, DROP TABLE,
UNLIMITED TABLESPACE
TO Mylab;
Ex: Write a SQL command to grant the user 201816123 the
following privileges.
CREATE SESSION
CREATE TABLE
GRANT CREATE SESSION, CREATE TABLE TO 201816123;
Ex: Write a SQL command to create a role called myrole.
CREATE ROLE myrole;
Ex: Write a SQL command to grant the role myrole the following
privileges.
CREATE SESSION, CREATE TABLE.
GRANT CREATE SESSION, CREATE TABLE TO myrole;
Ex: Write a SQL command to grant the user 201816123 the role
myrole.
GRANT myrole TO 201816123;
The Grant command can be used to do the following:
Grand set of privileges to user.
Grand set of privileges to role.
Grand role to user.
Ex: Write a SQL command to revoke following privileges from user
the 201816123.
CREATE SESSION and CREATE TABLE
REVOKE CREATE SESSION , CREATE TABLE FROM 201816123;
Ex: Write a SQL command to grant the user 201816123 the
following privileges with administrator abilities.
CREATE SESSION
CREATE TABLE
GRANT CREATE SESSION, CREATE TABLE TO 201816123 WITH
ADMIN OPTION;
Variables and fields define format:
Fieldname TypeName(Size)
Ex: Define field called Bookname of typeVARCHAR2 with 20 characters length.
Bookname VARCHAR2(20)
Ex: Define field called Specialization of type CHAR with 3 characters length.
Specialization CHAR(3)
Char data type use ASCII(8-bits) code while the Nchar use the Unicode(16-bits).
Ex: Define field called Arabicname for multi language purpose 30 characters
length.
Arabicname NCHAR(30)
Ex: define the following fields:
F1 of length 20 characters accept variable length strings.
F2 of length 5 characters accept space.
F3 of length 30 characters accept any language.
F1 varchar2(20)
F2 char(5)
F3 nchar(30)
Ex: Define field called StudentID of type number with 9 integer digits length.
StudentID Number(9)
Ex: Define field called Salary of type number with 4 integers and 2 decimal digits
length.
Salary Number(6,2)
Ex: Define field called BirthD of type date.
BirthD Date
Binary Large Object (BLOB): Stores up to 4 GB of binary (any type of data
numbers image...) data.
Ex: define the field m_language_notes for storing multi-language text files.
m_language_notes NCLOB
EX: define the field audio_Sound that contains reference to audio files.
audio_Sound BFILE
Ex: create a table for the books object with the following fields:
B_id (book id number)
B_name( the book title)
B_author( the author name)
B_d( printing date)
B_p (book price)
CREATE TABLE books
( B_id NUMBER(6),
B_name VARCHAR2(30),
B_author VaRCHAR2(30),
B_d DATE, B_p number(6,2));
DESCRIBE books;
Ex: create a table for the OCMT students object with the following fields:
sid ( integer number of length 9)
fname( string of length 20)
sname( string of length 25)
Bd ( birth date)
GPA( fixed point 3 integers and two decimal)
Create table ocmt_students
(sid number(9),
fname varchar2(20),
sname varchar2(25),
Bd date,
GPA number(5,2));
DESCRIBE ocmt_students;
EX: Give the standard foreign key constraint name for the field advisor at the
students table.
Students_ advisor_fk
EX: Give the standard not null constraint name for the field price at the items table.
Items_price_nn
Ex: Define a table for the students’ object with following fields:
Sid number of length 9 (primary key)
Sname varchar2 of length 20
Create table students
( sid number(9) CONSTRAINT student_sid_pk PRIMARY KEY,
Sname varchar2(20));
DESCRIBE students;
Create table students1
( sid number(9) PRIMARY KEY,
Sname varchar2(20));
DESCRIBE students1;
Ex: Define a table for the cars object with following fields:
Cno number of length 5 (primary key)
Symbol char of length 1
OwnerId number of length 9
Ownername varchar2 of length 20
Create table cars
( cno number(5),
symbol char(1),
ownerid number(9),
ownername varchar2(20) ,
CONSTRAINT carscnopk PRIMARY KEY(cno));
DESCRIBE cars;
Create table mycars
( cno number(5),
symbol char(1),
ownerid number(9),
ownername varchar2(20) ,
PRIMARY KEY(cno));
DESCRIBE mycars;
Ex: Define a table for the cars object with following fields:
Cno number of length 5 (primary key)
Symbol char of length 2 (primary key)
OwnerId number of length 9
Ownername varchar2 of length 20
Create table cars1
( cno number(5),
symbol char(2),
ownerid number(9),
ownername varchar2(20) ,
CONSTRAINT carscnopk1 PRIMARY KEY(cno, symbol));
DESCRIBE cars1;
Create table cars2
( cno number(5),
symbol char(2),
ownerid number(9),
ownername varchar2(20) ,
PRIMARY KEY(cno, symbol));
DESCRIBE cars2;
Ex: Define a table for the Advisors object with following fields:
Profid number of length 4 (primary key)
sid number of length 9 (primary key)
profname varchar2 of length 20
sname varchar2 of length 20
Creat table Advisors
( profid number(4), sid number(9), profname varchar2(20), sname varchar2(20) ,
CONSTRAINT adconpk PRIMARY KEY(profid, sid));
Ex: Define two tables for the owners and cars objects with following fields:
Owners table:
Ownerid number(9) (primary key)
Ownername varchar2(20)
Phone number(8)
Cars table:
Cno number of length 5 (primary key)
Symbol char of length 1 (primary key)
Ownerid number of length 9 (foreign key from the owners table)
Create table owners
( ownerid number(9) PRIMARY KEY,
ownername varchar2(20) ,
phone number(8));
DESCRIBE owners;
Create table cars3
( cno number(5),
symbol char(2),
ownerid number(9) REFERENCES owners (ownerid),
PRIMARY KEY(cno, symbol));
DESCRIBE cars3;
Ex: Define two tables for the students and specializations objects with following
fields:
Specializations table:
Spid number(3) (primary key)
Spname varchar2(20)
Noy number(1)
Students table:
Sid number of length 9 (primary key)
Sname varchar2 of length 20
Spid (foreign key from specializations table)
Create table Specializations (spid number(3) CONSTRAINT sppk PRIMARY
KEY, Spname varchar2(20), Noy number(1));
DESCRIBE Specializations;
Create table students (sid number(9) CONSTRAINT studentsidpk PRIMARY
KEY, Sname varchar2(20), Spid number(3) CONSTRAINT studentsfk
REFERENCES Specializations (spid));
DESCRIBE students;
Ex: create a table for the OCMT students object with the following fields:
sid ( integer number of length 9) primary key
sname( string of length 20) Not null
Bd ( birth date) Not null
GPA( fixed point 3 integers and two decimal)
Sp char(2) check with the following values: CS,MS,AC, and BA.
Create table ocmtstudents1 (
sid number(9) PRIMARY KEY,
sname varchar2(20) Not Null,
Bd date Not Null,
GPA number(5,2),
Sp char(3) CHECK ((sp = 'CS') OR (sp = 'MS') OR (sp = 'AC') OR (sp = 'BA')));
DESCRIBE ocmtstudents1;
Ex: create a table for the OCMT students object with the following fields:
sid ( integer number of length 9) primary key
sname( string of length 20) Not null
Bd ( birth date) Not null
GPA( fixed point 3 integers and two decimal with default value 35)
Sp char(2) check with the following values: CS,MS,AC, and BA.
Create table ocmt_students (
sid number(9) PRIMARY KEY,
sname varchar2(20) Not Null,
Bd date Not Null,
GPA number(5,2) default 35,
Sp char(3) CHECK ((sp = 'CS') OR (sp = 'MS') OR (sp = 'AC') OR (sp = 'BA')));
DESCRIBE ocmt_students;
Ex: create a table for the OCMT students object with the following fields:
sid ( integer number of length 9) primary key
sname( string of length 20) Unique
Bd ( birth date) Not null
GPA( fixed point 3 integers and two decimal with default value 35)
Sp char(2) check with the following values: CS,MS,AC, and BA.
Create table ocmt1_students (
sid number(9) PRIMARY KEY,
sname varchar2(20) UNIQUE,
Bd date Not NULL,
GPA number(5,2) default 35,
Sp char(2) CHECK ((sp = 'CS') OR (sp = 'MS') OR (sp = 'AC') OR (sp = 'BA')));
DESCRIBE ocmt1_students;
Ex: Show the output of the command according to the above table:
DESCRIBE ocmt_students;
Name Null? Type
sid Not Null number(9)
sname varchar2(20)
Bd Not Null Date
GPA number(5,2)
Sp char(2)