OPERATORS

COMPARISON OPERATORS

>,<,!=  are comparison operators.

for example to display e_ name,job,salary from table employee_668 where job is not manager.

SELECT E_NAME,JOB,SALARY FROM EMPLOYEE_668 WHERE JOB!=MANAGER;

LOGICAL OPERATORS

AND,OR are logical operators.

 for eg. to display e_name whose salary is less than 2000 and whose job is clerk.

SELECT E_NAME FROM EMPLOYEE_668 WHERE SALARY<2000 AND JOB=CLERK;

for eg, to display e_name whose salary is greater than 10000 or whose job is manager.

SELECT E_NAME FROM EMPLOYEE_668 WHERE SALARY>10000 OR JOB=MANAGER;

SPECIAL OPERATORS

BETWEEN,IN  are special operators.

for eg.to display e_name from employee_668 where salary is between2000 and 3000

SELECT E_NAME FROM EMPLOYEE_668 WHERE SALARY BETWEEN 2000 AND 3000;

for eg. list details of employee_668 who are not working in department number10,20,30,40,50.

SELECT*FROM WHERE DEPT NO IN(10,20,30,40,50);

FUNCTIONS

5 TYPES OF FUNCTION

DML STATEMENTS

SELECT

Used to select particular coloumn from a table

 

Syntax: SELECT*FROM TABLE NAME;

INSERT

Used to create a row in the data base object and insert statement canhave many forms.

HOW TO INSERT MULTIPLE ROWS

 syntax:INSERT INTO TABLE NAME VALUES(V1,V2..............);

INSERT USING SUBSTITUTION VARIABLES

Syntax:INSERT INTO TABLE NAME VALUES(&COL1,&COL2.............);

HOW TO COPY ROWS FROM ONE TABLE TO OTHER

Syntax:INSERT INTO TABLE1(COL1,COL2...........) SELECT COL1,COL2.......................
            FROM TABLE2 WHERE CONDITION;

UPDATE 

used to update  existing records in the table.

syntax: UPDATE TABLE NAME SET COLOUMN NAME=VAL1,COL2=VAL2............
             WHERE CONDITION;

UPDATE 2 COLOUMNS BY USING A SUB QUERY

Syntax: UPDATE TABLE NAME SET(COL1,COL2...............)=((SUB QUERY1),(SUB             QUERY2)................) WHERE CONDITION;

DELETE

 Syntax: DELETE FROM TABLE NAME[WHERE CONDITION];
              DELETE*FROM TABLE NAME;

TRUNCATE

Used to delete all th data from particular table

syntax:truncate table table name;

MERGE

 

Used to insert new records and to update existing records based on weither or not a condition matches.

syntax: MERGE INTO TABLE NAME USING TABLE REFERENCE
            ON CONDITION WHEN MATCHED THEN UPDATE      
            SET COL1=VAL1[,......................]
            WHEN NOT MATCHED THEN INSERT[COL1,...........] VALUES[VAL1.......];

ARITHEMATIC OPERATIONS IN QUERIES ?

ADDITION

SELECT COLOUMN NAME+1000  FROM TABLE NAME;

To give new name to the operated coloumn

SELECT COLOUMN NAME +1000 NEW COLOUMN NAME FROM TABLE NAME;

SUBTRACTION

SELECT COLOUMN NAME-10 FROM TABLE NAME;

MULTIPLICATION

SELECT COLOUMN NAME *10 FROM TABLE NAME;

DIVISION 

SELECT COLOUMN NAME /10 FROM TABLE NAME;

CERTAIN QUERIES TO DESCRIBE,ALTER,DISTINCT A TABLE AND INSERT VALUES,DISAPLY A PARTICULAR COLOUMN IN A TABLE ?

TO DESCRIBE

describe table name;

TO INSERT VALUES

to insert values in table employee

create table employee_732

(

e_name char(10),

e_id varchar(5),

e_salary number(6),

e_commission varchar(5)

);

describe employee_732;

to insert values in a table INSERT command is used.for example to insert values in above table

INSERT INTO EMPLOYEE_732 VALUES('ram','a101',15000,'45per');

if we want to insert values in some particular attributes of table employee_668  then

INSERT INTO EMPLOYEE_732(e_name) VALUES('ram');

TO DISPLAY A PARTICULAR COLOUMN

to display a table we use

SELECT*FROM TABLENAME;

to display a particular coloumn we use

SELECT COLOUMN NAME FROM TABLE NAME;

TO ALTER A PARTICULAR TABLE

ALTER TABLE TABLE NAME ADD COLOUMN NAME DATA TYPE(SIZE);

for example to add coloumn e_phnno in above created table 

ALTER TABLE EMPLOYEE_668 ADD E_PHNNO NUMBER(10);

TO GET DISTINCT VALUES

to get distinct values from a table

SELECT DISTINCT*TABLE NAME;

to get distinct values from a particular coloumn

SELECT DISTINCT COLOUMN NAME FROM TABLE NAME;

