SQL

SQL

Content

* Introduction to SQL
* DDL
* DML
* Creating Database
* Creating Table
* Deleting Table
* Deleting Database
* Data Types
* Inserting Data into Table
* Updating Table
* Deleting Table Data
* Selecting Table Data
* Constraints
* NOT NULL
* UNIQUE Constraint
* Primary Key
* Foreign Key
* Default Constraint
* Auto Increment

Introduction to SQL

* Most of the actions you need to perform on a database are done with SQL statements.
* Some database systems require a semicolon at the end of each SQL statement. 
* SQL is a language common to access almost all the DBMS software.
* SQL can be divided into two parts 
 - The Data Manipulation Language (DML) 
 - The Data Definition Language (DDL)

DDL

* The DDL part of SQL permits database tables to be created or deleted.
* It specify links between tables, and impose constraints between tables. 
* The most important DDL statements in SQL are:
 - CREATE DATABASE ==> creates a new database 
 - ALTER DATABASE ==> modifies a database 
 - CREATE TABLE ==> creates a new table 
 - ALTER TABLE ==> modifies a table 
 - DROP TABLE ==> deletes a table
 - DROP DATABASE ==> delete a database

DML

* The DML is Data Manipulation Language.
* It is the set of commands to manipulate application data stored in the database.
* The query and update commands form the DML part of SQL:
 - SELECT ==> extracts data from a database 
 - UPDATE ==> updates data in a database 
 - DELETE ==> deletes data from a database 
 - INSERT ==> INTOinserts new data into a database

Creating Database

* Any user of MySQL can create database, provided he has been privileged. Providing privileges will be study in later chapters
* User root is predefined user and has all the privileges
* The CREATE DATABASE statement is used to create a database.

CREATE DATABASE database_name;

* Database is a collection of tables.


Creating Table

* After creating database you can create tables in it. First select a database through use command.
* Following is the syntax to create table

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
);


Describing table


Deleting Table

* Deleting table is different from deleting content of the table. 
* Deleting table means the whole structure is deleted, thus all data stored in the table will also be removed.
* The DROP TABLE statement is used to delete a table.

DROP TABLE table_name;


Deleting Database

* The DROP DATABASE statement is used to delete a database. 

DROP DATABASE database_name;


Data Types

* SQL support variety of data types.

Data Type ==> Description 

char(size) ==> Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters 
varchar(size) ==> Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type 
TEXT==> Holds a string with a maximum length of 65,535 characters 
MEDIUMTEXT==> Holds a string with a maximum length of 16,777,215 characters 
LONGTEXT ==> Holds a string with a maximum length of 4,294,967,295 characters 

Data Type ==> Description 

enum(x,y,z) ==> Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted
int(size) ==> -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED. The maximum number of digits may be specified in parenthesis 
float(size,d)==> The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. It occupies 4 bytes.
double(size,d)==> A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. It occupies 8 bytes.

Data Type ==> Description 

date ==> A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' 
time ==> A time. Format: HH:MM:SS
The supported range is from '-838:59:59' to '838:59:59' 
bigint(size)==>  The signed range is -9223372036854775808 to 9223372036854775807.  
mediumint(size)==> The signed range is -8388608 to 8388607. 

Inserting Data into table

* The INSERT INTO statement is used to insert a new row in a table

INSERT INTO table_name 
(column1, column2, column3,...) 
VALUES (value1, value2, value3,...); 




Updating table

* The UPDATE statement is used to update existing records in a table.

UPDATE table_name SET column1=value, column2=value2,...
WHERE some_column=some_value;


Deleting Table data

* The DELETE statement is used to delete rows in a table.

DELETE FROM table_name
WHERE some_column=some_value;

Selecting data from table(s)

* The SELECT statement is used to select data from a database. 

SELECT column_name(s) FROM table_name;

* Above SELECT command is to fetch all the rows from the table with selected set of columns.
* SELECT command can also be used to retrieve all the rows from the table with all its columns. In such case write asterisk (*) in the place of column names
SELECT * FROM table_name;

* The WHERE clause is used to extract only those records that fulfill a specified criterion. 

SELECT column_name(s)
FROM table_name
WHERE column_name operator value;

Constraints

* Constraints are used to limit the type of data that can go into a table
* Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
* Following are the constraints:
 - NOT NULL
 - UNIQUE
 - PRIMARY KEY
 - FOREIGN KEY
 - DEFAULT

NOT NULL

* The NOT NULL constraint enforces a column to NOT accept NULL values.
* The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. 
* You can define any of the column NOT NULL as mentioned in the following syntax

CREATE TABLE table_name
(
column_name1 data_type NOT NULL,
column_name2 data_type NOT NULL,
column_name3 data_type,
....
);

UNIQUE Constraint

* The UNIQUE constraint uniquely identifies each record in a database table. 
* The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns 
* Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. 

Example

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
);

* You can apply Unique constraint even after creating table using alter command

ALTER TABLE Persons
ADD UNIQUE (P_Id);

Primary Key
* The PRIMARY KEY constraint uniquely identifies each record in a database table. 
* A primary key column cannot contain NULL values.
* Primary keys must contain unique values.
* Each table should have a primary key, and each table can have only ONE primary key

Example

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);

* You can define primary key even after creating table using alter command

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id);

