Assignment no.1 Data Definition Query
(Create simple tables with all constraints)
SET A
1. Create a table with following details
Table Name:- Player
Columns Column Name Column Data Type Constraints
1 player_id integer Primary key
2 Name varchar (50)
3 Birth_date date
4 Birth_place varchar(100)
Table level constraint Name should not be NULL
Answer:-
CREATE TABLE Player
(
player_id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Birth_date DATE,
Birth_place VARCHAR(100)
);
2. Create a table with following details
Table Name :-Student
Columns Column Name Column Data Type Constraints
1 Roll_no integer
2 Class varchar (20)
3 Weight numeric (6,2)
4 Height numeric (6,2)
Table level constraint Roll_no and class as primary key
Answer:-
CREATE TABLE Student
(
Roll_no INT,
Class VARCHAR(20),
Weight NUMERIC(6,2),
Height NUMERIC(6,2),
CONSTRAINT pk_student PRIMARY KEY (Roll_no, Class)
);
Set B
1. Create a table with details as given below
Table Name:- Machine
Columns Column Name Column Data Type Constraints
1 Machine_id integer primary key
2 Machine_name Varchar (50) NOT NULL, uppercase
3 Machine_type varchar(10) Type in ( ‘drilling’, ‘milling’, ‘lathe’,
‘turning’, ‘grinding’)
4 Machine_price float Greater than zero
5 Machine_cost float
Table level constraint Machine_cost less than Machine_price
Answer:-
CREATE TABLE Machine
(
Machine_id INT PRIMARY KEY,
Machine_name VARCHAR(50) NOT NULL
CHECK (Machine_name = UPPER(Machine_name)),
Machine_type VARCHAR(10)
CHECK (Machine_type IN ('drilling', 'milling', 'lathe', 'turning', 'grinding')),
Machine_price FLOAT
CHECK (Machine_price > 0),
Machine_cost FLOAT,
CONSTRAINT chk_cost_price
CHECK (Machine_cost < Machine_price)
);
2. Create a table with details as given below
Table Name:- Employee
Columns Column Name Column Data Type Constraints
1 Employee_id integer Primary key
2 Employee_name varchar (50) NOT NULL, uppercase
3 Employee_desg varchar(10) Designation in ( ‘Manager’, ‘staff’, ‘worker’)
4 Employee_sal float Greater than Zero
5 Employee_uid text Unique
Table level constraint Employee_uid not equal to Employee_id
Answer:-
CREATE TABLE Employee
(
Employee_id INT PRIMARY KEY,
Employee_name VARCHAR(50) NOT NULL
CHECK (Employee_name = UPPER(Employee_name)),
Employee_desg VARCHAR(10)
CHECK (Employee_desg IN ('Manager', 'staff', 'worker')),
Employee_sal FLOAT
CHECK (Employee_sal > 0),
Employee_uid TEXT UNIQUE,
CONSTRAINT chk_uid_not_equal
CHECK (Employee_uid <> CAST(Employee_id AS TEXT))
);
Set C
1. Create a table with details as given below
Table Name:- Student
Columns Column Name Column Data Type Constraints
1 Stud_id integer Primary key
2 Stud _name varchar (50) NOT NULL, uppercase
3 Stud _Class varchar(10) Classin ( ‘FY’, ‘SY’, ‘TY’)
4 Stud _Marks float Greater than Zero
5 Stud _uid text Unique
Table level constraint Stud_uid not equal to Stud_id
CREATE TABLE Student
(
Stud_id INT PRIMARY KEY,
Stud_name VARCHAR(50) NOT NULL
CHECK (Stud_name = UPPER(Stud_name)),
Stud_Class VARCHAR(10)
CHECK (Stud_Class IN ('FY', 'SY', 'TY')),
Stud_Marks FLOAT
CHECK (Stud_Marks > 0),
Stud_uid TEXT UNIQUE,
CONSTRAINT chk_uid_not_equal
CHECK (Stud_uid <> CAST(Stud_id AS TEXT))
);
No comments:
Post a Comment