Untitled

Name:-
Div:-FY-IT.B
DATE:-30/9/2022
Subject:-DBMS
Pratical Assignment-3

Q-1. Display ALL records FROM course TABLE.

CREATE TABLE stud_COURSE
(
 course_no VARCHAR2(7),
 title VARCHAR2(20),
 instructor VARCHAR2(20),
 credits NUMBER(2)
);
0.06 seconds

INSERT INTO STUD_COURSE
VALUES ('AC202','Accounting','Guffsaton',3);

INSERT INTO stud_course
VALUES ('FL101','Basic English','McCoy',3);

INSERT INTO stud_course
VALUES ('CIS36','Database','choi',2);

INSERT INTO stud_course
VALUES ('FL105','speech','Frank',2);

INSERT INTO stud_course
VALUES ('FL503','Compsition','Mauffler',3);

INSERT INTO stud_course
VALUES ('MA102','Basic Math','Murray',3);

INSERT INTO stud_course
VALUES ('MA130','Statistics','Counter',3);

INSERT INTO stud_course
VALUES ('NM204','Journalism','Hemandon',2);

INSERT INTO stud_course
VALUES ('PH121','Basic circuit','Shockly',3);

INSERT INTO stud_course
VALUES ('PH150','Tennis','Lizzer',1);

