LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

Syntax :-

    SELECT * FROM table_name
    WHERE column_name LIKE 'pattern';

Example :-

    SELECT * FROM new_tab
    WHERE name LIKE '%nu';

Wildcards:-

Wildcards are used to search for data within a table. These characters are used with the LIKE operator.

1. % - Zero or more characters

    'Irawen%' - All starting with Irawen  Ex:-  Pirawen
    '%shows' - All ending with shows Ex:-  Pirawen
    '%sh%' - All containing with sh.  Ex:-  Pirawen

2.  - One single character

     'show_ '- Starting with show then any character. Ex:-  shows
     '_rawen' - any character then eek.  Ex:- irawen
     'I_r_a' -  I then any character, then r then any character, then a Ex:-  -Irawen

Solve Example :-

Use LIKE operator :-

Read More

BETWEEN Number operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

1. Between Number

Syntax :- 

  SELECT * FROM table_name
  WHERE column_name BETWEEN value1 and value2;

Example :-

  SELECT * FROM new_tab
  WHERE stu_id BETWEEN 6 and 8;

Solve Example :-

Read More

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

Syntax :-

   SELECT * FROM table_name
   WHERE column_name IN ('value1' , 'value2' , ......);

Example :-

  SELECT * FROM table_name
  WHERE name IN ('Anu' , 'Sonu');

New table create 

Solve Example :-

Read More

OR Operator

The OR operator displays a record if either the first condition OR the second condition is true.

Syntax :-

  SELECT * FROM table_name
  WHERE column_name = 'value'
   OR column = 'value';

Example :-

   SELECT * FROM new_tab
   WHERE name = 'Anu'
    OR stu_id = 5;

First we see a table :-
  

Example :-

Read More

AND Operator

The AND operator display a records if both the first condition AND the second condition are true.

Syntax :- 
    SELECT * FROM table_name
    WHERE column_name = 'value'
    AND column = 'value';

Ex:-
 SELECT * FROM new_tab
 WHERE name = 'Anu'
 AND stu_id = '5';

This is a database.

Example :-

Read More

SQL Statements and Rules

SQL command or statement is a special kind of sentence that contains clauses and all end with a semicolon(;) just as a sequence ends with a period.

SQL statements are divided into four sub language:

(i) Data Definition Language (DDL):- It is used to define the structure of tables in the database.It contains the necessary statement to CREATE , RENAME , ALTER and DROP the tables.

(ii) Data Manipulation Language (DML):- It is a used to manipulate the data in the database.It contains statement to UPDATE , DELETE , INSERT and SELECT data that is stored in the database.

(iii)Data Control Language (DCL):- It is used to control data stored in the database.It contains statement give permission to access the data in the database.These statements are GRANT and REVOKE.

(iv) Transaction Control Language(TCL):- It is used to control the transaction in a database system. It contains statements like COMMIT , ROLLBACK and SAVEPOINT.

Rules for SQL Command/Statements

Rules for SQL commands are given below:
* SQL statements are not case sensitive.
* SQL statements can be executed on one or more tables.
* Keywords cannot be abbreviated.
* The statement can be typed in single line or multiple lines.
* Place a semicolon at the end of the SQL statements.
* A comma (,) is used to separate parameters without a clause.
* Characters and data constants or literals must be enclosed in single quotes(' ').

Read More

WHERE Clause and Equal Operator

WHERE is used to search for a specific data.

Syntax :-

1. Specific data from all column
     Syntax:-
      SELECT * FROM table_name
      WHERE column_name operator 'value';

Ex :-
SELECT * FROM new_tab WHERE name='Anu';

SELECT * FROM new_tab WHERE stu_id = 7;

2. Specific data from specific column
   
    Syntax :-
      SELECT column_name FROM table_name WHERE column_name operator 'value';

Ex:-
   SELECT name FROM new_tab WHERE name = 'Anu';

  SELECT name FROM new_tab WHERE stu_id = 5;

 

Note :- Value can be text or numeric. if it is text then we have to  put single quotes.

 Example :-

Read More

Single quotes Problem

