Java Technologies

  A query inside another query is known as subquery . A subquery is also called an inner query or inner select , while the statement containing a subquery is also called an outer query or outer select . Types of subquery 1.independent subquery 2.correlated subquery INDEPENDENT SUBQUERY     The inner query and the outer query can be executed independently . They independent to each other . select * from stud where courseid =( select courseid from course where duration = 2 ) CORRELATED SUBQUERY    The inner query and the outer query are dependent on each other . The cannot be executed seperately . select sid , sname from stud s1 where s1 . courseid =   ( select avg ( courseid ) from stud s2 where s1 . sid = s2 . sid )

SQL joins are used to query data from two or more tables , based on a relationship between certain columns in these tables . TYPES OF JOINS    1. Inner join    2. Outer join        * Left outer join        * Right outer join        * Full outer join    3. Cross join    4. Self join INNER JOIN    Return rows when there is at least one match in both tables .   SYNTAX :-           SELECT column_name ( s )          FROM table_name1          INNER JOIN table_name2          ON table_name1 . column_name = table_name2 . column_name LEFT OUTER JOIN   Return all rows from the left table , even if there are ...

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 . The rule constraint is used to insert only a specified range of values into a column . create table test1 ( id int constraint pk_test primary key , sname varchar ( 50 )) select * from test1 create rule r_test as @range between 100 and 1000 sp_bindrule r_test , 'test1.id' insert into test1 values ( 101 , 'siva' ) sp_unbindrule 'test1.id' drop rule r_test

The CHECK constraint is used to limit the value range that can be placed in a column . If you define a CHECK constraint on a single column it allows only certain values for this column . If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row .   create table emp_det3 ( empno int constraint pk_empdet3 primary key , ename varchar ( 20 ), city varchar ( 20 ), state char ( 2 ) constraint ck_sta1 check ( state in( 'TN' , 'AP' , 'KL' ))) select * from emp_det3

'Unique Key(same as column level,table level,compsite unique key)' The UNIQUE constraint uniquely identifies each record in a database table .    Unique key does not allow duplicate values . Unique allows null values , but only once .Any number of unique keys can be set in a table . create table emp3 ( empno int constraint uk_emp13 unique , ename varchar ( 20 ), desg char ( 3 ), salary money , deptno int ) 'FOREIGN KEY'    Foreign key is an referential constraint . A FOREIGN KEY in one table points to a PRIMARY KEY in another table .   create table emp_det ( empno int constraint pk_empdet primary key , ename varchar ( 20 ), desg char ( 3 ), salary money , deptno int ) insert into emp_det values ( 111 , 'Vinoth' , 'TRN' , 10000.00 , 10 ) insert into emp_det values ( 112 , 'Karthik' , 'TRN' , 20000.00 , 20 ) insert into emp_det values ( 113 , 'Suresh' , '...

PRIMARY KEY   The PRIMARY KEY constraint uniquely identifies each record   in a database table . Primary keys must contain unique values . A primary key column cannot contain NULL values . Each table should have a primary key , and each table can have only ONE primary key . COLUMN LEVEL CONSTRAINT To add a primary key for a column . To add a primary key while creating a table   create table emp ( empno int constraint pk1_emp primary key , ename varchar ( 20 ), desg char ( 3 ), salary money , deptno int ) TABLE LEVEL CONSTRAINT   To add a primary key for a table . COMPOSITE PRIMARY KEY     If we add primary key for more than one column it is called   as composite primary key . create table emp2 ( empno int , ename varchar ( 20 ), desg char ( 3 ), salary money , deptno int , constraint pk_emp2 primary   key ( empno , deptno )) Adding primary key for an e...

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 ). We will focus on the following constraints : 1. Primary key Constraint 2. Unique   Constraint 3. Foreign Key Constraint 4. Check Constraint 5. Default 6. Rule 7. Null 8. NOT NULL