Computer Department, GGP, Abad Enrollment No.
_____________________________
Subject: Relational Database Management System
Code: 4330702 Date:
Lecture 1 (Unit 1 Introduction to Database System and SQL commands)
___________________________________________________________________________
Learning Outcomes:
At the end of this Lecture Students will be able to :
□ Demonstrate the use of different DBMS Data types,
□ Create Tables using (DDL) commands
What Is SQL?
SQL (pronounced “sequel”) is an acronym for Structured Query Language, a standardized
language used to access and manipulate data.
The history of SQL corresponds closely with the development of a relational databases
concept published in a paper by Dr. E. F. Codd at IBM in 1970.
Advantages of SQL :
SQL has many advantages which makes it popular and highly demanded. It is a reliable and
efficient language used for communicating with the database. Some advantages of SQL are as
follows:
1. Faster Query Processing –
Large amount of data is retrieved quickly and efficiently. Operations like Insertion,
deletion, manipulation of data is also done in almost no time.
2. No Coding Skills –
For data retrieval, large number of lines of code is not required. All basic keywords
such as SELECT, INSERT INTO, UPDATE, etc are used and also the syntactical
rules are not complex in SQL, which makes it a user-friendly language.
3. Standardized Language –
Due to documentation and long establishment over years, it provides a uniform
platform worldwide to all its users.
4. Portable –
It can be used in programs in PCs, server, laptops independent of any platform
(Operating System, etc). Also, it can be embedded with other applications as per
need/requirement/use.
5. Interactive Language –
Easy to learn and understand, answers to complex queries can be received in seconds.
6. Multiple data views –
1
Computer Department, GGP, Abad Enrollment No._____________________________
7. Scalability: SQL databases can handle large volumes of data and can be scaled up or
down as per the requirements of the application.
8. Security: SQL databases have built-in security features that help protect data from
unauthorized access, such as user authentication, encryption, and access control.
9. Data Integrity: SQL databases enforce data integrity by enforcing constraints such as
unique keys, primary keys, and foreign keys, which help prevent data duplication and
maintain data accuracy.
10. Backup and Recovery: SQL databases have built-in backup and recovery tools that
help recover data in case of system failures, crashes, or other disasters.
11. Data Consistency: SQL databases ensure consistency of data across multiple tables
through the use of transactions, which ensure that changes made to one table are
reflected in all related tables.
Commonly Used Data Types
DATE
The DATE data type stores date and time information. Depending on your setup, the default
display format for a date may be DD-MON-YY. For example, July 4, 2009, displays as
04-JUL-09. There are a number of functions you can use to change the display format or to
show the time.
NUMBER
Columns with the data type NUMBER allow only numeric data; no text, hyphens, or dashes
are permitted. A column defined as NUMBER(5,2) can have a maximum of three digits
before the decimal point and two digits after the decimal point. The first digit (5) is called the
precision; the second digit (2) is referred to as the scale. The smallest allowed number is –
999.99, and the largest is 999.99.
VARCHAR2 and CHAR
The VARCHAR2 and CHAR data types store alphanumeric data (for example, text, numbers,
special characters). VARCHAR2 is the variable-length data type and the most commonly
used alphanumeric data type; its maximum size is 4,000 characters. The main difference
between VARCHAR2 and CHAR is that the CHAR data type is a fixed-length data type, and
any unused room is blank padded with spaces.
Table I.1 Oracle’s Most Commonly Used Built-in Data Types
2
Computer Department, GGP, Abad Enrollment No._____________________________
3
Computer Department, GGP, Abad Enrollment No._____________________________
Overview of SQL Language Commands
You work with tables, rows, and columns using the SQL language. SQL allows you to query
data, create new data, modify existing data, and delete data. Within the SQL language you
can differentiate between individual sublanguages, which are a collection of individual
commands.
SQL allows you to create new database structures such as tables or modify existing ones; this
subcategory of SQL language commands is called the Data Definition Language (DDL).
CREATE TABLE command
You create tables by using the CREATE TABLE command, in either of two ways. The first
method is to specify the columns and their data types explicitly; the second method is to
create a table based on an existing table.
4
Computer Department, GGP, Abad Enrollment No._____________________________
The following statement creates a table called TOY that consists of four columns. A NOT
NULL constraint is specified for the DESCRIPTION column. The newly created table
contains no data.
Syntax:
Create table table name
( columnname_1 datatype(datasize), columnname_2 datatype(datasize),
……….
columnname_ndatatype(datasize) );
Dropping Tables
You can drop tables when they are no longer needed by using the DROP TABLE command,
whose syntax is a follows.
DROP TABLE tablename;
Drop Table toy;
ALTER TABLE command
After a table is created, you sometimes need to change its characteristics. The ALTER
TABLE command, in conjunction with the ADD, DROP, MODIFY, and RENAME clauses,
allows you to do this. You can add or delete a column; change the length, data type, or default
value of a column; or add, drop, enable, disable, or rename a table’s integrity constraints.
Adding Columns:
The following statement alters the TOY table to add a new column called
MANUFACTURER.
Syntax
Alter table tablename add (
new_columnname_1 datatype(datasize), new_columnname_2 datatype(datasize),
……….
new_columnname_ndatatype(datasize));
5
Computer Department, GGP, Abad Enrollment No._____________________________
Dropping Columns
You can drop columns from a table by using the ALTER TABLE command and the DROP
clause. The following statement drops the LAST_PURCHASE_DATE column from the TOY
table.
Syntax:
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;
Renaming Columns
You can rename an individual column with the following command.
ALTER TABLE toy RENAME COLUMN description TO
description_tx
Syntax:ALTER TABLE TABLENAME RENAME COLUMN OLD_COLUMNNAME TO
NEW_COLUMNNAME;
Modifying Columns
You modify the data type, length, and column default of existing columns with the ALTER
TABLE statement.
The following statement changes the length of the DESCRIPTION column from 15 to 25 characters.
The next statement modifies the data type of the REMAINING_QUANTITY column from NUMBER
to VARCHAR2 and makes the column not null simultaneously. This statement executes successfully
because the table contains no data.
The TRUNCATE Command
The TRUNCATE command deletes all rows from a table, just like the DELETE command. However,
the TRUNCATE command does not allow a WHERE clause and automatically issues a COMMIT. All
rows are deleted, and you cannot roll back the change.
TRUNCATE TABLE toy
Table truncated.
6
Computer Department, GGP, Abad Enrollment No._____________________________
Renaming Tables
You can rename tables by using the RENAME command. The syntax of the command is as follows.
RENAME oldname TO newname
The following statement renames the JAN_07_ENROLLMENT table JAN_07.
RENAME jan_07_enrollment TO jan_07
Table renamed.
Alternatively, you can execute the ALTER TABLE command,
ALTER TABLE jan_07_enrollment RENAME TO jan_07
Exercises
1. Explain the error(s) in the following CREATE TABLE statement and rewrite the
statement correctly.
2. Write and execute a CREATE TABLE statement to create an empty table called
NEW_STUDENT that contains the following columns: first name, last name, the
description of the first course the student takes, and the date the student registered in
the program. Determine the data type and length necessary for each column, based on
the tables in the STUDENT schema.
3. Alter the table called NEW_STUDENT that you created by adding four columns. The
columns should be called PHONE, NUM_COURSES with data type and length
NUMBER(3), CREATED_BY, and CREATED_DATE. Determine the other column
data types and lengths, based on the STUDENT table. The PHONE,
NUM_COURSES, and CREATED_BY columns should allow null values. The
CREATED_DATE column should not allow null values.
4. Execute the following INSERT statement to insert a row into the NEW_STUDENT
table.
Then alter the table to change the PHONE column from NULL to NOT NULL. What
do you observe?
5. Alter the NEW_STUDENT table to change the REGISTRATION_DATE column
from the DATE data type to the VARCHAR2 data type. What do you observe?
6. Remove the column description from NEW_STUDENT table.
7. Drop the table NEW_STUDENT.
