21ZC52- Database Technologies Laboratory
Dharun kanna N (23MZ01)
Exp 1
I. Create the following tables :
1. Member master table : member
CREATE TABLE member (
member_id VARCHAR2(5) PRIMARY KEY,
lname VARCHAR2(15) ,
fname VARCHAR2(15),
area VARCHAR2(20) ,
phone_no NUMBER(10)
);
2. Books master table : books
CREATE TABLE books(
book_id VARCHAR2(10) PRIMARY KEY,
title VARCHAR2(80) ,
type CHAR(3),
author VARCHAR2(20),
price NUMBER(9,2)
);
3. Transaction table : transaction
CREATE TABLE transaction(
t_id VARCHAR2(3) PRIMARY KEY,
book_id VARCHAR2(10) REFERENCES BOOKS(book_id),
member_id VARCHAR2(5) REFERENCES MEMBER(member_id),
issue_date DATE,
return_date DATE
);
II. Insert the following data into their respective tables :
Data Insertion :
1. Data for member table :
INSERT INTO member VALUES(
'&member_id',
'&lname',
'&fname',
'&area'
,&phone_no
);
2. Data for books table :
INSERT INTO books VALUES(
'&book_id',
'&title',
'&type',
'&author',
&price
);
3. Data for transaction table :
INSERT INTO transaction VALUES (
'&t_ie',
'&book_id',
'&member_id',
'&issue_date',
'&return_date'
);
III. SQL Sentence Constructs for practice :
1. Find out the names of all the members.
SELECT FNAME|| ' ' || LNAME AS "NAMES" FROM member;
2. Print the entire member table.
SELECT * FROM MEMBER;
3. Retrieve the list of fname and the area of all the members.
SELECT FNAME,AREA FROM MEMBER;
4. List the various book types available from the book table without duplicates.
SELECT DISTINCT TYPE FROM BOOKS;
5. Find the names of all members having ’a’ as the second letter in their fnames.
SELECT * FROM MEMBER WHERE FNAME LIKE '_a%';
6. Find the lnames of all members that begin with ’s‘ or ’j’.
SELECT * FROM member WHERE lname LIKE 'J%' OR lname LIKE 'S%';
7. Find out the members who stay in an area whose second letter is ’B’.
SELECT * FROM member WHERE area LIKE '_B%';
8. Find the list of all members who stay in area ‘MYL’ or ‘CBE’ or ‘DEL’.
SELECT * FROM member WHERE area IN ('MYL','CBE','DEL');
9. Print the list of members whose phone numbers greater than the value 9800000000
SELECT * FROM member WHERE phone_no > 9800000000;
10. Print the information from transaction table of members who have been issued books in the month
of september.
SELECT * FROM transaction WHERE issue_date LIKE '%SEP%';
11. Display the transaction table information for member id ’M03‘and ’M04’.
SELECT * FROM transaction WHERE member_id IN ('M03','M04');
12. Find the books of type ’CSE’ and ’ECE’.
SELECT * FROM books WHERE type IN ('CSE','ECE');
13. Find the books whose price is greater than 3500 and less than or equal to 5000.
SELECT * FROM books WHERE price > 3500 AND price <= 5000;
14. Find the books that cost more than 5000 also find the new cost as original cost multiplied by 15.
SELECT title , price * 15 FROM books WHERE price > 5000;
15. Rename the new column in the above query as new price.
SELECT title , price * 15 AS new_price FROM books WHERE price > 5000;
16. List the books in sorted order of their titles.
SELECT * FROM books ORDER BY title;
17. Print the names and types of all the books except IC books.
SELECT title,type FROM books WHERE type NOT IN ('IC');
18. Calculate the square root of the price of each book.
SELECT title, SQRT(price) AS Sqrt_Price FROM Books;
19. Divide the cost of book ’Digital Design” by difference between its price and 1000.
SELECT title, price/(price-1000) FROM books WHERE Title = 'Digital Design';
20. List the names. areas and member id of members without phone numbers.
SELECT fname || ' ' || lname AS name, area, member_id FROM member;
21. List the names of members without lname
SELECT fname FROM member WHERE lname IS NULL;
22. List the book id. title, type of books whose author name begin with letter ’M’.
SELECT book_id , title ,type FROM books WHERE author LIKE 'M%';
23. List the book id and t id of members having t id less than ‘t05’ from the transaction table.
SELECT book_id, t_id FROM transaction WHERE t_id < 'T05';