BAsic sql Commands and Constaint creation

• For Create Table Syntex :

• create table my_table (first_name varchar(20),last_name     varchar(30));

• Insert data into Table

• insert into my_table(first_name,last_name )
• values ('raju','kumar1')
• or
• insert into my_table values (‘ram’,’raj’)

• Select data from Table

• Select all rows and all column from table
• select * from my_table
• Select  select rows and all column from the table

• Select  * from my_table where first_name=’raju’

• Select select column and all row from the table

• Select  last_name from my_table

• Select selected column and selected rows from tha table

• Select  first_name, last_name from mytable where   first_name in (‘raju’,’monu’)

• Table structure altration or modifications :

• Add column to existing table

• alter table my_table add mid_name varchar(20)

• Modify column to existing table

• alter table my_table alter column mid_name varchar(40)

• Drop column from existing table

• alter table my_table drop column mid_name

• Delete , Truncate and Drop operation on table

• Delete all rows from a table

• Delete from table_name
• Truncate table table_name

• Delete selected rows from table

• delete from my_table where last_name='kumar1'

• Destroyed object from database
• Drop table table_name

• Create table with identity column

• create table my_table (my_id int identity (1,1),first_name       varchar(20),last_name varchar(30));

• Insert values manually into identity column:

• set identity_insert  my_table on

• insert into my_table(my_id,first_name,last_name )
• values (3,'raju','kumar1')

• set identity_insert my_table off

How to rename a table :

Sp_rename ‘old_tablename’,’new tablename’

sp_rename  'mig_tt', 'mig_tt2'

          Create table with primary key :

create table table_name (

column_name datatype(size),

column_name datatype(size),

column_name datatype(size),

primary key (column_name )

)

create table my_primary( m_no int,

m_name varchar(20),

m_date datetime,primary key (m_no))

Add Primary key with existing column :

create table my_table( m_col1 int ,

m_col2 varchar(20),

m_col3 datetime)

alter table my_table alter column m_col1 int not null

alter table my_table add constraint pk_m_no primary key (m_col1)

create Froeign key

 CREATE TABLE Matches (
    Society_Group_Id int NOT NULL ,
    Match_Id         int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED
    Points_Against   smallint NOT NULL
 GO

 ALTER TABLE Match_Scores
  ADD CONSTRAINT [FK_Match_Scores_Matches] FOREIGN KEY (Match_Id)
   REFERENCES [Matches] ([Match_Id])
GO

 CREATE TABLE employee (emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY,
                    emp_fname CHAR(20) NOT NULL,
                    emp_lname CHAR(20) NOT NULL,
                    dept_no CHAR(4) NULL)

 GO
 CREATE TABLE works_on (emp_no     INTEGER NOT NULL,
                        project_no CHAR(4) NOT NULL,
                        job        CHAR (15) NULL,
                        enter_date DATETIME NULL,
                        CONSTRAINT prim_works PRIMARY KEY (emp_no, project_no),
                        CONSTRAINT foreign_works FOREIGN KEY (emp_no) REFERENCES employee (emp_no))
 GO

create table EmergencyContact(
EID INTEGER NOT NULL PRIMARY KEY,
 Name VARCHAR(20))
 GO

 ALTER TABLE EmergencyContact
 ADD CONSTRAINT FK_EmergencyContact_Employee
 FOREIGN KEY (EID)
 REFERENCES Employee (ID)
 GO

-- Add Unique constaint to table creation
CREATE TABLE projects (project_no   CHAR(4)  DEFAULT 'p1',
                        project_name CHAR(15) NOT NULL,
                        budget       FLOAT    NULL
                        CONSTRAINT   unique_no UNIQUE (project_no))
 GO

 drop table projects
 GO
ALTER TABLE employee
4> ADD CONSTRAINT UNQ_Name
5> UNIQUE (first_Name)
6> GO

CREATE TABLE MyTable (
6>   MyID Int IDENTITY(1,1) NOT NULL , ID Int NOT NULL
7>  , Description nVarChar(50)
8>  , Region nVarChar(10) DEFAULT 'PNW'
9>  , CONSTRAINT MyTable_PK PRIMARY KEY NONCLUSTERED (MyID,ID))
10> GO

A column-level check constraint that limits Billings to positive amounts
A statement that defines the check constraint


11> CREATE TABLE Billings
12> (BillingID       INT   NOT NULL IDENTITY PRIMARY KEY,
13> BillingTotal     MONEY NOT NULL CHECK (BillingTotal > 0))
14> GO

CREATE TABLE Billings
9> (BillingID       INT   NOT NULL IDENTITY PRIMARY KEY,
10> BillingTotal     MONEY NOT NULL,
11> PaymentTotal     MONEY NOT NULL DEFAULT 0,
12> CHECK ((BillingTotal >= 0) AND (PaymentTotal >= 0)))
13>

CREATE TABLE employee (emp_no    INTEGER NOT NULL  CONSTRAINT prim_empl PRIMARY KEY,
3>                        emp_fname CHAR(20) NOT NULL,
4>                        emp_lname CHAR(20) NOT NULL,
5>                        dept_no   CHAR(4) NULL)
6>
7> CREATE TABLE project   (project_no   CHAR(4) NOT NULL  CONSTRAINT prim_pro PRIMARY KEY,
8>                         project_name CHAR(15) NOT NULL,
9>                         budget FLOAT NULL)
10>
11> CREATE TABLE works_on1
12>             (emp_no INTEGER NOT NULL,
13>              project_no CHAR(4) NOT NULL,
14>              job CHAR (15) NULL,
15>              enter_date DATETIME NULL,
16>              CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),
17>              CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no) REFERENCES employee(emp_no) ON DELETE CASCADE,
18>              CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADE)
19>
20> -- The creation of the works_on1 table that uses the ON DELETE CASCADE and ON UPDATE CASCADE options.
21> drop table works_on1
22> GO
1>
2> drop table project
3> drop table employee
4> GO
1>