Question

- Create student table with the following design. – student(id, name_surname, age, hobby) id: primary key of the student table. • Assume that id follows 8 digits – Each student can have multiple hobbies or no hobby. - Age must be between 15 and 120.

POSTGRESQL

Answer 1

In case of any queries,please comment. I would be very happy to assist all your queries.Please give a Thumps up if you like the answer.

The Create table statement is

CREATE TABLE student (id numeric(6) NOT NULL,name_surname VARCHAR (50),age int ,hobby VARCHAR (50),PRIMARY KEY (id),CONSTRAINT CheckAge CHECK (Age BETWEEN 15 and 120));

Different test cases to insert value into table student are
insert into student values(123,'steve_jobs',16,'reading');
insert into student values(123456789,'rose_jolie',20,'reading');
insert into student values(123456,'rose_jolie',20,'reading');
insert into student values(124,'tim_cook',14,'reading');
insert into student values(124,'tim_cook',20,'reading,writing');
insert into student values(127,'james_lee',25,NULL);

To list the values in student table
select * from student;

postgres=# CREATE TABLE student (id numeric(6) NOT NULL,name_surname VARCHAR (50), age int , hobby VARCHAR (50),PRIMARY KEY (id), CONSTRAINT CheckAge CHECK (Age BETWEEN 15 and 120)); CREATE TABLE postgres=# insert into student values (123, 'steve_jobs',16, 'reading'); INSERT 01 postgres=# insert into student values (123, 'steve_jobs',16, 'reading'); ERROR: duplicate key value violates unique constraint "student_pkey" DETAIL: Key (id)=(123) already exists. postgres=# insert into student values (123456789, 'rose_jolie',20, 'reading'); ERROR: numeric field overflow DETAIL: A field with precision 6, scale o must round to an absolute value less than 10^6. postgres=# insert into student values (123456, 'rose_jolie',20, 'reading'); INSERT 01 postgres=# insert into student values(124, 'tim_cook',14,'reading'); ERROR: new row for relation "student" violates check constraint "checkage" DETAIL: Failing row contains (124, tim_cook, 14, reading). postgres=# insert into student values(124, 'tim_cook',20, 'reading, writing'); INSERT 01 postgres=# insert into student values (127,'james_lee',25,NULL); INSERT 01 postgres=# select * from student; id | name_surname | age | hobby 123 | steve_jobs 123456 | rose_jolie 124 | tim_cook 127 | james_lee (4 rows) 16 | reading 20 | reading | 20 | reading, writing | 25 |