CREATE TABLE Student( studentID BIGINT NOT NULL, phone_number BIGINT NOT NULL, f

Question

CREATE TABLE Student(

studentID BIGINT NOT NULL,

phone_number BIGINT NOT NULL,

first_name VARCHAR(25) NOT NULL,

last_name VARCHAR(25) NOT NULL,

programme VARCHAR(255) NOT NULL,

school_name VARCHAR(255) NOT NULL,

status TINYTEXT NOT NULL

);

Display only the student ID based on phone_number from the list.

Present all student based on a “string” pattern from the last name.

Add one column Age ( int ) CHECK (Age>=18)

Find all student whose Age is in between the range. (Inclusive)

Insert at least 10 records into the table.

Change the status of the student and show the result.

Add one more column “option” in the table to show the student choice of coop/non-coop, if coop then mark it as ‘y’ else ‘n’.

Display the list if the option of the student is “coop”.

Add the primary key for a combination of columns student ID and phone number.

Present on your own the use of primary keys in the two columns.

Explanation / Answer

1.Display only the student ID based on phone_number from the list.

select studentID from Student where phone_number = 4545454545;

(give exact phone number to fine that studentID)
==========================================================================

2.Present all student based on a “string” pattern from the last name.

select * from Student where first_name like '%pattern%' and last_name like '%pattern%';

(edit the the patter according to your choice)

===========================================================================

3.Add one column Age ( int ) CHECK (Age>=18)

alter table Student add age int;

alter table Student add check (age>=18);

(now update the existing student data with corresponding age value. statement will be like - update student set age = 20 where studentID = 100;)
==========================================================================
4.Find all student whose Age is in between the range. (Inclusive)

select * from Student where age between 20 and 24;

(give your range in place of 20 and 24)
===========================================================================

5.Insert at least 10 records into the table.  

insert into student values (100,8989898988,'Joe','Mathew','SCIENCE','XYZ SCHOOL','Y',21);
insert into student values (101,8989898989,'Mathew','Jim','COMMERCE','HYG SCHOOL','N',22);
insert into student values (102,8989898968,'Ton','Joseph','ARTS','XYZ SCHOOL','N',20);
insert into student values (103,8989898948,'Ben','Parker','COMMERCE','HYG SCHOOL','Y',19);
insert into student values (104,8989898938,'Peter','Ron','SCIENCE','HYG SCHOOL','Y',21);
insert into student values (105,8989898928,'Jhon','Cinna','COMMERCE','HYG SCHOOL','N',24);
insert into student values (106,8989898918,'Joe','Root','SCIENCE','XYZ SCHOOL','Y',20);
insert into student values (107,5989898988,'Andrew','Simons','COMMERCE','ABC SCHOOL','N',23);
insert into student values (108,5989848988,'Rickey','Ponting','SCIENCE','XYZ SCHOOL','Y',25);
insert into student values (109,8289898988,'Jesse','Mathew','ARTS','ABC SCHOOL','N',21);
===============================================================================
6.Change the status of the student and show the result.

update student set status = 'A' where studentID = 102;

select studentID from student where studentID = 102;

================================================================================
7.Add one more column “option” in the table to show the student choice of coop/non-coop, if coop then mark it as ‘y’ else ‘n’.

alter table Student add OPTION VARCHAR(25);

FOR coop student run this query - update student set OPTION = 'y' where studentID IN (100,103,104);
For non-coop student run this query - update student set OPTION = 'n' where studentID IN (101,102,105);

(Give required studentId inside that 'IN' condition )
==================================================================================
8.Add the primary key for a combination of columns student ID and phone number.

ALTER TABLE Student
ADD CONSTRAINT PK_std PRIMARY KEY (studentID ,phone_number );

===================================================================================

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.