Create Table Statement and Constraints Assignment

Table Creation and Data insertion:
Create following Tables with required constraints (as mentioned) using SQL DDL. 

Table: Student

sid (int) Primary Key	sname Varying Char (10)	sbranch Char(5)	sage (int) (not greater than 25)
1001	Kamal	IT	18
1004	Yogesh	IT	17
1011	Mani	CSE	20
1022	Farooq	EEE	21
1027	Girija	CSE	24
1030	Mousmi	CSE	22
1036	Rajanish	EEE	21
1052	Praneet	EEE	21
1059	Joseph	CSE	17
1063	Lahiri	IT	21

Table: Course

cid (int) PK	Cname (chars 4 and unique)	units (int)
10	OS	3
20	DBMS	4
30	CNW	5
40	PHY	3

Table: StudentCourse

stid PK1 and FK to sid of student	cno PK2 and FK to cid of course
1001	20
1001	30
1004	10
1004	20
1004	30
1004	40
1027	20
1027	30
1036	10
1036	30
1036	20
1059	10
1063	20

Solution

DDL: Data Definition Language
 - create 
 - alter 
 - drop

DML: Data Manipulation Language 
 - select 
 - insert 
 - update 
 - delete 

Microsoft SQL Server
How to add a "Check Constraint" on a column:
CREATE TABLE CountryList (
  Id INT IDENTITY PRIMARY KEY,
  CountryName VARCHAR(255) NOT NULL,
  CountryPopulation INT CHECK(CountryPopulation > 0)
)

ALTER TABLE student ADD CONSTRAINT CheckSage CHECK (sage > 25);

Adding a "Foreign Key"
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
); 

Creating a 'Composite Primary Key'
Create table StudentCourse ( stid  integer FOREIGN Key REFERENCES Student (sid) , 
cno integer Foreign Key References Course (cid),
PRIMARY KEY (stid, cno) ); 

A mistake that students make...
Create table StudentCourse ( stid  integer Primary Key FOREIGN Key REFERENCES Student (sid), 
cno integer PRIMARY key Foreign Key References Course (cid) );

Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'StudentCourse'. 

Oracle DB

SQL> create table student_ashish_12345 (sid int primary key, sname varchar2(10), sbranch char(5), sage int constraint stud_sage_ck check (sage <= 25));

SQL> create table course_ashish_12345 (cid int primary key, cname char(4) unique, units int);

SQL> create table studentcourse_ashish_12345 (stid int, cno int, constraint sc_pk primary key(stid, cno), constraint sc_fk1 foreign key (stid) references bits_student (sid), constraint sc_fk2 foreign key (cno) references bits_course (cid));

SQL> select table_name from tabs where table_name like '%ASHISH_12345';

Tags: Database,Technology,