Q1.
TABLE CREATE:
CREATE TABLE PRODUCTS_7
(
P_ID VARCHAR(15) PRIMARY KEY,
P_NAME VARCHAR(9),
CATEGORY VARCHAR(15),
PRICE NUM BER(10),
STOCK_QUANTITY NUMBER(15)
);
CREATE TABLE SUPPLIERS_7
(
S_ID VARCHAR(15) PRIMARY KEY,
S_NAME VARCHAR(15),
CONTACT NUMBER(15),
ADDRESS VARCHAR(15)
);
CREATE TABLE PRODUCT_SUPPLIER_7
(
P_S_ID VARCHAR(15) PRIMARY KEY,
P_ID VARCHAR(15),
S_ID VARCHAR(15),
SUPPLY_DATE DATE,
QUANTITY_SUPPLIED NUM BER(15)
);
VALUE INSERTION:
INSERT INTO PRODUCTS_7 VALUES('P101','RICE','FOOD','100','7');
INSERT INTO PRODUCTS_7
VALUES('P102','FRIDGE','ELECTRONICS','25000','4');
INSERT INTO PRODUCTS_7 VALUES('P105','TEA','BEVERAGES','200','5');
INSERT INTO SUPPLIERS_7 VALUES('S1','RAM','12345','AS_LANE');
INSERT INTO SUPPLIERS_7 VALUES('S2','SAM','67890','MG_ROAD');
INSERT INTO SUPPLIERS_7 VALUES('S3','JODU','54321','AJC_ROAD');
INSERT INTO PRODUCT_SUPPLIER_7 VALUES('11','P101','S1','05-NOV-
2024','5');
INSERT INTO PRODUCT_SUPPLIER_7 VALUES('12','P102','S2','10-NOV-
2024','2');
INSERT INTO PRODUCT_SUPPLIER_7 VALUES('15','P105','S3','01-NOV-
2024','3');
INSERT INTO PRODUCT_SUPPLIER_7 VALUES('10','P105','S2','02-NOV-
2024','1');
INSERT INTO PRODUCT_SUPPLIER_7 VALUES('20','P102','S3','07-NOV-
2024','1');
DISPLAY TABLE:
SQL> SELECT * FROM PRODUCTS_7;
P_ID P_NAME CATEGORY PRICE STOCK_QUANTITY
--------------- --------- --------------- ---------- --------------
P101 RICE FOOD 100 7
P102 FRIDGE ELECTRONICS 25000 4
P105 TEA BEVERAGES 200 5
SQL> SELECT * FROM SUPPLIERS_7;
S_ID S_NAME CONTACT ADDRESS
--------------- --------------- ---------- ---------------
S1 RAM 12345 AS_LANE
S2 SAM 67890 MG_ROAD
S3 JODU 54321 AJC_ROAD
SQL> SELECT * FROM PRODUCT_SUPPLIER_7;
P_S_ID P_ID S_ID SUPPLY_DA QUANTITY_SUPPLIED
--------------- --------------- --------------- --------- -----------------
11 P101 S1 05 -NOV-24 5
12 P102 S2 10-NOV-24 2
15 P105 S3 01-NOV-24 3
10 P105 S2 02 -NOV-24 1
20 P102 S3 07-NOV-24 1
QUERIES:
[Link] OUT OF STOCK PRODUCTS, A PRODUCT IS OUT OF STOCK IF
STOCK_QUANTITY IS LESS THAN 5.
SQL> SELECT P_NAME FROM PRODUCTS_7 WHERE STOCK_QUANTITY < 5;
P_NAME
---------
FRIDGE
[Link] TOTAL QUANTITY OF EACH PRODUCT SUPPLIED BY EACH SUPPLIER.
SQL> SELECT PS.P_ID, P.P_NAME, PS.S_ID, S.S_NAME,
SUM (PS.QUANTITY_SUPPLIED) AS TOTAL_QUANTITY FROM
PRODUCT_SUPPLIER_7 PS JOIN PRODUCTS_7 P ON PS.P_ID = P.P_ID JOIN
SUPPLIERS_7 S ON PS.S_ID = S.S_ID GROUP BY PS.P_ID, P.P_NAME, PS.S_ID,
S.S_NAME;
P_ID P_NAME S_ID S_NAME TOTAL_QUANTITY
--------------- --------- --------------- --------------- --------------
P105 TEA S3 JODU 3
P105 TEA S2 SAM 1
P102 FRIDGE S2 SAM 2
P101 RICE S1 RAM 5
P102 FRIDGE S3 JODU 1
____________________________________________________________
Q2. TABLE CREATE:
CREATE TABLE EMPLOYEE1
(
E_ID VARCHAR(10) PRIMARY KEY,
E_NAME VARCHAR(10),
DESIGNATION VARCHAR(10),
SALARY NUMBER(10),
JOIN_DATE DATE
);
CREATE TABLE DEPARTMENT1
(
D_ID VARCHAR(10) PRIMARY KEY,
D_NAME VARCHAR(10),
LOCATION VARCHAR(10)
);
CREATE TABLE ASSIGNMENT1
(
AS_ID VARCHAR(10) PRIMARY KEY,
E_ID VARCHAR(10),
D_ID VARCHAR(10),
START_DATE DATE,
END_DATE DATE
);
VALUE INSERTION:
INSERT INTO EMPLOYEE2 VALUES('E1','RAM ','HR','1000','10-JAN-2011');
INSERT INTO EMPLOYEE2 VALUES('E2','SAM ','MD','2000','05-JAN-2017');
INSERT INTO EMPLOYEE2 VALUES('E3','ROY','GM','3000','01-FEB-2019');
INSERT INTO DEPARTMENT2 VALUES('D1','IT','1_FLOOR');
INSERT INTO DEPARTMENT2 VALUES('D2','MARKETING','2_FLOOR');
INSERT INTO DEPARTMENT2 VALUES('D3','ACCOUNTS','3_FLOOR');
INSERT INTO ASSIGNMENT2 VALUES('A1','E1','D1','05-NOV-2024','20-NOV-
2025');
INSERT INTO ASSIGNMENT2 VALUES('A2','E2','D2','10-NOV-2024','22-FEB-
2025');
INSERT INTO ASSIGNMENT2 VALUES('A3','E3','D3','01-NOV-2024','30-JAN-
2025');
INSERT INTO ASSIGNMENT2 VALUES('A4','E3','D2','02-NOV-2024','10-MAY-
2025');
INSERT INTO ASSIGNMENT2 VALUES('A5','E1','D3','07-NOV-2024','11-MAR-
2025');
DISPLAY TABLE:
SQL> SELECT * FROM EMPLOYEE2;
E_ID E_NAME DESIGNATIO SALARY JOIN_DATE
---------- ---------- ---------- ---------- ---------
E1 RAM HR 1000 10-JAN-11
E2 SAM MD 2000 05-JAN-17
E3 ROY GM 3000 01-FEB-19
SQL> SELECT * FROM DEPARTMENT2;
D_ID D_NAME LOCATION
---------- ---------- ----------
D1 IT 1_FLOOR
D2 MARKETING 2_FLOOR
D3 ACCOUNTS 3_FLOOR
SQL> SELECT * FROM ASSIGNMENT2;
AS_ID E_ID D_ID START_DAT END_DATE
---------- ---------- ---------- --------- ---------
A1 E1 D1 05-NOV-24 20-NOV-25
A2 E2 D2 10-NOV-24 22-FEB-25
A3 E3 D3 01-NOV-24 30-JAN-25
A4 E3 D2 02-NOV-24 10-MAY-25
A5 E1 D3 07-NOV-24 11-MAR-25
QUERIES:
[Link] TOTAL SALARY EXPENDITURE FOR EACH DEPARTMENT.
SQL> SELECT D.D_NAME, SUM ([Link]) AS TOTAL_SALARY_EXPENDITURE
FROM ASSIGNMENT2 A JOIN EMPLOYEE2 E ON A.E_ID = E.E_ID JOIN
DEPARTMENT2 D ON A.D_ID = D.D_ID GROUP BY D.D_NAME;
D_NAME TOTAL_SALARY_EXPENDITURE
---------- ------------------------
IT 1000
ACCOUNTS 4000
MARKETING 5000
[Link] EMPLOYEES WHO HAVE WORKED IN MORE THAN ONE DEPT.
SQL> SELECT E.E_NAME, E.E_ID, COUNT(DISTINCT A.D_ID) AS
DEPARTMENT_COUNT FROM ASSIGNMENT2 A JOIN EMPLOYEE2 E ON A.E_ID
= E.E_ID GROUP BY E.E_NAME, E.E_ID HAVING COUNT(DISTINCT A.D_ID) > 1;
E_NAME E_ID DEPARTMENT_COUNT
---------- ---------- ----------------
RAM E1 2
ROY E3 2
__________________________________________________________________
Q3. TABLE CREATE:
CREATE TABLE ROOM
(
R_NUM VARCHAR(10) PRIMARY KEY,
TYPE VARCHAR(10),
OCCUPANCY VARCHAR(10),
PRICE NUM BER(10),
AVAILABLE VARCHAR(10)
);
CREATE TABLE GUEST
(
G_ID VARCHAR(10) PRIMARY KEY,
G_NAME VARCHAR(10),
CITY VARCHAR(10),
CONTACT NUMBER(10),
EMAIL VARCHAR(10)
);
CREATE TABLE RESERVATION
(
RES_ID VARCHAR(10) PRIMARY KEY,
R_NUM VARCHAR(10),
G_ID VARCHAR(10),
CHECKIN_DATE DATE,
CHECKOUT_DATE DATE
);
VALUE INSERTION:
INSERT INTO ROOM VALUES('R1','SINGLE','1P','1000','YES');
INSERT INTO ROOM VALUES('R2','DOUBLE','2P','2000','YES');
INSERT INTO ROOM VALUES('R3','FAMILY','5P','3000','YES');
INSERT INTO GUEST VALUES('G1','RAM ','PATNA','12345','ABC@');
INSERT INTO GUEST VALUES('G2','SAM ','DELHI','67890','XYZ@');
INSERT INTO GUEST VALUES('G3','JADU','KOCHI','54321','PQR@');
INSERT INTO RESERVATION VALUES('RE1','R1','G1','05-NOV-2024','10-NOV-
2024');
INSERT INTO RESERVATION VALUES('RE2','R2','G2','10-NOV-2024','20-NOV-
2024');
INSERT INTO RESERVATION VALUES('RE3','R3','G3','01-NOV-2024','20-NOV-
2024');
DISPLAY TABLE:
SQL> SELECT * FROM ROOM ;
R_NUM TYPE OCCUPANCY PRICE AVAILABLE
---------- ---------- ---------- ---------- ----------
R1 SINGLE 1P 1000 YES
R2 DOUBLE 2P 2000 YES
R3 FAMILY 5P 3000 YES
SQL> SELECT * FROM GUEST;
G_ID G_NAME CITY CONTACT EMAIL
---------- ---------- ---------- ---------- ----------
G1 RAM PATNA 12345 ABC@
G2 SAM DELHI 67890 XYZ@
G3 JADU KOCHI 54321 PQR@
SQL> SELECT * FROM RESERVATION;
RES_ID R_NUM G_ID CHECKIN_D CHECKOUT_
---------- ---------- ---------- --------- ---------
RE1 R1 G1 05-NOV-24 10-NOV-24
RE2 R2 G2 10-NOV-24 20-NOV-24
RE3 R3 G3 01-NOV-24 20-NOV-24
QUERIES:
[Link] ROOM NO. AND CHEKIN DATES FOR ALL RESERVATION.
SQL> SELECT R_NUM, CHECKIN_DATE FROM RESERVATION;
R_NUM CHECKIN_DAT
---------- ------------
R1 05-NOV-24
R2 10-NOV-24
R3 01-NOV-24
[Link] TOTAL REVENUE GENERATED BY HOTEL IN NOVEMBER.
SQL> SELECT SUM ([Link] * (RES.CHECKOUT_DATE - RES.CHECKIN_DATE +
1))
AS TOTAL_REVENUE
FROM RESERVATION RES
JOIN ROOM R ON RES.R_NUM = R.R_NUM
WHERE RES.CHECKIN_DATE >= TO_DATE('01-NOV-2024', 'DD-M ON-YYYY')
AND RES.CHECKIN_DATE <= TO_DATE('30-NOV-2024', 'DD-M ON-YYYY');
TOTAL_REVENUE
-------------
88000
Q4. TABLE CREATE:
CREATE TABLE STUDENT
(
S_ID VARCHAR(15) PRIMARY KEY,
S_NAME VARCHAR(15),
DOB DATE,
CONTACT NUMBER(15),
EMAIL VARCHAR(15)
);
CREATE TABLE COURSE
(
C_ID VARCHAR(15) PRIMARY KEY,
TITLE VARCHAR(15),
DURATION VARCHAR(15),
FEES NUMBER(15)
);
CREATE TABLE ENROLLMENT
(
E_ID VARCHAR(15) PRIMARY KEY,
S_ID VARCHAR(15),
C_ID VARCHAR(15),
ENROLL_DATE DATE
);
VALUE INSERTION:
INSERT INTO STUDENT VALUES('S1','RAM','21-FEB-2004','12345','ABC@');
INSERT INTO STUDENT VALUES('S2','SAM ','15-AUG-2000','67890','XYZ@');
INSERT INTO STUDENT VALUES('S3','JADU','03-SEP-2003','54321','PQR@');
INSERT INTO COURSE VALUES('C1','MECHANICS','5YR','1000');
INSERT INTO COURSE VALUES('C2','CIVIL','4YR','2000');
INSERT INTO COURSE VALUES('C3','COMPUTER','4YR','3000');
INSERT INTO ENROLLMENT VALUES('E1','S1','C1','05-SEP-2022');
INSERT INTO ENROLLMENT VALUES('E2','S2','C2','10-JUL-2022');
INSERT INTO ENROLLMENT VALUES('E3','S3','C3','01-OCT-2022');
DISPLAY TABLE:
SQL> SELECT * FROM STUDENT;
S_ID S_NAME DOB CONTACT EMAIL
--------------- --------------- --------- ---------- ---------------
S1 RAM 21-FEB-04 12345 ABC@
S2 SAM 15-AUG-00 67890 XYZ@
S3 JADU 03-SEP-03 54321 PQR@
SQL> SELECT * FROM COURSE;
C_ID TITLE DURATION FEES
--------------- --------------- --------------- ----------
C1 MECHANICS 5YR 1000
C2 CIVIL 4YR 2000
C3 COMPUTER 4YR 3000
SQL> SELECT * FROM ENROLLMENT;
E_ID S_ID C_ID ENROLL_DA
--------------- --------------- --------------- ---------
E1 S1 C1 05 -SEP-22
E2 S2 C2 10 -JUL-22
E3 S3 C3 01-OCT-22
QUERIES:
[Link] NAME OF STUDENTS ENROLLED IN ‘COMPUTER’ COURSE.
SQL> SELECT S.S_NAM E FROM STUDENT S JOIN ENROLLMENT E ON S.S_ID =
E.S_ID JOIN COURSE C ON E.C_ID = C.C_ID WHERE [Link] = 'COMPUTER';
S_NAME
---------------
JADU
[Link] COURSES WITH NO ENROLLMENTS IN CURRENT YEAR-2024.
SQL> SELECT [Link] FROM COURSE C
LEFT JOIN ENROLLMENT E ON C.C_ID = E.C_ID AND EXTRACT(YEAR FROM
E.ENROLL_DATE) = 2024
WHERE E.E_ID IS NULL;
TITLE
---------------
CIVIL
COMPUTER
MECHANICS
Q.5 TABLE CREATE:
CREATE TABLE FLIGHT7
(
F_ID VARCHAR(5) PRIMARY KEY,
AIRLINE VARCHAR(10),
SOURCE VARCHAR2(9),
DESTINATION VARCHAR2(15),
DEPARTURE VARCHAR(9),
ARRIVAL VARCHAR(9)
);
CREATE TABLE PASSENGER7
(
P_ID VARCHAR(15) PRIMARY KEY,
P_NAME VARCHAR(15),
CONTACT NUMBER(15),
EMAIL VARCHAR2(15)
);
CREATE TABLE RESERVATION7
(
RES_ID VARCHAR(10) PRIMARY KEY,
F_ID VARCHAR(10),
P_ID VARCHAR(10),
RESERV_DATE DATE,
SEAT_NUM NUMBER(10)
);
VALUE INSERTION:
INSERT INTO FLIGHT7
VALUES('F1','SPICEJET','KOLKATA','DELHI','10PM','2PM ');
INSERT INTO FLIGHT7 VALUES('F2','INDIGO','DELHI','M UM BAI','1AM ','5AM');
INSERT INTO FLIGHT7
VALUES('F3','VISTARA','BENGALURU','PATNA','4PM ','9PM');
INSERT INTO PASSENGER7 VALUES('P1','RAM ','12345','ABC@');
INSERT INTO PASSENGER7 VALUES('P2','SAM ','67890','XYZ@');
INSERT INTO PASSENGER7 VALUES('P3','JADU','54321','PQR@');
INSERT INTO PASSENGER7 VALUES('P4','ROY','54021','KQR@');
INSERT INTO RESERVATION7 VALUES('RE1','F1','P1','05-NOV-2024','4');
INSERT INTO RESERVATION7 VALUES('RE2','F2','P2','10-NOV-2024','2');
INSERT INTO RESERVATION7 VALUES('RE3','F3','P3','01-NOV-2024','3');
INSERT INTO RESERVATION7 VALUES('RE4','F1','P4','08-NOV-2024','3');
DISPLAY TABLE:
SQL> SELECT * FROM FLIGHT7;
F_ID AIRLINE SOURCE DESTINATION DEPARTURE ARRIVAL
----- ---------- --------- --------------- --------- ---------
F1 SPICEJET KOLKATA DELHI 10PM 2PM
F2 INDIGO DELHI MUMBAI 1AM 5AM
F3 VISTARA BENGALURU PATNA 4PM 9PM
SQL> SELECT * FROM PASSENGER7;
P_ID P_NAME CONTACT EMAIL
--------------- --------------- ---------- ---------------
P1 RAM 12345 ABC@
P2 SAM 67890 XYZ@
P3 JADU 54321 PQR@
P4 ROY 54021 KQR@
SQL> SELECT * FROM RESERVATION7;
RES_ID F_ID P_ID RESERV_DA SEAT_NUM
---------- ---------- ---------- --------- ----------
RE1 F1 P1 05-NOV-24 4
RE2 F2 P2 10-NOV-24 2
RE3 F3 P3 01-NOV-24 3
RE4 F1 P4 08-NOV-24 3
QUERIES:
1. FIND NO. OF RESERVATIONS FOR SPECIFIC FLIGHT.
SQL> SELECT F_ID, COUNT(*) AS NUM_RESERVATIONS FROM
RESERVATION7 GROUP BY F_ID HAVING F_ID = 'F1';
F_ID NUM_RESERVATIONS
---------- ----------------
F1 2
[Link] NAME OF PASSENGER5WHO BOOK FLIGHTS FOR S GIVEN
DESTINATION.
SQL> SELECT P.P_NAME FROM PASSENGER7 P JOIN RESERVATION7 R ON
P.P_ID = R.P_ID JOIN FLIGHT7 F ON R.F_ID = F.F_ID WHERE [Link]
= 'M UM BAI';
P_NAME
---------------
SAM
Q6. TABLE CREATE:
CREATE TABLE PRODUCT18
(
P_ID VARCHAR(15) PRIMARY KEY,
P_NAME VARCHAR(9),
CATEGORY VARCHAR(15),
PRICE NUM BER(10),
STOCK_QUANTITY NUMBER(15)
);
CREATE TABLE CUSTOMER18
(
C_ID VARCHAR(15) PRIMARY KEY,
C_NAME VARCHAR(15),
CONTACT NUMBER(15),
ADDRESS VARCHAR(15)
);
CREATE TABLE ORDER18
(
O_ID VARCHAR(15) PRIMARY KEY,
P_ID VARCHAR(15),
C_ID VARCHAR(15),
ORDER_DATE DATE,
QUANTITY NUMBER(15)
);
VALUE INSERTION:
INSERT INTO PRODUCT18 VALUES('P1','RICE','FOOD','100','8');
INSERT INTO PRODUCT18 VALUES('P2','DAL','FOOD','250','19');
INSERT INTO PRODUCT18 VALUES('P3','TEA','BEVERAGES','200','9');
INSERT INTO CUSTOM ER18 VALUES('S1','RAM','12345','AS_LANE');
INSERT INTO CUSTOM ER18 VALUES('S2','SAM','67890','MG_ROAD');
INSERT INTO CUSTOM ER18 VALUES('S3','JODU','54321','AJC_ROAD');
INSERT INTO ORDER18 VALUES('11','P1','C1','05-NOV-2024','5');
INSERT INTO ORDER18 VALUES('12','P2','C2','10-NOV-2024','2');
INSERT INTO ORDER18 VALUES('16','P3','C3','01-NOV-2024','3');
INSERT INTO ORDER18 VALUES('10','P3','C2','02-NOV-2024','1');
INSERT INTO ORDER18 VALUES('20','P2','C3','07-NOV-2024','1');
DISPLAY TABLE:
SQL> SELECT * FROM PRODUCT18;
P_ID P_NAME CATEGORY PRICE STOCK_QUANTITY
--------------- --------- --------------- ---------- --------------
P1 RICE FOOD 100 8
P2 DAL FOOD 250 19
P3 TEA BEVERAGES 200 9
SQL> SELECT * FROM CUSTOMER18;
C_ID C_NAME CONTACT ADDRESS
--------------- --------------- ---------- ---------------
S1 RAM 12345 AS_LANE
S2 SAM 67890 MG_ROAD
S3 JODU 54321 AJC_ROAD
SQL> SELECT * FROM ORDER18;
O_ID P_ID C_ID ORDER_DAT QUANTITY
--------------- --------------- --------------- --------- ----------
11 P1 C1 05 -NOV-24 5
12 P2 C2 10-NOV-24 2
16 P3 C3 01-NOV-24 3
10 P3 C2 02 -NOV-24 1
20 P2 C3 07-NOV-24 1
QUERIES:
[Link] TOTAL REVENUE FOR A SPECIFIC PRODUCT CATEGORY.
SQL> SELECT
[Link],
SUM([Link] * [Link]) AS TOTAL_REVENUE
FROM
ORDER18 O
JOIN
PRODUCT18 P ON O.P_ID = P.P_ID
WHERE
[Link] = 'FOOD'
GROUP BY
[Link];
CATEGORY TOTAL_REVENUE
--------------- -------------
FOOD 1250
2. DISPLAY PRODUCTS WITH LOW STOCK QUANTITY, WHEN QUANTITY IS
LESS THAN 10.
SQL> SELECT P.P_ID, P.P_NAME, [Link], P.STOCK_QUANTITY FROM
PRODUCT18 P WHERE P.STOCK_QUANTITY < 10;
P_ID P_NAME CATEGORY STOCK_QUANTITY
--------------- --------- --------------- --------------
P1 RICE FOOD 8
P3 TEA BEVERAGES 9
[Link] CREATE:
CREATE TABLE ITEM
(
I_ID VARCHAR(10) PRIMARY KEY,
I_NAME VARCHAR(9),
TYPE VARCHAR(10),
PRICE NUM BER(10),
COLOUR VARCHAR(10)
);
CREATE TABLE CUSTOMER20
(
C_ID VARCHAR(10) PRIMARY KEY,
C_NAME VARCHAR(10),
CONTACT NUMBER(10),
CITY VARCHAR(10)
);
CREATE TABLE SALES
(
S_ID VARCHAR(10) PRIMARY KEY,
I_ID VARCHAR(10),
C_ID VARCHAR(10),
SALES_DATE DATE
);
VALUE INSERTION:
INSERT INTO ITEM VALUES('I1','RICE','FOOD','100','WHITE');
INSERT INTO ITEM VALUES('I2','DAL','FOOD','250','YELLOW');
INSERT INTO ITEM VALUES('I3','TEA','BEVERAGES','200','RED');
INSERT INTO CUSTOM ER20 VALUES('C1','RAM','12345','KOLKATA');
INSERT INTO CUSTOM ER20 VALUES('C2','SAM','67890','DUMDUM ');
INSERT INTO CUSTOM ER20 VALUES('C3','JODU','54321','DURGAPUR');
INSERT INTO SALES VALUES('S1','I1','C1','05-NOV-2024');
INSERT INTO SALES VALUES('S2','I2','C2','10-NOV-2024');
INSERT INTO SALES VALUES('S3','I3','C3','01-NOV-2024');
INSERT INTO SALES VALUES('S4','I3','C2','02-NOV-2024');
INSERT INTO SALES VALUES('S5','I2','C3','07-NOV-2024');
DISPLAY TABLE:
SQL> SELECT * FROM ITEM;
I_ID I_NAME TYPE PRICE COLOUR
---------- --------- ---------- ---------- ----------
I1 RICE FOOD 100 WHITE
I2 DAL FOOD 250 YELLOW
I3 TEA BEVERAGES 200 RED
SQL> SELECT * FROM CUSTOMER20;
C_ID C_NAME CONTACT CITY
---------- ---------- ---------- ----------
C1 RAM 12345 KOLKATA
C2 SAM 67890 DUMDUM
C3 JODU 54321 DURGAPUR
SQL> SELECT * FROM SALES;
S_ID I_ID C_ID SALES_DAT
---------- ---------- ---------- ---------
S1 I1 C1 05-NOV-24
S2 I2 C2 10-NOV-24
S3 I3 C3 01-NOV-24
S4 I3 C2 02-NOV-24
S5 I2 C3 07-NOV-24
QUERIES:
[Link] NAME OF CUSTOMER AND TOTAL PRICE OF ALL ITEMS PURCHASED
BY HIM/HER.
SQL> SELECT
C.C_NAME AS Custom er_Name,
SUM([Link]) AS Total_Price
FROM
CUSTOMER20 C
JOIN
SALES S ON C.C_ID = S.C_ID
JOIN
ITEM I ON S.I_ID = I.I_ID
GROUP BY
C.C_NAME;
CUSTOMER_N TOTAL_PRICE
---------- -----------
RAM 100
JODU 450
SAM 450
[Link] ALL CUSTOMERS FROM KOLKATA AND DURGAPURWHO HAVE
PURCHASED ‘DAL’.
SQL> SELECT
C.C_NAME AS Custom er_Name,
[Link] AS City,
I.I_NAME AS Purchased_Item
FROM
CUSTOMER20 C
JOIN
SALES S ON C.C_ID = S.C_ID
JOIN
ITEM I ON S.I_ID = I.I_ID
WHERE
[Link] IN ('KOLKATA', 'DURGAPUR')
AND I.I_NAME = 'DAL';
CUSTOMER_N CITY PURCHASED
---------- ---------- ---------
JODU DURGAPUR DAL
