SQL Commands and Data Types || SQL Basics - DBMS Tutorial 3

SQL Commands and Data Types

DBMS Tutorial 3

SQL Basics

SQL Commands:

Structured Query Language (SQL) is used to manage data in all relational databases like DB2, Oracle, SQL Server, etc. SQL standards are maintained by ISO. While most database products comply with the ISO standard, they also offer additional proprietary features. In this course, we will restrict ourselves to feature set offered by the Oracle database.

Data Definition Language (DDL):

Data Definition Language (DDL) is used to specify the structure i.e. schema of a relational database. DDL provides commands for creation, modification, and deletion of various database objects like tables, views, stored procedures, indexes, constraints, etc. The output of DDL is placed in a data dictionary which contains metadata i.e. data about data.

Data Manipulation Language (DML):

Data Manipulation Language (DML) enables users to access or manipulate data in a relational database. DML provides commands for retrieval, creation, deletion, and modification of information in a database. DML requires a user to specify what data are needed without specifying how to get it. The database engine is left to figure out effective means of retrieving data.

Data Control Language (DCL):

Data Control Language (DCL) enables users to provide access to various database objects like views, tables, stored procedures, etc. in a relational database. Typically only DBAs have access to grant and revoke privileges. Whenever a user submits a query, the database checks against the granted privileges and rejects the query if it is not authorized.

Transaction Control Language (TCL):

Transaction Control Language (TCL) specifies commands for beginning and ending a transaction. A transaction consists of a sequence of SQL statements that are applied in an atomic (all or none) manner. A commit makes all the changes applied by the transaction permanent in the database while a rollback undoes all the changed applied by the transaction.

To summarize, Structured Query Language (SQL) provides four types of languages based on the type of operation to be performed on a database. These languages can be considered as subsets of SQL and logical groups only. Physically commands for all these languages are executed from the same interface provided by the database.

Quiz 41: Which of the following are classifications of SQL?

a) Data Definition Language ---
b) Data Manipulation Language ---
c) Extensible Markup Language
d) Security Assertion Markup Language

Quiz 42: Determine the appropriate command classification for each of the following commands.

UPDATE - Used to change existing data in tables

a) DDL
b) DML ---
c) DCL
d) TCL

ALTER - Modifies existing database objects like tables.

a) DDL ---
b) DML
c) DCL
d) TCL

REVOKE - Remove access to database objects like tables from users

a) DDL
b) DML
c) DCL ---
d) TCL

COMMIT - Save database changes and end transaction

a) DDL
b) DML
c) DCL
d) TCL ---

INSERT - Add new rows to database tables

a) DDL
b) DML ---
c) DCL
d) TCL

TRUNCATE - Empty a table by deleting all rows

a) DDL ---
b) DML
c) DCL
d) TCL

SQL Character Data Types:

SQL supports two character data types for storing printable and displayable characters. They are used for storing information like name, address, description, etc.

	CHAR(n)	VARCHAR2(n)
Useful for	Storing characters having pre-determined length	Storing characters whose length vary a lot
Storage size	size for n characters	size for actual no. of characters + fixed size to store length
Storage Characteristic	Trailing spaces are applied if data to be stored has a smaller length than n.	Trailing spaces are not applied.
Maximum size	2000 bytes	4000 bytes
Example	A CHAR(10) field will store "Hello" as 10 bytes by appending 5 trailing spaces.	A VARCHAR2(10) field will store "Hello" as 7 bytes (assuming 2 bytes to store length).
Alternate Name	CHARACTER(n)	CHARACTER VARYING(n)

SQL Integral Data Types:

SQL supports SMALLINT, INTEGER and INT data types that are used for storing whole numbers. Unlike other databases, Oracle does not define different size limits for them. They are all treated internally to have 38 digits of precision. Some real-life examples of values are provided below:

Example	Value
Height of Mount Everest in meters	8848
Length of the Great Wall of China in meters	885000
The average distance of Earth from the Sun in meters	150000000000

SQL, unlike programming languages, does not provide support for arbitrary length numbers i.e. numbers not bound by size limits. For e.g. Python supports bignum and Java supports BigInteger data types.

SQL Nonintegral Data Types:

Nonintegral data types have an integer part and a fractional part. Either NUMERIC, DECIMAL or NUMBER data types can be used to store nonintegral numbers.

The scale is the number of digits allowed after the decimal point. Precision is the total number of significant digits i.e. digits both before and after the decimal point. If Scale is not provided then NUMBER datatype can be used to store integral values.

Precision and Scale of non-integral data type determines the maximum value that can be stored. It also determines how to input data will be modified before storing the value. We can use the following logic to determine the value that will be stored:

If No_of_Digits_after_Decimal_In_Input > DataType_Scale
Modified_Input = Round(Input, DataType_Scale)
Else
Modified_Input = Input
End If

