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 - dropDML: Data Manipulation Language
- select - insert - update - deleteMicrosoft 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';