TO APPLY CONDITION IN A PARTICULAR QUERY AND ORDER BY CLAWS.

to apply condition WHERE command is used

syntax : SELECT*FROM TABLE NAME WHERE CONDITION;

for example to select coloumns from table employee_668 where salary is greater than or equal to 20000

SELECT*FROM EMPLOYEE_668 WHERE SALARY>=20000;

ORDER BY CLAWS

FOR ASCENDING ORDER

SELECT COLOUMN NAME FROM TABLE NAME WHERE CONDITION ORDER BY COLOUMN NAME;

FOR DESCENDING ORDER

SELECT COLOUMN NAME FROM TABLE NAME WHERE CONDITION ORDER BY COLOUMN NAME DESC;

HOW TO CREATE A TABLE USING DBMS ?

create table tablename

(

coloumn1 datatype(size),

coloumn2 datatype(size),

.

.

.

coloumn10 datatype(size)

);

for example TO CREATE A TABLE EMPLOYEE_732

create table employee_668

(

e_name char(10),

e_id varchar(5),

e_salary number(6),

e_commission varchar(5)

);

HTML ORDERED LIST | HTML NUMBERED LIST

HTML Ordered List or Numbered List displays elements in numbered format. The HTML ol tag is used for ordered list. There can be different types of numbered list:

• Numeric Number (1, 2, 3)
• Capital Roman Number (I II III)
• Small Romal Number (i ii iii)
• Capital Alphabet (A B C)
• Small Alphabet (a b c)

To represent different ordered lists, there are 5 types of attributes in <ol> tag.

Type	Description
Type "1"	This is the default type. In this type, the list items are numbered with numbers.
Type "I"	In this type, the list items are numbered with upper case roman numbers.
Type "i"	In this type, the list items are numbered with lower case roman numbers.
Type "A"	In this type, the list items are numbered with upper case letters.
Type "a"	In this type, the list items are numbered with lower case letters.

---

HTML Ordered List Example

Let's see the example of HTML ordered list that displays 4 topics in numbered list. Here we are not defining type="1" because it is the default type.

1. <ol>  
2.  <li>HTML</li>  
3.  <li>Java</li>  
4.  <li>JavaScript</li>  
5.  <li>SQL</li>  
6. </ol>  

start attribute

The start attribute is used with ol tag to specify from where to start the list items.

<ol type="1" start="5"> : It will show numeric values starting with "5".

<ol type="A" start="5"> : It will show capital alphabets starting with "E".

<ol type="a" start="5"> : It will show lower case alphabets starting with "e".

<ol type="I" start="5"> : It will show Roman upper case value starting with "V".

<ol type="i" start="5"> : It will show Roman lower case value starting with "v".

1. <ol type="i" start="5">  
2.  <li>HTML</li>  
3.  <li>Java</li>  
4.  <li>JavaScript</li>  
5.  <li>SQL</li>  
6. </ol>  

HTML Unordered List | HTML Bulleted List

HTML Unordered List or Bulleted List displays elements in bulleted format. The HTML ul tag is used for the unordered list. There can be 4 types of bulleted list:

• disc
• circle
• square
• none

To represent different ordered lists, there are 4 types of attributes in <ul> tag.

Type	Description
Type "disc"	This is the default style. In this style, the list items are marked with bullets.
Type "circle"	In this style, the list items are marked with circles.
Type "square"	In this style, the list items are marked with squares.
Type "none"	In this style, the list items are not marked .

---

HTML Unordered List Example

1. <ul>  
2.  <li>HTML</li>  
3.  <li>Java</li>  
4.  <li>JavaScript</li>  
5.  <li>SQL</li>  
6. </ul>  

HTML Description List | HTML Definition List

HTML Description List or Definition List displays elements in definition form like in dictionary. The <dl>, <dt> and <dd> tags are used to define description list.

The 3 HTML description list tags are given below:

1. <dl> tag defines the description list.
2. <dt> tag defines data term.
3. <dd> tag defines data definition (description).

1. <dl>  
2.   <dt>HTML</dt>  
3.   <dd>is a markup language</dd>  
4.   <dt>Java</dt>  
5.   <dd>is a programming language and platform</dd>  
6.  <dt>JavaScript</dt>  
7.  <dd>is a scripting language</dd>  
8.   <dt>SQL</dt>  
9.   <dd>is a query language</dd>   
10. </dl>  

HTML Form

An HTML form is a section of a document which contains controls such as text fields, password fields, checkboxes, radio buttons, submit button, menus etc.

An HTML form facilitates the user to enter data that is to be sent to the server for processing.

---

Why use HTML Form

HTML forms are required if you want to collect some data from of the site visitor.

For example: If a user want to purchase some items on internet, he/she must fill the form such as shipping address and credit/debit card details so that item can be sent to the given address.

---

HTML Form Syntax

1. <form action="server url" method="get|post">  
2.   //input controls e.g. textfield, textarea, radiobutton, button  
3. </form>  

