Page 149 - Informatics_Practices_Fliipbook_Class12
P. 149
In the above description of CREATE DATABASE statement, CREATE is an SQL keyword and database_name is the
name of the database to be specified by the user.
While describing the SQL statements, we shall write the SQL keywords in courier font uppercase letters in
boldface. We will write the user-defined names and user inputs in courier font but not boldface.
Now, we are ready to create the COMPANY database using the CREATE DATABASE statement:
CREATE DATABASE COMPANY;
To display the list of databases that have been created so far, we use the SHOW DATABASES statement:
SHOW DATABASES;
SHOW DATABASES: Displays names of all the existing databases.
Before we perform any operations on a database, it has to be opened for use. SQL statement USE <database_
name> serves this purpose:
USE database_name;
USE database_name: Makes a database active for use
To select the database COMPANY for creating and manipulating tables in the COMPANY database, we execute the
statement:
USE COMPANY;
4.4.2 CREATE TABLE statement
Next, let us create tables corresponding to different entities (EMPLOYEE, DEPARTMENT, PROJECT, and WORKS_
ON). CREATE TABLE statement creates tables (relations). The syntax for the CREATE TABLE statement is :
CREATE TABLE table_name(
attribute1 data_type [constraint]
[, attribute2 data_type [constraint]]
[, attributeN data_type [constraint]]
table_constraints]
);
In the description of an SQL statement, a pair of square brackets ([]) denotes optional parts. Thus, a CREATE TABLE
statement may include any number of attributes or constraints. Note that the attributes and constraints are specified
within parenthesis separated by commas.
Next, we give an example of a statement that creates an EMPLOYEE table with the following attributes: ID, FName,
LName, Gender, Address, City, Pin_Code, DOB, Salary and DeptNo. Also note that attribute
ID is the primary key of the table EMPLOYEE. When the primary key comprises a single attribute, often we use the
keyword PRIMARY KEY, following the type of the attribute to denote that it is the primary key of the table as shown
below in the CREATE TABLE statement:
CREATE TABLE: Used to specify
Table name
Attribute names, default values
Attribute types
Primary key
Constraints: NOT NULL, foreign key, values allowed
Database Query using SQL 135