If No_of_Digits_before_Decimal_In_Modified_Input > DataType_Precision – DataType_Scale
Raise Error
Else
Stored Value = Modified_Input
End if

Let us see some examples of how to input data is changed before storage.

Input	Data Type	Stored Value	Explanation
121.79	NUMBER	121.79	Since no precision and scale is specified, this can store any decimal number with 38 significant digits. The number is stored without any changes.
121.79	NUMBER(3)	122	Scale defaults to zero if precision is specified but the scale is not. Hence the number is rounded to zero decimal places.
121.79	NUMBER(5,2)	121.79	Here 5 - 2 = 3 digits are allowed before the decimal point and 2 digits are allowed after the decimal point. Since number conforms to this rule, it is stored without any changes.
121.79	NUMBER(6,1)	121.8	Only 1 digit is allowed after the decimal point so the number is rounded to 1 decimal place.
121.79	NUMBER(4,2)	error	Only 4 - 2 = 2 digits are allowed before decimal point while the number has three digits. This is an error scenario as truncation will result in incorrect value getting stored.

Quiz: Data Types

Quiz 43: Choose a data type to store the weight of a person in kilograms with an accuracy of 1 gram. As per Wikipedia, Jon Brower Minnoch was the heaviest human ever recorded with a weight of 635kg.

a) NUMBER(3,3)
b) NUMBER(3,2)
c) NUMBER(6,2)
d) NUMBER(6,3) ---

Quiz 44: What is the maximum value that can be stored in NUMERIC(4,2)?

a) 9999.99
b) 99.9999
c) 99.99 ---
d) 9.99

Quiz 45: Which of the following statements are TRUE?

a) Scale denotes the number of digits allowed after decimal point ---
b) Precision denotes the number of digits allowed before the decimal point
c) SQL performs rounding if the user attempts to store a value that has higher scale than the data type ---
d) SQL performs truncation if the user attempts to store a value that has higher precision than the data type

Miscellaneous Data Types:

SQL supports the following data types for representing date and large objects:

Data Type	Useful for
DATE	Storing date data where the time portion is not required. For e.g. Date of Birth, Date of Joining a Company, etc. The default format in which data needs to be specified is DD-MON-YY.
TIMESTAMP	Storing date data with precision up-to 1 billionth (9 digits) of a second. Timestamps are typically used as audit fields in the database to record the exact time when a transaction occurred.
CLOB (Character Large Object)	Storing large character-based data which cannot be stored in VARCHAR2 due to its 4000 bytes size limit.
BLOB (Binary Large Object)	Storing large binary data like movies, images with size up to 4GB.

Here are some key events and their dates from history:

Data Type	Example	Value
DATE	Date Infosys was founded	02-JUL-81
TIMESTAMP	Date Apollo 11 landed on the moon	20-JUL-69 08:18:00.000000 PM

Quiz 46: Determine the data type for these columns. Each data type must be used only once. Choose the most suitable data type in case multiple data types are possible for any column.

Column Name --> Description --> Example

Price --> Cost of an item in rupees and paise --> 200.21

a) BLOB
b) CHAR(1)
c) CHAR(11)
d) DATE
e) INTEGER
f) NUMBER(5, 2) ---
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50)

IFSC_Code --> A 11 Character alphanumeric code that identifies a bank branch --> SBIN0009044

a) BLOB
b) CHAR(1)
c) CHAR(11) ---
d) DATE
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50)

Meeting_Time --> Time and date of the meeting --> 2014/01/01 10:00 AM

a) BLOB
b) CHAR(1)
c) CHAR(11)
d) DATE
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP ---
i) VARCHAR2(50)

Profile_Image --> Image of the employee

a) BLOB ---
b) CHAR(1)
c) CHAR(11)
d) DATE
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50)

Book_Title --> A text string --> Winning

a) BLOB
b) CHAR(1)
c) CHAR(11)
d) DATE
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50) ---

Gender --> A single character gender code, M or F --> M

a) BLOB
b) CHAR(1) ---
c) CHAR(11)
d) DATE
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50)

PIN_Code --> Six digit numeric PIN code for any address in India --> 560100

a) BLOB
b) CHAR(1)
c) CHAR(11)
d) DATE
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6) ---
h) TIMESTAMP
i) VARCHAR2(50)

Employee_Id --> Unique number assigned to every employee --> 100000

a) BLOB
b) CHAR(1)
c) CHAR(11)
d) DATE
e) INTEGER ---
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50)

Date_Of_Birth --> Date of Birth of the employee --> 1990/01/01

a) BLOB
b) CHAR(1
c) CHAR(11)
d) DATE ---
e) INTEGER
f) NUMBER(5, 2)
g) NUMBER(6)
h) TIMESTAMP
i) VARCHAR2(50)