Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE custo(
- cust_id VARCHAR2(3) NOT NULL PRIMARY KEY,
- lnaame VARCHAR2(20),
- fname VARCHAR2(20),
- area VARCHAR2(2),
- ph_no NUMBER(10)
- );
- CREATE TABLE movies(
- mv_no NUMBER(2) NOT NULL PRIMARY KEY,
- title VARCHAR2(20),
- TYPE VARCHAR2(20),
- star VARCHAR2(20),
- price NUMBER(6,2)
- );
- CREATE TABLE invoice(
- inv_no VARCHAR2(3) NOT NULL PRIMARY KEY,
- cust_id VARCHAR2(3),
- mv_no NUMBER(2),
- issue_date DATE,
- return_date DATE,
- CONSTRAINT cust_con FOREIGN KEY(cust_id) REFERENCES custo(cust_id),
- CONSTRAINT movies_con FOREIGN KEY(mv_no) REFERENCES movies(mv_no)
- );
- -- 1. Names of all the customers
- SQL> SELECT fname,lname FROM custo;
- -- FNAME LNAME
- -- --------------- ---------------
- -- Ivan Bayross
- -- Vandana Saitwal
- -- Basu Navindgi
- -- Ravi Sreedharan
- -- Rukmini
- -- Pramada Jaguste
- -- 2. Entire customer table
- SQL> SELECT * FROM custo;
- -- CUS LNAME FNAME AR PHONE_NO
- -- --- --------------- --------------- -- ----------
- -- a01 Bayross Ivan sa 6125467
- -- a02 Saitwal Vandana mu 5560379
- -- a04 Navindgi Basu ba 6125401
- -- a05 Sreedharan Ravi va
- -- a06 Rukmini gh 5125274
- -- a03 Jaguste Pramada da 45863891
- -- 3. fname and area of customers
- SQL> SELECT fname, area FROM custo;
- -- FNAME AR
- -- --------------- --
- -- Ivan sa
- -- Vandana mu
- -- Basu ba
- -- Ravi va
- -- Rukmini gh
- -- Pramada da
- -- 4. Various movie types in the movie table
- SQL> SELECT DISTINCT(TYPE) FROM movies;
- -- TYPE
- -- ----------
- -- action
- -- comedy
- -- drama
- -- horror
- -- romance
- -- suspense
- -- thriller
- -- 5. Name of all customers having 'a' as the second letter of their fnames
- SQL> SELECT fname FROM cust WHERE fname LIKE '_a%';
- -- FNAME
- -- ---------------
- -- vandana
- -- basu
- -- ravi
- -- 6. lnames of all customers that begin with 's' or 'j'
- SQL> SELECT lname FROM custo WHERE lname LIKE 'S%' OR lname LIKE 'J%';
- -- LNAME
- -- ---------------
- -- Saitwal
- -- Sreedharan
- -- Jaguste
- -- 7.Customers who stay in an area whose second letter is 'a'
- SQL> SELECT fname, lname FROM cust WHERE area LIKE '_a%';
- -- FNAME LNAME
- -- --------------- ---------------
- -- ivan bayross
- -- pramada jaguste
- -- basu navindgi
- -- ravi sreedharan
- -- 8.Customers who stay in area 'da' or 'mu' or 'gh'
- SQL> SELECT fname,lname FROM custo WHERE area = 'da' OR area = 'mu' OR area = 'gh';
- -- FNAME LNAME
- -- --------------- ---------------
- -- Vandana Saitwal
- -- Rukmini
- -- Pramada Jaguste
- -- 9. Customers whose phone numbers are greater than 5550000
- SQL> SELECT fname, lname FROM custo WHERE phone_no > 5550000;
- -- FNAME LNAME
- -- --------------- ---------------
- -- Ivan Bayross
- -- Vandana Saitwal
- -- Basu Navindgi
- -- Pramada Jaguste
- -- 10. Customers who have been issued movies in the month of September
- SQL> SELECT cust_id FROM invoice WHERE issue_date LIKE '%SEP%';
- -- CUS
- -- ---
- -- a06
- -- a06
- -- a03
- -- 11. invoice table information for cust_id 'a01' and 'a02'
- SQL> SELECT * FROM invoice WHERE cust_id = 'a01' OR cust_id = 'a02';
- -- INV MV_NO CUS ISSUE_DAT RETURN_DA
- -- --- ---------- --- --------- ---------
- -- i01 4 a01 23-JUL-93 25-JUL-93
- -- i02 3 a02 12-AUG-93 15-AUG-93
- -- i03 1 a02 15-AUG-93 18-AUG-93
- -- i08 9 a01 11-AUG-93 14-AUG-93
- -- 12. Movies of type and 'action' and 'comedy'
- SQL> SELECT title FROM movies WHERE TYPE = 'action' OR TYPE = 'comedy';
- -- TITLE
- -- -------------------------
- -- bloody vengeance
- -- home alone
- -- quick change
- -- carry on doctor
- -- 13. Movie whose price is greater than 150 and less than or equal to 200
- SQL> SELECT title FROM movies WHERE price > 150 AND price <=200;
- -- TITLE
- -- -------------------------
- -- bloody vengeance
- -- the firm
- -- pretty woman
- -- the fugitive
- -- dracula
- -- gone with the wind
- -- 14, 15. movies that cost more than 150 and new cost as original cost * 15
- SQL> SELECT title, price * 15 AS "new_cost" FROM movies WHERE price > 150;
- -- TITLE new_cost
- -- ------------------------- ----------
- -- bloody vengeance 2714.25
- -- the firm 3000
- -- pretty woman 2258.25
- -- the fugitive 3000
- -- dracula 2253.75
- -- gone with the wind 3000
- -- 16. Movies sorted according to title
- SQL> SELECT title FROM movies ORDER BY title;
- -- TITLE
- -- -------------------------
- -- bloody vengeance
- -- carry on doctor
- -- coma
- -- dracula
- -- gone with the wind
- -- home alone
- -- pretty woman
- -- quick change
- -- the firm
- -- the fugitive
- -- 17. names and types of all movies except horror movies
- SQL> SELECT title, TYPE FROM movies WHERE TYPE != 'horror';
- -- TITLE TYPE
- -- ------------------------- ----------
- -- bloody vengeance action
- -- the firm thriller
- -- pretty woman romance
- -- home alone comedy
- -- the fugitive thriller
- -- coma suspense
- -- quick change comedy
- -- gone with the wind drama
- -- carry on doctor comedy
- -- 18. Square root of price of each movie
- SQL> SELECT SQRT(price) FROM movies;
- -- SQRT(PRICE)
- -- -----------
- -- 13.4517657
- -- 14.1421356
- -- 12.2698818
- -- 12.2474487
- -- 14.1421356
- -- 10
- -- 12.2576507
- -- 10
- -- 14.1421356
- -- 10
- -- 19. Divide cost of movie 'home alone' by difference between its price and 130
- SQL> SELECT price /(price - 100) FROM movies WHERE title = 'home alone';
- -- PRICE/(PRICE-100)
- -- -----------------
- -- 3
- -- 20. Names, areas and cust_id of customers without phone numbers
- SQL> SELECT fname,lname, area, cust_id FROM custo WHERE phone_no IS NULL;
- -- FNAME LNAME AR CUS
- -- --------------- --------------- -- ---
- -- Ravi Sreedharan va a05
- -- 21. names of customers without lname.
- SQL> SELECT fname FROM custo WHERE lname IS NULL;
- -- FNAME
- -- ---------------
- -- Rukmini
- -- 22. mv_no, title, type of movies whose stars begin with 'm'
- SQL> SELECT mv_no, title, TYPE FROM movies WHERE star LIKE 'm%';
- -- MV_NO TITLE TYPE
- -- ---------- ------------------------- ----------
- -- 4 home alone comedy
- -- 6 coma suspense
- -- 23. mv_no and inv_no of customers having inv_no less than 'i05'
- SQL> SELECT mv_no, inv_no FROM invoice WHERE inv_no<'i05';
- -- MV_NO INV
- -- ---------- ---
- -- 4 i01
- -- 3 i02
- -- 1 i03
- -- 6 i04
- -- 24. Total number of customers
- SQL> SELECT COUNT(cust_id) FROM Custo;
- -- COUNT(CUST_ID)
- -- --------------
- -- 6
- -- 25. Sum of prices of all movies
- SQL> SELECT SUM(price) FROM movies;
- -- SUM(PRICE)
- -- ----------
- -- 1531.75
- -- 26. Average price of all movies
- SQL> SELECT SUM(price)/COUNT(price) FROM movies;
- -- SUM(PRICE)/COUNT(PRICE)
- -- -----------------------
- -- 153.175
- -- 27. Maximum and and Minimum movie prices, renaming to "max_price" and "min_price"
- SQL> SELECT MAX(price) AS "max_price", MIN(price) AS "min_price" FROM movies;
- -- max_price min_price
- -- ---------- ----------
- -- 200 100
- -- 28. Number of movies having price greater than 150
- SQL> SELECT COUNT(price) FROM movies WHERE price>= 150;
- -- COUNT(PRICE)
- -- ------------
- -- 7
- -- 29. Printing something
- SQL> SELECT 'The invoice number of customer ID ' || cust_id || ' is ' || inv_no || ' and movie number is ' || mv_no FROM invoice;
- -- --------------------------------------------------------------------------------
- -- The invoice number of customer ID a01 is i01 and movie number is 4
- -- The invoice number of customer ID a02 is i02 and movie number is 3
- -- The invoice number of customer ID a02 is i03 and movie number is 1
- -- The invoice number of customer ID a06 is i10 and movie number is 8
- -- The invoice number of customer ID a01 is i08 and movie number is 9
- -- The invoice number of customer ID a05 is i07 and movie number is 9
- -- The invoice number of customer ID a06 is i06 and movie number is 2
- -- The invoice number of customer ID a04 is i05 and movie number is 7
- -- The invoice number of customer ID a03 is i09 and movie number is 5
- -- The invoice number of customer ID a03 is i04 and movie number is 6
- SQL> SELECT cust_id||' has taken movie number '||mv_no||' on '|| issue_date||' and will return on '||return_date FROM invoice;
- -- --------------------------------------------------------------------------------
- -- a01 has taken movie number 4 on 23-JUL-93 and will return on 25-JUL-93
- -- a02 has taken movie number 3 on 12-AUG-93 and will return on 15-AUG-93
- -- a02 has taken movie number 1 on 15-AUG-93 and will return on 18-AUG-93
- -- a06 has taken movie number 8 on 03-SEP-93 and will return on 06-SEP-93
- -- a01 has taken movie number 9 on 11-AUG-93 and will return on 14-AUG-93
- -- a05 has taken movie number 9 on 07-JUL-93 and will return on 10-JUL-93
- -- a06 has taken movie number 2 on 18-SEP-93 and will return on 21-SEP-93
- -- a04 has taken movie number 7 on 05-AUG-93 and will return on 08-AUG-93
- -- a03 has taken movie number 5 on 06-JUL-93 and will return on 09-JUL-93
- -- a03 has taken movie number 6 on 10-SEP-93 and will return on 13-SEP-93
- -- 30. Printing the type and average price of each movie
- SQL> SELECT TYPE, avg(price) FROM movies GROUP BY TYPE;
- -- TYPE AVG(PRICE)
- -- ---------- ----------
- -- action 180.95
- -- comedy 116.666667
- -- drama 200
- -- horror 150.25
- -- romance 150.55
- -- suspense 100
- -- thriller 200
- -- 31. Number of movies in each type
- SQL> SELECT TYPE, COUNT(title) FROM movies GROUP BY TYPE;
- -- TYPE COUNT(TITLE)
- -- ---------- ------------
- -- action 1
- -- comedy 3
- -- drama 1
- -- horror 1
- -- romance 1
- -- suspense 1
- -- thriller 2
- -- 32. Number of movies in 'comedy' and 'thriller' type
- SQL> SELECT TYPE, COUNT(TYPE) FROM movies WHERE TYPE = 'comedy' OR TYPE = 'thriller' GROUP BY TYPE;
- -- TYPE COUNT(TYPE)
- -- ---------- -----------
- -- comedy 3
- -- thriller 2
- -- 33. Average price of all movies where type is 'comedy' or 'thriller' and price is greater than or equal to 150
- SQL> SELECT TYPE, avg(price) FROM movies WHERE price <= 150 GROUP BY TYPE;
- -- TYPE AVG(PRICE)
- -- ---------- ----------
- -- comedy 116.666667
- -- suspense 100
- -- 34. Average price of all movies where type is 'comedy' or 'thriller' and price
- SQL> SELECT TYPE, avg(price) FROM movies WHERE price>=150 GROUP BY TYPE HAVING TYPE = 'comedy' OR TYPE = 'thriller';
- -- TYPE AVG(PRICE)
- -- ---------- ----------
- -- comedy 150
- -- thriller 200
- -- 35. The movie number which has been issued to 'Ivan'
- SQL> SELECT mv_no FROM invoice i, custo c WHERE i.cust_id = c.cust_id AND fname = 'Ivan';
- -- MV_NO
- -- ----------
- -- 4
- -- 9
- -- 36. Names and movie from all the customers who have been issued a movie
- SQL> SELECT fname, lname, mv_no FROM invoice i, custo c WHERE i.cust_id = c.cust_id;
- -- FNAME LNAME MV_NO
- -- --------------- --------------- ----------
- -- Ivan Bayross 4
- -- Vandana Saitwal 3
- -- Vandana Saitwal 1
- -- Rukmini 8
- -- Ivan Bayross 9
- -- Ravi Sreedharan 9
- -- Rukmini 2
- -- Basu Navindgi 7
- -- Pramada Jaguste 5
- -- Pramada Jaguste 6
- -- 37. Movie title & no. and customer ID for all issued movies
- SQL> SELECT title, cust_id, m.mv_no FROM movie m, invoice i WHERE m.mv_no = i.mv_no;
- -- TITLE CUS MV_NO
- -- ------------------------- --- ----------
- -- Home Alone a01 4
- -- Pretty Woman a02 3
- -- Bloody Vengeance a02 1
- -- Coma a03 6
- -- Dracula a04 7
- -- The Firm a06 2
- -- Gone with the Wind a05 9
- -- Gone with the Wind a01 9
- -- The Figitive a03 5
- -- Quick Change a06 8
- -- 38. Title and types of movies issued to Vandana
- SQL> SELECT title, TYPE FROM movie m, invoice i, cust c WHERE m.mv_no = i.mv_no AND i.cust_id = c.cust_id AND c.fname = 'Vandana';
- -- TITLE TYPE
- -- ------------------------- ----------
- -- Pretty Woman Romance
- -- Bloody Vengeance Action
- -- 39. Names of customers who have been issued Drama movies
- SQL> SELECT lname, fname FROM cust c, movie m, invoice i WHERE c.cust_id = i.cust_id AND i.mv_no = m.mv_no AND m.TYPE = 'Drama';
- -- LNAME FNAME
- -- --------------- ---------------
- -- Sreedharan Ravi
- -- Bayross Ivan
- -- 40. Customer names and movie titles for issued movies with movie number greater or equal to 3.
- SQL> SELECT 'The movie taken by '||fname||' '||lname||' is '||title||'.' FROM invoice i, movie m, cust c WHERE i.cust_id = c. cust_id AND i.mv_no = m.mv_no AND m.mv_no >=3;
- --------------------------------------------------------------------------------
- -- The movie taken by Vandana Saitwal is Pretty Woman.
- -- The movie taken by Ivan Bayross is Home Alone.
- -- The movie taken by Pramada Jaguste is The Figitive.
- -- The movie taken by Pramada Jaguste is Coma.
- -- The movie taken by Basu Navindgi is Dracula.
- -- The movie taken by Rukmini is Quick Change.
- -- The movie taken by Ivan Bayross is Gone with the Wind.
- -- The movie taken by Ravi Sreedharan is Gone with the Wind.
- -- 41. Customers which have been issued movie number 9.
- SQL> SELECT fname FROM custo WHERE cust_id IN (SELECT cust_id FROM invoice WHERE mv_no=9);
- -- FNAME
- -- ---------------
- -- Ivan
- -- Ravi
- -- 42. Customer name and area with invoice number 'i10'.
- SQL> SELECT fname, area FROM custo WHERE cust_id IN (SELECT cust_id FROM invoice WHERE inv_no = 'i10');
- -- FNAME AR
- -- --------------- --
- -- Rukmini gh
- -- 43. Customer names and phone numbers who have been issued movies
- SQL> SELECT lname, fname, ph_no gtom cust WHERE cust_id IN(SELECT cust_id IN invoice);
- -- 44. Name of movies issued to Vandana and Ivan
- SQL> SELECT title FROM movies WHERE mv_no IN(SELECT mv_no FROM invoice WHERE cust_id IN(SELECT cust_id FROM custo WHERE fname = 'Vandana' OR fname = 'Ivan'));
- -- TITLE
- -- -------------------------
- -- home alone
- -- pretty woman
- -- bloody vengeance
- -- gone with the wind
- -- 45. Movie number, movie names issued to all customers
- SQL> SELECT mv_no, title FROM movies WHERE mv_no IN (SELECT mv_no FROM invoice);
- -- MV_NO TITLE
- -- ---------- -------------------------
- -- 4 home alone
- -- 3 pretty woman
- -- 1 bloody vengeance
- -- 8 quick change
- -- 9 gone with the wind
- -- 2 the firm
- -- 7 dracula
- -- 5 the fugitive
- -- 6 coma
- -- 46. Type and movie number of movies issued to cust_id a01 and a02
- SQL> SELECT mv_no, title FROM movies WHERE mv_no IN (SELECT mv_no FROM invoice WHERE cust_id = 'a01' OR cust_id = 'a02');
- -- MV_NO TITLE
- -- ---------- -------------------------
- -- 4 home alone
- -- 3 pretty woman
- -- 1 bloody vengeance
- -- 9 gone with the wind
- -- 47. Cust_id of the customer issuing the movie staring tom cruise
- SELECT cust_id FROM invoice WHERE mv_no IN (SELECT mv_no FROM movies WHERE star = 'tom cruise');
- -- CUS
- -- ---
- -- a06
- -- 48. lname, fname who have been issued movies
- SQL> SELECT lname, fname FROM custo WHERE cust_id IN(SELECT cust_id FROM invoice);
- -- LNAME FNAME
- -- --------------- ---------------
- -- Bayross Ivan
- -- Saitwal Vandana
- -- Rukmini
- -- Sreedharan Ravi
- -- Navindgi Basu
- -- Jaguste Pramada
- -- 49. Invoice number and day on which customers were issued movies
- SQL> SELECT inv_no,to_char(issue_date, 'day') FROM invoice;
- -- INV TO_CHAR(I
- -- --- ---------
- -- i01 friday
- -- i02 thursday
- -- i03 sunday
- -- i10 friday
- -- i08 wednesday
- -- i07 wednesday
- -- i06 saturday
- -- i05 thursday
- -- i09 tuesday
- -- i04 friday
- -- 50. Month(in alphabets) in which customers are supposed to return the movies
- SQL> SELECT to_char(return_date, 'month') FROM invoice;
- -- TO_CHAR(R
- -- ---------
- -- july
- -- august
- -- august
- -- september
- -- august
- -- july
- -- september
- -- august
- -- july
- -- september
- -- 51. issue date in the form 'dd-month-year'
- SQL> SELECT to_char(issue_date, 'dd-month-yyyy') FROM invoice;
- -- TO_CHAR(ISSUE_DAT
- -- -----------------
- -- 23-july -1993
- -- 12-august -1993
- -- 15-august -1993
- -- 03-september-1993
- -- 11-august -1993
- -- 07-july -1993
- -- 18-september-1993
- -- 05-august -1993
- -- 06-july -1993
- -- 10-september-1993
- -- 52. The date, 15 days after the current date
- SQL> SELECT sysdate+15 FROM dual;
- -- SYSDATE+1
- -- ---------
- -- 08-OCT-13
- -- 53. Number of days elapsed between the current date and the return date of the movies for all customers
- SQL> SELECT sysdate-return_date FROM invoice;
- -- SYSDATE-RETURN_DATE
- -- -------------------
- -- 7365.64499
- -- 7344.64499
- -- 7341.64499
- -- 7322.64499
- -- 7345.64499
- -- 7380.64499
- -- 7307.64499
- -- 7351.64499
- -- 7381.64499
- -- 7315.64499
- -- 54. Changing telephone number of Pramada to 466389
- SQL> UPDATE custo SET phone_no = 466389 WHERE fname = 'Vandana';
- -- 1 row updated.
- -- 55. Changing issue date of cust_id 'a01' to 24-07-93
- UPDATE invoice SET issue_date = '24-JUL-93' WHERE cust_id = 'a01';
- -- 1 row updated.
- -- 56. Changing the price of Gone with the Wind to Rs. 250.00
- SQL> UPDATE movies SET price = 250.00 WHERE title = 'gone with the wind';
- -- 1 row updated.
- -- 57. Deleting the record with invoice no. 'i08' from the invoice table
- SQL> DELETE FROM invoice WHERE inv_no = 'i08';
- -- 1 row deleted.
- -- 58. Deleting all the records having return date befor 10/07/93
- DELETE FROM invoice WHERE return_date < '10-JUL-93';
- -- 1 row deleted.
- -- 59. Changing area of cust_id 'a05' to 'vs'
- SQL> UPDATE custo SET area = 'vs' WHERE cust_id = 'a05';
- -- 1 row updated.
- -- 60. Changing the return date of invoice number 'i08' to 16/08/93
- SQL> UPDATE invoice SET return_date = '16-AUG-93' WHERE inv_no = 'i08';
- -- 1 row updated.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement