SQL Data Definition Language


SQL Tutorial 5: Data Definition Language

SQL (Structured Query Language) is for Database Developers, Database Administrators, and Database testers.

Three main subsets of SQL:

i) Data Definition Language
ii) Data Manipulation Language
iii) Data Control Language
------------------------------
Database Engine is required to practice / use SQL, we can use any Database engine like Oracle, MS SQL Server or MySQL etc...
----------------------------------------
Overview

i) Database Management System
ii) Database Engine
iii) Database Server
iv) Database
v) Table and Record

i) Database Management System

> Database Management System is designed to allow the definition, creation, querying, update and administration of Databases

> It is a Computer Software Application that interacts with the user, other applications, and the database itself to capture and analyze the data

ii) Database Engine

Software that stores and retrieves data in a database. It may be self-contained program or the part of DBMS that performs the storage and retrieval operations.

iii) Database Server

Database Management Systems provide Database server functionality, database server provides database services to other computer programs or computers.

iv) Database


> A Database is a Systematic collection of data
> Databases support storage and manipulations of data
> Databases make data management easy

v) Table and Record

> A Table is a predefined format of rows and columns that define an entity
> Each column contains a different type of attribute and each row corresponds a single record.
---------------------------------------------------------
Data Definition Language

Data Definition Language Commands are used to Create, Modify, and Drop the Structure of Database Objects like Table, View, and Index etc...

Data Definition Language Commands and Operations
----------------------------------------
Important DDL Commands

1) Create
2) Alter
3) Drop
4) Truncate
5) Rename
----------------------------------------
Important DDL Operations

1) Create a Database
2) Use Database
3) Rename a Database
4) Drop Database
5) Create a Table
6) Rename Table
7) Add a Column to exiting Table
8) Add multiple columns to existing Table
9) Modify an existing column
10) Rename a Column
11) Drop a Column
12) Truncate a Table
13) Drop a Table
SQL Tutorial
Download and Install MS SQL Server Express Edition (It is Free Edition) and practice SQL Commands and Operations.
----------------------------------------
1) Create a Database

Syntax:

Create Database databaseName;

Example:

Create Database gcreddyDB;
----------------------------------------
2) Use Database

Syntax

Use databaseName;

Example:

Use gcreddyDB;
----------------------------------------
3) Rename a Database

Syntax

Alter Database databaseName Modify Name = newdatabseName;

Example:

Alter Database gcreddyDB Modify Name = hyderabad

Or

Alter Database gcreddyDB 
Modify Name = hyderabad
----------------------------------------
4) Drop a Database

Syntax:

Drop Database databaseName;

Example:

Drop Database gcreddyDB;
----------------------------------------
5) Create a Table

Syntax:

Create Table tableName
(
column1_name dataType(size),
column2_name dataType(size),
.
.
.
);

Example:

Create Table Students
(
STID int,
STName char(50),
);
------------------------------------
View Table info
Select * from Students

View Table Schema
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Students';
----------------------------------------
6) Rename Table

Syntax:

EXEC sp_rename 'old_tablename', 'new_tablename';

Example:

EXEC sp_rename 'Students', 'newStudents';
----------------------------------------
7) Add a Column to existing Table

Syntax:

Alter Table table_name add column_name dataType(size);

Example:
Alter Table newStudents add City char(50);
----------------------------------------
8) Add multiple columns to an existing Table

Syntax:

Alter Table table_name add column1_name dataType(size), column2_name dataType(size);

Or
Alter Table table_name add
column1_name dataType(size),
column2_name dataType(size),
.
.;

Example:

Alter Table newStudents add add1 char(100), add2 char(70);

Or

Alter Table newStudents add 
add3 char(100), 
add4 char(70),
add5 char (100),
phone int;
----------------------------------------
9) Modify an existing column

Syntax: 

Alter Table table_name Alter Column column_name dataType(size);

Example:

Alter Table newStudents Alter Column add1 varchar(150);
----------------------------------------
10) Rename a Column

Syntax:

EXEC sp_rename 'table_name.old_column_name', 'new_colum_name';

Example:
ExEC sp_rename 'newStudents.phone', 'mobile'
----------------------------------------
11) Drop a Column

Syntax:

Alter Table table_name Drop Column column_name;

Example:

Alter Table newStudents Drop Column City;
----------------------------------------
12) Truncate a Table

Truncate Table command is used to delete complete data from an existing table

Syntax:

Truncate Table table_name;

Example:

Truncate Table newStudents;
----------------------------------------
13) Drop a Table

Drop Table command is used to delete complete Table (Data and Table Structure) from the Database.

Syntax:

Drop Table table_name;

Example:
Drop Table newStudents;
-----------------------------------------------------------------
Also Read:


1) SQL Tutorial 1: Introduction to SQL

