[Oracle SQL Exercise 2] #Oracle #SQL

2_Problem_Assignment.sql

DROP TABLE TOY_STORE_1579349 CASCADE CONSTRAINTS;
DROP TABLE TOY_DTLS_1579349 CASCADE CONSTRAINTS; 
DROP TABLE TOY_REL_1579349 CASCADE CONSTRAINTS;
--DROP SEQUENCE TOY_STORE_SN_1579349;
--DROP SEQUENCE TOY_DTLS_SN_1579349;

CREATE TABLE TOY_STORE_1579349
(
  TOY_STORE_ID NUMBER(3) PRIMARY KEY,
  TOY_STORE_NAME VARCHAR2(30) NOT NULL,
  CITY VARCHAR2(30) DEFAULT 'Delhi',
  PHONENUMBER NUMBER(10) UNIQUE NOT NULL,
  STORE_OPENING_TIME TIMESTAMP CHECK(EXTRACT(MINUTE FROM STORE_OPENING_TIME) >= 0 AND (EXTRACT(HOUR FROM STORE_OPENING_TIME)) >= 8),
  STORE_CLOSING_TIME TIMESTAMP CHECK(EXTRACT(HOUR FROM STORE_CLOSING_TIME) < 22)
);

--CREATE SEQUENCE TOY_STORE_SN_1579349;

