Thursday, 28 March 2019
Monday, 18 March 2019
Microsoft SQL Server 2008, Second Edition
Irawen March 18, 2019 SQL No comments
Also get fast facts to : Plan, deploy, and configure SQL Server 2008 Administer server and surface security, access, and network configuration Import, export, transform, and replicate data Manipulate schemas, tables, indexes, and views Automate maintenance and implement policy-based management Monitor server activity and tune performance Manage log shipping and database mirroringPerform backups and recovery
Buy :
Microsoft SQL Server 2008 Administrator's Pocket Consultant Paperback – 2009 by Stanek (Author), William R (Author)
PDF Download :
Microsoft SQL Server 2008 Administrator's Pocket Consultant Paperback – 2009 by Stanek (Author), William R (Author)
Sunday, 24 June 2018
JOINs
Irawen June 24, 2018 SQL 1 comment
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 :-
Saturday, 23 June 2018
UNION Operator
Irawen June 23, 2018 SQL No comments
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
FOREIGN KEY
Irawen June 23, 2018 SQL 1 comment
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.
Friday, 22 June 2018
ALTER TABLE
Irawen June 22, 2018 SQL No comments
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);
Thursday, 21 June 2018
SQL The Complete Reference, 3rd Edition Paperback – 1 Jul 2017 by James Groff (Author), Paul Weinberg (Author), Andy Oppel (Author)
Irawen June 21, 2018 SQL No comments
SQL The Complete Reference, 3rd Edition
- 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
Auto Increment
Irawen June 21, 2018 SQL 1 comment
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
Wednesday, 20 June 2018
PRIMARY KEY
Irawen June 20, 2018 SQL No comments
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)
);
Tuesday, 19 June 2018
UNIQUE KEY
Irawen June 19, 2018 SQL 1 comment
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 :-
Monday, 11 June 2018
ORDER BY
Irawen June 11, 2018 SQL 1 comment
Sunday, 10 June 2018
LIKE Operator
Irawen June 10, 2018 SQL 1 comment
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 :-
Thursday, 7 June 2018
IN Operator
Irawen June 07, 2018 SQL 1 comment
OR Operator
Irawen June 07, 2018 SQL 1 comment
Wednesday, 6 June 2018
AND Operator
Irawen June 06, 2018 SQL 1 comment
Friday, 1 June 2018
SQL Statements and Rules
Irawen June 01, 2018 SQL 1 comment
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(' ').
Saturday, 21 April 2018
WHERE Clause and Equal Operator
Irawen April 21, 2018 SQL 1 comment
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;
Friday, 20 April 2018
Select Statement
Irawen April 20, 2018 SQL 1 comment
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 :-
Popular Posts
-
Activity_main.xml File <? xml version= "1.0" encoding= "utf-8" ?> < LinearLayout xmlns: android = &qu...
-
Procedure Oriented Programming Language:- High level language such as COBOL , FORTRAN AND C is commonly known as procedure oriented progr...
-
Activity Main : <? xml version ="1.0" encoding ="utf-8" ?> < androidx.constraintlayout.widget.ConstraintLayout...
-
Build.gradle File:- implementation 'com.google.android.material:material:1.3.0' implementation 'com.squareup.retrofit2:retr...
-
Advantages of Object Oriented Programming Object oriented programming has several advantage to the programmer and user. Through inheri...
-
There are many programming languages, each corresponding to specific needs (formula calculus, character string processing, real-time, etc.)...
-
Build interactive, data-driven websites with the potent combination of open-source technologies and web standards, even if you have only ...
-
Pseudocode is a compact and informal high-level description of a computer programming algorithm. Pseudo-code typically omits details tha...
-
What is Python? → Python is an interpreted High level programming Language for General purpose programming. Python is created by Gui...
-
Java :- It is a fast, secure and reliable general purpose computer programming language. Python :- A Readable, efficient and powerfu...