We will write a simple/normal syntax.
INSERT INTO my_tab (t_id, c_name, address) VALUES (142, 'K.K's Company', 'Delhi');

There are two way to solve this problem :-

Use backslash
Ex :-  'K.K\'s Company'

Use two time single quotes
Ex :- 'K.K"s Company

Example :-

Output :-

Read More

Select Statement

The SELECT statement is used to select data from database and retrieve the information.

1. Select all columns from the table
   
  Syntax :- SELECT * FROM table_name;
   
   Ex :- SELECT * FROM my_tab;

2. Select Particular columns the table
   
Syntax :- SELECT column_name1, column_name2,..............FROM table_name;
   
   Ex :- SELECT name, mobile FROM my_tab;

Example :-

Read More

Insert into

The INSERT INTO statement is used to insert new records/row/tuple in a table.

Syntax :-
INSERT INTO table_name (column1, column2, column3, column4,.....)
VALUES (value1, 'value2', 'value3', value4.....);

Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (05, 'Anu', 'Delhi', 982112);

Rules :-
Column and Value order should be same.
Any value that goes into a VARCHAR, CHAR, DATE, or TEXT column has single quotes around it. There are no need of quotes for numeric values (INT, DEC).

Example :-

Without specifying column name

Syntax :-
INSERT INTO table_name
VALUES(value1, 'value2', 'value3', value4);

Ex :-
INSERT INTO my_tab
VALUES (05, 'Anu', 'Delhi', 982112);

Rules :-
The values order should be same as column
We need to insert record for each column we can not leave any column.

Changing the order of column

Syntax :-
INSERT INTO table_name (column2, column1, column4, column3)
VALUES ('value2', value1, value4, 'value3');

Ex :-
INSERT INTO my_tab(name, stu_id, mobile_no, address)
VALUES ('Anu', 05, 982112, 'Delhi');

Insert Data only in Specified Columns

Syntax :-
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, 'value2', 'value3')

Ex :-
INSERT INTO my_tab(stu_id, name, address)
VALUES (05, 'Anu', 'Delhi');

Insert multiple records at one time

Syntax :-
INSERT INTO table_name (column1, column2, column3, column4)
VALUES (value1, 'value2', 'value3', value4);
(value1, 'value2', 'value3', value4);

Ex :-
INSERT INTO my_tab (stu_id, name, address, mobile_no)
VALUES (01, 'Anu', 'Delhi', 982112),
(02, 'Rohan', 'Mumbai', 561425);

 

Read More

Data Types

INT or INTEGER - It holds whole number between -32,768 and 32,767 either it is negative or positive. It can not hold a decimal numbers. The maximum number of digits may be specified in parenthesis.

Syntax :-
 column_name INT (size);

Ex :-
 roll INT (5);


DEC or DECIMAL (size, p) - It holds fixed point numbers, size is the total number of digits and p is the number of digits after the decimal point. The decimal point and the negative sign '-' are not counted in size. If p is 0, values have no decimal point. The maximum number of size for decimal is 65 and for p 30. if p omitted the default is 0. if size is omitted, the default is 10.

Syntax :-
 column_name DECIMAL(size, p);

Ex :- 
 price DECIMAL (4,2);

CHAR or CHARACTER - It holds a fixed length string  (can contain letter, numbers, and special characters). The fixed size is specified in parenthesis. It can store up to 255haracters.

Syntax :- 
 column_name CHAR (20);

Ex :-
  name CHAR (20);

VARCHAR - It holds a variable length string (can contains letters, numbers, and special character). The maximum size is specified parenthesis. It can store up to 255 characters.
Note : If we put a greater value than 255 it will be converted to a TEXT type.

Syntax :- 
 column_name VARCHAR (size);

Ex :- 
 name VARCHAR (50);

TEXT - It holds a string with a maximum length of 65,535 characters.

Syntax :-
 column_name TEXT;

Ex :- 
 address TEXT;

DATE - It display Date values in yyyy-mm-dd format.

Syntax :-
 column_name DATE;

Ex :-
age DATE;

DATATIME - It display DATETIME values in yyyy-mm-dd  hh:mm:ss format.

Syntax :- 
 column_name DATETIME;

Ex :- 
 Date_of_join DATETIME;

TIMESTAMP - It also displayes date and time.

Syntax :-
 column_name TIMESTAMP;

Ex :- 
 login_dt TIMESTAMP;

Read More

Show Databases/ Tables

SHOW DATABASES - This command is used to view all the databases name.

Syntax :- 
 SHOW DATABASES;

SHOW TABLES - This command is used to view all tables of current database.

Syntax :- 
 SHOW TABLES;

Read More

DESC Command

This is used to describe your table. DESC only describes structure of table not the information (rows) inside table. DESC is short form of describe.

Syntax :-
 DESC table_name;

Ex :-
  DESC my_tab;

Read More

Create Table

Syntax :-

CREATE TABLE table name
(
Column_name1 data-type (size),
Column_name2 data-type (size),
Column_name3 data-type (size)
);