2) SQL Tutorial 2: SQL Overview


3) SQL Tutorial 3: MS SQL Server Installation


4) SQL Tutorial 4: Database Fundamentals, SQL Elements


SQL Interview Questions and Answers

Java Interview Questions and Answers

Java Interview Questions and Answers

Section I: General
Section II: Comments in Java
Section III: Java Modifiers
Section IV: Java Data Types
Section V: Java Variables
Section VI: Operators in Java
Section VII: Java Control Flow Statements
Section VIII: Java IO and File Handling
Section IX: String Handling in Java
Section X: Java Arrays
Section XI: Java Methods
Section XII: Exception Handling in Java
Section XIII: Java Inheritance
Section XIV: Java Polymorphism
Section XV) Java Abstraction
Section XVI: Java Encapsulation
Java Tutorial
------------------------------------------------
Section I: General

1) What is Java?

Java is a Programming Language and a Platform

• Java is used as a programming to develop Software Applications
• Java is used as Software Platform to run Java Applications

2) What are important Java Editions or Parts of Java?

Java has Three Important Editions

i) Java Standard Edition / Core Java (* Old name J2SE)
ii) Java Enterprise Edition / Advanced Java (*Old name J2EE)
iii) Java Micro Edition (* Old name J2ME)

3) Where Java is used? 

Java is used to create,
 Desktop Applications
 Web Applications
 Enterprise Applications
 Mobile Applications
 Smart Cards
 Embedded Systems
 • Scientific Applications
 Computer Games Etc...

4) How Java is Used?

Java is an Open Source Software and Platform independent,

• For Software Development download and install JDK (includes JRE and JVM) Software,
• For Run-time (to run Software Applications that developed in Java) download and install JRE (includes JVM)

We can run Java byte code on any supported platform.

5) What are important features of Java?

Important Features of Java are,

• Simple
• Object Oriented
• Platform independent
• Secured
• Robust, 
• Architecture neutral
• Portable
• Interpreted
• Multithreaded

• Distributed

6) What is JVM?

Java Virtual machine (JVM) is the virtual machine that run the Java bytecodes. 
The JVM doesn't understand Java source code, that's why you compile your *.java files to obtain *.class files that contain the bytecodes understandable by the JVM. 

7) What is JRE?

The Java Runtime Environment (JRE) provides the libraries, the Java Virtual Machine, and other components to run applets and applications written in the Java programming language.

8) What is JDK?

Java Development Kit (JDK) is a superset of the JRE, and contains everything that is in the JRE, plus tools such as the compilers and debuggers necessary for developing applets and applications.

9) What are important Java Language Elements?

Important Language Elements of Java are,

i) Data Types
ii) Modifiers
iii) Variables
iv) Operators
v) Control Flow Statements
vi) Methods Etc...

10) What are the Fundamentals of OOPS?

Four fundamentals of Object Oriented Programming are,

i) Inheritance
ii) Polymorphism
iii) Abstraction
iv) Encapsulation

11) What are Java Syntax Rules?

Java Syntax Rules:

• Java is a case sensitive language.

• First letter of Class name should be in Upper case.

• Method names should start with lower case letter.

• Java program file name should exactly match with class name.

• Java program execution starts from main method, which is mandatory in every Java program.

• Every statement/step should end with semi colon symbol.

• Code blocks enclosed with {}.

12) How to setup Java Environment on Windows Operating System?

• Download Java Software (JDK) and Install.
(Download Java (JDK) Software from either java.com or oracle.com, and Install)

• Set Environment Variable path (Path variable)

• Download Eclipse IDE and extract

Launch Eclipse IDE, Write Java Program and execute.

13) How to set Environment Variable path in Windows Operating environment?

After Java Installation then you can get Java folder in C:\Program Files.

Set Environment Variable path (Path variable)

Navigation

First take jdk bin directory path

Select MyComputer and right click
> Properties
> Advanced System Settings
> Environment Variables
> Select Path variable from system variables
> Edit
> Paste java bin directory path in variable value field
> OK
> Ok
>OK
> Close

14) How to write and execute Java Programs using Windows Command Prompt (Without Eclipse IDE)?

In Computer Programming we have three steps.

Step 1: Write a Sample Program in Notepad and save with .java extension

public class Sample {
public static void main (String [] args) {
System.out.println ("Hello Java World");
}
}

Step 2: Compile the Program

> Launch Command prompt
> Chang to Java program file directory.
> Type javac Sample.java

* It creates Java class file

Step 3: Run / Execute the program.

Type java Sample

Then it displays the Output on the Console.

15) What is Eclipse IDE?

Eclipse IDE (Integrated Development Environment)

• Eclipse IDE is a platform to write and execute computer programs like Java, Perl, Python, Ruby, PHP etc...

• Eclipse IDE is open source.

