Auto Increment

Auto increment is used to  generate an unique, when a new record is inserted into a table.
If use a auto increment than increase by 1. 
In table sequence is increment automatically.
Auto increment ignore null value.

Syntax :-

CREATE TABLE table_name
(
Column_name int NOT NULL AUTO_INCREMENT,
Column_name1 varchar (50) NOT NULL,
Column_name2 varchar (50),
PRIMARY KEY (column_name)
);

Example :-

CREATE TABLE emp
(
Emp_id int NOT NULL AUTO_INCREMENT,
Emp_name varchar (50) NOT NULL,
City varchar (50),
PRIMARY KEY (Emp_id)
);

Insert rule are different 

INSERT INTO emp (emp_name, city)
VALUES
('Subham', 'Delhi'),
('Ankit', 'Mumbai');

INSERT INTO emp (emp_id, emp_name, city)
VALUES
(NULL, 'Subham', 'Delhi'),
(NULL, 'Ankit', 'Mumbai');

INSERT INTO emp (emp_id, emp_name, city)
VALUES
(NULL, 'Subham', 'Delhi'),
(1, 'Ankit', 'Mumbai');

Table:-

Use NULL VALUE

Read More

PRIMARY KEY

The PRIMARY KEY constraints uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most a tables should have a primary key, and each table can have only ONE primary key.

Example:-

CREATE TABLE student 
(
Name varchar (30),
Roll integer (5) NOT NULL PRIMARY KEY,
Mobile_no integer (10)
);

CREATE TABLE student 
(
Name varchar (30),
Roll integer (5) NOT NULL,
Mobile_no integer (10),
PRIMARY KEY (Roll)
);

Read More

UNIQUE KEY

The UNIQUE constraint uniquely identifies each record in a database table. There can be many UNIQUE constraints per table. 

A Unique key column can contain NULL values.

Syntax :-

   CREATE TABLE student
   (
    Name varchar (30),
    Roll integer (5),
    Mobile_no integer (10) UNIQUE KEY
    );

Example :-

CREATE TABLE u_tab
(
stu_id int (5) UNIQUE KEY,
name varchar (30),
roll int (5) UNIQUE KEY,
city varchar (40)
);

Insert Table :-

Read More

ORDER BY

This is used to sort the record.

ASC - It sorts in ascending order (by default).
DESC - It sorts in descending order.

1. Sorts in descending order

Syntax :-

   SELECT * FROM table_name
   ORDER BY column_name DESC;

Example :-

   SELECT * FROMemp
   ORDER BY emp_name DESC;


 Example :-

Read More

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