JOINs

The JOIN is used to combine rows from two or more tables.

SQL Inner Join :-

Select all rows from tables for the match between the columns in tables.
Same as JOIN

Syntax :-
SELECT column FROM table1
INNER JOIN table2
on table1.column = table2.column;

[only matching rows are retrieved]

 Example :-

SELECT emp.eno, emp.ename, dept.dno, dept.dname FROM emp
INNER JOIN dept
on emp.dno = dept.dno; 

SQL LEFT JOIN :-

Returns all rows from the left table, with the matching rows in the right table.
The result is NULL in the right side when there is no match.

Syntax :-

SELECT Columns FROM table1
LEFT [OUTER] JOIN table2
on table1.column = table2.column;
 
Example :-
SELECT emp.eno, emp.ename,dept.dno, dept.dname FROM emp
LEFT JOIN dept
on emp dno = dept.dno; 

SQL RIGHT JOIN :-

Returns all rows from the right table, with the matching rows in the left table.
The result is NULL in the left side when there is no match.

Syntax :-

SELECT columns FROM table1
RIGHT [OUTER] JOIN table2
on table1.column = table2.column;

Example :-

 FULL OUTER JOIN

Returns all rows from the left table and from the right table.
The combines the result of both LEFT and RIGHT joins.

Syntax :-

SELECT columns FROM table1
FULL [OUTER] JOIN table2
on table1.column = table2.column;

Example :-

Read More

UNION Operator

Combines the result of two or more select statement.

Each Select Statement must have same number of columns and columns must have same data types.

Columns should also be in same order.

Syntax :-

[ Removes the duplicate Values ]

SELECT Column1, Column2, FROM table1 
UNION 
SELECT Column1, Column2, FROM table2 

[ Duplicate Values are retained ]

SELECT Column FROM table1
UNION ALL
SELECT Column FROM table2


Example :-

SELECT ename, job FROM emp1
UNION
SELECT ename, job FROM emp2

We have two tables :-

After use UNION  operator we get this table,

a

Read More

FOREIGN KEY

A FOREIGN KEY in one table points to PRIMARY KEY in another table.

A foreign key can have a different name than the primary key it comes from.

The primary key used by a foreign key is also known as a parent key. The table where the primary key is from is known as a parent table.

The foreign key can be used to make sure that the row in one table have corresponding row in another table.

Foreign key value can be null, even though primary key value can't.

Foreign key don't have to be unique in fact, they often aren't.

Create table from use a FOREIGN KEY:-

CREATE TABLE department
(
D_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
D_Name varchar (40),
E_id int,'
CONSTRAINT employee_Eid_fk
FOREIGN KEY (E_id) REFERENCES employee (E_id)

The CONSTRAINT clause allows to define constraints name for the foreign key constraint. If we omit it MySQL will generate a name automatically. It is optional.

The REFERENCES clause specifies the parent table and its columns to which the columns in the child table refer. The number of columns in the child table and parent table specified in the FOREIGN KEY and REFERENCES must be the same.

Read More

ALTER TABLE

This command is used to Add/Change/Modify/Drop existing structure of the table.

ADD Column
Enable/Disable Constraints
Change Column
Modify Column
Drop Column

ADD Column :- When a new column is to be added to the table structure without constraints.

Syntax :-

ALTER TABLE table_name
ADD COLUMN column_name datatype (size);

Example:-

ALTER TABLE my_tab
ADD COLUMN stu_id integer (5);

Change Column :-This is used to change name and data type of an existing column without constraints.

Syntax:-

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_data_type (size);

Example:-

ALTER TABLE my_tab
CHANGE COLUMN name student varchar (5);

Modify Column :- This is used to modify size of the data type or the data type itself of an existing column without changing column name.

Syntax:-

ALTER TABLE table_name
MODIFY COLUMN column_name datatype (size);

Example:-

ALTER TABLE  my_tab
MODIFY COLUMN roll integer (10);

DROP COLUMN :- When a column in a table need to delete

Syntax :- 

ALTER TABLE table_name
DROP COLUMN column_name;

Example:-

ALTER TABLE my_tab
DROP COLUMN roll;

When removing constraints from a column

Syntax:-

ALTER TABLE table_name
DROP constraints_name column_name;

Example:-

ALTER TABLE my_tab
DROP UNIQUE KEY (roll);

Read More

SQL The Complete Reference, 3rd Edition Paperback – 1 Jul 2017 by James Groff (Author), Paul Weinberg (Author), Andy Oppel (Author)

SQL The Complete Reference, 3rd Edition

Get comprehensive coverage of every aspect of SQL from three leading industry experts. Revised with coverage of the latest RDBMS software versions, this one-stop guide explains how to build, populate, and administer high-performance databases and develop robust SQL-based applications. 

SQL: The Complete Reference, Third Edition shows you how to work with SQL commands and statements, set up relational databases, load and modify database objects, perform powerful queries, tune performance, and implement reliable security policies. Learn how to employ DDL statements and APIs, integrate XML and Java scripts, use SQL objects, build web servers, handle remote access, and perform distributed transactions. Techniques for managing in-memory, stream, and embedded databases that run on today's mobile, handheld, and wireless devices are included in this in-depth volume.

• Build SQL-based relational databases and applications
• Create, load, and modify database objects using SQL
• Construct and execute simple, multitable, and summary queries
• Implement security measures with authentication, privileges, roles, and views
• Handle database optimization, backup, recovery, and replication
• Work with stored procedures, functions, extensions, triggers, and objects
• Extend functionality using APIs, dynamic SQL, and embedded SQL
• Explore advanced topics such as DBMS transactions, locking mechanisms, materialized views, and two-phase commit protocol
• Understand the latest market trends and the future of SQL

About the Author

James R. Groff is senior vice president of business strategy at Oracle Corporation. He is a SQL expert whose SQL-oriented software company, TimesTen Performance Software, was acquired by Oracle in 2005.

Paul N. Weinberg is senior vice president of NetWeaver MDM at SAP. He is a SQL expert whose SQL-oriented software company, A2i, Inc., was acquired by SAP in 2004. Weinberg is the bestselling author, with James Groff, of the previous editions of this book.

Read More

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