Queries

1) Create Table Query: CREATE TABLE customer ( S_no integer primary key, Cid integer not null, C_no integer check(c_no>0), First_Name char(50), Address char(50), Birth_Date date);

2) Insert rows: Insert into customer(S_no,Cid,C_no,First_name,Address,Birth_Date) Values(1,1,1,”Test”,”Dubai”,#05/09/1989#); We insert date using #.

3) Alter Table Query: In Alter command, you can  Add a column  Drop a column  Alter a column’s datatype or constraint 1. Adding a column Alter table customer (add country char(50)); 2. Dropping a column Alter table customer (drop country char(50)); 3. Alter a column’s datatype or constraint Alter table customer (alter country char(25) not null);

4) Drop and Truncate Table:

The difference between them is that DROP table command DELETES THE TABLE from the database and TRUNCATE table command DELETES ONLY THE ROWS OF THAT TABLE Drop table customer; Truncate table customer;

5) Count & Distinct command: Count counts the number of rows of a particular column you have selected Distinct returns the unique values of a particular column you have selected When combined: Select count(distinct first_name) from customer; This will first return all the different first names in the table customer and then out of only THAT particular rows returned, it will count and print out the number

6) AVG, MIN, MAX,SUM I think this you people already know…Just the syntaxes: Select avg(cid) from customer; Select max(cid) from customer; Select min(cid) from customer; Select sum(cid) from customer;

7) Group by, Having clause: Group by clause when used, returns the rows by grouping them according to the particular column you have selected. By default it is in ascending order. Select first_name from customer group by address; This command will return the first_name of customers NOT in the ascending order of their names but in the ascending order of the address they belong to…so the first preference is given to address…if the records are abu dhabi, sharjah, deira…dose ppl’s first_name will be printed accordingly…if you want it in DESCENDING order, just give it as: Select first_name from customer group by address desc;

Having clause is same like Where clause but the DIFFERENCE is Where clause cannot use aggregate functions(SUM,MIN,MAX..etc.) but Having clause can: Select first_name from customer having max(cid); Oh ya! There is Order by clause also but it is mainly used to sort a single column in ascending or descending order…the main difference between these three is kinda difficult to explain here..so ask me in class if u want!

8) Sub query and joints… Ahh please refer to the notes or ask me in class…

9) Table and column alias: Select first_name As The_First_Name from customer cust; The_First_Name – Column Alias (Note that we have used “As”) Cust – Table alias

10) Almost forgot…between clause: Select first_name from customers where Birth_Date BETWEEN #05/09/1985# AND #01/31/2000#; Birth_Date is of date datatype, so we have to use hash-# ok?? It’s not the same as character datatype…