SQL(LAB) Assignment 4 Queries's Answer

1. Create table Student with below given table structure and constraints. Datatype Constraints Remarks Roll_no Number(3) Primary Key  Unique identification number of student Name Varchar2(15) Unique Name of student Birth_Date Date Gender Varchar2(10) Not Null  Male or Female.

1. CREATE TABLE STUDENT

(ROLL_NO NUMBER(3) CONSTRAINT PK_RNO PRIMARY KEY,

NAME VARCHAR2(15) CONSTRAINT UN_NAME UNIQUE,

BIRTH_DATE DATE,

GENDER VARCHAR2(10) CONSTRAINT NN_GENDER NOT NULL);

2. Check the table structure of Student table.

A. DESC STUDENT

3. Create table Issue_Details with below given table structure and constraints. 

Ensure that submission_date is greater than or equal to issue_date.

(CONSTRAINT FK_RNO FOREIGN KEY(ROLL_NO) REFERENCES STUDENT,

CONSTRAINT FK_BCODE FOREIGN KEY(BCODE) REFERENCES BOOK_DETAILS,)

ALTER TABLE ISSUE_DETAILS ADD FOREIGN KEY(BOOK_CODE) REFERENCES BOOK_DETAILS;

A. CREATE TABLE Issue_Details

(ROLL_NO NUMBER(3),

BOOK_CODE NUMBER(2),

ISSUE_DATE DATE,

SUBMISSION DATE,

CONSTRAINT PK_RNO_BCODE_IDATE PRIMARY KEY(ROLL_NO,BOOK_CODE,ISSUE_DATE),

CONSTRAINT CH_SUB_IDATE CHECK(SUBMISSION>=ISSUE_DATE));

4.Check the structure of Issue_Details table.

A. DESC ISSUE_DETAILS;

5. Insert the following records in Student table

A. INSERT INTO STUDENT

VALUES(1,'ALLAN','10-Jan-91','Male');

INSERT INTO STUDENT

VALUES(2,'Sachin','22-Dec-90','MALE');

INSERT INTO STUDENT

VALUES(3,'Alpa','25-Jun-91','FEMALE');

INSERT INTO STUDENT

VALUES(4,'Christina','05-Sep-90','FEMALE');

INSERT INTO STUDENT

VALUES(5,'Mukesh','04-Dec-90','MALE');

INSERT INTO STUDENT

VALUES(6,'Anil','15-Nov-91','MALE');

INSERT INTO STUDENT

VALUES(7,'Jyoti','06-Apr-91','FEMALE');

INSERT INTO STUDENT

VALUES(8,'Sneha','10-Jan-91','FEMALE');

6.Check the records of Student table.

A. SELECT * FROM STUDENT;

7. Insert the following records in Issue_Details table.

A. INSERT INTO Issue_Details

VALUES(1,2,'10-Jul-10','15-Jul-10');

INSERT INTO Issue_Details

VALUES(3,4,'15-Jul-10','17-Jul-10');

INSERT INTO Issue_Details

VALUES(5,6,'21-Aug-10',NULL);

INSERT INTO Issue_Details

VALUES(5,7,'21-Aug-10','30-Aug-10');

INSERT INTO Issue_Details

VALUES(1,2,'11-Dec-10','25-Dec-10');

INSERT INTO Issue_Details

VALUES(1,8,'05-Jan-11','07-Jan-11');

INSERT INTO Issue_Details

VALUES(4,5,'10-Feb-11',NULL);

INSERT INTO Issue_Details

VALUES(6,4,'14-Feb-11','18-Feb-11');

8. List name of all students in lowercase, title case and the way in which they are stored in the table.

A. SELECT NAME, LOWER(NAME), UPPER(NAME) FROM STUDENT;

9. Display the book name and the length of each book name from book_details table.

A. SELECT BNAME, LENGTH(BNAME) FROM BOOK_DETAILS

10. Display the details of books from book_details in descriptive manner as:

Book <book_name> published by <publisher> is having price <price>.

A. SELECT 'Book'|| bname || 'published by' || publisher|| 'is having price' || price

FROM BOOK_DETAILS;

11. Display book name and 6th to 10th characters (substring) of each book name.

A. SELECT BNAME, SUBSTR(BNAME,6,10) FROM BOOK_DETAILS;

12. List book name and last 7 characters (substring) of each book name.

A. SELECT BNAME, SUBSTR(BNAME,-7) FROM BOOK_DETAILS;

13. Display book name and position of 1st 'a' from each book name.

A. SELECT BNAME, INSTR(BNAME,'A') FROM BOOK_DETAILS;

SELECT BNAME, INSTR(BNAME,'a') FROM BOOK_DETAILS;

14. Display book name and extract a substring from book name consisting of 4 characters and starting from the first 'e'.

A. SELECT BNAME, SUBSTR(BNAME,INSTR(BNAME,'E')) FROM BOOK_DETAILS;

15. List bcode, bname, release_date, month of release_date and year of release_date from book_details table.

A. SELECT BCODE, BNAME,RDATE, TO_CHAR(RDATE, 'MONTH'), TO_CHAR(RDATE,'YEAR') FROM BOOK_DETAILS;

16. List details of all books released in the month of January or June.

A. SELECT * FROM BOOK_DETAILS

WHERE TO_CHAR(RDATE,'MM')=01 OR TO_CHAR(RDATE,'MM')=06;

17. List details of all books released in the year 1995 or 1998 or 2005.

A. SELECT * FROM BOOK_DETAILS

WHERE TO_CHAR(RDATE,'YYYY')=1995 OR TO_CHAR(RDATE,'YYYY')=1998 OR TO_CHAR(RDATE,'YYYY')=2005;

18. List name and birth_date of all students born on Tuesday or Saturday.

A. SELECT NAME,BIRTH_DATE, TO_CHAR(BIRTH_DATE,'DAY') FROM STUDENT

WHERE TO_CHAR(BIRTH_DATE,'DAY') LIKE'TUESDAY%' OR TO_CHAR(BIRTH_DATE,'DAY') LIKE'SATURDAY%';

19. Display eno, name, dob and age of all employees.

A. SELECT ENO, NAME, DOB, TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(DOB,'YYYY') FROM EMPLOYEES;

20. List roll_no, book_code, issue_date, submission_date and the total days for which each student has kept the library books with them.

A. SELECT ROLL_NO, BOOK_CODE, ISSUE_DATE, SUBMISSION,MONTHS_BETWEEN(SUBMISSION,ISSUE_DATE)*30 TOTAL_DAYS FROM ISSUE_DETAILS;