SQL Queries

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.