Page 63 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes  SQL supports many functions that can be used with dates, numbers, and character strings.
You can also convert from one data type to another implicitly or explicitly. For example,
TO_DATE, TO_CHAR, and so on.
 Another set of functions that are used for data manipulation are group functions like AVG,
MAX, COUNT, WIN, etc.

 To join two or more tables we have equi join, self join, and outer (uses a + sign). To write
certain complicated queries, use of subqueries makes things easier. A subquery is written
in the where condition of the outer query along with operators like =, IN, ANY, ALL or
EXISTS.

 SQL also supports set operations like UNION, DIFFERENCE, and MINUS. There a number
of database objects that can be created and altered:
 CREATE TABLE Creating a new table

 CREATE VIEW Creating a new view
 CREATE SEQUENCE Creating an automatic sequence of numbers
 INDEX Creating an index on columns other than primary key for
efficient data retrieval

 The two statements GRANT and REVOKE are important for assigning or removing
privileges of a table or column or any database object.

 It is possible to set locks to tables in a multi-user environment for efficient security
purpose. Finally, several additional examples were discussed on SQL.

3.13 Keywords

Creating table: To create a table, the create statement specifies the name of the table and the
names and data types of each column of the table.
Data Definition Language (DDL): This part of SQL supports the creation, deletion and modification
of tables. Integrity constraints can be defined on tables, either when the table is created or later.
The DDL also provides commands for specifying access rights to tables. The commercial
implementations also provide commands for creating and deleting indexes.
Data Manipulation Language (DML): This part of SQL allows users to pose queries, insert
tuples, delete tuples and modify tuples (rows) in the database.

Select clause: SELECT is a command from DML language which is used to select specific columns
of the tables.
SQL: SQL is the standard language for relational database management systems.

3.14 Self Assessment

Choose the appropriate answer:
1. SQL stands for:
(a) Systematic Query Language
(b) Semantic Query Language
(c) Structured Query Language

(d) Structured Queue Language

56 LOVELY PROFESSIONAL UNIVERSITY