• It provides Editor for writing programs, Syntax guidance, Context help, auto compilation etc...

Note: Using Eclipse IDE we can write and execute Java Programs.

16) How to write and execute Java programs using Eclipse IDE?

Steps to Write and Execute a Java Program using Eclipse IDE :

> Launch Eclipse IDE
> Create Java Project
> Create Java Package
> Create Java Class
Write Java Program and Run.

Note: Eclipse IDE provides auto compilation facility.

17) Explain about Java Program Structure?

Sections of Java Program,

i) Documentation Section 
ii) Package declaration statement
iii) Import Statement/s
iv) Class declaration 
v) main Method (Java Program execution starts from main method)
vi) Declarations (We declare Variables and Constants.)
vii) General Statements (Ex: d = a + b + c;)
viii) Code blocks (Condition blocks, Loop Blocks etc...)

Note: Every Normal Statement should end with Semi colon and Every code block enclosed with {}
------------------------------------------------
Section II: Comments in Java

1) What are Comments in Computer Program?

Comments are English words used for Code documentation

Purpose of Comments
i) To make the code Readable
ii) To make the code disable from Execution

2) How to write comments in Java?

Comments Syntax in Java

Java supports Single line comment and multiple line comment.

a) Use // for Single line comment

b) Use /*..........
------------
----------
----------
-------------*/ for multiple line comment

Or
In Eclipse IDE,

Select Statements/Steps
Source menu -> Add Block Comment

Uncomment
Select Comment block
Source menu -> Remove Block Comment
------------------------------------------------
Section III: Java Modifiers

1) What are Modifiers?

Modifiers are keywords that we add to those definitions to change their meaning.

2) What are the types of Modifiers in Java?

Two types of Modifiers in Java

i) Access Modifiers
ii) Non Access Modifiers

3) What are Access Modifiers?

We use access modifiers to define access control for classes, methods and variables.

Access Modifiers in Java are,

i) public
public access modifiers is accessible everywhere.

Ex:
public class Sample{
}

ii) private
The private access modifier is accessible only within class.

Ex:
private int a=100;

iii) default

If we don't specify any modifier then it is treated as default, this can be accessible only within package.

Ex:
class Sample {
}

iv) protected
The protected modifier is accessible within package, outside of the package but through Inheritance.

Ex:
protected class Sample {
}

4) What are the important Non Access Modifiers in Java?

i) static 
static modifier is used to create classes, methods and variables.

ii) final 
final modifier for finalizing classes, methods and variables.

iii) abstract 
abstract modifier is used to create abstract classes and abstract methods.
Etc...
------------------------------------------------
Section IV: Java Data Types

1) What is Data type?

Data Type is a classification of the type of data that a variable or Constant or Method can hold in Computer program.

Ex: character, integer, float, boolean etc...

Java Supports Explicit declaration of data types.

2) What is Explicit declaration of data types?

We need to specify the data type before declaring variables or Constants.

Examples in Java:

int a;
int b=100;
int c, d, e;
int f=10, g=20, h=30;

3) What are the categories of Data Types in Java?

Two categories of Data Types in Java

i) Primitive Data Types
ii) Non Primitive Data Types

4) What are Primitive Data Types in Java?

Primitive Data Types (8 Data types) in Java are,

i) Integer Data Types 

a) byte (8 bits), Ex: byte a = 10;
b) short (16 bits), Ex: short b =10000;
c) int (32 bits), Ex: int c = 1000000;
d) long (64 bits), Ex: long d = 100000000000000000000;

ii) Relational data types (Numbers with decimal places)

e) float (32 bits), Ex: float x = 10.23;
f) double (64 bits), Ex: double y = 1245.34567897345;

iii) Characters

g) Character, Ex: char z = "A";

iv) Conditional

h) Boolean, Ex: boolean x = true/false;

5) What are Non Primitive Data Types?

Non Primitive Data Types Or Reference data Types in Java are Objects and Arrays.

Example:

Sample obj = new Sample();
------------------------------------------------
Section V: Java Variables

1) What is Variable?

A named memory location to store the temporary data within a program.

Two types of memory in Computer environment.

i) Primary memory (RAM)
ii) Secondary memory (ROM - HDD, DVD, USB drive etc...)

Note: Variables store in Primary memory (RAM).

2) How to Declare Variables in Java?

Java supports Explicit declaration of variables.

Syntax:

dataType variableName;

Example:
int a;
char x;

3) How to assign values to Variables in Java?

Two types of Assign values to variables,

i) Initialization

int a =100; //Initialization

ii) Reading (Reading using Input devices, from files)

int a =100;
int b;
b=a;//Reading

4) What are Variable Naming restrictions in Java?

i) Java Variables are case sensitive.
ii) Java variable names should start with a letter or $ or _
iii) Variable names should not match with Java Keywords/Reserved words
iv) Must be unique in the scope of declaration.
v) Must not exceed 255 characters.
Etc...

