SQL Query by Parth Dhorda: July 2014

automaty do gier

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