RDBMS

Creating a Table

Before creating a table, decide: what data it stores, its name (which should depict its content), its columns, the name, datatype and maximum length of each column, and the integrity rules to be implemented.

create table COURSES
( ccode         varchar2(5),
  name          varchar2(30),
  duration      number(3),
  fee           number(5),
  prerequisite  varchar2(100)
);

Table created.

Rules for naming tables and columns

• The name must begin with a letter (A–Z or a–z).
• Letters, digits and the special characters _ $ # are allowed.
• Maximum length is 30 characters.
• It must not be an SQL reserved word, and no other object in your account may have the same name.

Oracle Datatypes

DESCRIBE and INSERT

DESC[RIBE] objectname is a SQL*Plus command that shows each column’s name, datatype and whether it can hold NULL. It works for tables, views, synonyms, functions and procedures.

insert into courses
values ('ora','Oracle database',25,4500,'Knowledge of Windows');

insert into courses
values ('c','C Programming',25,3000,null);          -- NULL for an unknown value

insert into courses (ccode,name)
values ('odba','Oracle Database Administration');  -- other columns get NULL / default

commit;                                              -- make the changes permanent

• Character and date values are enclosed in single quotes.
• One INSERT ... VALUES inserts one row; repeat it for more rows (or use a subquery – Chapter 11).
• DATE values are given as DD-MON-YY or DD-MON-YYYY. With a two-digit year the current century is used, so give four digits when needed.
• NULL means a value that is not available or not known – it is neither 0 nor blank.
• Issue COMMIT after inserting; uncommitted rows may be lost, e.g. on power failure.

SELECT: Projection, Expressions and Aliases

select name, fee, fee * 0.15 discount
from courses;

NAME                  FEE   DISCOUNT
-------------------- ----- ---------
Oracle database       4500       675
VB.NET                5500       825
C programming         3500       525
ASP.NET               5000       750
Java Language         4500       675
XML Programming       4000       600

• Projection – selecting only some of the columns. * selects all columns.
• Arithmetic operators + − * / may be used in expressions.
• A column alias replaces the expression as the column heading.
• A schema prefix (owner name) is needed only for tables of another account.

ORDER BY

• Sorts the retrieved rows; the rows stored in the table are not changed.
• Default order is ascending; use DESC for descending. Several columns may be given.
• You can sort by column name, by column position in the select list (not in the table) or by alias.
• ORDER BY must be the last clause of SELECT.
• NULLs come last in ascending order and first in descending order.

select name, duration, fee from courses
order by duration, fee desc;

select name, fee, fee * 0.15 from courses
order by 3;

Selection with WHERE and SQL Operators

The WHERE clause implements the selection operator – only rows satisfying the condition are returned.

select name from courses where duration between 20 and 25;
select name from courses where duration in (20,30);
select * from courses where ccode like '_b%' and prerequisite like '%programming%';
select * from courses where fee is null;      -- correct
select * from courses where fee = null;       -- never true: use IS NULL

SELECT DISTINCT fee FROM courses; returns each fee value only once.

Editing the SQL Buffer

The last SQL command entered is kept in the SQL buffer. SQL*Plus commands (such as DESCRIBE) are not stored in the buffer.

Chapter 3 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 3 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
3. Oracle Corporation. (n.d.). SQL*Plus User’s Guide and Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/
4. Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
5. Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.