Foreign Key

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

Example

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

* Foreign key may repeat but value must exist in the master table where it is a primary key.
* You can add foreign key constraint even after creating the table using alter command
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id);

Default Constraint

* The DEFAULT constraint is used to insert a default value into a column.
* The default value will be added to all new records, if no other value is specified. 

Example

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Bhopal'
);

* You can set default constraint even after creating the table

ALTER TABLE Persons
ALTER City SET DEFAULT 'Bhopal';

Auto Increment

* Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. 

Example

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);

<<Prev     Home     Next>>

Java Script

JavaScript

Content

* Introduction to Javascript
* Where to write script block
* Javascript Statements
* Javascript Comments
* Variables
* Data Types
* Arrays
* Alert box
* Confirm box
* Prompt box

Introduction to Javascript

* JavaScript is used in web pages to add functionality, validate forms, communicating with server and read write html elements.
* JavaScript is a client side scripting language.
* JavaScript is an interpreted language, rather than compiled language.
* As the browser goes through the JavaScript, it passes it to a special program called interpreter, which converts the JavaScript to the machine code your computer understands
* Browsers come equipped with JavaScript interpreters.
* Initially it was known as LiveScript
* It was first available with Netscape Navigator 2.
* Netscape decided to change its name to JavaScript as Java was the hot technology of the time.
* Following is the script tag for javascript

<script type="text/javascript">
...
...
</script>

Where to write Script Block

* Either between <head> and </head> or between <body> and </body>
* If we want to run the <script> before page loads then it is kept in the <head> section.
* If we want to run the <script> after the page loads so that it can interact with HTML elements then it is kept in the <body> section.
* JavaScript can also be placed in external files.
* External script cannot contain the <script></script> tags
* <script type="text/javascript" src="myscript.js"> </script>

JS Statements

* JavaScript is a sequence of statements to be executed by the browser.
* JavaScript is case sensitive language.
* Example

<script type="text/javascript">
document.write("JavaScript Example");
</script>
* document is an object
* write is a function or method used to print message on web page
* It is normal to add a semicolon at the end of each executable statement
* However, semicolon is optional according to the JavaScript standards

JS Comments

* Comments can be added to explain the JavaScript, or to make the code more readable.
* Single line comments start with //.
* Multi line comments start with /* and end with */.

Variables

* JavaScript variables are used to hold values or expressions.
* Variable names are case sensitive.
* Variable names must begin with a letter or the underscore character
* A variables value can change during the execution of a script
* Declaring JavaScript variable with var keyword
* var x;

The above declaration creates a variable name x and contains nothing.
However we can initialize variable during declaration.

* var x=2;
If you redeclare a JavaScript variable, it will not lose its value.
* When you assign a text value to a variable, use quotes around the value.
* var y="saurabh";
If you assign values to variables that have not yet been declared, the variables will automatically be declared as global variables
* N="Saurabh";

Important note

* Every non zero value is true, 0 is false
* You can compare two strings unlike C language

Example

<script type="text/javascript">
var s1="amit";
var s2="amar";
if(s1==s2)
    document.write("Strings are same");
else
    document.write("Strings are not same");
</script>

Data Type

* Javascript has dynamic types, this means that the same variable can be used as different types
* You can assign boolean values
* var x=true;
* var x=false;

Displaying Variable value
To display a variables value we just have to pass it’s name to the document.write( ) method.
var a=10;
document.write(a);
To print a variable’s value with some text we use operator " + "
var a=10;
document.write(‘Value of a is ‘+a);

Arrays

* var students= new Array();

students[0]="Amit";
students[1]="Mita";
students[2]="bunty";

* var students= new Array("Amit","Mita","bunty");
* var students= ["Amit","Mita","bunty"];
* You can always use length property of array
* For example in the above case students.length is 3.

Alert Box

* An alert box is often used if you want to make sure information comes through  the user
* When an alert box pops up, the user will have to click "OK" to proceed.
* alert("some text");

Example

<script type="text/javascript">
alert("Welcome to home page");
</script>

Confirm Box

* A confirm box is often used if you want the user to verify or accept something.
* When a confirm box pops up, the user will have to click either "OK" or "Cancel" to proceed
* If the user clicks "OK", the box returns true. If the user clicks "Cancel", the box returns false
* variable=confirm("some text");
* Variable would get either true or false as a value.

Example
<script type="text/javascript">
var  x = confirm("Are you interested in learning?");
if(x)
    document.write("Good, you are a geek");
else
    document.write("Ok, clap for others!!!");
</script>

Prompt Box

* A prompt box is often used if you want the user to input a value before entering a page.
* When a prompt box pops up, the user will have to click either "OK" or "Cancel" to proceed after entering an input value
* If the user clicks "OK" the box returns the input value. If the user clicks "Cancel" the box returns null
* var x=prompt("some text", "default value");

Example

<html>
 <head>
  <title> JAVASCRIPT </title>
  <script type="text/javascript" >
   var x=parseInt(prompt("Enter a number","0"));
   var i=1;
   while(i<=10)
   {
     a=i*x;
     document.write(x+" x "+i+" = "+a+"<br/>");
     ++i;
   }
  </script>
 </head>
 <body>
 </body>
</html>

<<Prev     Home     Next>>