---

HTML Form Tags

Let's see the list of HTML 5 form tags.

Tag	Description
<form>	It defines an HTML form to enter inputs by the used side.
<input>	It defines an input control.
<textarea>	It defines a multi-line input control.
<label>	It defines a label for an input element.
<fieldset>	It groups the related element in a form.
<legend>	It defines a caption for a <fieldset> element.
<select>	It defines a drop-down list.
<optgroup>	It defines a group of related options in a drop-down list.
<option>	It defines an option in a drop-down list.
<button>	It defines a clickable button.

HTML 5 Form Tags

Let's see the list of HTML 5 form tags.

Tag	Description
<datalist>	It specifies a list of pre-defined options for input control.
<keygen>	It defines a key-pair generator field for forms.
<output>	It defines the result of a calculation.

---

HTML TextField Control

The type="text" attribute of input tag creates textfield control also known as single line textfield control. The name attribute is optional, but it is required for the server side component such as JSP, ASP, PHP etc.

1. <form>  
2.     First Name: <input type="text" name="firstname"/> <br/>  
3.     Last Name:  <input type="text" name="lastname"/> <br/>  
4.  </form>  

---

Label Tag in Form

It is considered better to have label in form. As it makes the code parser/browser/user friendly.

If you click on the label tag, it will focus on the text control. To do so, you need to have for attribute in label tag that must be same as id attribute of input tag.

1. <form>  
2.     <label for="firstname">First Name: </label>  
3.               <input type="text" id="firstname" name="firstname"/> <br/>  
4.    <label for="lastname">Last Name: </label>  
5.               <input type="text" id="lastname" name="lastname"/> <br/>  
6.  </form>  

---

HTML Password Field Control

The password is not visible to the user in password field control.

1. <form>  
2.     <label for="password">Password: </label>  
3.               <input type="password" id="password" name="password"/> <br/>  
4. </form>  

---

HTML 5 Email Field Control

The email field in new in HTML 5. It validates the text for correct email address. You must use @ and . in this field.

1. <form>  
2.     <label for="email">Email: </label>  
3.               <input type="email" id="email" name="email"/> <br/>  
4. </form>  

---

Radio Button Control

The radio button is used to select one from multiple options. It is used in gender, quiz questions etc.

If you use one name for all the radio buttons, only one radio button can be selected at a time.

1. <form>  
2.     <label for="gender">Gender: </label>  
3.               <input type="radio" id="gender" name="gender" value="male"/>Male  
4.               <input type="radio" id="gender" name="gender" value="female"/>Female <br/>  
5. </form>  

---

Checkbox Control

The checkbox control is used to check multiple options from given checkboxes.

1. <form>  
2. Hobby:<br>  
3.               <input type="checkbox" id="cricket" name="cricket" value="cricket"/>  
4.                  <label for="cricket">Cricket</label>  
5.               <input type="checkbox" id="football" name="football" value="football"/>  
6.                  <label for="football">Football</label>  
7.               <input type="checkbox" id="hockey" name="hockey" value="hockey"/>  
8.                  <label for="hockey">Hockey</label>  
9. </form>  

---

HTML Form Example

Let's see a simple example of creating HTML form.

1. <form action="#">  
2. <table>  
3. <tr>  
4.     <td class="tdLabel"><label for="register_name" class="label">Enter name:</label></td>  
5.     <td><input type="text" name="name" value="" id="register_name" style="width:160px"/></td>  
6. </tr>  
7. <tr>  
8.     <td class="tdLabel"><label for="register_password" class="label">Enter password:</label></td>  
9.     <td><input type="password" name="password" id="register_password" style="width:160px"/></td>  
10. </tr>  
11. <tr>  
12.     <td class="tdLabel"><label for="register_email" class="label">Enter Email:</label></td>  
13.     <td  
14. ><input type="email" name="email" value="" id="register_email" style="width:160px"/></td>  
15. </tr>  
16. <tr>  
17.     <td class="tdLabel"><label for="register_gender" class="label">Enter Gender:</label></td>  
18.     <td>  
19. <input type="radio" name="gender" id="register_gendermale" value="male"/>  
20. <label for="register_gendermale">male</label>  
21. <input type="radio" name="gender" id="register_genderfemale" value="female"/>  
22. <label for="register_genderfemale">female</label>  
23.     </td>  
24. </tr>  
25. <tr>  
26.     <td class="tdLabel"><label for="register_country" class="label">Select Country:</label></td>  
27.     <td><select name="country" id="register_country" style="width:160px">  
28.     <option value="india">india</option>  
29.     <option value="pakistan">pakistan</option>  
30.     <option value="africa">africa</option>  
31.     <option value="china">china</option>  
32.     <option value="other">other</option>  
33. </select>  
34. </td>  
35. </tr>  
36. <tr>  
37.     <td colspan="2"><div align="right"><input type="submit" id="register_0" value="register"/>  
38. </div></td>  
39. </tr>  
40. </table>  
41. </form>