Database Management (Unit-3) | 3341605
Unit – III: Structure Query Language (Marks-22)
SQL – “Structured Query Language”, which is a computer language for storing, manipulating and
retrieving data stored in a relational database (RDBMS). (Developed by IBM in 1970)
SQL software (RDBMS) like Oracle, MySQL, Microsoft SQL Server, Microsoft Access
SQL is Case insensitive language means do not difference between uppercase and lowercase such as
both SELECT and select are same.
List data types used in DBMS 02 – Summer-2022, 2021, 2019, 2018, Winter-2020
SQL Data types
These data types can be divided into different categories as given below:
1. Numerical Data type
2. Character/String Data type
3. Date Data type
4. Binary Data type
1. Numerical Data type: Used to store zero, negative and positive numerical values. These values can
be fixed-point or floating-point.
Storage Range: Precision range(p): 1 to 38 and Scale range(s): -84 to 127
Syntax: number (p, s) / number (p) / number / int
o Where, p is the precision (maximum no. of digits in a no.) and s is the scale (no. of digit to right
of decimal point)
o For example, number (5) = 12345 and number(5,2) = 432.12
2. Character/String Data type:
SR.
CHAR VARCHAR/VARCHAR2 LONG
NO.
CHAR should be used to store VARCHAR2 is used to store Variable-length strings.
1
Fixed-length character strings. Fixed- length character strings. (backward compatible)
Values are padded with space
Values are not padded with any Values are not padded with
2 characters to match the
characters. any characters.
specified length.
3 Maximum size of 255 bytes. Maximum size of 2000 bytes. Maximum size of 2GB.
Syntax: varchar(size) /
4 Syntax: char(size) Syntax: Long
varchar2(size)
Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
Department of Information Technology [Ms. Nikita A. Patel] 1
, Database Management (Unit-3) | 3341605
3. Date Data type: Used to store date and time.
The standard format is ‘DD-MON-YY’ to store date. Ex, ’21-JUL-2018’
The current date and time can be retrieved using function SYSDATE.
4. Binary Data type: Used to store binary type data like song, video and image.
Sr. No. Raw Long Raw
1 Stores binary type data. Stores large amount of binary type data.
2 Maximum size of 2000 bytes. Maximum size of 2GB.
Give types of SQL commands 02 – Summer-2021, Winter-2020
SQL Components:
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
Explain DDL commands with examples 04 – Winter-2021, 2019, 2017, Summer-2016
Data Definition Language (DDL) Commands- It is a set of SQL Commands used to create,
modify and delete tables
DDL statements include the following commands:
1. CREATE - To create tables in the database
2. ALTER - To modify the structure of the database
3. DROP - To delete tables from the database
4. TRUNCATE - To remove (delete) all rows from a table
Write syntax of Create Table command 02 – Summer-2021, Winter-2020
Explain create and alter commands with example. 04 – Winter-2019, 2016
1. CREATE TABLE Command: It is used to create a table in a database. Tables are organized into
rows and columns; and each table must have a name.
Syntax:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
:
column_name3 data_type(size)
);
Rules for Creating Table:
1) A table name should be unique.
Department of Information Technology [Ms. Nikita A. Patel] 2
, Database Management (Unit-3) | 3341605
2) A table name and column name must start with the alphabets from A-Z, a-z, and numbers from 0-9
are allowed and the use of special character like _ (underscore) is allowed
3) Table name and column name SQL reserved words not allowed.
4) A name can have the maximum up to 30 characters.
5) Each column definition requires name, datatype and size for that column.
6) Each column separated from the other by ‘,’ (comma).
7) The entire SQL statement is terminated by ‘;’ (semicolon).
Describing a Table
Syntax:
Describe tablename ; OR desc tablename;
Example, Write SQL command for Create table employee.
SQL> connect system/system - Connected.
SQL> create table employee (staffed number(12), staffname varchar2(10), address varchar(100),
salary number(10,2), dept varchar2(12)); - Table created.
SQL> describe employee;
Write the syntax to Alter the table. 02 – Winter-2021
What is the use of the ALTER command in SQL? 02 – Summer-2018, 2017
2. ALTER TABLE Command: It is used to add, delete or modify columns in an existing table.
A. Adding New Columns:
Syntax:
ALTER TABLE table_name ADD (col_name1 datatype(size), col_name2 datatype(size), …);
Example, Add new column designation and mobile_no in employee table.
SQL> alter table employee add (designation varchar(20), mono number(12)); Table altered.
SQL> describe employee;
Department of Information Technology [Ms. Nikita A. Patel] 3
, Database Management (Unit-3) | 3341605
B. Dropping a Columns: This command deletes an existing column from the table along with the data
held by that column.
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example, Remove column mobile_no in employee table.
SQL> alter table employee drop column mono; - Table altered.
SQL> describe employee;
C. Modifying Columns: This command sets new Datatype and new Size as datatype and size for specified
column respectively.
Syntax:
ALTER TABLE table_name MODIFY column_name newdatatype(new size);
Example, Change the size of ‘staffname’ column to 20 in employee table
SQL> alter table employee modify (staffname varchar(20)); - Table altered.
SQL> describe employee;
3. TRUNCATE TABLE Command: It is used to remove all records from a table. Logically, this is
equivalent to DELETE statement that deletes all rows without using WHERE clause.
Syntax:
TRUNCATE TABLE table_name;
Example, Delete all rows of employee table.
SQL> truncate table employee; - Table truncated.
SQL> select * from employee; - no data found
4. DROP TABLE Command: It is used to delete a table.
Syntax:
DROP TABLE table_name;
Department of Information Technology [Ms. Nikita A. Patel] 4
Unit – III: Structure Query Language (Marks-22)
SQL – “Structured Query Language”, which is a computer language for storing, manipulating and
retrieving data stored in a relational database (RDBMS). (Developed by IBM in 1970)
SQL software (RDBMS) like Oracle, MySQL, Microsoft SQL Server, Microsoft Access
SQL is Case insensitive language means do not difference between uppercase and lowercase such as
both SELECT and select are same.
List data types used in DBMS 02 – Summer-2022, 2021, 2019, 2018, Winter-2020
SQL Data types
These data types can be divided into different categories as given below:
1. Numerical Data type
2. Character/String Data type
3. Date Data type
4. Binary Data type
1. Numerical Data type: Used to store zero, negative and positive numerical values. These values can
be fixed-point or floating-point.
Storage Range: Precision range(p): 1 to 38 and Scale range(s): -84 to 127
Syntax: number (p, s) / number (p) / number / int
o Where, p is the precision (maximum no. of digits in a no.) and s is the scale (no. of digit to right
of decimal point)
o For example, number (5) = 12345 and number(5,2) = 432.12
2. Character/String Data type:
SR.
CHAR VARCHAR/VARCHAR2 LONG
NO.
CHAR should be used to store VARCHAR2 is used to store Variable-length strings.
1
Fixed-length character strings. Fixed- length character strings. (backward compatible)
Values are padded with space
Values are not padded with any Values are not padded with
2 characters to match the
characters. any characters.
specified length.
3 Maximum size of 255 bytes. Maximum size of 2000 bytes. Maximum size of 2GB.
Syntax: varchar(size) /
4 Syntax: char(size) Syntax: Long
varchar2(size)
Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
Department of Information Technology [Ms. Nikita A. Patel] 1
, Database Management (Unit-3) | 3341605
3. Date Data type: Used to store date and time.
The standard format is ‘DD-MON-YY’ to store date. Ex, ’21-JUL-2018’
The current date and time can be retrieved using function SYSDATE.
4. Binary Data type: Used to store binary type data like song, video and image.
Sr. No. Raw Long Raw
1 Stores binary type data. Stores large amount of binary type data.
2 Maximum size of 2000 bytes. Maximum size of 2GB.
Give types of SQL commands 02 – Summer-2021, Winter-2020
SQL Components:
1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
Explain DDL commands with examples 04 – Winter-2021, 2019, 2017, Summer-2016
Data Definition Language (DDL) Commands- It is a set of SQL Commands used to create,
modify and delete tables
DDL statements include the following commands:
1. CREATE - To create tables in the database
2. ALTER - To modify the structure of the database
3. DROP - To delete tables from the database
4. TRUNCATE - To remove (delete) all rows from a table
Write syntax of Create Table command 02 – Summer-2021, Winter-2020
Explain create and alter commands with example. 04 – Winter-2019, 2016
1. CREATE TABLE Command: It is used to create a table in a database. Tables are organized into
rows and columns; and each table must have a name.
Syntax:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
:
column_name3 data_type(size)
);
Rules for Creating Table:
1) A table name should be unique.
Department of Information Technology [Ms. Nikita A. Patel] 2
, Database Management (Unit-3) | 3341605
2) A table name and column name must start with the alphabets from A-Z, a-z, and numbers from 0-9
are allowed and the use of special character like _ (underscore) is allowed
3) Table name and column name SQL reserved words not allowed.
4) A name can have the maximum up to 30 characters.
5) Each column definition requires name, datatype and size for that column.
6) Each column separated from the other by ‘,’ (comma).
7) The entire SQL statement is terminated by ‘;’ (semicolon).
Describing a Table
Syntax:
Describe tablename ; OR desc tablename;
Example, Write SQL command for Create table employee.
SQL> connect system/system - Connected.
SQL> create table employee (staffed number(12), staffname varchar2(10), address varchar(100),
salary number(10,2), dept varchar2(12)); - Table created.
SQL> describe employee;
Write the syntax to Alter the table. 02 – Winter-2021
What is the use of the ALTER command in SQL? 02 – Summer-2018, 2017
2. ALTER TABLE Command: It is used to add, delete or modify columns in an existing table.
A. Adding New Columns:
Syntax:
ALTER TABLE table_name ADD (col_name1 datatype(size), col_name2 datatype(size), …);
Example, Add new column designation and mobile_no in employee table.
SQL> alter table employee add (designation varchar(20), mono number(12)); Table altered.
SQL> describe employee;
Department of Information Technology [Ms. Nikita A. Patel] 3
, Database Management (Unit-3) | 3341605
B. Dropping a Columns: This command deletes an existing column from the table along with the data
held by that column.
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example, Remove column mobile_no in employee table.
SQL> alter table employee drop column mono; - Table altered.
SQL> describe employee;
C. Modifying Columns: This command sets new Datatype and new Size as datatype and size for specified
column respectively.
Syntax:
ALTER TABLE table_name MODIFY column_name newdatatype(new size);
Example, Change the size of ‘staffname’ column to 20 in employee table
SQL> alter table employee modify (staffname varchar(20)); - Table altered.
SQL> describe employee;
3. TRUNCATE TABLE Command: It is used to remove all records from a table. Logically, this is
equivalent to DELETE statement that deletes all rows without using WHERE clause.
Syntax:
TRUNCATE TABLE table_name;
Example, Delete all rows of employee table.
SQL> truncate table employee; - Table truncated.
SQL> select * from employee; - no data found
4. DROP TABLE Command: It is used to delete a table.
Syntax:
DROP TABLE table_name;
Department of Information Technology [Ms. Nikita A. Patel] 4
