CLASS 11 Database Mysql important Query Notes

INFORMATICS PRACTICES WITH PYTHON

CLASS 11 SUBJECT CODE: 065 

REVISION NOTES

SQL COMMANDS

CLASS 11 SUBJECT CODE: 065 

REVISION NOTES

SQL COMMANDS

There is different type of SQL commands. We will discuss some of them in this pdf. In DDL and DML command. DDL means Data Definition Language which define the structure of the Table. Commands are Create, Alter, Rename, Drop etc. DML means Data Manipulation Language. Commands are Select, Insert, Delete, Update.

Let us discuss Some DDL commands:

Create command:

Create databases Creating Databases is an easier task relatively.

In simplest form the create Database command takes the following syntax.

SYNTAX

Create DATABASE [IF NOT EXISTS] <DATABASE_NAME>;

IF NOT EXISTS clause,

if used, will first test whether a database by the mentioned name

already exists or not. If it does, then “Create database command “is

simply ignored, otherwise a database with the mentioned name is created.

Create database mydb; 

Create database if not exists mydb; 

Opening database Command : 

Creating database is not enough, before you create tables in it, you

need to open the database. To open a database, you simply need to

write the statement as per following 

Syntax.

USE <database_name>;

Use mydb;

The only thing you need to ensure before opening a database Is that it

must already exist. It must be already created. To check the names of

existing databases, you may write following commands:

SYNTAX 

SHOW DATABASES;

IF YOU WANT TO LOOK FOR A SPECIFIC DATABASE YOU MAY WRITE

Show databases like <database_name>

Show databases like mydb;

If mysql shows no name and just gives Empty set, it means that given

database name does not exist.

Creating a tables:

Tables are defined with the create Table command. When a table is

created, its columns are named, datatypes and size are supplied for

each column. Each table must have at least one column. 

CREATE TABEL <TABLE_NAME> 

(

COLUMN_NAME1  DATATYPE  (SIZE)

COLUMN_NAME2  DATATYPE  (SIZE)

COLUMN_NAME3  DATATYPE  (SIZE)

)

To create an employee table whose schema is as follows:

CREATE TABLE EMPLOYEE

(     Ecode  int,

Ename char(20),

Sex char(1),

Grade char(2),

Gross decimal       );

When you create a table, you can place constraints on the values that

can be entered into its field, if this is specified, SQL will reject any values

(entered/changed through INSERT/UPDATE command) that violate

the criteria you define.A constraint is a condition or check applicable

on a field or set of fields.

There tow basic types of constraints are:

1. Column constraints:   applicable to column

2. Table constrains:         applicable to groups.

The difference between the two constraint types is that column constraints apply only to individual columns, whereas table constraints apply to groups of one or more columns. The following is the syntax for CREATE TABLE commands, expanded to include constraints.:

Create table <table name>

(

COLUMN_NAME1  DATATYPE  (SIZE)  column constraint,

COLUMN_NAME2  DATATYPE  (SIZE) column constraint,

COLUMN_NAME3  DATATYPE  (SIZE) column constraint

)

 

CREATE TABLE EMPLOYEE

( ecode  integer NOT NULL,

Ename char(20) NOT NULL,

Sex char(1) NOT NULL,

Grade  char(2),

Gross decimal

);

Different constraints:

These constraints ensure database integrity, thus are sometimes

called database integrity constraints.

1. UNIQUE CONSTRAINT

2. Primary key constraint

3. Default constrains

4. Check constraint

5. Foreign key constraint

CREATE TABLE EMPLOYEE

( 

ecode  integer NOT NULL  primary key,

Ename char(20) NOT NULL ,

Sex char(1) NOT NULL DEFAULT ”M”,

Grade  char(2),

Gross decimal CHECK (gross>1000)

);

APPLY TABLE CONSTRAINTS

When a constraint is to be applied on a group of columns of the table,

it is called table constraint. The table constraints appear in the end of

table constraint.

CREATE TABLE EMPLOYEE

( ecode  integer NOT NULL ,

Ename char(20) NOT NULL ,

Sex char(1) NOT NULL ,

Grade  char(2),

Gross decimal 

PRIMARY KEY(ecode)      # table constraints                      );

Alter command

When we define a system, we specify what data we need to store, the

size and data type of that data. What can we do when requirements

change? 

Alter table command is used:

1. To add a column

2. To add an integrity constraint

3. To redefine a column (datatype, size, default, value).

Adding a new column:

Syntax

ALTER TABLE<TABLE_NAME>ADD<COLUMN_NAME><DATATYPE><SIZE>

[<CONSTRAINT_NAME>];

Command : Alter table Employee ADD dept Char(5);

After Add new column in employee

MODIFY COLUMN

To modify existing columns of table, ALTER TABLE command can be

used according to following syntax. Using Modify constraint you can

change the column datatype and size of the datatype using modify

command. Syntax of the modify command is given below.

Syntax

ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME>

<DATATYPE><SIZE>;

After modify the table

Command: Alter table employee modify dept char (10) primary key;

Change the column name 

Syntax 

ALTER TABLE<TABLE_NAME> CHANGE <OLD_COL_NAME>

<NEW_COL_NAME> <COLUMN_DEFINATION>;

Command: Alter table employee change sec salary int not null;

After Changing the column name 

SET DEFAULT VALUE

TABLE BEFORE SET DEFAULT VLAUE:

After applying Default value syntax

Syntax 

ALTER TABLE <TABLE_NAME> ALTER DEPT SET DEFAULT

“DEFAULT_VALUE”

Command : alter table employee alter dept set default “CS”;

Changing the table name

Syntax

Alter table <table_name> rename to <New_table_name>;

Click here to download MYSQL PDF