Ex :-
CREATE TABLE my_tab
(
name varchar (30),
roll int (4),
address varchar (100)
);

Another way to create a table

Syntax :-
CREATE TABLE table name
(
Column_name1 data-type (size) [constraints],
Column_name2 data-type (size) [constraints],
Column_name3 data-type (size) [constraints]
);

Ex :-
CREATE TABLE my_tab
(
name varchar (30) NOT NULL,
roll int (4) PRIMARY KEY,
address varchar (100)
);

Guidelines for creation of Table

 1. Table name should start with an alphabet.
 2. In table name, blank spaces and single quotes are not allowed.
 3. Reserve words of that RDBMS/DBMS cannot be used as table name.
 4. Proper data type and size should be specified.
 5. Unique column name should be specified.

 

Read More

Create Database

It is used to create database.

Syntax:- CREATE DATABASE database_name;
Ex:- CREATE DATABASE my_db;

Guide for creation of database:-
 Name should start with an alphabet
Blank space and single quotes are not allowed
Reserve words of that RDBMS/DBMS can not be used as database name

Use a database

 This is used to tell your RDBMS/DBMS that you want to use this database.

Syntax:- USE database_name;
Ex:- USE my_db;

Read More

Student Registration Form

Program:-

<html>
<head>
<script type=”text/javascript” src=”validate.js”></script>
</head>
<body>
<form action=”#” name=”StudentRegistration” onsubmit=”return(validate());”>
<table cellpadding=”2″ width=”20%” bgcolor=”99FFFF” align=”center”
cellspacing=”2″>
<tr>
<td colspan=2>
<center><font size=4><b>Student Registration Form</b></font></center>
</td>
</tr>
<tr>
<td>Name</td>
<td><input type=text name=textnames id=”textname” size=”30″></td>
</tr>
<tr>
<td>Father Name</td>
<td><input type=”text” name=”fathername” id=”fathername”
size=”30″></td>
</tr>
<tr>
<td>Postal Address</td>
<td><input type=”text” name=”paddress” id=”paddress” size=”30″></td>
</tr>
<tr>
<td>Personal Address</td>
<td><input type=”text” name=”personaladdress”
id=”personaladdress” size=”30″></td>
</tr>
<tr>
<td>Sex</td>
<td><input type=”radio” name=”sex” value=”male” size=”10″>Male
<input type=”radio” name=”sex” value=”Female” size=”10″>Female</td>
</tr>
<tr>
<td>City</td>
<td><select name=”City”>
<option value=”-1″ selected>select..</option>
<option value=”New Delhi”>NEW DELHI</option>
<option value=”Mumbai”>MUMBAI</option>
<option value=”Goa”>GOA</option>
<option value=”Patna”>PATNA</option>
</select></td>
</tr>
<tr>
<td>Course</td>
<td><select name=”Course”>
<option value=”-1″ selected>select..</option>
<option value=”B.Tech”>B.TECH</option>
<option value=”MCA”>MCA</option>
<option value=”MBA”>MBA</option>
<option value=”BCA”>BCA</option>
</select></td>
</tr>
<tr>
<td>District</td>
<td><select name=”District”>
<option value=”-1″ selected>select..</option>
<option value=”Nalanda”>NALANDA</option>
<option value=”UP”>UP</option>
<option value=”Goa”>GOA</option>
<option value=”Patna”>PATNA</option>
</select></td>
</tr>
<tr>
<td>State</td>
<td><select Name=”State”>
<option value=”-1″ selected>select..</option>
<option value=”New Delhi”>NEW DELHI</option>
<option value=”Mumbai”>MUMBAI</option>
<option value=”Goa”>GOA</option>
<option value=”Bihar”>BIHAR</option>
</select></td>
</tr>
<tr>
<td>PinCode</td>
<td><input type=”text” name=”pincode” id=”pincode” size=”30″></td>
</tr>
<tr>
<td>EmailId</td>
<td><input type=”text” name=”emailid” id=”emailid” size=”30″></td>
</tr>
<tr>
<td>DOB</td>
<td><input type=”text” name=”dob” id=”dob” size=”30″></td>
</tr>
<tr>
<td>MobileNo</td>
<td><input type=”text” name=”mobileno” id=”mobileno” size=”30″></td>
</tr>
<tr>
<td><input type=”reset”></td>
<td colspan=”2″><input type=”submit” value=”Submit Form” /></td>
</tr>
</table>
</form>
</body>
</html>

Read More