5) What are the types of Variables in Java?

i) Local Variables
Local variables are declared in methods or blocks.

ii) Instance Variables
Instance Variables are declared in a class but outside of a method or any block.

iii) Class / Static Variables
Static Variables are declared as static, these can't be local.
------------------------------------------------
Section VI: Operators in Java

1) What are Operators?

Operators are used to perform mathematical, comparison and logical operations.

2) What are the categories of Operators in Java?

i) Arithmetic Operators
ii) Relational Operators
iii) Assignment Operators
iv) Logical Operators
Etc...

3) What are Arithmetic Operators in Java?

Arithmetic Operators in Java are,

i) Addition + (Addition, String concatenation)
ii) Subtraction - (Subtraction, Negation)
iii) Multiplication *
iv) Division /
v) Modules %
vi) Increment ++
vii) Decrement --

Note: Arithmetic Operators return value based result.

4) Give an Example for Arithmetic Operators?

public static void main(String[] args) {

int a =10, b =5;
String c = "Selenium", d ="Testing";

System.out.println("Addition of a, b is: " +(a+b));//Addition of a, b is: 15
System.out.println(a+b);//15
System.out.println(a-b);//5
System.out.println(a*b);//50
System.out.println(a/b);//2
System.out.println(a % b);//0
b=10;
a = ++b;
System.out.println(a);//11
b=10;
a = --b;
System.out.println(a);//9
}
}

5) What are Relational Operators in Java?

Relational Operators in Java are,

i) == 
ii) !=
iii) >
iv) >=
v) <
vi) <=

Note: Relational Operators return Boolean/logical result (true/false)

6) Give an Example for Relational Operators?

public static void main(String[] args) {
int a =10, b =20;

System.out.println(a>b);//false
System.out.println(a>=b);//false
System.out.println(a<b);//true
System.out.println(a<=b);//true
System.out.println(a==b);//false
System.out.println(a!=b);//true
}
}

7) What are Logical Operators in Java?

Important Logical Operators in Java are,

i) Logical Not operator !
ii) Logical And Operator &&
iii) Logical Or operator ||

8) Give an Example for Logical Operators?

Example for Logical Operators,

public static void main(String[] args) {
boolean a =true, b =false;

System.out.println(!(a && b));//true
System.out.println(a && b);//false
System.out.println(a || b);//true
}

9) What are the important Assignment Operators in Java?

Important Assignment Operators are,

i) Assignment  =
ii) Add and Assign +=
iii) Subtract and Assign -=
iv) Multiply and Assign *=

10) Give an Example for Assignment Operators?

public static void main(String[] args) {

int a = 10;
System.out.println(a);//10

a += 10;
System.out.println(a);//20

a -= 10;
System.out.println(a);//10

a *= 10;
System.out.println(a);//100
}
}

11) What is Operator Precedence?
------------------------------------------------
Section VII: Java Control Flow Statements

1) What are the Types of Control Flow Statements in Java?

Java has Three types of Control Flow Statements,

i) Decision Making Statements
ii) Looping Statements
iii) Branching Statements
------------------------------------------------
Section VIII: Java IO and File Handling

Section IX: String Handling in Java

Section X: Java Arrays

Section XI: Java Methods

Section XII: Exception Handling in Java

Section XIII: Java Inheritance

Section XIV: Java Polymorphism

Section XV) Java Abstraction

Section XVI: Java Encapsulation
------------------------------------------------
Also Read / watch:

Java Step by Step Videos

List of Social Bookmarking Sites

Social Bookmarking
List of Social Bookmarking Sites


1) DIIGO

2) DELICIOUS

3) Reddit

4) Only2Clicks

5) TUMBLR

6) Stumble Upon

7) Scoop

8) Twitter

9) Myspace

10) Bit.ly

11) SiteJot

12) Thinkery.me

13) Draggo.com

14) Google Bookmarks

15) Pearltrees.com

16) WattPad

17) Historio.us

18) Bookmarkee

19) Evernote

20) Aboogy

21) Box

22) Bookmark4you

23) Get Pocket

24) Educlipper.net

25) FlipDrive

26) Rememberthemilk

27) Favable

28) Buffercom

29) Linkedin

30) Tuenti

31) Symbaloo

32) LinkBoard.org

33) Instapaper

34) Mendeley

35) whitelinks.com

36) Classmates

37) VK

38) Meetup

39) Thamblr

40) Flicker
--------------------------------------------------------------
List of Social Bookmarking Web Sites for Search Engine Optimization, Social Networking, Link Popularity, and SEO Practices.

Popular Social Networking Sites/Applications are,

i) Facebook
ii) Twitter
iii) LinkedIn
iv) GooglePlus Etc...