SQl Tutorial and various Quiries

                    How to Create a table in SQL

General syntax for creating a Table is as follows

create table table_name(field1_name datatype(size), field2_name datatype(size),...........);

Eg,

CREATE TABLE SALESMAN

(SNUM NUMBER(4) PRIMARY KEY,

SNAME VARCHAR(20),

CITY VARCHAR(10),

COMMISSION NUMBER(6,2));

this query will create a table name SALESMAN

See the structure of salesman table

How can we see the structure of the table which we created previously.
Have look at the query below.

DESC SALESMAN;

Insert values into table salesman

There are two ways in which we can insert the values in any table.

INSERT INTO SALESMAN VALUES(1001,'PIYUSH','LONDON',.12);

And the second way is

INSERT INTO SALESMAN VALUES(&SNUM,'&SNAME','&CITY',&COMMISSION);

This will generate the following output:-
Enter value for snum: 1001
Enter value for sname: PIYUSH
Enter value for city: LONDON
Enter value for commission: .11

and the insert the following value

SNUM SNAME          CITY              COMMISSION
--------------------------------------------------------  
    1001 PIYUSH        LONDON            12%
    1002 NIRAJ           SURAT                 13%
    1003 MITI              LONDON            11%
    1004 RAJESH        BARODA            15%
    1005 ANAND        NEW DELHI       10%
    1006 RAM              PATAN                  10%
    1007 LAXMAN     BOMBAY              09%

As said earlier create a similar table named as Customer

CREATE TABLE CUSTOMER
(CNUM NUMBER(4) PRIMARY KEY,
 CNAME VARCHAR2(20),
CITY VARCHAR2(25),
RATING NUMBER(3),
SNUM NUMBER(3) CONSTRAINT SNUM_FKEY REFERENCES SALESMAN);

And insert the following values into Customer Table.


CNUM CNAME         CITY     RATING     SNUM
--------------------------------------------------------  
    2001 HARDIK           LONDON    100      1001
    2002 GITA               ROME         200      1003
    2003 LAXIT           SURAT        200      1002
    2004 GOVIND       BOMBAY    300     1002
    2005 CHANDU      LONDON    100      1001
    2006 CHAMPAK   SURAT        300     1007
    2007 PRATIK        ROME          100      1004

Query in our lab manual

Hey friends today I will solve the query in our labmanual......

1. Produce the order no ,amount and date of all orders.

2. Give all the information about all the customers with salesman number 1001.

3. Display the information in the sequence of city, sname, snum, and Commission.