How to create a table in sql

[varmela]

Create a table with following rules and regulations ma

1)Id should b identity
2)Name should allow only starting Letter with a to p
3)city should not allow Chennai,cbe, Salem
4) department should allo only cse,it,ece,
5) college default should b anna university
6) per should above only 70%
7) year of passing should be between 65 to 85
8) mailid should not allow null values
9)ph num should b primary key
10) designation should b unique constraints
11) salary default should b 0

[pritaeas]

So... what do you have so far?

[Schol-R-LEA]

Which RDBMS are you using (MySQL, MS SQL Server, Oracle, SQLite, PostgreSQL, MS Access, etc.)? While SQL is standardized, no RDBMS follows the standard exactly, and most have some extensions to the standard which might affect our answer.

It might also be useful to know if the SQL statements are embedded or command line, and what language they are embedded in if the former.

That having been said, the general form for CREATE TABLE is:

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

As for the constraints, well to be honest I'm not sure how you would enforce some of them, at least in standard SQL. The easiest ones would be the PRIMARY KEY and UNIQUE constraints, as those are commonly used in all SQL variants I can think of. However, the syntax for UNIQUE contraints differs from RDBMS to RDBMS. The version most widely used is:

CREATE TABLE foo (
    column int NOT NULL,
     user_id VARCHAR(128) NOT NULL UNIQUE,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

But in MySQL it is this instead:

    CREATE TABLE foo (
        column int NOT NULL,
        user_id VARCHAR(128) NOT NULL,
        name VARCHAR(100),
        PRIMARY KEY (id),  UNIQUE(user_id)
    );

AFAIK, all of them allow you to create named constraints thusly:

  CREATE TABLE foo (
        column int NOT NULL,
        user_id VARCHAR(128) NOT NULL,
        name VARCHAR(100),
        PRIMARY KEY (id),
        CONSTRAINT id_unique UNIQUE(user_id)
    );

Where id_unique is the name of the constraint.

For a fixed set of values (e.g., the Department attribute in your problem description), you can use an enumeration type if the RDBMS in question supports them.

    CREATE TABLE foo (
        bar ENUM("one", "two", "three", "four"),
        baz ENUM("A", "B", "C")
    );

The rest would be done using CHECK constraints, I think, except some RDBMSes (MySQL and SQLite specificially) don't support those.

For example, to ensure that the value of an attribute is between 0 and 100, you would have something like this (not tested code, this is just a quick example of what I mean):

    CREATE TABLE quux (
        percent int, CONSTRAINT req_percentage CHECK (percent BETWEEN 0 AND 100)
    );

The ones involving specific naming constraints would presumably use the LIKE expression with a regular expression pattern to match against, but again, I'm not sure how widely supported that would be.