SQL statement

Select statement:

select   columns   from  table_name
select * from products
select prodid  from products
select   columns   from  table_name  order by column_name   asc/ desc
select * from products  order by prodprice
select   columns   from  table_name  where criteria
select * from products where prodprice>1000
select * from products where prodid between 1 and 10
select * from products where prodname  like 'm%'

SQL FUNCTIONS:
1- Aggregate Functions
2- Scalar Functions

select  count(*)  from products
select sum(quantity) from products
select sum(prodprice) from products
select sum(quantity) from products where prodid<=50
select invoiceno, sum(quantity) from purchase   group by  invoiceno
select invoiceno, sum(quantity) from purchase   group by  invoiceno having invoiceno=1

Selecting data from multiple tables:
Using table joins:

select  table1.columns, table2.columns from table1, table2   where   table1.column=table2.columns
select  personal.name, personal.address, fee.course, fee.advance  from  personal, fee where   personal.rollno=fee.rollno


Types of Joins:
1-Inner joins/ Join/ Equi-Join
select  table1.columns, table2.columns from table1   Join table2   on table1.column=table2.columns
select  personal.name, personal.address, fee.course, fee.advance  from  personal join  fee on  personal.rollno=fee.rollno

- Outer Joins:
     - Right outer join/right join
     - Left outer join/left join
     - Full outer join/Full join
RDBMS  -> relational database management system
- updating main table affects child table too
- deleting record from  main table affects child table too
Table Relationing:
- Primary Key Field: Field in main table/ parent table to create relationship with other tables
- Foreign Key Field:Field in sub table/ child table to create relationship with parent tables

Types of Relationships:
1- One-to-One (common field in both tables must beset as primary key field)      
2- One-to-many (common field in parent tables is set as primary key field)
3- many-to-many (use junction table  to develop this typeof relation ship)

Creating Relationship Diagram:
- right click on diagram in database objects list, select new database diagram, follow wizard steps