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,