INSERT INTO TOY_STORE_1579349 VALUES(1, 'Kid''s Cave', 'Delhi', 9912312312, TO_TIMESTAMP('09:10:12', 'HH24:MI:SS'), TO_TIMESTAMP('21:42:05', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(2, 'Kid''s Corner', 'Mumbai', 9912312313, TO_TIMESTAMP('09:00:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(3, 'Play and Grow', 'Mumbai', 9912312301, TO_TIMESTAMP('09:00:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(4, 'Puzzles and More', 'Delhi', 8112312301, TO_TIMESTAMP('08:15:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(5, 'Uncle Sam Toys Den', 'Delhi', 8112312314, TO_TIMESTAMP('08:01:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));
INSERT INTO TOY_STORE_1579349 VALUES(6, 'Mickey Toys', 'Delhi', 8222312301, TO_TIMESTAMP('09:00:00', 'HH24:MI:SS'), TO_TIMESTAMP('20:00:00', 'HH24:MI:SS'));

CREATE TABLE TOY_DTLS_1579349
(
  TOY_ID NUMBER(4),
  TOY_NAME VARCHAR2(30),
  TOY_PRICE NUMBER(7,2),
  DISCOUNT_PERCENT NUMBER(5,2),
  AGEGROUP NUMBER(3) default 5,
  TOY_RATING NUMBER(1),
  TOY_CATEGORY CHAR(1),
  TOY_PIC BLOB
);

ALTER TABLE TOY_DTLS_1579349 ADD 
(
  CONSTRAINT pk_1 PRIMARY KEY(TOY_ID),
  CONSTRAINT uc1_ UNIQUE(TOY_NAME),
  CONSTRAINT chk_TOY_NAME CHECK(TOY_NAME IS NOT NULL),
  CONSTRAINT chk_TOY_PRICE CHECK(TOY_PRICE > 10 AND TOY_PRICE IS NOT NULL),
  CONSTRAINT chk_DISCOUNT_PERCENT CHECK(DISCOUNT_PERCENT < 90),
  CONSTRAINT chk_TOY_RATING CHECK(TOY_RATING BETWEEN 1 AND 5),
  CONSTRAINT chk_TOY_CATEGORY CHECK(TOY_CATEGORY IN ('I', 'O', 'B', 'i', 'o', 'b', 'Indoor', 'Outdoor', 'Both'))
);

--CREATE SEQUENCE TOY_DTLS_SN_1579349;

INSERT INTO TOY_DTLS_1579349 VALUES(1, 'Bat', 500, NULL, 5, NULL, NULL, NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(2, 'Ball', 50, NULL, 5, NULL, 'o', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(3, 'Brainvita', 250, 2, 10, 5, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(4, 'Scrabble', 100, 80, 15, 1, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(5, 'Snakes and Ladders', 40, 0, 5, 2, 'I', NULL);

INSERT INTO TOY_DTLS_1579349 VALUES(6, 'Chess', 50, 5, 10, 4, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(7, 'Basket Ball', 150, 5, 10, 4, 'I', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(8, 'Volley Ball', 150, 5, 10, 4, 'o', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(9, 'Ping pong Ball', 30, 2, 5, 2, 'b', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(10, 'Skates', 450, NULL, 10, 3, 'o', NULL);
INSERT INTO TOY_DTLS_1579349 VALUES(11, 'Racing _-_ cars', 50, NULL, 5, 3, 'I', NULL);

CREATE TABLE TOY_REL_1579349
(
  TOY_ID NUMBER(4),
  TOY_STORE_ID NUMBER(3),
  IN_STOCK CHAR(1) DEFAULT 'N' CHECK(IN_STOCK IN ('Y', 'N')),
  AVAILABLE_QTY NUMBER(3) DEFAULT 0,
  PRIMARY KEY(TOY_ID, TOY_STORE_ID),
  CONSTRAINT fk_RelDtls FOREIGN KEY(TOY_ID) REFERENCES TOY_DTLS_1579349,
  CONSTRAINT fk_RelStore FOREIGN KEY(TOY_STORE_ID) REFERENCES TOY_STORE_1579349
);

INSERT INTO TOY_REL_1579349 VALUES(1, 1, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(2, 2, 'Y', 4);
INSERT INTO TOY_REL_1579349 VALUES(3, 2, 'Y', 6);
INSERT INTO TOY_REL_1579349 VALUES(1, 4, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(3, 4, 'Y', 12);
INSERT INTO TOY_REL_1579349 VALUES(4, 4, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(5, 5, 'Y', 4);
INSERT INTO TOY_REL_1579349 VALUES(6, 6, 'Y', 6);
INSERT INTO TOY_REL_1579349 VALUES(7, 1, 'N', 10);
INSERT INTO TOY_REL_1579349 VALUES(8, 2, 'Y', 12);
INSERT INTO TOY_REL_1579349 VALUES(9, 3, 'N', 10);
INSERT INTO TOY_REL_1579349 VALUES(10, 4, 'Y', 4);
INSERT INTO TOY_REL_1579349 VALUES(6, 5, 'Y', 6);
INSERT INTO TOY_REL_1579349 VALUES(7, 6, 'N', 0);
INSERT INTO TOY_REL_1579349 VALUES(8, 4, 'Y', 12);

--1.
SELECT CITY, COUNT(TOY_STORE_ID) AS NUM_OF_STORE
FROM TOY_STORE_1579349
GROUP BY CITY;

--2.
SELECT * FROM (
  SELECT TOY_STORE_NAME, (STORE_CLOSING_TIME - STORE_OPENING_TIME) AS MAX_OPEN_TIME
  FROM TOY_STORE_1579349
  ORDER BY MAX_OPEN_TIME DESC
)
WHERE ROWNUM = 1;

--SELECT * FROM (
--  SELECT TOY_STORE_NAME, TO_NUMBER(TO_CHAR((STORE_CLOSING_TIME - STORE_OPENING_TIME), 'HH24:MI:SS')) AS MAX_OPEN_TIME
--  FROM TOY_STORE_1579349
--  ORDER BY MAX_OPEN_TIME DESC
--)
--WHERE ROWNUM = 1;

--3.
SELECT TOY_STORE_NAME, STORE_OPENING_TIME FROM TOY_STORE_1579349
WHERE TO_NUMBER(TO_CHAR(STORE_OPENING_TIME, 'HH24')) <= (TO_NUMBER(TO_CHAR(LOCALTIMESTAMP, 'HH24')) - 2);

--4.
SELECT TOY_STORE_NAME, STORE_OPENING_TIME FROM TOY_STORE_1579349
WHERE TO_NUMBER(TO_CHAR(STORE_OPENING_TIME, 'HH24')) < 9 AND TO_NUMBER(TO_CHAR(STORE_CLOSING_TIME, 'HH24')) > 21;

--5.
SELECT TOY_STORE_NAME, STORE_OPENING_TIME FROM TOY_STORE_1579349
WHERE UPPER(TOY_STORE_NAME) LIKE 'P%E%';

--6.
SELECT * FROM (
  SELECT CITY, COUNT(TOY_STORE_ID) AS NUM_OF_STORE
  FROM TOY_STORE_1579349
  GROUP BY CITY
  ORDER BY NUM_OF_STORE DESC
) WHERE ROWNUM = 2;

--7.
SELECT TOY_STORE_NAME, STORE_OPENING_TIME FROM TOY_STORE_1579349
WHERE UPPER(TOY_STORE_NAME) LIKE 'KID%';

--8.
SELECT TOY_NAME, TOY_CATEGORY
FROM TOY_DTLS_1579349
WHERE TOY_CATEGORY = 'o';

--9.
SELECT TOY_CATEGORY, COUNT(TOY_ID) AS NUM_OF_TOY
FROM TOY_DTLS_1579349
GROUP BY TOY_CATEGORY;

--10.
SELECT TOY_STORE_NAME FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID 
MINUS (
SELECT TOY_STORE_NAME FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_CATEGORY = 'I' OR TOY_CATEGORY = 'B');

--11.
SELECT TOY_CATEGORY, COUNT(ts.TOY_STORE_ID) AS NUM_OF_STORE FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY TOY_CATEGORY;

--12.
SELECT TOY_NAME, TOY_RATING FROM TOY_DTLS_1579349
ORDER BY TOY_RATING DESC;

--13.
SELECT TOY_NAME, TOY_RATING FROM TOY_DTLS_1579349
WHERE TOY_RATING IS NULL;

--14.
SELECT AVG(TOY_RATING) AS AVG_RATING FROM TOY_DTLS_1579349;

--15.
SELECT TOY_STORE_NAME, TOY_NAME, TOY_RATING FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_RATING = (SELECT MAX(TOY_RATING) FROM TOY_DTLS_1579349);

--16.
SELECT AGEGROUP, TOY_NAME FROM TOY_DTLS_1579349
ORDER BY AGEGROUP ASC;

--17.
SELECT TOY_STORE_NAME, AGEGROUP FROM TOY_REL_1579349 tr
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
WHERE AGEGROUP = (SELECT MIN(AGEGROUP) FROM TOY_DTLS_1579349);

--18.
SELECT AVG(TOY_RATING) FROM TOY_DTLS_1579349
WHERE AGEGROUP = (SELECT MIN(AGEGROUP) FROM TOY_DTLS_1579349);

--19.
SELECT TOY_NAME, DISCOUNT_PERCENT FROM TOY_DTLS_1579349
WHERE DISCOUNT_PERCENT = (SELECT MAX(DISCOUNT_PERCENT) FROM TOY_DTLS_1579349);

--20.
SELECT TOY_PRICE, DISCOUNT_PERCENT, (TOY_PRICE * (1 - (DISCOUNT_PERCENT/100))) AS DISCOUNTED_PRICE 
FROM TOY_DTLS_1579349;

--21.
SELECT TOY_PRICE, DISCOUNT_PERCENT FROM TOY_DTLS_1579349
WHERE TOY_PRICE = (SELECT MIN(TOY_PRICE) FROM TOY_DTLS_1579349) OR
DISCOUNT_PERCENT = (SELECT MIN(DISCOUNT_PERCENT) FROM TOY_DTLS_1579349);

--22.
SELECT (MAX(TOY_PRICE) - MIN(TOY_PRICE)) AS DIFF_HIGHEST_LOWEST FROM TOY_DTLS_1579349;

--23.
SELECT TOY_STORE_NAME, TOY_PRICE FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_PRICE = (SELECT MAX(TOY_PRICE) FROM TOY_DTLS_1579349);

--24.
WITH t1 AS (
SELECT TOY_STORE_NAME, TOY_NAME, (TOY_PRICE * (1 - (DISCOUNT_PERCENT/100))) AS DISCOUNTED_PRICE FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID)
SELECT * FROM t1
WHERE DISCOUNTED_PRICE = (SELECT MIN(DISCOUNTED_PRICE) FROM t1);

--25.
SELECT TOY_NAME, TOY_PRICE FROM TOY_DTLS_1579349
WHERE TOY_PRICE > (SELECT AVG(TOY_PRICE) FROM TOY_DTLS_1579349);

--26.
SELECT * FROM (
SELECT TOY_CATEGORY, AVG(TOY_PRICE) AS AVG_PRICE FROM TOY_DTLS_1579349
GROUP BY TOY_CATEGORY)
WHERE AVG_PRICE > (SELECT MIN(TOY_PRICE) FROM TOY_DTLS_1579349);

--27.
SELECT * FROM (
SELECT CITY, AGEGROUP, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE AGEGROUP = (SELECT MIN(AGEGROUP) FROM TOY_DTLS_1579349)
GROUP BY CITY, AGEGROUP
ORDER BY NUM_OF_TOY DESC )
WHERE ROWNUM = 1;

--28.
SELECT CITY, COUNT(ts.TOY_STORE_ID) AS NUM_OF_STORE, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY CITY;

--29.
SELECT TOY_STORE_NAME, TOY_NAME, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY TOY_STORE_NAME, TOY_NAME;

--30.
SELECT * FROM (
SELECT TOY_STORE_NAME, TOY_PRICE, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_PRICE = (SELECT MIN(TOY_PRICE) FROM TOY_DTLS_1579349)
GROUP BY TOY_STORE_NAME, TOY_PRICE
ORDER BY NUM_OF_TOY DESC
) WHERE ROWNUM = 1;

--31.
SELECT * FROM (
SELECT TOY_STORE_NAME, TOY_NAME, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY TOY_STORE_NAME, TOY_NAME
) WHERE NUM_OF_TOY > 0 AND NUM_OF_TOY < 12;

--32.
SELECT AGEGROUP, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE AGEGROUP > 5
GROUP BY AGEGROUP;

--33. Display toy store name where total number of toys available is greater than maximum available quantity for any toy
SELECT * FROM (
SELECT TOY_STORE_NAME, SUM(AVAILABLE_QTY) AS NUM_OF_TOY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY TOY_STORE_NAME
) WHERE NUM_OF_TOY > (SELECT MAX(AVAILABLE_QTY) FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID);

--34. Display toy store names that belong to the same city.
SELECT CITY, TOY_STORE_NAME FROM TOY_STORE_1579349;

--35. Display toy names that have the same price
SELECT TOY_PRICE, TOY_NAME FROM TOY_DTLS_1579349;

--36. Display toy store which has not stored any toys yet
SELECT TOY_STORE_NAME, SUM(AVAILABLE_QTY) FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY TOY_STORE_NAME
HAVING SUM(AVAILABLE_QTY) = 0;

--37. Display Toys that are not available in any toy store yet
SELECT TOY_ID, TOY_NAME FROM TOY_DTLS_1579349 t1 JOIN (
SELECT TOY_ID FROM TOY_DTLS_1579349
MINUS (SELECT TOY_ID FROM TOY_REL_1579349)) t2
ON t1.TOY_ID = t2.TOY_ID;

--38. Display Toy store name with Toy names it sells, also those stores which do not sell any toys yet should be displayed
(SELECT TOY_STORE_NAME, TOY_NAME FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID)
UNION 
(SELECT TOY_STORE_NAME, TOY_NAME FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY TOY_STORE_NAME, TOY_NAME
HAVING COUNT(tr.TOY_ID) = 0);

--39. Display Toy store name with Toy names it sells, also those toys which are not sold in any toy store yet should be displayed.
--(SELECT TOY_STORE_NAME, TOY_NAME FROM TOY_REL_1579349 tr
--JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
--JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID)
--UNION
--(SELECT TOY_STORE_NAME, tr.TOY_NAME 
--FROM (SELECT TOY_ID FROM TOY_DTLS_1579349
--MINUS (SELECT TOY_ID FROM TOY_REL_1579349)) t1
--JOIN TOY_REL_1579349 tr ON t1.TOY_ID = tr.TOY_ID
--JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID);

--40.

--41.
SELECT TOY_STORE_NAME, TOY_RATING FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
MINUS
(SELECT TOY_STORE_NAME, TOY_RATING FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_RATING < 3 AND TOY_RATING IS NOT NULL);

--42.
SELECT TOY_STORE_NAME, TOY_RATING FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
MINUS
(SELECT TOY_STORE_NAME, TOY_RATING FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_RATING < (SELECT MAX(TOY_RATING) FROM TOY_DTLS_1579349) AND TOY_RATING IS NOT NULL);

--43.
SELECT TOY_STORE_NAME, DISCOUNT_PERCENT FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
MINUS
(SELECT TOY_STORE_NAME, DISCOUNT_PERCENT FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE DISCOUNT_PERCENT > 0);

--44.
SELECT * FROM (
SELECT td.TOY_ID, TOY_NAME, DISCOUNT_PERCENT, AGEGROUP, TOY_RATING, TOY_CATEGORY, COUNT(ts.TOY_STORE_ID) AS NUM_OF_STORE FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
GROUP BY td.TOY_ID, TOY_NAME, DISCOUNT_PERCENT, AGEGROUP, TOY_RATING, TOY_CATEGORY)
WHERE NUM_OF_STORE = 1;

--45.
SELECT * FROM TOY_DTLS_1579349 WHERE TOY_NAME LIKE '%\_%' ESCAPE '\';

--46.
SELECT * FROM TOY_DTLS_1579349 WHERE LENGTH(TOY_NAME) > 6;

--47.


--48. Display all Toy store and Toy names without any spaces like PingpongBall.
SELECT REPLACE(TOY_STORE_NAME, ' ', ''), REPLACE(TOY_NAME, ' ', '') FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID;

--49.
SELECT TOY_NAME || '**' || TOY_PRICE || '**' || AVAILABLE_QTY FROM TOY_REL_1579349 tr
JOIN TOY_DTLS_1579349 td ON tr.TOY_ID = td.TOY_ID
JOIN TOY_STORE_1579349 ts ON tr.TOY_STORE_ID = ts.TOY_STORE_ID
WHERE TOY_STORE_NAME LIKE 'Puzzles and More';

--50. Display count of store names that start with the same first alphabet, also display that alphabet.
SELECT FIRST_LETTER, COUNT(FIRST_LETTER) AS NUM_OF_STORE_NAME_START_WITH FROM (
SELECT TOY_STORE_NAME, SUBSTR(TOY_STORE_NAME, 0, 1) AS FIRST_LETTER FROM TOY_STORE_1579349)
GROUP BY FIRST_LETTER;