INSERT INTO stud_course
VALUES ('PH123','Resistor T’,Charlse’,);


Q-2. Display all records from Enrollment table.

CREATE TABLE STUD_ENROLLMENT
(
 student_id number(10),
 course_no varchar2(10),
 grade varchar2(1)
);

insert into STUD_ENROLLMENT
values (777949993,'AC202','C');

insert into STUD_ENROLLMENT
values (777949993,'CS303','A');

insert into STUD_ENROLLMENT
values (777949993,'FL105','D');

insert into STUD_ENROLLMENT
values (777949993,'MA130','A');

insert into STUD_ENROLLMENT
values (777949993,'NM204','B');

insert into STUD_ENROLLMENT
values (777949993,'PH150','A');

insert into STUD_ENROLLMENT
values (777949993,'PY123','B');

insert into STUD_ENROLLMENT
values (800005555,'CIS326','C');

insert into STUD_ENROLLMENT
values (999001111,'CIS326','A');

insert into STUD_ENROLLMENT
values (234556677,'CIS326','D');

insert into STUD_ENROLLMENT
values (999110000,'CIS326','A');

insert into STUD_ENROLLMENT
values (999119800,'CIS326','B');

insert into STUD_ENROLLMENT
values (000348282,'CIS326','C');

insert into STUD_ENROLLMENT
values (666110001,'CIS326','C');

insert into STUD_ENROLLMENT
values (555119800,'CIS326','D');
insert into STUD_ENROLLMENT
values (444339837,'CIS326','A');

insert into STUD_ENROLLMENT
values (333448887,'CIS326','B');

insert into STUD_ENROLLMENT
values (222551288,'CIS326','B');

insert into STUD_ENROLLMENT
values (222551288,'NM204','B');


Q-3. Display all records from student table.

CREATE TABLE STUDENT_ID
(
 stud_id number(10),
 first_name varchar2(10),
 last_name varchar2(10),
 major varchar2(10),
 status number(1),
 gender number(1),
 DOB date
);

0.06 seconds


insert into STUDENT_ID
values (000348282,'Tirilee','Lytler','Spanish',1,2,'12/01/1974');

insert into STUDENT_ID
values (23456677,'Koons','Smith','Business',1,1,'03/03/1976');

insert into STUDENT_ID
values (777349993,'John','Doe','Business',1,1,'02/03/1974');

insert into STUDENT_ID
values (800005555,'Patrik','Saint','Business',2,2,'07/08/1974');

insert into STUDENT_ID
values (999001111,'James','Kirkland','Und',1,1,'01/05/1976');

insert into STUDENT_ID
values (999009800,'Mary','Smith','Math',1,2,'08/28/1947');

insert into STUDENT_ID
values (999110000,'Sally','Jones','English',1,2,'06/19/1978');

insert into STUDENT_ID
values (666110001,'Bill','Mack','English',2,1,'09/06/1978’);

INSERT INTO STUDENT_ID
VALUES (555119800,'Gerg','Johnson','Business',2,1,'04/05/1955');

INSERT INTO STUDENT_ID
VALUES (444339837,'Jacob','Schwartz','English',1,1,'12/25/1974');

INSERT INTO STUDENT_ID
VALUES (333448887,'Jeny','Donald','English',3,2,'08/07/1974');

INSERT INTO STUDENT_ID
VALUES (222551288,'Nacy','Ogur','Art',1,2,'05/05/1948');

Q-4. Retrieve course NUMBER OF course  FROM Course TABLE.

SELECT COURSE_no FROM stud_COURSE


COURSE_NO
AC202
FL101
CIS36
FL105
FL503
MA102
MA130
NM204
PH121
PH150
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.

Q-5.  Retrieve student id OF student FROM student TABLE.

SELECT student_id FROM STUD_ENROLLMENT;


STUDENT_ID
777949993
777949993
777949993
777949993
777949993
777949993
777949993
800005555
999001111
234556677
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.


Q-6. Retrieve course NUMBER AND Title FROM Course TABLE.


SELECT course_no,title FROM stud_COURSE

COURSE_NO   TITLE
AC202   Accounting
FL101   Basic English
CIS36   DATABASE
FL105   speech
FL503   Compsition
MA102   Basic Math
MA130   STATISTICS
NM204   Journalism
PH121   Basic circuit
PH150   Tennis
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.

Q-7. Display FIRST Name , status AND grade FROM student TABLE.

SELECT first_name,status,gender FROM STUDENT_ID


FIRST_NAME  STATUS  GENDER
Tirilee 1   2
Koons   1   1
John    1   1
Patrik  2   2
Jeny    3   2
Nacy    1   2
James   1   1
Mary    1   2
Bill    2   1
Jacob   1   1


10 ROWS returned IN 0.00 seconds

Q-8.  Display ALL those records course Title IS ‘Basic English’  FROM course TABLE.

SELECT * FROM stud_COURSE WHERE title='Basic English';


COURSE_NO   TITLE   INSTRUCTOR  CREDITS
FL101   Basic English   McCoy   3


1 ROWS returned IN 0.03 seconds

Q-9. Display ALL those records OF students whose student id IS ‘777349993’ FROM student TABLE.

SELECT * FROM STUDENT_ID WHERE stud_id=777349993


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
777349993   John    Doe Business    1   1   02/03/1974


1 ROWS returned IN 0.01 seconds

Q-10. Display ALL those records OF students whose course NUMBER IS ‘CIS326’ FROM Enrollment TABLE.

SELECT * FROM STUD_ENROLLMENT WHERE course_no = 'CIS326';


STUDENT_ID  COURSE_NO   GRADE
800005555   CIS326  C
999001111   CIS326  A
234556677   CIS326  D
999110000   CIS326  A
999119800   CIS326  B
348282  CIS326  C
666110001   CIS326  C
555119800   CIS326  D
444339837   CIS326  A
333448887   CIS326  B
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.


10 ROWS returned IN 0.04 seconds

Q-11. Display ALL those records OF students whose grade IS ’2’ FROM student  TABLE.

SELECT * FROM STUD_ENROLLMENT WHERE course_no='CIS326' OR course_no='NM204' OR  course_no='CS303';


STUDENT_ID  COURSE_NO   GRADE
777949993   CS303   A
777949993   NM204   B
800005555   CIS326  C
999001111   CIS326  A
234556677   CIS326  D
999110000   CIS326  A
999119800   CIS326  B
348282  CIS326  C
666110001   CIS326  C
555119800   CIS326  D
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.


10 ROWS returned IN 0.00 SECOND

Q-12. . Display ALL those records OF students whose course NUMBER IS ‘CIS326’ FROM
Enrollment TABLE.

SELECT * FROM STUD_ENROLLMENT WHERE course_no = 'CIS326';


STUDENT_ID  COURSE_NO   GRADE
800005555   CIS326  C
999001111   CIS326  A
234556677   CIS326  D
999110000   CIS326  A
999119800   CIS326  B
348282  CIS326  C
666110001   CIS326  C
555119800   CIS326  D
444339837   CIS326  A
333448887   CIS326  B
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.


10 ROWS returned IN 0.16 seconds

Q-13. TO display ALL students that course NUMBER are IN "CIS326", "NM204" OR
"CS303" FROM Enrollment TABLE.

SELECT * FROM STUD_ENROLLMENT WHERE course_no='CIS326' OR course_no='NM204' OR course_no='CS303';


STUDENT_ID  COURSE_NO   GRADE
777949993   CS303   A
777949993   NM204   B
800005555   CIS326  C
999001111   CIS326  A
234556677   CIS326  D
999110000   CIS326  A
999119800   CIS326  B
348282  CIS326  C
666110001   CIS326  C
555119800   CIS326  D
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.


10 ROWS returned IN 0.03 seconds

Q-14. TO display ALL students that major are NOT IN "English", "Art" OR
"business" FROM student TABLE.

SELECT * FROM STUDENT_ID WHERE major NOT IN ('English','Business','Art');


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
348282  Tirilee Lytler  Spanish 1   2   12/01/1974
999001111   James   Kirkland    Und 1   1   01/05/1976
999009800   Mary    Smith   Math    1   2   08/28/1947


3 ROWS returned IN 0.00 seconds


Q-15. TO display ALL students WHERE grade IS “c” AND courseno IS “CIS326” FROM
Enrollment TABLE.


SELECT * FROM STUD_ENROLLMENT WHERE course_no='CIS326' AND grade='C';


STUDENT_ID  COURSE_NO   GRADE
800005555   CIS326  C
800005555   CIS326  C
800005555   CIS326  C
348282  CIS326  C
666110001   CIS326  C
348282  CIS326  C
666110001   CIS326  C


7 ROWS returned IN 0.04 seconds

Q-16. TO display ALL students WHERE grade IS “c” OR grade IS ”a” FROM
Enrollment TABLE.

SELECT * FROM STUD_ENROLLMENT WHERE grade='C' OR grade='A';


STUDENT_ID  COURSE_NO   GRADE
777949993   AC202   C
777949993   CS303   A
777949993   MA130   A
777949993   PH150   A
800005555   CIS326  C
800005555   CIS326  C
999001111   CIS326  A
777949993   AC202   C
777949993   CS303   A
777949993   MA130   A
More than 10 ROWS available. Increase ROWS selector TO VIEW more ROWS.


10 ROWS returned IN 0.00 seconds


Q-17. TO display ALL students WHERE Major IS NOT "English " FROM student TABLE.

SELECT * FROM  STUDENT_ID WHERE NOT major='English';


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
348282  Tirilee Lytler  Spanish 1   2   12/01/1974
23456677    Koons   Smith   Business    1   1   03/03/1976
777349993   John    Doe Business    1   1   02/03/1974
800005555   Patrik  Saint   Business    2   2   07/08/1974
222551288   Nacy    Ogur    Art 1   2   05/05/1948
999001111   James   Kirkland    Und 1   1   01/05/1976
999009800   Mary    Smith   Math    1   2   08/28/1947


7 ROWS returned IN 0.00 seconds


Q-18. TO display ALL students WHERE Major IS “Business” AND Status must be “1” OR “3”.

SELECT * FROM  STUDENT_ID WHERE major='Business' AND status=1 OR status=3;


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
23456677    Koons   Smith   Business    1   1   03/03/1976
777349993   John    Doe Business    1   1   02/03/1974
333448887   Jeny    Donald  English 3   2   08/07/1974


3 ROWS returned IN 0.01 seconds

Q-19. Display ALL records whose FIRST name starting WITH "J" FROM student TABLE.

SELECT * FROM  STUDENT_ID WHERE first_name LIKE 'J%'


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
777349993   John    Doe Business    1   1   02/03/1974
333448887   Jeny    Donald  English 3   2   08/07/1974
999001111   James   Kirkland    Und 1   1   01/05/1976
444339837   Jacob   Schwartz    English 1   1   12/25/1974


4 ROWS returned IN 0.02 seconds


Q-20. Display ALL records whose FIRST name ending WITH "y" FROM student TABLE .

SELECT * FROM  STUDENT_ID WHERE first_name LIKE '%y'


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
333448887   Jeny    Donald  English 3   2   08/07/1974
222551288   Nacy    Ogur    Art 1   2   05/05/1948
999009800   Mary    Smith   Math    1   2   08/28/1947


3 ROWS returned IN 0.02 seconds

Q-21. Display ALL records whose Instructor starting WITH "m" IN ANY position.

SELECT * FROM  stud_COURSE WHERE instructor LIKE '%M%';


COURSE_NO   TITLE   INSTRUCTOR  CREDITS
FL101   Basic English   McCoy   3
FL503   Compsition  Mauffler    2
MA102   Basic Math  Murray  3


3 ROWS returned IN 0.11 seconds


Q-22. Display ALL course title that have "a" IN the SECOND position.

SELECT * FROM  stud_COURSE WHERE title LIKE '_a%';


COURSE_NO   TITLE   INSTRUCTOR  CREDITS
FL101   Basic English   McCoy   3
CIS36   DATABASE    choi    2
MA102   Basic Math  Murray  3
PH121   Basic circuit   Shockly 3


4 ROWS returned IN 0.02 seconds
Q-23. Display ALL students WITH a lastName that starts WITH "J" AND are AT LEAST 3
characters IN LENGTH FROM student TABLE.

SELECT * FROM STUDENT_ID WHERE last_name LIKE 'J__%';


STUD_ID FIRST_NAME  LAST_NAME   MAJOR   STATUS  GENDER  DOB
999110000   Sally   Jones   English 1   2   06/19/1978
555119800   Gerg    Johnson Business    2   1   04/05/1955
2 ROWS returned IN 0.00 seconds