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
   144   145   146   147   148   149   150   151   152   153   154