SQL ASSIGNMENT

Activity $1$: Create a table STUDENT with under mentioned structure by using SQL Statement:

StdID Number Primary Key

StdName Character $(30)$ NOT NULL

Sex Character$(6)$ Male or Female

Percentage Number

SClass Number

Sec Character

Stream Character$(10)$ Science or Commerce

DOB Date Date of Birth

Step $1$: Open MySQL$,$ Open Database and create table as:

CREATE TABLE Student $($

StdID INT$(4)$ PRIMARY KEY, StdName VARCHAR$(30)$ NOT NULL,

Sex VARCHAR$(1),$ Percentage DECIMAL$(5,2),$ SClass INT $,$ Sec VARCHAR$(1),$ Stream VARCHAR$(10),$ DOB DATE $)$;

Step $2$: Press Enter keyto complete create table:

Step $3$: Insert records into STUDENT table.

INSERT INTO Student VALUES $(1001,$AKSHRA AGARWAL,'FEMALE',$70,11,$A$,10/11/1996)$;

Step $4$: As you press enter key after typing above statement, $1$ recordwill be stored into STUDENTtable.

Step$5$: Similarly like step $3,$ enter other records of the following table.

StdID StdName Sex Percentage Class Sec Stream DOB

$1001$ AKSHRA AGARWAL FEMALE $7011$ A Science $10/11/1996$

$1002$ ANJANI SHARMA FEMALE $7511$ A Commerce $18/09/1996$

$1003$ ANSHUL SAXENA MALE $7811$ A Commerce $19/11/1996$

$1004$ AISHWARYA SINGH FEMALE $7911$ A Commerce $1/11/1996$

$1005$ AKRITI SAXENA FEMALE $7611$ A Commerce $20/09/1996$

$1006$ KHUSHI AGARWAL FEMALE $7711$ A Commerce $14/09/2003$

$1007$ MAAHI AGARWAL FEMALE $7411$ A Science $21/04/1997$

$1008$ MITALI GUPTA FEMALE $7812$ A Science $26/11/1997$

$1009$ NIKUNJ AGARWAL MALE $5812$ A Science $12/7/1997$

$1010$ PARKHI FEMALE $5912$ A Commerce $20/12/1997$

$65$

$1011$ PRAKHAR TIWARI MALE $4312$ A Science $22/04/1997$

$1012$ RAGHAV GANGWAR MALE $5812$ A Commerce $21/12/1997$

$1013$ SAHIL SARASWAT MALE $5712$ A Commerce $13/08/1997$

$1014$ SWATI MISHRA FEMALE $9811$ A Science $13/08/1996$

$1015$ HARSH AGARWAL MALE $5811$ B Science $28/08/2003$

$1016$ HARSHIT KUMAR MALE $9811$ B Science $22/05/2003$

$1017$ JAHANVI KAPOOR MALE $6511$ B Science $10/1/1997$

$1018$ STUTI MISHRA MALE $6611$ C Commerce $10/1/1996$

$1019$ SURYANSH KUMAR

AGARWAL

MALE

$85$

$11$

C

Commerce

$22/08/2007$

$1020$ TANI RASTOGI FEMALE $7512$ C Commerce $15/01/1998$

$1021$ TANISHK GUPTA MALE $5512$ C Science $11/4/1998$

$1022$ TANMAY AGARWAL MALE $5711$ C Commerce $28/06/1998$

$1023$ YASH SAXENA MALE $7911$ C Science $13/3/1998$

$1024$ YESH DUBEY MALE $8512$ C Commerce $3/4/1998$

Activity $2$: Open school database, then select student table and use following SQL statements.

TYPE THE STATEMENT, PRESS ENTER AND NOTE THE OUTPUT

$1$ To display all the records form STUDENT table.

SELECT $*$ FROM student ;

$2.$To display ony name and date of birth from the table STUDENT. SELECT StdName, DOB FROM student ;

$3.$To display all students record where percentage is greater of equal to $80$ FROM student table. SELECT $*$ FROM student WHERE percentage $>=80$;

$4.$To display student name, stream and percentage where percentage of student is more than $80$ SELECT StdName, Stream, Percentage WHERE percentage $>80$;

$5.$To display all records of science students whose percentage is more than $75$ form student table. SELECT $*$ FORM student WHERE stream $=$Science$$ AND percentage $>75$;

Activity $3$: Open school database, then select student table and use following SQL statements.

TYPE THE STATEMENT, PRESS ENTER AND NOTE THE OUTPUT

$1.$To display the STUDENT table structure.

DESCRIBE Student;

$2.$To add a column $($FIELD$)$ in the STUDENT table, for example TeacherID as VARCHAR$(20)$;

ALTER TABLE Student ADD TeacherID VARCHAR$(20)$;

$3.$Type the statement

DESC Student;

Press enter key, now note the difference in table structure.

$4.$Type the statement and press enter key, note the new field that you have added as TeacherID

SELECT $*$ FROM student;

$5.$To modify the TeacherID data type form character to integer.

$66$

ALTER TABLE Student MODIFY TeacherID INTEGER ; DESC Student;

SELECT $*$ FROM student;

Activity $4$

$1.$To Drop $($Delete$)$ a field form a table. For e$.$g you want to delete TeacherID field.

ALTER TABLE Student DROP TeacherID;

$2.$To subtract $5$ form all students percentage and display name and percentage.

SELECT name, percentage $-5$ FROM Student;

$3.$Using column alise for example we want to display StdName as Student Name and DOB as Date of Birth then the statement will be$.$

SELECT StdName AS "Student Name",

DOB As $$Date of Birth$$ FROM Student;

$4.$Display the name of all students whose stream is not Science

SELECT StdName FROMstudent WHERE Stream $<>$Science$$;

$5.$Display all name and percentage where percentage is between $60$ and $80$

SELECT StdName, percentage FROM student WHERE percentage $>=60$ AND

percentage$<=80$ ;

Activity $5$:

$1.$To change astudent name from SWATIMISHRA to SWATI VERMA whose StdID is $1014$ and alsochange percentage $86.$

UPDATE Student SET StdName $=$SWATI VERMA$,$ percentage $=86$

WHERE StdId $=1014$;

$2.$To delete the records form student table where StdId is $1016.$

DELETE FROM Student WHERE StdID $=1016$;

$3.$Type the following SQL statement and note the output.

SELECT $*$ FROM Student WHERE StdName LIKE $\text{'}$G$\_\text{'}$ ; SELECT $*$ FROM Student